series
MySQL MySQL series 4
MySQL > select * from MySQL; select * from MySQL;
MySQL > select an index for MySQL > select an index for MySQL
Did you know that strings can be indexed in this way? MySQL series 7
In the project, errors, anomalies and long execution time of MySQL are all put into the nail group, which is conducive to timely processing at ordinary times. Today we are going to talk about slow queries that cannot be repeated.
Why does “slow” SQL occur that cannot be repeated
Redo log; binlog; MySQL series 2: Redo log; At this point, you know that when a transaction commits, instead of directly modifying the database data, you first update memory and log the related operations in the redo log.
After all, data is flushed from memory to disk, also known as dirty pages.
What are dirty and clean pages
Most sources talk about dirty pages, but what exactly is a dirty page? A dirty page is a memory page whose data is inconsistent with disk data.
When a memory page is written to disk, the memory and disk data pages are consistent, and the memory page is called clean.
When does a dirty page become a clean one
The first kind of
Innodb’s redo log is full, so write POS has caught up with the check point, and all updates are stopped.
The redo log cannot continue writing until the check point is advanced and the dirty pages are flushed to disk.
Innodb_log_file_size: innodb_log_file_size: innodb_log_file_size: Innodb_log_file_size: Innodb_log_file_size
The second,
When you run out of memory, you update a statement to the redo log. If you run out of memory, you cannot apply for new memory and some data pages need to be flushed. You need to flush the dirty pages to disk.
Have you ever wondered if you could flush out the memory page and read the redo log from disk when requested?
The next time a clean page is requested from disk, you also need to determine if there are any changes to the page in the redo log, and if so, you need to apply the redo log to the page. The dirty page was always flushed, but now there is a redo log application. Instead of flushing memory directly, flush memory directly when it is full.
In addition, redo logs are written in a circular manner. To apply redo logs, redo logs must always exist and cannot be deleted. It violates the system design.
The third kind of
MySQL brushes dirty pages during system off-peak periods
A fourth
When the MySQL server is properly shut down, dirty pages in the memory are flushed to the disk. After the MySQL server is restarted, it can directly read data from the disk, which speeds up the startup.
conclusion
You should know by now that inexplicably slow SQL is caused by flush. How do these four conditions affect MySQL?
The impact of four types of Flush on performance
The third and fourth conditions that do not cause MySQL to slow down are when the system is idle and when the database is about to shut down.
Redo log is full, need to flush dirty pages
This scenario was outlined in the second installment of this article. Once the redo log was full the system was no longer updated, and all updates were stopped until the Check Point was promoted.
extension
The innodb_log_file_size parameter is provided in MySQL to optimize the redo log.
Innodb_log_file_size also has some calculation rules, which are described below.
If innodb_log_file_size is too small, the redo log file frequently switches, triggering database check points. This increases the number of records updated to data files, affecting I/O performance.
Similarly, if there is a large transaction and all redo logs are full, but not yet complete, the log cannot be switched, causing MySQL to block.
Innodb_log_file_size is too large, which greatly improves I/O performance. However, in the event of MySQL restart or downtime, the recovery time is prolonged due to the excessively large redo log file. And that recovery time is often out of control.
How to set innodb_log_file_size properly?
Use a script to execute the script periodically, record the sequenumber of the corresponding time, and take the average value to minimize the calculated error. Sequenumber is a value that starts at 1 for each binlog generated and increases, incrementing sequenumber by 1 for each additional transaction.
The system is out of memory. Dirty pages need to be brushed
Innodb manages memory by buffer pool. Memory pages can be found in three states above: unused, clean and dirty.
For a long-running library, there are very few unused pages, and when memory runs low, only the oldest unused data pages are weeded out of memory.
If eliminated is a clean page, it is directly released to use, but if it is a dirty page must first brush the dirty page disk, into a clean page for reuse.
If the data to be queried is not in the memory, data needs to be read from the disk. If too much data is read, multiple dirty pages need to be eliminated, resulting in a long query time.
The redo log is full and all updates are not performed by the system. This is unacceptable for most services.
To prevent this, you need to control the frequency of dirty pages.
How to set the speed of dirty pages
The speed at which dirty pages are flushed to disk depends on the SYSTEM I/O capacity. In MySQL, innodb_io_capacity controls the speed at which dirty pages are flushed.
When check points are flushed from the buffer, the number of dirty pages flushed per second equals the value of innodb_io_capacity.
This value can be set to the IOPS of the disk, which can be tested using the FIO tool.
The speed of cleaning dirty pages depends on the percentage of dirty pages and the redo log write speed.
The innodb_max_dirty_pages_pct parameter is the upper limit of the dirty page ratio, which defaults to 90% in MySQL8.0 and 75% in MySQL5.6.
In general, set innodb_io_capacity to the maximum number of dirty pages and the log number during redo log writing minus the checkpoint value.
The formula for calculating the proportion of dirty pages is Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
Copy the codeIn this SQL statement you can see the global_status table used in performance_schema. Before running this command, run use performance_schema.
When your MySQL write speed is slow, TPS is low, IO pressure is not high
Consider whether innodb_io_capacity is set properly.
The default value is 200 on a 2G-core server, and 2000 on a corporate server, depending on the server configuration.
Interesting metrics
Innodb_flush_neighbors defaults to 0 in MySQL8.0.
When a query needs to flush a dirty page during execution, if the data page next to it happens to be a dirty page, the data page will be flushed, and the associated logic will persist. Makes SQL queries slower.
Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.