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.

MySQL settings help needed

Discussion in 'Workarounds and Optimization' started by PankajKumar, Jul 1, 2014.

  1. PankajKumar

    PankajKumar Member

    Joined:
    Nov 30, 2013
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi All,

    I recently moved my hosting server and upgraded RAM from 1GB to 3GB. The reason for move was high traffic causing MySQL to go down every 4-5 days.

    When i run mysqltuner on new VPS, it produced following output.

    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.38-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 67M (Tables: 285)
    [--] Data in InnoDB tables: 172M (Tables: 102)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 11
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 9h 34m 42s (1M q [4.225 qps], 42K conn, TX: 18B, RX: 243M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 748.0M global + 8.5M per thread (250 max threads)
    [!!] Maximum possible memory usage: 2.8G (95% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 14% (36/250)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/31.2M
    [OK] Key buffer hit rate: 99.8% (3M cached / 6K reads)
    [OK] Query cache efficiency: 46.3% (1M cached / 2M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 31K sorts)
    [OK] Temporary tables created on disk: 24% (4K on disk / 20K total)
    [OK] Thread cache hit rate: 99% (49 created / 42K connections)
    [OK] Table cache hit rate: 33% (454 open / 1K opened)
    [OK] Open file limit used: 3% (637/16K)
    [OK] Table locks acquired immediately: 99% (258K immediate / 258K locks)
    [OK] InnoDB buffer pool / data size: 256.0M/172.8M
    [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
        Enable the slow query log to troubleshoot bad queries
    
    Maximum possible memory usage is 2.8 GB, that is not good. When I am looking at /etc/my.cnf file, it already has a lot of settings.

    Code:
    [mysqld]
    ## connection settings
    max_connect_errors=400
    max_connections=250
    max_user_connections=50
    wait_timeout=60
    connect_timeout=10
    interactive_timeout=60
    
    ## cache settings
    query_cache_limit=4M
    query_cache_size=96M
    query_cache_type=1
    table_open_cache=1024
    thread_cache_size=8
    
    ## buffer sizes
    key_buffer=256M
    sort_buffer_size=4M
    read_buffer_size=2M
    join_buffer_size=2M
    aria_pagecache_buffer_size = 16m
    aria_sort_buffer_size = 16m
    
    ## tmpdir / temp table sizes
    tmp_table_size=128M
    max_heap_table_size=128M
    
    ## misc. settings
    datadir=/var/lib/mysql
    skip-external-locking
    
    server-id = 1
    open-files-limit = 16384
    max_allowed_packet = 16M
    
    ## innodb settings
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_thread_concurrency = 6
    innodb_buffer_pool_size = 256M
    innodb_log_buffer_size = 4M
    innodb_file_per_table = 1
    
    ## slow query logging
    #log_slow_queries=/var/lib/mysql/slow-queries.log
    #log-long-format
    #long_query_time=5
    
    open_files_limit=4262
    [mysqld_safe]
    open-files-limit = 16384
    
    [mysqldump]
    quick
    max_allowed_packet=32M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 32M
    write_buffer = 16M
    
    [mysql]
    no-auto-rehash
    
    I am not sure which ones to adjust for reducing the memory footprint, usually mysqltuner provide some settings to optimize but it has not done so here. So I am confused and don't want to risk anything by doing something I dont know.

    In my older server, my.cnf was created by me and it was very simple.
    Code:
    [mysqld]
    skip-name-resolve
    max_connections=75
    max_user_connections=50
    open_files_limit=4262
    query_cache_size=16M
    tmp_table_size=8M
    max_heap_table_size=5M
    thread_cache_size=8
    innodb_buffer_pool_size=192M
    key_buffer_size=50M
    sort_buffer_size=256K
    max_allowed_packet=268435456
    
    Please provide some guidance for mysql settings.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    I'm sure you will receive some user-feedback regarding settings to the /etc/my.cnf file, but in the meantime, you can also use a command such as:

    Code:
    mysqladmin processlist
    This might help you pinpoint any particular databases that are using excessive resources.

    Thank you.
     
Loading...

Share This Page