Optimization of the manager of MySQL query optimization lectures

The optimizations described in the previous section are implemented by a MySQL user with no privileges. The system administrator who can control the MySQL server or computer can perform additional optimization measures. For example, some server parameters are attached to the query processing process and can be adjusted, and some hardware configuration factors have a direct impact on the query processing speed. In many cases, these optimizations improve the performance of the entire server, so that all MySQL users can benefit.

Generally, when you perform administrator optimizations, you should keep the following rules in mind:

· Accessing data in memory is faster than accessing data on the disk.

· Keeping data in memory as much as possible can reduce disk operations.

· Keeping information in an index is more important than keeping the contents of a data record.

We'll discuss how to apply these rules later.

Increase the size of the server cache. The server has many parameters (System variables) that you can change to affect the operation of the server. Several of these parameters directly affect the speed of query processing. The most important parameter you can change is the size of the data table cache and the cache size that the storage engine uses to buffer the index operation information. If you have the available memory, assign it to the server's cache to allow information to be stored in memory and reduce disk operations. This works well because accessing the information in memory is much faster than reading information from disk.

· When the server opens the table file, it tries to keep the files open to reduce the number of open file operations. To implement such a feature, it maintains information about open files in the table cache. The Table_cache system variable controls the size of the cache. If the server accesses a large number of tables, the table cache is filled up, and the server closes the tables that have not been used for some time, leaving room for the new table to open. You can access the effect of the table cache by checking the Opened_tables status indicator:

Show STATUS like ' opened_tables ';

Opened_tables shows the number of times a data table must be opened (because it is not yet open). This value is also displayed as the opens value in the output information of the Mysqladmin status command. If the number is stable or slowly growing, then it may be set correctly. If this number grows fast, it means that the cache is too small, and you must often close the datasheet to make room for other data tables to open. If you have file description information, increasing the table cache size will reduce the number of data table open operations.

· The MyISAM storage engine uses a key buffer to maintain an index block of information about an index-related operation. Its size is controlled by the key_buffer_size system variable. The larger the value, the more information that MySQL keeps in memory, and the possibility of finding key values in memory, rather than reading new chunks of information from disk. The default size of the key cache is 8MB. If you have a lot of memory, this is a very conservative value, you can directly increase its size, and you will see indexed retrieval, indexing and modification of the performance of the operation has greatly improved.

In the MySQL 4.1 version, you can create additional key caching for the MyISAM datasheet and specify that some tables use them. This can help increase the speed of query processing on these data tables.

· The InnoDB and BDB engines have their own caches for buffering data and index values. Their size is controlled by innodb_buffer_pool_size and bdb_cache_size variables. The InnoDB engine also maintains a log buffer. The innodb_log_buffer_size variable can control its size.

· Another dedicated cache is the query cache, which we explained in the "Using Query Caching" section.

When you change the values of these parameters, you should follow some of the following principles:

· Change only one argument at a time. If you change multiple independent variables at once, it is difficult to assess the effect of each change.

· Gradually increase the value of the system variable. According to the theory, the more the quantity, the better the performance, but if you make a variable too large, it may cause a lack of system resources, resulting in reverse effect, reduce speed.

· Do not try to adjust the parameters of the server running the business MySQL database, it is better to set up a stand-alone test server.

· To get a general idea of which parameter variables might be appropriate for your own system, you can view the MY-SMALL.CNF, MY-MEDIUM.CNF, MY-LARGE.CNF and my-huge.cnf option files (on UNIX systems, you can always find these files in the support file directory of the source publishing files and the shared directory of the binaries publishing files.) On Windows, they are located in the basic installation directory with an. ini extension. These files may let you know that it is best to change the parameters on the server to accommodate different levels of use, and provide some typical values for these parameters.

Other strategies for improving the performance of the server include:

Prohibit unwanted storage engines. The server does not allocate any memory for the banned engine, so we can take advantage of that. If you build MySQL from a source file, most storage engines can be excluded from the server when configured. For the engines that are included in the server, the most of them can be banned at run time using the appropriate startup options.

Maintain the simplicity of authorization table permissions. Although the server caches the contents of the authorization table in memory, if you have some data rows in the Tables_priv or Columns_priv table, the server must check the table-level and column-level permissions for each query statement. If these tables are empty, the server can optimize its own permission-checking process and skip these levels.

If you build MySQL from a source file, configure it to use a static class library instead of a shared class library. Save disk space using dynamic binaries for shared class libraries, but static binaries are faster. However, if you use a user-defined function (UDF) mechanism, some systems require dynamic linking. On such systems, static binaries do not work.

Using the MyISAM key cache

When MySQL executes a statement that leverages the index of the MyISAM datasheet, it uses the key cache to hold the index value. This cache reduces disk I/O: If a data table needs a key value that is found in the cache, it does not need to be read from disk again. Unfortunately, this key cache is limited and, by default, is shared by all MyISAM data tables. If the key value is not found in the key cache and the key cache is full, contention will result in the need to discard certain values in the cache and leave room for the new values. The next time you need a value that has been discarded, you must read it again from the disk.

If you rely heavily on MYISAM data tables, it would be nice to keep their keys in memory, but contention in the cache can result in the opposite effect. Reading data from the same table or from a different table can cause contention. You can avoid contention for the same data table by setting the key cache enough to hold all indexes of a particular datasheet, but the keys of other data tables still need to contend with cache space.

The MySQL 4.1 version provides a solution to this problem: It enables us to create multiple key caches and allows us to specify the index of a data table and to mount a cache beforehand. This is useful if your datasheet is used very frequently and you have enough memory to load its index into the cache. This ability allows you to simultaneously avoid contention between the same table and different tables: Create a cache that is large enough to hold all the indexes of the datasheet, and specify that the cache is dedicated to that data table. The disk I/O operation is not required after the key is loaded into the cache. Also, the key value is never discarded, and the view of the key to the datasheet can be done in memory.