![iRant | [root@jploh.com ~]# cat /var/log/irant_](http://blog.jploh.com/wp-content/themes/default2/images/blog_jplohcom.jpg)
MySQL Woes
Tuesday, July 31st, 2007I was having problems with slow queries last night and I noticed that MySQL, even with two other CPUs available, just stuck to one processor. I did a little googling and found out that MySQL is not that multi-threaded.
I’m currently working with about 70 instances, all versions, different configs and settings, different loads. (Sometimes more than 7000qps)
Basically, here are the facts:
MySQL relies on NTPL or PTHREADS to handle it’s concurrent processing, and this in itself is a design flaw. Real transactional databases use multiple processes and multiple threads within those processes. Using linux threads can push a small percentage of the load to other processors/cores but it’s not anywhere near what a real multi-process application can do
If you’re lucky, your top will have the “H” option which shows threads as fake separate processes. If you’ve got enough connections per second, you’ll see mysql throw off a few threads to a different processor, but your MPSTAT will show that only one of the cores is doing most of the work (based on interupts per second, which is more accurate than load average or any other variable in top) In fact, the majority of the load on the other cores comes from the operating system services (kjournald, syslogd, …)
MySQL was designed as a simple single-process database app that sits on the same server as php and apache, so if you’re running a typical LAMP blog site or zencart on one server, this is more than enough
If you install PostgreSQL, Oracle, Sybase, basically any real database application, you’ll see dramatic difference in how the workload is distributed. The CPU cores do about the same number of interupts per second
I’ve tested mysql on every platform, and the outcome is always the same. A Sun T2000 box with 32 cores, regardless of mysql config, thread library, or version, will show activity on only one core from MySQL (even with hundreds of open connections)
Anything more than a single core CPU is completely useless for a database server running mysql. If you want to spend money on what really matters, get a decent fiber storage array with a fast controller
The conclusion: If you want to leverage multiple CPUs on a server running nothing but a database application, don’t use MySQL. Otherwise get a single core pizzabox and a fast disk controller and you’re all set
If this is true, can I setup another instance of MySQL on the same machine and have it replicate?
I listened to the July 21st episode of FLOSS Weekly the other night and the topic was Postgresql. This is exactly what Josh Berkus was talking about. I should try playing with Postgresql again. Table partitioning sounds like a huge lifesaver and I could sure use the extra data types.
Google, on the other hand, uses MySQL in a couple of apps. They use a huge cluster of mid-range machines with large memory. I’m after utilizing the other available CPUs.
Links
- PostgreSQL Table Partitioning from PGCon 2007 in PDF or HTML
- A Googly MySQL Cluster Talk
Archives