High MySQL CPU Load

Hello All,

I am currently running the following:
OS X Server 10.5.5
2x3GHz Multi-Core Xserve
8GBs RAM
MySQL Server v5.0.67

One problem I am having is that when someone does a MySQL database search on a particular webpage the CPU load can get upwards to 400% render the search useless to all other clients. The MySQL CPU load is getting so high that no one can then do a search and the results never come back until the other queries are finished first.

Why won't MySQL use the 8GBs of memory I have installed instead of sucking up CPU? Is there a way to allocate this in the OS or terminal? If not, should I be optimizing MySQL settings instead to better work with my query statement and 8GBs of memory that I have installed? I'm not exactly sure what to adjust within the MySQL settings if this is the case.

Any help would be greatly appreciated!
Thank you very much!

Support all Mac machines, Mac OS X (10.5.7)

Posted on Jul 28, 2009 2:40 PM

17 replies

spraguga Author

Level 1

63 points

Aug 21, 2009 10:27 AM in response to Mabel O'Farrell

@Mable, thank you very much for all of your insight. I am learning a great deal about the MySQL environment from all of your help and it is highly appreciated!!!

This is a relief, however, I have 2 more servers that I have to implement that have the same exact hardware. Would you recommend I go with the x86 or x86_64 bit to keep them all the same? I guess if it doesn't really matter, I could just install x86_64 on the others as well if it isn't going to hurt MySQL performance.

Here is the latest output:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 45M (Tables: 64)
[--] Data in InnoDB tables: 1G (Tables: 339)
[!!] Total fragmented tables: 339

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 18h 54m 20s (73K q [0.125 qps], 1K conn, TX: 11B, RX: 4M)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 442.0M global + 12.4M per thread (151 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.3G (28% of installed RAM)
[OK] Slow queries: 0% (17/73K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 384.0M/1.3G
[OK] Key buffer hit rate: 99.8% (2M cached / 5K reads)
[!!] Query cache efficiency: 1.4% (6K cached / 434K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 3% (21 temp sorts / 658 sorts)
[!!] Temporary tables created on disk: 36% (8K on disk / 23K total)
[OK] Thread cache hit rate: 98% (13 created / 1K connections)
[OK] Table cache hit rate: 42% (512 open / 1K opened)
[OK] Open file limit used: 7% (202/2K)
[OK] Table locks acquired immediately: 100% (241K immediate / 241K locks)
[!!] InnoDB data size / buffer pool: 1.2G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp table_size/max_heap_tablesize equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query cachelimit (> 1M, or use smaller result sets)
tmp tablesize (> 16M)
max heap_tablesize (> 16M)
innodb buffer_poolsize (>= 1G)

So MySQL on 32bit arch will only utilize 2GBs of RAM?
Any recommendations on the variables to adjust would be great?
Also do you know how to optimize every databases' InnoDB tables at once? I only know how to do MyISAM tables using:
mysqlcheck -u root -p --auto-repair --check --optimize --all-database

Thanks again for everything!!! 😀

Aug 21, 2009 2:54 PM in response to spraguga

From MySQLTuner's recommendations change the settings in /etc/my.cnf for the parameters below:
querycachelimit (> 1M, or use smaller result sets)
Start the above at '1M' and adjust up until you see the 'Query cache efficiency' level off. You probably won't have to go above 2M.
tmptablesize (> 16M)
Set the above parameter to the suggested value and leave it there.
maxheap_tablesize (> 16M)
Set the above parameter to the suggested value and leave it there.
innodbbuffer_poolsize (>= 1G)
This is the toughest one to adjust because you are running on a 32-bit OS version. The max virtual memory size under OS X for a 32-bit process is 4GB, so if you get close to this in memory usage with MySQL, it's going to start throwing 'out of memory' errors and the server will slow to a crawl. try it at 1GB and see what happens. You may have to adjust down if you run into the memory issue.

The syntax for 'OPTIMIZE TABLE' is here:

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

and works on InnoDB tables as well as MyISAM tables.

So MySQL on 32bit arch will only utilize 2GBs of RAM?

It works a little differently on OS X with the 4GB virtual memory size for a single 32-bit user process -which is how 'mysqld' runs as. If it were on Windows you'd be limited to 2GB user and 2GB kernel unless you start Windows with the /3GB switch. Linux allocates 3GB user and 1GB kernel. Basically what you see here is a warning from MySQLTuner and not from MySQL. The author needs to make some changes to the code to have a little more smarts as to what system it's running on.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

High MySQL CPU Load