...
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
table_cache
- Recommended: tune to be larger than your number of tables
key_buffer_size
- Recommended: 20M (we still build temp tables in myISAM)
Per-connection configuration
...