MySQL 临时表已满。

Question

我正在尝试使用以下语法创建和加载一个临时的mySQL表到内存中，但是遇到了“表已满”的错误：

CREATE TEMPORARY TABLE IF NOT EXISTS tmpHistory ENGINE=MEMORY SELECT * FROM history ORDER BY date ASC;

我的原始InnoDB表格大约有3百万行，大小约为300MB。我已将以下两个服务器变量从它们的16MB默认值增加到：

max_heap_table_size = 536870912

tmp_table_size = 536870912

我在AWS r3.xlarge上运行mySQL，这是一个具有30.5GB RAM的4核盒子。

我已经查看了这个SO指南，但仍然遇到“表已满”的错误。我是第一次使用Memory Engine，因此欢迎任何建议。

- Gunnar

Answer 1

max_heap_table_size而不是tmp_table_size控制着任何后续MEMORY表的最大大小。

MEMORY有几个怪癖。也许这个会困扰你：将VARCHARs转换为CHARs。因此，如果VARCHAR(255)是CHARACTER SET utf8，则每行需要765个字节。

为什么要将一个完好无损的InnoDB表复制到MEMORY表中？为了避免磁盘读写？不是...如果innodb_buffer_pool_size足够大，InnoDB表将有效地存在于RAM中。加快速度吗？不一定，因为InnoDB具有行锁，但MEMORY只有表锁。

请提供SHOW CREATE TABLE；可能有其他事情可以反对你正在做的事情和/或解释为什么出现错误。

- Rick James

---

感谢您的评论。我应该提到我的历史表被分区为几年，而这个临时表正在加载其中一个年份以供只读访问。我每天刷新一次这个临时表。我也有点尴尬地承认，我试图从 Sequel Pro 运行此语句，但返回了错误。在命令行中运行成功地在内存中创建了临时表。我对针对这个新的临时内存表进行的查询响应速度的初始测试显示有90%的改进。 - Gunnar

一天只重新加载一次？不如将本应分配给内存的空间分配给缓冲池。 - Rick James

Answer 2

查找使用engine=memory创建的所有表格

select table_name,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
from information_schema.tables 
where table_schema = 'db1' and engine = 'memory';

使用top命令查看哪个进程消耗了多少内存。对于非常大的表，避免使用内存。考虑使用类似Amazon RedShift的列式存储。

- MontyPython

---

我的所有表都被列为InnoDB。 - Gunnar