Automatically Optimizing MySQL

Discussion in 'Tutorials and Guides' started by TruvisT, May 31, 2013.

  1. TruvisT

    TruvisT Server Management Specialist Verified Provider

    398
    144
    May 16, 2013
    Found this on one of our old servers during spring cleaning while moving sites over to our new highend servers. Enjoy.
     


    #!/bin/sh

    mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
    mysql -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
    if [ "$datafree" -gt 0 ] ; then
    fragmentation=$(($datafree * 100 / $datalength))
    echo "$database.$name is $fragmentation% fragmented."
    mysql -NBe "OPTIMIZE TABLE $name;" "$database"
    fi
    done
    done
    SRC: http://blog.solidshellsecurity.com/2013/05/31/automatically-optimizing-mysql/
     
    Last edited by a moderator: Jun 1, 2013
    Kyn_DH and drmike like this.
  2. Reece-DM

    Reece-DM New Member Verified Provider

    400
    58
    May 16, 2013
  3. walesmd

    walesmd New Member

    35
    12
    May 26, 2013
    I knew your script appeared to be a bit excessive, assuming CentOS 6.4 64bit:

    Code:
    wget http://goo.gl/BLhej -O /etc/yum.repo.d/mariadb.repo
    sudo yum install MariaDB-server MariaDB-client
    sudo yum install MariaDB-Galera-server MariaDB-client galera
    sudo /etc/init.d/mysql start
    
     
    Last edited by a moderator: May 31, 2013
  4. WebSearchingPro

    WebSearchingPro VPS Peddler Verified Provider

    493
    143
    May 15, 2013
     

    Don't you just love it when you find little scripts that keep things running smoothly or funny comments in the code far after your forgot it was there?

    Its like Christmas! (sort of) in a sort of nostalgic way.
     
  5. JDiggity

    JDiggity New Member

    210
    25
    Apr 29, 2013
    Rename this to optimize table. As it doesn't optimze mysql.
     
  6. Kris

    Kris New Member

    167
    113
    May 7, 2013
    Code:
    wget --no-check-certificate https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
    
    perl mysqltuner.pl 
    • Repair all DBs : mysqlcheck -Ar
    • Optimize all DBs: mysqlcheck -Ao
     
    notFound likes this.
  7. JDiggity

    JDiggity New Member

    210
    25
    Apr 29, 2013
    @Kris, I never really looked at it but is there a way to actually optimize the config portion automatically?
     
    Last edited by a moderator: Jun 5, 2013
  8. Kris

    Kris New Member

    167
    113
    May 7, 2013
    Not really. You take the output it gives you and make the changes accordingly in /etc/my.cnf

    Quick repair, optimize and optimizing my.cnf never let me down :)
     
    Last edited by a moderator: Jun 5, 2013
  9. JDiggity

    JDiggity New Member

    210
    25
    Apr 29, 2013
    Damn, I wish there was a a program that did it for me. There it is people write that program and you guys will be a hero of the low end community.
     
  10. jcaleb

    jcaleb New Member

    279
    100
    May 15, 2013
    I think optimizing my.cnf really needs human judgement
     
    notFound and Kris like this.
  11. vanarp

    vanarp Active Member

    299
    32
    May 16, 2013
  12. TruvisT

    TruvisT Server Management Specialist Verified Provider

    398
    144
    May 16, 2013
    Truth.

    It can be done automatically, assuming the script was moniting the activitiy and edited based on that along with usage and system resources. But would it be reliable?
     
  13. Kris

    Kris New Member

    167
    113
    May 7, 2013
    Not really. You can end up with an outlier of traffic one day which would try to increase the query_cache_size and tmp_table_size well over 128M (which is the recommended limit to avoid any degradation of performance.)

    In the end, I always use mySQL tuner, and use those numbers as a guideline when editing my.cnf.

    If it's telling you to increase certain values (while also warning you some will cause a slow-down) that's just judgement.
     
  14. acd

    acd New Member

    176
    71
    May 16, 2013
    From a theoretical perspective, yes. You could make an application that monitored system CPU, RAM, and IO needs,disk cache hit/miss, and read styles, compared to mysql QPS, avg query time per query type (baysian match), http pages service rate, cpu time per page, page popularity, varnish/nginx/etc cache size, churn rate, hit/miss rate, total service pool and expire-before-reuse rate and turn that into a reasonable set of auto-tuning parameters based on your usage case and application stack.

    The problem would be you'd use more resources than you could expect to save unless you processed the log files off-system. Also, you'd need a reasonable understanding of machine learning algorithms that most low-end providers simply don't have the expertise for. Any site in the multiple-dedicated range can afford to hire a fellow to tune their application stack, if they don't already have that knowledge in-house. Smaller than that and it's far cheaper to either DIY or upgrade to a more capable box. An automated system would be like killing some cockroaches by beating them with a 1U server.
     
    Last edited by a moderator: Jun 7, 2013
    Kris likes this.
  15. TruvisT

    TruvisT Server Management Specialist Verified Provider

    398
    144
    May 16, 2013
    VEry well said. Great analogy too!
     
  16. ICPH

    ICPH Member

    170
    2
    Aug 24, 2013
    When i am running the OP (first post of this topic) script via cronjob monthl, it always send me e-mail with output:
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    But when i run that script manually, it always processing database good, but i think somewhere in the process it still shows the mentioned error. Seems that the script may need tweaking to work 100%. I am not a coder so i suppressed any errors by adding: &>/dev/null
    at the end of the line where i am launching the script.