MySQL config and optimization

Question

Here is the info for my server Giga Dedicated 6-Core:

• Intel Core i7 (6 x 3,20GHz)
• 24 GB RAM
• 160 GB SSD + 2000 GB HDD I have CentOS 5.6 (64bit) installed and Plesk Panel 10
• Apache version is 2.2.19
• MySQL version is 5.5
• PHP 5.3.6

I was interested in optimizing mysql server. I will have tens of databses, copule of them are several gigabytes in size.

So I am asking for opinion of my.cnf variable sizes.

Here is my my.cnf:

key_buffer_size = 1024M   
table_cache = 1024   
sort_buffer_size = 32M  
read_buffer_size = 32M  
read_rnd_buffer_size = 16M  
myisam_sort_buffer_size = 128M  
thread_cache_size = 16  
query_cache_size= 64M  
query_cache_limit = 1M  

thread_concurrency = 8

innodb_buffer_pool_size = 500M  
innodb_additional_mem_pool_size = 128M  

max_connections=250

And here is mysqltuner log:

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.5.14  
[OK] Operating on 64-bit architecture  

-------- Storage Engine Statistics -------------------------------------------  
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster   
[--] Data in MyISAM tables: 7G (Tables: 104)  
[--] Data in InnoDB tables: 530M (Tables: 213)  
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)  
[!!] Total fragmented tables: 213  

-------- Security Recommendations  -------------------------------------------  
[OK] All database users have passwords assigned  

-------- Performance Metrics -------------------------------------------------  
[--] Up for: 4s (9 q [2.250 qps], 6 conn, TX: 10K, RX: 695)  
[--] Reads / Writes: 100% / 0%  
[--] Total buffers: 1.7G global + 80.4M per thread (250 max threads)  
[!!] Maximum possible memory usage: 21.3G (90% of installed RAM)  
[OK] Slow queries: 0% (0/9)  
[OK] Highest usage of available connections: 0% (1/250)  
[!!] Key buffer size / total MyISAM indexes: 1.0G/7.2G  
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)  
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)  
[OK] Query cache prunes per day: 0  
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)  
[OK] Thread cache hit rate: 83% (1 created / 6 connections)  
[OK] Table cache hit rate: 78% (26 open / 33 opened)  
[OK] Open file limit used: 0% (18/2K)  
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)  
[!!] Connections aborted: 16%  
[!!] InnoDB data size / buffer pool: 530.1M/500.0M  

-------- Recommendations -----------------------------------------------------  
General recommendations:  
    Run OPTIMIZE TABLE to defragment tables for better performance  
    MySQL started within last 24 hours - recommendations may be inaccurate  
    Reduce your overall MySQL memory footprint for system stability  
    Enable the slow query log to troubleshoot bad queries  
    Your applications are not closing MySQL connections properly  
Variables to adjust:  
    key_buffer_size (> 7.2G)  
    query_cache_limit (> 1M, or use smaller result sets)  
    innodb_buffer_pool_size (>= 530M)  

What would be the best config for my server? What is your opinion, suggestion, experience?

Update:

I have corrected a little my.cnf settings. Here they are:

key_buffer_size = 1024M
table_cache = 1024
sort_buffer_size = 10M
join_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
query_cache_limit = 1M
-#Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 128M
max_connections=250

And answer of the queries that suggest key_buffer_size and innodb_buffer_pool_size are:

+---------------------------------------------------+
| recommended_innodb_buffer_pool_size |
+---------------------------------------------------+
| 1129M |
+---------------------------------------------------+
and
+----------------------------------------+
| recommended_key_buffer_size |
+----------------------------------------+
| 4M |
+----------------------------------------+

Is recommended_key_buffer_size too little? What do you think are the settings good enough? What worries me also is max_connections=250? Is this enough?

Website I host has about 18000 visits and 70000 pageviews in one day.

And here is what the mysqltuner say on these settings:

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.5.14
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 114)
[--] Data in InnoDB tables: 530M (Tables: 219)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 221

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 32m 2s (11K q [0.179 qps], 461 conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 2.2G global + 40.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 12.0G (51% of installed RAM)
[OK] Slow queries: 0% (0/11K)
[OK] Highest usage of available connections: 8% (20/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/3.5M
[OK] Key buffer hit rate: 99.9% (688K cached / 510 reads)
[OK] Query cache efficiency: 57.3% (4K cached / 7K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234 sorts)
[!!] Temporary tables created on disk: 34% (661 on disk / 1K total)
[OK] Thread cache hit rate: 95% (20 created / 461 connections)
[OK] Table cache hit rate: 98% (611 open / 622 opened)
[OK] Open file limit used: 14% (329/2K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 530.2M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
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 (> 16M)
max_heap_table_size (> 16M)

I would really appreciate your help. I know this is a lot of text for you to read but I am just trying to learn something.

Solution

You need to set the MyISAM Key Cache and the InnoDB Buffer Pool to accommodate the data you have. I published two queries in the DBA StackExchange to compute the recommended size for noth of those caches.

For MyISAM, since only index pages are cached, this query will recommend the size of key_buffer_size based on the sum of your .MYI files and cap it at 4G if it exceeds 4G:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.4999), SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_key_buffer_size
FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM
(SELECT SUM(index_length) KBS1 FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA
) A,(SELECT 2 PowerOfTwo) B;

For InnoDB, since data and index pages are cached, this query will recommend the size of innodb_buffer_pool_size based on the sum of your data and index pages:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.49999), SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tablesM
WHERE engine='InnoDB') A,
(SELECT 2 PowerOfTwo) B;

According to your display of mysqltuner.pl, you have about 24GB of RAM, 530MB id InnoDB, 7.2GB of MyISAM indexes. No matter what the recommendations come up as, please just use common sense by setting innodb_buffer_pool_size to a number about 530MB but under 1GB. Since MyISAM doesn't really take advantage of memory in certain instances, you could leave the key_buffer_size to 1024M because mysqltuner.pl says 90% of RAM is used by the DB Connections. The OS needs RAM too.

I would lower these:

[mysqld]
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M

Restart mysql afterwards and run mysqltuner.pl. Your goal here is to get the Maximum possible memory usage under 80% so the OS has breathing room for memory.

CAVEAT : Please excuse the Column called 'PowerOfTwo'. It should really be called PowerOf1024. The purpose of PowerOfTwo is to display recommended answers in GB,MB,KB

• (SELECT 0 PowerOfTwo) displays the value in Bytes
• (SELECT 1 PowerOfTwo) displays the value in KiloBytes
• (SELECT 2 PowerOfTwo) displays the value in MegaBytes
• (SELECT 3 PowerOfTwo) displays the value in GigaBytes

Comments

[pauska]

MySQLtuner clearly states that you should not use this script without understanding what each setting does to your enviroment...