mysql Tuning

[Gibby]

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

[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

 >>  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]

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

 

[Gibby]

...
long_query_time=0.1
...

Is that correct? 0.1 seconds?

 

[thinkbot]

yes, to track all queries running longer than 0.1s
and the ones not using indexes

 

[cPanelMichael]

Hello

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

Thank you.

 

[Gibby]

Hello

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

Thank you.

How many days worth of data should we wait for?

 

[thinkbot]

24h is great

 

[Gibby]

I know it has been longer than 24h, still going to post the output though. Thanks in advance!

 >>  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)