MySQL crashing on AWS | SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

[Shirish V]

Hi,

I am running a Magento community ( I have downgraded it from Enterprise ) with 6 multisites having around 100000 products. The web store runs fine but some times it suddenly crashes throwing the same error : SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;

I tried to view the full processlist but can see only Magento generated sql queries in the list so terminating them each time is a temporary fix.

I even tried to raise innodb_lock_wait_timeout to 120 and even above but it didn't help. My MySQL keeps crashing intermittently showing higher level CPU utilisation.

I am running this Magento on Amazon EC2 large instance. I need to know if there is any permanent solution to this? Can up scaling in this case would help resolve the problem? Or can switching to Amazon RDS or using load balancer would help please advise. I really need some permanent solution ASAP.

PS: I do not have any customizations on Magento and already have category flat catalog and product flat catalog enabled. I feel the large number of products with multisites is the problem so need to know if increasing infrastructure would help me to permanently resolve the locking issue or if there is any other permanent solution other than tweaking MySQL default configuration values.

Thanks,
Shirish

[JLHC]

How's the overall CPU and RAM utilization rate? 

Anyway I believe the issue can be easily resolved if your MySQL configurations are properly tweaked and optimized. Is there any reason on why you want to avoid doing this? 

[Shirish V]

When the lockout occurs the CPU utilisation goes above 50%, not observed the RAM but it must also be going high.

Regarding not tweaking MySQL defaults, I am using Enterprise plan from this for hosting https://www.mgt-commerce.com/magento-aws-managed-plans-and-pricing.html according to them they already have set configurations to optimum defaults and tweaking the configuration would cause reduced speed performance.

Can you advise what could be the MySQL configuration parameters for best performance? And also can you comment on if up scaling would help me if I don't want to tweak the MySQL configuration?

Thnaks

[JLHC]

Since they are managing your server, did you ask them to look into the problem for you? 

There's no "fixed" MySQL configuration as it depends on your usages. 

Scaling may or may not resolve the issue, depending on what is causing the issue in the first place. 

[Shirish V]

Yes, I did ask them to look into this and they just asked to look into process list and kill bad queries ( which is just a temporary fix and bad queries they are refering to are actually Magento generated queries with joins to multiple tables which we can't avoid )

They confirmed the MySQL is fine tuned and they are serving other people since long with very good performance so can't doubt on it.

The main problem I feel is large number of products with multisites configuration is causing to generate complex queries with heavy joins so even raising MySQL defaults wouldn't help. I believe we need high processing power to handle these large queries so I am trying to get opinion on up scaling.

i am mainly trying to figure out if community version is really capable of handling 100000+ products with multisites configuration or we are lacking on infrastructure.

[JLHC]

To be frank I highly doubt that your MySQL is optimized enough as having a default configuration which is the same across all servers may not cut it. 

Without looking at your backend, my general suggestion is to try moving to faster storage drives and replace MySQL with a faster alternative like Percona. 

Magento Community should have no problems in dealing with your configurations if the infrastructure is ready to handle it. 

[Shirish V]

Thank you for suggestion on Percona but at this stage it wouldn't be possible to make this switch.

Regarding MySql optimisation, I'll try to get values of defaults set for MySQL ASAP so that we can confirm if it's fine tuned. Is it possible for you to mention mainly which parameters I should check for?

Thanks

[JLHC]

That's not how it works though. A system administrator will need to monitor your server, go through the logs, etc and tweak your configurations. Many users will give you a set of one-size-fit-all my.cnf config but those are just guidelines only. 

[Shirish V]

Thanks for your support, I am already monitoring server logs and trying to find out which events causing to generate heavy queries so that we can suppress them. I'll try to find generic values for optimized MySQL and fine tune it.

Let's see if anybody else have any suggestion on upscaling or any permanent solution to get around this locking issue.

[robfico]

Take a look at the bad queries that get hung up, and run an "EXPLAIN" on them to see if it's an indexing issue or not. That is one place to start. Also get these queries from the slow query log.

Is the InnoDB memory setting high enough to contain all your data and indexes (innodb_buffer_pool_size)?

Are you creating a lot of on disk temporary tables? If so, try adjusting tmp_table_size and max_heap_table_size to accommodate these tables. Also consider a RAM drive mounted to have your MySQL temp tables load in RAM instead of on-disk.

Do you have a lot of unindexed JOINs? If so, you can up the join_buffer_size to try and compensate for bad queries. Tools such as mysqltuner.pl and tuning-primer.sh can shed some light on problem areas in MySQL. Don't follow their recommendations blindly, but take into account the results.

What does I/O load look like? sar output over time to see if I/O is the issue.

Optimized MySQL is more than just a generic set of settings. It needs to be customized and tuned for each Magento store, especially ones as large as yours.