Making MySQL Accept Connections Faster
Mark Callaghan (Facebook) posted two graphs showing the improvements Facebook got in optimizing the speed of accepting connections in MySQL1.
First thing I thought of was persistent connections are always fast and you should always use a connection pool. Even if Facebook is a using PHP there should be a way to use a connection pool with MySQL. But maybe this is a problem that occurs only at their scale and in specific scenarios.
It was only at the time I was preparing to ask Mark for more details that I’ve noticed the link to Domas Mituzas’s post in which he profiles the MySQL connection accepting code, but he also presents a scenario that reveals this issue:
Sometimes connection avalanches come unexpected, and even if MySQL would have no trouble dealing with queries, it will have problems letting clients in.
From link to link, I then arrived at the MySQL documentation page describing how MySQL uses threads for client connections. If you are a MySQL user and haven’t seen this page I’d suggest you reading it, but here are the interesting parts:
By default, connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.
The thread cache has a size determined by the
thread_cache_sizesystem variable. The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates2 . Setthread_cache_sizeto N to enable N inactive connection threads to be cached.
I guess it’s time to connect to your MySQL server, check these settings, and update them accordingly.
Original title and link: Making MySQL Accept Connections Faster (©myNoSQL)
