How do I optimize all the databases (4096) in my multi-db installation?

RE: https://premium.wpmudev.org/forums/topic/optimize-mass-databases#post-866155
It looks like this thread was abandoned in 2012 without a conclusion. I posted a question to this thread April 15, 2015 and haven't seen a reply so I am starting this fresh thread in the hope that it will be noticed and answered.

What, exactly, is the recommended method/MYSQL command to use to repair and optimize all of the 4096 db's in my multisite multi-db database and save and email me a backup copy of the entire db with as few actions as possible?

(note~ all my master and sub db's use the same user/pass setup as per the multidb setup instructions. I am running Debian 3.16.7 on a VM on the google cloud. MySQL Server version: 5.5.40)

How, exactly, can I set up a cron job to do this on a regular basis?

I tried:
mysqlcheck -u root -p --optimize --all-databases

and

mysqlcheck -u root -p --auto-repair --all-databases

They both ran. They both spit out long lists of results with this error at the end of the second script.

mysql.user
warning  : Table is marked as crashed and last repair failed
warning  : 1 client is using or hasn't closed the table properly
warning  : Size of datafile is: 848       Should be: 772
error    : Keypointers and record positions doesn't match
warning  : Found 13 key parts. Should be: 9
error    : Corrupt
Repairing tables
mysql.user
error    : Can't create new tempfile: './mysql/user.TMD'
status   : Operation failed

I'm not sure how to interpret these results. Was this error thrown because the last db that got optimized is , as yet, empty? Any ideas?

It would seem that a reliable optimizing and repair db method/script, run on a daily (or weekly) schedule, is a universal requirement for everyone who uses the multi-db plugin. The idea from @argh2xxx for a plugin to perform this task would be a wonderful thing....in the meantime...

  • joe

    Hello again, Jude,

    As much as I would love to see this made into a plugin (or an extension of the multi-db plugin), what I am looking for right now is the recommended method/MySQL commands to repair and optimize all of the 4096 db's in my multisite multi-db database, then save and email a backup copy of the entire db using as few actions as possible. Then put all of this into a cron job.

    I think all multi-db users would agree that this should be a part of the multi-db plugin because this is vital to maintaining the end result.

  • Jose

    Hello there @joe,

    Hope you are doing well today.

    I think all multi-db users would agree that this should be a part of the multi-db plugin because this is vital to maintaining the end result.

    While we can add this to be considered as a feature request, I must say that Multi-DB is not a plugin for the common end user. It doesn't do everything for you -as you may already noticed when you installed the plugin-, and is just a small piece on a list of requirements when it comes to scale wordpress. In fact, if you have up to 5.000 blogs in your MS install, it is recommended to use the default single DB setup.
    I mean, this kind of scenarios will always require a system/DB admin dedicated to maintain everything up and running.

    That said, I was consulting with one of our system administrators for edublogs and, according to his experience, DB optimization is rarely necessary.
    If you still want to automate this process, your script above looks good. The error can be caused by a locked table.
    You should trying shutting down your site before running the script.
    This link can shed some light:
    http://stackoverflow.com/questions/8843776/mysql-table-is-marked-as-crashed-and-last-automatic-repair-failed

    In order to backup all your DBs and send it via email, you should try to use this script in a cron job:
    mysqldump -u root -p --all-databases | gzip -9 > file.gz ; mutt -a file.gz "attachment" you@example.com
    I'm sure you will end having issues with the size of the backup file, so I would suggest to save it into a server location as well just in case.

    This article can be of help:
    http://www.kohactive.com/blog/backup-a-sql-database-and-email-it

    Please don't hesitate to ask if you have further questions.

    Cheers,
    Jose

  • joe

    Hello @Jose,

    Thank you for responding.

    I understand that using the 4096 db option is a huge overkill, but if the day came that it wasn't overkill, the installation could immediately handle it. The multi-db instructions state:

    There’s no performance hit for using 256 databases over 16. But if you’re expecting massive growth, planning ahead at this point will save you additional work down the road.

    Is there a performance hit for 4096db's over 256 db's? My intention is prepare now in order to avoid the "additional work down the road" just in case, for some reason, my multi site is actually wildly successful. :wink: The site is built on a VM on the Google cloud so the server can scale to handle any load.

    So then, I need to establish an effective maintenance routine. As per your recommendation I need two more lines of code. One to shut down the site before I run the scripts and one to start up the site after the scripts run. Please advise on the correct syntax for those two lines. Should I run the repair line before the optimize line?

    mysqlcheck -u root -p --optimize --all-databases
    
    mysqlcheck -u root -p --auto-repair --all-databases
    
    mysqldump -u root -p --all-databases | gzip -9 > file.gz ; mutt -a file.gz "attachment" you@example.com

    Also, I agree that emailing backups will eventually have sizing issues so I will be much better off storing the backups on my google disk. Please advise on the correct syntax for the mysqldump line to direct the backup to the google disk.

    I was consulting with one of our system administrators for edublogs and, according to his experience, DB optimization is rarely necessary.

    The MySQL manual states: (https://dev.mysql.com/doc/refman/5.5/en/optimization.html)

    Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.

    These statements seem to contradict each other, please explain.

    One more question: How often would you recommend I run this maintenance routine?

    Thank again for your help and guidance. I really appreciate it. WPMUdev support and plugins have proven to be indispensable components for building out any multi site project.

    Cheers!

  • Jose

    Hey @Joe,
    Glad to hear back from you.

    Is there a performance hit for 4096db's over 256 db's?

    No. Basically, this means that you won't see an improvement between using 256 vs 4096, but just that it comes the day that you have a HUGE amount of blogs in your site, 4096 config will work better than 256.

    Please advise on the correct syntax for the mysqldump line to direct the backup to the google disk.

    I'm not quite sure about this. Assuming that you are in a VM in google cloud and you just want to store the files in that cloud, I guess that a standard comman would do the trick:

    timestamp=$(date '+%d-%m-%Y@%H:%M')
    mysqldump -u dbuser -pPassword --all-databases | gzip > /path/in/your/vm/bakckup_$timestamp.sql.gz

    Now, if you are referring to google drive or some kind of external cloud storing, there should be an API for that and you would need to read the API documentation.

    These statements seem to contradict each other, please explain.

    Actually, not. It is talking about optimization in general. It says that you should optimize all the aspects of your hardware (CPU, memory,etc) so that mySQL have more resources to work with.
    When we talk about optimization/repair above, we are talking about DB tables optimization, which is a very specific process.

    One more question: How often would you recommend I run this maintenance routine?

    Long story short: only when needed.
    As I said before, the benefit of this table optimization/repair will be relative. You only need to run this process ONLY, if there is something to optimize or repair.
    See this blog post and the comments for more opinions in this regard:
    http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/

    So then, I need to establish an effective maintenance routine. As per your recommendation I need two more lines of code. One to shut down the site before I run the scripts and one to start up the site after the scripts run. Please advise on the correct syntax for those two lines. Should I run the repair line before the optimize line?

    It makes sense to first run the repair.
    I believe that you should do this only when necessary, putting all your focus and attention on it. I wouldn't use an automated routine. You should manually shutdown the server and run the process while monitoring it.

    My overall advice is to focus you efforts on benchmarking and diagnosis. Most probably, you won't need to repair or optimize all your tables but just some specific tables time to time.
    Also, this is a very technical matter and you should know your stuff very well. I'm not an expert on this neither.
    I guess that if your blog is successful enough to need Multi-DB, you should include a DB admin in your budget. :slight_smile:

    Hope this helps.

    Let me know if you have further questions.

    Cheers!
    Jose

  • joe

    Hello again @Jose,

    Thanks for the response.

    Okay, I understand that there is no hit for the 4096 over the 256 db's so I will continue down that path. There is no down side, and if I ever needed this capacity, I will be ready.

    On the backup issue, I think my best bet may be to use the snapshot feature in the google admin. This is supposed to backup the entire VM and store it in the google cloud account. Are there any potential pitfalls I should be aware of using this method?

    On the cron job idea: I will abandon this idea based on your suggestion and also based on the article you referenced. Thx!

    Okay then, one unresolved question. Just in case I ever need to run the repair and optimize scripts, are these proper code commands to start and stop mysql?

    mysql stop
    mysqlcheck -u root -p --auto-repair --all-databases
    mysqlcheck -u root -p --optimize --all-databases
    mysql start

    I agree, if my project ever got big enough to need all this capacity, it would be time to hook up with someone who could efficiently manage everything. At that point, I could afford it. In the meantime I want to get the project up and running properly, while being prepared for the possibility of success, even if that possibility is remote :wink:

    Thanks again, @Jose!

    • Jose

      On the backup issue, I think my best bet may be to use the snapshot feature in the google admin. This is supposed to backup the entire VM and store it in the google cloud account. Are there any potential pitfalls I should be aware of using this method?

      Sounds as a good idea. Just double check if the DBs are included in the backup. I guess it is the case, but it worth nothing to check it. (I would suggest to create a backup of a test site, drop some DB or DB tables and restore it just to see it working).

      Okay then, one unresolved question. Just in case I ever need to run the repair and optimize scripts, are these proper code commands to start and stop mysql?

      Depending in your OS, the proper command could be one of the following:

      service mysql stop
      service mysqld stop
      /etc/init.d/mysqld stop

      BUT. You don't want to stop your mysql server. If you do so, then mysqlcheck wouldn't be able to connect to your server and do his stuff.
      You should stop any process that is using you DB.
      In this case, I would try stopping apache, restarting mysql (just to be sure there is no process going on), run the optimization, and restart apache.
      In my server, it looks something like this:

      service apache2 stop
      service mysql restart
      mysqlcheck -u root --auto-repair --optimize --all-databases
      service apache2 restart

      Let me know if that works for you. :slight_smile:

      Cheers,
      Jose

  • joe

    (I would suggest to create a backup of a test site, drop some DB or DB tables and restore it just to see it working).

    great idea, thanks!

    In this case, I would try stopping apache, restarting mysql (just to be sure there is no process going on), run the optimization, and restart apache.
    In my server, it looks something like this:

    service apache2 stop
    service mysql restart
    mysqlcheck -u root --auto-repair --optimize --all-databases
    service apache2 restart

    Would I need to stop mysql before I stop apache?

    service mysql stop
    service apache2 stop
    service mysql restart
    mysqlcheck -u root --auto-repair --optimize --all-databases
    service apache2 restart

    Thanks, @Jose!

  • joe

    Hello again, @Jose,

    I really appreciate your help. I'm going to do as you suggested and create a test site to rehearse a site restoration from the snapshot made in the google admin. I will report the results back to this thread. If all goes well, I will close the thread after posting. (it may be a day or two before I can get to this)

    Again thanks for all your assistance and patience. I appreciate you hanging with me through this issue.

    Cheers!

  • joe

    Hello again, @Jose,

    The google site restore from snapshot goes like this:
    Login to google console. Click on VM instances.
    Select the instance you want to snapshot and click stop.
    Click on snapshot and create a snapshot.
    Go back to VM instances, click on the instance and click start from the instance settings page. Return to the VM instance list page.
    Click new instance. name and provision the instance and click change next to the disk description.
    Click on the snapshot heading and select your snapshot and save.
    Delete the original disk and click on the new disk.
    Change the external IP from the default ephemeral address to your static ip and save.
    That's it!

    I hope this thread is useful to the WPMU community.

    @Jose --I appreciate you hanging with me through this issue.

    Cheers!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.