...
query_cache_size
- Recommended: Cap at 128M, maybe 256M.
query_cache_limit
- Recommended:
1M
(default)
- Recommended:
query_cache_type
- Recommended:
1
(enables cache by default) - Consider turning the default caching off and using
SQL_CACHE
in specific queries accessing tables with lessUPDATE
orINSERT
volume.
- Recommended:
max_connections
- Slightly more than maxclients (in apache) to prevent "db offline" errors
innodb_buffer_pool_size
- Recommended: As large as possible while accommodating connection memory without swapping.
default_storage_engine
- Recommended:
InnoDB
- Recommended:
default_character_set
- Recommended:
utf8
- Recommended:
collation_server
- Recommended:
utf8_general_ci
- Recommended:
character_set_server
- Recommended:
utf8
- Recommended:
max_allowed_packet
- Recommended: Just make consistent across all servers and clients to avoid "packet too large" errors.
sync_binlog
- Recommended: Set to
1
if the disk controller has a battery-backed cache.
- Recommended: Set to
innodb_log_file_size
- Recommended: Up to 4G, but start with 1G
innodb_flush_log_at_trx_commit
- Recommended: 0 (may mean less than 100% acid-compliance)
innodb_flush_method
- Recommended: O_DIRECT (linux only)
innodb_file_per_table
- Recommended: 1 to allow us to reclaim space on disk
Per-connection configuration
...
thread_stack
- Default:
192K
- Default:
net_buffer_length
(allocated twice per connection)- Default:
16K
- Default:
sort_buffer_size
- Recommended:
2M
- Recommended:
read_buffer_size
- Recommended:
1M
200K
- Recommended:
join_buffer_size
- Recommended:
2M
1M
- Recommended:
bulk_insert_buffer_size
- Recommended:
8M
- Allocated when using
INSERT INTO ... SELECT
,INSERT INTO ... VALUES (...), (...)
, andLOAD DATA INFILE
.
- Recommended:
read_rnd_buffer_size
- May be allocated when reading rows in an arbitrary sequence