13.0.2 Crashed 20 core DB server
-
Customer updated the plugin 20 minutes ago to 13.0.2. 2.3 million entries in wp_statistics_visitor table. This query was enqueued 20 times – blocking eachother and effectively killing the database server.
# User@Host: dbname[dbname] @ localhost []
# Thread_id: 86507332 Schema: relrules QC_hit: No
# Query_time: 290.705604 Lock_time: 196.087331 Rows_sent: 0 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1606660741;
OPTIMIZE TABLEwp_statistics_visitor;Optimize table is unsafe to run from a plugin. That is a task that should be monitored while running by a human being so it can be terminated in case it blocks too many requests.
It seems optimizing tables is happening all over the place – file:line number:
grep -nR optimizeTable * includes/class-wp-statistics-visitor.php:55: DB::optimizeTable(DB::table('visitor')); includes/class-wp-statistics-visitor.php:183: DB::optimizeTable(DB::table('visitor_relationships')); includes/admin/class-wp-statistics-admin-purge.php:37: DB::optimizeTable(DB::table('historical')); includes/admin/class-wp-statistics-admin-purge.php:69: DB::optimizeTable(DB::table('historical')); includes/admin/class-wp-statistics-admin-purge.php:147: DB::optimizeTable(DB::table('historical')); includes/class-wp-statistics-user-online.php:171: DB::optimizeTable(DB::table('useronline')); includes/class-wp-statistics-exclusion.php:102: DB::optimizeTable(DB::table('exclusions')); includes/class-wp-statistics-db.php:179: public static function optimizeTable($table_name) includes/class-wp-statistics-schedule.php:153: DB::optimizeTable(DB::table('visit')); includes/class-wp-statistics-schedule.php:233: DB::optimizeTable($table_name); includes/class-wp-statistics-pages.php:274: DB::optimizeTable(DB::table('pages')); includes/class-wp-statistics-search-engine.php:395: DB::optimizeTable(DB::table('search'));Technically, the db server ran fine, but the webserver ran out of processes due to many open processes waiting for the optimize table or the table lock it caused.
-
It’s not just an initial thing.
What OPTIMIZE TABLE does is to rebuild a table and it’s indexes from scratch when it is InnoDB. In the mean time, traffic is blocked. This is under no circumstances something that a plugin should trigger (or care about).
ANALYZE TABLE will recalculate index statistics, which usually has the same result – except from the storage that OPTIMIZE TABLE may free up. Performance-wise ANALYZE usually has the same effect as OPTIMIZE.
ANALYZE or OPTIMIZE are not tasks you’d like to run often, once a week should be considered often, while once every other month would be more than enough. We might do this as seldom as once per year for many of our own installations.
I assume this plugin also writes to it’s tables on every page load to collect the statistics. Any INSERT query will be queued/paused until OPTIMIZE is finished. In the mean the mean time, the plugin triggers both more OPTIMIZE queries and more INSERTS if traffic comes in.
It’s almost guaranteed to take down any site with some traffic.
This plugin crashed one of our servers as well, after the Plesk-Panel auto-updated this plugin on several sites yesterday.
I am also not sure if this new database optimization feature really just runs once per day, cause we saw the optimize table query for the same sites multiple times in the last couple hours.
Moreover it seems to be able to trigger this routine multiple times at once, making it way worse due to mutual table locking…show processlist; +-------+---------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+---------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 26258 | wp_user | localhost | wp_hzd7f | Query | 117 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26271 | wp_user | localhost | wp_hzd7f | Query | 116 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26284 | wp_user | localhost | wp_hzd7f | Query | 115 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26292 | wp_user | localhost | wp_hzd7f | Query | 115 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26298 | wp_user | localhost | wp_hzd7f | Query | 115 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26306 | wp_user | localhost | wp_hzd7f | Query | 114 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26312 | wp_user | localhost | wp_hzd7f | Query | 114 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26322 | wp_user | localhost | wp_hzd7f | Query | 114 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26329 | wp_user | localhost | wp_hzd7f | Query | 107 | Waiting for table level lock | INSERT IGNORE INTO <code>cePJ9J6pH_statistics_visitor</code> (<code>last_counter</code>, <code>referred</code>, <code>agent</code>, <code>platform</code>, | | | 26333 | wp_user | localhost | wp_hzd7f | Query | 113 | Waiting for table metadata lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26343 | wp_user | localhost | wp_hzd7f | Query | 113 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26362 | wp_user | localhost | wp_hzd7f | Query | 111 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26380 | wp_user | localhost | wp_hzd7f | Query | 110 | Waiting for table level lock | OPTIMIZE TABLE <code>cePJ9J6pH_statistics_visitor</code> | | | 26610 | wp_user | localhost | wp_hzd7f | Query | 87 | Waiting for table metadata lock | SELECT <code>location</code> FROM cePJ9J6pH_statistics_visitor WHERE <code>ip</code> = '' and <code>last_counter</code> >= '2019-12-0 | | | 26648 | wp_user | localhost | wp_hzd7f | Query | 83 | Waiting for table metadata lock | SELECT <code>location</code> FROM cePJ9J6pH_statistics_visitor WHERE <code>ip</code> = '' and <code>last_counter</code> >= '2019-12-0 | | | 27003 | wp_user | localhost | wp_hzd7f | Query | 55 | Waiting for table metadata lock | SELECT <code>location</code> FROM cePJ9J6pH_statistics_visitor WHERE <code>ip</code> = '' and <code>last_counter</code> >= '2019-12-0 | +-------+---------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
The topic ‘13.0.2 Crashed 20 core DB server’ is closed to new replies.
