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.

Help me to optimize SQL with tuner 28days log ?

Discussion in 'Workarounds and Optimization' started by LeGastronome, Jun 27, 2011.

  1. LeGastronome

    LeGastronome Member

    Joined:
    Oct 21, 2010
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    Could you help me to optimize conf MySql ?

    Code:
    >>  MySQLTuner 1.0.1 - 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 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 343M (Tables: 678)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 11
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 28d 13h 32m 34s (66M q [26.842 qps], 402K conn, TX: 75B, RX: 12B)
    [--] Reads / Writes: 84% / 16%
    [--] Total buffers: 196.0M global + 3.1M per thread (40 max threads)
    [OK] Maximum possible memory usage: 318.5M (31% of installed RAM)
    [OK] Slow queries: 0% (271/66M)
    [OK] Highest usage of available connections: 32% (13/40)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/197.8M
    [OK] Key buffer hit rate: 98.6% (200M cached / 2M reads)
    [OK] Query cache efficiency: 95.3% (60M cached / 63M selects)
    [!!] Query cache prunes per day: 50383
    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 61044
    [OK] Temporary tables created on disk: 12% (47K on disk / 376K total)
    [OK] Thread cache hit rate: 99% (16 created / 402K connections)
    [!!] Table cache hit rate: 0% (400 open / 209K opened)
    [OK] Open file limit used: 77% (795/1K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 26M)
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 400)
     
  2. nb-master

    nb-master Member

    Joined:
    Sep 26, 2007
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    What is the size of their bases?
    There are more inserts or selects?
     
  3. LeGastronome

    LeGastronome Member

    Joined:
    Oct 21, 2010
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    281.90 MO / Select
     
  4. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    post your existing mysql config
     
  5. LeGastronome

    LeGastronome Member

    Joined:
    Oct 21, 2010
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Code:
    [mysqld]
    
    
    ###The MySQL server
    join_buffer_size = 6M
    key_buffer_size = 128M
    max_connections = 40
    max_allowed_packet = 1M
    table_cache = 900
    #sort_buffer_size = 4M
    #read_buffer_size = 4M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    wait_timeout = 60
    #connect_timeout = 30
    interactive-timeout = 50
    query_cache_size = 128M
    query_cache_limit = 20M
    # Try number of CPU's*2 for thread_concurrency
    #thread_concurrency = 8
    max_user_connections = 30
    log-error = /var/log/mysqld.log
    tmp_table_size = 64M
    max_heap_table_size = 32M
    open_files_limit = 2000
     
  6. 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
    On MySQL 5.1, table_cache is actually table_open_cache so you would want to rename to table_open_cache for that variable in the /etc/my.cnf file

    I would remove the commented out settings as well such as thread_concurrency doesn't exist on anything other than Solaris, so that variable that is commented out might simply be removed.

    You really don't have any further suggestions from mysqltuner to go with. I would suggest enabling the slow query log to see if any databases are having slow queries. On MySQL 5.1, this would enable the slow query log:

    Code:
    slow_query_log
    This will place into /var/lib/mysql the hostname-slow.log file. After you have that file created and give it around 24 hours upon a MySQL restart to push any slow queries into it, you could then check if it has anything in it. If it does, you would want to check those databases with slow queries.
     
  7. LeGastronome

    LeGastronome Member

    Joined:
    Oct 21, 2010
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    ok I will try
     
Loading...

Share This Page