主主复制的mysql从库内存表Thetable'pvlogs'isfull问题处理

现在有一套主主复制的MySQL数据库,其中有个表pvlogs是member引擎的内存表,主库(就是vip所在的那个库)一切正常,但是从库报错:The table 'pvlogs' is full,
经过询问这个问题已经持续好长时间了,我们这个表是每天都要先把数据insert 进另一个表,然后truncate掉。每天都是到111848这个数量就会报错:The table 'pvlogs' is full。
立马想到了控制内存表大小的两个参数:
tmp_table_size = 671088640
max_heap_table_size = 671088640
在主从库查看得知设置是一样的,如下所示:
主库查看:
MariaDB [log]> show VARIABLES like '%max_heap_table_size%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 2271087616 |
+---------------------+------------+
1 row in set (0.00 sec)

MariaDB [log]> show VARIABLES like '%tmp_table_size%';
+----------------+-----------+
| Variable_name | Value |
+----------------+-----------+
| tmp_table_size | 527108864 |
+----------------+-----------+
1 row in set (0.00 sec)

从库查看:
MariaDB [log]> show VARIABLES like '%max_heap_table_size%';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 2271087616 |
+---------------------+------------+
1 row in set (0.00 sec)

MariaDB [log]> show VARIABLES like '%tmp_table_size%';
+----------------+-----------+
| Variable_name | Value |
+----------------+-----------+
| tmp_table_size | 527108864 |
+----------------+-----------+
1 row in set (0.00 sec)

很显然不是这两个参数导致的,还想到了MAX_ROWS=1000000000,表的属性,经查看两边还是一样的,靠,这就蛋疼了,如下所示:
主库:
MariaDB [log]> show create table pvlogs;

| Table | Create Table
| pvlogs | CREATE TABLE `pvlogs` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`member_id` int(11) DEFAULT NULL,
`jsession` bigint(20) DEFAULT NULL,
`ip` bigint(20) DEFAULT NULL,
`search_id` bigint(20) DEFAULT NULL,
`info_id` bigint(20) DEFAULT NULL,
`lastmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`disc` int(11) NOT NULL,
`status` int(11) NOT NULL DEFAULT '0' COMMENT 'When the page(html) is open ,this attribute will set 1',
PRIMARY KEY (`id`),
KEY `info_id` (`info_id`),
KEY `member_id` (`member_id`),
KEY `ip` (`ip`)
) ENGINE=MEMORY AUTO_INCREMENT=831382377522705486 DEFAULT CHARSET=utf8 MAX_ROWS=2000000000|

1 row in set (0.00 sec)

从库:
MariaDB [log]> show create table pvlogs;
| Table | Create Table
| pvlogs | CREATE TABLE `pvlogs` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`member_id` int(11) DEFAULT NULL,
`jsession` int(11) DEFAULT NULL,
`ip` bigint(20) DEFAULT NULL,
`search_id` bigint(20) DEFAULT NULL,
`info_id` bigint(20) DEFAULT NULL,
`lastmodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`disc` int(11) NOT NULL,
`status` int(11) NOT NULL DEFAULT '0' COMMENT 'When the page(html) is open ,this attribute will set 1',
PRIMARY KEY (`id`),
KEY `info_id` (`info_id`),
KEY `member_id` (`member_id`),
KEY `ip` (`ip`)
) ENGINE=MEMORY AUTO_INCREMENT=223696 DEFAULT CHARSET=utf8 MAX_ROWS=2000000000 |
+--------+--------------------------------------------------------------------------------------

细心的朋友可能已经发现,这两边的创建表语句中的参数AUTO_INCREMENT是不一样的,我们知道这个参数代表自增的当前值加上自增步长(auto_increment_increment控制列中的值的增量值,也就是步长),这个是某个表特有的属性,会随着自增列id的增大而增大。

查看从库总数据量:
MariaDB [log]> select count(*) from pvlogs;
+----------+
| count(*) |
+----------+
| 111848 |
+----------+
1 row in set (0.00 sec)

于是我尝试着改成一样,在改之前,我想到了另一个问题,就是主主复制的结构中,自增列
多主自增长ID重复
  • 假如我们在AB都建立一张test表,表中有一个auto increment的字段
  • 停掉A的同步,在B上对数据表test(存在自增长ID)执行插入操作,返回插入ID为1
  • 然后停掉B的同步,在A上对数据表test(存在自增长ID)执行插入操作,返回的插入ID也是1
  • 然后同时启动A,B,就会出现主键ID重复
解决方法:
我们只要保证两台服务器上插入的自增长数据不同就可以了
如:A插入奇数ID,B插入偶数ID,当然如果服务器多的话,你可以定义算法,只要不同就可以了
在这里我们在A,B上加入参数,以实现奇偶插入
A:my.cnf上加入参数
  1. auto_increment_increment=2  
  2. auto_increment_offset=1  
这样A的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID了
B:my.cnf上加入参数
  1. auto_increment_increment=2  
  2. auto_increment_offset=2  
一定注意AUTO_INCREMENT可不是初始值,初始值是auto_increment_offset系统参数控制,

于是我修改从库的pvlogs表的AUTO_INCREMENT大小:改成了一个比目前大的一个值。
MariaDB [log]> alter table pvlogs AUTO_INCREMENT=831331632;

从库正常了,不再报错,开始正常插入数据库。。。。。

文章标题:主主复制的mysql从库内存表Thetable'pvlogs'isfull问题处理
本文URL:http://scyanting.com/article/gedojh.html