High MySQL CPU Load

@Camelot,
I have not configured anything, everything is default install. I have about 6 devs on this box at all times and we do not have a MySQL admin. I really need to fine tune this setup as the DBs are starting to grow very big. I am looking for any help at this point and want to start fixing this now before it becomes more of a problem. I am going to start researching the MySQL dev zone ASAP.

@Mabel
Here is the output of MySQLtuner:
*Maybe I need Snow Leopard to utilize all 8GBs of RAM??*
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-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: 5M (Tables: 16)
[--] Data in InnoDB tables: 138M (Tables: 51)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 99d 16h 39m 42s (53K q [0.006 qps], 1K conn, TX: 17B, RX: 2M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 442.0M global + 12.4M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.6G (41% of installed RAM)
[OK] Slow queries: 0% (0/53K)
[OK] Highest usage of available connections: 8% (8/100)
[OK] Key buffer size / total MyISAM indexes: 384.0M/123.9M
[OK] Key buffer hit rate: 99.8% (10K cached / 18 reads)
[!!] Query cache efficiency: 0.8% (52 cached / 6K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 11% (5 temp sorts / 44 sorts)
[!!] Temporary tables created on disk: 38% (11K on disk / 29K total)
[OK] Thread cache hit rate: 99% (8 created / 1K connections)
[OK] Table cache hit rate: 91% (76 open / 83 opened)
[OK] Open file limit used: 7% (86/1K)
[OK] Table locks acquired immediately: 100% (10K immediate / 10K locks)
[!!] InnoDB data size / buffer pool: 138.2M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
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)
sort buffersize (> 2M)
read rnd_buffersize (> 8M)
tmp tablesize (> 32M)
max heap_tablesize (> 16M)
innodb buffer_poolsize (>= 138M)

Should I just go with these adjustments for now or do you guys recommend anything else?
Thank you very very much guys!!!

Message was edited by: spraguga

The previous output was on my test server which I thought would be the same as my production server, obviously not though. Here is the productions server's output:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-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: 79M (Tables: 42)
[--] Data in InnoDB tables: 128M (Tables: 9)
[!!] Total fragmented tables: 10

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 22m 48s (388K q [4.257 qps], 69K conn, TX: 3B, RX: 22M)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 442.0M global + 12.4M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.6G (20% of installed RAM)
[OK] Slow queries: 0% (98/388K)
[OK] Highest usage of available connections: 22% (22/100)
[OK] Key buffer size / total MyISAM indexes: 384.0M/47.8M
[OK] Key buffer hit rate: 100.0% (13M cached / 176 reads)
[OK] Query cache efficiency: 76.2% (67K cached / 89K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (13 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 2% (2K on disk / 80K total)
[OK] Thread cache hit rate: 99% (51 created / 69K connections)
[OK] Table cache hit rate: 94% (124 open / 131 opened)
[OK] Open file limit used: 12% (146/1K)
[OK] Table locks acquired immediately: 99% (30K immediate / 30K locks)
[!!] InnoDB data size / buffer pool: 128.2M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
innodb buffer_poolsize (>= 128M)

{quote:title=Mabel O'Farrell wrote:}
What hardware are you running on; Intel or G5? What OS version? The output says that you're running a 32 bit OS -seems strange.{quote}
Running 2x3Ghz Quad-Core Intel Xeon w/OS X S 10.5.5 and 8GBs RAM

{quote:title=Mabel O'Farrell wrote:}Is there a reason that you are using InnoDB tables and MyISAM tables at the same time; possibly phpMyAdmin?{quote}
I believe a dev changed one of DBs to InnoDB for a backup script having to do with table locking. This is a pretty major database not sure how easy it is to change it back to MyISAM.

{quote:title=Mabel O'Farrell wrote:}Change this to the recommendation or eliminate the use of InnoDB tables. If you can't get rid of the InnoDB tables, upgrade to a 64 bit 5.1 release. The OS should be 64 bit by default, but I have seen reports that early versions of Leopard server weren't fully 64 bit with the original disks shipped -no cite on this because I can't find it anymore.{quote}
I am very hesitant to upgrade MySQL to 5.1. I guess I could upgrade the OS though to 10.5.7? I am not very familiar with the different types of DBs, why do you recommend MyISAM over InnoDB?

Thank you for everything!

Message was edited by: spraguga

Might I also mention that this InnoDB is the DB that is overloading the CPU when a search is done using the web app.

Thanks again!

On the test server I adjusted everything that MySQLtuner recommended and the CPU load is still the same.

{quote}The bug report I linked indicates that there is a known problem with 5.0.67 and InnoDB tables on OS X. Upgrading the OS won't install a newer version of MySQL. So the only way to get past the InnoDB tables bug is to stop using them -which apparently you can't- or upgrade to a newer community release. You did mention that you have a test server, maybe you could test the upgrade and see if it gets past the 32/64-bit problem and fixes the InnoDB tables bug (probably does).{quote}

The bug report you mentioned is for 10.4, I am running 10.5.5, or are you talking about just in general with MySQL 5.0.67 running InnoDB?

I remember upgrading MySQL on OS X Server 10.4 and the MySQLStartupItem never worked correctly and I would always have 2 instances running at the same time and then I would have to kill the mysql process and restart it in the terminal every time I restarted the server.
Hopefully this doesn't happen anymore and MySQL 5.1 will startup automatically on server startup, can anyone confirm??
I assume I just leave the MySQL service off in Server Admin and only use the MySQLStartupItem that gets installed into system preferences??
Are there any adjustments that I will need to make for everything to continue to work?

Obviously you can tell I'm a little nervous about upgrading this since my last experience on OS X S 10.4 was not that great!

@Mabel,

Thank you very much for all of your help!!! I'll see if I can't get everything working smoothly and report back.

Thanks again! 😉

OK I upgraded to MySQL 5.1.37 on a clean OS X S 10.5.8 build and MySQLtuner is still saying:
!! Switch to 64-bit OS - MySQL cannot currently use all of your RAM

Also I am still experiencing high CPU load with the mysqld process.

Any help would be great!
Thanks!

God Mabel you are just an encyclopedia of information! I wish I knew this command in the past, I've been looking for something like this for a while. I guess I am not running a 64bit system. I thought the 2x3 Dual-Core Intel Xeon servers where, guess not!
| | "firmware-abi" = <EFI32>

This is not good, because I installed the 64 bit version of MySQL!!! Is this bad? 😟