Mysql optimization 11_mysql

Mysql optimization 11 translator: Ye Jinrong (Email :), source: MySQL manual version 5.0.20, source: http://imysql.cn, reprint please indicate the translator and the source, and cannot be used for commercial purposes, offenders must investigate.
Disk 7.6

* Disk search is a major performance bottleneck. This problem is especially evident when the data grows so large that the effective cache cannot be used. When you access a large database more or less immediately, there will inevitably be at least one disk search to read data and two disk searches to write data. To minimize this problem, reduce the number of disk searches.
* Increase the number of valid disk motors (which can reduce the search overhead) or link different files to different disks or disk shards:

Use symbolic links
This means that the index file and/or data file of the MyISAM table are linked to other disks from the usual place in the data directory (this is also a disk Shard ). If this disk is not used for other purposes, the read/write count is better. For details, see "7.6.1 Using Symbolic Links ".
Split disk
If there are several disks, place the first block on the first disk, the second block on the second disk, and so on. This means that the normal data size is smaller than the data size after the disk (or exactly the same), which can achieve better performance. Disk splitting depends entirely on the operating system and disk splitting size. Therefore, you must use different disk splitting sizes to benchmark the application. For details, see "7.1.5 Using Your Own Benchmarks ". The speed of the benchmark test depends entirely on the disk size. Depending on the disk Shard setting parameters and disk quantity, a large number of different measurement results are obtained. The optimization method must be selected randomly or sequentially.

* RAID 0 + 1 may be used for reliability. in this case, 2 * N drivers must be used to store data on N drives. If there is enough memory, this may be the best method. However, this also requires the use of volume management software to effectively manage data.
* Another good solution is that the RAID level depends on the importance of data. For example, save some important data that can be regenerated on a RAID 0 disk, and store important data such as host information logs on a RAID 0 + 1 or raid n disk. Raid n may be faulty when there are many writes, because it requests to update the same byte at the same time.
* In Linux, hdparm can be used to configure disk interfaces for better performance (up to 100% under load is not impossible ). The following hdparm configuration options are suitable for MySQL and may be good for other applications:

Hdparm-m 16-d 1

Note: When this command is used, the performance and reliability will depend on the hardware. Therefore, we strongly recommend that you do the test after hdparm is used. Please refer to the hdparm manual. If hdparm is not correctly used, file system conflicts may occur, so back up before the test.
* You can also set the parameter on the file system used by the database: If you do not need to know the last access time of the file (this is useless to the database system ), use the-o noatime option when mounting a file system. This skips the last access time to update the file system node and reduces disk search. In many operating systems, you can use the-o async option to update the file system asynchronously. If your machine is quite stable, this will bring about performance improvement, but the reliability is not sacrificed much (it can only be used in Linux by default ).

7.6.1 use symbolic links

You can move a data table or database to another directory and then use a symbol to link to a new location. You may want to do this. for example, if you want to distribute tables to different disks to increase the system speed, you can move them to disks with more available space. We recommend that you only link the database to another disk. the data table link is the final choice.
7.6.1.1 link a database with symbols on Unix

On Unix, a symbolic link is used to create a directory on another disk and then link it to the MySQL data file directory.

Shell> mkdir/dr1/databases/test
Shell> ln-s/dr1/databases/test/path/to/datadir

MySQL does not support linking a directory to multiple databases. As long as there is no symbolic link between databases, it is okay. If a database db1 already exists in the MySQL data file directory, link db1 to db2:

Shell> cd/path/to/datadir
Shell> ln-s db1 db2

Now, the table tbl_a in db1 will also appear in db2. If one client updates db1.tbl _ a and the other updates db1.tbl _ a, a problem occurs.

If you do need to do so, modify a source file. The file to be modified varies with the MySQL version. For MySQL 4.0 or update, find the following statement in the 'mysys/my_symlink.c 'File:

If (! (MyFlags & MY_RESOLVE_LINK) |
(! Lstat (filename, & stat_buff) & S_ISLNK (stat_buff.st_mode )))

Before MySQL 4.0, find the following statement in the 'mysys/mf_format.c 'File:

If (flag & 32 | (! Lstat (to, & stat_buff) & S_ISLNK (stat_buff.st_mode )))

Then change the statement:

If (1)

On Windows, you can use the option DUSE_SYMDIR to compile MySQL to support directory symbolic links. This allows you to store different Databases on different disks. for details, see "7.6.1.3 Using Symbolic Links for Databases on Windows ".
7.6.1.2 symbolic link to a data table on Unix

Before MySQL 4.0, do not link data tables unless you are particularly careful. One problem is that when you execute alter table, repair table, or optimize table on a symbolic chain TABLE, the symbolic link will be deleted and replaced with the original file. This is because when executing these statements, you need to create a temporary file under the Database Directory, and then replace the temporary file with the original file after the operation is complete. It is recommended that you do not link data tables on operating systems that do not support realpath () calling (but at least Linux and Solaris support realpath ()). Run the show variables like 'have _ symlink 'statement to check whether your system supports symbolic links. In MySQL 4.0, the MyISAM table fully supports symbolic links. If other table types also have symbolic links, some strange problems may occur before the statement is executed. The MyISAM table symbolic link in MySQL 4.0 works as follows:

* In the data directory, there are always table definition files, data files, and index files. Data and index files can be moved anywhere and replaced by symbolic links, but table definition files cannot.
* Data and index files can be linked to different directories respectively.
* When mysqld is not running, you can use the command line ln-s to manually complete the symbolic link. If SQL is used, you can use the DATA DIRECTORY and INDEX DIRECTORY options when creating TABLE to tell the server to use symbolic links. For details, see "14.2.6 create table Syntax ".
* Myisamchk does not replace the data or index file of the symbolic link. It operates directly on the file to which the symbolic link points. Any temporary file is created in the directory where the data or index file is located.
* After a table with a symbolic link is deleted, the linked table and the table to which it points will be deleted. This is why mysqld cannot be run as root. Similarly, users are not allowed to write MySQL database directories.
* If you use alter table... if the RENAME statement renames a table and does not move it to another database, the file in the database directory will be renamed. correspondingly, the data or index file it points to will also be renamed.
* If you use the alter table... RENAME statement to move a TABLE to another database, the TABLE will be moved to another Database Directory. The old link and the file to which it points are deleted. In other words, the new table will no longer be linked by symbols.
* If the symbolic link is not used, add the option -- skip-symbolic-links to mysqld to ensure that no one can delete or rename files outside the data file directory.

Before MySQL 4.0.15, the show create table statement does not report whether a TABLE has symbolic links. The same is true for mysqldump. it uses show create table to generate the create table statement. Table symbolic link operations are not supported yet:

* The alter table operation ignores the data directory and index directory table options.
* The backup table and restore table do not consider symbolic links either.
* The '. frm' file cannot be symbolic links (as mentioned earlier, indexes and data files can be symbolic links ). Attempts to do so (such as using synonyms) will lead to some errors. Assume that there is a database db1 under the Database Directory, and there is a table tbl1 in the database. in the db1 Directory, link the tbl2 symbol to tbl1:

Shell> cd/path/to/datadir/db1
Shell> ln-s tbl1.frm tbl2.frm
Shell> ln-s tbl1.MYD tbl2.MYD
Shell> ln-s tbl1.MYI tbl2.MYI

Now, if one thread reads db1.tbl1 and the other thread updates db1.tbl2, the problem occurs:
O the query cache will be fooled (it considers that tbl1 is not updated, so the out-of-data result is returned ).
O execution of the ALTER Statement on tbl2 also fails.

7.6.1.3 link a database with symbols on Windows

From MySQL 3.23.16, the mysqld-max and mysql-max-nt servers can be compiled using the-DUSE_SYMDIR option on Windows. This allows you to store a database on another disk through a symbolic link. This is similar to the symbolic link on Unix, but the setting process is not the same. Starting from MySQL 4.0, symbolic links are supported by default. If not, use the skip-symbolic-links option to disable it.

[Mysqld]
Skip-symbolic-links

By default, symbolic links are not supported before MySQL 4.0. To support it, add the following content to the 'My. cnf 'or 'My. ini' File:

[Mysqld]
Symbolic-links

On Windows, create a file containing the path of the target directory under the MySQL data file directory for symbolic link. The name of this file is 'DB _ name. sym', and db_name is the name of the database. Assume that the MySQL data file directory is 'c:/mysql/data', and you want to put the database foo under the Directory 'd:/data/Foo. Set the parameters as follows:

1. confirm that the 'd:/data/Foo' directory exists and create it if necessary. If a database directory named 'foo' already exists in the data file directory, move it to the directory 'd:/data '. Otherwise, the symbolic link does not take effect. It is best not to run the server when you move the database to avoid possible problems.
2. create a file 'C:/mysql/data/foo. sym' with the path D:/data/foo /.

All tables under Database foo are created under D:/data/foo. Note: If the database directory already exists under the MySQL data file directory, the symbolic link will not be used.