Skip to main content

MySQL Server Optimization - Only using 6% installed RAM

HappymanUK
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: [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). -------- 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

Comments

7 comments

Date Votes
  • HappymanUK
    After almost 2 days, this is what it is currently showing on mysqltuner: >> MySQLTuner 1.2.0_1 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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)
    0
  • gopkris2005
    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
    0
  • cPanelMichael
    Hello :) Did you end up making any changes to your MySQL configuration file? If so, did you notice improved performance? Thank you.
    0
  • HappymanUK
    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
    0
  • HappymanUK
    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 >> MySQLTuner 1.2.0_1 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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
    0
  • HappymanUK
    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 >> MySQLTuner 1.2.0_1 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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)
    0
  • HappymanUK
    I was just wondering if anyone had any other suggested improvements ? Thanks in advance, Daniel
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post