Mysql – Terrible thesqld performance after system upgrade

lionmac-osxMySQLperformance

I recently replaced my old Core2duo Mac with 4GB and OS X 10.6.8 with a 4-core i5 with 16GB, an SSD and 10.7.2.

The new system is wonderfully fast for everything except mysqld. It's OK with light loads but when I give it some real work to do, it behaves in a very peculiar way.

I've tried mysql-5.1.59-osx10.6-x86_64 and mysql-5.5.16-osx10.6-x86_64 with similar results.

The workload is a batch file that takes a large database we get from a supplier and manipulates the data into a form we can use in our app on the production servers. On the old Mac it took 3 to 4 hours to run.

On the new one it runs fast for a while and then bogs down, after which each query runs amazingly slowly with trivial IO and the CPU pegged at 100%, on one core (the batch file runs only one query at a time through the regular mysql cli client). Other apps also slow down badly and use a lot of CPU even though, according to top, there's 10GB inactive memory.

For example, right now mysqld is running 

select t.t_id, a.a_id from t inner join a on a.x=t.x into outfile;

This query has been running for about an hour while it took only 4 minutes on the old system. It's using 100% cpu on one core and it writes a 1MB block to the outfile every minute or so. Using iosnoop I can't see any reads from either table a or t (both MyISAM) so I guess they are cached in VM. The relevant index was preloaded in the MyISAM key buffer. So there's definitely no IO bottleneck. And yet this new system is much slower (~20x) than the old one.

I've got the 5.5 performance schema but I don't understand it. And I have dtrace but I'm not really competent to drive it other than using the given utilities like iosnoop.

What could possibly be going on? What could I do to uncover relevant info?

EDIT: This is all my.cnf has for mysqld—defaults for everything else.

[mysqld]
datadir = /Users/fsb/mysql
port = 3306
socket = /tmp/mysql.sock
key_buffer_size = 1536M
performance_schema = ON

Best Answer

Your observation is not at all surprising.

If there is one thing I have learned over the years as a MySQL DBA is this: MySQL is only as performance-enhanced as you configure it. I can say this with absolute certainty because I have installed PostgreSQL and MySQL and have seen PostgreSQL perform much better "out-of-the-box" than MySQL "out-of-the-box".

To prove this point outside of my own opinion,

Percona recently performed a "Battle Royale" among multiple releases of MySQL

  • MySQL 4.1
  • MySQL 5.0
  • MySQL 5.1 (with built-in InnoDB)
  • MySQL 5.1 with InnoDB-plugin
  • MySQL 5.5
  • MySQL 5.6

All tests were performed with MySQL unconfigured. The results?

  • MySQL 4.1 performs the best in a single-threaded environment
  • MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

What do I get out of this? Your must configure MySQL 5.5/5.6 to get multicore enhancements engaged.

Given the my.cnf you are using, MySQL is still running, for all intents and purpose, "out-of-the-box".

Please execute the necessary due diligence (query tuning, desired configuration, DB infrastructure, Proper Storage Engine Selection and Tuning) to get MySQL up-to-snuff to perform better for you.

Related Topic