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.

Cent OS 5.10 MySQL optimization

Discussion in 'Workarounds and Optimization' started by johnchristy, Apr 22, 2014.

  1. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    hi guyz

    I ran MySQL tuner and here is my result :

    Code:
    
     >>  MySQLTuner 1.3.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
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 15M (Tables: 163)
    [--] Data in InnoDB tables: 10M (Tables: 603)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5s (23 q [4.600 qps], 6 conn, TX: 21K, RX: 1K)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 684.0M global + 6.4M per thread (151 max threads)
    [!!] Maximum possible memory usage: 1.6G (109% of installed RAM)
    [OK] Slow queries: 0% (0/23)
    [OK] Highest usage of available connections: 0% (1/151)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/2.9M
    [!!] Key buffer hit rate: 88.9% (9 cached / 1 reads)
    [!!] Query cache efficiency: 0.0% (0 cached / 12 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
    [OK] Thread cache hit rate: 83% (1 created / 6 connections)
    [OK] Table cache hit rate: 82% (33 open / 40 opened)
    [OK] Open file limit used: 0% (32/3K)
    [OK] Table locks acquired immediately: 100% (45 immediate / 45 locks)
    [OK] InnoDB buffer pool / data size: 256.0M/10.7M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 1M, or use smaller result sets)
    
    my.cnf :

    Code:
    [myisamchk]
    write_buffer=2M
    sort_buffer_size=128M
    key_buffer=128M
    read_buffer=2M
    [mysqld]
    federated
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    thread_concurrency=2
    local-infile=0
    innodb_log_file_size=64M
    table_cache=128
    tmpdir="/dev/shm"
    innodb_log_buffer_size=8M
    innodb_additional_mem_pool_size=20M
    innodb_buffer_pool_size=256M
    innodb_flush_log_at_trx_commit=1
    read_rnd_buffer_size=4M
    skip-external-locking
    innodb_data_home_dir="/var/lib/mysql/"
    thread_cache_size=8
    innodb_log_group_home_dir="/var/lib/mysql/"
    sort_buffer_size=1M
    key_buffer=256M
    innodb_data_file_path="ibdata1:10M:autoextend"
    innodb_lock_wait_timeout=50
    max_allowed_packet=1M
    query_cache_size=128M
    myisam_sort_buffer_size=64M
    read_buffer_size=1M
    port=3306
    open_files_limit=3608
    [isamchk]
    write_buffer=2M
    sort_buffer_size=128M
    key_buffer=128M
    read_buffer=2M
    [mysqlhotcopy]
    interactive-timeout
    
    My VPS RAM is 1.5, can someone point me to what are the changes i should make to my my.cnf? I use prestashop/magento etc and need good mysql performance

    Thanks
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. johnchristy

    johnchristy Active Member

    Joined:
    Mar 7, 2014
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello Michael, Kindly refer this log :

    Code:
     >>  MySQLTuner 1.3.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
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 15M (Tables: 163)
    [--] Data in InnoDB tables: 10M (Tables: 603)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 2
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 21h 11m 29s (76K q [0.468 qps], 6K conn, TX: 64M, RX: 8M)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 684.0M global + 6.4M per thread (151 max threads)
    [!!] Maximum possible memory usage: 1.6G (109% of installed RAM)
    [OK] Slow queries: 0% (0/76K)
    [OK] Highest usage of available connections: 5% (8/151)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/3.0M
    [OK] Key buffer hit rate: 99.0% (81K cached / 849 reads)
    [OK] Query cache efficiency: 86.3% (41K cached / 48K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 757 sorts)
    [OK] Temporary tables created on disk: 21% (421 on disk / 1K total)
    [OK] Thread cache hit rate: 99% (8 created / 6K connections)
    [!!] Table cache hit rate: 4% (128 open / 3K opened)
    [OK] Open file limit used: 3% (137/3K)
    [OK] Table locks acquired immediately: 99% (16K immediate / 16K locks)
    [OK] InnoDB buffer pool / data size: 256.0M/10.7M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        table_cache (> 128)
    
     
Loading...

Share This Page