2

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:

  1. Cannot restart container yet - I will try later tonight when our domestic traffic has dropped
  2. Enabled mysql and php-fpm slowlog.
  3. Found functions that did DB queries in php-fpm slowlog were taking over 1s to complete at times
  4. Found some simple queries in mysql slowlog taking well over 1s to complete that should take less than 1s.
  5. 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.
  6. Top does not reveal anything too interesting
  7. Only resource related thing i can see is load averages much higher than normal
  8. Up until today, mysql has been fine, there have been no major changes to the db since yesterday.
  9. Sometimes things are so bad, I am seeing bad gateway errors after 60s of execution time.
  10. Innodb is doing on average 300-1400 reads/sec.
  11. Mysql is doing 3-10 queries/sec
  12. slow query count in 2 hours uptime is 171 (with slow timeout at 1 second)
  13. 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?

2
  • 1
    What does an EXPLAIN tell you about the query? What is the CPU load during that query? Does other IO act extremly slow during that query execution? May 8, 2012 at 22:25
  • @Brett: Could you resolve it? I have the exactly same issue
    – Ut xD
    Sep 18, 2014 at 18:01

3 Answers 3

1

Sounds almost definitely like a peripheral issue influencing what is going on and fairly typical of running Magento in a constrained and contended environment like a VPS.

If your configuration was previously working well and you haven't changed any code or installed any extensions - then it will be an external influence, most likely an increase in traffic - but with a VPS, it is more than likely increased I/O activity.

It sounds exactly like I/O wait - simply installing a graphing tool like Munin would conclusively prove this (showing a correlating in MySQL slow queries with I/O activity/wait).

I'll refer to a few other posts on the same vain - as running Magento in a VPS always yields similar behaviour.

  1. https://serverfault.com/a/368649/113375
  2. https://serverfault.com/a/367861/113375
  3. https://stackoverflow.com/a/8216096
0

The 2 most likely issues are that:

1) your query is being blocked by another one - check your slow query log for what was running when you kicked off the update

2) your VM is not getting scheduled by the host machine. Set up a watchdog script to write a heartbeat to a log file

There may be other causes, but unless you can eliminate the 2 above, there's not much point discussing them.

In terms of resolving the issues, the former is just a matter of query tuning, for the latter, you'll need to speak to whoever manages the host machine.

0

What does :

SHOW CREATE TABLE catalogsearch_query\G
SHOW INNODB STATUS;
SELECT * FROM information_schema.PROCESSLIST WHERE State='Locked' ORDER BY Time;

look like ?

I would suspect you are getting hit by the hanging 'opening tables/closing tables' problem. I would not restart your DB too much, it will need time to warm up, especially when doing lots of updates.

To make it complete, I would love to see this too :

SHOW STATUS LIKE '%cache%';
SHOW STATUS LIKE '%table%';

And all this at a moment of trouble, I will bet you that when you are in trouble and do a "flush tables;" , it will take ages.

A few important configuration parameters that will speed up processing:

innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files = 16000
innodb_io_capacity   = 400
innodb_flush_log_at_trx_commit = 2
innodb_flush_method  = O_DIRECT
innodb_thread_concurrency=8
innodb_lock_wait_timeout = 90

Mind you that this comes from a MariaDB thus mysql on steroids.(XtraDB) Especially, the per_table setting is drastic, but will only take affect new tables, you'll need to re-create problem tables if you want them in their own innodb. That along with O_DIRECT flush method and trx_commit=2 (read about this one first!) will speed things up severely, especially with large update/inserts.

Hope this helps.

1
  • the file_per_table requires dumping, dropping and re-creating the entire database - it doesn't just immediately take effect for existing databases. Also, setting the open_file_limit so high would almost undoubtedly cause his VPS to fall over. Or not even let MySQL start if he hasn't increased his ulimit settings accordingly. Jun 20, 2012 at 21:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .