MySQL Cache Optimization and mysql Cache Optimization

MySQL Cache Optimization and mysql Cache Optimization

I'm glad to have a blogger mark my article. After I know mark, I will seldom continue to pay attention to it. However, it is explained from the perspective that when a blogger opens a 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 to be useful to yourself. If it is useful to others, it is the best result. I insist on writing a blog to find the most reliable solution when I forget the knowledge point. When you remember your summarized knowledge, it will be a little slower. After a long time, this part of knowledge is finally blurted out by yourself, and you will never be afraid to forget it again. This blog will continue to talk about MySQL content, Cache Optimization, and the process I learned.

Let's take a look at our mysql version. The version on my mac is 5.7, and a lot of content has changed. Here we mainly talk about the version 5.6.

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

I. MySQL cache Classification

MySQL optimization refers to a very large system. During the interview, I used to explain the optimization of SQL statements. This optimization also plays a role, but it is optimized logically. However, when all the logic layers are not optimized, all the indexes have been added, and the table structure is also designed properly, but in the case of high concurrency, why can't MySQL continue. Of course, we can relieve the pressure on MySQL from other aspects. For MySQL, We have to squeeze the performance of the machine as much as possible, so that all computing resources are not wasted and can serve us. MySQL runs on a server, which is a Linux server. The server's hard disk, CPU, memory, and network all affect MySQL performance. MySQl is very memory-consuming. The MySQL memory on the online server is about 80%, and the memory is too small. Other optimization spaces are actually very small.

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 'handshaking 'involves authentication and permission verification. handshaking requires a certain amount of network resources and MySQL server memory resources.

I have to mention the lock competition. For databases with high concurrency performance requirements, if there is fierce lock competition, it will be a great blow to the database performance. The lock competition will obviously increase the thread context switching overhead, which is irrelevant to the expected demand.

Ii. show status and show variables

These commands are frequently seen in the previous blogs of the MySQL series. 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. You can use this command to display the session status variables of the current MySQL server connection. By default, the first letter of the variable name is capitalized.

Show variables

Show variables is used to display various system variables (such as global system variables, session system variables, and static variables) of the MySQL service instance. These variables include the default values of MySQL parameters during compilation, or my. the parameter value set in cnf. System variables or parameters are static. By default, the names of system variables are all lowercase letters.

Run the MySQL Command show status or show session status to view the session variable information of the current MySQL server connection. The session state variable value is valid for the current MySQL client, for example: opened_tables and Opened_table_definitions status variables.

Cache Mechanism

The reason why the cache is effective is mainly because the access to memory or external storage is characterized by locality when the program is running. The locality features are space locality and time locality. Temporal locality means that recently accessed data may be accessed again. spatial locality means that data in adjacent locations may be accessed after a location is accessed. The cache mechanism of MySQL is to save the recently accessed data (Time locality) and the data to be accessed in the future (space locality) to the cache, or even to the high-speed cache. This improves I/O efficiency.

Based on the different Cache read/write functions, MySQL divides the Cache into Buffer Cache and Cache.

Buffer cache. Hard Disk Writing speed is too slow or frequent I/O, which is a great waste of efficiency. After a certain amount of data is stored in the cache, the data can be written to the hard disk at one time. Buffer cache is mainly used to write data and improve I/O performance.

Cache. Cache generally refers to data that is frequently accessed but rarely changed. If the Cache is full, the LRU algorithm is enabled for data elimination. Remove the farthest unused data to open up a new storage space. However, for ultra-large websites, it is difficult to mitigate high-frequency read requests by relying on this policy. Generally, the frequently accessed data is statically converted and directly returned to users by nginx. The less interaction between programs and database I/O devices, the higher the efficiency.

Iii. MySQL timeout

When using MySQL, various timeout exceptions may occur, such as connection timeout and lock wait.

View 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)

Connect_timeout is 10 s by default. The result of a MySQL connection is the handshake between the client and the server, and the handshake is performed multiple times. In addition to verifying the account name and identity information, you also need to verify host and domain name resolution. If a network fault exists between the client and the server, you can use the connect_timeout parameter to prevent repeated handshakes between the client and the server.

Interactive_timeout refers to an interactive terminal, which is input in the command line. If the default value is exceeded, it is disconnected.

Wait_timeout is a non-interactive terminal. For example, the Mysql connection instantiated by PHP is always in use. If the value of this parameter is exceeded, it is automatically disconnected.

The net_write_timeout MySQL Server generates a large dataset. If the MySQL client fails to accept the dataset within the time set for this value, the connection is disconnected.

The net_read_timeout MySQL client reads a large amount of data. If the data cannot be read within the set value, the connection is 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 of InnoDB is 50 s, and the value of Row-Level Lock wait is set. When a lock wait occurs, if the wait duration exceeds this value, the SQL rollback of the lock wait will occur (not the whole transaction rollback ). To roll back the entire transaction, you must 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 is rolled back when the transaction times out.

Replication connection timeout

If the master/slave configuration fails to read the binary log from the master server (slave), the slave server will wait for slave_net_timeout and pull the binary log from the master server. It can be set to 10 s.

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

This part of summary should be completed on Sunday evening, and the results are dragged to today. The subsequent Plan will be postponed again, and the procrastination is really serious.