Introduced methods to optimize MYSQL from several aspects to obtain performance. Optimization is a complex task as it ultimately requires an understanding of the entire system. While it is possible to do some local optimization with little knowledge of your system/application, the more you want to make your system more optimized, you must know It is also more. Therefore, this chapter will try to explain and give some examples of different ways to optimize MySQL. But remember that there are always certain (increasingly difficult) ways left to do that are faster for the system. In order to make a The most important part of making your system faster is of course the basic design. You also need to know that your system is going to do things like this, and that's your bottleneck. The most common bottlenecks are:
Disk seek. The time it takes a disk to find a piece of data. The average time for a modern disk used in 1999 was usually less than 10ms, so in theory we could seek about 1000 times a second. This time improves slowly with newer disks and is difficult to measure. One table optimization. The way to optimize it is to spread the data over multiple disks. The disks read/write when the disks are in the right location where we need to read the data. With modern 1999, one disk transfer is like 10-20Mb/s This must seek easier to optimize because you can read from multiple disks in parallel CPU cycles. When we read data into memory, (or if it is already there) we need to process it to achieve our results. When This is the most common limiting factor when we have tables with relatively small memory, but with small tables speed is usually not an issue. Memory bandwidth. Cache bandwidth becomes a bottleneck in memory when the CPU needs more data than can fit in the CPU cache. This is an uncommon bottleneck on most systems but you should be aware of it. 10.2 System/Compile-time and Boot Parameter Tuning We start with the system-level stuff, since some of these decisions are made very early. Other times, a quick glance at this section might be enough as it's not critical to the big gain, but it's always good to have a feel for how big the gain is at this level. The default OS used does matter! For maximum To use multiple CPUs to a certain extent, you should use Solaris (because threads work really well) or Linux (because the 2.2 core has really good SMP support). And on 32-bit machines, Linux has a file size limit of 2G by default. . 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 it before potentially choosing it.
Other suggestions:
If you have enough RAM, you can remove all swap devices. Some operating systems will use a SWAP device in some cases even if you have free memory. Use the --skip -locking MySQL option to avoid external locking. Note this Will not affect MySQL functionality as long as it is only running on one server. Just remember to stop the server (or lock the relevant parts) before you run myisamchk. On some systems this switch is mandatory because external locking is not available on any works in all cases. When compiling with MIT-pthreads, the --skip-locking option defaults to on (on) because flock() is not fully supported by MIT-pthreads on all platforms. The only case is if you When running the MySQL server (not the client) on the same data, you cannot use --skip-locking, otherwise run myisamchk on the table without first flushing or locking the mysqld server. You can still use LOCK TABLES/UNLOCK TABLES, even if you are using --skip-locking.
How compilation and linking affect MySQL speed
Most of the following tests were conducted on Linux and using the MySQL benchmark, but they should give some indication for other operating systems and workloads. You get the fastest executables when you link with -static. Using Unix sockets Connecting to a database instead of TCP/IP may also give better performance. On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile "sql_yacc.cc" with these options, you Requires about 200M memory, because gcc/pgcc requires a lot of memory to make all functions inline. When configuring MySQL, you should also set CXX=gcc to avoid including the libstdc++ library (it is not needed). Just by using a better With a better compiler or better compiler options, you can get a 10-30% speedup in your application. This is especially important if you compile the SQL server yourself! On Intel, you should e.g. use pgcc or the Cygnus CodeFusion compiler Get maximum speed. We have tested the new Fujitsu compiler, but it is not yet error-free enough to optimize the compilation of MySQL.
Here are some measurement sheets we have made:
If you use pgcc with -O6 and compile anything, the mysqld server is 11% faster than with gcc (with the string 99 version). If you link dynamically (without -static), the result is 13% slower. Note that you are still Can use a dynamically linked MySQL library. Only the server is critical for performance. If you use TCP/IP instead of Unix sockets, the result is 7.5% slower. On a Sun SPARCstation 10, gcc2.7.3 is faster than Sun Pro C++ 4.2 is 13% faster. On Solaris 2.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 even greater. Courtesy of TcX The MySQL-Linux distribution is compiled with pgcc and statically linked.
As mentioned earlier, disk seeking is a big performance bottleneck. This problem becomes more and more obvious when the data starts to grow so that caching becomes impossible. For large databases, where you have to be more or less random To access 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 disk spindles (and thereby reducing seek overhead), it is possible to symlink files to different disks or to split the disk. Using symlinks this means that you symbolically link the index/data files from the normal data directory to the other disk (that can also be split ). This makes seek and read times better (if the disk is not used for other things). See 10.2.2.1 Using symlinks to databases and tables. Split Split means you have many disks and put the first block on on one disk, the second block is on the second disk, and the nth block is on the (n mod number_of_disks)th disk, etc. This means that if your normal data size is smaller than the split size (or perfect (arranged accordingly), you will get slightly better performance. Note that splitting is very OS and split size dependent. So test your application with different split sizes. See 10.8 Using Your Own Benchmark. Note that splitting is very dependent on the OS and split size. The speed difference is very parameter dependent, depending on how you split the parameters and the number of disks, you can get orders of magnitude difference. Note that you have to choose to optimize for random or sequential access. For reliability, you may want to use raid RAID 0+ 1 (split + mirror), but in this case you will need 2*N drives to hold the data of N drives. If you have the money, this is probably the best option! However you may also have to invest some Invest in volume management software to handle it efficiently. A good option is to have the less important data (which can be reproduced) on a RAID 0 disk, and the really important data (like host information and log files) on a RAID 0+ 1 or RAID N disks. If you have a lot of writes due to updating parity bits, RAID N 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 in the inode which it skips updating, and this will avoid some disk seeks.
You can move tables and databases from the database directory to another location and replace them with symbols that link to the new location. You might want to do this, for example, by moving a database to a file system that has more free space. If MySQL notices A table is a symbolic link, which will resolve the symbolic link and use the table it actually points to. It works on all systems that support the realpath() call (at least Linux and Solaris support realpath())! On systems that do not support realpath() On your system, you should not access the table via both a real path and a symbolic link at the same time! If you do, the table will be inconsistent after any updates. MySQL does not support database links by default. As long as you do not make a symbolic link between databases, everything will work fine. Assume you have a database db1 in the MySQL data directory, and make a symbolic link db2 pointing to db1:
shell&> cd /path/to/datadir
shell&> ln -s db1 db2
Now, for any table tbl_a in db1, there will also be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be problems. If you really need this, you The following code in "mysys/mf_format.c" must be changed:
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 this:
if (realpath(to,buff))