MySQL configuration and tuning

Owned by Former user (Deleted)
Last updated: Apr 19, 2010 by AnonymousVersion comment
2 min read

Documentation and discussion

Management software

Tuning analysis software

High availability

  • Use circular replication.
  • Choose a fail-over strategy:
    • Load-balancer
      • Master server VIP must only connect to a single MySQL instance.
      • Fail-over generally requires Apache and PHP on the MySQL server to allow the load balancer to indirectly monitor MySQL status.
    • Clustering
      • Moderation/arbitration
      • Fencing
        • APC7920 or WTI
        • iLO, DRAC, IBM RSAII
  • Plan for likely failure:
    • Disks
  • Plan for possible failure:
    • Network
    • OOM
    • Power

Global configuration

Any memory specified below is allocated once for each MySQL instance, regardless of the number of connections.

  • query_cache_size
    • Recommended: Cap at 128M, maybe 256M.
  • query_cache_limit
    • Recommended: 1M (default)
  • 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 less UPDATE or INSERT volume.
  • 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
  • default_character_set
    • Recommended: utf8
  • collation_server
    • Recommended: utf8_general_ci
  • character_set_server
    • Recommended: utf8
  • 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.
  • 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

Any memory specified below is allocated per-connection. The maximum number of concurrent connections is max_connections. The MySQL server should not have to swap even if it reaches max_connections and MySQL uses all globally available memory. If a particular session or connection would benefit from increases in any of the buffers marked with (star), these buffers can be increased on a session-by-session basis.