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.

MysqlTuner - How to do this optimizations

Discussion in 'Workarounds and Optimization' started by jmanalansan, May 20, 2011.

  1. jmanalansan

    jmanalansan Registered

    Joined:
    May 19, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    How do i set these recommendations in the mysql configuration file?

    Thanks in advance,

    UPDATE:
    Btw, here is my current my.conf file

    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
     
    #1 jmanalansan, May 20, 2011
    Last edited: May 20, 2011
  2. bhd

    bhd Well-Known Member

    Joined:
    Sep 20, 2003
    Messages:
    149
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    JNB ZA
    cPanel Access Level:
    Root Administrator
    your first problem is this -
    You should wait another day or so before doing anything it says. The suggestions it makes only have real value after MySQL has been running for [at least] a day.
     
  3. jmanalansan

    jmanalansan Registered

    Joined:
    May 19, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Thanks, here's the new data after a day:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 26M (Tables: 92)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 16
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 23h 17m 16s (21M q [128.130 qps], 323K conn, TX: 31B, RX: 1B)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
    [OK] Slow queries: 0% (1/21M)
    [OK] Highest usage of available connections: 9% (49/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/11.4M
    [OK] Key buffer hit rate: 99.3% (10M cached / 72K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 10K sorts)
    [OK] Temporary tables created on disk: 16% (170 on disk / 1K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 2% (64 open / 2K opened)
    [OK] Open file limit used: 4% (112/2K)
    [OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
    [OK] InnoDB data size / buffer pool: 144.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        thread_cache_size (start at 4)
        table_cache (> 64)
    Anyone can help me out how to do these optimizations? :)
     
  4. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    post your existing my.conf file
     
  5. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
  6. jmanalansan

    jmanalansan Registered

    Joined:
    May 19, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Here it is


    Code:
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database 
    @LinuxTechie: Thanks for the link :)
     
  7. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    You are welcome :)
     
  8. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    you start with below my.conf file

    and restart mysql services

    wait for 24hours and post your results output of mysqltuner
     
  9. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    This person's machine is MySQL 5.1.56 per the mysqltuner.pl information:

    You cannot use this entry for MySQL 5.1:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    This would be for a MySQL 5.0 machine and would require creating the file with the right permissions. This is why no "one fits all" my.cnf file is going to be good to use. The correct entry for MySQL 5.1 would be:

    Code:
    slow_query_log
    This will create a log file named hostname-slow.log in /var/lib/mysql on MySQL 5.1 machines.
     
Loading...

Share This Page