101 MySQL Debugging and optimization tips

MySQL is a powerful open source database. With more and more database-driven applications, people have been pushing MySQL to its limits. Here are 101 tips for adjusting and optimizing MySQL installation. Some of the tricks are specific to the installation environment, but these ideas are generic. I've divided them into several categories to help you learn more about MySQL's tuning and optimization skills.

MySQL server hardware and operating system tuning:

1. Have enough physical memory to load the entire InnoDB file into memory-faster access to files in memory than when accessed on a hard disk.

2. Avoid the use of swap partitions at all costs-the swap is read from the hard drive and it is slow.

3. Use battery-powered RAM (Note: RAM is random memory).

4. Use advanced RAID (note: Redundant Arrays of Inexpensive disks, disk array) – preferably RAID10 or higher.

5. Avoid RAID5 (note: A storage solution that balances storage performance, data security, and storage costs) – Ensuring that database integrity is validated at a cost.

6. Separate the operating system from the data partition, not only logically, but also physically – the operating system's read and write operations can affect the performance of the database.

7. Put the MySQL temporary space and the replication log and data into different partitions-when the database is read and written from the disk in the background can affect the performance of the database.

8. More disk space equals faster speed.

9. Better and faster disk.

10. Use SAS (note: Serial Attached SCSI, serial attached SCSI) instead of SATA (note: SATA, that is, serial hard drive).

11. Smaller hard drives are faster than large hard drives, especially in the case of RAID configurations.

12. Use a battery-supported cache RAID controller.

13. Avoid the use of software disk arrays.

14. Consider using solid-state IO cards (not disk drives) for data partitioning – These cards can support 2gb/s write speeds for virtually any number of data.

15. Setting the Swappiness value in Linux is 0– there is no reason to cache files in the database server, which is the advantage of a server or desktop.

16. If available, use Noatime and nodirtime to mount file systems-there is no reason to update the time that the database file was modified.

17. Using XFS file Systems – a faster, smaller file system than ext3, with a number of logging options, and ext3 has been proven to have a double buffering problem with MySQL.

18. Adjust XFS file system logs and buffer variables – for maximum performance standards.

19. In Linux systems, the CFQ and anticipatory timing scheduler is very slow compared to the NOOP and deadline timing Scheduler, using the NOOP or deadline IO timing scheduler.

20. Using a 64-bit operating system-for MySQL, there will be greater memory support and usage.

21. Remove unused installation packages and daemons on the server – less resource consumption.

22. Put the host with MySQL and your MySQL host in a Hosts file – no DNS lookup.

23. Do not force the killing of a MySQL process – you will damage the database and the program that is running the backup.

24. The contribution of the server to the mysql– background process and other services can shorten the CPU time of the database.

MySQL configuration:

25. When writing, use Innodb_flush_method=o_direct to avoid double buffering.

26. Avoid the use of O_direct and EXT3 file Systems – you will serialize all that you want to write.

27. Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-read less from the disk.

28. Do not set the Innodb_log_file_size parameter too large, so that you can have more disk space at the same time-lost a lot of log is usually good, after the database crash can reduce the time to restore the database.

29. Do not mix innodb_thread_concurrency and Thread_concurrency parameters – these 2 values are incompatible.

30. Assign a minimum number to the Max_connections parameter – too many connections will run out of RAM and lock down the MySQL service.

31. Keep The thread_cache in a relatively high number, about 16 – to prevent slow opening of the connection.

32. Use Skip-name-resolve parameter – Remove DNS lookup.

33. If your query is duplicated and the data does not change frequently, you can use the query cache. But if your data changes frequently, using a query cache can be frustrating for you.

34. Increase the temp_table_size value to prevent writing to disk

35. Increase the max_heap_table_size value to prevent writing to disk

36. Do not set the Sort_buffer_size value too high, otherwise your memory will soon run out

37. According to Key_read_requests and Key_reads value to determine the size of the Key_buffer, in general, key_read_requests should be higher than the key_reads value, otherwise you can not efficiently use Key_buffer

38. Setting the Innodb_flush_log_at_trx_commit to 0 will improve performance, but if you want to keep the default (1), then you need to ensure the integrity of the data, and you also want to ensure that replication does not lag.

39. You need to have a test environment to test your configuration, and you can often reboot without impacting normal production.

MySQL mode optimization:

40. Keep your database organized.

41. Old Data Archive – Delete extra rows to return or search for queries.

42. Add your data to the index.

43. Do not overuse indexes, compare and query.

44. Compress text and BLOB data types-to save space and reduce disk reads.

Both UTF 8 and UTF16 are less efficient than latin1 execution.

46. Use triggers in a controlled manner.

47. Redundant data is kept to a minimum-no duplication of unnecessary data.

48. Use linked tables instead of extended rows.

49. Note the data type, and use the smallest one possible in your real data.

50. If other data is often used in queries, and blob/text data is not, separate blob/text data from other data.