Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
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. –  fsb Oct 20 '11 at 23:21
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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). –  fsb 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 –  fsb Oct 20 '11 at 23:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.