Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how MySQL server disk is divided and optimized". In daily operation, I believe many people have doubts about how MySQL server disk is divided and optimized. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how MySQL server disk is divided and optimized". Next, please follow the editor to study!

1. Use symbolic connections

This means that you link index / data file symbols from normal data directories to other disks (which can also be segmented). This makes seek and read time better (if the disk is not used for other things)

2.Segmentation

Splitting means that you have many disks and put the first on the first, the second on the second, and the nth on the (nmodnumber_of_disks), and so on. This means that if your normal data is smaller than the split size (or perfectly arranged), you will get better performance. Note whether the segmentation depends heavily on the OS and the size of the split. So test your application with different partition sizes. See 10.8 use your own benchmark. Note that the difference in the speed of segmentation depends on the parameters, depending on how you split the parameters and the number of disks, you can get a quantitative difference. Note that you must choose to optimize for random or sequential access.

To be reliable, you may want to use attack RAID0+1 (split + Mirror), but in this case, you will need 2N 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 investment to handle it efficiently.

A good choice is to have the more important data (which can be regenerated) be stored on the RAID0 disk, and the really important data (such as host information and log files) on a RAID0+1 or RAIDN disk. If you have a lot of writes because of newer even bits, RAIDN may 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 for it to skip updates in inode, and this will avoid some disk seek.

Hardware issu

Hardware can be used to improve server performance more effectively:

1. Install more memory in the machine. This can increase the cache and buffer size of the server, make the server use the information stored in memory more frequently, and reduce the requirement of fetching information from disk.

2. If there is enough RAM for all swapping to be done in the in-memory file system, you should reconfigure the system to remove all disk swapping settings. Otherwise, even if there is enough RAM to swap, some systems still have to swap with the disk.

3. Add faster disks to reduce the waiting time of Icano. Seek time is the main factor determining performance here. Moving the head word for word is slow, and once the head is positioned, it is faster to read the block from the track.

Try to redistribute disk activity on different physical devices. If possible, store your two busiest databases on different physical devices. Note that it is not enough to use different partitions on the same physical device. This does not help because they will still compete for the same physical resources (disk heads). The process of moving a database is introduced in Chapter 10.

4. You should be sure to understand the loading characteristics of the system before replacing the data to different devices. If there is already some specific major activity on a particular physical device, putting the database there may actually make performance worse. For example, do not move the database to a Web server device that handles a large amount of Web traffic.

5. When setting up MySQL, you should configure it to use static libraries instead of shared libraries. Dynamic binary systems that use 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 the UDF mechanism relies on dynamic connections).

How to split and optimize the disk of MySQL Server

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, and several of the parameters are mainly related to the query, so it is necessary to mention here:

Delayed_queue_size

This parameter determines the number of rows placed in the queue from the INSERTDELAYED statement before the client executing the other INSERTDELAYED statement blocks. Increasing the value of this parameter allows the server to receive more rows from this request, so the client can continue to execute without blocking.

Key_buffer_size

This parameter is the size of the buffer used to store the index block. If you have a lot of memory, increasing this value can save time for index creation and modification. Higher values allow MySQL to store more index blocks in memory, which increases the likelihood of finding keys in memory without reading disk blocks.

In MySQL3.23 and later versions, if you increase the size of the key buffer, you may also want to start the server with the-- init-file option. This allows you to specify a file of SQL statements 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 the index to find very fast HEAP tables.

Back_log

Introduces the number of client connection requests that are queued as they are processed from the current client. If you have a busy site, you can increase the value of the change variable.

How compilation and linking affect the speed of MySQL

Most of the following tests are done on Linux and with MySQL benchmarks, but they should give some instructions to other operating systems and workloads.

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

On Linux, when compiling with pgcc and-O6, you will get the fastest code. In order to compile "sql_yacc.cc" with these options, you need about 200m of memory, because gcc/pgcc needs a lot of memory to inline all functions. When configuring MySQL, you should also set CXX=gcc to avoid including the libstdc++ library (it is not required).

You can get a 10-30% acceleration in your application only by using a better compiler or a better compiler option. This is especially important if you compile the SQL server yourself!

On Intel, you should use the pgcc or CygnusCodeFusion compiler for maximum speed, for example. We have tested the new Fujitsu compiler, but it is not error-free enough to optimize the compilation of MySQL.

Here are some of the measurements we have made:

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 link dynamically (without-static), the result is 13% slower. Note that you can still use a dynamically linked MySQL library. Only the server is critical to performance.

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

On a SunSPARCstation10, gcc2.7.3 is 13% faster than SunProC++4.2.

On Solaris2.5.1, MIT-pthreads is 8-12% slower than Solaris with native threads on a single processor. With more load / cpus, the difference should become greater.

The distribution of MySQL-Linux provided by TcX is compiled with pgcc and statically linked.

At this point, the study on "how the MySQL server disk is divided and optimized" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.