Documentation and discussion

• MySQL 5.0 Reference Manual
• MySQL Performance Blog

Management software

• phpMyAdmin
• Maatkit

Tuning analysis software

• mysqlsla
• MySQLTuner
• Activity Report

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

• thread_stack
  • Default: 192K
• net_buffer_length (allocated twice per connection)
  • Default: 16K
• sort_buffer_size
  • Recommended: 2M
• read_buffer_size
  • Recommended: 200K
• join_buffer_size
  • Recommended: 1M
• bulk_insert_buffer_size
  • Recommended: 8M
  • Allocated when using INSERT INTO ... SELECT, INSERT INTO ... VALUES (...), (...), and LOAD DATA INFILE.
• read_rnd_buffer_size
  • May be allocated when reading rows in an arbitrary sequence