About MySQL database optimization _ MySQL

About MySQL database optimization we have briefly introduced some basic operations on MYSQL databases. This chapter describes the MYSQL database optimization problems for MYSQL database administrators.

1. optimize the MySQL server

1.1 MYSQL server system variables

We have talked about some basic management of the MYSQL server in the previous chapter. here we will give a brief introduction to the server variables and status variables of the MYSQL server.

Query MYSQL server system variables:
C:/Program Files/MySQL Server 5.0/bin> mysqld -- verbose-help
Use the mysqladmin command to query the MYSQL server system variables:
C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p variables> d:/init.txt
Enter password :******

Part of Init.txt:
+ --------------------------------- + ---------------------------------------------------------------- +
| Variable_name | Value |
+ --------------------------------- + ---------------------------------------------------------------- +
| Auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
| Automatic_sp_privileges | ON |
| Back_log | 50

............
| Version_compile_machine | ia32 |
| Version_compile_ OS | Win32 |
| Wait_timeout | 28800 |
+ --------------------------------- + ---------------------------------------------------------------- +

Obtain the actual server system variables used by MYSQL:
Mysql> show variables;
Use the like parameter to display specific server system variables:
Mysql> show variables like 'init _ connect % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Init_connect |
+ --------------- + ------- +
1 row in set (0.00 sec)

Adjust the system variables of the MYSQL server

The mysqld server maintains two variables. Global variables affect global operations on the server. Session variables affect operations related to client connection. When the server is started, all global variables are initialized to the default value. You can change these default values in the options file or the options specified in the command line. After the server is started, you can change the dynamic GLOBAL variables by connecting to the server and executing the set global var_name statement. To change global variables, you must have the SUPER permission.

Method 1:
Mysql> show variables like 'query _ cache_size ';
+ ------------------ + ---------- +
| Variable_name | Value |
+ ------------------ + ---------- +
| Query_cache_size | 23068672 |
+ ------------------ + ---------- +
1 row in set (0.01 sec)

Mysql> set global query_cache_size = 31457280;
Query OK, 0 rows affected (0.00 sec)

Mysql> show variables like 'query _ cache_size ';
+ ------------------ + ---------- +
| Variable_name | Value |
+ ------------------ + ---------- +
| Query_cache_size | 31457280 |
+ ------------------ + ---------- +
1 row in set (0.00 sec)

Method 2:

Mysql> show variables like 'query _ cache_size % ';
+ ------------------ + ---------- +
| Variable_name | Value |
+ ------------------ + ---------- +
| Query_cache_size | 31457280 |
+ ------------------ + ---------- +
1 row in set (0.00 sec)

Mysql> SET @ global. query_cache_size = 20971520;
Query OK, 0 rows affected (0.09 sec)

Mysql> show variables like 'query _ cache_size % ';
+ ------------------ + ---------- +
| Variable_name | Value |
+ ------------------ + ---------- +
| Query_cache_size | 20971520 |
+ ------------------ + ---------- +
1 row in set (0.00 sec)

Mysql> select @ query_cache_size;
+ -------------------- +
| @ Query_cache_size |
+ -------------------- +
| 1, 20971520 |
+ -------------------- +
1 row in set (0.06 sec)

The mysqld server also maintains session variables for each client connection. The client session variable is initialized using the current value of the corresponding global variable during connection. You can use the SET [SESSION] var_name statement to change the dynamic SESSION variable. You do not need special permissions to set session variables, but you can only change your session variables without changing the session variables of other customers.

Mysql> SET sort_buffer_size = 10*1024*1024;
Query OK, 0 rows affected (0.08 sec)

Mysql> show variables like 'sort _ buffer % ';
+ ------------------ + ---------- +
| Variable_name | Value |
+ ------------------ + ---------- +
| Sorting _ buffer_size | 10485760 |
+ ------------------ + ---------- +
1 row in set (0.00 sec)

Note: When you use the start option to set variables, you can use the suffix K, M, or G to indicate kilobytes, megabytes, or gigabytes. For example, the following command sets the buffer size of the key value to 16 megabytes when starting the server:
C:/ProgramFiles/MySQL Server 5.0/bin> mysqld -- key_buffer_size = 16 M
The suffix is case-sensitive. 16 m and 16 m are the same.

When running, use the SET statement to SET system variables. In this case, you cannot use a suffix, but you can use the following expressions for the value:
Mysql> SET sort_buffer_size = 10*1024*1024;

1.2 MYSQL server status variables

Mysqladmin views server status variables (dynamic changes ):

C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p extended-status

Enter password :******
+ ----------------------------------- + ---------- +
| Variable_name | Value |
+ ----------------------------------- + ---------- +
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2664 |
| Bytes_sent | 96723 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |

This command is equivalent to the following command:
// Obtain MYSQL server statistics and status indicators
Mysql> show status;
+ ----------------------------------- + ---------- +
| Variable_name | Value |
+ ----------------------------------- + ---------- +
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 765 |
| Bytes_sent | 80349 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |

// Refresh the MYSQL server status variable
Mysql> show status like 'bytes _ sent % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Bytes_sent | 53052 |
+ --------------- + ------- +
1 row in set (0.00 sec)

Mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

Mysql> show status like 'bytes _ sent % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Bytes_sent | 11 |
+ --------------- + ------- +
1 row in set (0.00 sec)

1.3 MYSQL server key parameter optimization

Key_buffer_size

This parameter is very important to the MyISAM table. If our system only uses the MyISAM table, you can set it to 30-40% of the operating system's physical memory. Depending on the index size, data volume, and load, the MyISAM table uses the operating system cache to cache data. Therefore, you need to leave some memory for them. in many cases, the data is much larger than the index. However, you need to always check whether all key_buffer is used. If the *. MYI file only has 1 GB, and the key_buffer is set to 4 GB, this is a waste. If no MyISAM table exists, the 16-32 MB key_buffer_size is retained for the temporary table index. that is, do not disable this parameter (set to 0 ).

Below are several important MYSQL server parameters. here we will focus on the parameters related to the InooDB engine.

Innodb_buffer_pool_size

This parameter is very important to the Innodb table. Compared with MyISAM tables, Innodb is more sensitive to Buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system, therefore, if you only need Innodb and the amount of data in the system is very large, you can set it to up to 70-80% of the available memory.

In some conclusions, we should not set the memory to be too high on Linux x86, and the total memory usage below should not exceed 2 GB. let's remind you.

Innodb_buffer_pool_size + key_buffer_size +
Max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ Max_connections * 2 MB <2G

Innodb_additional_mem_pool_size

InnoDB is used to store the size of data dictionary information and memory pools of other internal data structures. The ideal value is 2 M. If there are more tables, you need to reassign them here. If InnoDB uses up all the memory in this pool, it allocates memory from the operating system and writes error messages to the MySQL error log. Set it in numbers in my. ini.

Innodb_log_file_size

It is important to write data at a high load, especially for large datasets. The larger the value, the higher the performance, but note that the recovery time may increase. We often set it to 64-512 MB, which varies with the size of the server file system.

Innodb_log_buffer_size

By default, the server performance is acceptable when the write load is moderate and the transaction is short. If there is a peak update operation or a large load, you should consider increasing the value. If its value is set too high, memory may be wasted-it will refresh once every second, so you do not need to set the memory space more than 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.

Innodb_flush_log_at_trx_commit

Is it because Innodb is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that each update transaction submitted (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure-some transactions will be lost when the MySQL server crashes. If set to 2, only the transaction that is refreshed to the operating system cache will be lost.

Innodb_log_files_in_group

The number of log files in the log group. InnoDB writes files in a circular mode (circular fashion. Value 3 is recommended. Set it in numbers in my. ini.

Innodb_lock_wait_timeout

Before rolling back, InnoDB transactions will wait for the timeout time (in seconds ). InnoDB automatically checks its own transaction deadlocks when the table is locked and the transaction is rolled back. If you use the lock tables command or use another transaction-safe table processor (transaction safe table handlers than InnoDB) in the same transaction, a deadlock that InnoDB cannot notice may occur. In this case, timeout will be used to solve this problem. The default value of this parameter is 50 seconds. Set it in numbers in my. ini.

Table_cache

Opening a table may have a high overhead. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

Tmp_table_size

If the temporary table in the memory exceeds this value, MySQL automatically converts it to the MyISAM table on the hard disk, so when we talked about key_buffer_size in the front, we once said that even if there is no MyISAM table in our system, we should keep the value of key_buffer_size as 16-32 M. If the system has a lot of group by queries and a lot of memory, you can increase the value of tmp_table_size.

Thread_cache

The overhead of thread creation and destruction may be large, because the connection/disconnection of each thread is required. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of Threads_Created is large, I will increase the value. It does not need to create a new thread in common operations.

Query_cache

This is useful if your application has a large number of reads without application-level caching. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.

Max_connections

Number of concurrent client connections allowed. This parameter is determined based on the number of connections of the system.

Sort_buffer

As you can see, the global table volumes are different based on hardware configurations and different storage engines, but session variables are usually set based on different loads. If you only have some simple queries, you do not need to increase the sort_buffer_size value, even though you have 64 GB memory. Poor performance may be reduced. I usually set session variables after analyzing the system load.

Innodb_file_io_threads

File I/O threads in InnoDB. It is usually set to 4, but a larger value can be set in Windows to increase disk I/O. Set it in numbers in my. ini.

Innodb_fast_shutdown

If this parameter is set to 0, InnoDB performs a full cleaning and a insert buffer merge before it is disabled. These operations take several minutes, and in extreme cases it takes several hours. If you set this parameter to 1, InnoDB skips these operations when it is disabled. The default value is 1. If you set this value to 2 (this value is not available in Netware), InnoDB will refresh its log and shut down it cold, as if MySQL crashed. Committed transactions will not be lost, but a crash recovery will be performed at the next startup.

Innodb_max_dirty_pages_pct

This is an integer ranging from 0 to 100. The default value is 90. The main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty pages (pages not written) cannot exceed this value. If you have the SUPER permission, you can change the percentage as follows when the server is running:
Set global innodb_max_dirty_pages_pct = value;

Innodb_thread_concurrency

InnoDB tries to keep the number of operating system threads in InnoDB less than or equal to the limit given by this parameter. If there is a performance problem and the show innodb status shows many threads waiting for the signal, you can make the thread "thrashing" and set this parameter to be smaller or larger. If your computer has multiple processors and disks, you can try to make better use of your computer resources with a larger value. A recommended value is the sum of the number of processors and disks on the system. The value is 500 or later than 500. concurrent checks are prohibited. The default value is 20. if the value is greater than or equal to 20, concurrent checks are disabled.

THREAD_STAC

The stack size of each thread. Many of the limitations detected by the crash-me test depend on this value. The default value is large enough for normal operations.

2. a function debugging tool

If our problem is only related to a specific MySQL expression or function, we can use the BENCHMARK () function of the mysql client program to perform a scheduled test. Its syntax is:
BENCHMARK (loop_count, expression)

Mysql> select benchmark (+ 1 );
+ ------------------------ +
| BENCHMARK (+ 1) |
+ ------------------------ +
| 0 |
+ ------------------------ +
1 row in set (0.13 sec)

Mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

Mysql> select benchmark (+ 1 );
+ ------------------------ +
| BENCHMARK (+ 1) |
+ ------------------------ +
| 0 |
+ ------------------------ +
1 row in set (0.03 sec)