updates of sales_flat_quote periodically gets extremely slow (5000 times slower)

[Hrafnahnef]

Hello, I have an issue with CE 1.9.1.0.

During campaigns, with typical (heavy) load, the cart and checkout produces ~200 UPDATE queries to sales_flat_quote. Normally these takes ~1 ms (during heavy load). However, every 1-2 hours, magento slows down to a complete standstill.

The reason is these update queries run for 5-35 seconds instead of 1 ms. For the exact same queries. 

For most of the times, a complete reboot of the machine is the only thing that solves the issue. Restarting php-fpm or mysql doesn't work. Oddly enough, sometimes the update queries doesn't run for more than 2-7 seconds, and after 5-20 minutes the problem goes away naturally.

I'm eagerly looking for a guiding hand as to what can cause this and where to investigate. 

[Brent W Peterson]

You should first look at your server. How fast it is? Where are you hosted at? What are the resources available to MySQL

Next you should consider periodically cleaning your quote table. AOE Quote cleaner is a great little extension that will clean quotes after a certain amount of days.

[Hrafnahnef]

Thanks. I will try an automatic cleaner of the quote table. Currently I have been trying to clean the quote table manually to keep it smaller.

The server is a 32 CPU VMWare VPS, with an overkill amount of RAM available. The mysqld is configured to use 10 GB of innodb_buffer_pool (Mysql innodb data is 5.8 Gb). Also, the mysql file limit never seems to be reached or the maximum number of connections or any other value I can guess makes a difference.

The CPU is never really utilized more than 50-70 % of all available CPU power, and this drops as this freeze occurs. What I haven't been able to measure is disk I/O utilization during these freezes . Mysql itself uses a fairly small amount of cpu, during medium load 10-20 % (of a single core) and during really heavy load 150-200 % (of a single core).

[Hertzmedia2017]

Hi Hrafnahnef,

I am facing similar issue with my webstore as well and am hosted on Amazon RDS. Were you able to find a solution for this problem?

I have tried all th solutions available on the internet but yet no luck. Please guide me if you have any solution for this problem.

Thanks