主主複製的mysql從庫 記憶體表The table 'pvlogs' is full問題處理

賀子_DBA時代發表於2017-02-14
MySql記憶體
現在有一套主主複製的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;

從庫正常了，不再報錯，開始正常插入資料庫。。。。。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2133527/，如需轉載，請註明出處，否則將追究法律責任。

相關文章