MySQL server hardware, OS and configuration tuning tips

MySQL server hardware, OS and configuration tuning tips Hardware and OS

1. Use a 64-bit OS because it supports adding more memory.

2. Do not use software RAID.

3. Avoid RAID 5 and use RAID 10 instead.

4. A RAID controller with battery-backed cache is highly desirable.

5. Use disks that are not too large for better performance.

6. Use disks with fast reads and writes.

7. Put data and the OS on separate partitions.

8. Mount file systems with noatime and nodirtime.

9. Use the DEADLINE I/O scheduler instead of CFQ or ANTICIPATORY. Add elevator=deadline to grub.conf on the entry for the kernel you want to load.

   title CentOS (2.6.32-220.7.1.el6.x86_64)
           root (hd0,0)
           kernel /vmlinuz-2.6.32-220.7.1.el6.x86_64 ro root=LABEL=/ quiet elevator=deadline
           initrd /initramfs-2.6.32-220.7.1.el6.x86_64.img[

10. Install only the packages you use and remove unnecessary packages.

11. Check the number of files the mysql user can open with ulimit. If the database has many tables, raise the limit appropriately.

12. Keep MySQL temporary space and replication logs on a different partition from the data.

13. Prevent swap use by adding “vm.swappiness=0” to /etc/sysctl.conf.

14. Use the XFS file system to improve performance and prevent double buffering, which occurs with ext3, when used with MySQL.

15. Add frequently used hostnames and IP addresses to /etc/hosts to avoid DNS lookups.

16. Do not force-kill MySQL processes, as this can corrupt the database.

Configuration

1. Use innodb_flush_method=O_DIRECT to prevent double buffering when writing data.
2. Avoid using O_DIRECT with the EXT3 file system, as it serializes writes.
3. Use skip-name-resolve.
4. Set innodb_buffer_pool_size to no more than 70–80% of memory. This lets the system load InnoDB files into memory.
5. Do not make innodb_log_file_size too large; around 256MB is reasonable. A larger size is faster but slows recovery after a system crash.
6. Do not set max_connections too high. If too many connections arrive, memory use may exceed the available memory and crash the system.
7. Keep innodb_log_buffer_size within 2–8MB unless you use large BLOBs.
8. Enable innodb_file_per_table to prevent the main tablespace file from growing too large and store each table in its own file.
9. Use the query cache when data does not change often.
10. Increase temp_table_size and max_heap_table_size to avoid writing to disk.
11. Do not set sort_buffer_size too high, because its memory use scales with the number of connections.

Backup

1. Back up from the slave.
2. Do not rely on LVM snapshots for backups, because data inconsistencies may occur.
3. For online backups, I recommend xtrabackup.