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

  • 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 , these buffers can be increased on a session-by-session basis.