Gibby

Member
Feb 24, 2012
9
0
51
cPanel Access Level
DataCenter Provider
I have been using mysqltuner.pl and reading through the forums and other blogs trying to get mysql tweaked just right for our servers.

The server I am testing on has 4G RAM, 4G Swap and 4x 2.1Ghz CPU's.

my.cnf
Code:
[mysqld]
local-infile=0                  # security tweak

max_connections         = 250   # cPanel default is 500
max_allowed_packet      = 16M   # Default 1M

slow-query-log                  # because enquiring minds want to know.
log-slow-queries        = /var/lib/mysql/slow.log # Where to log slow queries
long-query-time         = 10     # Default 10

######### Performance Tweaks ##########

thread_cache_size       = 4     # Default 0
table_cache             = 1024  # Default 64

key_buffer_size         = 1024M # Default 8M
sort_buffer_size        = 256K  # Default 2M
join_buffer_size        = 512K  # Default 128K
read_buffer_size        = 256K  # Default 128K.

query_cache_size        = 16M   # Default 0

######### End Performance Tweaks #######
open_files_limit=8030
mysqltuner.pl
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
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 408M (Tables: 1190)
[--] Data in InnoDB tables: 7M (Tables: 331)
[--] Data in MEMORY tables: 0B (Tables: 12)
[!!] Total fragmented tables: 369

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 1h 21m 8s (1M q [1.080 qps], 93K conn, TX: 1B, RX: 189M)
[--] Reads / Writes: 49% / 51%
[--] Total buffers: 1.0G global + 1.5M per thread (250 max threads)
[OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 3% (8/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/176.1M
[OK] Key buffer hit rate: 96.2% (9M cached / 371K reads)
[OK] Query cache efficiency: 70.1% (370K cached / 528K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (111 temp sorts / 41K sorts)
[!!] Joins performed without indexes: 15222
[OK] Temporary tables created on disk: 16% (12K on disk / 73K total)
[OK] Thread cache hit rate: 99% (33 created / 93K connections)
[!!] Table cache hit rate: 0% (1K open / 535K opened)
[OK] Open file limit used: 21% (1K/8K)
[OK] Table locks acquired immediately: 99% (407K immediate / 407K locks)
[OK] InnoDB data size / buffer pool: 7.0M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_cache (> 1024)


Any input would be appreciated!

Thanks.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
its quite ok, you have not many queries

just add some code to track slow queries, those not using indexes and those using temp tables
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,203
363
Hello :)

Let us know the outcome after you have had some time to track the slow queries.

Thank you.
 

Gibby

Member
Feb 24, 2012
9
0
51
cPanel Access Level
DataCenter Provider
I know it has been longer than 24h, still going to post the output though. Thanks in advance!

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 446M (Tables: 1328)
[--] Data in MRG_MYISAM tables: 87M (Tables: 16)
[--] Data in InnoDB tables: 7M (Tables: 341)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 12)
[!!] Total fragmented tables: 85

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 7h 28m 34s (38M q [18.168 qps], 1M conn, TX: 34B, RX: 4B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 1.2G global + 1.5M per thread (250 max threads)
[OK] Maximum possible memory usage: 1.5G (38% of installed RAM)
[OK] Slow queries: 0% (276K/38M)
[OK] Highest usage of available connections: 12% (32/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/108.6M
[OK] Key buffer hit rate: 96.9% (41M cached / 1M reads)
[OK] Query cache efficiency: 91.4% (28M cached / 31M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 653K sorts)
[!!] Joins performed without indexes: 23734
[OK] Temporary tables created on disk: 12% (103K on disk / 818K total)
[OK] Thread cache hit rate: 99% (3K created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 12M opened)
[OK] Open file limit used: 21% (1K/8K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 7.6M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_cache (> 1024)