Cache Optimization for MySQL Optimization

I am glad that there are bloggers mark who wrote my article. After I know mark, I rarely pay attention to it again. But from the side, it shows that while bloggers click to open blogs, 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 useful to others, it is the best result. I insist that the purpose of writing a blog is to be able to find reliable solutions as quickly as possible when I forget knowledge points. When the induction of their own knowledge, remember again will be forgotten slow 1 point, such as a long time, this part of knowledge finally turned into their own blurted out words, that is no longer afraid of forgetting. This blog will continue to talk about the content of MySQL, this one is about cache optimization, and the process is also the process I learned.

Let's take a look at our version of mysql first. My version of mac is 5.7, and many contents have changed. This is mainly about version 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

The optimization of MySQL refers to a large system. I said it from the statement optimization of sql before the interview. This optimization also plays a role, but it is optimized from the logical aspect. However, when all logical levels have not been optimized, all indexes have been added, and the table structure is designed reasonably, why can't MySQL carry it when encountering high concurrency? Of course, there are other ways to relieve the pressure of MySQL, which we will not talk about for the time being. For MySQL, we should try our best to squeeze the performance of the machine, so that all computing resources are not wasted and can serve us. MySQL runs on a server, specifically the Linux server. Then the hard disk of the server, CPU, memory and network all affect the performance of MySQL. MySQl consumes a lot of memory. The memory of MySQL of online server needs to eat about 80%, which is too small, and other optimization space is actually very small.

In addition, connection (connection) is also an important aspect that affects the performance of MySQL. The connection between the MySQL client and the MySQL server is the result of repeated handshakes between the MySQL client and the MySQL server. Every handshake goes through authentication, authority verification and other links. Handshake needs to occupy 1 fixed network resources and MySQL server memory resources.

It has to be mentioned that lock competition. For databases with high concurrent performance requirements, if there is fierce lock competition, it will be a great blow to the performance of the database. Lock contention will significantly increase the overhead of thread context switching, which is independent of the expected requirements.

2. show status and show variables

These commands are frequently seen in the first few blogs in the MySQL series, so let's take a look at what these two commands show the MySQL system administrator:

show status

When the MySQL service is running, the state information of the MySQL service instance is dynamic. Use this command to display session state variable information for the current MySQL server connection. By default, the initials of variable names are capitalized.

show variables

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

Using the MySQL command show status or show session status, you can view the session variable information of the current MySQL server connection. The variable value of the session state is valid for the current MySQL client, such as Opened_tables, Opened_table_definitions state variables.

Caching mechanism

The main reason why cache is effective is that the access to memory or external memory when programs are running is localized, which is characterized by spatial locality and temporal locality. Temporal locality means that the data that has just been accessed may be accessed again in the near future, while spatial locality means that after a certain location is accessed, the data of its adjacent location is likely to be accessed. The caching mechanism of MySQL is to save the data just accessed (temporal locality) and the data to be accessed in the future (spatial locality) into the cache, even in the cache. Thereby improving the efficiency of I/O.

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

Buffer cache. Due to the slow writing speed of the hard disk, or frequent I/O, it is a great waste of efficiency for the hard disk. Then you can wait until 1 amount of data is stored in the cache and then write it to the hard disk once. Buffer cache is mainly used to write data and improve I/O performance.

Cache cache. The Cache cache is usually a data that is frequently accessed but less changed. If the Cache cache is full, the LRU algorithm is enabled to eliminate the data. Eliminate the farthest unused data, thus opening up 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 visited very frequently will be static and returned to users directly by nginx. The less the program interacts with the database I/O device, the more efficient it is.

3. MySQL timeout

In the process of using MySQL, there may be various time-out (timeout) exceptions, such as connection timeout, lock wait and so on.

See the types of timeouts:

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)

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

interactive_timeout refers to an interactive terminal, which is typed on the command line. If the default value is exceeded, it will be disconnected.

wait_timeout refers to non-interactive terminals, such as Mysql connection instantiated by PHP, which is directly occupied by 1. If it exceeds the value set by this parameter, it will be automatically disconnected.

The net_write_timeout MySQL server generates a large dataset, and the MySQL client disconnects if it does not accept it within the time set by this value.

The net_read_timeout MySQL client has read a large piece of data and will automatically disconnect if it cannot finish reading within the set value.

InnoDB lock wait timeout

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

The lock wait time of InnoDB defaults to 50s, setting the value of lock wait for row-level locks, beyond which SQL rollback of lock wait (not whole transaction rollback) will result when lock wait occurs. If you want the entire transaction to be rolled back, you need to turn on 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 transaction timeout is encountered and the operation of the entire transaction is rolled back.

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 new slave master machine. It 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 sorted out on Sunday night, and the result has been put off until today. Later plans have to be delayed again. Procrastination is really serious.