Locks up my database

  • Resolved robbsnell

    (@robbsnell)


    4 years, 9 months ago

    On initiation this upgrade script permanently locks up the DB.
    Our postmeta table is very large.
    PHP message: WordPress database error Lock wait timeout exceeded; try restarting transaction for query DELETE FROM wp_postmeta WHERE meta_key LIKE '%wooptpm_google_analytics_4_mp_partial_refund_hit_%' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('-----/wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/woocommerce-google-adwords-conversion-tracking-tag/wgact.php'), WGACT->__construct, WGACT\Classes\Db_Upgrade->__construct, WGACT\Classes\Db_Upgrade->delete_old_partial_refund_hit_keys

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author alekv

    (@alekv)

    4 years, 9 months ago

    Hi @robbsnell

    Technically that’s an interesting error, since the SQL query is very lightweight and shouldn’t put too much load on the database.

    In any case. The query that the plugin tries to run is just a cleanup for some old settings. It is not necessary to run it.

    We have a few options.

    1. I’ll remove the script with the next version of the plugin. It’s been active long enough. And you wait until I publish the next version, probably next week.

    2. I’ll send you a beta version of the plugin with the cleanup turned off. If you want this, send me an email to [email protected]

    3. You deactivate the script yourself. Open the plugin directory. Go to the /classes/ folder. Open the class-db-upgrade.php file. Delete or comment out the 4 lines of the if statement that contains $this->delete_old_partial_refund_hit_keys(); around line 19.

    Let me know what you plan to do.

    Regards
    Aleksandar

    Thread Starter robbsnell

    (@robbsnell)

    4 years, 9 months ago

    That query is not lightweight at all, it’s actually incredibly heavy.
    Using a wildcard at the start and end of a LIKE query is probably the heaviest type of query you can do (non sargable), especially when mixed with a DELETE which locks the table.
    It would be far better to query for the results and then loop through the results to delete them with individual DB requests using their key(s).
    I’ll remove that update line myself (your 3rd option) and then await your official update.

    Plugin Author alekv

    (@alekv)

    4 years, 9 months ago

    That query is not lightweight at all, it’s actually incredibly heavy.
    Using a wildcard at the start and end of a LIKE query is probably the heaviest type of query you can do (non sargable), especially when mixed with a DELETE which locks the table.

    You’re probably right. I’m not an expert (yet) in creating SQL queries and I see your point with the LIKE. I guess if I could use an exact match it would be much faster.

    It would be far better to query for the results and then loop through the results to delete them with individual DB requests using their key(s).

    Isn’t that the same as what I do, but just in a different order? I guess the time intense part is the match agains the LIKE part, which means the db has to go through each record and run some kind of regex. That part doesn’t change in your proposed solution. Don’t worry if you don’t have time to answer. I’m just curious and would like to learn how to improve this and detect such issues before they happen.

    And, I’ll close the ticket for now. If anything keeps not working as it should just add another reply to this thread.

    • This reply was modified 4 years, 9 months ago by alekv.
    Thread Starter robbsnell

    (@robbsnell)

    4 years, 9 months ago

    Definitely doing an exact match or even a prefix match (with the wildcard only at the end) would be faster. If your database is under a decent amount of load then the trick is to ensure that your queries are very fast and that they don’t lock tables for any milliseconds longer than they have to.

    This is exacerbated in WordPress because pretty much every request hits the postmeta table many times. Your query locks that table for a long time so any other updates are literally blocked until your very long query stops. FYI my postmeta table has 14 million records and our site has good traffic.

    In terms of performance, it’s a lot better to lock the postmeta table many times for a few milliseconds than to lock it once for a lot of seconds. Note that doing a SELECT doesn’t lock the table. You can do your SELECT to find the records that need to be deleted, then in PHP you can loop through those results and fire off individual DELETE queries for each. This is much better (particularly in a multi-threaded environment) because the DB can choose which queries to run as a priority. It might handle 5 or 10 delete requests, then handle some other critical queries, then it can come back and handle the remaining delete requests etc. Ultimately this makes the database more robust and it would perform far better in an enterprise system.

    Plugin Author alekv

    (@alekv)

    4 years, 9 months ago

    Note that doing a SELECT doesn’t lock the table. You can do your SELECT to find the records that need to be deleted, then in PHP you can loop through those results and fire off individual DELETE queries for each.

    Aah. Using non locking SELECT, that’s a key ingredient! I get it now.

    And thanks for the detailed explanation!

    Improving every day 🙂

    Plugin Author alekv

    (@alekv)

    4 years, 8 months ago

    I released the new version that doesn’t contain the query anymore.

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

The topic ‘Locks up my database’ is closed to new replies.