内存

查看mysql当前进程内存使用率

htop -p `ps aux|grep mysqld | grep -v "grep" | awk '{print $2}'`

什么是innodb buffer pool呢？

InnoDB Buffer Pool是MySQL中InnoDB存储引擎用于缓存表数据和索引数据的内存区域。它对于提高数据库性能、减少磁盘I/O操作至关重要。以下是对InnoDB Buffer Pool的详细解析：

一、Buffer Pool的基本作用

• 缓存数据页：Buffer Pool缓存着表和索引的数据页，包括行数据和索引数据，以及一些系统数据。这些数据页在内存中，使得查询可以直接在内存中进行，提高了访问速度。
• 减少磁盘I/O：由于数据页在内存中，查询操作不需要每次都从磁盘中读取数据，从而显著减少了磁盘I/O操作的次数，提高了数据库的整体性能。

二、Buffer Pool的结构与管理

• 页框架：Buffer Pool被划分为一系列固定大小的页框架（默认为16KB），每个页框架用来存储一个数据页。
• 缓存页：实际缓存在Buffer Pool中的数据页，包括从磁盘读取的数据以及被频繁访问或修改的数据。
• 脏页：在内存中已被修改但尚未写回磁盘的数据页。这些页会被周期性地刷新到磁盘以保证数据的持久性。
• 链表管理：
  • LRU链表：用于管理数据页的访问顺序，将最近使用的页放在链表前面，而长时间没有被使用的页则放在链表末尾。这有助于淘汰长时间没有访问的数据页，腾出空间给新的数据页缓存。
  • Free链表：用于管理空闲的页框架。当某页框架中的页被淘汰或某页框架被分配但尚未加载数据页时，这些空闲的页框架会被添加到Free链表中，以供后续的新数据页加载使用。
  • Flush链表：用于管理需要刷新回磁盘的脏页。这些脏页需要被定期地刷新回磁盘，以确保数据的持久性。

三、Buffer Pool的配置与优化

• 大小配置：Buffer Pool的大小通过innodb_buffer_pool_size参数来配置，它直接影响数据库的性能。通常推荐设置为系统内存的50%~70%，但也需要根据具体的数据库工作负载和系统资源情况进行调整。
• 数据块与实例：从MySQL 5.7.5版本开始，Buffer Pool可以被分割成多个实例（通过innodb_buffer_pool_instances参数配置），以提高并发性能。每个实例都独立管理自己的Free链表、LRU链表和Flush链表。
• 调整与监控：通过性能分析工具和监控工具，可以了解数据库系统的实际工作情况，并根据监控信息调整Buffer Pool的大小和配置，以优化数据库性能。

四、Buffer Pool的高级特性

• 预读机制：InnoDB会根据查询模式预测未来可能需要的数据页，并提前将这些页加载到Buffer Pool中，以提高查询性能。但过度的预读可能会导致缓存污染，降低缓存命中率。
• Change Buffer：对于非唯一索引的插入、更新和删除操作，InnoDB不会立即将更改写回磁盘，而是先写入Change Buffer。这样可以减少磁盘I/O操作，提高性能。但在查询这些索引时，需要合并Change Buffer中的更改。

综上所述，InnoDB Buffer Pool是MySQL中InnoDB存储引擎的重要组成部分，它通过缓存数据页和索引页，显著提高了数据库的读写性能，减少了磁盘I/O操作的次数。合理配置和优化Buffer Pool的大小和配置参数，对于提升数据库的整体性能至关重要。

innodb_buffer_pool_instances参数

innodb_buffer_pool_instances 是 MySQL InnoDB 存储引擎中的一个重要配置参数，它决定了 InnoDB 缓冲池（Buffer Pool）被划分为多少个实例（instances）。每个实例都维护自己的数据结构和锁，以提高并发访问和写入的性能，减少线程之间的争用。

基本概念

• Buffer Pool：InnoDB 使用缓冲池来缓存表数据和索引，以减少对磁盘的访问，提高数据库性能。
• 实例（Instances）：将缓冲池划分为多个实例，每个实例独立管理自己的缓存页面和数据结构，如 LRU 列表、空闲列表、刷新列表等。

配置参数

• innodb_buffer_pool_instances：设置 InnoDB 缓冲池被划分为多少个实例。默认值为 1，最大可以设置为 64。
• innodb_buffer_pool_size：设置 InnoDB 缓冲池的总大小，以字节为单位。该参数决定了所有缓冲池实例总共可以使用的内存量。

配置原则

1. 多实例提升并发：在并发量较高的情况下，将缓冲池划分为多个实例可以减少线程之间的锁竞争，提高性能。
2. 实例大小需考虑：每个实例的大小（innodb_buffer_pool_size / innodb_buffer_pool_instances）应大于 1GB，以避免因实例过小而导致的性能问题。
3. 结合硬件资源：在配置 innodb_buffer_pool_instances 时，应考虑服务器的 CPU 核心数、内存大小等硬件资源，以充分利用硬件资源，提高数据库性能。

示例

假设有一个具有 32 个 CPU 核心的服务器，且内存充足，可以考虑将 innodb_buffer_pool_instances 设置为 16 或 32。这样的配置有助于平衡内存访问与 CPU 调度，提高并发处理能力。

注意事项

• 修改 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 需要重启 MySQL 服务才能生效（在 MySQL 5.7 及以后版本中，部分情况下可以动态调整 innodb_buffer_pool_size，但 innodb_buffer_pool_instances 仍然需要重启）。
• 在调整这些参数时，应谨慎考虑，避免对数据库性能造成负面影响。建议在实际部署前进行充分的测试和评估。

综上所述，innodb_buffer_pool_instances 是一个重要的配置参数，通过合理设置可以提高 MySQL InnoDB 存储引擎的并发处理能力和性能。

通过下面命令查看当前innodb_buffer_pool_instances变量值

show variables like 'innodb_buffer_pool_instances';

# 或者等价于上面命令
select @@innodb_buffer_pool_instances;

查看performance_schema使用多少内存

SHOW ENGINE Statement

show engine performance_schema status\G;

performance_schema.memory是performance_schema使用的内存总和字节数。

innodb_buffer_pool_size参数

innodb_buffer_pool_size 是 MySQL 中 InnoDB 存储引擎的一个重要配置参数，用于指定 InnoDB 存储引擎用于缓存数据和索引的内存区域（Buffer Pool）的大小。以下是关于 innodb_buffer_pool_size 的详细解释和配置建议：

作用

InnoDB 存储引擎将数据和索引保存在表空间中，并依赖内存缓存池（Buffer Pool）来缓存表中的数据和索引，以减少对磁盘的访问，从而提高数据库的访问速度和性能。innodb_buffer_pool_size 参数决定了这个缓存池的大小。

默认值与范围

• 默认值：根据不同的 MySQL 版本和安装方式，innodb_buffer_pool_size 的默认值可能有所不同。在一些版本中，默认值可能是 8MB、128MB 或更高。
• 范围：innodb_buffer_pool_size 可以设置的最小值和最大值取决于系统的内存大小和 MySQL 的版本。当 innodb_buffer_pool_instances 设置大于 1 时，innodb_buffer_pool_size 的最小值可能要求不低于 1GB。

配置建议

1. 合理分配内存：建议将 innodb_buffer_pool_size 设置为系统总内存的 60% 到 80%。这样可以确保 InnoDB 存储引擎有足够的内存来缓存数据和索引，同时留出足够的内存给操作系统和其他应用程序使用。
2. 避免过大或过小：
   • 如果设置过大，可能会导致 MySQL 占用过多内存，使系统 swap 增多，反而降低性能。
   • 如果设置过小，InnoDB 存储引擎将无法缓存足够的数据和索引，导致频繁的磁盘 I/O 操作，影响性能。
3. 考虑系统硬件和业务需求：在配置 innodb_buffer_pool_size 时，还需要考虑服务器的硬件配置（如内存大小、CPU 性能等）以及业务的需求（如数据访问频率、并发用户数等）。

配置方法

1. 找到配置文件：MySQL 的配置文件通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf 等位置。
2. 修改配置项：在配置文件中找到 innodb_buffer_pool_size 配置项，并将其值修改为所需的内存大小（单位为字节，但通常使用 K、M、G 等后缀来表示千字节、兆字节、吉字节等）。
3. 保存并重启 MySQL：修改完配置文件后，保存并退出编辑器，然后重启 MySQL 服务以使配置生效。

注意事项

• innodb_buffer_pool_size 是一个重要的性能调优参数，但在调整之前应该充分评估其对系统性能的影响。
• 在生产环境中修改此参数时，建议先在测试环境中进行验证，以确保修改后的配置不会对系统造成负面影响。
• 如果需要在线调整 innodb_buffer_pool_size 的大小（即不重启 MySQL 服务），请确保 MySQL 版本支持此操作，并按照官方文档或社区指南进行操作。

综上所述，innodb_buffer_pool_size 是 MySQL 中影响 InnoDB 存储引擎性能的关键参数之一，合理配置该参数对于提高数据库性能具有重要意义。

实验

以下实验通过 示例 协助

实验1

测试innodb_buffer_pool_size对内存使用率影响

• 调整innodb_buffer_pool_size为2g

  运行InnoDBBufferPoolSizeJmhTests测试，结论：innodb_buffer_pool_size设置越大内存利用率越高。

show engine innodb status\G命令输出解释

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-08-25 14:00:22 140375305615104 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds

INNODB MONITOR OUTPUT 的一部分显示了一个时间戳（2024-08-25 14:00:22）和线程ID（140375305615104），以及一个说明，即每秒平均值是根据过去8秒计算得出的。

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 2367 srv_idle
srv_master_thread log flush and writes: 0

在您提供的INNODB MONITOR OUTPUT的片段中，关于BACKGROUND THREAD的部分显示了InnoDB主线程（master thread）的循环状态和一些特定的操作计数。这里是对这些信息的解释：

• srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 2367 srv_idle

  • 这行信息表示InnoDB的主线程在过去的某个时间段内（可能是自MySQL服务器启动以来或自上次监控输出重置以来）的循环状态。
  • srv_active：表示主线程处于活跃状态（即正在执行某些任务）的次数为1。
  • srv_shutdown：表示主线程处于关闭状态的次数为0。这通常意味着MySQL服务器没有经历过正常的关闭过程，或者至少在这个时间段内没有。
  • srv_idle：表示主线程处于空闲状态的次数为2367。这意味着在大多数情况下，主线程没有执行任何任务，这通常是正常的，因为主线程的任务是周期性的，并且在大多数时间里，它可能只是在等待下一个任务或事件的到来。

• srv_master_thread log flush and writes: 0

  • 这行信息可能有些误导，因为它通常不会直接出现在BACKGROUND THREAD部分下，而是可能作为主线程执行的具体任务之一的一部分。不过，如果确实是这样显示，那么它可能意味着在监控的时间段内，主线程没有执行任何日志刷新（log flush）或写入（writes）操作。然而，这通常不太可能，因为InnoDB的主线程会定期刷新日志以确保数据的一致性。
  • 更可能的是，这个计数（0）可能指的是在监控输出被捕获的特定瞬间，主线程没有正在进行或刚刚完成的日志刷新和写入操作。InnoDB的日志刷新和写入操作是异步的，并且可能由多个线程或进程触发，而不仅仅是主线程。

总的来说，这些信息本身并不直接指示任何性能问题或错误。然而，如果您正在调查性能问题或尝试优化MySQL服务器的性能，那么您可能需要查看更全面的监控输出，包括但不限于：

• 缓冲池的使用情况（如读命中率、写命中率等）。
• 锁等待和死锁情况。
• 事务的持续时间和吞吐量。
• 磁盘I/O的等待时间和吞吐量。

此外，您还可以考虑使用MySQL的性能模式（Performance Schema）或第三方监控工具来获取更详细的性能数据。这些工具可以帮助您更准确地识别和解决性能瓶颈。

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 110
OS WAIT ARRAY INFO: signal count 79
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

todo ...

------------
TRANSACTIONS
------------
Trx id counter 2315
Purge done for trx's n:o < 2313 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421851517910232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421851517909424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421851517908616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

todo ...

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1166 OS file reads, 251 OS file writes, 64 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

todo ...

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 0 buffer(s)
Hash table size 1106407, node heap has 1 buffer(s)
Hash table size 1106407, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

todo ...

---
LOG
---
Log sequence number          28617305
Log buffer assigned up to    28617305
Log buffer completed up to   28617305
Log written up to            28617305
Log flushed up to            28617305
Added dirty pages up to      28617305
Pages flushed up to          28617305
Last checkpoint at           28617305
49 log i/o's done, 0.00 log i/o's/second

todo ...

# mysql8.0 输出
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 375221
Buffer pool size   262123
Free buffers       260834
Database pages     1285
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1143, created 142, written 167
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1285, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

# mariadb10.4.19 输出
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1090519040
Dictionary memory allocated 34160
Buffer pool size   64216
Free buffers       24737
Database pages     38004
Old database pages 13864
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 185, created 37819, written 40752
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 38004, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

这段输出是关于MySQL数据库（特别是使用InnoDB存储引擎时）的缓冲池（Buffer Pool）和内存使用情况的详细统计。InnoDB的缓冲池是InnoDB存储引擎用来缓存表数据和索引内存的区域，以提高数据库操作的性能。下面是对这段输出的详细解释：

• Total large memory allocated 0：表示没有为InnoDB缓冲池分配大内存（large memory）。这通常意味着没有使用InnoDB的大页（large pages）特性，或者该特性在当前配置下未启用。
• Dictionary memory allocated 375221：表示分配给数据字典的内存量为375,221字节。数据字典是InnoDB存储引擎用来存储表结构、索引等元数据的内部数据结构。
• Buffer pool size 262123：表示缓冲池的总大小为262,123个页面。InnoDB缓冲池的大小以页面为单位，每个页面的大小通常是16KB（但可以通过配置更改）。
• Free buffers 260834：表示当前空闲的缓冲池页面数量为260,834。这表明大部分缓冲池页面都是空闲的，可能是因为数据库负载较低或者缓冲池配置得相对较大。
• Database pages 1285：表示当前缓冲池中包含的数据库页面数量为1,285。这些页面可能包含表数据、索引等。
• Old database pages 0：“老”数据库页面的数量为13864。在InnoDB中，缓冲池通过LRU（最近最少使用）算法来管理页面，而“老”页面是指较长时间未被访问的页面。
• Modified db pages 0：被修改但尚未写入磁盘的数据库页面数量为0。这表示所有的更改都已经同步到了磁盘上，或者当前的负载非常低，没有发生任何修改。
• Percent of dirty pages: 脏页（被修改但尚未写入磁盘的页面）的百分比为0.000%。这是正常的情况，特别是在没有更新操作或者所有更新都已经提交到磁盘时。
• Max dirty pages percent: 允许的最大脏页百分比为75.000%。这是InnoDB可以容忍的脏页最大比例，超过这个比例可能会导致写操作被阻塞，直到脏页被刷新到磁盘。
• Pending reads, writes：待处理的读/写操作为0，表示当前没有等待中的I/O操作。
• Pages made young/not young: 使页面“年轻”或保持“不年轻”的操作为0。这是与LRU算法相关的操作，用于维护缓冲池中页面的新旧状态。
• Pages read, created, written：分别表示从磁盘读取、创建（新加入缓冲池）和写入磁盘的页面数量。这些值及其速率反映了缓冲池的使用效率和数据库的I/O活动。
• Reads/s, creates/s, writes/s：表示每秒的读、创建和写操作数，当前均为0，表示没有活动。
• Buffer pool hit rate: 缓冲池命中率。这个例子中为1000/1000，意味着所有的请求都能在缓冲池中找到相应的页面，没有发生磁盘I/O。这是非常理想的情况。
• Read-ahead and Eviction: 读前取、逐出等I/O操作速率为0，表示当前没有这类活动。
• LRU len, unzip_LRU len：LRU链表中页面的数量和未压缩LRU链表中页面的数量。未压缩LRU链表主要用于存储解压的页面（对于压缩表）。
• I/O sum, unzip sum：表示I/O操作和未压缩操作的当前和累计统计信息。

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000解释如下：

关于MySQL InnoDB存储引擎中的Buffer Pool Hit Rate（缓冲池命中率）和Young-Making Rate（年轻页面生成率），以及相关的"not young"指标，以下是详细的解释：

Buffer Pool Hit Rate（缓冲池命中率）

• 定义：缓冲池命中率是指从缓冲池中成功获取数据页的比率，即请求的数据页已经在缓冲池中的比例。
• 表示方式：通常以“成功获取的次数 / 总请求次数”的形式表示。在您给出的例子中，命中率为1000 / 1000，这意味着所有的数据页请求都在缓冲池中找到了对应的页面，没有发生磁盘I/O操作。
• 意义：高命中率是数据库性能优化的一个重要指标，因为它减少了磁盘I/O，提高了数据访问速度。命中率越高，说明缓冲池的使用效率越高，数据库的整体性能也越好。

Young-Making Rate（年轻页面生成率）

• 定义：年轻页面生成率是指将旧页面（old page）移动到新页面列表（young list）头部的频率，即页面因为被访问而变为“年轻”的速率。
• 表示方式：通常以“每秒年轻页面生成数 / 1000”的形式表示。在您给出的例子中，年轻页面生成率为0 / 1000，意味着在当前的时间段内，没有旧页面因为被访问而变为年轻页面。
• 意义：这个指标反映了缓冲池中页面的活跃程度。如果年轻页面生成率很低，可能说明缓冲池中的页面大多数都是长时间未被访问的，或者数据库的工作负载主要是写入操作，而不是读取操作。然而，在您的例子中，由于命中率是1000 / 1000，这可能与当前时间段的低负载或特定的工作负载模式有关。

"not young"指标

• 定义：在InnoDB的监控信息中，"not young"可能指的是那些保持在旧页面列表（old list）中，没有被移动到新页面列表（young list）的页面数量或比率。然而，在您的例子中，并没有直接给出"not young"的具体数值，而是用"0 / 1000"的形式与年轻页面生成率一起表示。这里可能是一个误解或格式问题，因为"not young"通常不会以这种比率的形式出现。
• 意义：如果有一个具体的"not young"数值或比率，它可以用来评估缓冲池中旧页面的比例和这些页面的活跃程度。高比例的"not young"页面可能意味着缓冲池中存在大量的不活跃数据，这可能影响缓冲池的整体效率。然而，在您的例子中，由于命中率为1000 / 1000，且没有直接的"not young"数值，因此这个指标在当前上下文中可能不太相关。

总结

• 高命中率（1000 / 1000）表明缓冲池的使用效率非常高，所有的数据页请求都能够在缓冲池中找到对应的页面，从而避免了磁盘I/O操作。
• 低年轻页面生成率（0 / 1000）可能反映了当前时间段的低负载或特定的工作负载模式，因为所有请求的数据页都已经在缓冲池中，没有旧页面需要因为被访问而变为年轻页面。
• 关于"not young"指标，在您的例子中可能是一个不完整的表示或误解，因为它通常不会以这种比率的形式出现。在实际的监控和性能分析中，应该关注更具体的数值和比率来评估缓冲池的性能。

I/O sum[0]:cur[0], unzip sum[0]:cur[0]解释如下：

在数据库管理系统（如MySQL，特别是使用InnoDB存储引擎时）或一些其他需要处理大量数据读写的系统中，I/O sum[0]:cur[0] 和 unzip sum[0]:cur[0] 是两种可能出现在性能监控或日志中的指标，它们分别代表了不同类型的I/O操作的状态。不过，需要注意的是，这些确切的术语和格式可能会根据具体的数据库系统或监控工具而有所不同。下面，我将基于一般性的理解来解释这些指标。

I/O sum[0]:cur[0]

这个指标通常指的是“输入/输出”操作的统计信息。在这个上下文中，sum[0] 和 cur[0] 可能表示：

• sum[0]：自某个监控周期开始（比如数据库服务启动后）以来，某种类型I/O操作的总数。这里的0可能是一个索引或标识符，但在很多情况下，如果系统中只有一种类型的I/O被特别提及（比如读操作、写操作等），这个索引可能只是形式上的，或者表示特定的上下文（比如，所有类型的I/O被汇总为一个总数）。然而，在某些系统中，这个0可能确实代表某种特定的I/O类型或分类。
• cur[0]：在当前监控时刻或周期内，这种类型I/O操作的当前数量。这个值通常用于展示实时的I/O活动情况。

由于这里的值都是0，它可能意味着自监控开始以来，没有这种类型的I/O操作发生，或者监控工具/系统尚未捕获到任何相关的I/O活动。

unzip sum[0]:cur[0]

这个指标则特定于数据解压缩的I/O操作。在数据库中，尤其是处理压缩表或压缩索引时，数据在读取到内存中之前可能需要进行解压缩。unzip sum[0]:cur[0] 可能表示：

• sum[0]：自某个监控周期开始以来，解压缩操作的总数。这反映了数据库在处理压缩数据时进行了多少次解压缩操作。
• cur[0]：当前监控时刻或周期内，正在进行的解压缩操作的数量。这个值可以帮助理解当前的数据库负载是否涉及大量的解压缩工作，从而可能影响到查询性能。

同样，这里的0值可能表示自监控开始以来没有发生任何解压缩操作，或者监控工具没有捕获到相关的活动。

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32766
Free buffers       32524
Database pages     242
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 241, created 1, written 3
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 242, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32765
Free buffers       32587
Database pages     177
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 175, created 2, written 2
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 177, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32765
Free buffers       32632
Database pages     132
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 129, created 3, written 7
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 132, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32765
Free buffers       32700
Database pages     64
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 63, created 1, written 5
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32765
Free buffers       32689
Database pages     75
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 74, created 1, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 75, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32767
Free buffers       32656
Database pages     111
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 47, created 64, written 64
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 111, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32765
Free buffers       32530
Database pages     235
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 170, created 65, written 72
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 235, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32765
Free buffers       32516
Database pages     249
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 244, created 5, written 13
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 249, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

每个Buffer Pool的Database pages总和等于Buffer Pool And Memory的Database pages总和。

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140371187914496 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 317, deleted 0, read 4675
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

todo ...

show engine innodb status\G命令用法

查看innodb buffer pool用量

innodb buffer pool当前使用大小计算：innodb buffer pool大小 = Database pages x 页面大小

查询页面大小

select concat(@@innodb_page_size/1024,'KB') as '页面大小';

查看mysql i/o情况

• Pending reads 0和Pending writes: LRU 0, flush list 0, single page 0判断当前是否有排队的数据页面读写。
• Pages read 185, created 37819, written 40752和0.00 reads/s, 0.00 creates/s, 0.00 writes/s判断当前是否有频繁的数据页面读写。
• I/O sum[0]:cur[0], unzip sum[0]:cur[0]判断累计和最近一个周期的数据页面读写操作数。

查看innodb查询缓存命中率

• Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000判断查询缓存命中率。

innodb_additional_mem_pool_size参数

它指定了InnoDB用来保存数据字典信息和其他内部数据结构的内存池的大小，单位是字节（byte）。mysql5.6被标记为已弃用，从mysql5.7开始被移除。mysql5.7之后不需要关注此参数配置。

innodb_log_buffer_size参数

innodb_log_buffer_size 是 MySQL 中 InnoDB 存储引擎的一个重要参数，它定义了 InnoDB 用于写入磁盘上的日志文件的缓冲区的大小（以字节为单位）。这个参数的设置对数据库的性能有显著影响。

主要作用

• 提高写入性能：较大的日志缓冲区可以缓存更多的日志数据，减少事务提交时写入磁盘的次数，从而提高写入性能。
• 减少磁盘I/O：在事务提交之前，日志数据首先被写入到这个缓冲区中，而不是直接写入磁盘。这样，只有在缓冲区满或者事务提交时，日志数据才会被写入磁盘，从而减少了磁盘I/O操作。

默认值与推荐值

• 默认值：在不同的 MySQL 版本中，innodb_log_buffer_size 的默认值可能有所不同。但通常，其默认值为 16MB（在 MySQL 5.7 及以后版本中较为常见）。
• 推荐值：推荐值的大小取决于具体的应用场景和数据库的工作负载。一般来说，如果数据库中存在大量更新、插入或删除操作的事务，可以考虑适当增加这个参数的值。然而，过大的值会占用过多的内存资源，影响系统的稳定性。因此，建议根据实际的监控数据和性能测试结果来确定一个合适的值。

注意事项

• 内存占用：innodb_log_buffer_size 的值越大，占用的内存就越多。因此，在设置时需要考虑到服务器的总内存大小和其他内存密集型应用的内存需求。
• 调优建议：
  • 可以通过监控 InnoDB redo log 缓存使用率和写入性能等指标来进行调优。
  • 如果发现日志缓冲区频繁满溢，导致频繁的磁盘I/O操作，可以考虑增加 innodb_log_buffer_size 的值。
  • 如果系统内存资源紧张，或者发现其他内存密集型应用受到影响，可以考虑适当减小这个参数的值。

结论

innodb_log_buffer_size 是 MySQL 中一个非常重要的参数，它直接影响到数据库的写入性能和稳定性。在设置这个参数时，需要根据实际的应用场景和数据库工作负载来进行合理的配置和调整。

key_buffer_size参数

key_buffer_size 是MySQL中一个重要的配置参数，它用于设置MyISAM存储引擎索引块缓存的大小，直接影响数据库的读写性能。以下是关于key_buffer_size的详细解析：

作用与影响

• 提高查询效率：key_buffer_size决定了索引缓冲区的大小，索引缓冲区是存储索引数据的内存区域。当执行查询时，MySQL会首先检查索引缓冲区，如果索引数据在缓冲区中找到了，就可以直接返回结果，无需从磁盘中读取数据，从而显著提高查询效率。
• 影响内存分配：key_buffer_size的值表示请求的内存量，MySQL服务器会尽可能分配这么多内存用于索引缓存，但实际分配量可能受到操作系统或硬件平台限制。

设置建议

• 合理范围：一般建议将key_buffer_size设置为系统总内存的25%以内，以避免影响操作系统和其他服务的性能。然而，这个比例并非绝对，具体需要根据实际负载和性能需求来调整。
• 平台差异：在32位平台上，key_buffer_size的最大值通常建议不超过2GB或4GB（取决于具体系统和MySQL版本），因为32位系统的内存寻址能力有限。而在64位平台上，可以支持更大的值，但同样需要注意内存使用效率。
• 性能测试：通过实验和测试来确定最佳的key_buffer_size值，以获得最佳的性能。可以观察Key_reads/Key_read_requests的比率，该比率通常应小于0.01，表示索引缓存的读取命中率较高。

监控与调优

• 监控性能：通过SHOW STATUS命令可以监控与索引缓存相关的性能状态参数，如Key_reads、Key_read_requests、Key_writes等，以了解索引缓存的使用情况和性能表现。
• 调整策略：如果发现索引缓存的读取命中率较低或写入操作频繁导致性能下降，可以考虑调整key_buffer_size的值。同时，也可以结合其他相关参数（如key_cache_division_limit、key_cache_age_threshold等）进行调优。

注意事项

• 内存限制：在设置key_buffer_size时需要考虑服务器的总内存大小和其他应用的内存需求，避免因为MySQL占用过多内存而导致系统不稳定。
• 版本差异：不同版本的MySQL可能对key_buffer_size的处理和限制有所不同，因此在设置时需要参考具体版本的官方文档或相关指南。

总之，key_buffer_size是MySQL中一个关键的配置参数，合理的设置可以显著提高数据库的查询性能。在设置时需要根据实际需求和性能监控结果进行调整，并注意避免内存资源的过度占用。

key_buffer_size对innodb有影响吗？

key_buffer_size对InnoDB没有直接影响。

key_buffer_size是MySQL中用于设置MyISAM存储引擎索引块缓存大小的参数。它主要影响MyISAM表的索引读取性能，通过缓存索引数据来减少对磁盘的访问，从而提高查询效率。然而，InnoDB存储引擎使用自己的缓冲池（innodb_buffer_pool_size）来缓存数据和索引，而不是依赖于key_buffer_size。

InnoDB的缓冲池（innodb_buffer_pool_size）是InnoDB存储引擎中最重要的内存区域之一，它用于缓存表数据和索引，以减少对磁盘的访问，提高数据库的读写性能。与key_buffer_size不同，innodb_buffer_pool_size的设置对InnoDB表的性能有直接影响。

因此，当使用InnoDB存储引擎时，应该关注innodb_buffer_pool_size的设置，并根据数据库的实际负载和性能需求进行调整。而对于MyISAM表，如果仍然在使用，则需要关注key_buffer_size的设置。但需要注意的是，随着MySQL版本的更新和InnoDB存储引擎的不断发展，MyISAM存储引擎的使用已经逐渐减少，许多新特性和优化都是针对InnoDB存储引擎进行的。

综上所述，key_buffer_size对InnoDB没有直接影响，它是专门为MyISAM存储引擎设计的索引缓存参数。在配置MySQL时，应该根据所使用的存储引擎和数据库的实际需求来选择合适的参数进行设置。

query_cache_size参数

mysql8.0之后已经删除此参数，删除原因请参考 链接

query_cache_size 是MySQL数据库中的一个系统变量，它用来设置查询缓存（Query Cache）的大小，单位是字节。查询缓存是MySQL中的一种机制，可以缓存SELECT语句的查询结果，从而提高查询性能。

主要功能和作用

• 设置缓存大小：通过调整query_cache_size，可以控制MySQL为查询缓存分配的内存量。这个值直接决定了查询缓存能够缓存多少查询结果。
• 性能优化：合理设置query_cache_size可以帮助提高查询性能，特别是在读操作远多于写操作的场景中。然而，如果设置得过大，可能会占用过多的内存资源，影响其他数据库操作的性能。

默认值与调整建议

• 默认值：MySQL中，query_cache_size的默认值可能因版本和配置而异，但通常可能是一个相对较小的值，比如0（表示不开启查询缓存）或者是一个具体的字节数（如3MB）。
• 调整建议：
  • 如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size一般建议设置为总内存的10%左右，但具体值需要根据实际情况进行调整。
  • 在高并发、动态更新频繁的应用场景中，建议关闭查询缓存（即将query_cache_size设置为0），因为查询缓存可能会因为数据更新而频繁失效，反而降低性能。
  • 可以通过计算查询缓存的命中率（Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%）来评估和调整query_cache_size的大小。命中率越高，说明查询缓存的效果越好。

注意事项

• 内存占用：过大的query_cache_size会占用过多的内存资源，影响数据库的整体性能。
• 数据更新：当数据库中的数据发生变化时，所有引用了该数据的查询缓存都会失效，这在高更新频率的场景中可能会导致性能下降。
• 查询语句：查询缓存是基于查询语句的，如果查询语句中包含变量或动态因素（如当前时间、随机数等），则无法缓存。

如何查看和设置

• 查看：可以通过执行SHOW GLOBAL VARIABLES LIKE 'query_cache_size';命令来查看当前的query_cache_size值。
• 设置：可以通过执行SET GLOBAL query_cache_size = [新值];命令来动态调整query_cache_size的大小。但请注意，这种调整是临时的，重启MySQL服务后设置会失效。要永久更改，需要在MySQL的配置文件（如my.cnf或my.ini）中设置该参数。

综上所述，query_cache_size是MySQL查询缓存的一个重要参数，合理设置该参数可以帮助提高数据库的查询性能。但需要根据实际情况进行调整和优化，以避免内存占用过多或查询缓存频繁失效等问题。

• 查看是否支持query cache特性

  SHOW VARIABLES LIKE 'have_query_cache';

binlog_cache_size参数

binlog_cache_size 是 MySQL 数据库中的一个重要配置参数，它用于指定在事务处理期间，用于保存二进制日志（binlog）的内存大小。以下是对 binlog_cache_size 的详细解释：

1. 定义与用途

• 定义：binlog_cache_size 是指 MySQL 服务器为每个会话（SESSION）分配的用于存储二进制日志事件的内存大小。当服务器启用了二进制日志（通过将 log_bin 系统变量设置为 ON），并且存储引擎支持事务时，此参数就会生效。
• 用途：该缓存用于临时存储事务的二进制日志数据。当事务提交时，这些日志数据将被写入实际的二进制日志文件中，以便进行复制、恢复等操作。

2. 默认值与调整

• 默认值：在 MySQL 中，binlog_cache_size 的默认值是 32KB，且其取值必须是 4KB 的整数倍。
• 调整方法：根据需要，管理员可以通过 SQL 语句或配置文件来调整 binlog_cache_size 的值。例如，可以使用 SET GLOBAL binlog_cache_size = 65536;（64KB）来修改全局设置，或者使用 SET SESSION binlog_cache_size = 65536; 来修改当前会话的设置。

3. 注意事项

• 内存使用：设置 binlog_cache_size 时，需要考虑到服务器的内存资源。如果设置得过大，可能会浪费内存；如果设置得过小，则可能导致频繁地将日志数据写入磁盘，从而影响性能。
• 事务大小：理想情况下，binlog_cache_size 应该能够容纳大部分事务的二进制日志数据，以减少对磁盘的依赖。因此，在调整此参数时，可以参考实际事务的大小来进行设置。
• 监控与调整：MySQL 提供了多种监控工具和方法来查看 binlog_cache_size 的使用情况，如 SHOW GLOBAL STATUS LIKE 'Binlog_cache%'; 语句可以显示与二进制日志缓存相关的状态信息。如果发现 Binlog_cache_disk_use 的值异常高，可能表明需要增加 binlog_cache_size 的值。

4. 相关参数

• max_binlog_cache_size：这个参数用于指定当某个事务的二进制日志事件超过了 binlog_cache_size 的大小时，可以使用的最大缓存大小（包括 binlog_cache_size 和额外的临时文件空间）。如果事务的日志量超过了 max_binlog_cache_size + binlog_cache_size 的设置，MySQL 将报错。

综上所述，binlog_cache_size 是 MySQL 中一个关键的配置参数，它对于确保数据库的性能和稳定性至关重要。在调整此参数时，需要综合考虑服务器的内存资源、事务的大小以及监控到的使用情况。

实验

以下实验通过 示例 协助

查看binlog_cache_use和binlog_cache_disk_use状态值

show global status like '%binlog%cache%';

实验1

调整事务大小观察binlog_cache_use和binlog_cache_disk_use状态值

• LargeTransactionService#execute的statementCount参数值2，binlog_cache_size默认值32k

  运行LargeTransactionServiceTests#test测试，结论：事务执行会导致binglog_cache_use状态值增加，因为statementCount为2事务小不会导致binlog_cache_disk_use状态值增加。

• LargeTransactionService#execute的statementCount参数值128，binlog_cache_size默认值32k

  运行LargeTransactionServiceTests#test测试，结论：事务执行会导致binglog_cache_use状态值增加，因为statementCount为128事务大与binlog_cache_size默认值32k会导致binlog_cache_disk_use状态值增加。

实验2

通过调整my.cnf文件中binlog_cache_size参数大小，观察binlog_cache_use和binlog_cache_disk_use状态值

• LargeTransactionService#execute的statementCount参数值128，调整binlog_cache_size参数值分别为32k和16m

  运行LargeTransactionServiceTests#test测试，结论：binlog_cache_size设置过小会导致binlog_cache_disk_use状态值增加。

实验3

binlog_cache_size设置值和事务大小对mysql实例内存使用率的影响

• LargeTransactionService#execute的statementCount参数值4096，调整binlog_cache_size参数值分别为32k和16m

  运行LargeTransactionService#testMemoryUsage测试，结论：binlog_cache_size默认值32k即使事务很大也不会占用太多内存；binlog_cache_size设置为16m，事务很大会占用很多内存。

• binlog_cache_size参数值设置为16m，LargeTransactionService#execute的statementCount参数值8（小事务）

  运行LargeTransactionService#testMemoryUsage测试，结论：binlog_cache_size设置为16m，事务小也不会占用很多内存。

join_buffer_size参数

todo：无法成功做实验模拟

join_buffer_size 是 MySQL 数据库中的一个重要配置参数，它用于指定在执行连接（JOIN）操作时，MySQL 为每个连接操作分配的缓冲区大小。以下是对 join_buffer_size 的详细解释：

1. 定义与用途

• 定义：join_buffer_size 是 MySQL 在执行连接查询时，为每个连接操作分配的缓冲区大小。这个缓冲区用于存储参与连接操作的表中的数据，以便在连接过程中进行高效的匹配和比较。
• 用途：当 MySQL 执行连接查询时，如果连接操作涉及到全表扫描或索引扫描，并且无法直接通过索引来优化查询性能，那么 MySQL 就会使用 join_buffer_size 指定的缓冲区来存储被连接表的数据。通过减少被连接表的读取次数，join_buffer_size 可以提高连接查询的性能。

2. 默认值与调整

• 默认值：在 MySQL 中，join_buffer_size 的默认值是 256KB（即 256K）。这个默认值对于小型数据库和轻量级查询可能是足够的，但对于大型数据库和复杂的查询来说，可能需要更大的缓冲区来提高性能。
• 调整方法：管理员可以通过修改 MySQL 的配置文件（如 my.cnf 或 my.ini）或在运行时使用 SQL 语句来调整 join_buffer_size 的值。例如，在配置文件中设置 join_buffer_size = 4M 可以将缓冲区大小调整为 4MB。需要注意的是，join_buffer_size 是一个会话级别的参数，但也可以设置为全局级别。

3. 注意事项

• 内存使用：增加 join_buffer_size 的值可以提高连接查询的性能，但同时也会增加 MySQL 服务器的内存使用量。因此，在调整此参数时，需要考虑到服务器的内存资源是否充足。
• 并发连接：join_buffer_size 是一个连接级别的参数，每个连接都会分配一个独立的缓冲区。因此，在高并发连接的情况下，如果 join_buffer_size 设置得过大，可能会导致服务器内存不足的问题。
• 查询优化：虽然 join_buffer_size 可以提高连接查询的性能，但最好的做法还是通过优化查询语句和索引来减少连接操作的需求。例如，通过添加合适的索引来避免全表扫描或索引扫描，可以显著提高查询性能而无需增加 join_buffer_size 的值。

4. 实际应用

在实际应用中，join_buffer_size 的调整需要根据具体的查询需求和服务器资源来进行。对于复杂的连接查询和大型数据库来说，适当增加 join_buffer_size 的值可能有助于提高性能。但是，在调整之前，建议先通过查询优化和索引优化来减少连接操作的需求，并监控服务器的内存使用情况以避免内存不足的问题。

此外，还需要注意的是，join_buffer_size 的调整并不是一劳永逸的。随着数据库的增长和查询需求的变化，可能需要定期重新评估和调整此参数的值以保持最佳的性能。

read_buffer_size参数

注意：read_buffer_size只对myisam引擎起作用（对innodb引擎不起作用），具体说明请参考 链接

read_buffer_size 是 MySQL 数据库中的一个重要配置参数，它主要用于指定 MySQL 在进行顺序读取（sequential read）操作时所使用的缓存大小。以下是对 read_buffer_size 的详细解释：

定义与用途

• 定义：read_buffer_size 是 MySQL 读取数据时使用的内存缓冲区大小。这个参数控制了 MySQL 在执行顺序扫描（如全表扫描或全索引扫描）时，每次从磁盘读取数据到内存中的块大小。
• 用途：当 MySQL 需要顺序读取大量数据时，如执行全表扫描或无法通过索引快速定位数据的情况下，read_buffer_size 指定的缓存大小将影响读取操作的效率。通过合理设置 read_buffer_size，可以减少磁盘I/O次数，提高数据读取速度。

默认值与调整

• 默认值：MySQL 的 read_buffer_size 默认值可能因版本和配置而异，但通常是一个相对较小的值，如 128KB 或 256KB。这个默认值对于小型查询和轻量级应用可能是足够的，但对于处理大量数据的应用来说，可能需要调整。
• 调整方法：管理员可以通过修改 MySQL 的配置文件（如 my.cnf 或 my.ini）或在运行时使用 SQL 语句来调整 read_buffer_size 的值。例如，在配置文件中设置 read_buffer_size = 4M 可以将缓冲区大小调整为 4MB。

注意事项

• 内存使用：增加 read_buffer_size 的值可以提高顺序读取操作的性能，但同时也会增加 MySQL 服务器的内存使用量。因此，在调整此参数时，需要综合考虑服务器的内存资源是否充足。
• 会话级别与全局级别：read_buffer_size 可以设置为会话级别或全局级别。会话级别的设置仅影响当前会话，而全局级别的设置将影响所有新创建的会话。
• 查询优化：虽然 read_buffer_size 可以提高顺序读取操作的性能，但最好的做法还是通过优化查询语句和索引来减少顺序扫描的需求。例如，通过添加合适的索引来避免全表扫描或全索引扫描，可以显著提高查询性能而无需增加 read_buffer_size 的值。

实际应用

在实际应用中，read_buffer_size 的调整需要根据具体的查询需求和服务器资源来进行。对于需要执行大量顺序扫描操作的应用来说，适当增加 read_buffer_size 的值可能有助于提高性能。但是，在调整之前，建议先通过查询优化和索引优化来减少顺序扫描的需求，并监控服务器的内存使用情况以避免内存不足的问题。

此外，还需要注意的是，read_buffer_size 的调整并不是一劳永逸的。随着数据库的增长和查询需求的变化，可能需要定期重新评估和调整此参数的值以保持最佳的性能。

实验

以下实验通过 示例 协助

实验1

测试read_buffer_size对myisam引擎的表内存使用率影响

• 调整read_buffer_size为16m

  运行ReadBufferSizeJmhTests测试，结论：read_buffer_size对myisam引擎设置越高内存占用率越高。

read_rnd_buffer_size参数

注意：read_rnd_buffer_size对myisam和innodb引擎内存使用率产生影响，当sort_buffer_size设置设置为128k时，read_rnd_buffer_size设置越大排序sql内存使用率越高，参考 链接

read_rnd_buffer_size是MySQL数据库中的一个系统变量，它控制着MySQL在处理排序（ORDER BY）操作后，从已排序的键值表中读取行数据时所使用的随机读缓冲区的大小。以下是对read_rnd_buffer_size的详细解释：

定义与作用

• 定义：read_rnd_buffer_size是MySQL用于优化排序查询后读取数据行性能的一个参数。它指定了MySQL为每个客户端连接分配的随机读缓冲区的大小（以字节为单位）。
• 作用：当MySQL执行ORDER BY等排序操作后，需要从排序后的结果中读取数据时，如果这些数据不是连续存储在磁盘上的（即需要随机读取），MySQL就会使用read_rnd_buffer_size指定的缓冲区来减少磁盘I/O操作，提高查询效率。

使用场景

read_rnd_buffer_size在以下场景中特别有用：

• 排序查询频繁：当数据库中执行大量包含ORDER BY子句的查询时，增加read_rnd_buffer_size的值可以提高这些查询的性能。
• 大表排序：对于包含大量数据的表进行排序时，较大的read_rnd_buffer_size可以减少磁盘I/O次数，加快查询速度。
• 包含BLOB/TEXT字段的查询：如果排序后的查询结果中包含BLOB或TEXT等大数据类型的字段，且这些字段的数据长度超过了max_length_for_sort_data参数的值，那么MySQL会使用read_rnd_buffer_size来优化这些字段的数据读取。

设置与注意事项

• 设置方式：read_rnd_buffer_size可以在MySQL的配置文件（如my.cnf或my.ini）中全局设置，也可以在MySQL会话中动态设置。但是，由于它是为每个客户端连接分配的，因此不建议将全局值设置得过大，以免浪费内存资源。
• 默认值：MySQL的read_rnd_buffer_size默认值可能因版本而异，但通常是一个相对较小的值（如256KB）。
• 最大值：read_rnd_buffer_size的最大值可以设置为2GB，但实际应用中应根据服务器的内存大小和数据库的工作负载来合理设置。
• 内存开销：由于read_rnd_buffer_size是为每个客户端连接分配的，因此设置过大会导致内存开销过大，影响服务器性能。

总结

read_rnd_buffer_size是MySQL中一个重要的系统变量，用于优化排序查询后的数据读取性能。在配置时，需要根据服务器的实际情况和数据库的工作负载来合理设置其值，以达到最佳的性能效果。

实验

以下实验通过 示例 协助

实验1

sort_buffer_size=128k，read_rnd_buffer_size设置为128k和16m时对内存使用率影响

• 调整sort_buffer_size=128k，read_rnd_buffer_size分别设置为128k和16m

  运行SortQueryJmhTests测试，结论：read_rnd_buffer_size设置越大内存使用率越高。

sort_buffer_size参数

sort_buffer_size是MySQL数据库中的一个重要系统变量，它用于控制排序操作的临时缓冲区大小。以下是关于sort_buffer_size的详细解释：

定义与作用

• 定义：sort_buffer_size指定了MySQL为每个需要执行排序操作的会话（session）分配的缓冲区大小（以字节为单位）。这个缓冲区用于存储排序操作中的中间结果。
• 作用：在执行ORDER BY或GROUP BY等排序操作时，MySQL会利用sort_buffer_size指定的缓冲区来减少磁盘I/O操作，提高排序性能。较大的sort_buffer_size可以容纳更多的排序数据，从而减少排序过程中与磁盘的交互次数，但同时也会占用更多的内存资源。

设置与查看

• 查看当前值：可以通过执行SHOW VARIABLES LIKE 'sort_buffer_size';命令来查看当前的sort_buffer_size值。
• 设置值：
  • 会话级别：使用SET SESSION sort_buffer_size = 值;命令可以为当前会话设置sort_buffer_size的值，但这个设置仅对当前会话有效，会话结束时设置将失效。
  • 全局级别：使用SET GLOBAL sort_buffer_size = 值;命令可以为所有新会话设置sort_buffer_size的默认值，但这个设置对已经存在的会话没有影响，且需要MySQL的super权限。
  • 配置文件：要永久改变sort_buffer_size的值，需要在MySQL的配置文件（如my.cnf或my.ini）中设置sort_buffer_size的值，并重启MySQL服务。

注意事项

• 内存资源：虽然较大的sort_buffer_size可以提高排序性能，但也会占用更多的内存资源。因此，在设置sort_buffer_size时，需要根据服务器的内存大小和数据库的工作负载来合理设置。
• 并发性能：在高并发的环境下，如果sort_buffer_size设置过大，可能会导致系统内存资源紧张，进而影响其他操作的性能。因此，需要权衡排序性能与系统整体性能之间的关系。
• 默认值：MySQL的sort_buffer_size默认值可能因版本和安装方式而异，但通常是一个相对适中的值（如256KB或2MB）。

优化建议

• 监控与分析：通过监控数据库的性能指标和查询计划，了解哪些查询使用了大量的排序操作，并根据实际情况调整sort_buffer_size的值。
• 逐步调整：在调整sort_buffer_size时，建议逐步增加其值，并观察数据库性能的变化，以找到最优的设置。
• 考虑其他因素：除了sort_buffer_size外，还需要考虑其他与排序性能相关的因素，如max_sort_length、query_cache_size等参数的设置以及索引的使用情况。

实验

以下实验通过 示例 协助

实验1

sort_buffer_size=4m，参与order by记录越多内存使用率越高，limit startIndex,length中startIndex越大内存使用率越高

• 调整SortQueryJmhTests#test测试中的Random bound参数2000，以观察不同的Random bound值对order by排序的内存使用率影响

  运行SortQueryJmhTests#test测试，结论：参与order by记录越多内存使用率越高，limit startIndex,length中startIndex越大内存使用率越高。

实验2

SortQueryJmhTests#test测试中的Random bound参数为20000，sort_buffer_size越大内存使用率越高

• 调整SortQueryJmhTests#test测试中的Random bound参数为20000，sort_buffer_size分别设置为64k和32m

  运行SortQueryJmhTests#test测试，结论：sort_buffer_size越大内存使用率越高。

tmp_table_size参数

注意

• 编写测试sql不要触发Created_tmp_disk_tables状态值增加，否则内存使用率不会随着tmp_table_size增加而增加，例如：测试sql group by长度大的字段会触发Created_tmp_disk_tables状态值增加，导致内存使用率不高。
• tmp_table_size与max_heap_table_size参数共同决定了内部临时表在内存中的最大大小。这两个参数中的较小值将作为实际限制。

tmp_table_size是MySQL数据库中的一个系统变量，它用于指定临时表的最大大小（以字节为单位）。临时表是MySQL在执行查询操作时临时生成的表，通常用于处理中间结果或者临时存储数据。设置合适的tmp_table_size可以提高查询性能，避免临时表过大导致性能下降。

默认值与范围

• 默认值：tmp_table_size的默认值可能因MySQL版本和安装设置而异，但通常是一个相对较大的值，如16MB（即16777216字节）。
• 范围：tmp_table_size的最小值可以设置为1，最大值接近MySQL可以处理的最大值（例如，在64位系统上接近18446744073709551615字节）。

如何查询tmp_table_size

要查询当前MySQL服务器的tmp_table_size设置，可以使用以下SQL语句：

SHOW VARIABLES LIKE 'tmp_table_size';

执行上述SQL语句后，MySQL将返回一个结果集，其中包含tmp_table_size参数的值以及其他相关信息。结果集中的Value列即为当前tmp_table_size的设置值。

如何设置tmp_table_size

要修改tmp_table_size的值，可以使用以下SQL语句：

SET GLOBAL tmp_table_size = <size>;

其中，<size>是你想要设置的内存大小，可以使用字节（B）、千字节（KB）、兆字节（MB）或吉字节（GB）作为单位（但通常不带单位后缀，直接使用字节数）。例如，要设置tmp_table_size为32MB，可以写为：

SET GLOBAL tmp_table_size = 33554432; -- 32MB对应的字节数

注意：修改tmp_table_size可能会影响到系统的性能。如果设置得太小，可能会导致临时表无法容纳足够的数据，从而降低查询效率；如果设置得太大，可能会占用过多的内存资源，导致系统性能下降或内存溢出。因此，在修改tmp_table_size之前，需要仔细评估当前系统的需求和资源情况，以确保合理配置。

其他相关参数

值得注意的是，tmp_table_size与max_heap_table_size参数共同决定了内部临时表在内存中的最大大小。这两个参数中的较小值将作为实际限制。此外，如果临时表的大小超过了tmp_table_size或max_heap_table_size的限制，MySQL可能会将临时表从内存转移到磁盘上，这可能会影响查询性能。因此，在调整这些参数时，也需要考虑max_heap_table_size的设置以及磁盘I/O性能。

总结

tmp_table_size是MySQL中一个重要的系统变量，用于控制临时表的最大大小。通过合理设置tmp_table_size，可以提高查询性能并优化系统资源的使用。在调整该参数时，需要综合考虑系统需求、资源情况和查询性能等因素。

触发使用内部临时表条件

• https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html

通过命令查看sql是否使用内部临时表

show global status like '%Created_tmp%';

实验

以下实验通过 示例 协助

实验1

tmp_table_size对内存使用率影响

• 分别调整tmp_table_size``128k和32m默认值，观察mysql内存使用率变化

  运行TempTableJmhTests测试，结论：tmp_table_size设置越大内存使用率越高。

tmp_table_size和max_heap_table_size区别

max_heap_table_size说明

tmp_table_size和max_heap_table_size是MySQL数据库中两个重要的系统变量，它们分别用于控制临时表和内存表的大小。下面详细解释这两个参数的作用和区别：

tmp_table_size

作用：

• tmp_table_size用于定义MySQL服务器中内部内存临时表的最大大小。这个变量控制的是在执行查询时，MySQL为存储临时数据而在内存中创建的表的大小限制。
• 当查询中需要创建的临时表大小超过tmp_table_size指定的值时，MySQL会自动将这个临时表从内存转移到磁盘上，这可能会降低查询的性能，因为磁盘的I/O速度远低于内存。

特性：

• tmp_table_size既可以设置为全局变量，也可以设置为会话级变量。全局变量对所有会话生效，而会话级变量仅对当前会话生效。
• 默认值通常为16MB（但具体值可能因MySQL版本和配置而异），可以通过配置文件（如my.cnf或my.ini）或SQL命令（如SET GLOBAL tmp_table_size=...;）进行调整。

max_heap_table_size

作用：

• max_heap_table_size用于限制用户创建的MEMORY表（内存表）可以增长到的最大大小。内存表是一种特殊的表，其数据存储在内存中，因此可以提供比磁盘表更快的访问速度。
• 这个变量不影响MySQL自动创建的内部临时表，而是仅对用户明确创建的MEMORY表有效。

特性：

• 同样，max_heap_table_size也可以设置为全局变量或会话级变量。
• 默认值通常为16MB（但具体值可能因MySQL版本和配置而异），可以通过配置文件或SQL命令（如SET GLOBAL max_heap_table_size=...;）进行调整。
• 当MEMORY表的大小超过max_heap_table_size时，MySQL会报错，并阻止表的进一步增长。

对比与关系

• 作用域：两者都可以设置为全局或会话级变量。
• 用途：tmp_table_size用于控制内部临时表的大小，而max_heap_table_size用于控制用户创建的MEMORY表的大小。
• 限制条件：当内存中的临时表或MEMORY表超过其对应变量的限制时，MySQL会采取不同的措施。对于临时表，MySQL会将其转移到磁盘上；对于MEMORY表，MySQL会报错并阻止其进一步增长。
• 默认值：两者默认值可能因MySQL版本和配置而异，但通常为16MB。

调整建议

• 在调整tmp_table_size和max_heap_table_size时，需要根据服务器的内存大小和数据库的工作负载来合理设置。
• 如果数据库经常需要处理大量数据，并且有足够的内存资源，可以适当增加这两个参数的值以提高性能。
• 但是，过高的值可能会占用大量内存资源，影响系统的稳定性和其他应用程序的性能。
• 在调整参数后，建议监控数据库的性能指标（如查询响应时间、内存使用率等），以确保调整效果符合预期。

mariadb 10.4.19设置使用jemalloc内存管理器

Using MariaDB with TCMalloc or jemalloc

mariadb 10.4.19设置使用jemalloc作为内存管理器，防止内存泄漏（默认的system内存管理器似乎内存泄漏不会回收已分配的部分内存）。

• 显示当前使用的内存管理器

  SHOW GLOBAL VARIABLES LIKE 'version_malloc_library';

• 在mariadb容器中执行以下命令查找jemalloc内存管理器路径

  find /usr/lib -name "libjemalloc.so.*"

• mariadb Dockerfile添加以下配置以使用jemalloc作为内存管理器

  ENV LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.2

理论最大内存使用率计算

参考

在MySQL中，内存占用主要包括以下几部分，全局共享的内存、线程独占的内存，具体如下：

全局共享

• innodb_buffer_pool_size：InnoDB缓冲池的大小
• innodb_additional_mem_pool_size：InnoDB存放数据字典和其他内部数据结构的内存大小，5.7已被移除
• innodb_log_buffer_size：InnoDB日志缓冲的大小
• key_buffer_size：MyISAM缓存索引块的内存大小
• query_cache_size：查询缓冲的大小，8.0已被移除

线程独占

• thread_stack：每个线程分配的堆栈大小
• sort_buffer_size：排序缓冲的大小
• join_buffer_size：连接缓冲的大小
• read_buffer_size：MyISAM顺序读缓冲的大小
• read_rnd_buffer_size：MyISAM随机读缓冲的大小、MRR缓冲的大小
• tmp_table_size/max_heap_table_size：内存临时表的大小
• binlog_cache_size：二进制日志缓冲的大小

计算公式如下：

select 
(@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size+@@query_cache_size)/1024/1024 as '全局共享内存(MB)',
((@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+
(case when @@tmp_table_size>@@max_heap_table_size then @@max_heap_table_size else @@tmp_table_size end)+@@binlog_cache_size)/1024/1024)*
(select count(id) from information_schema.processlist where id!=connection_id() and user!='system user') as '线程最大内存(MB)';

select count(id) from information_schema.processlist where id!=connection_id() and user!='system user'获取当前连接数

微型mysql实例配置

参考链接

[mysqld]
innodb_flush_log_at_trx_commit=0
innodb_file_per_table=1

# 一旦提供log_bin参数无论是何值或者不提供值时，表示启用binlog功能
# 不提供log_bin表示禁用binlog功能
log_bin
expire_logs_days=10
binlog_format=mixed
max_binlog_size=1024m
# 指定binlog文件的前缀
log_basename=master1
# mysql5.7需要设置此参数才能够启动
server-id=10001

max_connections=16

innodb_buffer_pool_size=5m
innodb_log_buffer_size=128k
# mysql8报告变量不存在导致不能启动
query_cache_size=0
key_buffer_size=8
thread_stack=146k
sort_buffer_size=64k
read_buffer_size=32k
read_rnd_buffer_size=32k
max_heap_table_size=16K
tmp_table_size=1k
join_buffer_size=32k
binlog_cache_size=16k

使用以上的配置控制mysql实例内存使用率不超过512m

内存调试结论

使用mysql 实验协助项目 的InnoDBBufferPoolSizeJmhTests测试得出结论如下：

• innodb buffer pool设置过小因为数据读取时频繁i/o导致读性能降低。
• innodb buffer pool设置很大时（大于数据库的所有数据大小总和），最终数据会被全部加载到innodb buffer pool缓存中（所有数据都被select读取过）。

docker limit MySQL内存是否会导致崩溃

通过 协助项目 实验结论：docker limit MySQL内存时，如果MySQL使用内存超出limitation则会导致崩溃。

实验如下：

• docker compose内存limit设置为256m，协助项目中docker-compose.yaml配置如下：

  deploy:
    resources:
      limits:
        memory: 256m

• 使用下面命令观察MySQL内存使用率

  htop -p `ps aux|grep mysqld | grep -v "grep" | awk '{print $2}'`

• 运行MemoryUsageJmhTests测试

• 等待一会后，MySQL实例崩溃