What are the measures to optimize the MySQL server?

The following articles mainly describe the actual operating scheme of MySQL Server optimization and the actual operations on how to use hardware to effectively improve the performance of MySQL server, if you are interested in the actual operations, you can click the following article.

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 MySQL 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

The hardware can be used to improve the performance of the MySQL server 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 ).

Server Parameter Selection

The server has several parameters (or variables) that can change and thus affect its operations ). You can run the mysqladmin varibles command to check the current value of the system variable. Several parameters are mainly related to the query. It is necessary to mention them here:

Delayed_queue_size

This parameter determines the number of rows in the queue from the insert delayed statement before the client that executes other insert delayed statements is blocked. Add the value of this parameter so that the MySQL server can receive more rows from this request, so that the client can continue to execute without blocking.

Key_buffer_size

This parameter is the buffer size used to store index blocks. If the memory is large, increasing this value can save the index creation and modification time. A large value enables MySQL to store more index blocks in the memory, which increases the possibility of finding key values in the memory without reading disk blocks.

In MySQL 3.23 and later versions, if the size of the key buffer is increased, you may also want to use the -- init-file option to start the server. In this way, you can specify an SQL statement file that is executed when the MySQL server is started. If you want to store read-only tables in the memory, you can copy them to the index to find the HEAP table that is very fast.

Back_log

Number of client connection requests introduced. These requests are queued when they are processed from the current client. If you have a very busy site, you can increase the number of changes.

How compilation and linking affect MySQL speed

Most of the following tests are performed on Linux using the MySQL benchmark, but they should give some instructions to other operating systems and workloads.

When you use the-static link, you get the fastest executable file. Using Unix sockets instead of TCP/IP to connect to a database also provides better performance.

In Linux, you will get the fastest code when compiling with pgcc and-O6. To compile "SQL _yacc.cc" with these options, you need about MB of memory, because gcc/pgcc requires a lot of memory to embed all functions (inline ). When configuring MySQL, you should also set CXX = gcc to avoid including libstdc ++ Library (which is not required ).

By using a better compiler or a better compiler option, you can get a 10-30% acceleration in your application. This is especially important if you compile the SQL server on your own!

On Intel, you should use the pgcc or Cygnus CodeFusion compiler to get the maximum speed. We have tested the new Fujitsu compiler, but it is not enough to optimize MySQL compilation.

Here are some tables we have done:

· If you use pgcc with-O6 and compile anything, the mysqld server is 11% faster than using gcc (using the string 99 version ).

· If you dynamically Link (without-static), the result will be 13% slower. Note that you can still use a dynamically connected MySQL database. Only servers are critical to performance.

· If you use TCP/IP instead of a Unix socket, the result is 7.5% slower.

· On a Sun SPARCstation 10, gcc2.7.3 is 4.2 faster than Sun Pro C ++ 13%.

· On Solaris 2.5.1, MIT-pthreads on a single processor is 8-12% slower than Solaris with native threads. With more loads/cpus, the difference should be larger.

Distribution of MySQL-Linux provided by TcX is compiled by pgcc and statically linked.

Summary

This section briefly introduces how to optimize the database performance on the MySQL server 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.