Requesting help optimizing MySQL settings
I hope someone can advise me on optimizing the MySQL settings...
MySQLTuner:
my.conf:
Server details:
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! :)
>> 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! :)
-
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 -
[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 -
Server running noticably smoother now :) 0 -
[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 -
[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.
Comments
5 comments