Optimizing for MySQL

SysAdmin tips

• Older Linux systems (at least 2.6.16) need 'irqbalance' when the system is SMP (multicore - NOT hyperthreading)
• Every time MySQL reads or writes from a table, the kernel updates the inode: it stores the last access time. this can have a negative impact. at "noatime" to your fstab to let it stop doing this. use "mount -o remount,noatime /var/lib/mysql" (when you have a seperate filesystem mounted on /var/lib/mysql, of course) to set this at runtime. kernel 2.6.20 has "relative atime" which reduces the need for noatime
• Put your databases on disks seperate from the other installed software
• Put your database on a RAID-1 diskset
• If you have > 20% write access, do NOT put the databases on RAID-5!
• When you do frequent update/delete queries, you might want to run this script 'optimize_mysql.tar.gz' (from cron for example) when the system is idle-ish
• Choosing filesystem: this page. Conclusion is more or less (read the page!) use ext3 with data=writeback - make sure your system is on a UPS though
• When using InnoDB tables, set "set-variable = innodb_buffer_pool_size=256M" in my.cnf where 256M should be 70% of your free memory (aprox) - as innodb doesn't use mysql's own buffers
• Use the 'cfq' IO scheduler: "echo cfq > /sys/block/hda/queue/scheduler"
• Disable the binary log if not needed.
• mount the filesystem on which the database datafiles are located with 'noatime' or 'relatime' (noatime is preferred)
• Run the 'performance tuning primer script' from www.day32.com

DBA tips

Must reads

• Linux-Magazine - MySQL Server Performance Tuning (2001)

Links