MySQL server optimization in Linux environment

Ordinary MySQL users take advantage of table creation and indexing operations, as well as optimizations that can be made with the writing of queries. However, there are some optimizations that can only be done by MySQL administrators and system administrators who have control over the MySQL server or the machine running MySQL. Some server parameters are directly applicable to query processing and can be turned on. And some hardware configuration issues directly affect the query processing speed, they should be adjusted.

Disk Problems
As mentioned earlier, disk seek is a big bottleneck in performance. This problem becomes more and more apparent as the data begins to grow and the cache becomes impossible. For large databases, where you have more or less random access to data, you can rely on you to have at least one disk seek to read and several disk seeks to write. To minimize this problem, use a disk with low seek time.

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

1. Using symbolic links
This means that you link index/data file symbols from the normal data directory to other disks (that can also be split). This makes seek and read times better (if the disk is not used for other things)

2, split
Split means you have a lot of disks and put the first one on the first On the disk, the second block is placed on the second disk, and the nth block is on the (nmodnumber_of_disks) disk, and so on. This means that if your normal data size is split (or perfectly aligned), you will get better performance. Note that the split is very dependent on the OS and split size. So test your application with different split sizes. See 10.8 using your own benchmark. Note that the speed difference for splitting is very dependent on the parameters, depending on how you split the parameters and the number of disks, you can get an order of magnitude difference. Note that you must choose to optimize for random or sequential access.

For reliability, you may want to use raid RAID0+1 (split + mirror), but in this case you will need 2*N drives to hold the data for N drives. If you have money, this may be the best option. However, you may also have to invest in some volume management software investment to handle it efficiently.

A good choice is to have slightly more important data (it can be regenerated) on a RAID0 disk, and really important data (like host information and log files) on a RAID0+1 or RAIDN disk. If you have many writes because of updating the parity bit, RAIDN can be a problem.

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

Hardware Problems
Hardware can be used to improve server performance more efficiently:

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

2. If there is enough RAM to make all the swaps complete in the memory file system, then the system should be reconfigured to remove all disk swap settings. Otherwise, some systems still have to swap with the disk even if there is enough RAM to satisfy the exchange.

3, increase the faster disk to reduce I /O wait time. The seek time is the main factor determining performance here. Moving the head verbatim is very slow, and once the head is positioned, reading the block from the track is faster.

4. Before you put the data back into different devices, you should be sure to understand the loading characteristics of the system. If you have some specific primary activity on a particular physical device, putting the database there might actually make the performance worse. For example, don't move the database to a web server device that handles a lot of web traffic.

5, when setting up MySQL, you should configure it to use static libraries instead of shared libraries. Using a shared binary dynamic binary system saves disk space, but a static binary system is faster (however, if you want to load a user-defined function, you can't use a static binary system because the UDF mechanism relies on dynamic connections).

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

delayed_queue_size
This parameter is executed in other INSERTDELAYED statements. Before the client blocks, determine the number of rows in the queue from the INSERTDELAYED statement. Increasing the value of this parameter allows the server to receive more rows from such a request, so the client can continue to execute without blocking.

key_buffer_size
This parameter is the buffer size used to store the index block. If there is more memory, increasing this value can save time in index creation and modification. Larger values ​​allow MySQL to store more index blocks in memory, which increases the likelihood of finding key values ​​in memory without reading disk blocks.

In MySQL 3.23 and later, if you increase the size of the key buffer, you may want to start the server with the ——init-file option. This allows you to specify a SQL statement file to be executed when the server starts. If you have read-only tables that you want to store in memory, you can copy them to a HEAP table that looks very fast for the index.