Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "MySQL server optimization skills under Linux system". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

disk problems

As mentioned earlier, disk seek is a big performance bottleneck. This problem becomes more and more apparent as the data starts to grow so that caching becomes impossible. For large databases, where you have more or less random access to data, you can rely on the fact that you will need at least one disk seek to read and several disk seeks to write. To minimize this problem, use disks with low seek times.

To increase the number of available spindles (and thereby reduce seek overhead), it is possible to symbolically join files to different disks or split disks.

1. Use symbolic connections

This means that you link index/data file symbols from the normal data directory to other disks (that can also be partitioned). This makes seek and read times better (if the disk is not used for anything else)

2. Division

Partitioning means you have many disks and put *** blocks on *** disks, the second block on the second disk, and the nth block on the (nmodnumber_of_disks) th disk, and so on. This means that if your normal data size is smaller than the partition size (or arranged ***), you will get better performance. Note that partitioning depends heavily on OS and partition size. So test your application with different partition sizes. See 10.8 Use your own benchmarks. Note that the speed difference for partitioning is very parameter-dependent. Depending on how you partition parameters and the number of disks, you can get orders of magnitude differences. Note that you must select optimization for random or sequential access.

To be reliable, you might want to use attack RAID0+1(split + mirror), but in this case you will need 2*N drives to hold N drives of data. If you have money, this may be the best choice! However, you may also have to invest in some volume management software to process it efficiently.

A good option is to keep the slightly more important data (which can be regenerated) on RAID 0 disk, while keeping the really important data (like host information and log files) on a RAID 0 +1 or RAID N disk. RAIDN may be a problem if you have many writes because of the update parity bits.

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 *** access time it skips updating in the inode, and this will avoid some disk seeks.

hardware problem

Hardware can be leveraged to improve server performance more effectively:

Install more memory in the machine. This increases the size of the server's cache and buffers, allowing the server to use information stored in memory more often and reducing the need to retrieve information from disk.

If there is enough RAM for all swaps to be done in the memory file system, then the system should be reconfigured to remove all disk swap settings. Otherwise, some systems will still swap with disk, even if there is enough RAM for the swap.

Add faster disks to reduce I/O latency. Seek time is the main factor determining performance here. Moving the head verbatim is slow and reading blocks from tracks is faster once the head is positioned.

Before relocating data to a different device, make sure you understand the loading characteristics of the system. If there is already some particular primary activity on a particular physical device, placing the database there may actually make performance worse. For example, don't move databases to Web server devices that handle a lot of Web traffic.

When setting up MySQL, you should configure it to use static libraries instead of shared libraries. Dynamic binary systems using shared libraries save disk space, but static binary systems are faster (however, static binary systems cannot be used if you want to load user-defined functions because UDF mechanisms rely on dynamic connections).

Selection of server parameters

A server has several parameters (or variables) that can be changed to affect its operation. The current values of system variables can be checked by executing the MySQLadminvaribles command. Several parameters are mainly related to queries. It is necessary to mention here:

delayed_queue_size

This parameter determines the number of rows from an INSERT DELAYED statement that are queued before clients executing other INSERT DELAYED statements block. Increasing the value of this parameter enables the server to receive more rows from such requests so that the client can continue execution without blocking.

key_buffer_size

This parameter is the buffer size used to hold index blocks. If you have a lot of memory, increasing this value saves time in index creation and modification. Larger values enable MySQL to store more index blocks in memory, which increases the likelihood that the key will be found in memory without reading disk blocks.

"MySQL server optimization skills under Linux system" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.