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 my.cnf Suggestions Needed

Discussion in 'Workarounds and Optimization' started by jimlongo, Apr 30, 2014.

  1. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    I wonder if anyone would care to help me optimize mysql on my server.

    It is a Centos6.5 VPS with 2MB of RAM.
    For the most part mysql seems to run well. However there are times it will slow down, and memory use will increase so that it seems I need to restart it to get it going again. There are maybe 50 email addresses and 10-20 low traffic Wordpress sites (most of them don't allow comments), less than 5 Drupal sites. Most website tables are INNODB.

    Even though currently the slow log function is off, when enabled nothing writes to the slow.log even though mysql>status will show that there are slow queries.

    Here is my.cnf
    Code:
    [mysqld]
    log-warnings=2
    symbolic-links=0
    
    net_read_timeout=120
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci
    local-infile=0
    
    open_files_limit=3480
    max_connections=50
    
    max_allowed_packet=32M
    wait_timeout=600
    
    innodb_buffer_pool_size=256M
    thread_cache_size=4
    table_open_cache=2100
    table_cache=2250
    query_cache_size=64M
    query_cache_limit=48M
    
    tmp_table_size=256M
    max_heap_table_size=256M
    
    join_buffer_size=148k
    key_buffer_size=24M
    
    general_log=1
    general_log_file=/var/lib/mysql/general.log
    #slow_query_log_file=/var/lib/mysql/slow.log
    #long_query_time=1
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    

    And the latest output of mysqltuner
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 50M (Tables: 24)
    [--] Data in InnoDB tables: 119M (Tables: 1407)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 1410
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 16h 2m 26s (426K q [2.957 qps], 19K conn, TX: 1B, RX: 58M)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 616.0M global + 2.8M per thread (50 max threads)
    [OK] Maximum possible memory usage: 754.5M (36% of installed RAM)
    [OK] Slow queries: 0% (225/426K)
    [OK] Highest usage of available connections: 60% (30/50)
    [OK] Key buffer size / total MyISAM indexes: 24.0M/10.4M
    [OK] Key buffer hit rate: 99.0% (216K cached / 2K reads)
    [OK] Query cache efficiency: 68.4% (230K cached / 336K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
    [OK] Temporary tables created on disk: 15% (1K on disk / 12K total)
    [OK] Thread cache hit rate: 99% (110 created / 19K connections)
    [OK] Table cache hit rate: 56% (2K open / 3K opened)
    [OK] Open file limit used: 2% (104/4K)
    [OK] Table locks acquired immediately: 100% (106K immediate / 106K locks)
    [OK] InnoDB data size / buffer pool: 119.7M/256.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
    
    Thanks for any input.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    In addition to optimizing your MySQL configuration, you may also want to use a command such as "mysqladmin processlist" the next time you notice slowness to see any particular databases are the source of the issue.

    Thank you.
     
  3. server10

    server10 Registered

    Joined:
    May 1, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I see that in your my.cnf file,general_log is set to 1. I suggest you disable general log because it slows down your server very quickly
     
  4. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    Thanks I've done that. I did some research after you comment and didn't realize the performance implications.
     
Loading...

Share This Page