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