Connection handling in Aurora MySQL and MySQL - Amazon Aurora MySQL Database Administrator’s Handbook

Connection handling in Aurora MySQL and MySQL

MySQL Community Edition manages connections in a one-thread-per-connection fashion. This means that each individual user connection receives a dedicated operating system thread in the mysqld process. Issues with this type of connection handling include:

  • Relatively high memory use when there is a 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

Aurora MySQL supports a thread pool approach that addresses these issues. You can characterize the thread pool approach as follows:

  • It uses thread multiplexing, where a number of worker threads can switch between user sessions (connections). A worker thread is not fixed or dedicated to a single user session. Whenever a connection is not active (for example, is idle, waiting for user input, waiting for I/O, and so on), the worker thread can switch to another connection and do useful work.

    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 of programs simultaneously because they're not all active at the same time. This highly efficient approach means that Aurora MySQL can handle thousands of concurrent clients with just a handful of worker threads.

  • The thread pool automatically scales itself. The Aurora MySQL database process continuously monitors its thread pool state and launches new workers or destroys existing ones as needed. This is transparent to the user and doesn’t need any manual configuration.

Server thread pooling reduces the server-side cost of maintaining connections.

However, it doesn’t eliminate the cost of setting up these connections in the first place. Opening and closing connections isn't as simple as sending a single TCP packet. For busy workloads with short-lived connections (for example, key-value or online transaction processing (OLTP)), consider using an application-side connection pool.

The following is a network packet trace for a MySQL connection handshake taking place between a client and a MySQL-compatible server located in the same Availability Zone:

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 the connection:

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

As you can see, even the simple act of opening and closing a single connection involves an exchange of several network packets. The connection overhead becomes more pronounced when you consider SQL statements issued by drivers as part of connection setup (for example, SET variable_name = value commands used to set session-level configuration). Server-side thread pooling doesn’t eliminate this type of overhead.