MySQL8.0内存分配
1.1计算MySQL在负载高峰时占用的总内存
mysql> select ( @@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@binlog_cache_size+ @@max_connections
*( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size +
@@join_buffer_size + @@thread_stack + @@tmp_table_size ) ) / (1024 * 1024 * 1024) as max_memory_gb;
[root@node1 ~]# mysql -uroot -pP@ssw0rd -S /data/mysql/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@db 17:15: [(none)]> select ( @@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@binlog_cache_size+ @@max_connections
-> *( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size +
-> @@join_buffer_size + @@thread_stack + @@tmp_table_size ) ) / (1024 * 1024 * 1024) as max_memory_gb;
+---------------+
| max_memory_gb |
+---------------+
| 21.4951 |
+---------------+
1 row in set (0.00 sec)
在实际工作中，这里算出来的数值通常偏大，因为所有的线程都同时用到设定内存分配的最大值的情况几乎不会出现，每个线程如果只是处理简单的工作，大约只需要 256KB 的内存。通过查询 sys.memory_global_total 视图可以得到当前 MySQL 实例使用内存总和。
root@db 17:15: [(none)]> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 4.38 GiB |
+-----------------+
1 row in set (0.00 sec)
1.2系统参数 key_buffer_size
系统参数 key_buffer_size 从字面上理解是指定索引缓存的大小，需要注意的是它只对 MyISAM 表起作用，对 InnoDB表无效。这个参数在字面上并没有明确加上 MyISAM，是因为它是在MyISAM 作为 MySQL 默认存储引擎的时代产生的。由于现在通常用的是 InnoDB 表，因此通常不需要调整这个参数。
1.3参数 innodb_buffer_pool_size
MySQL 的默认配置是针对内存为512MB 的虚拟机设计的，innodb_buffer_pool_size 默认值是 128MB，这个值在生产中通常都太小。
当一台服务器被一个 MySQL 实例独占时，通常 innodb_buffer_pool_size 可以设置为内存的 70%左右。
如果在同一台服务器上还有其他的 MySQL 或别的应用，设置 innodb_buffer_pool_size 的大小就要考虑更多因素，一个重要的因素是 InnoDB 的总数据量(包括表和索引)。
InnoDB 的总数据量(包括表和索引)
SELECT count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') num_row,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size FROM information_schema.TABLES WHERE engine='InnoDB';
root@db 17:16: [(none)]> SELECT count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') num_row,
-> concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size FROM information_schema.TABLES WHERE engine='InnoDB';
+--------+---------+-------+-------+------------+
| TABLES | num_row | DATA | idx | total_size |
+--------+---------+-------+-------+------------+
| 51 | 0.99M | 0.21G | 0.02G | 0.23G |
+--------+---------+-------+-------+------------+
1 row in set (0.13 sec)
把参数 innodb_buffer_pool_size 设置成超过 InnoDB 的总数据量是没有意义的，通常设置到能容纳 InnoDB 的活跃数据就够了。
1.4InnoDB缓存池的命中率
两个MySQL的状态参数可以计算出它的命中率：
（1） Innodb_buffer_pool_read_requests：表示向InnoDB缓存池进行逻辑读的次数。
（2） Innodb_buffer_pool_reads：表示从物理磁盘中读取数据的次数。
InnoDB缓存池的命中率的计算公式如下：
InnoDB缓存池的命中率=（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads） / Innodb_buffer_pool_read_requests * 100%。
InnoDB缓存池的命中率的计算例子
root@db 17:21: [(none)]> show status like 'Innodb_buffer_pool_read%s';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 18289 |
| Innodb_buffer_pool_reads | 1220 |
+----------------------------------+-------+
2 rows in set (0.00 sec)
root@db 17:24: [(none)]> select (18289-1220)/18289*100 'InnoDB buffer pool hit';
+------------------------+
| InnoDB buffer pool hit |
+------------------------+
| 93.3293 |
+------------------------+
1 row in set (0.00 sec)
1.5状态参数Innodb_buffer_pool_reads
代表MySQL不能从InnoDB缓存池读到需要的数据而不得不从硬盘中进行读的次数，使用下面的命令查询MySQL每秒从磁盘读的次数：
[root@node1 ~]# mysqladmin -uroot -pP@ssw0rd -S /data/mysql/tmp/mysql.sock extended-status -ri1 | grep Innodb_buffer_pool_reads
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_reads | 1220
| Innodb_buffer_pool_reads | 0
把这个值和硬盘的I/O能力进行对比，如果接近了硬盘处理I/O的上限，那么从操作系统层查看到的CPU用于等待I/O的时间（IOwait，例如vmstat中的cpu的wa或iostat中的%iowait）会变长，这时硬盘I/O就成了性能的瓶颈，增大InnoDB缓存池可能会减少MySQL访问硬盘的次数，提高数据库的性能。
对设置InnoDB缓存大小的考虑
太小的缓冲池可能会导致数据页被频繁地从磁盘读取到内存，引起性能下降。
但如果设置得过大，又可能会造成内存被交换到位于硬盘的内存交换分区，引起性能急剧下降。这两种情况比较起来，把InnoDB缓存池设置小一些对性能的负面影响并不特别大。实际生产中， mysqld进程崩溃的一个常见原因是操作系统的内存耗尽，操作系统被迫把mysqld进程杀死。
设置InnoDB缓存大小
早期调整innodb_buffer_pool_size需要重新启动MySQL，从MySQL 5.7后，这个参数可以动态地进行调整，例如下面的命令把这个参数设置成256MB：
mysql> set persist innodb_buffer_pool_size =256*1024*1024;
set global innodb_buffer_pool_size =512*1024*1024;
innodb_buffer_pool_instances系统参数
一个和相关innodb_buffer_pool_size的参数是innodb_buffer_pool_instances，它设定把
InnoDB缓存池分成几个区，当innodb_buffer_pool_size大于1GB时，这个参数才会起作用，对于大的InnoDB缓存池，建议把它设置得大一些，这样可以减少获取访问InnoDB缓存池时需要上锁的粒度，以提高并发度。
