Optimization Techniques for 101 MySQL instances (1)

MySQL is a powerful open-source database. with more and more database-driven applications, people have been pushing MySQL to its limit. here are 101 tips for adjusting and optimizing MySQL installation. some tips are applicable to specific installation environments, but these ideas are common. I have already divided them into several categories to help you master more MySQL adjustment and optimization techniques.

MySQL server hardware and operating system adjustment:

1. Have enough physical memory to load the entire InnoDB file into the memory. The speed of accessing files in the memory is much faster than that on the hard disk.

2. Avoid using Swap to Swap a partition at any cost-the Swap is read from the hard disk, and its speed is very slow.

3. Battery-powered RAM Note: RAM is random memory ).

4. Use advanced RAID. Note: Redundant Arrays of Inexpensive Disks (disk array)-preferably RAID 10 or higher.

5. Avoid RAID5 Note: A storage solution that combines storage performance, data security, and storage costs-ensuring database integrity verification is costly.

6. Separate the operating system from the Data Partition, not only logically, but also physically-the read/write operations of the operating system will affect the database performance.

7. Place the MySQL temporary space and copy logs and data in different partitions-when the database background reads and writes from the disk, the database performance will be affected.

8. More disk space equals faster speed.

9. better and faster disks.

10. Use SAS Note: Serial Attached SCSI (Serial Connection SCSI) instead of SATA (SATA ).

11. Smaller hard disks are faster than larger ones, especially when RAID is configured.

12. Use the battery-Supported High-speed cache RAID Controller.

13. Avoid using a software disk array.

14. Consider using a solid-state iocard (not a disk drive) for data partitioning-these cards can support 2 Gb/s write speed for almost any amount of data.

15. Set the value of swappiness to 0 in Linux-there is no reason to cache files on the database server, which is a server or desktop advantage.

16. If possible, use noatime and nodirtime to mount the file system-there is no reason to update the modification time for accessing database files.

17. Use XFS file system-a file system that is faster and smaller than ext3 and has many log options, and ext3 has been confirmed to have dual-buffering problems with MySQL.

18. Adjust the XFS File System Log and buffer variables-for the highest performance standard.

19. In Linux, the use of NOOP or deadline io scheduled scheduling program-compared with the NOOP and DEADLINE scheduled scheduling program, this CFQ and ANTICIPATORY scheduled scheduling program appear very slow.

20. Use a 64-bit operating system-for MySQL, more memory is supported and used.

21. delete unused installation packages and daemon on the server-less resource occupation.

22. Put the host using MySQL and your MySQL host in a hosts file-No DNS lookup.

23. Do not force a MySQL process to be killed-you will damage the database and the program running the backup.

24. Contributing servers to MySQL-background processes and other services can shorten the CPU usage time of databases.

MySQL Configuration:

25. When writing data, innodb_flush_method = O_DIRECT is used to avoid double buffering.

26. Avoid using O_DIRECT and EXT3 file systems-you will serialize all the files to be written.

27. allocate enough innodb_buffer_pool_size to load the entire InnoDB file to the memory-less read 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. It is usually good to lose more logs. After the database crashes, it can reduce the database recovery time.

29. Do not mix the innodb_thread_concurrency and thread_concurrency parameters-these two values are incompatible.

30. assign a very small number to the max_connections parameter-too many connections will use up RAM and lock the MySQL service.

31. Keep thread_cache in a relatively high number, around 16-to prevent slow connection.

32. Use the skip-name-resolve parameter-Remove DNS lookup.

33. if your queries are repeated and the data does not change frequently, you can use the query cache. however, if your data changes frequently, using the query cache will disappoint you.

34. Increase the value of temp_table_size to prevent data from being written to the disk.

35. Increase the max_heap_table_size value to prevent data from being written to the disk.

36. Do not set sort_buffer_size too high. Otherwise, your memory will soon be exhausted.

37. The key_read_requests and key_reads values are used to determine the key_buffer size. Generally, the key_read_requests value should be higher than the key_reads value. Otherwise, you cannot use key_buffer efficiently.

38. Setting innodb_flush_log_at_trx_commit to 0 will improve performance, but if you want to keep the default value 1), you must ensure data integrity and ensure that the replication will not lag.

39. You need to have a test environment to test your configuration and often restart it without affecting normal production.

MySQL mode optimization:

40. Keep your database organized.

41. Archive old data-delete redundant rows and return or search for queries.

42. Add an index to your data.

43. Do not over-use indexes, comparison and query.

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

45. UTF 8 and UTF16 are both less efficient than latin1.

46. Use the trigger in a controlled manner.

47. Keep redundant data to a minimum-do not repeat unnecessary data.

48. Use a chain table instead of expanding rows.

49. Pay attention to the data type. Use the smallest data type in your real data.

50. If other data is often used for query, but BLOB/TEXT data is not, the BLOB/TEXT data is separated from other data.

51. Check and frequently optimize the table.

52. Rewrite InnoDB table optimization frequently.

53. Sometimes, the index is deleted when a column is added, and then the index is added back, which will be faster.

54. Use different storage engines to meet different requirements.

55. Use the archive storage engine log table or audit table-this is more effective to write.

56. session data is stored in the cache for memcache, rather than in MySQL. The cache allows automatic value filling and prevents you from creating time-space data that is difficult to read and write to MySQL.

57. Use VARCHAR instead of CHAR-to save space when storing variable-length strings, because the fixed-length CHAR, And the VARCHAR length is not fixed UTF8 is not affected ).

58. Gradual changes to models-a small change can have a huge impact.

59. test all modes in the development environment to reflect production changes.

60. Do not arbitrarily change the value in your configuration file. It can have disastrous effects.

61. Sometimes, less configs in MySQL is more.

62. If you have any questions, use a common MySQL configuration file.