Optimizing for MySQL
On this page I collect tips & tricks on how to optimize your system for running MySQL. Some tips are in general usefull for optimizing your system.
- 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
Use 'explain'. More to follow.
set autocommit to 0 and only commit when really ncessary.
graphing MySQL statistics with MRTG