Detailed explanation of cache optimization for MySQL optimization (1)

The most frequently asked question is about MySQL databasePerformance optimization, so I plan to write a MySQL databaseperformance optimization recently This series of articles hopes to be helpful to junior and intermediate MySQL DBAs and other friends who are interested in MySQL performance optimization.

I am happy that a blogger marked my article. After I know Mark, I rarely come back and continue to pay attention. But it shows from the side 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 useful to others, that is the best result. The purpose of my insistence on blogging is so that when I forget a knowledge point, I can find a reliable solution as quickly as possible. When you remember your summarized knowledge, you will forget it more slowly. After a long time, this part of the knowledge finally turns into your own words, then you are no longer afraid of forgetting. This blog will continue to talk about the content of MySQL. This article talks about caching optimization. The process of talking about it is also my learning process.

Let’s take a look at our mysql version first. The version installed on my mac is 5.7, and many contents have changed. What we are talking about here is mainly 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. During the interview, I It is said from the aspect of SQL statement optimization. This kind of optimization also has an effect, but it is optimized from the logical aspect. But when all the logical levels can no longer be optimized, all indexes have been added, and the table structure is reasonably designed, but when encountering high concurrency, why can't MySQL still handle it? Of course, the pressure on MySQL can be alleviated through other aspects, which we will not discuss here for now. For MySQL, we must try our best to squeeze the performance of the machine so that all computing resources can serve us without wasting them. 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 consumes a lot of memory. The MySQL memory of the online server consumes 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, etc. The handshake requires certain network resources and MySQL server memory resources.

I have to mention lock competition. For databases with relatively high concurrency performance requirements, if there is fierce lock competition, the performance of the database will be a big blow. Lock contention will significantly increase the overhead of thread context switching, which is independent of the expected demand.

2. show status and show variables

In the first few blogs of the MySQL series, you will often see these commands, so let’s take a look at these two commands separately. What information does this command display to the MySQL system administrator:

show status

When the MySQL service is running, the status of the MySQL service instance Information is dynamic. Use this command to display the session status variable information of the current MySQL server connection. By default variable names have the first letter capitalized.

show variables

show variables is used to display various system variables of the MySQL service instance (such as: global system variables, session system variables, staticvariables), 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 value of the session status is related to the current MySQL client. Valid, for example: Opened_tables, Opened_table_definitions status variables.

Caching mechanism

缓存之所以有效，主要是因为程序运行时对内存或者外存的访问呈现局部性特征，局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据（时间局部性）以及未来即将访问到的数据（空间局部性）保存到缓存中，甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

三、MySQL 超时

在使用MySQL的过程中，可能会出现各种超时（timeout）异常，典型的有连接超时、锁等待等。

查看超时时间的类型有哪些：

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、连接超时（connect_timeout）

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果，并且是多次握手的结果，每次握手，除了验证账户名和身份信息外，还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障，可以通过connect_timeout参数来设置，防止它们之间重复握手。

interactive_timeout指的是交互式的终端，在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端，比如PHP实例化的Mysql连接，一直占用着，超过了这个参数设置的值，就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集，MySQL客户机在该值设置的时间内不能接受完毕，则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据，在设置值内不能读取完毕，则会自动断开连接。

InnoDB锁等待超时

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

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值，当出现锁等待的时候，等待时长超过该值会导致锁等待的SQL回滚（不是整个事务回滚）。如果希望整个事务回滚，需要开启innodb_rollback_on_timeout参数。

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

innodb_rollback_on_timeout设置为true 后，遇到事务超时，会回滚整个事务的操作。

复制连接超时

当主从配置是，从服务器（slave）从主服务器（master）读取二进制日志失败后，从服务器会等待 slave_net_timeout 后，从新从master机拉去二进制日志。可以设置成10s.

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

这部分总结，应该是周日晚上就该整理好的，结果拖到了今天。后面的计划又要后延了，拖延症真严重。

The above is the detailed content of Detailed explanation of cache optimization for MySQL optimization (1). For more information, please follow other related articles on the PHP Chinese website!