MySQL 如何让mysql MEMORY ENGINE存储更多数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9842720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make the mysql MEMORY ENGINE store more data?
提问by Bing Hsu
I want to alter a table from INNODB to MEMORY ENGINE.
我想将表从 INNODB 更改为 MEMORY ENGINE。
So I typed this command:
所以我输入了这个命令：
alter table sns ENGINE=MEMORY;
alter table sns ENGINE=MEMORY;
Then the MySQL shows
然后MySQL显示
ERROR 1114 (HY000): The table '#sql-738_19' is full
The data size for the table is 1GB, and I have 8GB Memory.
表的数据大小是 1GB，我有 8GB 内存。
I checked my.cnf, and I didn't find where to change the max_size setting. Shouldn't I be able to store more data?
我检查了 my.cnf，我没有找到更改 max_size 设置的位置。我不应该能够存储更多数据吗？
回答by RolandoMySQLDBA
You should adjust the way you make and load the table
您应该调整制作和加载表格的方式
CREATE TABLE sns_memory SELECT * FROM sns WHERE 1=2;
ALTER TABLE sns_memory ENGINE=MEMORY;
INSERT INTO sns_memory SELECT * FROM sns;
DROP TABLE sns;
ALTER TABLE sns_memory RENAME sns;
This will get around any imposed limits by tmp_table_sizeand max_heap_table_size.
这将绕过tmp_table_size和max_heap_table_size强加的任何限制。
Just the same, you need to do two things:
同样，你需要做两件事：
Add this to /etc/my.cnf
将此添加到 /etc/my.cnf
[mysqld]
tmp_table_size=2G
max_heap_table_size=2G
this will cover mysql restarts. To set these values in mysqld right now without restarting run this:
这将涵盖 mysql 重启。要立即在 mysqld 中设置这些值而不重新启动，请运行以下命令：
SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 2;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 2;
If you are checking the above variables with
如果您正在检查上述变量
SELECT @@max_heap_table_size;
or
或者
SHOW VARIABLES LIKE 'max_heap_table_size';
you may notice that they don't seem to change following the SET GLOBAL...statements. This is because the settings only apply to new connections to the server. Make a new connection, and you'll see the values update or you could change it within your session by running:
您可能会注意到它们似乎并没有随着这些SET GLOBAL...陈述而改变。这是因为这些设置仅适用于到服务器的新连接。建立新连接，您将看到值更新，或者您可以通过运行以下命令在会话中更改它：
SET tmp_table_size = 1024 * 1024 * 1024 * 2;
SET max_heap_table_size = 1024 * 1024 * 1024 * 2;
回答by Cez
max_heap_table_sizeis what you are looking for
max_heap_table_size就是你要找的
回答by Karoly Horvath
Increase max_heap_table_size.
增加max_heap_table_size。
回答by Tom Donnelly
If you're still having a problem, remember that the disk space a table occupies is often less than the memory requirement. Using VARCHAR (256) with a string of length 8 will consume 8 bytes on disk but because STORAGE doesn't support dynamic rows, it reserves the full 256 bytes in memory for every instance.
如果仍有问题，请记住表占用的磁盘空间通常小于内存要求。使用带有长度为 8 的字符串的 VARCHAR (256) 将在磁盘上消耗 8 个字节，但由于 STORAGE 不支持动态行，因此它在内存中为每个实例保留了完整的 256 个字节。
回答by thephper
The max size for the memory table is set on creation and altering and based on the max_heap_table_size value.
内存表的最大大小在创建和更改时设置，并基于 max_heap_table_size 值。
So when you want to raise the max size for an existingmemory table you can change the max_heap_table_size and then apply it by altering the table to the same storage engine.
因此，当您想提高现有内存表的最大大小时，您可以更改 max_heap_table_size，然后通过将表更改为相同的存储引擎来应用它。
Example:
例子：
# Raise max size to 4GB
SET max_heap_table_size = 1024 * 1024 * 1024 * 4;
# If already a memory table, the alter will not change anything.
# Only apply the new max size.
ALTER TABLE table_name ENGINE=MEMORY;
Misunderstanding regarding tmp_table_size
关于 tmp_table_size 的误解
The tmp_table_size variable only determines the max size for internalmemory tables. Not user-defined.
tmp_table_size 变量仅确定内部存储器表的最大大小。不是用户定义的。
As stated in the MySQL docs:
正如MySQL 文档中所述：
The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
内部内存临时表的最大大小。此变量不适用于用户创建的 MEMORY 表。
