Date: 07/25/05 (MySQL Communtiy)    Keywords: mysql, database, sql

Hey everyone, I'm new to the community.. i'm curious if anyone has seen anything like this. i've got a RHES3 (Fedora Core 3/4 tested as well) server running running 4.1.10a.

Long story short.. mysql runs fine minus seemingly random spiked load averages caused by a pileup of locked connections due to an UPDATE query. While query runs, all additional updates are locked. Sometimes it lasts 2 minutes, sometimes it lasts 30 minutes. You'll notice my lingo is more admin oriented, that's because I'm the sysadmin and not the programmer. Our guys who create and program the site don't have much insight to resource usage.

The query looks like this: UPDATE shockwave_traffic SET minutes_vis = minutes_vis + 1 WHERE game_id = '1' AND start_hour LIKE '2005-07-24 20%';

root 19379 0.0 0.0 5860 996 ? S 20:44 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql --open-files=4096 --datadir=/usr/local/mysql/data -O max_heap_table_size=1000m --log-slow-queries --skip-bdb --skip-innodb
mysql 19423 99.9 1.9 472984 75136 ? S 20:44 108:28 \_ /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/moe.pid --skip-locking --open-files-limit=4096 --port=3306 --socket=/tmp/mysql.sock -O max_heap_table_size=1000m --log-slow-queries --skip-bdb --skip-innodb

random tidbits i've noticed while troubleshooting:

- load avg stays between 2.00 -- 3.00 which is fine, i would think if a process were truly taxing a CPU for 99% of it's resources the load would be MUCH higher. see my next tidbit.
- during peak times and not during peak times at seemingly un-andom intervals, the load will jump to 30. When this happens I check the process list and there's one UPDATE query running and theres a full connection limit of locked queries, I believe said locked queries are waiting for the update query to finish.
- table size is tiny, 400k -- there are other tables within the db that are 2.2gb that aren't accessed, purely around for archival reasons. I've ran myisamchk and there isn't any errors being reports, check table via the CLI reports no errors as well.
- nothing in the slow query log.
- binary log disabled.
- I've had this problem on databases housed on other machines while doing SELECT (using AS), but raising the max_heap_table_size was able to calm them down.

Hardware info:

Dell poweredge 2850
Raid 5 via PERC4 dual channel raid adapter
3 15K SCSI drives.
Dual 3.2ghz Intel Xeon processors (HT enabled, although this has been disabled with the same effect).


Any thoughts or insight would be greatly appreciated. :D

Source: http://www.livejournal.com/community/mysql/63772.html