Get maximum optimized performance from MySQL
- 2020-05-06 11:44:38
- OfStack
Optimization is a complex task, because it needs the understanding of the whole system. When using your knowledge of the system/application of small to do some local optimization is possible, the more you want to make your system better, you must know it. , therefore, this chapter will attempt to explain and give some examples of different methods of optimizing MySQL. But remember there is always some (progressively difficult) is left to do system faster method. in order to make a system faster, of course, is the most important part of the basic design. You also need to know your system will do such a thing, that's your bottlenecks. The most common bottleneck in is:
Disk seek. The disk takes time to find a data, and the average time for a modern disk in 1999 is usually less than 10ms, so in theory we can find 1000 times per second When the disk in the right place, we need to read data disk read/write. With 1999 years of modern, similar to a disk transfer 10-20 Mb/s. It will seek optimization more easily, because you can read from several disks in parallel. CPU cycle. When we read data into the memory (or if it is already there). We need to deal with it in order to achieve our results when we have a relatively small memory table, this is the most common limiting factor, but with a small table speed is not a problem. Usually Memory bandwidth. When CPU need beyond fit cpu cache data, cache bandwidth has become a bottleneck of memory. This is a not common for most system bottleneck but you should know it. 10.2 system and launch parameters adjustment/compile time what we to system level, some have long done because these decisions. In other cases, a quick look at this part may be enough, because it is not important for big harvest, but there is one on the feeling of how much this level, the harvest is always good. The default OS used is really important! To get the most out of CPU, you should use Solaris(because threads really work well) or Linux(because the 2.2 core really works well with SMP support). And on 32-bit machines,Linux has a default file size limit of 2G. Hopefully this will be fixed soon when the new file system is released ( XFS ) Since we don't run production MySQL on many platforms, we advise you to test the platform you intend to run on before you might choose it.
Other Suggestions:
If you have enough RAM, you can remove all switching devices. Some operating systems will use an SWAP device in some cases, even if you have free memory. is used --skip - locking MySQL options to avoid external locking. Note that this will not affect MySQL function, as long as it runs only on a server. As long as before you run myisamchk, remember to stop the server (or lock). This switch is mandatory in some systems, because the external locking is not under any circumstances. When compiled using MIT - pthreads - The skip-locking option defaults to open (on) because flock() is not fully supported by MIT-pthreads on all platforms UNLOCK TABLES, even if you're using -- skip-locking.
How does compilation and linking affect MySQL's speed
Most of the following tests were performed on Linux using the MySQL benchmark, but they should give some indication of other operating systems and workloads On Linux, you will get the fastest code when compiled with pgcc and -O6. To compile "sql_ms.cc" with these options, you will need around 200M 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 the libstdc++ library (it does not). gives you a 10-30% acceleration in your application just by using a good compiler or a good compiler option. This is especially important if you compile SQL servers yourself! on Intel, you should for example use pgcc or Cygnus CodeFusion compiler to get the maximum speed
Here are some of the measures we did:
If you used in - O6 pgcc and compile anything mysqld server is faster than using gcc 11% (in 99 version of the string) if you dynamically link (no - static), and the results slowed by 13% for attention you can still use a dynamic connection MySQL library. Only the server is the key to performance. if you use TCP/IP rather than Unix sockets, results slow 7.5% On an Sun SPARCstation 10, gcc2.7.3 is 13% faster than Sun Pro C++ 4.2 2.5.1 MIT-pthreads is 8-12% slower on a single processor than Solaris with native threads
As stated earlier, disk seek is a performance bottleneck. When data growing impossible that cache, this problem is becoming more and more obvious. For large database, in that you more or less random access to data, you can rely on you will need at least a disk seek to read a few times and seek written to disk. In order to minimize this problem, using a low disk seek time. in order to increase the number of available disk shaft (and thus reducing seek overhead), symbolic link files to a different disk or partition the disk is possible. Use symbolic link that means you will index/data file symbols from normal data directory links to other disk (it can also be divided). This makes the seek time and read better (if the disk is not used for other things). See 10.2.2.1 use symbolic links database and table. segmentation segmentation means that you have a lot of disk and the first on the first disk, in the second block in the second disk, and the first n block in the first (n mod number_of_disks) on disk, etc. This means that if your normal data size is greater than the split size (or perfectly aligned), you will get better performance Use your own benchmarks. Pay attention to the speed of segmentation difference is dependent on parameters, depends on how you segmentation parameters and amount of disk, you can draw by orders of magnitude different. Note that you have to choose as random or sequential access optimization. to reliable, you might want to use attack RAID 0 + 1 (+ image segmentation), but in this case, you will need to be 2 * N drive to save N drive data. If you have money, it may be the best choice! But you may also need to invest some investment volume management software to effectively deal with it. a good choice is to let a little important data (which can regenerate) on RAID 0 on the disk, and will really important data (like the host information and log files) there is a RAID 0 + 1 or RAID N disk. If because of the more novel parity bit write, you have many RAID N can be a problem. you can also set parameters on 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 skipped updates in inode, and this will avoid some disk seek.
You can from the database directory database tables and elsewhere, and symbols to replace them with a link to the new site. You may want to do so, for example, transfer of a database to a more free space in a file system. If MySQL noticed a table is a symbolic link, it will parse a symbolic link and the use of the actual point table, it can work in support realpath all system () call (at least Linux and Solaris support realpath ())! On systems that do not support realpath(), you should not access tables via both real paths and symbolic links! If you do this, the tables will not be consistent after any updates. MySQL does not support database linking by default. As long as you do not make a symbolic link between databases, everything will work fine
shell& > cd /path/to/datadir
shell& > ln -s db1 db2
Now, for either table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be a problem. If you do, you must change the following code in "mysys/ mf_format.c" :
if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */
if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
Change the code to look like this:
if (realpath(to,buff))
Disk seek. The disk takes time to find a data, and the average time for a modern disk in 1999 is usually less than 10ms, so in theory we can find 1000 times per second When the disk in the right place, we need to read data disk read/write. With 1999 years of modern, similar to a disk transfer 10-20 Mb/s. It will seek optimization more easily, because you can read from several disks in parallel. CPU cycle. When we read data into the memory (or if it is already there). We need to deal with it in order to achieve our results when we have a relatively small memory table, this is the most common limiting factor, but with a small table speed is not a problem. Usually Memory bandwidth. When CPU need beyond fit cpu cache data, cache bandwidth has become a bottleneck of memory. This is a not common for most system bottleneck but you should know it. 10.2 system and launch parameters adjustment/compile time what we to system level, some have long done because these decisions. In other cases, a quick look at this part may be enough, because it is not important for big harvest, but there is one on the feeling of how much this level, the harvest is always good. The default OS used is really important! To get the most out of CPU, you should use Solaris(because threads really work well) or Linux(because the 2.2 core really works well with SMP support). And on 32-bit machines,Linux has a default file size limit of 2G. Hopefully this will be fixed soon when the new file system is released ( XFS ) Since we don't run production MySQL on many platforms, we advise you to test the platform you intend to run on before you might choose it.
Other Suggestions:
If you have enough RAM, you can remove all switching devices. Some operating systems will use an SWAP device in some cases, even if you have free memory. is used --skip - locking MySQL options to avoid external locking. Note that this will not affect MySQL function, as long as it runs only on a server. As long as before you run myisamchk, remember to stop the server (or lock). This switch is mandatory in some systems, because the external locking is not under any circumstances. When compiled using MIT - pthreads - The skip-locking option defaults to open (on) because flock() is not fully supported by MIT-pthreads on all platforms UNLOCK TABLES, even if you're using -- skip-locking.
How does compilation and linking affect MySQL's speed
Most of the following tests were performed on Linux using the MySQL benchmark, but they should give some indication of other operating systems and workloads On Linux, you will get the fastest code when compiled with pgcc and -O6. To compile "sql_ms.cc" with these options, you will need around 200M 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 the libstdc++ library (it does not). gives you a 10-30% acceleration in your application just by using a good compiler or a good compiler option. This is especially important if you compile SQL servers yourself! on Intel, you should for example use pgcc or Cygnus CodeFusion compiler to get the maximum speed
Here are some of the measures we did:
If you used in - O6 pgcc and compile anything mysqld server is faster than using gcc 11% (in 99 version of the string) if you dynamically link (no - static), and the results slowed by 13% for attention you can still use a dynamic connection MySQL library. Only the server is the key to performance. if you use TCP/IP rather than Unix sockets, results slow 7.5% On an Sun SPARCstation 10, gcc2.7.3 is 13% faster than Sun Pro C++ 4.2 2.5.1 MIT-pthreads is 8-12% slower on a single processor than Solaris with native threads
As stated earlier, disk seek is a performance bottleneck. When data growing impossible that cache, this problem is becoming more and more obvious. For large database, in that you more or less random access to data, you can rely on you will need at least a disk seek to read a few times and seek written to disk. In order to minimize this problem, using a low disk seek time. in order to increase the number of available disk shaft (and thus reducing seek overhead), symbolic link files to a different disk or partition the disk is possible. Use symbolic link that means you will index/data file symbols from normal data directory links to other disk (it can also be divided). This makes the seek time and read better (if the disk is not used for other things). See 10.2.2.1 use symbolic links database and table. segmentation segmentation means that you have a lot of disk and the first on the first disk, in the second block in the second disk, and the first n block in the first (n mod number_of_disks) on disk, etc. This means that if your normal data size is greater than the split size (or perfectly aligned), you will get better performance Use your own benchmarks. Pay attention to the speed of segmentation difference is dependent on parameters, depends on how you segmentation parameters and amount of disk, you can draw by orders of magnitude different. Note that you have to choose as random or sequential access optimization. to reliable, you might want to use attack RAID 0 + 1 (+ image segmentation), but in this case, you will need to be 2 * N drive to save N drive data. If you have money, it may be the best choice! But you may also need to invest some investment volume management software to effectively deal with it. a good choice is to let a little important data (which can regenerate) on RAID 0 on the disk, and will really important data (like the host information and log files) there is a RAID 0 + 1 or RAID N disk. If because of the more novel parity bit write, you have many RAID N can be a problem. you can also set parameters on 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 skipped updates in inode, and this will avoid some disk seek.
You can from the database directory database tables and elsewhere, and symbols to replace them with a link to the new site. You may want to do so, for example, transfer of a database to a more free space in a file system. If MySQL noticed a table is a symbolic link, it will parse a symbolic link and the use of the actual point table, it can work in support realpath all system () call (at least Linux and Solaris support realpath ())! On systems that do not support realpath(), you should not access tables via both real paths and symbolic links! If you do this, the tables will not be consistent after any updates. MySQL does not support database linking by default. As long as you do not make a symbolic link between databases, everything will work fine
shell& > cd /path/to/datadir
shell& > ln -s db1 db2
Now, for either table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be a problem. If you do, you must change the following code in "mysys/ mf_format.c" :
if (!lstat(to,&stat_buff)) /* Check if it's a symbolic link */
if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
Change the code to look like this:
if (realpath(to,buff))