Maximize optimal performance from MySQL

Optimization is a complex task because it ultimately requires an understanding of the entire system. When it is possible to do some local optimization with your system/application little knowledge, the more you want to optimize your system, the more you have to know. Therefore, this chapter will attempt to explain and give some examples of the different ways to optimize MySQL. But remember that there are always certain (and gradually difficult) ways in which the system can be kept in a faster way. The most important part of making a system faster is of course the basic design. You also need to know that your system will do such things, and that is your bottleneck. The most common bottlenecks are:

Disk finder. Disk takes time to find a data, the average time spent on modern disks in the 1999 is usually less than 10ms, so theoretically we can find 1000 times a second. This time is slow to use new disks and is difficult to optimize for a table. The way to optimize it is to spread the data across multiple disks. Disk Reads/writes when the disk is in the correct position to read the data. With 1999 years of modern, a disk transmission resembles 10-20mb/s. This must be optimized because you can read in parallel from multiple disks. CPU cycles. When we read data into memory, (or if it is already there) we need to deal with it to achieve our results. This is the most common constraint when we have a table with a smaller relative memory, but it is usually not a problem to use a small table speed. Memory bandwidth. When the CPU needs to exceed the CPU-cached data, the cache bandwidth becomes a bottleneck in memory. This is an uncommon bottleneck for most systems but you should know it. 10.2 System/compile-time and startup parameter tuning we started with something at the system level, because some of these decisions have been made early. In other cases, it may be enough to quickly browse this part, because it is not important for big gains, but it is always good to have a sense of how much is harvested at this level. The default OS used is really important! To maximize the use of multiple CPUs, you should use Solaris (because the threads work really well) or Linux (because the 2.2 core is really good SMP support). And on 32-bit machines, Linux defaults to 2G file size limits. When the new file system is released ( XFS), hopefully this will be corrected soon. Since we don't run production MySQL on many platforms, we advise you to test the platform you plan to run before you can choose it.

Other suggestions:

If you have enough RAM, you can remove all switching 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 that this will not affect MySQL functionality, as long as it is running on only one server. Just remember to stop the server (or lock the relevant part) before you run MYISAMCHK. On some systems, this switch is mandatory. Because external locking does not work under any circumstances. When compiling with mit-pthreads,--skip-locking option defaults to open (on) because Flock () Not fully supported on all platforms. The only situation is if you run a MySQL server (not a client) on the same data, you cannot use the--skip-locking, otherwise you will not be able to clear the mit-pthreads (flushing) or lock the MYSQLD server's table to run MYISAMCHK. You can still use lock tables/unlock tables even if you are using--skip-locking.

How compilation and linking affect MySQL's speed

Most of the following tests are done on Linux with MySQL benchmarks, but they should give some indication of 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 can also give you some good performance. On Linux, when compiling with PGCC and-o6, you get the fastest code. To compile "sql_yacc.cc" with these options, you need approximately 200M of RAM because GCC/PGCC requires a lot of memory to embed all functions (inline). When you configure MySQL, You should also set the CXX=GCC to avoid including the Libstdc++ library (it does not need to). You can get a 10-30% acceleration in your application only by using a better compiler or better compiler option. If you compile your own SQL Server, this is especially important! On Intel, you should use the PGCC or Cygnus codefusion compiler for example to get the maximum speed. We have tested the new Fujitsu compiler, but it is not enough to make mistakes to optimize the MySQL compilation.

Here are some of the gauges we've done:

If you use PGCC and compile anything with-o6, the MYSQLD server is 11% faster than GCC (with a version of String 99). If you dynamically link (no-static), the result is 13% slower. Note that you can still use a dynamically connected MySQL library. Only the server is 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 the Solaris 2.5.1, Mit-pthreads is slower 8-12% on a single processor than with a native thread. The difference should be greater with more load/cpus. The distribution of Mysql-linux supplied by TCX is compiled and statically linked by PGCC.

As mentioned earlier, disk seek is a major bottleneck in performance. This problem becomes more and more apparent as data begins to grow so that the cache becomes impossible. To large databases, where you have to more or less randomly access data, You can count on you will need at least one disk seek to read and several disk seek writes. To minimize this problem, use a disk with low seek time. To increase the number of available disk spindles (and thereby reduce the seek overhead), it is possible for symbols to join files to different disks or split disks. Using symbolic connections This means that you link index/data file symbols from the normal data directory to the other disk (which can also be split). This makes the search and read time better (if the disk is not something else). See 10.2.2.1 Use database and table symbolic links. Split segmentation means you have a lot of disks and put the first block on the first disk, the second block is placed on the second disk, and the nth block is on the (n mod number_of_disks) disk, and so on. This means that if your normal data size is split (or perfectly aligned), You will get a better performance. Note whether the split is very dependent on the OS and the size of the split. So test your application with a different split size. See 10.8 Use your own benchmark. Note that the speed difference between partitions depends very much on the parameters, depending on how you divide the parameters and the number of disks, You can draw a different order of magnitude. Note that you must choose to optimize for random or sequential access. In order to be reliable, you may want to use RAID 0+1 (split + mirroring), but in this case you will need to 2*n a drive to save N drive data. If you have money, this may be the best choice! However, you may also have to invest in some volume management software investments to handle it efficiently. One good option is to have a RAID 0 disk on a bit of important data (it regenerates). Data that is really important (like host information and log files) is on a raid 0+1 or RAID n disk. If you have many writes because of more novelty, RAID n 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 time it skips updates in the inode, and this avoids some disk finder.

You can move tables and databases from the database directory and replace them with symbols linked to new locations. You might want to do this, for example, to transfer a database to a file system with more free space. If MySQL notices that a table is a symbolic link, it parses the symbolic link and uses the table it actually points to, which works on all systems that support Realpath (at least Linux and Solaris Support Realpath ())! On systems that do not support Realpath (), you should not access the table at the same time through the real path and symbolic links! If you do this, the table will be inconsistent after any updates. MySQL defaults do not support database links. As long as you do not have a symbolic link between the database, everything will work fine. Suppose you have a database db1 in the MySQL data directory, and you do a symbolic link db2 point to DB1:

Shell&> Cd/path/to/datadir

Shell&> ln-s DB1 DB2

Now, for any table in the DB1 tbl_a, there is a table tbl_a in the DB2 species. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be a problem. If you do need to do this, 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 this:

if (Realpath (To,buff))