Requesting help optimizing MySQL settings

Ebridge · Nov 15, 2013

I hope someone can advise me on optimizing the MySQL settings...

MySQLTuner:

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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:

Code:

[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:

Code:

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!

thinkbot · Nov 15, 2013

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

Ebridge · Nov 15, 2013

thinkbot said:
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.

Ebridge · Nov 16, 2013

Server running noticably smoother now

cPanelMichael · Nov 19, 2013

Ebridge said:
Server running noticably smoother now

I am happy to hear you are have experienced better results. Thank you for updating us with the outcome.

Ebridge · Nov 20, 2013

cPanelMichael said:
I am happy to hear you are have experienced better results. Thank you for updating us with the outcome.

latest output of MySQLTuner

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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)

Thread starter	Similar threads	Forum	Replies	Date
S	I need help optimizing MySQL	Workarounds and Optimization	5	Nov 20, 2022
S	How to optimize mysql - help	Workarounds and Optimization	2	Mar 11, 2020
M	Help on DB optimization	Workarounds and Optimization	3	Feb 24, 2020
T	SOLVED New to MySQL optimization - need help!	Workarounds and Optimization	3	Jan 21, 2020
E	Help Optimizing MySQL	Workarounds and Optimization	2	Dec 28, 2017

Search

Search

Requesting help optimizing MySQL settings

Ebridge

Member

thinkbot

Well-Known Member

Ebridge

Member

Ebridge

Member

cPanelMichael

Administrator

Ebridge

Member