该表已满(带有MEMORY引擎) [英] The table is full (with MEMORY engine)

问题描述

我想将生产数据库传输到我的开发机器上进行测试.

I want to transfer a production database to my development machine for tests.

出于性能目的，它具有6个带有MEMORY引擎的表.

It has 6 tables with MEMORY engine for performance purposes.

我做了mysqldump --routines -hxxx -uxxx -pxxx prod_database > prod_dump.sql.当我在同一台生产服务器上进行mysql -hxxx -uxxx -pxx prod_clone_database < prod_dump 时(因为我尚未购买UAT Mysql虚拟服务器，所以我有了UAT的克隆)，可以正常工作.

I did mysqldump --routines -hxxx -uxxx -pxxx prod_database > prod_dump.sql. When I did mysql -hxxx -uxxx -pxx prod_clone_database < prod_dump on the same production server (so I had a clone for UAT, because we haven't yet bought a UAT Mysql virtual server) that works OK.

当我执行相同的操作mysql -uroot -proot prod_clone < prod_dump.sql时，出现错误ERROR 1114 (HY000) at line 138735: The table 'sezione_a' is full

When I do that same mysql -uroot -proot prod_clone < prod_dump.sql I get an error ERROR 1114 (HY000) at line 138735: The table 'sezione_a' is full

生产服务器运行具有1GB RAM的Linux，并且仅运行mysqld守护程序. 我的笔记本电脑有16GB的RAM.为什么我会得到一个表已满的错误?我该如何避免呢?由于该表中的数据与测试无关，我可以从转储中排除其DML吗?我仍然需要DDL

Production server runs Linux with 1GB of RAM, and runs only mysqld daemon. My laptop has 16GB of RAM. Why the heck do I get a table full error? How do I avoid that? Since data in that table is not relevant for tests, can I exclude its DML from the dump? I need the DDL anyways

推荐答案

在MySQL中，默认情况下，使用内存引擎创建的临时表可以迅速增长到 max-heap-table-size 和 tmp-table-size ，因为每行分配的内存比通常需要的更多.例如，如果每行需要16kb，则只需要1k行就可以达到限制.对于许多应用程序，可以通过使用 ROW_FORMAT = DYNAMIC 解决此问题，如下所述:

In MySQL, by default, the temp tables created with the memory engine can quickly grow beyond the 16mb limit of max-heap-table-size and tmp-table-size because more memory is allocated per row than is usually required. For example, if each row requires 16kb, then it only takes 1k rows to reach the limit. For many applications, this issue can be addressed by using ROW_FORMAT=DYNAMIC as explained here:

http://www.percona.com/doc/percona-server/5.5/flexibility/improved_memory_engine.html

这篇关于该表已满(带有MEMORY引擎)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！