如何使mysql MEMORY ENGINE存储更多数据? [英] How to make the mysql MEMORY ENGINE store more data?
问题描述
我想将一个表从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 和
This will get around any imposed limits by tmp_table_size and 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;
如果您正在使用上述检查变量
If you are checking the above variables with
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存储更多数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!