MySQL memory 引擎 table is full 处理

解决mysql的内存表“table is full”错误

101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table ‘test_1291870945841162′ is full

101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table ‘test_1291870945841162′ is full

101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table ‘test_1291870945841162′ is full

101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table ‘test_1291870945841162′ is full

有这一句话很重要：

You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Section 5.1.3, “Server System Variables”.

于是就修改Mysql的配置文件/etc/my.cnf，在[mysqld]下添加/修改两行：

tmp_table_size = 256M

max_heap_table_size = 256M

系统默认是16M，别忘记重新启动mysql，你也可以在线动态修改该参数，经过这几天的观察，这个世界安静了许多。

通过修改 memory table MAX_ROWS 也可以。

-------------------------------------------

（memory）HEAP表是访问数据速度最快的MySQL表，他使用保存在内存中的散列索引。但如果MySQL或者服务器重新启动，表中数据将会丢失.

用法：如论坛的在线人数统计，这种表的数据应该是无关紧要的,就几个简单的字段,数据也不多,记录数怎么也不会超过1000吧,但是操作是最频繁的(基本用户的每次动作都要更新这个表).

创建内存表非常的简单，只需注明 ENGINE= MEMORY 即可:

(none)@betbrain > show create table betbrain.t_zq_odds_change_152_2\G;

*************************** 1. row ***************************

Table: t_zq_odds_change_152_2

Create Table: CREATE TABLE `t_zq_odds_change_152_2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`bettingofferid` bigint(20) NOT NULL,

`bettingtypeid` int(11) NOT NULL,

`eventid` bigint(20) NOT NULL DEFAULT '0',

`outcomeid` bigint(20) NOT NULL,

`providerid` int(11) NOT NULL,

`odds` float(9,3) NOT NULL,

`status` tinyint(4) NOT NULL DEFAULT '0',

`lastchangedtime` datetime NOT NULL,

`lastchangedtime_ms` bigint(20) DEFAULT '0',

`addtime` datetime DEFAULT NULL,

`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`bettingstatusid` tinyint(4) NOT NULL DEFAULT '0',

`addtime_500` datetime DEFAULT NULL COMMENT '收到数据的时间',

`send_data_start_time_hk` datetime DEFAULT NULL COMMENT '数据文件开始往内地服务器传输的时间点',

`request_start_time_hk` datetime DEFAULT NULL COMMENT '中转服务器请求数据的时间点',

`addtime_hk` datetime DEFAULT NULL COMMENT '香港服务器收到数据的时间点',

`addtime_betbrain` datetime DEFAULT NULL COMMENT 'Betbrain更新数据的时间点',

`file_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '数据文件id',

`slotnum` int(11) NOT NULL DEFAULT '0',

`islive` tinyint(4) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `outcomeid` (`outcomeid`) USING BTREE,

KEY `updatetime` (`updatetime`) USING BTREE,

KEY `eventid` (`eventid`),

KEY `status` (`status`)

)ENGINE=MEMORY AUTO_INCREMENT=3761409 DEFAULT CHARSET=gbk MAX_ROWS=10000000

1 row in set (0.00 sec)

注意：

当内存表中的数据大于max_heap_table_size设定的容量大小时，mysql会转换超出的数据存储到磁盘上，因此这是性能就大打折扣了， 所以我们还需要根据我们的实际情况调整max_heap_table_size，

例如在.cnf文件中[mysqld]的下面加入：

max_heap_table_size = 2048M

另外在建表语句中还可以通过MAX_ROWS来控制表的记录数。

内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制。

1、heap对所有用户的连接是可见的，这使得它非常适合做缓存。

2、仅适合使用的场合。 heap不允许使用xxxTEXT和xxxBLOB数据类型；只允许使用=和<=>操作符来搜索记录 （不允许<、>、<=或>=）；不支持auto_increment；只允许对非空数据列进行 索引（not null）。

注：操作符 “<=>” 说明：NULL-safe equal.这个操作符和“=”操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。

3、一旦服务器重启，所有heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。

4、如果heap是复制的某数据表，则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。

memory 重启注意事项：

5、对于重启造成的数据丢失，有以下的解决办法：

a、 在任何查询之前，执行一次简单的查询，判断heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。

b、对于需要该heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。

c、更好的办法是在mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。

6、一些预期可能用到的sql语句

//如果表存在，则删除

DROP TABLE IF EXISTS `abc`;

//复制整张表xyz为heap表abc（包含所有数据）

CREATE TABLE `abc` type=heap select * from `xyz`;

//添加主键id

ALTER TABLE `abc` ADD PRIMARY KEY (`id`);

//添加索引username

ALTER TABLE `abc` ADD INDEX `abc` (`username`);

其它参考：

官方文档：As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

可以看出来MEMORY确实是very fast，and very useful for creating temporary tables .把临时表和内存表放在一起使用确实会快不少：create table tmp2(id int not null) engine memory;

内存表的建立还有一些限制条件：

MEMORY tables cannot contain BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。

The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.

To free memory used by a MEMORY table when you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP TABLE.为了释放内存，你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

几个关键参数

max_heap_table_size

mysql HEAP MEMORY tables 提高行数支持的方法

别人问到的 记一下

mysql MEMORY tables 如果目前支持的行数到上限还不够用 可以把 my.conf 配置里面

max_heap_table_size = 256M

改大

设置 MAX_ROWS

在跑着 可以 ALTER TABLE tbl_name MAX_ROWS=

MAX_ROWS 依赖于 max_heap_table_size 设置

最终解决方法:

后来GOOGLE得知，需要重建该表才可以。

1. 设置新的参数

mysql> set global max_heap_table_size=1048576000

mysql> set global tmp_table_size=1048576000

2. 修改mysql配置文件，使得mysql重新启动时变动能够持续生效。

3. 最后，你需要重新连上MYSQL，重新创建相关的内存表。

mysql> ALTER TABLE zaojiao_sessions ENGINE MEMORY;

4. 最后，当应用重新连接到mysql数据库时，这些变更就生效了。

-- memory 设置 tmp ,max 参数后, 需要把表重新定义为内存表.

提供批量查询SQL:

查询:

MYSQL > select TABLE_SCHEMA,TABLE_NAME,sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024 as total_mb from information_schema.tables where table_schema='xxxx' and table_name like 'txxxxyyyyyyy%' group by TABLE_SCHEMA,TABLE_NAME having total_mb <65 order by total_mb desc;

批量注册 内存表:

MYSQL > select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' engine memroy ;') from information_schema.tables where table_schema='xxxxx' and table_name like 'yyyyyy%';