Multi-core MySQL fun!

May 11, 2008 2:49 AM

MySQL experts: How to get the optimal performance from a database server with a quad core processor?

We've just put a database server in with a quad core processor to replace its struggling predecessor, and it does seem to be doing a lot better than the previous box. A couple of concerning things for me though are: 1) it seems that the first core is doing almost all of the work, while the other three don't really do much. 2) The load average on this box (linux) is usually in the teens, sometimes up to 40 or 50. Now while this is going on, shells are very responsive and a test query from another machine is in the 0.01 second response range.

I should also note this machine is handling on average 800-1200 concurrent connections.

So this is a 2 parter question. 1) seeing my concerns above, is that normal and nothing to be concerned about? and 2) where can I get information on optimizing MySQL for multiple cores? My Google-fu returned surprisingly little in this vein. Mostly I got benchmarks of tests where people didn't bother to explain HOW they optimized MySQL for multi-core, only mentioning that they had done so.

posted by barc0001 to computers & internet (14 comments total)

IO is more important for databases than processor.

Try RAID, or spend some on the fastest drives you can- or both.
posted by mattoxic at 3:31 AM on May 11

This thread on the MySQL forums seems to suggest that mysqld should be taking advantage of all four cores (I presume you're not using clustering or you'd have mentioned it - hence the comments about ndbd are not relevent).

I wonder if you have to set a config variable to 'tell' mysql about the other cores ?

I also wonder what the nature of your processing is ? I came across this comment which if true points at at least one way in which a programming style could lead to your multi-cores not being able to do much for (I'm just imaging that you have a schema with one or two tables which are needed for almost every transactions and which the programmers like to lock ?. Just a thought.
posted by southof40 at 4:31 AM on May 11

Sorry it's late here - should have read...

not being able to do much for you (I'm just imaging that you have a schema with one or two tables which are needed for almost every transactions and which the programmers like to lock ? Just a thought).
posted by southof40 at 4:32 AM on May 11

I don't know much about MySQL, but you generally need to explicitly convert your code from a single thread to a multi-threaded approach in order to take advantage of the multiple cores. As mattoxic mentions, the slowdown on a database is generally the read/write operations, and optimization is achieved by minimizing reads/writes, sophisticated memory management techniques, and massive RAID arrays, although the new processor can't hurt.
posted by sophist at 4:49 AM on May 11

solidDB for MySQL. IBM just bought solidDB.
posted by paulsc at 4:53 AM on May 11

Didn't mean to be as cryptic as that last comment might have appeared. First, you've got to see how your MySQL database was built. If you have InnoDB support, you can use record level locks, foreign keys, and other performance improving techniques. If you're dealing with a simple MyISAM table structure, your choices are either to re-develop/re-load your existing DB with InnoDB support, or look into one of the other MyISAM accelerators, like solidDB.

MyISAM is the underlying architecture of a lot of MySQL database applications that grew over time. For simplicity, MySQL is usually going to use MyISAM tables unless at table creation you specifically call for InnoDB table types, and set up your keys. One big performance problem with MyISAM is table locking, which is a big performance hit for many applications, once they get any size or popularity. Locking a table is inimical to multi-thread performance, as it puts all other threads in a spinlock, waiting on locked tables. Not bad if your application is heavily read, but death if you are doing significant update or insert.

So, solidDB is a way of throwing RAM and multi-core processors at MyISAM databases, that sort of masks the major MyISAM problems with some clever caching and the solidDB referential engine running in RAM. Essentially, if you can cram your heavily accessed or locked tables into RAM, solidDB can make them work better than if they were InnoDB tables from the outset, running on high end disk hardware.
posted by paulsc at 6:05 AM on May 11

Barc, have you read through the section of the mysql manual that deals with optimization, and changed your my.cnf settings as indicated?

First off, make sure it's utilizing the full amount of RAM. The default table memory caches are ridiculously low. If you can keep all your tables in RAM and only r/w to the disk when you need to, you're far better off. The next thing is to make sure that the thread concurrency and other settings are set properly. I can't remember what these are off the top of my head, but I know one of them needs to be set to double the processor cores you have. In your case, with a single socket quad core, this would be 8.

Things in general I'd look at with the server: You ARE running a MySQL in threaded mode and not multiple child mode, right? What does top say for the reason your load average is so high? Is everything in iowait? What's mpstat say? Are you getting a lot of interrupts? Are there any other weird messages in dmesg?

But before you look at all that -- read that chapter in the manual again, closely -- I'm 99% sure your answer lies within.
posted by SpecialK at 7:01 AM on May 11

Oh, and enable the slow query log and set it to something low like two seconds. That'll tell you what queries might need optimization via indexes or re-jiggering the join.
posted by SpecialK at 7:02 AM on May 11

IANADBA, but I have a hunch that your high load average but snappy CLIs indicates that things are in iowait, suggesting that disks may be your bottleneck. (With what little data I have, it's hard to make this determination for sure.)

As others have suggested, I'd look into the disk subsystem, starting with the simple (hdparm), and eventually looking at things like striping. Are you running SCSI/SAS? Striping across the disks? Those things might be the speed boost you need.
posted by fogster at 8:31 AM on May 11

Agree that IO may give you more improvement than CPU. If you’re handling a fair amount of write operations, I would recommend putting the Write Ahead Log (WAL) on a completely separate disk from the actual data.
posted by ijoshua at 8:52 AM on May 11

I'm no DBA, but the following tips have helped me out recently when optimizing queries:

1. Avoid UTF8 strings if at all possible. It is much faster to join on latin1 strings than UTF8. Better still is to join on integers
2. Sub queries never have indexes, so if the inner result set is large, try transforming your query to one that uses indexed temporary tables
3. conversely, too many indexes will make inserts impossibly slow
4. Disks are everything, processors don't matter nearly as much.
posted by b1tr0t at 9:36 AM on May 11

Hi all, thanks for the information. It actually seems our issue is more io throughput than CPU at this point. Since we're using crappy SATA drives but have a whack of memory on that box what I've elected to do is run the database in a ramdisk(!) and replicate to another machine as a backup strategy. Seems to work, and running the DB in RAM has cut query times by orders of magnitude.

Thanks again for the pointers!
posted by barc0001 at 10:18 PM on May 11

MySQL (or in the case of MyISAM, the OS) should be caching your database in memory if it fits there. If you're using InnoDB, have you configured its buffer cache to some suitably large fraction of available memory?

Does SHOW PROCESSLIST indicate it's just a few types of queries which are backing up? Try EXPLAIN [query] and see how it's decided to run one of them; probably it'll be a full table scan eating all your IO. If you're running 5.1.23 or above you may well be hitting a recent query optimizer bug.
posted by Freaky at 1:21 PM on May 12

Seconding Freaky, managing the ram/swap tradeoff is the OS' job, it will generally do it better than you can. That said, there are some quirks to Mysql, Linux, and swapping. Definitely make sure your mysql is using as much available memory as is feasible for the box.
posted by Skorgu at 2:33 PM on May 12