MySQL Limitations Part 4: One thread per connection [MySQL 短板 4]

This is the third in a series on what’s seriously limiting MySQL in core use cases (links: part1, 2, 3). This post is about the way MySQL handles connections, allocating one thread per connection to the server.

MySQL is a single process with multiple threads. Not all databases are architected this way; some have multiple processes that communicate through shared memory or other means. It’s cheap to create a connection to MySQL, because it just requires creating a thread (or taking one from a cache). This is generally so fast that there isn’t really the need for connection pools as there is with other databases, at least not in the same way. Windows in particular has had excellent threading support practically forever; Linux has very good threading now, but that wasn’t always the case.

However, many development environments and programming languages really want a connection pool. They’re just built that way (I’m looking at you, Java). And many others use persistent connections by default, so that a connection isn’t really closed when it’s closed; it’s kind of like a connection pool, except that the connection is persisted from request to request within the same process, rather than being shared with whichever request needs a connection.

Connection pools and persistent connections combined with a large number of application servers can lead to a situation where the database server has a very large number of connections open to it, most of which are doing nothing. It’s not uncommon for me to see a server with 1000 to 5000 connections open, and maybe one to three are actually running queries on average. These connections originate from dozens to hundreds of application server instances. When you have a heavily sharded or otherwise horizontally scaled application, it’s not only easy to get into this pickle, it’s really hard or impossible to avoid it.

And with 5000 connections open, you get 5000 threads in the server. That increases the overhead from thread scheduling, and potentially memory usage as well. I feel like I’m forgetting some reasons that this matters — please fill in whatever’s missing in the comments.

There can be more than one solution to this problem, but the one that’s actually partially implemented is a pool of threads, which was originally coded for MySQL 6.0, but isavailable now in MariaDB.

Unfortunately it isn’t a full solution, because it can cause undesirable lock-out or waiting, and the specific implementation has ascalability bottleneck on multicore servers. Mark Callaghan has done much more investigation of the pool of threads than I have. There are more details inthis blog post by Mark, and two followup blog posts from Tim Cook (1,2).

Thanks for the great comments on the last post. Some of them were good guesses. Remember that the context for this series isn’t micro-limitations or edge-case badness (even if they are serious in some cases), but rather a focus on shortcomings in the main use cases for the server. There are a lot of things MySQL doesn’t do well, but it doesn’t matter that much, because that’s not what it’s designed for. Wrong tool, wrong use, NotABug. I’m thinking of the lack of sort-merge joins or intra-query parallelism, for example. It would be lovely to have those things, if you’re running a data warehouse on MySQL, and in some cases for other uses too (note that most databases that do have these query plans usually try to use nested-loop joins whenever possible, because of things like the lower startup cost for the query). But MySQL isn’t a data warehouse DBMS first and foremost. It’s a general-purpose OLTP database server that runs well on affordable hardware and is great for Web usage. It’s so good, in fact, that it can be used for tons of other things such as… data warehousing. But it isn’t a Netezza or Paraccel, and if it were, it wouldn’t be a great OLTP web database too.

MySQL replication is one of the core, fundamental features — and it’s single-threaded and relies on the binary log, which are two major limitations. And it has subqueries, which are a core, fundamental part of SQL — but it’s bad at certain kinds of them. That’s why I listed those as major limitations. And because MySQL is a multi-threaded database for Web usage that tends to be used in sharded environments with tons of application servers, which creates a situation with many thousands of connections to the database, and because it doesn’t handle that very well, I list its one-thread-per-connection design as a serious limitation.