MySQL Execution Time Spikes
- by Brett
I am having issues with MySQL all of the sudden today.
Details:
OS: CentOS release 5.7
Server type: Parallels virtuozzo container running on mediatemple DV 4.0 package
Average total memory usage: <500mb
Total memory usage allowed: 1gb (part of shared pool for emergency only, users are only guaranteed 500mb)
Processor: 1ghz
Main database sizes with most usage: 275mb & 107mb
server stack: nginx 1.0.10, mysql 5.1.54, php 5.3.8 with php-fpm
innodb_buffer_pool_size=100M
php-fpm max children: 5
Webapps: custom php-based sites, magento & drupal
slow query timeout is set to 1 second
Steps I completed towards diagnosis:
Cannot restart container yet - I will try later tonight when our domestic traffic has dropped
Enabled mysql and php-fpm slowlog.
Found functions that did DB queries in php-fpm slowlog were taking over 1s to complete at times
Found some simple queries in mysql slowlog taking well over 1s to complete that should take less than 1s.
Most interesting - execution time seems to spike at times. A query will take .2s a couple times, then one time it will take 8s to run the same query. These results were verified by running raw SQL queries through mysql command line.
Top does not reveal anything too interesting
Only resource related thing i can see is load averages much higher than normal
Up until today, mysql has been fine, there have been no major changes to the db since yesterday.
Sometimes things are so bad, I am seeing bad gateway errors after 60s of execution time.
Innodb is doing on average 300-1400 reads/sec.
Mysql is doing 3-10 queries/sec
slow query count in 2 hours uptime is 171 (with slow timeout at 1 second)
Tried restarting mysql, nginx, php-fpm multiple times
For example:
UPDATE `catalogsearch_query` SET `query_text` = 'EW 90', `num_results` = '7532', `popularity` = '99180', `redirect` = NULL, `synonym_for` = NULL, `store_id` = '1', `display_in_terms` = '1', `is_active` = '1', `is_processed` = '1', `updated_at` = '2012-05-08 21:38:31' WHERE (query_id='31');
This query took 17sec to complete one time, rest of the time around .079 sec. But varies, sometimes 1sec, sometimes .004 sec. This is running the same query, over and over with a couple seconds time in between each.
Most tables are innodb, and sometimes I noticed the lock time taking 90% of the query execution time, but most of the time lock time is insignificant.
Any idea what's going on here?
