Let’s begin by assuming you have a server that runs MySQL and lots and lots of traffic flows through it everyday, let’s say… something like 50% of the size of the partition that the mysql binary logs are written to is on, then we will assume the binary log is turned on. Then we assume that expire_logs_days is not set.
What happens? Nagios/etc alerts that the partition is reaching a usage threshold because – low and behold the binary logs are filling up the partition. Tuning this variable is also important. It may need to be set to as low as 1 day, in which case I would say we need a bigger partition for binary logs, but setting it so low can cause replication problems if the slave(s) gets behind more than 1 day – god help us if it does – then those binary logs that the slave is reading are no longer available, and rebuilding replication will be next on the task list. (or using maatkit)
While I’m at it here are some good ones for relieving tension. By no means is this a full list but some things on the top of my head at the moment.
expire_logs_days = x
myisam_recover
skip_bdb
sql_mode = NO_AUTO_CREATE_USER
max_binlog_size = x
long_query_time = x
log_slow_queries
log_warnings
transaction-isolation=REPEATABLE-READ (or SERIALIZABLE)
What about sane values for myisam_repair_threads? The default is to use a single thread so if you have a lot of very large tables, your startup could take a while.
Also, would be good to use –myisam-recover=FORCE,BACKUP, especially the BACKUP.
[ed: Jeff that's a good point. Just added BACKUP and increased the threads to 4. I don't like the idea of FORCE for an automated recovery though - I'd prefer to force things manually
]