Table locks when using multisite and simultaneous cron

Resolved demon_ru
(@demon_ru)

5 years, 9 months ago

I use Multisite and call cron procedures by external (in relation to WordPress) scripts.
Since cron is a table independent for each blog, I implemented a cron call for several sites at the same time. And at night it led to the allocation of all memory and stop all sites.

Reason: every site runs cerber_do_daily. As the table plugin doesn’t depend on the blog, they are usually just multiple (useless) cleaned up and optimized.
However, when you call cron for multiple sites at the same time, the tables are locked and waiting. All accesses to sites also go into waiting for the lock to be released (for writing to tables).

The simplest solution is to call these actions only for one site on the entire network.

So far I’ve just commented out this code (call OPTIMIZE). I hope for a speedy and correct solution of the problem.

This may be related: https://wordpress.org/support/topic/heavy-database-load/

Viewing 10 replies - 1 through 10 (of 10 total)

Plugin Author gioni
(@gioni)

5 years, 9 months ago

Hi! That’s correct. In case of a multisite with numerous websites, the daily cron task can cause heavy database load. Handling daily tasks for such cases will be improved in the next release.

Plugin Author gioni
(@gioni)

5 years, 9 months ago

A new version with protection from invoking multiple simultaneous cron tasks: https://my.wpcerber.com/downloads/wp-cerber.7.9.6.zip

A new release will be uploaded on wordpress.org in a week.

Thread Starter demon_ru
(@demon_ru)

5 years, 9 months ago

I was waiting for the release of the plugin to update it on production. Version 7.9.7 was released today.
After looking at the code, I didn’t notice any changes that were made to prevent the problem from happening again.
Please tell me what edit in the code (you can link to the revision in SVN) should solve this problem.
Thanks.

Plugin Author gioni
(@gioni)

5 years, 9 months ago

You don’t need to look at the code. Just install the latest version.

Plugin Author gioni
(@gioni)

5 years, 9 months ago

How’s it going?
Thread Starter demon_ru
(@demon_ru)

5 years, 8 months ago
It’ ok.
Thank you.
- This reply was modified 5 years, 8 months ago by demon_ru.
Oleksiy
(@evinak)

5 years, 4 months ago

We’ve just got the same lock with the latest plugin version. Hosted under AWS Aurora (MySQL) and Beanstalk Multi-site version with HyperDB enabled.

|
| 877151 | dbuser | xxx.xxx.xxx.xxx:33466 | dbname | Query | 60542 | Waiting for table level lock | OPTIMIZE TABLE cerber_log |
| 877152 | dbuser | xxx.xxx.xxx.xxx:33468 | dbname | Query | 60542 | Waiting for table metadata lock | OPTIMIZE TABLE cerber_log |
| 877492 | dbuser | xxx.xxx.xxx.xxx:39848 | dbname | Query | 59013 | Waiting for table metadata lock | INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta |
| 877494 | dbuser | xxx.xxx.xxx.xxx:36054 | dbname | Query | 58983 | Waiting for table metadata lock | INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta |
…

Plugin Author gioni
(@gioni)

5 years, 4 months ago

@evinak Make sure that the tables are set to use the InnoDB engine.
Oleksiy
(@evinak)

5 years, 4 months ago
mysql> SHOW TABLE STATUS WHERE Name = ‘cerber_log’;
```
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation       | Checksum | Create_options | Comment             |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------------+
| cerber_log | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                | Cerber activity log |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------------+
1 row in set (0.00 sec)
```
Plugin Author gioni
(@gioni)

5 years, 4 months ago

@evinak Try setting innodb_table_locks=0 in MySQL configuration