Support » Plugin: WP Statistics » 13.0.2 Crashed 20 core DB server

  • Resolved erlendeide

    (@erlendeide)


    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 TABLE wp_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.

    • This topic was modified 1 month, 4 weeks ago by erlendeide.
    • This topic was modified 1 month, 4 weeks ago by erlendeide.
    • This topic was modified 1 month, 4 weeks ago by erlendeide.
Viewing 11 replies - 1 through 11 (of 11 total)
  • Took us down too on a fairly big VPS with only one site. Another site sharing a less powerful VPS did recover after going down for some time. Maybe it’s an initial db process after update.

    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 reply was modified 1 month, 4 weeks ago by erlendeide.
    • This reply was modified 1 month, 4 weeks ago by erlendeide.

    I have experienced exactly the same thing, causing my client’s website to timeout with a 503 error. Monitoring the currently active processes, I can see HTTP queries queuing waiting on the database locks to free up.

    I have disabled WP Statistics for now as it renders the website useless.

    Plugin Contributor veronalabs

    (@veronalabs)

    Hi,

    Thank you for using our plugin and sorry for this error.

    We are working hard to fix it and run this optimization in the background.

    Again, sorry for any inconvenience this may have caused.
    Thanks for your patience and understanding.

    Best,

    Hi

    Please do not run this kind of optimization in the background, you will not succeed at making this *not crash* any servers. You should remove the code, and replace it with a help page that allows people to do this manually if they want to, but it should not be part of anything that happens automatically in your plugin.

    I agree with @erlendeide; a plugin should never run this kind of database optimisation. Not in the foreground. Not in the background. Not ever.

    If there is a valid reason to perform such a database optimisation – perhaps because of data structure changes etc. – then as developers you should let your users know the reasons for doing the optimisation and what exactly they should do post the upgrade. The rest should be up to them to decide if and when they wish to follow your advice.

    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 | 
    +-------+---------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
    • This reply was modified 1 month, 4 weeks ago by iwcro.

    @aberbenni what is this exactly doing?

    • Disabled some repair and optimization table queries during the initial request.

    https://github.com/wp-statistics/wp-statistics/releases/tag/13.0.3

    Plugin Contributor veronalabs

    (@veronalabs)

    Hi everyone,

    Sorry for any inconvenience our latest update may have caused.

    We fixed the issues and released the new version.
    Please update the plugin to v13.0.3 and let us know the result.

    Best,

Viewing 11 replies - 1 through 11 (of 11 total)
  • You must be logged in to reply to this topic.