MySQL Tuning.

[Slavik]

Looking to optimise my server a little as i'm going to be moving a Megento store (or 3) onto it soon, already hosting several XenForo sites and several wordpress sites with no issues.

Its Quad core with 12gb Ram.

Current my.cnf

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
query_cache_type=1
query_cache_size=64M
table_open_cache=16384
table_definition_cache=6384
tmp_table_size=64M
join_buffer_size=512k

Mysql tuner

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  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.61
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 614M (Tables: 1416)
[--] Data in InnoDB tables: 1G (Tables: 2446)
[--] Data in MEMORY tables: 1M (Tables: 42)
[!!] Total fragmented tables: 2612
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 7h 4m 57s (18M q [18.815 qps], 721K conn, TX: 160B, RX: 4B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (3% of installed RAM)
[OK] Slow queries: 0% (171/18M)
[OK] Highest usage of available connections: 29% (44/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/430.4M
[OK] Key buffer hit rate: 95.9% (181M cached / 7M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 13899
[!!] Temporary tables created on disk: 33% (239K on disk / 723K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 1M opened)
[OK] Open file limit used: 0% (74/65K)
[OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
[!!] InnoDB data size / buffer pool: 1.6G/8.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 1G)

Mysql primer

        -- MYSQL PERFORMANCE TUNING PRIMER --
            - By: Matthew Montgomery -
 
MySQL Version 5.1.61 x86_64
 
Uptime = 11 days 7 hrs 3 min 52 sec
Avg. qps = 18
Total Questions = 18359638
Threads Connected = 7
 
Server has been running for over 48hrs.
It should be safe to follow these recommendations
 
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
 
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 171 out of 18359659 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
 
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
 
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 3
Historic threads_per_sec = 0
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size
 
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 7
Historic max_used_connections = 44
The number of used connections is 29% of the configured maximum.
Your max_connections variable seems to be fine.
 
INNODB STATUS
Current InnoDB index space = 343 M
Current InnoDB data space = 1.59 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
 
MEMORY USAGE
Max Memory Ever Allocated : 138 M
Configured Max Per-thread Buffers : 415 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 433 M
Physical Memory : 11.61 G
Max memory limit seem to be within acceptable norms
 
KEY BUFFER
Current MyISAM index space = 430 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 24
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
 
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
 
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
 
JOINS
Current join_buffer_size = 132.00 K
You have had 13596 queries where a join could not use an index properly
You have had 303 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
 
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
 
OPEN FILES LIMIT
Current open_files_limit = 65535 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
 
TABLE CACHE
Current table_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 3927 tables
You have 64 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.
 
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 484516 temp tables, 33% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
 
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 20 : 1
read_buffer_size seems to be fine
 
TABLE LOCKING
Current Lock Wait ratio = 1 : 69809
Your table locking seems to be fine

Proposed new my.cnf

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
table_open_cache=8192
thread_cache_size=4
max_connections=200
innodb_buffer_pool_size=4096M
query_cache_type=1
query_cache_size=256M
query_cache_limit=8M
join_buffer_size=1M
table_definition_cache=6384
tmp_table_size=512M
max_heap_table_size=512M
innodb_thread_concurrency=10
key_buffer_size=512M
max_allowed_packet=32M
sort_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=64M
myisam_sort_buffer_size=64M
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Anyone spot anything glaringly obvious i've missed or messed up in the proposed new my.cnf before I commit?

Cheers

//Slav

 

[robdog]

Damn that is a beefy server, what are your current response times right now?

 

[Slavik]

Damn that is a beefy server, what are your current response times right now?

PDQ

 

[CTXMedia]

Caveat: These suggestions are based on my own experience of tuning my own server over the past 18 months or so and may/may not be useful in your situation.

sort_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=64M

I think these are way too large and since they are per-thread you'll be adding a large memory overhead (bigger isn't always better ). I'd set them all to 2M and see how you go.

key_buffer_size=512M

I'd change this to 1024M (you have plenty of RAM in the machine).

thread_cache_size=4

I'd increase this to 50.

query_cache_size=256M

I'd reduce this to 64M maybe even 32M and use the additional memory elsewhere. (IME 32M works just fine.)

innodb_thread_concurrency=10

I'd set this to 0 (zero).

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

I'd add these.

NB: You might also want to consider adding an InnoDB log file [ innodb_log_file_size = 512M ] which buffers table writes - but it does add a delay to MySQL restarts and crash recovery (as the log transactions are checked against the tables), so it may be something you want to research yourself first.

Cheers,
Shaun

 

[Slavik]

Nice suggestions Shaun

 

[Deebs]

Slavik,

Personally disable the MySQL query cache, it is just crap and is designed for systems with 1 or 2 cpus at the most - old design. Everything is protected by a single mutex and almost everyone who strives for performance disables it and lets the query run as it is quicker as most of the time the cache is being invalidated due to row updates etc (remember, as soon as a row is updated/inserted/deleted then the query cache has to invalidate anything in cache). It is awful. Unfortunately if you are on stock MySQL 5.1 even disabling it leaves the mutex in place, you either need to migrate to Percona (great software, I use this) or stock MySQL 5.5.

 

[Slavik]

Slavik,

Personally disable the MySQL query cache, it is just crap and is designed for systems with 1 or 2 cpus at the most - old design. Everything is protected by a single mutex and almost everyone who strives for performance disables it and lets the query run as it is quicker as most of the time the cache is being invalidated due to row updates etc (remember, as soon as a row is updated/inserted/deleted then the query cache has to invalidate anything in cache). It is awful. Unfortunately if you are on stock MySQL 5.1 even disabling it leaves the mutex in place, you either need to migrate to Percona (great software, I use this) or stock MySQL 5.5.

Thanks for the insight deebs.

Unfortunately upgrading to 5.5 is tricky with plesk, officially it supports it, but as of yet everytime i've done so on my testbed it ends in disaster and have to roll back.

Same with Percona, Plesk does not support anything outside of mysql, even though they are "drop in replacements", plesk specifically looks to the mysql package for any updates.

 

[Deebs]

Here was me thinking you had a totally unmanaged server

 

[Slavik]

Here was me thinking you had a totally unmanaged server

I wish, unfortunately as of yet, ive not found another peice of software as good as plesk at handling 200+ clients

 

[Slavik]

Slavik,

Personally disable the MySQL query cache, it is just crap and is designed for systems with 1 or 2 cpus at the most - old design. Everything is protected by a single mutex and almost everyone who strives for performance disables it and lets the query run as it is quicker as most of the time the cache is being invalidated due to row updates etc (remember, as soon as a row is updated/inserted/deleted then the query cache has to invalidate anything in cache). It is awful. Unfortunately if you are on stock MySQL 5.1 even disabling it leaves the mutex in place, you either need to migrate to Percona (great software, I use this) or stock MySQL 5.5.

Just to point out, this article.

It seems Magento at least seems to suck up and use the query cache to good effect?

http://www.crucialwebhost.com/blog/improving-magento-speed-and-performance-with-mysql-query-cache/

Just googling 'MySQL query cache magento' seems to suggest at least that it seems to do more good than harm?

 

[Slavik]

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
table_open_cache=8192
thread_cache_size=50
max_connections=200
innodb_buffer_pool_size=4096M
query_cache_type=1
query_cache_size=64M
query_cache_limit=8M
join_buffer_size=1M
table_definition_cache=6384
tmp_table_size=512M
max_heap_table_size=512M
innodb_thread_concurrency=0
key_buffer_size=1024M
max_allowed_packet=32M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M
 
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

[whyweprotest]

I wish, unfortunately as of yet, ive not found another peice of software as good as plesk at handling 200+ clients

http://krebsonsecurity.com/2012/07/plesk-0day-for-sale-as-thousands-of-sites-hacked/

 

[Slavik]

http://krebsonsecurity.com/2012/07/plesk-0day-for-sale-as-thousands-of-sites-hacked/

Good thing I don't use old versions of Plesk

 

[melbo]

table_open_cache=8192
table_definition_cache=6384

How did you calculate these?

 

[craigiri]

Not an answer for yours, Melbo, but something on the table_open directive -
http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

I have mine set a bunch lower and it's not affecting performance. According to that blog entry it may even hurt to have too many!

I think that is mostly an ISAM tuning setting anyway - and it defaults to as low as 64. I am using an older version of mysql so it is table_open instead of table_open_cache

Mine is set at 700. It does fill up, but all that means is that the machine goes to disk for them (I think). I may set it up to 900 or so, but no need for me to go higher. My reports of mysql in phpadmin and in mysql tuner are pretty good now and I don't want to fuss with it too much!

 

[melbo]

When I set them as high as Slaviks, mysqltuner started to ask me to make them larger. I set them both back down to 2000 and no more complaints.

AMD Quad w/ 16GB RAM

Here's my output after 27 hours:

[root@####### ~]# mysqltuner
 
>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  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.27
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 232M (Tables: 174)
[--] Data in InnoDB tables: 451M (Tables: 333)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 8)
[!!] Total fragmented tables: 38
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 33m 54s (503K q [5.075 qps], 52K conn, TX: 3B, RX: 132M)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 3.1G global + 7.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 3.4G (21% of installed RAM)
[OK] Slow queries: 0% (0/503K)
[OK] Highest usage of available connections: 40% (20/50)
[OK] Key buffer size / total MyISAM indexes: 512.0M/147.7M
[OK] Key buffer hit rate: 99.7% (5M cached / 13K reads)
[OK] Query cache efficiency: 65.3% (167K cached / 257K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (420 temp sorts / 21K sorts)
[!!] Joins performed without indexes: 15134
[!!] Temporary tables created on disk: 48% (20K on disk / 42K total)
[OK] Thread cache hit rate: 99% (20 created / 52K connections)
[OK] Table cache hit rate: 74% (1K open / 1K opened)
[OK] Open file limit used: 14% (592/4K)
[OK] Table locks acquired immediately: 99% (309K immediate / 310K locks)
[OK] InnoDB data size / buffer pool: 452.0M/2.0G
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)

Here's my.cnf - Lot's of commented out settings as this conf has been with me for 8 years.

[mysqld]
# open-files-limit=16384
max_allowed_packet=32M
# max_delayed_threads=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
# back_log=50
max_connections=50
key_buffer_size=512M
myisam_sort_buffer_size=64M
#myisam_max_sort_file_size=2048M
join_buffer_size=1M
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
table_definition_cache=2000
table_open_cache=2000
#table_cache=20000
thread_cache_size=384
# wait_timeout=60
# interactive_timeout=30
# connect_timeout=10
tmp_table_size=512M
max_heap_table_size=512M
# max_seeks_for_key=1000
# group_concat_max_len=1024
# max_length_for_sort_data=1024
# net_buffer_length=16384
# max_connect_errors=100000
# concurrent_insert=2
# bulk_insert_buffer_size=8M
query_cache_type=1
query_cache_size=64M
query_cache_limit=8M
# query_prealloc_size=262144
# query_alloc_block_size=65536
# range_alloc_block_size=4096
# transaction_alloc_block_size=8192
# transaction_prealloc_size=4096
default-storage-engine=InnoDB
# max_write_lock_count=4
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size=20M
 
# Trial http://blog.secaserver.com/2011/08/mysql-recommended-my-cnf-settings-innodb-engine/
# innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_change_buffering=all
innodb_file_per_table
# innodb_log_file_size=256M
# innodb_log_buffer_size=16M
innodb_read_io_threads=16
innodb_write_io_threads=16
# End Trial
 
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
 
[mysqld_safe]
# max_delayed_threads=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# nice = -5
# open-files-limit=16384
 
[mysqldump]
quick
max_allowed_packet=2150MB
 
# [myisamchk]
# key_buffer_size=256M
# sort_buffer_size=64M
# read_buffer_size=16M
# write_buffer_size=16M
 
# [mysqlhotcopy]
# interactive-timeout

 

[craigiri]

If there is one thing I have learned over the years...it's to leave it alone if things work fairly well. As it stands, mine is working very well so I am going to hod myself back from fiddling!

 

[Volion]

[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
table_open_cache=8192
thread_cache_size=50
max_connections=200
innodb_buffer_pool_size=4096M
query_cache_type=1
query_cache_size=64M
query_cache_limit=8M
join_buffer_size=1M
table_definition_cache=6384
tmp_table_size=512M
max_heap_table_size=512M
innodb_thread_concurrency=0
key_buffer_size=1024M
max_allowed_packet=32M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M
 
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have basically the same server setup, ergo cpu/ram. How is that revised .cnf working out for you Slavik?

 

[giorgino]

Slavik,

Personally disable the MySQL query cache, it is just crap and is designed for systems with 1 or 2 cpus at the most - old design.

Hi Debbs how to disable query cache correctly in MySql 5.5?

 

[Deebs]

Hi Debbs how to disable query cache correctly in MySql 5.5?

Add the following to my.cnf

query_cache_size                = 0 
query_cache_type                = 0

Then from a mysql session type the following to turn it off dynamically

SET GLOBAL query_cache_size=0;
SET GLOBAL query_cache_type=off;