mustafamsy

Registered
Apr 11, 2012
4
0
51
cPanel Access Level
Root Administrator
Hi there

I really need the best optimization/configuration for "my.cnf" - MySQL 5.5 - CentOS 5.8

Currently for my.cnf I have the following:
Code:
[mysqld]
max_connections=500
join_buffer_size = 2M
tmp_table_size = 24M
max_heap_table_size = 24M
query_cache_size = 256M
key_buffer=256M
key_buffer_size = 1332M
thread_cache_size = 4
table_cache = 500
table_open_cache = 96
innodb_buffer_pool_size = 27M
slow_query_log
local-infile=0
Processor Information
Code:
Total processors: 4
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
Speed
1998.000 MHz
Cache
2048 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
Speed
1998.000 MHz
Cache
2048 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
Speed
1998.000 MHz
Cache
2048 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
Speed
1998.000 MHz
Cache
2048 KB
Memory Information
Code:
Memory for crash kernel (0x0 to 0x0) notwithin permissible range
Memory: 8165712k/9175040k available (2574k kernel code, 212064k reserved, 1304k data, 212k init)

MySQLTuner
Code:
[email protected] [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >>  MySQLTuner 1.1.2 - 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.5.21-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 7422)
[--] Data in InnoDB tables: 1M (Tables: 21)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 994K (Tables: 59)
[!!] Total fragmented tables: 482

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 30m 14s (1M q [127.577 qps], 18K conn, TX: 30B, RX: 131M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 1.6G global + 4.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.9G (49% of installed RAM)
[OK] Slow queries: 0% (9/1M)
[OK] Highest usage of available connections: 5% (29/500)
[OK] Key buffer size / total MyISAM indexes: 1.3G/1.3G
[OK] Key buffer hit rate: 96.5% (7M cached / 280K reads)
[OK] Query cache efficiency: 80.9% (782K cached / 967K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (27 temp sorts / 49K sorts)
[!!] Joins performed without indexes: 2248
[!!] Temporary tables created on disk: 26% (17K on disk / 65K total)
[OK] Thread cache hit rate: 96% (570 created / 18K connections)
[!!] Table cache hit rate: 0% (96 open / 101K opened)
[OK] Open file limit used: 4% (181/4K)
[OK] Table locks acquired immediately: 99% (329K immediate / 330K locks)
[OK] InnoDB data size / buffer pool: 1.3M/27.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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:
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 24M)
    max_heap_table_size (> 24M)
    table_cache (> 96)
Thank you for your help!!

~Mustafa
 

mustafamsy

Registered
Apr 11, 2012
4
0
51
cPanel Access Level
Root Administrator
Code:
[email protected] [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >>  MySQLTuner 1.1.2 - 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.5.21-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 7425)
[--] Data in InnoDB tables: 34M (Tables: 247)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 61)
[!!] Total fragmented tables: 732

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 19h 20m 20s (217M q [285.805 qps], 2M conn, TX: 3499B, RX: 19B)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 1.6G global + 4.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.9G (49% of installed RAM)
[OK] Slow queries: 0% (274/217M)
[OK] Highest usage of available connections: 51% (255/500)
[OK] Key buffer size / total MyISAM indexes: 1.3G/1.3G
[OK] Key buffer hit rate: 95.3% (1B cached / 71M reads)
[OK] Query cache efficiency: 70.5% (107M cached / 152M selects)
[!!] Query cache prunes per day: 210621
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 5M sorts)
[!!] Joins performed without indexes: 247029
[OK] Temporary tables created on disk: 20% (895K on disk / 4M total)
[OK] Thread cache hit rate: 94% (130K created / 2M connections)
[!!] Table cache hit rate: 0% (96 open / 11M opened)
[OK] Open file limit used: 7% (181/2K)
[!!] Table locks acquired immediately: 77%
[!!] InnoDB data size / buffer pool: 34.9M/27.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_cache (> 96)
    innodb_buffer_pool_size (>= 34M)

Up
 

NetMantis

BANNED
Apr 22, 2012
116
1
66
Utah
cPanel Access Level
DataCenter Provider
The correct configuration for my.cnf is going to vary greatly from server machine to server machine.

The values you want to put in there really depends heavily on both your usage and the resources that are available on your server machine so it would be unwise to use just any generic my.cnf posted somewhere built for some other server.

Looking at your my.cnf posted in your first post above and knowing you are MySQL 5.5, I do see a few obvious items:

You put this:
Code:
thread_cache_size = 4
Instead, you should try this (16K is the maximum for this item):
Code:
thread_cache_size = 16K
You are not making any real use of the multi-core processors above so add this:
Code:
low_priority_updates=1
concurrent_insert=ALWAYS
You have this in your my.cnf configuration which is a bit too low:
Code:
tmp_table_size = 24M
max_heap_table_size = 24M
For a good starting point with your memory, change both of those items above to 64M and then you can adjust those two upward as needed up to around 128M would be good if you have a lot of database activity going on particular the type that deals with creating tmp tables in memory.

You tried to set your query_cache_size to "256M" but failed to setup query_cache_limit so your actual functional query cache is only 1 MB instead of 256 MB like you had intended plus 256 is usually a bit too large even if you had set it up correctly.

Replace your query_cache_size line to the following:
Code:
query_cache_size = 128M
query_cache_limit = 148M
The above may even be a bit large or could be small depending on how your server is used and what sort of databases you keep on your server but is a decent starting point for a server with your memory and processor resources. I generally make the query_cache_limit just slightly larger than the base query_cache_size and MySQL scales the cache up to those limits as needed.

You wrote this in your my.cnf file which is really the same thing for both lines though the first line is a typographical error and would likely keep your MySQL server from properly starting up:
Code:
table_cache = 500
table_open_cache = 96
So you are either trying to set your table_cache to 500 or 96. Both of those are extremely small and would indicate that you do not plan on having very many database tables on your server.

What you need to do is find out how many tables you have on your server and then set a table_cache number about 20% roughly higher than that number and then table_definition_cache will be 4x that number and your open_files_limit would be 3x the table_cache number.

For example, lets say you had 862 tables on your server, then you might use a table_cache of 1K (shorthand for 1,024) which is just a bit larger than 862 and then the table_definition cache would then be 4K and the open_files_limit would be 3K.

Example of the lines needed for your table cache setup:
Code:
table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K
In your my.cnf, you wrote essentially the same thing two different ways in another section:
Code:
key_buffer=256M
key_buffer_size = 1332M
Both of these are quite large and could actually slow down your server. We'll ignore the 1332M you setup and go with the 256M. Even at the 256M, it is still quite large unless you have some very large MyISAM based databases; then in that case it might be warranted to go that high with the key_buffer size.

As a general decent starting point, I would typically start around 64M for the key_buffer and then move up or down as needed depending on the amount of MyISAM indexing you have on your server.

Should look more like this:
Code:
key_buffer = 64M
I notice you put no limits on query execution and that could actually impact performance a bit.

You might want to do something like this:
Code:
long_query_time = 5
Setting a 5 second limit which is generally more than sufficient for most queries on most databases

You also have a join_buffer_size of 2 MB. For your server size, using 3 MB might be better but go no higher than that!

The MySQL tuner you downloaded is generally good as a performance analyzer for tweaking on fine tuning an existing MySQL configuration at a later date but not really that good at setting up your initial configuration.

For initial tuning especially with finding the correct table_cache value, I would recommend running Matthew Montgomery's tuning-primer.sh script after you make the initial setup changes that I told you about above and that will help you get more exact values for your specific server.

The link to where to find the tuning primer script is here --> https://launchpad.net/mysql-tuning-primer

All of this should make a very noticeable difference in the performance of your MySQL server!
 

XenomediaBV

Well-Known Member
Sep 3, 2009
60
0
56
The Netherlands
cPanel Access Level
Root Administrator
This is actually not correct:
Code:
query_cache_size = 128M
query_cache_limit = 148M
make the query_cache_limit just slightly larger than the base query_cache_size and MySQL scales the cache up to those limits as needed.
MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables
Do not cache results that are larger than this number of bytes. The default value is 1MB.
The query cache is used to store queries with their results that are the same each time they are requested. So the results will always be the same until the table structure or its data changes. Those query/result-sets are candidates for caching.

This means that with the above settings MySQL will try to push query/result-sets up to 148M into the available query_cache_size. Everything that is above 128M will be dropped directly. Each query/result-set below 128M will be pushed into the cache. So, when you have two query/result-sets of 64M each your cache is stuffed and the "pruning" starts with a third query/result-set that is a cache candidate. Pruning means that the oldest cached query/result-set is purged out of the cache to make space for the newest candidate.

Something like below is more efficient:
Code:
query_cache_size = 128M
query_cache_limit = 4M
With a query_cache_size of 128M the cache can hold up to 32 query/result-sets and most likely a lot more because not all query/result-sets require 4M. You can tune the query_cache_limit a little higher if you have a lot of larger query/result-sets, but not too high since the query_cache_size is advised to have a maximum of 128M. Or of course lower since the default is 1M to cache more smaller queries/result-sets.

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
 
Last edited: