MySQL Server-level optimization in Linux

Abstract: This section briefly introduces how to optimize the database performance at the server level and the hardware problems involved in improving the database performance. Selecting a system as fast as possible is easy to think of using RAID disk arrays. For the database daemon, you can provide the appropriate parameters at compilation, or provide the parameters to be optimized in the option file.

The preceding sections describe how to create and index tables for normal MySQL users, as well as the optimization that can be done by writing queries. However, there are some optimizations that can only be completed by the MySQL administrator and system administrator who have control over the MySQL server or the machine running MySQL. Some server parameters apply directly to query and processing and can be opened. However, some hardware configuration problems directly affect the query processing speed and should be adjusted.

Disk Problems

As described above, disk tracing is a major performance bottleneck. This problem becomes more and more obvious when data increases and the cache becomes impossible. For large databases, where you want to access data randomly, you can rely on at least one disk seek to read data and write data to the disk several times. To minimize this problem, use a disk with a low track time.

To increase the number of available disk axes (and thus reduce track overhead), it is possible to connect files to different disks or split disks.

1. Connect using symbols

This means that you link the index/data file symbol from the normal data directory to another disk (that can also be split ). This makes searching and reading time better (if the disk is not used for other tasks)

2. Segmentation

Splitting means that you have many disks and place the first disk on the first disk, the second disk on the second disk, and the nth disk on the (n mod number_of_disks) disk, and so on. This means that if your normal data size is in the split size (or perfectly arranged), you will get better performance. Note: whether the split depends on OS and the split size. Therefore, test your application with different split sizes. See section 10.8 use your own benchmark. Note that the difference in the split speed depends on the parameter, depending on how you split the parameter and the number of disks, you can get a different order of magnitude. Note that you must select random or sequential access optimization.

To ensure reliability, you may want to use RAID 0 + 1 (split + image), but in this case, you will need 2 * N drives to save data on N drives. If you have money, this may be the best choice! However, you may also have to invest some volume management software to handle it efficiently.

A good choice is to store a slightly important data (which can be regenerated) on a RAID 0 disk, and make sure that important data (such as host information and log files) there is a RAID 0 + 1 or raid n disk. Raid n may be a problem if you have many writes because of the update parity.

You can also set parameters for the file system used by the database. An easy change is to mount the file system with the noatime option. This is the last access time it skips updates in inode, and this will avoid some disk seek.

Hardware problems

Hardware can be used to improve server performance more effectively:

1. install more memory on the machine. In this way, the server's high-speed cache and buffer size can be increased, so that the server can more often use the information stored in the memory, reducing the requirements for obtaining information from the disk.

2. If you have enough RAM to complete all swap operations in the memory file system, you should reconfigure the system and remove all disk swap settings. Otherwise, some systems still need to swap with the disk even if there is enough RAM to meet the exchange requirements.

3. Increase the disk speed to reduce the I/O wait time. Seeking time is the main factor that determines performance. It is very slow to move the head literally. Once the head is located, it is faster to read from the track.

Try to reassign disk activity on different physical devices. If possible, put your two busiest data inventories on different physical devices. Note that using different partitions on the same physical device is not enough. This does not help because they will still compete for the same physical resources (Disk header ). The process of moving databases is described in Chapter 10th.

4. Ensure that you understand the loading characteristics of the system before placing data on different devices. If a specific activity already exists on a specific physical device, putting the database there may actually cause worse performance. For example, do not move the database to a Web server that processes a large number of Web communications.

5. When setting MySQL, you should configure it to use a static library instead of a shared library. Using the Dynamic Binary System of the shared library can save disk space, but the static binary system is faster (however, if you want to mount a user-defined function, you cannot use the static binary system, because the UDF mechanism depends on dynamic connections ).

1 2 Next Page