MySQL Optimization: Cache Optimization

I am happy that some bloggers marked my article. After I got to know Mark, I rarely follow him again. But it indirectly shows that when the blogger clicks on the blog, he feels that this blog is valuable and can make up for his lack of knowledge. The most important thing about a blog is that it is useful to yourself. If it is also useful to others, that is the best result. The reason I insist on writing a blog is to find a reliable solution as quickly as possible when I forget some knowledge points. When you remember the knowledge you have summarized, it will be forgotten more slowly. After a long time, this knowledge will finally become the words you can speak fluently, and you will no longer be afraid of forgetting it. This blog will continue to talk about MySQL. This one will talk about cache optimization, and the process of talking is also my learning process.

Let's first take a look at our MySQL version. The version installed on my Mac is 5.7, and many contents have changed. The main version discussed here is 5.6.

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

1. MySQL cache classification

MySQL optimization refers to a very large system. During the interview, I talked about SQL statement optimization. This kind of optimization is also effective, but it is optimizing from a logical perspective. But when all logical aspects have been optimized, all indexes have been added, and the table structure is designed reasonably, why can't MySQL withstand high concurrency? Of course, you can relieve the pressure on MySQL in other ways, but we won’t discuss that here. For MySQL, we need to squeeze the performance of the machine to the maximum extent possible so that all computing resources are not wasted and can serve us. MySQL runs on a server, specifically a Linux server. Then the server's hard disk, CPU, memory, and network all affect the performance of MySQL. MySQl is very memory-intensive. The MySQL memory of an online server is about 80%. If the memory is too small, there is actually very little room for other optimizations.

In addition, connection is also an important aspect that affects MySQL performance. The connection between the MySQL client and the MySQL server is the result of repeated handshakes between the MySQL client and the MySQL server. Each 'handshake' goes through identity verification, permission verification and other links. The handshake requires a certain amount of network resources and MySQL server memory resources.

One thing that must be mentioned is lock contention. For databases with high concurrent performance requirements, if there is fierce lock contention, it will have a huge impact on database performance. Lock contention can significantly increase thread context switching overhead, which has nothing to do with expected demand.

2. show status and show variables

In the previous blogs of the MySQL series, we often see these commands, so let's take a look at what information these two commands show to the MySQL system administrator:

show status

When the MySQL service is running, the status information of the MySQL service instance is dynamic. This command can be used to display the session status variable information of the current MySQL server connection. By default, variable names start with a capital letter.

show variables

show variables is used to display various system variables of the MySQL service instance (such as global system variables, session system variables, static variables). These variables contain the default values ​​of MySQL compile-time parameters or the parameter values ​​set in my.cnf. System variables or parameters are a static concept. By default, system variable names are all lowercase letters.

Use the MySQL command show status or show session status to view the session variable information of the current MySQL server connection. The variable values ​​of the session status are valid for the current MySQL client, for example: Opened_tables, Opened_table_definitions status variables.

Cache mechanism

The reason why cache is effective is mainly because the access to memory or external storage when the program is running is local, and the local characteristics are spatial locality and temporal locality. Temporal locality means that data that has just been accessed may be accessed again in the near future, and spatial locality means that after a certain location is accessed, the data at its adjacent locations is likely to be accessed. The MySQL cache mechanism is to save the data that has just been accessed (temporal locality) and the data that will be accessed in the future (spatial locality) into the cache, or even the high-speed cache. Thereby improving I/O efficiency.

According to the different cache read and write functions, MySQL divides the cache into Buffer cache and Cache cache.

Buffer cache. Since the hard disk write speed is too slow or the I/O is too frequent, it is a huge waste of efficiency for the hard disk. Then you can wait until a certain amount of data is stored in the cache and then write it to the hard disk at one time. Buffer cache is mainly used to write data and improve I/O performance.

Cache Cache. Cache generally contains data that is frequently accessed but rarely changed. If the cache is full, the LRU algorithm is enabled to eliminate data. Eliminate the least used data to create new storage space. However, for very large websites, it is difficult to alleviate high-frequency read requests by relying on this strategy. Generally, the data that is accessed very frequently will be staticized and returned directly to the user by nginx. The less your program interacts with the database I/O devices, the higher the efficiency.

MySQL timeout

When using MySQL, various timeout exceptions may occur, typically connection timeout, lock wait, etc.

Check the timeout types:

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+

1. Connection timeout (connect_timeout)

The connect_timeout defaults to 10s. Obtaining a MySQL connection is the result of a handshake between the client and the server, and is the result of multiple handshakes. In each handshake, in addition to verifying the account name and identity information, it is also necessary to verify the host and domain name resolution. If there is a network failure between the client and the server, the connect_timeout parameter can be set to prevent repeated handshakes between them.

interactive_timeout refers to the interactive terminal, which is entered in the command line. If it exceeds the default value, it will be disconnected.

wait_timeout refers to a non-interactive terminal, such as a Mysql connection instantiated by PHP, which is always occupied and will be automatically disconnected if the value set by this parameter is exceeded.

net_write_timeout The MySQL server generates a large data set. If the MySQL client cannot receive it within the time set by this value, the connection will be disconnected.

net_read_timeout If the MySQL client reads a large amount of data and cannot finish reading it within the set value, the connection will be automatically disconnected.

InnoDB lock wait timeout

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+

The default lock wait time for InnoDB is 50s. Set the row-level lock wait value. When a lock wait occurs, if the wait time exceeds this value, the SQL for the lock wait will be rolled back (not the entire transaction). If you want the entire transaction to roll back, you need to enable the innodb_rollback_on_timeout parameter.

mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
+----------------------------+-------+

When innodb_rollback_on_timeout is set to true, the entire transaction will be rolled back if a transaction times out.

Replication connection timeout

When the master-slave configuration is, after the slave server (slave) fails to read the binary log from the master server (master), the slave server will wait for slave_net_timeout and then pull the binary log from the master again. Can be set to 10s.

mysql> show variables like 'slave_net_timeout';
+-------------------+------+
| Variable_name | Value |
+-------------------+------+
| slave_net_timeout | 3600 |
+-------------------+------+

This part of the summary should have been compiled on Sunday night, but it was delayed until today. My future plans will have to be postponed again. My procrastination problem is really serious.