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 Server Optimization - Only using 6% installed RAM

Discussion in 'Workarounds and Optimization' started by djblamire, Mar 23, 2014.

  1. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    I have just purchased a new dedicated server and currently using the defaults for MySQL and Apache. I'm looking to try and optimize the setup as best I can.

    Here is the spec of the machine:

    Intel Xeon E3 1225v2 3.25Ghz
    4 Cores / 4 Threads
    32GB RAM
    CentOS 6.5 - 64-Bit

    The spec is similar to my previous machine (i7-2600), except that had only 8GB RAM and this one has 32GB of RAM, but the server load seems to be generally higher - I did do some tweaks on the previous machine, and have made the same changes on this server.

    Here is the contents of /etc/my.cnf

    Code:
    Code:
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    open_files_limit=10000
    myisam_use_mmap=1
    connect_timeout = 2
    join_buffer_size=2M
    read_rnd_buffer_size=4M
    query_cache_type = 1
    query_cache_size = 100M
    query_cache_limit = 30M
    max_heap_table_size = 100M
    tmp_table_size = 100M
    thread_cache_size = 50
    table_open_cache = 2000
    slow_query_log=0
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    key_buffer_size = 500M
    concurrent_insert=2
    Even though I've made these changes (only recently - so cannot take complete notice of mysqltuner.pl for the results, it is showing:

    [OK] Maximum possible memory usage: 2.1G (6% of installed RAM)

    I would expect the server should be set to be allowed to use far more than a maxmimum of 2.1GB on a 32GB machine.

    I have run /usr/bin/mysqltuner.pl and this is what is showing (for info only, as it has only been running for 45 minutes since I made the changes).


    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: 21M (Tables: 185)
    [--] Data in InnoDB tables: 1M (Tables: 113)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [OK] Total fragmented tables: 0
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 46m 16s (9K q [3.418 qps], 2K conn, TX: 21M, RX: 1M)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 844.0M global + 8.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
    [!!] Slow queries: 14% (1K/9K)
    [OK] Highest usage of available connections: 3% (5/151)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/8.4M
    [!!] Key buffer hit rate: 21.6% (19K cached / 14K reads)
    [!!] Query cache efficiency: 18.2% (430 cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 2% (9 on disk / 309 total)
    [OK] Thread cache hit rate: 99% (5 created / 2K connections)
    [!!] Table cache hit rate: 16% (134 open / 808 opened)
    [OK] Open file limit used: 0% (7/10K)
    [OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
    [OK] InnoDB data size / buffer pool: 1.8M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_limit (> 30M, or use smaller result sets)
        table_cache (> 2000)

    Thanks in advance,

    Daniel
     
    #1 djblamire, Mar 23, 2014
    Last edited: Mar 23, 2014
  2. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    After almost 2 days, this is what it is currently showing on mysqltuner:

    Code:
    >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- 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: 31M (Tables: 185)
    [--] Data in InnoDB tables: 1M (Tables: 113)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 3
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 17h 38m 54s (607K q [4.055 qps], 150K conn, TX: 1B, RX: 94M)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 844.0M global + 8.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
    [!!] Slow queries: 13% (80K/607K)
    [OK] Highest usage of available connections: 5% (8/151)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/10.9M
    [OK] Key buffer hit rate: 95.1% (335K cached / 16K reads)
    [OK] Query cache efficiency: 35.3% (57K cached / 162K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (251 temp sorts / 99K sorts)
    [!!] Temporary tables created on disk: 28% (1K on disk / 4K total)
    [OK] Thread cache hit rate: 99% (8 created / 150K connections)
    [OK] Table cache hit rate: 30% (525 open / 1K opened)
    [OK] Open file limit used: 6% (604/10K)
    [OK] Table locks acquired immediately: 99% (125K immediate / 125K locks)
    [OK] InnoDB data size / buffer pool: 1.8M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 100M)
        max_heap_table_size (> 100M)
     
  3. gopkris2005

    gopkris2005 Well-Known Member

    Joined:
    Jan 9, 2007
    Messages:
    61
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    INDIA
    cPanel Access Level:
    Root Administrator
    Twitter:
    Paste here the following command result.

    mysqladmin proc

    It should be need to change depending on your query and RAM size. Anyway, If possible try the following changes

    join_buffer_size=3M
    read_rnd_buffer_size=3M
    max_heap_table_size = 2048M
    tmp_table_size = 2048M
    key_buffer_size = 5120M
    max_connections =500
    sort_buffer_size=3M
    thread_stack=1M
    innodb_buffer_pool_size=10M
    innodb_log_buffer_size=2M
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    Hi,

    Thanks for your replies.

    I've only just made the changes now so will need to see how it goes, but it does show that it can use up to a maximum of 40% of installed RAM (rather than 6% before). Although there were not issues before, should it have got busy the RAM usage was being heavily restricted.

    The /etc/my.cnf file now looks like this:

    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    open_files_limit=10000
    myisam_use_mmap=1
    connect_timeout=2
    join_buffer_size=3M
    read_rnd_buffer_size=4M
    query_cache_type=1
    query_cache_size=100M
    query_cache_limit=2048M
    max_heap_table_size=2048M
    tmp_table_size=2048M
    thread_cache_size=50
    table_open_cache=2000
    slow_query_log=0
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    key_buffer_size=5120M
    concurrent_insert=2
    max_connections=500
    sort_buffer_size=3M
    thread_stack=1M
    innodb_buffer_pool_size=10M
    innodb_log_buffer_size=2M


    The result of the command 'mysqladmin proc' as requested above is as follows (Although I'm not sure what help this information will be).


    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
    | 10 | eximstats | localhost | eximstats | Sleep | 71 | | |
    | 11 | DELAYED | localhost | eximstats | Delayed insert | 71 | Waiting for INSERT | |
    | 57 | root | localhost | | Query | 0 | | show processlist |
    +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+

    I'll post back the results of mysqltuner after it has been running for at least 24 hours. Any further comments/suggestions are appreciated.

    Thanks
     
  6. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    After making the above changes, and the server being up and running for 6 days, the following is the results of the mysqltuner.

    Any other suggestions or improvements ??

    Thanks in advance

    Code:
    >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- 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: 209M (Tables: 185)
    [--] Data in InnoDB tables: 1M (Tables: 113)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 4
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 12h 28m 6s (2M q [4.273 qps], 528K conn, TX: 4B, RX: 356M)
    [--] Reads / Writes: 72% / 28%
    [--] Total buffers: 7.1G global + 11.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 12.5G (40% of installed RAM)
    [!!] Slow queries: 11% (274K/2M)
    [OK] Highest usage of available connections: 2% (11/500)
    [OK] Key buffer size / total MyISAM indexes: 5.0G/56.7M
    [OK] Key buffer hit rate: 99.6% (1M cached / 6K reads)
    [OK] Query cache efficiency: 51.5% (399K cached / 776K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (749 temp sorts / 329K sorts)
    [!!] Temporary tables created on disk: 30% (4K on disk / 14K total)
    [OK] Thread cache hit rate: 99% (11 created / 528K connections)
    [OK] Table cache hit rate: 50% (525 open / 1K opened)
    [OK] Open file limit used: 6% (604/10K)
    [OK] Table locks acquired immediately: 99% (537K immediate / 538K locks)
    [OK] InnoDB data size / buffer pool: 1.8M/10.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
     
  7. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    After MySQL being up now for 33 days, the results of the MySQLTuner are shown below.

    I see there are still some areas that can be optimized further.

    Any suggestions/ideas on how to improve things further ??

    Thanks in advance


    Code:
    >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- 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: 183M (Tables: 185)
    [--] Data in InnoDB tables: 1M (Tables: 113)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [OK] Total fragmented tables: 0
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 33d 23h 42m 27s (13M q [4.589 qps], 3M conn, TX: 26B, RX: 2B)
    [--] Reads / Writes: 73% / 27%
    [--] Total buffers: 7.1G global + 11.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 12.5G (40% of installed RAM)
    [!!] Slow queries: 11% (1M/13M)
    [OK] Highest usage of available connections: 2% (11/500)
    [OK] Key buffer size / total MyISAM indexes: 5.0G/37.2M
    [OK] Key buffer hit rate: 97.9% (17M cached / 377K reads)
    [OK] Query cache efficiency: 49.0% (2M cached / 4M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
    [!!] Temporary tables created on disk: 30% (21K on disk / 70K total)
    [OK] Thread cache hit rate: 99% (11 created / 3M connections)
    [!!] Table cache hit rate: 3% (153 open / 4K opened)
    [OK] Open file limit used: 0% (32/10K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 1.8M/10.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Enable the slow query log to troubleshoot bad queries
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        table_cache (> 2000)
     
  8. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    I was just wondering if anyone had any other suggested improvements ?

    Thanks in advance,

    Daniel
     
Loading...

Share This Page