mysql update of sales_flat_quote suddenly gets very slow

Question

I have a problem with a magento 1.9 installation behind a varnish cache. The load average is rougly 500 request/minute (including ajax requests and esi callbacks).

Normally, I get ~200-250 UPDATE queries per minute for sales_flat_quote. Under normal circumstances, these only takes about 1 ms to finish. However, I have a problem where all of these updates at times takes 5-30 seconds to finish.

During these spikes, all page loads grinds to a halt, but the overall system load is lowered (from ~20 to ~12), as is cpu usage. Mysql never uses more than 70-80 % of a single CPU during these spikes (with 32 CPU cores available), and there are plenty of available RAM, with innodb_buffer_pool = 10G but only 6G used. The number of UPDATE queries are pretty much unaffected. In fact, they tend to be slightly lower at ~200 queries/second.

Restarting mysql or php-fpm doesn't solve this problem. The only way I am able to solve it is by rebooting the entire server, which is not an acceptable solution.

Furthermore, the sales_flat_quote table is routinely truncated and only contains ~ 5000 rows.

The source of these updates all comes from the cart and the checkout.

Answer 1

The only way I am able to solve it is by rebooting the entire server

Fire your hosting provider, immediately. The only time a server should be powered off is to physically upgrade it, or install a new kernel - never to solve a problem. If they can't provide a solution to such a simple issue, they aren't a company worth using.

---

Your issue is fairly clear, you are lacking instrumentation.

You should be graphing every single application and event on your server in initimate detail, so that you can correlate the sequence of events leading up to a slow query incident to identify a cause.

If everything performs normally, then suddenly slows down, I'd be inclined to review,

• Varnish hit rate - does it plummet at the time of the slowdown?
• Inbound traffic rate - do you see an increase in traffic, do you see an increase from a single IP?
• Cache store utilisation - has someone flushed a cache, or have several entries expired simultaneously?
• Hardware activity - what else is happening on the server at the time, is I/O high, SYS CPU etc. Is it cloud/VPS - if so, what is the hypervisor doing?

There could be dozens of possible causes, all of which should take seconds to identify and fix, but without instrumentation - you can't simply guess your way through a fix. You'll just cause further problems blindly making adjustments.

Answer 2

First of all make sure the sales_flat_quote table is cleaned up regularly. You can use the Aoe_QuoteCleaner module which can be downloaded from Github. (which you're already doing but added it anyway)

Another thing to do is to make sure your MySQL settings are optimized for Magento.

Here are some settings that should work quite well

innodb_thread_concurrency = 2 * [numberofCPUs] + 2
innodb_flush_log_at_trx_commit = 2
thread_concurrency = [number of CPUs] * 3
thread_cache_size = 32
table_cache = 1024
query_cache_size = 64M
query_cache_limit = 2M
join_buffer_size = 8M
tmp_table_size = 256M
key_buffer = 32M
innodb_autoextend_increment=512
max_allowed_packet = 16M
max_heap_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1

Also try the MySQL Tuner for Magento that should do a lot of the work for you.

For Magento we use Percona MySQL on our shops. You can ask your hosting provider if they can help you setting that up.

More on tuning the hosting environment can be found on this blogpost: https://www.mgt-commerce.com/blog/magento-on-steroids-best-practice-for-highest-performance/

Also, please check the MySQL Slow Query log for the queries that take the most time.

Answer 3

I give an idea.

• First take backup of this table and truncate that table.Also take Backup of whose data if required
• And after that check data save in 1 ms or 5-30S.
• If it will take 5-3 second then must be issue in your codes.
• May be some custom code Or There are wrong code which is creating the exceptions.
• If there exception error then we will need to debug the system using Fundamentals for debugging a Magento store
• Also there may be some logical issue on your code which are take too much time to execution.
• Basically you need to debug Mage_Checkout,Mage_Sales,Mage_Catalog,CatalogInventory and it's relatedall 3-rd party extensions

Note: it is my thought..

Answer 4

Surely, the issue is in code. It could happen when some module having observer on cart actions doing something inappropriately. This is just a guess but this can interrupt the standard flow with unnecessary execution.

Enable mysql queries log from file lib/Varien/Db/Adapter/Pdo/Mysql.php

protected $_debug = false; //make this true
protected $_logAllQueries = false; // make this true

You will get sql log in var folder. Find the query and try to execute it separately. Also check if query is not having unnecessary associations or data. If the query is fine and still taking time longer than expected.
Then quickly switch your hosting or work on your query, find the module which is making it slower. Share the query as well for better understanding with the problem.