The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to optimize all tables in all databases

Discussion in 'General Discussion' started by Silent Ninja, Oct 24, 2008.

  1. Silent Ninja

    Silent Ninja Well-Known Member

    Joined:
    Apr 18, 2006
    Messages:
    198
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Buenos Aires, Argentina
    I've made up this code (based on another one from Son Nguyen) to optimize all databases on the server so they index what they can run faster and smoothly.

    If you want to, it could be a good addon on the WHM / cPanel scripts :)
    It's a bash script, but it can be easily rewrote in Perl or PHP if necesary

    Code:
    #!/bin/bash
    
    # Este script optimiza todas las tablas de todas las bases de datos del servidor
    
    for DBNAME in `ls -1A /var/lib/mysql/ | egrep -v "\.sock|\.user|\,/" | cut -d/ -f1`
    do
    	# Busca todas las tablas de la base de datos en esta iteración
    	TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
    	# Hace un loop para optimizar a cada una
    	for TABLENAME in $TABLENAMES
    	do
    		mysql -D $DBNAME -e "REPAIR TABLE $TABLENAME;"
    		mysql -D $DBNAME -e "OPTIMIZE TABLE $TABLENAME;"
    	done
    done
    EDIT: Oops, comments are in spanish, hehe, forgot to translate them, they say...
     
    #1 Silent Ninja, Oct 24, 2008
    Last edited: Oct 24, 2008
  2. whplus

    whplus Well-Known Member

    Joined:
    Dec 8, 2007
    Messages:
    66
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Behind your business
    you can try this command:
    Code:
    mysqlcheck -o -A
    susan,
     
  3. mindnetcombr

    mindnetcombr Member

    Joined:
    Sep 16, 2004
    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    1
    lol

    replace a full script for single shell line.

    :D
     
  4. Keegan

    Keegan Well-Known Member

    Joined:
    Oct 22, 2001
    Messages:
    110
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    Silent Ninja, you've been WACKED!

    Its actually commendable that you'd post a helpful item, Susan just shortened it up a bit ()Thank you Susan!()
     
  5. rachelm

    rachelm Well-Known Member

    Joined:
    Jan 26, 2005
    Messages:
    94
    Likes Received:
    1
    Trophy Points:
    8
    And I'm replacing the single line with a cpanel script that'll both optimize and repair all the db's via our cpanel addon :P Just enter your pass and click a button.
     
  6. sebby

    sebby Well-Known Member

    Joined:
    Jun 15, 2004
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    6
    Hello everyone,

    What is the recommended standard practice regarding server wide MySQL DB optimization? Is it recommended to do this on a regular basis as a preventive measure (and if so at what fequency? every week? every month?) or should one admin wait for performance related issues before running it?

    Thanks for your insights.
     
    #6 sebby, Nov 23, 2008
    Last edited: Nov 24, 2008
Loading...

Share This Page