Creating indexes with large DB

  • Sam

    (@xumbrella)


    3 years ago

    Hello, your plugin has some really interesting features especially adding the appropriate indexes to the post_meta table.

    Our post_meta table is massive (11 million rows) so I am intrigued as to what to expect if I use your plugin to index such a massive table.

    I have tried other plugins that claim to do this on our staging site and they just killed our DB. Probably because they time out and don’t finish etc.

    Can you provide any guidance for users wanting to use this feature with massive sites like ours? I see the plugin supports WP CLI, would this be how the plugin runs such an expensive process on the DB?

    Many thanks.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Support Pieter Daalder

    (@wizzard_)

    Hi there Sam!

    Thank you for contacting us. If you’re worried about database performance during these kind of changes, it’s best to try it out on a staging environment first. It’s hard to tell wether it should work in your case, since we have no insight in the platform/stack you’re currently using.
    If you have the option to run this through WP-CLI, that would indeed be the best way of doing this.

    Let me know if there is anything else we can help you with!

    Hello Sam!
    Sorry to hear about your troubles adding indices. In our plugin you can run database optimization both from the GUI (WP Admin) or via WP CLI.

    How to trigger from WP Admin:
    1. Go to WP Admin -> Servebolt -> Performance Optimizer
    2. Go to the Database-tab
    3. Inspect the tables in the section “Database Indexes”. Here you can add indexes for each table, one at a time by clicking “Create index”.

    In the section below there is also a feature to convert any tables that uses the MyISAM storage engine and convert them to InnoDB instead. This removes the known issue with MyISAM that causes database locking.

    You can also click the “Optimize!”-button at the bottom of the page. This will trigger both the addition of the indices as well as the MyISAM/InnoDB storage engine fix.

    How to trigger from WP CLI:
    wp servebolt db optimize
    This will trigger both the addition of the indices as well as the MyISAM/InnoDB storage engine fix. There is no way of triggering the index addition process using WP CLI. This is currently only possible from the GUI.

    Note that our plugin runs database optimization by running MySQL queries (without any concurrency). As long as the query finishes without any errors or interruptions then the operation should work as expected. In terms of timeout it might be smart to use the WP CLI-option as there is usually no timeout policy.

    I hope this answered your questions. If not then please do not hesitate to ask!

    Kind regards
    Robert S?ther
    Servebolt

    Thread Starter Sam

    (@xumbrella)

    Hi Thanks for your detailed feedback.

    I did try it on our staging site and running from the GUI and it added the indexes without a problem.

    Regarding your WP CLI command, if I run that and all our tables are already InnoDB, plus wp_options already has an index, will it just skip those two and jump to adding the required indexes for the wp_postmeta table?.

    thanks

    Plugin Support Pieter Daalder

    (@wizzard_)

    Hi Sam,

    Yes it will skip any steps that are not needed.

    Kind regards,

    Pieter
    Servebolt

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Creating indexes with large DB’ is closed to new replies.