Support » Plugin: Optimize Database after Deleting Revisions » Possible to optimize odb_get_tables() usage?

  • Resolved chief1983

    (@chief1983)


    We were investigating a load issue on our site last week, and one thing we noticed was that running SHOW FULL PROCESSLIST showed a lot of calls ultimately traced back to this query:

    SHOW FULL TABLES
    FROM %s
    WHERE table_type = ‘BASE TABLE’

    This was a red herring as far as the root cause of the load, however, it does seems like something that is unnecessary to be running on every page load, when we try to use our cache as much as possible to reduce DB queries. This plugin doesn’t seem to serve any real purpose on a normal page request, merely to optimize the database either manually or via cron, as I understand it.

    Therefore, would it be possible to restructure the init logic a bit so that no db queries are ran unless the plugin is actually doing something else, such as executing its cron job, or loading the settings page, or being manually executed? I may have missed something that would make the creation of the log table necessary on a normal page load, but the core functionality we are using this plugin for would not seem to need any queries running on every single page load.

Viewing 12 replies - 1 through 12 (of 12 total)
  • Plugin Author cageehv

    (@cageehv)

    Hey chief1983,

    I just released v5.0.5 of my plugin.
    Check it out and let me know if this fixes your issue.

    Thanks,
    Rolf

    Thread Starter chief1983

    (@chief1983)

    Wow, that was incredible turnaround! Thanks for looking into it, I’ll be sure to let you know. May not be able to tell for sure until it goes to production, where the high traffic makes the behavior apparent.

    Thread Starter chief1983

    (@chief1983)

    I am sorry to see that this fix apparently caused some problems with the scheduler. For what it’s worth, since we already had it scheduled on production, I am guessing that allowed it to keep working after we upgraded to the new version (I am running 5.0.5 live and WP-Crontrol shows the optimizer entry still in the cron events, scheduled every day). I would be happy to do more testing of a pre-release version to make sure that it solves both the scheduler issue and the unnecessary SQL queries. I would even be able to test from a simple .patch file as I am a PHP developer myself and we developer our own internal plugin as well.

    Plugin Author cageehv

    (@cageehv)

    Hey chief1983,

    Yes, it’s a pity my update broke the scheduler…
    So, I will look at your get_tables issue soon.

    Rolf

    Thread Starter chief1983

    (@chief1983)

    I am curious if the log table is ever needed before the optimizer actually starts. If so, something like this might work? Also used the page check in the init to attempt to make it before actually kicking off the optimizer, but I’m not sure that’s necessary. This moves the creation into its own function that can be called from either place. I tested deactivation, activation, ensure that when the time comes around, the odb_start function is actually triggered, the cron event comes and goes when it should, etc. Perhaps this provides some ideas at least? Also cleaned up a few seemingly unused variable references after the rearranging.

    diff --git a/rvg-optimize-database.php b/rvg-optimize-database.php
    index f81ff37..2a5f162 100644
    --- a/rvg-optimize-database.php
    +++ b/rvg-optimize-database.php
    @@ -1,7 +1,7 @@
     <?php
     /**
      * @package Optimize Database after Deleting Revisions
    - * @version 5.0.7
    + * @version 5.0.8^M
      */
     /*
     Plugin Name: Optimize Database after Deleting Revisions
    @@ -10,7 +10,7 @@ Description: Optimizes the WordPress Database after Cleaning it out
     Author: CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands
     Author URI: http://cagewebdev.com
     Network: True
    -Version: 5.0.7
    +Version: 5.0.8^M
     */
     
     /********************************************************************************************
    @@ -28,8 +28,8 @@ $odb_class = new OptimizeDatabase();
     
     class OptimizeDatabase {
            // VERSION
    -       var $odb_version           = '5.0.7';
    -       var $odb_release_date      = '07/24/2021';
    +       var $odb_version           = '5.0.8';^M
    +       var $odb_release_date      = '07/29/2021';^M
     
            // PLUGIN OPTIONS
            var $odb_rvg_options       = array();
    @@ -92,50 +92,15 @@ class OptimizeDatabase {
             *      INITIALIZE PLUGIN
             *******************************************************************************/       
            function odb_init() {
    -               global $wpdb;
    -               
                    // LOAD CLASSES
                    $this->odb_classes();
     
                    // URLS AND DIRECTORIES
                    $this->odb_urls_dirs();
     
    -               $this->odb_tables = $this->odb_utilities_obj->odb_get_tables();
    -
    -               // CREATE LOG TABLE (IF NOT EXISTS) - v4.6
    -               $this->odb_logtable_name = $wpdb->base_prefix . 'odb_logs';
    -               
    -               $found = false;
    -               for($i = 0; $i < count($this->odb_tables); $i++) {
    -                       if ($this->odb_tables[$i][0] == $this->odb_logtable_name) {
    -                               $found = true;
    -                       }
    -               } // for($i = 0; $i < count($this->odb_tables); $i++)
    -               
    -               // v5.0.3
    -               if (!$found) {
    -                       $sql = '
    -                       CREATE TABLE IF NOT EXISTS <code>' . $this->odb_logtable_name . '</code> (
    -                         <code>odb_id</code>                      int(11) NOT NULL AUTO_INCREMENT,
    -                         <code>odb_timestamp</code>       varchar(20) NOT NULL,
    -                         <code>odb_revisions</code>       int(11) NOT NULL,
    -                         <code>odb_trash</code>           int(11) NOT NULL,
    -                         <code>odb_spam</code>            int(11) NOT NULL,
    -                         <code>odb_tags</code>            int(11) NOT NULL,
    -                         <code>odb_transients</code>      int(11) NOT NULL,
    -                         <code>odb_pingbacks</code>       int(11) NOT NULL,
    -                         <code>odb_oembeds</code>         int(11) NOT NULL,
    -                         <code>odb_orphans</code>         int(11) NOT NULL,
    -                         <code>odb_tables</code>          int(11) NOT NULL,
    -                         <code>odb_before</code>          varchar(20) NOT NULL,
    -                         <code>odb_after</code>           varchar(20) NOT NULL,
    -                         <code>odb_savings</code>         varchar(20) NOT NULL,
    -                         PRIMARY KEY (<code>odb_id</code>)
    -                       ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;                
    -                       ';
    -       
    -                       // CREATE TABLE
    -                       $wpdb->get_results($sql);
    +               // 5.0.8 This may not even be necessary anymore^M
    +               if ($this->odb_is_relevant_page()) {^M
    +                   $this->odb_create_log_table();^M
                    }
     
                    // GET (MULTI-SITE) NETWORK INFORMATION 
    @@ -154,8 +119,6 @@ class OptimizeDatabase {
                    $this->odb_minify = (defined('WP_DEBUG') && WP_DEBUG) ? '' : '.min';
                    
                    // LOAD STYLE SHEET (ONLY ON RELEVANT PAGES)
    -               $this_page = '';
    -               if(isset($_GET['page'])) $this_page = $_GET['page'];
                    // v4.0.3
                    if($this->odb_is_relevant_page()) {
                            wp_register_style('odb-style'.$this->odb_version, plugins_url('css/style'.$this->odb_minify.'.css', __FILE__));
    @@ -575,7 +538,7 @@ class OptimizeDatabase {
            function odb_is_relevant_page() {
                    $this_page = '';
                    if(isset($_GET['page'])) $this_page = $_GET['page'];
    -               return ($this_page == 'odb_settings_page' || $this_page == 'rvg-optimize-database');
    +               return (in_array($this_page, array('odb_settings_page', 'rvg-optimize-database')));^M
            } // odb_is_relevant_page()
     
     
    @@ -644,14 +607,58 @@ class OptimizeDatabase {
            } // odb_start_scheduler()
                    
     
    +    function odb_create_log_table() {^M
    +        global $wpdb;^M
    +^M
    +        // PLUGIN RUNNING (v5.0.5)^M
    +        $this->odb_tables = $this->odb_utilities_obj->odb_get_tables();^M
    +^M
    +        // CREATE LOG TABLE (IF NOT EXISTS) - v4.6^M
    +        $this->odb_logtable_name = $wpdb->base_prefix . 'odb_logs';^M
    +^M
    +        $found = false;^M
    +        for($i = 0; $i < count($this->odb_tables); $i++) {^M
    +            if ($this->odb_tables[$i][0] == $this->odb_logtable_name) {^M
    +                $found = true;^M
    +            }^M
    +        } // for($i = 0; $i < count($this->odb_tables); $i++)^M
    +^M
    +        // v5.0.3^M
    +        if (!$found) {^M
    +            $sql = '^M
    +                               CREATE TABLE IF NOT EXISTS <code>' . $this->odb_logtable_name . '</code> (^M
    +                                 <code>odb_id</code>                      int(11) NOT NULL AUTO_INCREMENT,^M
    +                                 <code>odb_timestamp</code>       varchar(20) NOT NULL,^M
    +                                 <code>odb_revisions</code>       int(11) NOT NULL,^M
    +                                 <code>odb_trash</code>           int(11) NOT NULL,^M
    +                                 <code>odb_spam</code>            int(11) NOT NULL,^M
    +                                 <code>odb_tags</code>            int(11) NOT NULL,^M
    +                                 <code>odb_transients</code>      int(11) NOT NULL,^M
    +                                 <code>odb_pingbacks</code>       int(11) NOT NULL,^M
    +                                 <code>odb_oembeds</code>         int(11) NOT NULL,^M
    +                                 <code>odb_orphans</code>         int(11) NOT NULL,^M
    +                                 <code>odb_tables</code>          int(11) NOT NULL,^M
    +                                 <code>odb_before</code>          varchar(20) NOT NULL,^M
    +                                 <code>odb_after</code>           varchar(20) NOT NULL,^M
    +                                 <code>odb_savings</code>         varchar(20) NOT NULL,^M
    +                                 PRIMARY KEY (<code>odb_id</code>)^M
    +                               ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;^M
    +                               ';^M
    +^M
    +            // CREATE TABLE^M
    +            $wpdb->query($sql);^M
    +        }^M
    +    }^M
    +^M
    +^M
            /*******************************************************************************
             *
             *      MAIN METHOD FOR CLEANING / OPTIMIZING
             *
             *******************************************************************************/
            function odb_start($scheduler) {
    -               global $wpdb;
    -               
    +               $this->odb_create_log_table();^M
    +^M
                    // PAGE LOAD TIMER
                    $time  = microtime();
                    $time  = explode(' ', $time);
    • This reply was modified 1 year ago by chief1983.
    Plugin Author cageehv

    (@cageehv)

    Hey chief1983,

    Can you please send me a copy of your modified version, so I can have a closer look at it and test it.

    Email: info @ cagewebdev.com

    Thanks for thinking with me!

    Rolf

    Thread Starter chief1983

    (@chief1983)

    Sure thing, I just sent it over.

    Plugin Author cageehv

    (@cageehv)

    Hey Chris,

    Got your email, thanks!

    I’ll check it out today.

    Rolf

    Plugin Author cageehv

    (@cageehv)

    Hey Chris,

    i’ve tested your edits extensively and didn’t find any problems.

    Just released v5.0.8.

    Thank you very much for your support!

    Rolf

    Thread Starter chief1983

    (@chief1983)

    Thanks again!

    Thread Starter chief1983

    (@chief1983)

    Sorry about that last patch I sent having the PHP [] array syntax. The patch I posted above had that edited out, but the file I zipped up had the PHP7 syntax I am using more frequently. I had switched the test for the page to use in_array since that would be easier to expand in the future, if the plugin ever adds any more pages to that list. But I should have used array() in the file and not the [] syntax I sent over. It’s hard to switch between backwards compatibility mindset when working on plugin patches, and using all the new bells and whistles for my team’s internal development since we are only worried about PHP 7.3+.

    Plugin Author cageehv

    (@cageehv)

    Hey Chris,

    Ah well, not a big deal! It was easy to fix the syntax error.
    My plugin claims to work with PHP 5.0+ and, for the time being, I like to keep it that way 😉

    Rolf

Viewing 12 replies - 1 through 12 (of 12 total)
  • The topic ‘Possible to optimize odb_get_tables() usage?’ is closed to new replies.