magento-mysql

Magento default mysql settings

default/startup settings for mysql database.
please read this before changing anything!

GENERAL OPTIONS

max_connections
How many connections to allow. Watch max_used_connections value
thread_cache
Cache to prevent excessive thread creation
50-100 is good value. Watch threads_created
table_cache/table_open_cache
Cache of opened table instances
Single table may have multiple entries
Watch opened_tables status value
Start with 4096
open_files_limit
MyISAM tables require up to 2 file handlers
Each connection is file handler too
Safe to set to 65535 in most systems
table_definition_cache
Cache table definitions (CREATE TABLE)
Only one entry per table
Watch Opened_table_definitions
Set to number of tables + 10% unless 50K+ tables
back_log
Need adjustment if many connections/sec
2048 is reasonable value
max_allowed_packet
Limits maximum size of query
Limits internal string variable size
16MB is a good value
max_connect_errors
Prevent password brute force attack
Can cause "Host Blocked" error messages
Value around 1000000 is good
skip_name_resolve
Avoid DNS lookup on connection. Faster and Safer
Do not use host names in GRANTs
old_passwords
Should NOT be enabled. Will cause insecure password hash to be used.
log_bin
Enable for replication and point in time recovery
Set to mysql-bin to avoid default naming
sync_binlog
Make Binlog durable. Set to 1 if have RAID with BBU or Flash
Can be really performance killer with slow drives.
expire_log_days
Purge old binary logs after this number of days
14 (2 weeks) is a good value with weekly backups
tmp_table_size
max_heap_table_size
Typically set to same value (workload based)
Created_tmp_disk_tables status variable
Beware BLOB/TEXT fields cause on disk table with any size.
query_cache_size
Enable query cache only if it is tested to provide significant gains
Often causes stalls and contention
Do not set over 512M
sort_buffer_size
In memory buffer used for sorting
Watch sort_merge_passes
Consider setting for session for large queries
Values up to 1MB are good default
Large values hurt performance of small queries
join_buffer_size
Helps performance of Joins with no indexes
Better get rid of such Joins
8MB can be reasonable value
default_storage_engine
Use Innodb engine for tables if not specified
read_rnd_buffer_size
Buffer for reading rows in sorted offer
Specifies Maximum Value
Values around 16MB often make sense
Do not mix with read_buffer_size
Tmpdir
Specify location of temporary directory
Tmpfs often good choice unless very large temporary space is needed.
tmpdir=/dev/shm
```
MyISAM OPTIONS
```
key_buffer_size
Cache MyISAM Indexes.
Does Not cache data.
Up to 30% of memory if using MyISAM only
myisam_recover
Automatically repair corrupted MyISAM tables after crash. BACKUP,FORCE is a good value.
myisam_sort_buffer_size
Buffer used for building MyISAM indexes by Sort. 8MB-256MB are good values
low_priority_updates
Allow higher concurrency for SELECTs
May starve update queries
bulk_insert_buffer_size
Buffer to optimize Bulk Inserts
Values of 1/4 of key_buffer_size make sense
Note it is per connection value
```
 INNODB MEMORY SETTINGS
```
innodb_buffer_pool_size
The most important setting. Often 80%+ of memory is allocated here.
innodb_buffer_pool_instances
Reduce contention. Set to 4+ in MySQL 5.5+
innodb_log_buffer_size
Buffer for log files. Good Values 4MB-128MB
Not only reduce writes but help contention
innodb_ibuf_max_size
Control size of Insert buffer. Default is 1/2 of Buffer pool. Smaller values are good for SSD
```
 INNODB IO OPTIONS
```
innodb_flush_log_at_trx_commit
Control Durability
1=flush and sync; 2=flush; 0=neither
Innodb_flush_method
Controls how Innodb Performs IO
O_DIRECT good value for most servers
innodb_auto_lru_dump
Percona Server Feature to warmup quickly
300 (seconds) is a good value
innodb_io_capacity
Controls Innodb Assumption about Disk Performance. Increase for faster drives
innodb_read_io_threads
innodb_write_io_threads
Control number of threads doing reads and writes
MySQL 5.5 has async IO so very high values might not be needed
4 is good default. Higher for large IO systems.
innodb_flush_neighbor_pages
Percona Server feature to control how flushing works
Disable (set to 0) for SSD
```
 OTHER INNODB OPTIONS
```
innodb_log_file_size
Size of redo log file. Larger logs better performance but longer recovery.
innodb_log_files_in_group
Leave at 2 which is default.
innodb_file_per_table
Store each Innodb table in separate file. Usually Good choice
innodb=force
Enable so MySQL does not start if Innodb could not initialize.
Otherwise it might start but error on access to all Innodb tables.
innodb_lock_wait_timeout
How long to wait for row level locks before bailing out
innodb_old_blocks_time
Helps to make buffer pool scan resistant
Values around 1000 make sense
innodb_file_format
Which file format Innodb will use
Antelope is default legacy format
Barracuda allows to use new features like compression
innodb_stats_on_metadata
Update statistics on meta data access
Such as Information_schema queries
Typically best disabled for more workloads Set to 0
Innodb will still refresh stats when table changes significantly
performance_schema
Enable Performance Schema in MySQL 5.5+
Watch potential overhead.
log_slow_queries
Enable Slow Query Log. Old but very helpful.
long_query_time
Especially with long_query_time set to 0 periodically to get sample of the load
log_slow_verbosity=full
Get a lot more data about queries in Percona Server
low_warnings=2
Get warnings about disconnects and other minor issues in error log.
More information but it can get spammy
userstat_running=1
Get advanced Table and Index usage statistics in Percona Server and MariaDB

jschreck/Magento-mysql

magento-mysql

Magento default mysql settings