Debugging and optimization techniques for 101 MySQL instances

Source: Internet
Author: User
Tags mysql host utf 8

Debugging and optimization techniques for 101 MySQL instances

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 skills.

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, that is, the 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, that is, Serial connection SCSI) instead of SATA (Note: SATA, that is, Serial drive ).

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 an advantage of a server or desktop.

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 faster and smaller file system than ext3, with many log options, and ext3 has been proven to have dual-buffering problems with MySQL.

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

19. In Linux, The NOOP or deadline io scheduled scheduling program is very slow compared with the NOOP and DEADLINE scheduled scheduling program.

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 usage.

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 that is 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. However, if you want to retain 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.