Optimization measures of the administrator in the MySQL Query optimization lecture

What MySQL users can execute. The system administrator of 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 optimization measures improve the performance of the entire server, so it can benefit all MySQL users.

Generally, when you perform administrator optimization, remember the following rules:

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

· Try to save data in the memory to reduce disk operations.

· Retaining the index information is more important than retaining the data record content.

We will discuss how to apply these rules later.

Increase the server cache size. The server has many parameters (system variables). You can change these parameters to affect server operations. Several parameters directly affect the query processing speed. The most important parameter you can change is the data table cache size and the cache size used by the storage engine to buffer index operation information. If you have available memory, allocate it to the server cache to allow information to be stored in the memory and reduce disk operations. This will have a good effect, because the access to information in the memory is much faster than reading information from the disk.

· When the server opens table files, it tries to keep these files open to reduce the number of file opening operations. To implement this function, it maintains the information of opened files in the table cache. The table_cache system variable controls the cache size. If the server accesses a large number of tables, the table cache will be filled up, and the server will shut down those tables that have not been used for a period of time to open the new table. You can access the table cache by checking the Opened_tables status indicator:

Show status like 'opened _ tables ';

Opened_tables displays the number of times a data table must be opened (because it has not been opened ). This value is also displayed as the Opens value in the output information of the mysqladmin status command. If the number is stable or slow, the setting may be correct. If this number grows fast, it means that the cache is too small. You must close the data table frequently to open other data tables. If you have file description information, increasing the table cache size will reduce the number of open operations on the data table.

· MyISAM storage engine uses key Buffering to maintain index information blocks for index-related operations. Its size is controlled by the key_buffer_size system variable. The larger the value, MySQL will keep more index information blocks in the memory at a time, which can be added to the memory (instead of reading new information blocks from the disk) the possibility of finding the key value. The default size of the key cache is 8 MB. If you have a lot of memory, this is a very conservative value, you can directly increase its size, the performance of index-based retrieval, index creation, and modification operations is greatly improved.

In MySQL 4.1 or later versions, you can create an additional key cache for the MyISAM data table and specify tables to use them. This helps increase the query processing speed on these data tables.

· InnoDB and BDB engines have their own caches used to buffer data and index values. Their size is controlled by the 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 query cache. We will explain it in the "use query cache" section.

When you change these parameter values, follow these principles:

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

· Gradually increase the value of system variables. According to theory, the larger the number, the better the performance, but if you make a variable too large, it may cause a lack of system resources, leading to reverse effects and speed reduction.

· Do not conduct parameter adjustment experiments on the server that runs the business MySQL database. It is best to create an independent test server.

· To get a general idea of which parameter variables might fit your system, you can view the my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf option files included in the MySQL release documentation (on Unix systems, you can find these files in the directory that supports source distribution files and the shared directory of binary distribution files. On Windows, they are located in the basic installation directory, and their extension may be. ini ). These files may let you know that it is best to change the parameters on the server to adapt to different levels of use, and provide some typical values for these parameters.

Other policies used to improve server operation performance include:

Disable unnecessary storage engines. The server does not allocate any memory for the banned engine, so we can use this. If MySQL is created from the source file, most storage engines can be excluded from the server during configuration. For those engines that are included in the server, using appropriate startup options can disable most of them at runtime.
Maintain the simplicity of table authorization. Although the server caches the authorization table content in the 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 check process and skip these levels.

If you create MySQL from the source file, configure it to use a static class library instead of a shared class library. Using dynamic binary files of shared class libraries saves disk space, but static binary files are faster. However, if you use a UDF mechanism, some systems require dynamic links. In such systems, static binary files cannot work.

Use the MyISAM key to cache

When MySQL executes a statement that uses the index of the MyISAM data table, it uses the key cache to maintain the index value. This cache reduces disk I/O: If you find the key value required by a data table in the cache, you do not need to read it from the disk again. Unfortunately, this type of key cache is limited, and by default, it is shared by all MyISAM data tables. If the key value is not found in the key cache and the key cache is full, competition will result in: some values in the cache must be discarded to leave space for the new value. If you need the discarded values next time, you must read them from the disk again.

If you rely heavily on the MyISAM data table, it will be good to save its key in the memory, but the contention in the cache will lead to the opposite effect. Reading data from the same or different tables may cause contention. You can set the key cache to save all the indexes of a specific data table to avoid contention for the same data table. However, Keys of other data tables still need to compete for the cache space.

MySQL 4.1 and later versions provide a solution to this problem: it allows us to create multiple key caches, and allows us to specify the indexes of a data table and pre-load a cache. If your data table is frequently used and you have enough memory to load its indexes into the cache, this operation is useful. This capability allows you to avoid contention between the same table and different tables at the same time: create a large enough cache for it to store all the indexes of the data table, and specify the cache for that data table. After the key is loaded into the cache, disk I/O operations are not required. At the same time, the key value will never be discarded. You can view the key of the data table in the memory.

The following example shows how to create a key cache for the member data table of the sampdb Database. The cache name is member_cache and the size is 1 MB. When executing these commands, you must have SUPER permissions.

1. Create an independent cache that is sufficient to accommodate data table indexes:

Mysql> set global member_cache.key_buffer_size = 1024*1024;

2. Specify a key cache for the data table:

Mysql> cache index member IN member_cache;
+ --------------- + -------------------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ --------------- + -------------------- + ---------- +
| Sampdb. member | assign_to_keycache | status | OK |
+ --------------- + -------------------- + ---------- +

3. Read the data table index into its key cache in advance:

Mysql> load index into cache member;
+ --------------- + -------------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ --------------- + -------------- + ---------- +
| Sampdb. member | preload_keys | status | OK | + --------------- + -------------- + ---------- +

If you want to load other data tables into the same cache or create a key cache for other data tables, the above operations are sufficient.

 

 

Use query cache

The MySQL server can use the query cache to speed up the processing of the repeated SELECT statements. Its performance improvement is usually amazing. The cache query method is as follows:

· When a SELECT statement is executed for the first time, the server remembers the text content of the query and the returned results.

· When the server encounters this statement next time, it will not execute this statement again. Instead, it directly retrieves results from the query cache and returns the results to the client.

· The query cache is based on the text content of the query string received by the server. If the text of some queries is identical, those queries are considered to be the same. If some queries have different characters or come from clients that use different character sets or communication protocols, these queries are considered different. Similarly, if some queries use other functions, but do not actually point to the same data table (for example, a data table with the same name in different databases), they are also different.

· When a data table is updated, any cache queries related to the data table become invalid and will be discarded. This prevents the server from returning expired results.

By default, MySQL provides built-in query cache support. If you do not want to use this cache and want to avoid the performance overhead caused by it, you can use the -- without-query-cache option to run the configuration script to create a server.

To check whether a server supports querying the cache, you can check its have_query_cache system variable:

Mysql> show variables like 'have _ query_cache ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Have_query_cache | YES |
+ ------------------ + ------- +

For those servers that support querying the cache, the cache operation is based on three system variable values:

· Query_cache_type determines the cache query operation mode. The following table shows the available mode values:

Mode	Description
0	Do not cache query results or retrieve cached results.
1	Cache queries, unless they start with SELECT SQL _NO_CACHE.
2	Only queries starting with SELECT SQL _CACHE are cached as needed.

· Query_cache_size determines the amount of memory allocated to the cache, in bytes.

· Query_cache_limit: set the maximum result set to be cached to a smaller value. Query results larger than this value will not be cached.

For example, to activate the query cache and allocate 16 MB of memory to it, use the following settings in the configuration file:

[Mysqld]
Query_cache_type = 1
Query_cache_size = 16 M

Even if the value of query_cache_type is set to zero, the specified memory size of query_cache_size will be allocated. To avoid memory waste, the cache size must be set to greater than zero only when you want to activate the cache. At the same time, even if query_cache_type is not zero, the cache size of the query cache is set to zero.

The independent client that uses the query cache will operate in the default cache mode of the server. The client can use the following statement to change the default cache mode of its own query:

SET query_cache_type = val;

The val value can be 0, 1, or 2. Its meaning is the same as that of the query_cache_type variable when the server is started. In the SET statement, the OFF, ON, and DEMAND symbol values correspond to 0, 1, and 2.

The client can also control the cache operations of individual queries by adding a modifier in the SELECT keyword the day after tomorrow. If the cache mode is ON or DEMAND, the SELECT SQL _CACHE statement will cache the query results. The SELECT SQL _NO_CACHE statement does not cache the query results.

If some queries retrieve information from frequently changed data tables, it is useful to stop caching these queries. In this case, the cache may not be of much use. Assume that you store the Web server request logs in the MySQL data table and periodically run a set of statistical queries on the data table. For a very busy Web server, a new row is frequently inserted into the data table, so any cached query results of the data table quickly become invalid. The meaning is that, although you submit statistical queries cyclically, the query cache may have little value for these queries. In this case, it is best to use the SQL _NO_CACHE modifier to tell the server not to cache the results of these queries.

Hardware problems

Hardware configuration is not taken into account for the technologies discussed earlier in this article to help you improve server performance. Of course, you can use better hardware to make the server run faster. However, not all hardware-related changes have the same value. When evaluating which hardware improves performance, the most important principle is the same as the principle of adjusting server parameters: store the most information as much as possible in the fastest storage, and keep the information in the storage as much as possible.

You can change several hardware configurations to improve the server performance:

Install more memory on your computer. This allows you to set the server cache and buffer size values to a greater value, so that the data is stored in the memory for a longer period of time and less information is read from the disk.

Reconfigure the system. If you have enough memory to perform all swap operations in the memory file system, delete all disk swap devices. Otherwise, even if you have enough RAM for swap operations, some systems will still swap with the disk.

Increase the disk speed to improve the I/O wait time. In this case, tracing time is a representative factor that determines the performance. The speed of horizontal movement of the head is relatively slow. After the head is located, the speed of reading information blocks from the track is relatively fast. However, if you need to choose whether to add more memory or a faster disk, you 'd better choose to add more memory. The memory is always faster than the disk, and adding memory allows you to use a larger cache to reduce disk activity.

The advantage of parallel operations is obtained by dividing disk activities on physical devices. If you can divide read and write operations on multiple physical devices, the speed is faster than reading and writing from the same device. For example, if you store a database on one device and store logs on another device, therefore, writing information to two devices at the same time is faster than sharing the same device with databases and logs. Note that using different partitions on the same physical device is not a parallel operation. This is not advantageous because they still need to compete for the same physical resources (head ).

Before re-deploying data to another device, make sure that you know the load status of the system. If you are running some important services on a specific physical device, placing the database on the device may cause worse performance. For example, if you are processing a large number of Web services and moving the database to the device where the Web Server Documentation Directory is located, you may not feel any advantages.

Using RAID allows you to gain the advantage of parallel operations.

Use multi-processor hardware. For multi-threaded applications similar to MySQL servers, the multi-processor hardware can execute multiple threads at the same time.