MySQL 内存表 The table 'pvlogs' is full 问题

今天收到报错: [ERROR] mysqld: The table 'pvlogs' is full, 导致数据无法写入, 影响业务。首先检查系统是否空间满了, 查看之后发现是正常的。经过查询发现, 该表使用的是内存存储引擎, 则可能是由于内存表大小限制所致。

内存存储引擎 (MEMORY)

数据存储: 内存存储引擎的表数据完全存储在内存中, 因此可以快速处理数据。
文件结构: 每个基于 MEMORY 存储引擎的表会在磁盘上有一个对应的文件 (.frm) , 该文件仅存储表的结构, 数据存储在内存中。
索引: 内存存储引擎默认使用哈希 (HASH) 索引, 速度较快, 但也支持 B-Tree 索引。
数据持久性: 由于数据完全在内存中, 若 mysqld 进程崩溃或服务器重启, 这些数据将会丢失。因此, 内存表的生命周期通常较短, 适合一次性使用的场景。

解决方案

调整内存表大小相关参数

查看当前参数

-- 查看内存表大小
MariaDB [log]> show VARIABLES like '%max_heap_table_size%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 671088640 |
+---------------------+-----------+
1 row in set (0.00 sec)

-- 查看临时表大小
MariaDB [log]> show VARIABLES like '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.01 sec)

设置新的参数 (临时生效, 重启后失效)

mysql> SET GLOBAL max_heap_table_size=1048576000;
mysql> SET GLOBAL tmp_table_size=1048576000;

永久生效的配置, 在 MySQL 配置文件 my.cnf 中添加

tmp_table_size = 671088640
max_heap_table_size = 671088640

客户端重新连接数据库。如果问题仍然存在, 考虑重新创建相关的内存表。

参数说明

tmp_table_size: 规定内部内存临时表的最大值, 每个线程分配。如果超出限制, MySQL 将自动转化为基于磁盘的 MyISAM 表, 存储在指定的 tmpdir 目录下 (默认是 /tmp/)。
```
mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ |
+---------------+-------+
```
max_heap_table_size: 定义用户可创建的内存表的最大大小。支持动态改变, 但对已存在的内存表无效, 需通过重新创建或修改表的方式生效。

但是对于已经存在的内存表就没有什么用了, 除非这个表被重新创建 (create table) 或者修改 (alter table) 或者 truncate table。服务重启也会设置已经存在的内存表为全局 max_heap_table_size 的值。这个变量和 tmp_table_size 一起限制了内部内存表的大小。

调整表的最大行数

ALTER TABLE tbl_name MAX_ROWS=1000000000;

这样可以立即生效, 解决表满的问题。

原文

MySQL 内存表The table ‘pvlogs’ is full问题处理