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.

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

Use 'explain'. More to follow.
set autocommit to 0 and only commit when really ncessary.

Must reads

Links

 graphing MySQL statistics with MRTG





 
 
Main menuSlashdot It! e-mail me at: folkert@vanheusden.com (CAcert assurer)
July 4, 2008 Please support Seti@Home