9.Mysql是怎样使用内存的？

1.How Mysql user memory?

　　Mysql分配buffer和cache去提高数据库操作的性能，默认的配置允许mysql可以在内存为512M的虚拟机上启动，你可以通过增加某些缓存和某些缓冲区相关系统变量值来提高mysql的性能，你也可以修改默认配置可以在内存有限的操作系统上运行mysql。

　　下面描述了mysql使用内存的一些方法：

innodb buffer pool 是一个内存区域，它主要用来保存表、索引、和其它缓存辅助缓冲区，为了提高大容量读取操作的效率，缓冲池被划分为可能容纳多行的页面。为了缓存管理的效率，缓冲池被implemented为页链表形式。data that is rarely used is aged out of the cache,using a variation of the LRU algorithm

　　缓冲池的大小系统的性能很重要

- innodb在服务器启动时为整个缓冲池分配内存，使用malloc()方法操作，innodb_buffer_pool_size 系统变量定义了缓冲池的大小.通常，建议innodb_buffer_pool_size的大小系统的50%~75%.innodb_buffer_pool_size大小可以在server运行时被动态配置。
- 当系统有大量的内存时，可以通过将缓冲池划分为多个buffer pool instance用来提高并发性。innodb_buffer_pool_instance系统变量定义了buffer pool instances
- a buffer pool that is too small may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.
- buffer pool 太大可能会由于内存竞争导致swapping。
存储引擎接口使优化器能够提供有关记录缓冲区大小的信息，以用于优化器估计可能读取多行的扫描。缓冲区可以根据估计的大小而变化.innodb使用这种可变大小的缓冲功能来可以用行预取，and to reduce the overhead of latching and B-tree.
所有的线程共享MyISAM buffer. the key_buffer_size 系统变量决定了这个值。

　　　对于每一个被打开的MyISAM表，它的索引文件被打开一次，对于访问该表的每一个并发运行的线程，数据文件打开一次。对于每个并发线程，分配一个表结构、每列的列结构和一个大小为3*N的缓冲区。

如果内部临时表变得太大(使用tmp_table_size和max_heap_table_size系统变量确定)，mysql自动将表从内存转到磁盘格式，从mysql 8.0.16开始，磁盘上的临时表始终使用innodb存储引擎，你可以增加允许的临时表大小。
mysql performace Schema是一种用于低级别监视mysql服务器执行的功能。性能模式以增量方法动态分配内存，将其内存使用扩展到实际的服务器负载，而不是在服务器启动期间分配期间分配所需的内存。一旦分配了内存，在服务器启动之前它是不会被释放。
服务器用来管理客户端连接的每个线程都需要一些具体的线程空间，以下列表指示了这些以及哪些系统变量控制它们的大小：
- a stack(thread_stack)
- a connection buffer(net_buffer_length)
- a result buffer(net_buffer_length)

　　连接缓冲区和结果缓冲区都以等于net_buffer_length字节的大小开始，但根据需要动态扩大到max_allowed_packet字节。结果缓冲区在每个sql语句之后缩小到net_buffer_length个字节。在运行语句时，还会分配当前字符串的副本

所有线程共享相同的基本内存
当线程不再被需要时，分配给它的内存被释放并返回给系统，除非线程回到线程缓存中。在这种情况下，内存保持分配状态。
每个对表执行顺序扫描的请求都会被分配一个读取缓冲区，read_buffer_size系统变量确定缓冲区大小。　　
当以任意顺序读取行时(例如，在排序之后)，可能会分配一个随机读取缓冲区以避免磁盘寻道，read_rnd_buffer_size系统变量就是确定这个缓冲区大小
几乎所有的解析和计算都是在线程本地和可重用的内存池中完成的。
Mysql需要用于表缓存的内存和描述符，所有正在使用的表的处理程序结构都保存在表缓存中，并且按照先进先出的方式进行管理。table_open_cache系统变量定义了初始表缓存的大小。
flush tables 或者mysqladmin flush-tables命令会立即关闭所有未使用的表，并在当前执行的线程完成时将所有正在使用的表为关闭。这有效地释放了大多数正在使用的内存。flush tables 在所有表都关闭之前都不会返回。
作为grant、create user 、create server和install plugin语句的结果，服务器将信息缓存在内存中。相应的revoke、drop user、drop server和uninstall plugin语句不会释放此内存，因此对于执行许多导致缓存的语句实例的服务器，缓存内存使用量会增加，除非使用flush 释放它。
对于复制技术，以下设置会影响内存使用，并且也可以调整
- the max_allowed_packet 系统变量在复制源上将会限制源发送到其他副本进行处理的最大message size 大小，此设置默认是64M.
- the max_binlog_cache_size 系统变量将会指定单个事务的内存使用上限。

2.Monitoring Mysql memory

　　大部分的Performance shcema 内存监控功能默认都是被禁用的，instruments可以通过updaing performance schema 的setup_instruments表的enable列进行开启。内存工具的名称格式为memory/code_area/instrument，其中code_area是sql或innodb，intrument_name是工具详细信息。

　　1.要查看可用的mysql内存工具，可以查询performance Schema setup_instruments表。以下查询为所有代码区域返回数百个内存工具。　

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory%';

　　2.也可以通过指定代码区域来缩小结果范围。例如可以通过将innodb执行为代码区域来将结果限制为innodb内存工具。　

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';

　　根据mysql的安装，代码区域可能包括performance_shcema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition等。

　　3.要启动内存工具，要将performance_schema-instrument规则添加到mysql配置文件中。例如，要启用所有内存工具，请将此规则添加到你的配置文件并重新启动服务器：　　　

　　performance-schema-instrument='memory/%=COUNTED'

　　这样的话，在重新启动服务器后，Performance Schema setup_instruments表的enalbed列应该为你启用的内存报告YES.对于内存仪器，setup_instruments表中的timed列被忽略。因为内存操作不是定时的。　　　

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

　　4.查询内存仪器监控数据，内存仪器数据将会在performance schema中memory_summary_global_by_event_name表给查询出来，里面的数据库将会包括event_name，这个event_name是仪器的名称。下面查询将会返回关于innodb buffer pool的内存数据。　

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 137428992
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
   HIGH_NUMBER_OF_BYTES_USED: 137428992

　　也可以使用sys模式memory_global_by_current_bytes表查询相同的基础数据，该表显示服务器内全局的当前内存使用情况，按分配类型细分。　

mysql> SELECT * FROM sys.memory_global_by_current_bytes
       WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 1
    current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
       high_count: 1
       high_alloc: 131.06 MiB
   high_avg_alloc: 131.06 MiB

　　sys schema 查询按代码区域聚合当前分配的内存(current_alloc):

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, FORMAT_BYTES(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 843.24 MiB    |
| memory/performance_schema | 81.29 MiB     |
| memory/mysys              | 8.20 MiB      |
| memory/sql                | 2.47 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 46.53 KiB     |
| memory/blackhole          | 512 bytes     |
| memory/federated          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+