Skip to main content

Requesting help optimizing MySQL settings

Ebridge
I hope someone can advise me on optimizing the MySQL settings... MySQLTuner: >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.70-cll [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 6244) [--] Data in InnoDB tables: 468M (Tables: 4371) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 5022 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 10d 19h 25m 56s (24M q [25.812 qps], 514K conn, TX: 88B, RX: 8B) [--] Reads / Writes: 60% / 40% [--] Total buffers: 662.0M global + 34.6M per thread (60 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 2.7G (68% of installed RAM) [OK] Slow queries: 0% (9K/24M) [OK] Highest usage of available connections: 48% (29/60) [OK] Key buffer size / total MyISAM indexes: 8.0M/481.9M [OK] Key buffer hit rate: 97.2% (160M cached / 4M reads) [OK] Query cache efficiency: 76.4% (13M cached / 17M selects) [!!] Query cache prunes per day: 58139 [OK] Sorts requiring temporary tables: 1% (5K temp sorts / 470K sorts) [!!] Joins performed without indexes: 71006 [!!] Temporary tables created on disk: 32% (304K on disk / 940K total) [OK] Thread cache hit rate: 99% (4K created / 514K connections) [!!] Table cache hit rate: 0% (5K open / 63M opened) [OK] Open file limit used: 14% (6K/48K) [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) [!!] InnoDB data size / buffer pool: 468.8M/300.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 128M) join_buffer_size (> 30.0M, or always use indexes with joins) tmp_table_size (> 224M) max_heap_table_size (> 224M) table_cache (> 5000) innodb_buffer_pool_size (>= 468M)
my.conf: [mysqld] set-variable = max_connections=60 log-slow-queries safe-show-database max_allowed_packet=16M local-infile=0 query_cache_size = 128M join_buffer_size = 30M tmp_table_size = 224M max_heap_table_size = 224M thread_cache_size = 4 table_cache = 9000 table_open_cache = 5000 table_definition_cache = 3000 innodb_buffer_pool_size = 300M open_files_limit=48542 long_query_time = 1 read_rnd_buffer = 4M sort_buffer_size = 256k
Server details: Total processors: 4 Processor #1 Vendor GenuineIntel Name Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz Speed 2000.000 MHz Cache 2048 KB Processor #2 Vendor GenuineIntel Name Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz Speed 2000.000 MHz Cache 2048 KB Processor #3 Vendor GenuineIntel Name Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz Speed 2000.000 MHz Cache 2048 KB Processor #4 Vendor GenuineIntel Name Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz Speed 2000.000 MHz Cache 2048 KB
Server is running CloudLinux 5 (Hybrid kernel) I went through a couple of runs of MySQLTuner, following it's advice, but I feel like I'm tweaking the settings outside of the sweet spot already. Any help would be highly appreciated! :)

Comments

5 comments

Date Votes
  • thinkbot
    You have 32-bit Linux and system with 4GB RAM For 4GB you should use 64-bit system, [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 2.7G (68% of installed RAM) [--] Total buffers: 662.0M global + 34.6M per thread (60 max threads) But since you have very bad settings (especially for per thread ones) we can optimize max memory usage a lot After the settings below you should have higher memory usage on start, but much slower for each additional thread: 3.6M per thread So in sum, your total memory usage for current set max threads (60) will be much lower than 2GB anways adjust: join_buffer_size = 1M read_rnd_buffer = 2M query_cache_size = 100M tmp_table_size = 25M max_heap_table_size = 25M thread_cache_size = 20 table_cache = 9000 - remove that, its the same as one below table_open_cache = 6000 innodb_buffer_pool_size = 600M This value keep always higher than [!!] InnoDB data size / buffer pool: 468.8M/300.0M than InnoDB data size, so higher than 468 MB in this case add there also key_buffer_size = 500M since [OK] Key buffer size / total MyISAM indexes: 8.0M/481.9M
    0
  • Ebridge
    [quote="thinkbot, post: 1509051"> But since you have very bad settings (especially for per thread ones)
    I was suspecting that, MySQLTuner kept telling me to increase the values so I'm glad I stopped increasing values even more and went to this forum :) I will let it run for a couple of days and report back. Thanks a lot for your advice! Really appreciated.
    0
  • Ebridge
    Server running noticably smoother now :)
    0
  • cPanelMichael
    [quote="Ebridge, post: 1509412">Server running noticably smoother now :)
    I am happy to hear you are have experienced better results. Thank you for updating us with the outcome.
    0
  • Ebridge
    [quote="cPanelMichael, post: 1510831">I am happy to hear you are have experienced better results. Thank you for updating us with the outcome.
    latest output of MySQLTuner >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.70-cll [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 6245) [--] Data in InnoDB tables: 481M (Tables: 4468) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 5123 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 10h 54m 39s (7M q [24.637 qps], 151K conn, TX: 27B, RX: 2B) [--] Reads / Writes: 64% / 36% [--] Total buffers: 1.2G global + 3.6M per thread (60 max threads) [OK] Maximum possible memory usage: 1.4G (35% of installed RAM) [OK] Slow queries: 0% (3K/7M) [OK] Highest usage of available connections: 41% (25/60) [OK] Key buffer size / total MyISAM indexes: 500.0M/480.8M [OK] Key buffer hit rate: 97.9% (60M cached / 1M reads) [OK] Query cache efficiency: 75.2% (4M cached / 5M selects) [!!] Query cache prunes per day: 81101 [OK] Sorts requiring temporary tables: 1% (1K temp sorts / 145K sorts) [!!] Joins performed without indexes: 18607 [!!] Temporary tables created on disk: 32% (113K on disk / 344K total) [OK] Thread cache hit rate: 99% (25 created / 151K connections) [!!] Table cache hit rate: 0% (6K open / 20M opened) [OK] Open file limit used: 15% (7K/48K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) [OK] InnoDB data size / buffer pool: 481.5M/600.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 100M) join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 25M) max_heap_table_size (> 25M) table_cache (> 6000)
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post