mySQL 临时表已满

Question

我正在尝试使用以下语法创建临时 mySQL 表并将其加载到内存中，但遇到"表已满"错误：

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

我的原始历史记录InnoDB表大约有3m行和大约300mb。 我已将以下两个服务器变量从其 16mb 默认值中增加：

max_heap_table_size = 536870912

tmp_table_size = 536870912

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

我已经查看了此SO指南，但仍然遇到"表已满"错误。 我是使用内存引擎的新手，因此任何建议都值得赞赏。

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;可能还有其他事情可以反对你正在做的事情和/或解释你为什么得到错误。

Answer 2

找出您使用引擎=内存创建的所有表

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这样的专栏商店。