Optimize mass databases

I like to request a new plugin for multidb user :slight_smile:. Optimizing each database is tedious. Plus I don't know how to mass optimize all 256 databases at the same time. I wonder if it's possible for a developer here to create an optimize plugin that allows wpmu + multidb owners to do one click to optimize all databases? For example: There will be options where wpmu+multidb owners get to pick to match up how many databases they have and want to optimize, such as 16, 256, and 4096.

Thanks...

  • Ovidiu
    • Code Wrangler

    @drmike:

    I am sstill using a very old script you posted a couple of years back on the regular wpmu forums to cleanup/optimize all DBs, if you happen to rememebr what I am talking about, can you post that again so I can check if I have the latest version?
    otherwise I'll have to check from home and post it here so you know what I am talking about :slight_smile:

    btw. not using multiple DBs so the old script worked just fine for me...

  • argh2xxx
    • The Incredible Code Injector

    LOL, and here I'm back again to question if anyone has find a solution for this very problem yet? Recently I ran mysqltuner.pl script and find out that I've so many fragmented tables that it scares me to death! Thinking of optimizing 4096 databases of tables, wow, how consuming this will be if one has to do one by one. Any alternative solution?

  • Ovidiu
    • Code Wrangler

    I have pasted drmike's script on pastebin: http://pastebin.com/sm9x6gK8

    I have been using it for years but after the latest mysql upgrade I am now getting warnings like this when running it:

    PHP Deprecated: mysql_db_query(): This function is deprecated; use mysql_query() instead in /root/optimize_dbs on line 30

    can I simply replace mysql_db_query with mysql_query to fix it? anyone willing to help out here?

    p.s. to run it type "php optimize_dbs" in your console.

  • Ovidiu
    • Code Wrangler

    @Mark: Any idea why a few particular tables are complaining?

    I am using: mysqlcheck --all-databases --auto-repair --extended --check --optimize -uroot -p*****

    c1foo.mag_catalog_product_super_attribute
    note     : Table does not support optimize, doing recreate + analyze instead
    status   : OK

    @everyone else: any help with the above linked script?

  • Mark de Scande
    • Syntax Hero

    @Ovidiu strange :slight_smile: ps if you

    1) Upgrade MySQL to V 5.5 it will also run the script for you
    2) If you upgrade WHM it will also run the script for you

    Trust me the db set up is 70GB big and it did it with mine

    Ps try this

    SSH
    type screen (press enter)
    type mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    Now wait for it to run just let it start and close SSH

    Now after about a hour log back in with SSH

    Type screen -r it should give you a list of screens that is open

    now type screen -r 1234

    Then you will see if it ran ok

    I hope this helps

  • Ovidiu
    • Code Wrangler

    thanks Mark but the latest Debian version of MYSQl is mysql-server 5.1.63-0+squeeze1

    I don't run WHM.

    the only difference between your command and mine is the --extended flag and still some tables complain: `note : Table does not support optimize, doing recreate + analyze instead
    status : OK` but nevermind it is showing results.

  • joe
    • Site Builder, Child of Zeus

    The idea from @argh2xxx for a plugin to perform this task would be a wonderful thing....in the meantime...

    It looks like this thread was abandoned in 2012 without a conclusion.

    What, exactly, is the MYSQL command that should I use to optimize all of the 4096 db's in my multisite multidb database? Does the code below work for the current versions of debian and mySQL?

    (note~ all 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?

    Ps try this
    SSH
    type screen (press enter)
    type
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Now wait for it to run just let it start and close SSH
    Now after about a hour log back in with SSH
    Type screen -r it should give you a list of screens that is open
    now type screen -r 1234
    Then you will see if it ran ok

  • joe
    • Site Builder, Child of Zeus

    I tried the script above and got an error:
    Error: /opt/bitnami/mysql/bin/mysqlcheck.bin doesn't support multiple contradicting commands.

    so I tried:

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

    They both ran. It spit out a long list of results with this error at the end

    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. Any ideas?

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.