General
Set sql-mode
- STRICT_TRANS_TABLES
- NO_AUTO_CREATE_USER
- NO_ENGINE_SUBSTITUTION
SET default-storage-engine
- to INNODB
Not used old_passwords
Use big-tables
Use Skip-name-resolve
log
use log-slow-queries and log-warnings
-for monitor log time queries and warning messages
Set log_query_time
-for log all queries that have taken more then log_query_time seconds
Sort/Read/join Buffer
Set sort_buffer_size
-Dynamic by number of ORDER By query
Set read_buffer_size
-Dynamic by trafice and b/w
Set join_buffer_size
- Dynamic of join table not use indexes
key_buffer_size
Very important for MyISAM tables 30-40% of RAM if using lower MyISm tables then keep it lower but still at least 16-32Mb
innodb_buffer_pool_size
70-80% of memory
innodb_additional_mem_pool_size
20MB not effect much but you can see how much memory Innodb allocates for misc needs.
innodb_log_file_size
Very important 64M-512M depending on server size.
innodb_log_buffer_size
8MB-16MB is enough. dont make it high because it rfresh on every second
innodb_flush_log_at_trx_commit
Crying about Innodb being 100 times slower than MyISAM.
Default value is 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache.
2 - which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache
1024 is good value
it is typically best to size your cache so it is large enough to keep most of your tables open.
thread_cache
which happen at each connect/disconnect. I normally set this value to at least 16.
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
query_cache_size
10% of RAM
32M to 512M
Do not set it too large as it may slow things down as its maintenance may get expensive.
query_cache_type
1 to Cache all query result expcept for those that begin with SELECT SQL_NO_CACHE
query_cache_limit
5-10% or RAM
tmp_table_size
5-10% of RAM
max_tmp_tables
5-10% number of table in all databases tempdir to different physical drive
Use skip-thread-priority
Set thread_concurrency
- number of Core CPU*2
Set thread_cache_size
- tp 100-((threads_created/max_connections)*100)
Set innodb_buffer_pool_size
Typical value 60-80% of memory (If Innodb is only your storate engine)
use 'innodb_additional_mem_pool_size'
Do not set too high, avoid memory waste
set innodb_flush_log_at_trx_commit=2
Control by OS
Set innodb_thread_concurrency
-2*(NumCOUs_NumDisks)-in theory
