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.

Optimizing MySQL with MySQLTuner

Discussion in 'Workarounds and Optimization' started by subhra, Apr 1, 2014.

  1. subhra

    subhra Registered

    Joined:
    Apr 1, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Last month I was facing frequent MySQL crash problem. I tried to optimize MySQL with MySQLTuner, it started working fine.

    But from last 2-3 days, I am facing the server down problem frequently. If I power cycle the server and restart MySql, then only it works properly. I am not sure whether it is MySQL crash problem or not.

    My MySQLTuner report,

    Code:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.69-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 617K (Tables: 126)
    [--] Data in InnoDB tables: 73M (Tables: 155)
    [!!] Total fragmented tables: 166
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 30m 26s (2K q [1.514 qps], 107 conn, TX: 9M, RX: 410K)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 132.0M global + 2.7M per thread (50 max threads)
    [OK] Maximum possible memory usage: 269.5M (27% of installed RAM)
    [OK] Slow queries: 0% (0/2K)
    [OK] Highest usage of available connections: 6% (3/50)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/419.0K
    [!!] Key buffer hit rate: 80.0% (65 cached / 13 reads)
    [OK] Query cache efficiency: 64.9% (1K cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 180 sorts)
    [!!] Temporary tables created on disk: 30% (60 on disk / 200 total)
    [OK] Thread cache hit rate: 97% (3 created / 107 connections)
    [!!] Table cache hit rate: 1% (67 open / 3K opened)
    [OK] Open file limit used: 1% (17/1K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    [OK] InnoDB data size / buffer pool: 73.7M/75.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        table_cache (> 67)
    
    

    And my current my.cnf file settings:

    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    default-storage-engine=InnoDB
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    max_connections=50
    wait_timeout=30
    query_cache_size=15M
    tmp_table_size=32M
    max_heap_table_size=32M
    thread_cache_size=4
    innodb_buffer_pool_size=75M
    table_cache=67
    
    slow-query-log=1
    slow_query_log_file=/var/log/mysqld/slow-query.log
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    
    Can anyone suggest what I need to do to improve the server performance?

    My server configuration:
    CentOS
    1 GB RAM
    512 MB swap memory

    I get average 3000 visits per day
     
  2. subhra

    subhra Registered

    Joined:
    Apr 1, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Also can anyone suggest me how can improve the performance for my website? It is developed using wordpress.
     
    #2 subhra, Apr 2, 2014
    Last edited by a moderator: Apr 2, 2014
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I suggest letting MySQL run for at least 24 hours before running the tuner. This will ensure more accurate results that you can provide here.

    Thank you.
     
  4. subhra

    subhra Registered

    Joined:
    Apr 1, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    Here is the latest MySQLTuner report,

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.69-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 569K (Tables: 126)
    [--] Data in InnoDB tables: 72M (Tables: 155)
    [!!] Total fragmented tables: 156
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 18m 13s (187K q [2.144 qps], 3K conn, TX: 411M, RX: 21M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 140.0M global + 2.7M per thread (50 max threads)
    [OK] Maximum possible memory usage: 277.5M (27% of installed RAM)
    [OK] Slow queries: 0% (0/187K)
    [OK] Highest usage of available connections: 26% (13/50)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/414.0K
    [!!] Key buffer hit rate: 83.3% (2K cached / 397 reads)
    [OK] Query cache efficiency: 79.9% (137K cached / 171K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
    [!!] Temporary tables created on disk: 34% (2K on disk / 5K total)
    [OK] Thread cache hit rate: 99% (21 created / 3K connections)
    [!!] Table cache hit rate: 4% (70 open / 1K opened)
    [OK] Open file limit used: 1% (19/1K)
    [OK] Table locks acquired immediately: 100% (42K immediate / 42K locks)
    [OK] InnoDB data size / buffer pool: 72.2M/75.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        tmp_table_size (> 40M)
        max_heap_table_size (> 40M)
        table_cache (> 70)
    

    And current my.cnf file settings:
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    default-storage-engine=InnoDB
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    max_connections=50
    wait_timeout=30
    query_cache_size=15M
    tmp_table_size=40M
    max_heap_table_size=40M
    thread_cache_size=4
    innodb_buffer_pool_size=75M
    table_cache=70
    
    slow-query-log=1
    slow_query_log_file=/var/log/mysqld/slow-query.log
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
     
Loading...

Share This Page