mysql8 系统优化知识点总结

最新推荐文章于 2024-04-24 07:00:00 发布

xuejianxinokok

最新推荐文章于 2024-04-24 07:00:00 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/xuejianxinokok/article/details/117932962

版权

数据库专栏收录该内容

57 篇文章 3 订阅

订阅专栏

0. change buffer

Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果

https://blog.csdn.net/qq_36652619/article/details/89460786

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

当由于insert update delete等 dml语句导致修改数据时会引起二级索引变更，如果这些二级索引页面没在buffer pool中。写入会先写到 changebuffer 中。当后续的查询需要二级索引页面而导致二级索引页面从磁盘缓存到buffer pool 中的时候，会合并chagebuffer 中的内容。
后续会有单独线程从buffer pool 刷新到磁盘。

Change buffer merging may take several hours when there are many affected rows and numerous secondary indexes to update. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed, and even after a server shutdown and restart (see Section 15.21.2, “Forcing InnoDB Recovery” for more information).

为什么重要

表的索引存于该表的ibd文件中，数据也存于此文件。表数据更新的同时也会更新对应的表的索引数据，
所以：例如对表进行insert时，很可能会产生大量的物理读(物理读索引数据页)，insert一个表，对应的表上面的索引会变动，
如果索引不常使用，则不会再buffer pool中，会从磁盘读取到buffer poll 而产生物理读，
（物理读（Physical Reads）：从磁盘读取数据块到内存的操作叫物理读，当缓存不存在这些数据块的时候就会产生物理读，物理读过大表现为磁盘 I/O 较高）

所以将对索引的更新记录存入Change Buffer中，而不是直接调入索引页进行更新；选择时机进行merge insert buffer的操作，将insert buffer中的记录合并(merge)到真正的辅助索引中。

系统大部分空闲时或在慢速关闭期间运行的清除（purge）操作会定期将更新的索引页写入磁盘。与每个值立即写入磁盘相比，purge操作可以更有效地为一系列索引值写入磁盘块。

当有许多受影响的行和许多要更新的二级索引时，Change Buffer合并可能需要几个小时。在此期间，磁盘I / O会增加，这会导致磁盘绑定查询显着减慢。在提交事务之后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能继续发生

在内存中，Change Buffer占用Buffer Pool的一部分(25%)。
在磁盘上，Change Buffer是系统表空间的一部分，其中的索引会在关闭数据库服务器时更改。

When INSERT, UPDATE, and DELETE operations are performed on a table, the values of indexed columns (particularly the values of secondary keys) are often in an unsorted order, requiring substantial I/O to bring secondary indexes up to date.
The change buffer caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded into the buffer pool, and the updated page is later flushed to disk. The InnoDB main thread merges buffered changes when the server is nearly idle, and during a slow shutdown.

对表执行 INSERT，UPDATE和 DELETE操作时，索引列的值（尤其是secondary keys的值）通常按未排序顺序排列，需要大量I / O才能使二级索引更新。Change Buffer会缓存这个更新当相关页面(相关连的索引页)不在Buffer Pool中，从而磁盘上的相关页面（索引页）不会立即被读避免了昂贵的I / O操作。当页面加载到缓冲池中时，将合并缓冲的更改，稍后将更新的页面刷新到磁盘。该InnoDB主线程在服务器几乎空闲时以及在慢速关闭期间合并缓冲的更改。

Because it can result in fewer disk reads and writes, change buffering is most valuable for workloads that are I/O-bound; for example, applications with a high volume of DML operations such as bulk inserts benefit from change buffering.
However, the change buffer occupies a part of the buffer pool(Change Buffer占用Buffer Pool的一部分(25%)), reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working data set fits entirely within the buffer pool, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.

来了一个关于二级索引页面的DML操作，并且这个页面没有在Buffer Pool内，那么把这个操作存入Change Buffer，
那么下一次需要加载这个页面的时候，也就是这个页面有需求的时候，会将Change Buffer内的更改合并到Buffer Pool，
随后当服务器在空闲的时候，这个更改会刷到disk（磁盘）上，这个是按索引排序后合并，合并随机写为连续写。
所以一开始那张很难读的图的流程就清晰了：（黄色箭头这样的走势）

如何配置

默认为25% bufferpool

The innodb_change_buffer_max_size variable permits configuring the maximum size of the change buffer as a percentage of the total size of the buffer pool. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50.
Consider increasing innodb_change_buffer_max_size on a MySQL server with heavy insert, update, and delete activity, where change buffer merging does not keep pace with new change buffer entries, causing the change buffer to reach its maximum size limit.
Consider decreasing innodb_change_buffer_max_size on a MySQL server with static data used for reporting, or if the change buffer consumes too much of the memory space shared with the buffer pool, causing pages to age out of the buffer pool sooner than desired.
Test different settings with a representative workload to determine an optimal configuration. The innodb_change_buffer_max_size variable is dynamic, which permits modifying the setting without restarting the server.

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ((change_buffer_pages/total_pages)*100))
AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
| 25 | 8192 | 0.3052 |
+---------------------+-------------+-------------------------------+

1.数据缓存池
https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html

you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging.

You can configure InnoDB buffer pool size offline or while the server is running.

The innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

多个数据缓存

For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option, and you might also adjust the innodb_buffer_pool_size value.

为什么要配置多个数据缓存？减少缓存冲突

When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention.

Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function.
Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool.
Prior to MySQL 8.0, each buffer pool was protected by its own buffer pool mutex. In MySQL 8.0 and later, the buffer pool mutex was replaced by several list and hash protecting mutexes, to reduce contention.
To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set innodb_buffer_pool_size to a size of 1GB or more. The total size you specify is divided among all the buffer pools.

怎么设置多个缓存池，提高并发性能
For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.
For information about modifying InnoDB buffer pool size, see Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”.

When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M. For more information, see Configuring InnoDB Buffer Pool Chunk Size.

buffer pool size =innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. 的整数倍
比如设置
mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M=2G
而 innodb-buffer-pool-size =8G 是 2G 的整数倍所以是合法的

如果设置
mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16

16*128=2G ,9G 不是2G 的倍数所以 innodb-buffer-pool-size 会自动调整为10G
SELECT @@innodb_buffer_pool_size/1024/1024/1024;

Changing those variable settings requires restarting the server.

innodb_buffer_pool_chunk_size 不能动态设置(默认为128M)，
innodb_buffer_pool_size可以动态设置 SET GLOBAL innodb_buffer_pool_size=402653184; 监控动态设置过程 https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html

innodb-buffer-pool-instances 不可以动态设置默认值为8个

innodb_buffer_pool_chunk_size can be increased or decreased in 1MB (1048576 byte) units but can only be modified at startup, in a command line string or in a MySQL configuration file.
Command line:
shell> mysqld --innodb-buffer-pool-chunk-size=134217728
Configuration file:
[mysqld]
innodb_buffer_pool_chunk_size=134217728

innodb_buffer_pool_size defines size of the buffer pool, which is the memory area that holds cached data for InnoDB tables, indexes, and other auxiliary buffers. The size of the buffer pool is important for system performance, and it is typically recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory. The default buffer pool size is 128MB. For additional guidance, see Section 8.12.3.1, “How MySQL Uses Memory”. For information about how to configure InnoDB buffer pool size, see Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”. Buffer pool size can be configured at startup or dynamically.

On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.
The number of buffer pool instances is controlled by the by innodb_buffer_pool_instances option.
By default, InnoDB creates one buffer pool instance. The number of buffer pool instances can be configured at startup. For more information, see Section 15.8.3.2, “Configuring Multiple Buffer Pool Instances”.

在线设置缓存池大小
SET GLOBAL innodb_buffer_pool_size=402653184;
查询缓存池大小单位为G
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
缓存池个数
SELECT @@innodb_buffer_pool_instances;

重置缓存池大小的操作是在所有事物完成后进行的，一旦开始调整缓存池大小所有事物必须等待
Active transactions and operations performed through InnoDB APIs should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes

关键度量指标缓存命中率:>=90%

SELECT Variable_name, Variable_value
FROM sys.metrics
WHERE Variable_name IN
('Innodb_pages_read',
'Innodb_buffer_pool_read_requests')

为什么需要 oldSublist

When a query requests data, it is always read from the buffer pool. If the data is not
already in the buffer pool, it is fetched from the tablespace. InnoDB divides the buffer pool
into two parts: the old blocks sublist and the new blocks sublist. Data is always read into
the head (top) of the old blocks sublist in whole pages. If data from the same page is
required again, the data is moved to the new blocks sublist. Both sublists use the least
recently used (LRU) principle to determine which pages to expel when it is necessary to
create room for a new page. Pages are evicted from the buffer pool from the old blocks
sublist. Since new pages spend time in the old blocks sublist before being promoted to the
new blocks sublist, it means that if a page is used once, but then left unused, then it will
quickly be expelled from the buffer pool again. This prevents large rare scans such as
backups from polluting the buffer pool.

If you have a data set that is larger than the buffer pool, a potential problem is that a large
scan can pull in data that is just used for that scan and then not used again for a long time.
When that happens, you risk that more frequently used data is expelled from the buffer
pool and the queries needing that data will suffer until the scan has completed and the
balance has been restored. Logical backups such as those made by mysqlpump and
mysqldump are good examples of jobs that can trigger the issue. The backup process
needs to scan all data, but the data is not needed again until the time of the next backup.

To avoid this issue, the buffer pool is split into two sublists: the new and old blocks
sublists. When pages are read from the tablespaces, they are first “quarantined” in the old
blocks sublist, and only if the page has been in the buffer pool for
more than innodb_old_blocks_time milliseconds
and is used again
will it be moved to the new blocks sublist.
This helps make the buffer pool scan resistant as a single table scan will
only read rows from a page in rapid succession and then not use the page again. This leaves
InnoDB free to expel the page once the scan has completed.

SELECT
sum(pool_size)*16/1024/1024 as "BP size in GB",
sum(modified_database_pages) as "flush list size"
, sum(database_pages) as "LRU list size"
FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

2. 日志缓存 Log Buffer https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html

The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 16MB. The contents of the log buffer are periodically flushed to disk.
A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.
The innodb_flush_log_at_trx_commit variable controls how the contents of the log buffer are written and flushed to disk. The innodb_flush_log_at_timeout variable controls log flushing frequency.

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_timeout

For durability and consistency in a replication setup that uses InnoDB with transactions:
If binary logging is enabled, set sync_binlog=1.
Controls how often the MySQL server synchronizes the binary log to disk.
参考
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_sync_binlog

Always set innodb_flush_log_at_trx_commit=1.

For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:

sync_binlog=1.

innodb_flush_log_at_trx_commit=1.

磁盘结构
近距离观察2.5英寸机械硬盘内部运转_哔哩哔哩 (゜-゜)つロ干杯~-bilibili
机械硬盘是怎么工作的？3D演示动画 - 知乎 (zhihu.com)

src=http://5b0988e595225.cdn.sohucs.com/images/20180323/44f1f6bb7bb84448b41a35419b5d0533.gif&refer=http://5b0988e595225.cdn.sohucs.com&app=2002&size=f9999,10000&q=a80&n=0&g=0n&fmt=jpeg (367×206) (baidu.com)

https://www.gpbctv.com/rjjc/202104/156558.html 理解大量离散读为什么会慢
GitHub - gaopengcarl/innblock: 用于分析innodb块结构的小工具 innblock是编译好的 page结构查看工具
(36条消息) mysql page directory_InnoDB Page结构详解_weixin_39639965的博客-CSDN博客

InnoDB – Jeremy Cole (jcole.us) innodb 页表结构

3. redo 配置 https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html

show variables like 'innodb_log_%'

show variables like 'innodb_%'

InnoDB creates two 48MB redo log files in the data directory named ib_logfile0 and ib_logfile1
默认48M 对于写入频繁的系统有点小

innodb_log_group_home_dir 定义rodo 日志文件位置，如果没有定义则默认在 datadir 数据目录中生成

线上发布按需要把这个目录放到闪盘上

You might use this option to place InnoDB log files in a different physical storage location than InnoDB data files to avoid potential I/O resource conflicts. For example:

[mysqld]
innodb_log_group_home_dir = /sata/soft/mysql/data/log

innodb_log_files_in_group defines the number of log files in the log group. The default and recommended value is 2.

由于写入redo 是按文件循环写入，在线上写入频繁时需要增加每组文件个数
the redo log is physically represented on disk by two files named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion

innodb_log_file_size defines the size in bytes of each log file in the log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group)
cannot exceed a maximum value that is slightly less than 512GB.
A pair of 255 GB log files, for example, approaches the limit but does not exceed it.
The default log file size is 48MB.
Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity,
which often means that there is enough redo log space to handle more than an hour of write activity.
The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.

如何优化redo
Consider the following guidelines for optimizing redo logging: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html

Make your redo log files big, even as big as the buffer pool. When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small redo log files cause many unnecessary disk writes. Although historically big redo log files caused lengthy recovery times, recovery is now much faster and you can confidently use large redo log files.
The size and number of redo log files are configured using the innodb_log_file_size and innodb_log_files_in_group configuration options. For information about modifying an existing redo log file configuration, see Changing the Number or Size of Redo Log Files.

Consider increasing the size of the log buffer. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. Log buffer size is configured using the innodb_log_buffer_size configuration option, which can be configured dynamically in MySQL 8.0.

Configure the innodb_log_write_ahead_size configuration option to avoid “read-on-write”. This option defines the write-ahead block size for the redo log. Set innodb_log_write_ahead_size to match the operating system or file system cache block size. Read-on-write occurs when redo log blocks are not entirely cached to the operating system or file system due to a mismatch between write-ahead block size for the redo log and operating system or file system cache block size.
Valid values for innodb_log_write_ahead_size are multiples of the InnoDB log file block size (2n). The minimum value is the InnoDB log file block size (512). Write-ahead does not occur when the minimum value is specified. The maximum value is equal to the innodb_page_size value. If you specify a value for innodb_log_write_ahead_size that is larger than the innodb_page_size value, the innodb_log_write_ahead_size setting is truncated to the innodb_page_size value.
Setting the innodb_log_write_ahead_size value too low in relation to the operating system or file system cache block size results in read-on-write. Setting the value too high may have a slight impact on fsync performance for log file writes due to several blocks being written at once.

MySQL 8.0.11 introduced dedicated log writer threads for writing redo log records from the log buffer to the system buffers and flushing the system buffers to the redo log files. Previously, individual user threads were responsible those tasks. As of MySQL 8.0.22, you can enable or disable log writer threads using the innodb_log_writer_threads variable. Dedicated log writer threads can improve performance on high-concurrency systems, but for low-concurrency systems, disabling dedicated log writer threads provides better performance.

Optimize the use of spin delay by user threads waiting for flushed redo. Spin delay helps reduce latency. During periods of low concurrency, reducing latency may be less of a priority, and avoiding the use of spin delay during these periods may reduce energy consumption. During periods of high concurrency, you may want to avoid expending processing power on spin delay so that it can be used for other work. The following system variables permit setting high and low watermark values that define boundaries for the use of spin delay.

innodb_log_wait_for_flush_spin_hwm: Defines the maximum average log flush time beyond which user threads no longer spin while waiting for flushed redo. The default value is 400 microseconds.

innodb_log_spin_cpu_abs_lwm: Defines the minimum amount of CPU usage below which user threads no longer spin while waiting for flushed redo. The value is expressed as a sum of CPU core usage. For example, The default value of 80 is 80% of a single CPU core. On a system with a multi-core processor, a value of 150 represents 100% usage of one CPU core plus 50% usage of a second CPU core.

innodb_log_spin_cpu_pct_hwm: Defines the maximum amount of CPU usage above which user threads no longer spin while waiting for flushed redo. The value is expressed as a percentage of the combined total processing power of all CPU cores. The default value is 50%. For example, 100% usage of two CPU cores is 50% of the combined CPU processing power on a server with four CPU cores.
The innodb_log_spin_cpu_pct_hwm configuration option respects processor affinity. For example, if a server has 48 cores but the mysqld process is pinned to only four CPU cores, the other 44 CPU cores are ignored.

如果参数innodb_log_file_size设置太小，就会导致MySQL的日志文件（redo log）频繁切换，频繁的触发数据库的检查点（Checkpoint），导致刷新脏页（dirty page）到磁盘的次数增加。从而影响IO性能。另外，如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换（因为实例恢复还需要，不能被循环复写，好比Oracle中的redo log无法循环覆盖）这样MySQL就Hang住了。如果参数innodb_log_file_size设置太大的话，虽然大大提升了IO性能，但是当MySQL由于意外（断电，OOM-Kill等）宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响。所以必须权衡二者进行综合考虑。

如何计算redo 生成多少?
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
https://www.percona.com/blog/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb statusG select sleep(60); show engine innodb statusG
Log sequence number 84 3836410803
1 row in set (0.06 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 84 3838334638
1 row in set (0.05 sec)

Notice the log sequence number. That’s the total number of bytes written to the transaction log. So, now you can see how many MB have been written to the log in one minute. (The technique I showed here works on all versions of MySQL. In 5.0 and newer, you can just watch Innodb_os_log_written from SHOW GLOBAL STATUS, too.)

计算过程
mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

定时统计redo

USE mysqls;
#建表
CREATE TABLE IF NOT EXISTS innodb_log_size_his
(
log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志编号',
log_date DATETIME COMMENT '记录当前数据的时间',
log_size DOUBLE COMMENT 'redo log的大小,单位为mb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT 'redo日志大小信息表';

--MySQL 5.*版本用下面脚本
DELIMITER &&

DROP PROCEDURE IF EXISTS `Record_Innodb_Log_Size`&&

CREATE PROCEDURE Record_Innodb_Log_Size()
BEGIN
INSERT INTO mysql.`innodb_log_size_his`
(
log_date,
log_size
)
SELECT now() AS log_date,
ROUND(CAST(VARIABLE_VALUE AS DOUBLE )/1024/1024, 1) as log_size
FROM information_schema.global_status
WHERE VARIABLE_NAME = 'innodb_os_log_written';
END &&

DELIMITER ;

--MySQL 8.0以上版本使用下面脚本
#统计
DELIMITER &&

DROP PROCEDURE IF EXISTS `Record_Innodb_Log_Size`&&

CREATE PROCEDURE Record_Innodb_Log_Size()
BEGIN
INSERT INTO mysql.`innodb_log_size_his`
(
log_date,
log_size
)
SELECT now() AS log_date,
ROUND(CAST(VARIABLE_VALUE AS DOUBLE )/1024/1024, 1) as log_size
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'innodb_os_log_written';

END &&

DELIMITER ;

然后创建MySQL的计划任务/事件调度，这个可以根据需求弹性设定。

CREATE EVENT DPA_REDOLOG_SIZE
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-10-16 08:00:00'
ON COMPLETION PRESERVE
DO CALL mysql.Record_Innodb_Log_Size;

然后你就可以基于这个表做一些简单的分析和统计了，例如，统计10分钟内生成重做日志生成了多少。如下所示：

SELECT m.*
,@lag AS last_redo_size
,ROUND(m.log_size - @lag,2) AS gen_redo_size
,@lag:=log_size
FROM mysql.`innodb_log_size_his` m, (SELECT @lag :='') AS n
WHERE m.log_date >= date_add(now(), interval -1 day)
ORDER BY m.log_id;

4. undo 配置 https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

该表空间文件删除后重启启动会自动重建(undo001 undo002)

Two default undo tablespaces are created when the MySQL instance is initialized. Default undo tablespaces are created at initialization time to provide a location for rollback segments that must exist before SQL statements can be accepted. A minimum of two undo tablespaces is required to support automated truncation of undo tablespaces. See Truncating Undo Tablespaces.
Default undo tablespaces are created in the location defined by the innodb_undo_directory variable. If the innodb_undo_directory variable is undefined, default undo tablespaces are created in the data directory. Default undo tablespace data files are named undo_001 and undo_002. The corresponding undo tablespace names defined in the data dictionary are innodb_undo_001 and innodb_undo_002.
As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using SQL. See Adding Undo Tablespaces.

As of MySQL 8.0.23, the initial undo tablespace size is normally 16MiB.

By default, undo logs reside in two undo tablespaces that are created when the MySQL instance is initialized.
The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.
The innodb_undo_directory variable defines the path where InnoDB creates default undo tablespaces.
If that variable is undefined, default undo tablespaces are created in the data directory.
The innodb_undo_directory variable is not dynamic. Configuring it requires restarting the server.

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records.
Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments reside in undo tablespaces and in the global temporary tablespace.

undo log(撤销日志) 保存在 undo log segments（撤销日志段）中。
undo log segments（撤销日志段）保存在 rollback segments(回滚段)中
rollback segments(回滚段) 保存在撤销表空间和全局零时表空间

undo log segment 重做日志段
A collection of undo logs. Undo log segments exists within rollback segments. An undo log segment might contain undo logs from multiple transactions. An undo log segment can only be used by one transaction at a time but can be reused after it is released at transaction commit or rollback.

Each undo tablespace and the global temporary tablespace individually support a maximum of 128 rollback segments. The innodb_rollback_segments variable defines the number of rollback segments.
innodb_rollback_segments defines the number of rollback segments allocated to each undo tablespace and the global temporary tablespace for transactions that generate undo records. The number of transactions that each rollback segment supports depends on the InnoDB page size and the number of undo logs assigned to each transaction.

Each undo tablespace and the global temporary tablespace individually support a maximum of 128 rollback segments. The innodb_rollback_segments variable defines the number of rollback segments.
The number of transactions that a rollback segment supports depends on the number of undo slots in the rollback segment and the number of undo logs required by each transaction.
The number of undo slots in a rollback segment differs according to InnoDB page size.

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
-> WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+--------------------------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+--------------------------------+
| innodb_undo_001 | /sata/soft/mysql/undo/undo_001 |
| innodb_undo_002 | /sata/soft/mysql/undo/undo_002 |
+-----------------+--------------------------------+
2 rows in set (0.01 sec)

---------------------------
创建
CREATE UNDO TABLESPACE ts_undo_003 ADD DATAFILE 'undo_003.ibu';

The undo tablespace file name must have an .ibu extension. 必须以.ibu结尾

A MySQL instance supports up to 127 undo tablespaces including the two default undo tablespaces created when the MySQL instance is initialized.

删除之前先使undo 不激活
ALTER UNDO TABLESPACE ts_undo_003 SET INACTIVE;
然后删除
DROP UNDO TABLESPACE ts_undo_003;

mysql> CREATE UNDO TABLESPACE ts_undo_003 ADD DATAFILE 'undo_003.ibu';
Query OK, 0 rows affected (0.12 sec)

mysql> ALTER UNDO TABLESPACE ts_undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP UNDO TABLESPACE ts_undo_003;
Query OK, 0 rows affected (0.01 sec)

5. 配置io 进程数量 https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-multiple_io_threads.html

InnoDB uses background threads to service various types of I/O requests. You can configure the number of background threads that service read and write I/O on data pages using the innodb_read_io_threads and innodb_write_io_threads configuration parameters. These parameters signify the number of background threads used for read and write requests, respectively. They are effective on all supported platforms. You can set values for these parameters in the MySQL option file (my.cnf or my.ini); you cannot change values dynamically. The default value for these parameters is 4 and permissible values range from 1-64.

The purpose of these configuration options to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is read-ahead requests.
InnoDB tries to balance the load of incoming requests in such way that most background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread, to increase the chances of coalescing the requests. If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.
On Linux systems, InnoDB uses the asynchronous I/O subsystem by default to perform read-ahead and write requests for data file pages, which changes the way that InnoDB background threads service these types of I/O requests.For more information, see Section 15.8.6, “Using Asynchronous I/O on Linux”.

6. 自动配置模式 https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

dedicated(专用的)，在docker容器中推荐使用

7. InnoDB and Online DDL 在线运维和管理 https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include:

Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.

For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the LOCK clause. See The LOCK clause.

Less disk space usage and I/O overhead than the table-copy method.

Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible.

ALGORITHM=INSTANT support is available for ADD COLUMN and other operations in MySQL 8.0.12.

8 连接数量可以动态设置 max_connections https://dev.mysql.com/doc/refman/8.0/en/connection-interfaces.html

show variables like 'max_connections'

Default Value 151

The maximum permitted number of simultaneous client connections. For more information, see Section 5.1.12.1, “Connection Interfaces”.

需要配置
open-files-limit=5000 配置使用
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_open_files_limit

https://dev.mysql.com/doc/refman/8.0/en/connection-interfaces.html

The maximum number of client connections MySQL supports (that is, the maximum value to which max_connections can be set) depends on several factors:
The quality of the thread library on a given platform.
The amount of RAM available.
The amount of RAM is used for each connection.
The workload from each connection.
The desired response time.
The number of file descriptors available.

9. bin_log https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.htm

https://www.cnblogs.com/kerrycode/p/6610874.html

可以直接复制 index 和数据到别的目录

show variables like 'log_bin%'

系统变量binlog_cache_size 表示为每个客户端分配binlog_cache_size大小的缓存，默认值32768。二进制日志缓存使用的前提条件是服务器端使用了支持事务的引擎以及开启了bin log功能，它是MySQL用来提高binlog的效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多或多事务语句，写入量比较大，可适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。
可以适当的调整sync_binlog，在牺牲一定的一致性下，获取更高的并发和性能

10. 共享表空间和单独表空间配置

共享表空间减少打开文件数量，但是对于删除表或truncate表不能释放空间

单独表空间打开文件数量较多，能够快速释放空间和表空间导入和导出

11. MySQL 复制调优

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterbaseconfig.html

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions,
you should use innodb_flush_log_at_trx_commit=1
and sync_binlog=1 in the source's my.cnf file.

Ensure that the skip_networking system variable is not enabled on the source. If networking has been disabled, the replica cannot communicate with the source and replication fails.

binary log can be used for data backups and crash recovery

https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html

MySQL replication capabilities are implemented using three main threads, one on the source server and two on the replica:

Binary log dump thread. The source creates a thread to send the binary log contents to a replica when the replica connects. This thread can be identified in the output of SHOW PROCESSLIST on the source as the Binlog Dump thread.
The binary log dump thread acquires a lock on the source's binary log for reading each event that is to be sent to the replica. As soon as the event has been read, the lock is released, even before the event is sent to the replica.

Replication I/O thread. When a START REPLICA | SLAVE statement is issued on a replica server, the replica creates an I/O thread, which connects to the source and asks it to send the updates recorded in its binary logs.
The replication I/O thread reads the updates that the source's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the replica's relay log.
The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS.

Replication SQL thread. The replica creates an SQL thread to read the relay log that is written by the replication I/O thread and execute the transactions contained in it.

-- 复制服务器的事物应用线程 (slave_parallel_workers 默认为0)

A replica uses two threads to separate reading updates from the source and executing them into independent tasks. Thus, the task of reading transactions is not slowed down if the process of applying them is slow. For example, if the replica server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the source when the replica starts, even if the SQL thread lags far behind. If the replica stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the transactions is stored locally in the replica's relay logs, ready for execution the next time that the replica starts.

You can enable further parallelization for tasks on a replica by setting the slave_parallel_workers system variable to a value greater than 0 (the default).
When this system variable is set, the replica creates the specified number of worker threads to apply transactions, plus a coordinator thread to manage them.

If you are using multiple replication channels, each channel has this number of threads. A replica with slave_parallel_workers set to a value greater than 0 is called a multithreaded replica. With this setup, transactions that fail can be retried

slave_parallel_type 并行类型

For multithreaded replicas (replicas on which slave_parallel_workers is set to a value greater than 0),
slave_parallel_type specifies the policy used to decide which transactions are allowed to execute in parallel on the replica.

The variable has no effect on replicas for which multithreading is not enabled. The possible values are:

LOGICAL_CLOCK: Transactions that are part of the same binary log group commit （二进制日志组提交） on a source are applied in parallel on a replica. The dependencies between transactions are tracked based on their timestamps to provide additional parallelization where possible. When this value is set, the binlog_transaction_dependency_tracking system variable can be used on the source to specify that write sets are used for parallelization in place of timestamps, if a write set is available for the transaction and gives improved results compared to timestamps.

When slave_preserve_commit_order=1 is set, you can only use LOGICAL_CLOCK.

DATABASE: Transactions that update different databases are applied in parallel. This value is only appropriate if data is partitioned into multiple databases which are being updated independently and concurrently on the source. There must be no cross-database constraints, as such constraints may be violated on the replica.

When your replication topology uses multiple levels of replicas, LOGICAL_CLOCK may achieve less parallelization for each level the replica is away from the source.
You can reduce this effect by using binlog_transaction_dependency_tracking on the source to specify that write sets are used instead of timestamps for parallelization where possible.

When binary log transaction compression is enabled using the binlog_transaction_compression system variable, if slave_parallel_type is set to DATABASE, all the databases affected by the transaction are mapped before the transaction is scheduled.
The use of binary log transaction compression with the DATABASE policy can reduce parallelism compared to uncompressed transactions, which are mapped and scheduled for each event.

binlog_order_commits=ON 动态参数默认开启

When this variable is enabled on a replication source server (which is the default), transaction commit instructions issued to storage engines are serialized on a single thread, so that transactions are always committed in the same order as they are written to the binary log. Disabling this variable permits transaction commit instructions to be issued using multiple threads. Used in combination with binary log group commit, this prevents the commit rate of a single transaction being a bottleneck to throughput, and might therefore produce a performance improvement.
Transactions are written to the binary log at the point when all the storage engines involved have confirmed that the transaction is prepared to commit. The binary log group commit logic then commits a group of transactions after their binary log write has taken place. When binlog_order_commits is disabled, because multiple threads are used for this process, transactions in a commit group might be committed in a different order from their order in the binary log. (Transactions from a single client always commit in chronological order.) In many cases this does not matter, as operations carried out in separate transactions should produce consistent results, and if that is not the case, a single transaction ought to be used instead.
If you want to ensure that the transaction history on the source and on a multithreaded replica remains identical, set slave_preserve_commit_order=1 on the replica.

https://dev.mysql.com/doc/refman/8.0/en/sql-replication-statements.html 复制语句

二进制日志刷新方式

#controls synchronization of the binary log to disk,默认为1,最小为0 最大基本不限
#sync_binlog保证每次事物提交前会把二进制日志同步到磁盘，保证数据不丢失
sync_binlog=1

sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。
可以适当的调整sync_binlog，在牺牲一定的一致性下，获取更高的并发和性能

12 数据页大小

Page Size Configuration
The innodb_page_size option specifies the page size for all InnoDB tablespaces in a MySQL instance. This value is set when the instance is created and remains constant afterward. Valid values are 64KB, 32KB, 16KB (the default), 8KB, and 4KB. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096).
The default page size of 16KB is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when a single page contains many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

13. 插件

sql 重写 https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html
复制插件 https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html

14. 服务器端网络和打开文件调优

打开文件限制

vi /etc/security/limits.conf

* soft noproc 65535
* hard noproc 65535
* soft nofile 65535
* hard nofile 65535

vi /etc/profile
ulimit -n 65535

使生效

[root@centos7db2 bin]# source /etc/profile
[root@centos7db2 bin]# ulimit -n
65535

15 双写buffer https://dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-buffer.html

双写缓存
The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files.
If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
Although data is written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the doublewrite buffer in a large sequential chunk, with a single fsync() call to the operating system (except in the case that innodb_flush_method is set to O_DIRECT_NO_FSYNC).
Prior to MySQL 8.0.20, the doublewrite buffer storage area is located in the InnoDB system tablespace.
As of MySQL 8.0.20, the doublewrite buffer storage area is located in doublewrite files.

InnoDB uses the doublewrite buffer to ensure it is
possible to detect whether a write was successful or not in case of a crash. The doublewrite
buffer is necessary, because most file systems do not guarantee atomic writes as an InnoDB
page is larger than the file system block size. （因为数据的库页16k 一般大于操作系统的页4k,数据库完整写入一页16k，对于操作系统来说需要4页

16K可能只有一部分被写到磁盘上
）

the doublewrite directory should be placed on the fastest storage media available

efore writing pages to a data file, InnoDB first writes them to a storage area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has completed does InnoDB write the pages to their proper positions in the data file. If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write (causing a torn page condition), InnoDB can later find a good copy of the page from the doublewrite buffer during recovery

16 备份和恢复

https://blog.csdn.net/qq_40687433/article/details/107408719

压力测试

mysql自带就有一个叫mysqlslap的压力测试工具，还是模拟的不错的。下面举例说说。mysqlslap是从5.1.4版开始的一个MySQL官方提供的压力测试工具。通过模拟多个并发客户端访问MySQL来执行压力测试，同时详细的提供了“高负荷攻击MySQL”的数据性能报告。并且能很好的对比多个存储引擎在相同环境下的并发压力性能差别。 https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

------------------------------------------------------------------------------------
参考文章 https://opensource.actionsky.com/20200409-mysql/ 版本比较旧但是有参考意义

https://opensource.actionsky.com/20190517-mysql-ddl/ MySQL DDL为什么成本高？
https://opensource.actionsky.com/20200805-mysql/ 选择合适的表空间

一条 insert 语句在写入磁盘的过程中到底涉及了哪些文件？顺序又是如何的？下面我们用两张图和大家一起解析 insert 语句的磁盘写入之旅。
图 1：事务提交前的日志文件写入

旅途过程：
首先 insert 进入 server 层后，会进行一些必要的检查，检查的过程中并不会涉及到磁盘的写入。
检查没有问题之后，便进入引擎层开始正式的提交。我们知道 InnoDB 会将数据页缓存至内存中的 buffer pool，所以 insert 语句到了这里并不需要立刻将数据写入磁盘文件中，只需要修改 buffer pool 当中对应的数据页就可以了。
buffer pool 中的数据页刷盘并不需要在事务提交前完成，其中的交互过程我们会在下一张图中分解。
但仅仅写入内存的 buffer pool 并不能保证数据的持久化，如果 MySQL 宕机重启了，需要保证 insert 的数据不会丢失。redo log 因此而生，当 innodb_flush_log_at_trx_commit=1 时，每次事务提交都会触发一次 redo log 刷盘。（redo log 是顺序写入，相比直接修改数据文件，redo 的磁盘写入效率更加高效）
如果开启了 binlog 日志，我们还需将事务逻辑数据写入 binlog 文件，且为了保证复制安全，建议使用 sync_binlog=1 ，也就是每次事务提交时，都要将 binlog 日志的变更刷入磁盘。
综上（在 InnoDB buffer pool 足够大且上述的两个参数设置为双一时），insert 语句成功提交时，真正发生磁盘数据写入的，并不是 MySQL 的数据文件，而是 redo log 和 binlog 文件。然而，InnoDB buffer pool 不可能无限大，redo log 也需要定期轮换，很难容下所有的数据，下面我们就来看看 buffer pool 与磁盘数据文件的交互方式。
名词背景说明
double write 背景
InnoDB buffer pool 一页脏页大小为 16 KB，如果只写了前 4KB 时发生宕机，那这个脏页就发生了写失败，会造成数据丢失。为了避免这一问题，InnoDB 使用了 double write 机制（InnoDB 将 double write 的数据存于共享表空间中）。在写入数据文件之前，先将脏页写入 double write 中，当然这里的写入都是需要刷盘的。有人会问 redo log 不是也能恢复数据页吗？为什么还需要 double write？这是因为 redo log 中记录的是页的偏移量，比如在页偏移量为 800 的地方写入数据 xxx，而如果页本身已经发生损坏，应用 redo log 也无济于事。
insert buffer 背景
InnoDB 的数据是根据聚集索引排列的，通常业务在插入数据时是按照主键递增的，所以插入聚集索引一般是顺序磁盘写入。但是不可能每张表都只有聚集索引，当存在非聚集索引时，对于非聚集索引的变更就可能不是顺序的，会拖慢整体的插入性能。为了解决这一问题，InnoDB 使用了 insert buffer 机制，将对于非聚集索引的变更先放入 insert buffer ，尽量合并一些数据页后再写入实际的非聚集索引中去。

为什么唯一索引不可以使用chage buffer？
针对唯一索引，如果buffer pool不存在对应的数据页，还是需要先去磁盘加载数据页，才能判断记录是否重复，这一步避免不了。
而普通索引是非唯一的，插入的时候以相对随机的顺序发生，删除和更新也会影响索引树中不相邻的二级索引树，通过使用合并缓冲，避免了在磁盘产生大量的随机IO访问获取普通索引页。
问题
当有许多受影响的行和许多辅助索引要更新时，change buffer合并可能需要几个小时，在此期间，I/O会增加，可能会导致查询效率大大降低，即使在事务提交之后，或者服务器重启之后，change buffer合并操作也会继续发生。
图 2：事务提交后的数据文件写入

旅途过程：
当 buffer pool 中的数据页达到一定量的脏页或 InnoDB 的 IO 压力较小时，都会触发脏页的刷盘操作。
当开启 double write 时，InnoDB 刷脏页时首先会复制一份刷入 double write，在这个过程中，由于double write的页是连续的，对磁盘的写入也是顺序操作，性能消耗不大。
无论是否经过 double write，脏页最终还是需要刷入表空间的数据文件。刷入完成后才能释放 buffer pool 当中的空间。
insert buffer 也是 buffer pool 中的一部分，当 buffer pool 空间不足需要交换出部分脏页时，有可能将 insert buffer 的数据页换出，刷入共享表空间中的 insert buffer 数据文件中。
当 innodb_stats_persistent=ON 时，SQL 语句所涉及到的 InnoDB 统计信息也会被刷盘到 innodb_table_stats 和 innodb_index_stats 这两张系统表中，这样就不用每次再实时计算了。
有一些情况下可以不经过 double write 直接刷盘
关闭 double write
不需要 double write 保障，如 drop table 等操作
汇总两张图，一条 insert 语句的所有涉及到的数据在磁盘上会依次写入 redo log，binlog，(double write，insert buffer) 共享表空间，最后在自己的用户表空间落定为安。

14. 慢查询日志

#查看状态
Show variables like '%slow_query%';
Show variables like '%long_query_time%';
#设置输出为table
set global log_output='TABLE';
#设置超过 xx秒的查询为慢查询，设置完成后需要重新连接数据库
set global long_query_time=3;
#开启
set global slow_query_log='ON';

#模拟超时
select sleep(5)

#select * From mysql.slow_log ;
#查询sql
select convert(sql_text using utf8mb4) sql_text from mysql.slow_log

15. 备份恢复和压缩
mysqldump --no-data --routines --events test > dump-defs.sql

16. 表压缩性能

https://dev.mysql.com/doc/refman/8.0/en/innodb-page-compression.html 数据压缩

基本上减少了一半

数据量 4309435

是否做表压缩主要是依赖表中数据：
包含字符串、重复值、这样压缩效果才会有。因为InnoDB基于Page来压缩数据，如果没有重复值，或者binary 数据类型（数值型），压缩意义也就不大了。

xuejianxinokok

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql8 系统优化知识点总结

change bufferChange buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果https://blog.csdn.net/qq_36652619/article/details/89460786The change buffer is a special data structure that caches changes tosecondary indexpages when thos...
复制链接

扫一扫