NoSQL Benchmarks NoSQL use cases NoSQL Videos NoSQL Hybrid Solutions NoSQL Presentations Big Data Hadoop MapReduce Pig Hive Flume Oozie Sqoop HDFS ZooKeeper Cascading Cascalog BigTable Cassandra HBase Hypertable Couchbase CouchDB MongoDB OrientDB RavenDB Jackrabbit Terrastore Amazon DynamoDB Redis Riak Project Voldemort Tokyo Cabinet Kyoto Cabinet memcached Amazon SimpleDB Datomic MemcacheDB M/DB GT.M Amazon Dynamo Dynomite Mnesia Yahoo! PNUTS/Sherpa Neo4j InfoGrid Sones GraphDB InfiniteGraph AllegroGraph MarkLogic Clustrix CouchDB Case Studies MongoDB Case Studies NoSQL at Adobe NoSQL at Facebook NoSQL at Twitter



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_size system 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 . Set thread_cache_size to 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.

  1. In case you are wondering, Facebook will release the code. 

  2. My emphasis. 

Original title and link: Making MySQL Accept Connections Faster (NoSQL database©myNoSQL)