Dedicated server MySQL Optimization (mysqltuner.pl results)

ES - George

Well-Known Member
PartnerNOC
Jun 12, 2011
178
25
78
UK
cPanel Access Level
DataCenter Provider
Twitter
Hi,

I've got a dedicated server with 8GB RAM and an Xeon X3440 w/ HT (8 CPUs). I've done a few things to optimize MySQL. Here's the results of the tuning script.

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 336M (Tables: 4687)
[--] Data in InnoDB tables: 6M (Tables: 256)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 7)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 28m 46s (841K q [31.256 qps], 35K conn, TX: 3B, RX: 148M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 222.0M global + 2.9M per thread (151 max threads)
[OK] Maximum possible memory usage: 656.1M (8% of installed RAM)
[OK] Slow queries: 0% (0/841K)
[OK] Highest usage of available connections: 10% (16/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/143.7M
[OK] Key buffer hit rate: 97.0% (14M cached / 435K reads)
[OK] Query cache efficiency: 69.2% (410K cached / 593K selects)
[!!] Query cache prunes per day: 48661
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 19K sorts)
[!!] Temporary tables created on disk: 36% (7K on disk / 20K total)
[OK] Thread cache hit rate: 99% (82 created / 35K connections)
[!!] Table cache hit rate: 0% (317 open / 54K opened)
[OK] Open file limit used: 0% (119/32K)
[OK] Table locks acquired immediately: 99% (331K immediate / 331K locks)
[OK] InnoDB data size / buffer pool: 6.5M/128.0M

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

[email protected] [~]#

I can see that the MySQL hasn't been up for the recommended 24 hours yet, however the results will be very similar if not the same when 24 hours has passed.

Here's my my.cnf file.
[mysqld]
query_cache_size=50M
local-infile=0
innodb_file_per_table=1
thread_cache_size=4
default-storage-engine=MyISAM
open-files-limit = 32000
tmp_table_size = 30M
max_heap_table_size = 20M
table_cache = 400
log-slow-queries=/var/lib/mysql/slow.log
join_buffer_size = 256K
Any suggestions to improve the configuration are appreciated.

Thank you.
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Code:
query_cache_size=96M
tmp_table_size = 32M
max_heap_table_size = 32M
--

Add This line
Code:
key_buffer_size = 160M
--

rename table_cache to
table_open_cache

and change it to
Code:
table_open_cache 4096
I would imagine it's at 54K because of accessing phpmyadmin and doing changes there so 4096 should be plenty.

--

This seems strange because you didn't post this in your mysql config
[OK] InnoDB data size / buffer pool: 6.5M/128.0M

128M is not needed here you only have 6.5M
Change or add this line.
Code:
innodb_buffer_pool_size=8M
---

[!!] Temporary tables created on disk: 36% (7K on disk / 20K total)

This line may be unimportant because you may have many blog or text fields. However if you want you can try to make these higher
Code:
tmp_table_size = 32M
max_heap_table_size = 32M
they both should be the same value. I would go up to 64M if you don't see an improvement in the 36% (lower than 36%) than go back to 32M

Also update mysql tuner to the lastest 1.2 version

re-run a new report after 24 hours.
 
Last edited:

ES - George

Well-Known Member
PartnerNOC
Jun 12, 2011
178
25
78
UK
cPanel Access Level
DataCenter Provider
Twitter
Code:
query_cache_size=96M
tmp_table_size = 32M
max_heap_table_size = 32M
--

Add This line
Code:
key_buffer_size = 160M
--

rename table_cache to
table_open_cache

and change it to
Code:
table_open_cache 4096
I would imagine it's at 54K because of accessing phpmyadmin and doing changes there so 4096 should be plenty.

--

This seems strange because you didn't post this in your mysql config
[OK] InnoDB data size / buffer pool: 6.5M/128.0M

128M is not needed here you only have 6.5M
Change or add this line.
Code:
innodb_buffer_pool_size=8M
---

[!!] Temporary tables created on disk: 36% (7K on disk / 20K total)

This line may be unimportant because you may have many blog or text fields. However if you want you can try to make these higher
Code:
tmp_table_size = 32M
max_heap_table_size = 32M
they both should be the same value. I would go up to 64M if you don't see an improvement in the 36% (lower than 36%) than go back to 32M

Also update mysql tuner to the lastest 1.2 version

re-run a new report after 24 hours.
Thanks!

How do I update the mysqltuner?
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
You can get the latest at
http://mysqltuner.pl/mysqltuner.pl

simply do a wget and put it somewhere safe. than you can run it directly from that folder.

You would run it with a command like below.
Code:
./folder/full_path_to_script/mysqltuner.pl
also make sure that the file is owned by root and can be executed.