Recently, we noticed a ton of Errors in AWS CloudWatch Logs, the message like this:<blockquote>2020-08-21T17:43:32.142855Z 8812 [Note] Aborted connection 8812 to db: 'dbname' user: 'db_user' host: 'x.x.x.x' (Got an error reading communication packets)</blockquote>“Aborted connection" is a common MySQL communication error. &nbsp;The possible reasons are numerous includes:<ol><li>It takes more than connect_timeout seconds to obtain a connect packet.</li><li>The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.</li><li>The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld.</li><li>The client program did not call mysql_close() before exiting.</li><li>The client program ended abruptly in the middle of a data transfer.</li><li>A connection packet does not contain the right information.</li><li>Any DNS related issues and the hosts are authenticated against their IP address instead of Hostnames.</li></ol>Yes, it's a long list, unfortunately.&nbsp;<h3>MySQL Tuning</h3>We went thru the list, the first thing we tried was increasing those three variables:<ol><li>max_allowed_packet = 500M</li><li>innodb_log_buffer_size = 32M</li><li>innodb_log_file_size = 2047M</li></ol>But they did not help, then we checked some timeout variables:<ol><li><code>wait_timeout</code> -this parameter refers to the number of seconds the server waits for activity on a non-interactive connection before closing it<ul><li>Allowed value: 1-31536000</li><li>The default value for MySQL 5.7: 28800</li></ul></li><li><code>connect_timeout</code> - this parameter the number of seconds that the server waits for a connect packet before responding with Bad handshake.<ul><li>Allowed value: 2- 31536000</li><li>The default value for MySQL 5.7:10</li></ul></li><li><code>interactive_timeout</code> - this parameter refers to the number of seconds the server waits for activity on an interactive connection before closing it.<ul><li>Allowed value: 1-31536000</li><li>The default value for MySQL 5.7: 28800</li></ul></li></ol>We have default values for all, so unlikely it is due to “The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.”<h3>Application Troubleshooting</h3>It's enough for the MySQL tuning. We looked into the Application side. We use Lambda Serverless &nbsp;Apollo Server. The database connection is using KnexJS library. &nbsp;Our DB connection was something like this:<pre><code class="language-javascript">require('mysql');

const dbContext = require('knex')({
 client: 'mysql',
 connection: async () =&gt; {
 return {
 host: await getSecret(`${process.env.ENV}_DB_HOST`),
 user: await getSecret(`${process.env.ENV}_DB_USER`),
 password: await getSecret(`${process.env.ENV}_DB_PASSWORD`),
 database: await getSecret(`${process.env.ENV}_DB_NAME`),
 ssl: 'Amazon RDS'
 };
 },
 pool: {
 min: 2,
 max: 10, 
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: 30000,
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100
 },
 debug: false
});

export default dbContext;</code></pre>Updated (10/19/2020) &nbsp; We removed the pool settings <code>propagateCreateError: true. </code>Per Mikael (knex/tarn maintainer) -&nbsp;<blockquote>the setting, <code>propagateCreateError,</code> should never be touched.</blockquote>There are many people having this issue on KnexJS GitHub issues channel (see the reference links below.) The key point here is that we are using serverless,&nbsp;<blockquote>Lambda functions are stateless, so there is no way to share a connection pool between functions. - rusher</blockquote>And our DB connection had pool setting as <code>min:2 / max: 10</code>. The good thing about connection pooling is that regarding wiki:<blockquote>In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.</blockquote>The first expression for us was that since Lambda functions doe not share a connection pool, maintaining a connection pool becomes a waste here. Those connections sleep there do nothing, and they end up taking all the available connections. So as many people suggested, we changed the pool setting to be&nbsp;<pre><code class="language-javascript"> pool: {
 min: 1,
 max: 1, 
 ...</code></pre>Okay, after that, we saw some improvement. And we also did one thing:&nbsp;<pre><code class="language-javascript">const APIGatewayProxyHandler = (event, context, callback) =&gt; {
 context.callbackWaitsForEmptyEventLoop = false;</code></pre>By specifying <code>context.callbackWaitsForEmptyEventLoop = false</code>, we allow a DB connection to be maintained in a global variable in the lambda's container resulting in a faster connection.However, we noticed the performance issue after changing the connection pool to be <code>min:1/max:1</code>. Why? Did we just said, “Lambda functions doe not share a connection pool”? &nbsp;Let's be more clear, although Lambda functions do not share a connection pool, the queries in the same function do share a connection pool. For example, we have a GraphQL call, getCustomPages. When it's called, a DB connection is created, and then all queries in this call can share the connection pool, especially, those queries are written in an async way (i.e., with Promise) To demonstrate this, we can use MySQLWorkbench under Management/Client Connections. When the pool size is 1, we see only one connection created for one GraphQL call, but when the pool size is set to 2/10, we see more connections (about 10) are created for one call, and then we can see all queries are sharing those connections perfectly. We see page load time drops from ~10 seconds to a half-second by changing the connection pool from 1/1 to 2/10. That's because without sharing the pool, every query has to wait until another query finished before it can be executed. If a lambda function has thousands of queries in a loop, the big connection poll will truly boost the performance.&nbsp;So for better performance, we should still use the default pool connection. But because AWS Lambda functions do not share the pool properly, the pool connection will be open for each Lambda function call and end up using all of the available MySQL connections.&nbsp;After looking into <a target="_blank" rel="noopener noreferrer" href="https://github.com/vincit/tarn.js">tarn.js</a>, the library KnexJS used to manage the connection pool, we found out that the setting <code>idleTimeoutMillis</code> controls how many mill seconds before the free resources are destroyed. For example, if we set it to 30000, ie. 30 seconds, then the idle connections will be destroyed after 30 seconds. However, it will not destroy all sleeping connections, but keep the minimum number of connections set in the pool configuration. Since we cannot reuse the pool connection in the next Lambda function call, why not set the minimum to 0?Brilliant! The final solution we have is:<ul><li>Use the pool settings as: (Updated: 8/26/2020 - since the connections are not shared by next Lambda function call, we could set idelTiemoutMillis to be much shorter, and we changed it to 1000 from 30000.)</li></ul><pre><code class="language-javascript">pool: {
 min: 0,
 max: 10, 
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: 1000, //changed it from 30000 to 1000
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100,
 propagateCreateError: true
 },</code></pre><ul><li>Set <code>context.callbackWaitsForEmptyEventLoop</code> to be <code>false</code> (Note: the DB connection outside of Lambda handler function)</li></ul><pre><code class="language-javascript">const APIGatewayProxyHandler = (event, context, callback) =&gt; {
 context.callbackWaitsForEmptyEventLoop = false;</code></pre><h3>The Ultimate Solution</h3>As we repeated many times above, the bottleneck of the AWS Lambda functions is that they don't share the resource. To better understand this, please check the following images:<figure class="image"><img src="https://images.deniapps.com/lambda-function-verwhelming-rds.png"><figcaption>Many concurrent connections kill Amazon RDS - Photo By <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">thundra</a></figcaption></figure>Since each Lambda function is an individual process, it has to establish its connection to the DB instance. This design becomes very resource-intensive.Luckily, Amazon launched the preview of Amazon RDS Proxy in December 2019 and then made it generally available for both Mysql and PostgreSQL engines in<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/"> Jun 2020</a>, which address this issue. We call it “the Ultimate Solution” here. :-)<blockquote>Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency, application scalability, and security. RDS Proxy&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/database/improving-application-availability-with-amazon-rds-proxy/">reduces client recovery time</a>&nbsp;after failover by up to 79% for Amazon Aurora MySQL and by up to 32% for Amazon RDS for MySQL. Also, its authentication and access can be managed through integration with&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/secrets-manager">AWS Secrets Manager</a>&nbsp;and&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/iam">AWS Identity and Access Management</a>&nbsp;(IAM).</blockquote>Again, we borrow a beautiful diagram from <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">Thundra</a>:<figure class="image"><img src="https://images.deniapps.com/lambda-function-with-rds-proxy.png"><figcaption>Many concurrent connections connect to Amazon RDS via RDS Proxy - Photo By <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">thundra</a></figcaption></figure>Please follow the links to see how to set up Amazon RDS Proxy if you are interested. We are about the end of this long long post. Thank you for reading. &nbsp;Updated (08/27/2020): We added <a target="_blank" rel="noopener noreferrer" href="https://deniapps.com/blog/setup-aws-lambda-to-use-amazon-rds-proxy">AWS RDS Proxy</a>.References:<ul><li><a target="_blank" rel="noopener noreferrer" href="https://github.com/knex/knex/issues/1875">https://github.com/knex/knex/issues/1875</a> (How do I use Knex with AWS Lambda</li><li><a target="_blank" rel="noopener noreferrer" href="https://github.com/vincit/tarn.js">https://github.com/vincit/tarn.js</a> (Another Resource Pool)</li><li><a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">https://blog.thundra.io/can-lambda-and-rds-play-nicely-together</a> (Play Lambda and RDS Nicely Together)</li><li><a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/">https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/</a> (AWS RDS Proxy)</li></ul>

Starting for AWS Cloudwatch logs, we spent a week looking into 'Aborted Connection" errors on RDS. Finally, we figured out that although Lambda functions do not share a connection pool, the queries in the same function do share a connection pool, so we should keep on using the pool, but we should set the pool with min 0, so it could be destroyed without taking space. And the ultimate solution is to use Amazon RDS.

All Posts In Mysql