1

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
  • Can you post your MYSQL configuration? Particularly settings like `table_open_cache`, `thread_concurrency`, whether you're using InnoDB or MyISAM, and the performance parameters for your chosen storage method. (key_buffer_size/sort_buffer_size or innodb_buffer_pool_size/innodb_log_file_size, and so on) – David Schwartz Oct 20 '11 at 23:05
  • I'm using MyISAM. /etc/my.cnf is almost empty; the only setting of interest is key_buffer_size=1500MB, which is big enough for all the queries. Apart from that everything is at its default. –  Oct 20 '11 at 23:21

2 Answers2

1

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.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
0

Do you have a sufficient amount of swap space?

Do you have a sufficient amount of /tmp and /var/tmp space?

I am just throwing out a wild guess, but what about tmp space for the MySQL database.

mdpc
  • 11,698
  • 28
  • 51
  • 65
  • Wild guesses is where I'm at with this too:-( There's lots of swap space and space for temp tables. The system is new and has mostly empty storage devices. I don't think swap can be a problem as the system has lots more memory than is required for these queries--10 of the 16GB remains inactive throughout (although I think the OS uses a lot of that for FS cache). –  Oct 20 '11 at 23:26
  • What about the compatibility of the port configurations between the system and the switch. Are they both 1G/Full Duplex? Are you seeing any network errors which would show up via ipconfig -a? or netstat -i?And along this lines, the actual ethernet cable, is it ok? – mdpc Oct 20 '11 at 23:35
  • The network isn't involved. The client is on the same machine connecting via socket –  Oct 20 '11 at 23:52