Tuesday, June 27, 2017

Quick look: Memory usage aspects and connection management best practices in Aurora and MySQL

Next up in the "quick look" series is a discussion of connection management best practices and the memory usage implications of idle connections in Aurora and MySQL. I'll also throw in some notes on how to configure your connection pools to avoid unpleasant surprises.

Thread handling in Aurora vs MySQL

Community MySQL editions use "one-thread-per-connection" approach to thread handling. It means that each individual user connection receives a dedicated OS thread within the mysqld process. This comes with issues, such as: 
  • Relatively high memory usage with large number of user connections, even if the connections are completely idle.
  • Higher internal server contention and context switching overhead when working with thousands of user connections.
To avoid such issues, some servers support a thread pool approach. Examples include Percona Server and Amazon Aurora.

In Aurora, the thread pooling works as follows (in a nutshell):

  • It uses a number of "worker threads" that can switch between user sessions in order to perform work. A worker thread is not fixed or dedicated to a user connection. Whenever a user connection is not actively executing on CPU (e.g. is idle, waiting for user, waiting for IO, etc.), the worker can switch to another connection and do some useful work. This is a very efficient approach, which means that Aurora can handle thousands of concurrent clients with just a handful of workers. If it's difficult to understand, you can think of worker threads as CPU cores in a multi-core system. Even though you only have a few cores, you can easily run hundreds or programs simultaneously because they're not active all at the same time.
  • The thread pool automatically scales itself. Aurora constantly monitor the thread pool state and will launch new workers or destroy existing ones as needed. This is entirely internal and does not need any manual configuration.

Now, the fact that a server leverages thread pooling does not mean that you no longer need to pool connections on application side. Server thread pooling reduces server-side cost of maintaining connections, but it does not remove the network roundtrips needed for setting up these connections in the first place. For OLTP workloads with short-lived connections, you should still be using an application-side connection pool, because opening and closing connections isn't as simple as sending a single TCP packet. Therefore, you need to take blog posts like this with a grain of salt. I'm happy to see people being enthusiastic about Aurora, but let's be realistic :)

This is a packet trace for mysql command line connection handshake:
04:23:29.547316 IP client.32918 > server.mysql: tcp 0
04:23:29.547478 IP server.mysql > client.32918: tcp 0
04:23:29.547496 IP client.32918 > server.mysql: tcp 0
04:23:29.547823 IP server.mysql > client.32918: tcp 78
04:23:29.547839 IP client.32918 > server.mysql: tcp 0
04:23:29.547865 IP client.32918 > server.mysql: tcp 191
04:23:29.547993 IP server.mysql > client.32918: tcp 0
04:23:29.548047 IP server.mysql > client.32918: tcp 11
04:23:29.548091 IP client.32918 > server.mysql: tcp 37
04:23:29.548361 IP server.mysql > client.32918: tcp 99
04:23:29.587272 IP client.32918 > server.mysql: tcp 0

This is a packet trace for closing a mysql command line connection:
04:23:44.325276 IP client.32918 > server.mysql: tcp 5
04:23:44.325294 IP client.32918 > server.mysql: tcp 0
04:23:44.325513 IP server.mysql > client.32918: tcp 0
04:23:44.325524 IP client.32918 > server.mysql: tcp 0

To put this into perspective, this is a trace for "SELECT 1" statement or any other statement where the request/response fits into a single TCP packet (<~1.5KB):
04:23:37.117523 IP client.32918 > server.mysql: tcp 13
04:23:37.117818 IP server.mysql > client.32918: tcp 56
04:23:37.117842 IP client.32918 > server.mysql: tcp 0

It should be clear from the above network traces that connection management overhead is a real thing.

Common misconceptions

If the server uses thread pooling, you don't need an app-side connection pool

As explained above, this is not true for OLTP-style workloads where connections are opened and torn down very frequently, and clients execute relatively few statements per connection.

You don't necessarily need a connection pool if your connections are not short-lived, where not short-lived means that connection lifetime is much longer than the time required to open the connection in the first place. You could easily run a packet trace with tcpdump and see how many packets you need to open/close connections versus to run your queries.

Idle connections don't use memory

This is not true, because the server allocates a connection descriptor (a memory structure called THD) for each user connection.

What is true is that Aurora uses less memory than vanilla MySQL to maintain the same number of connections. That said, memory usage is still not zero, even with Aurora. Here's some data from a rudimentary test executed with RDS MySQL 5.6, RDS MySQL 5.7 and Aurora. Idle connections were opened from Python PyMySQL and memory usage was pulled from CloudWatch's FreeableMemory metric:


Best practices

When using a connection pool, don't forget to check and recycle connections

You should:
  • Check/validate connections when they're borrowed from the pool. It can be a simple "SELECT 1", but in Aurora you can also leverage connection checks that return a different value depending on whether the node is read-write or read-only. Simply use the @@innodb_read_only variable: if it's TRUE, you're on a Replica.
  • Check/validate connections periodically even when they're not borrowed.
  • Don't let connections remain in the pool indefinitely. Recycle connections i.e. close and reopen them periodically (e.g. every 15 minutes).

Pay close attention to your auto-commit settings

  • Always run with autocommit enabled (autocommit=1) and manage transactions explicitly i.e. by using BEGIN/START TRANSACTION and COMMIT/ROLLBACK. You should start transactions when you need them, and commit as soon as your work is done.
  • Always double-check the autocommit settings of your database driver. For example, Python drivers such as MySQLdb and PyMySQL disable autocommit by default, which is so incorrect that I think it qualifies as a bug. Since most application developers are unaware of transaction configuration concepts, this happens to be a fairly widespread issue.
Running with autocommit disabled is a bad idea: it can not only cause confusion (Why am I not seeing stuff committed by other people?) but it can and likely will result in serious performance problems. In a nutshell, long-running transactions block the database's internal garbage collection processes, which are essential to maintaining optimal performance.

I'm really serious about this. Would you leave your car's engine running all the time or would you rather just start it when you need to go somewhere, and then stop it when you're done driving?

Consider using a smart driver

In the context of this post, a smart driver is one that can:
  • Automatically discover the topology of the Aurora cluster, detect the read-write and read-only nodes and expose them under common read-write and read-only handlers.
  • Detect topology changes (e.g. due to cluster failovers) and automatically update the read-write and read-only handlers without relying on DNS propagation.
A MariaDB JDBC driver is a great example of a smart driver with native Aurora support. It's a Java solution that can be used as drop-in replacement for your current JDBC driver. If you don't use Java, the MariaDB driver can still server as inspiration for how to build a smart driver for your particular platform.

Note that a smart driver still needs to be configured correctly. For example, if your connection pool is configured to never recycle connections (i.e. connection lifetime is unlimited), even MariaDB driver won't be able to pick up topology changes such as new Replicas being added to the cluster.

Don't cache DNS or use very short cache TTLs

Unless you're using a smart database driver, you depend on DNS for failovers and load balancing across Aurora Replicas. Make sure that your client configuration doesn't add unnecessary delays on top of Aurora's 5-second DNS TTL. DNS caching can occur anywhere from OS layer to an application container. For example, JVMs are notorious for caching DNS indefinitely unless configured otherwise.

If you do cache DNS, make sure that your TTL is no higher than a few seconds.

Become familiar with your database driver's internal operations

You might be surprised how much work happens behind the scenes when you open a new database connection or run a query. Drivers and client tools can sometimes execute series of statements in order to set up session configuration, therefore increasing the cost of creating new connections and possibly changing the way in which your queries execute.
  • Use an audit log or general query log to obtain a trace from a connection handshake.
  • For each statement executed as part of connection setup, you should be able to explain the statement's purpose and describe its impact on queries you'll subsequently execute on that connection.

Understand how load balancing works with Aurora's read-only DNS endpoint

Aurora provides three types of DNS endpoints:
  • Instance endpoint, which points to a given instance by its name. An instance endpoint always points to the same instance regardless of instance role (read-write or read-only).
  • Cluster endpoint, which always points to the current Writer node. During failovers, the cluster endpoint follows the Writer.
  • Cluster read-only endpoint, which:
    • If you have Replicas, contains all of them.
    • Contains the Writer node if you do not have any Replicas.
Since the cluster read-only endpoint contains all Replicas, it can provide DNS-based round-robin load balancing. In other words, every time you resolve the cluster read-only endpoint, you'll get a random instance back. What it also means is that you must keep resolving the DNS endpoint in order to get any load balancing at all. If you only resolve the endpoint once and then keep the connection in your pool, you will always hit the same Replica.

Reducing connection downtime with SQL proxies

SQL proxies are a middleware between the clients and the database. Clients connect to the proxy, which then connects to the database (likely using thread pooling). The main advantage there is that you can maintain client connection sockets even if the backend database undergoes a restart or a failover. Some proxies may support advanced features such as query caches or  "surge buffers", where client communication is buffered at TCP layer and sent asynchronously to the backend database, which further isolates the clients from server interruptions.

When considering to use a proxy:
  • Make sure you understand (and experimentally confirm!) the benefits of using it and gauge them against the setup and maintenance costs.
  • Configure and maintain the proxy so that it doesn't become a single point of failure for your database. In other words, your proxy's HA properties should be at least as good as HA properties of the database itself. You probably don't want to have a 3-AZ database cluster with 10-second recovery time fronted by a Single-AZ proxy which takes 5 minutes to re-launch in case of failure.
Popular examples of MySQL proxies include ProxySQL, MaxScale and ScaleArc.

Design for failure and rapid recovery

Things break all the time. Downtime will happen, connections will break, you can pretty much assume that. What you do not want is application code that needs manual intervention to deal with database restarts/failovers, or takes many minutes to recover automatically. The application should be designed so that it does not add unnecessary delay on top of database's recovery time.

Always test your applications and confirm that any assumptions you have regarding recovery time are actually correct.

No comments:

Post a Comment