...
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_CACHEin specific queries accessing tables with lessUPDATEorINSERTvolume.
- 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
1if 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
...