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.

MySQL, high number of fragment tables. Safe to optimize all?

Discussion in 'Workarounds and Optimization' started by jols, Sep 5, 2013.

  1. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    Hi,

    I am noticing that MySQL in Top regularly takes up between 160% and 400% CPU.

    I recently ran mysqltuner.pl and it listed over 1000 fragmented tables on the server.

    Question - Is is safe to run the following at shell? Does this have a chance of causing any corruption issues, etc?

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


    Thanks much!
     
  2. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    By the way, here's the contents of my.cnf

    -----------------------------------------------------------------------
    Code:
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-external-locking
    key_buffer_size = 384M
    max_allowed_packet = 32M
    table_open_cache = 5096
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 128M
    query_cache_limit = 16M
    join_buffer_size = 384K
    tmp_table_size = 32M
    max_heap_table_size = 32M
    table_cache = 6024
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    max_connections = 400
    log-slow-queries
    local-infile=0
    innodb_file_per_table=1
    tmpdir=/mysqltmp
    server-id       = 1
    
    innodb_buffer_pool_size = 2G
    open_files_limit=50000
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
      
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    -----------------------------------------------------------------------
    -----------------------------------------------------------------------



    And here's the report from mysqltuner.pl


    Code:
    >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 11G (Tables: 19935)
    [--] Data in InnoDB tables: 1G (Tables: 10064)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 105)
    [!!] Total fragmented tables: 1637
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 22m 9s (53K q [40.251 qps], 2K conn, TX: 266M, RX: 15M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 2.5G global + 12.6M per thread (400 max threads)
    [OK] Maximum possible memory usage: 7.5G (64% of installed RAM)
    [OK] Slow queries: 0% (38/53K)
    [OK] Highest usage of available connections: 2% (11/400)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/3.5G
    [OK] Key buffer hit rate: 99.0% (1M cached / 11K reads)
    [OK] Query cache efficiency: 32.2% (22K cached / 69K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (5 temp sorts / 2K sorts)
    [!!] Joins performed without indexes: 1134
    [OK] Temporary tables created on disk: 11% (456 on disk / 4K total)
    [OK] Thread cache hit rate: 99% (11 created / 2K connections)
    [OK] Table cache hit rate: 97% (1K open / 1K opened)
    [OK] Open file limit used: 3% (1K/50K)
    [OK] Table locks acquired immediately: 99% (32K immediate / 32K locks)
    [OK] InnoDB data size / buffer pool: 1.9G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 384.0K, or always use indexes with joins)
     
  3. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    Yes, You can run the this command on your server through SHELL, It will repair and optimize your all database

     
  4. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    Hi. Thanks, yup, but is it totally safe to run on a highly active server chock full of databases?
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    649
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    I recommend using the MySQL tuner after MySQL has been running for at least 24 hours. The output you pasted shows it was only up for 22 minutes when the tuner was ran. As for your other question, repairing and optimizing database tables is generally a safe operation.

    Thank you.
     
  6. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You should probably omit --check from the command as the --auto-repair option uses check automatically.
     
  7. pdelteil

    pdelteil Registered

    Joined:
    Sep 15, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Rio de Janeiro, Rio de Janeiro, Brazil
    cPanel Access Level:
    Website Owner
    You can't run the command with the --check and --auto-repair options at the same time.

    It gives this error:

    Error: mysqlcheck doesn't support multiple contradicting commands.
     
  8. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes, and you really don't need to specify the --check option as it is the default operator.
     
    #8 Archmactrix, Oct 27, 2014
    Last edited: Oct 27, 2014
Loading...

Share This Page