I am having issues with MySQL all of the sudden today.
- 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
- 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
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?