如何让mysql MEMORY ENGINE存储更多数据?

问题描述

我想将一个表从 INNODB 更改为 MEMORY ENGINE.

I want to alter a table from INNODB to MEMORY ENGINE.

所以我输入了这个命令:

So I typed this command:

alter table sns ENGINE=MEMORY;

然后MySQL显示

ERROR 1114 (HY000): The table '#sql-738_19' is full

表的数据大小是 1GB，我有 8GB 内存.

The data size for the table is 1GB, and I have 8GB Memory.

我查看了 my.cnf，但没有找到更改 max_size 设置的位置.难道我不能存储更多数据吗?

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?

推荐答案

你应该调整你制作和加载表格的方式

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;

这将绕过 tmp_table_size 和 max_heap_table_size.

同样的，你需要做两件事:

Just the same, you need to do two things:

将此添加到/etc/my.cnf

Add this to /etc/my.cnf

[mysqld]
tmp_table_size=2G
max_heap_table_size=2G

这将涵盖 mysql 重启.要立即在 mysqld 中设置这些值而不重新启动，请运行:

this will cover mysql restarts. To set these values in mysqld right now without restarting run this:

SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 2;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 2;

如果您使用

SELECT @@max_heap_table_size;

或

SHOW VARIABLES LIKE 'max_heap_table_size';

您可能会注意到，在 SET GLOBAL... 语句之后，它们似乎没有改变.这是因为这些设置仅适用于到服务器的新连接.建立新连接，您将看到值更新，或者您可以通过运行以下命令在会话中更改它:

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 tmp_table_size = 1024 * 1024 * 1024 * 2;
SET max_heap_table_size = 1024 * 1024 * 1024 * 2;

这篇关于如何让mysql MEMORY ENGINE存储更多数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！