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:
    166
    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:
    166
    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:
    62
    Likes Received:
    0
    Trophy Points:
    156
    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:
    35,700
    Likes Received:
    1,139
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello :)

    Did you end up making any changes to your MySQL configuration file? If so, did you notice improved performance?

    Thank you.
     
  5. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    166
    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:
    166
    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:
    166
    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:
    166
    I was just wondering if anyone had any other suggested improvements ?

    Thanks in advance,

    Daniel
     
Loading...

Share This Page