MySQL memory 引擎 table is full 处理

瑙ｅ喅mysql鐨勫唴瀛樿〃鈥渢able is full鈥濋敊璇?

101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table 鈥榯est_1291870945841162鈥?is full 101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table 鈥榯est_1291870945841162鈥?is full 101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table 鈥榯est_1291870945841162鈥?is full 101209 13:13:32 [ERROR] /usr/local/mysql/bin/mysqld: The table 鈥榯est_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, 鈥淪erver System Variables鈥?

浜庢槸灏变慨鏀筂ysql鐨勯厤缃枃浠?etc/my.cnf锛屽湪[mysqld]涓嬫坊鍔?淇敼涓よ锛? tmp_table_size = 256M max_heap_table_size = 256M

绯荤粺榛樿鏄?6M锛屽埆蹇樿閲嶆柊鍚姩mysql锛屼綘涔熷彲浠ュ湪绾垮姩鎬佷慨鏀硅鍙傛暟锛岀粡杩囪繖鍑犲ぉ鐨勮瀵燂紝杩欎釜涓栫晫瀹夐潤浜嗚澶氥€?

閫氳繃淇敼 memory table MAX_ROWS 涔熷彲浠ャ€? ------------------------------------------- 锛坢emory锛塇EAP琛ㄦ槸璁块棶鏁版嵁閫熷害鏈€蹇殑MySQL琛紝浠栦娇鐢ㄤ繚瀛樺湪鍐呭瓨涓殑鏁ｅ垪绱㈠紩銆備絾濡傛灉MySQL鎴栬€呮湇鍔″櫒閲嶆柊鍚姩锛岃〃涓暟鎹皢浼氫涪澶? 鐢ㄦ硶锛氬璁哄潧鐨勫湪绾夸汉鏁扮粺璁★紝杩欑琛ㄧ殑鏁版嵁搴旇鏄棤鍏崇揣瑕佺殑,灏卞嚑涓畝鍗曠殑瀛楁,鏁版嵁涔熶笉澶?璁板綍鏁版€庝箞涔熶笉浼氳秴杩?000鍚?浣嗘槸鎿嶄綔鏄渶棰戠箒鐨?鍩烘湰鐢ㄦ埛鐨勬瘡娆″姩浣滈兘瑕佹洿鏂拌繖涓〃).

鍒涘缓鍐呭瓨琛ㄩ潪甯哥殑绠€鍗曪紝鍙渶娉ㄦ槑 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璁惧畾鐨勫閲忓ぇ灏忔椂锛宮ysql浼氳浆鎹㈣秴鍑虹殑鏁版嵁瀛樺偍鍒扮鐩樹笂锛屽洜姝よ繖鏄€ц兘灏卞ぇ鎵撴姌鎵ｄ簡锛?鎵€浠ユ垜浠繕闇€瑕佹牴鎹垜浠殑瀹為檯鎯呭喌璋冩暣max_heap_table_size锛? 渚嬪鍦?cnf鏂囦欢涓璠mysqld]鐨勪笅闈㈠姞鍏ワ細 max_heap_table_size = 2048M 鍙﹀鍦ㄥ缓琛ㄨ鍙ヤ腑杩樺彲浠ラ€氳繃MAX_ROWS鏉ユ帶鍒惰〃鐨勮褰曟暟銆? 鍐呭瓨琛ㄤ娇鐢ㄥ搱甯屾暎鍒楃储寮曟妸鏁版嵁淇濆瓨鍦ㄥ唴瀛樹腑锛屽洜姝ゅ叿鏈夋瀬蹇殑閫熷害锛岄€傚悎缂撳瓨涓皬鍨嬫暟鎹簱锛屼絾鏄娇鐢ㄤ笂鍙楀埌涓€浜涢檺鍒躲€? 1銆乭eap瀵规墍鏈夌敤鎴风殑杩炴帴鏄彲瑙佺殑锛岃繖浣垮緱瀹冮潪甯搁€傚悎鍋氱紦瀛樸€? 2銆佷粎閫傚悎浣跨敤鐨勫満鍚堛€?heap涓嶅厑璁镐娇鐢▁xxTEXT鍜寈xxBLOB鏁版嵁绫诲瀷锛涘彧鍏佽浣跨敤=鍜?amp;lt;=>鎿嶄綔绗︽潵鎼滅储璁板綍 锛堜笉鍏佽<銆?amp;gt;銆?amp;lt;=鎴?amp;gt;=锛夛紱涓嶆敮鎸乤uto_increment锛涘彧鍏佽瀵归潪绌烘暟鎹垪杩涜 绱㈠紩锛坣ot null锛夈€? 娉細鎿嶄綔绗?鈥?amp;lt;=>鈥?璇存槑锛歂ULL-safe equal.杩欎釜鎿嶄綔绗﹀拰鈥?鈥濇搷浣滅鎵ц鐩稿悓鐨勬瘮杈冩搷浣滐紝涓嶈繃鍦ㄤ袱涓搷浣滅爜鍧囦负NULL鏃讹紝鍏舵墍寰楀€间负1鑰屼笉涓篘ULL锛岃€屽綋涓€涓搷浣滅爜涓篘ULL鏃讹紝鍏舵墍寰楀€间负0鑰屼笉涓篘ULL銆? 3銆佷竴鏃︽湇鍔″櫒閲嶅惎锛屾墍鏈塰eap琛ㄦ暟鎹涪澶憋紝浣嗘槸heap琛ㄧ粨鏋勪粛鐒跺瓨鍦紝鍥犱负heap琛ㄧ粨鏋勬槸瀛樻斁鍦ㄥ疄闄呮暟鎹簱璺緞涓嬬殑锛屼笉浼氳嚜鍔ㄥ垹闄ゃ€傞噸鍚箣鍚庯紝heap灏嗚娓呯┖锛岃繖鏃跺€欏heap鐨勬煡璇㈢粨鏋滈兘鏄┖鐨勩€? 4銆佸鏋渉eap鏄鍒剁殑鏌愭暟鎹〃锛屽垯澶嶅埗涔嬪悗鎵€鏈変富閿€佺储寮曘€佽嚜澧炵瓑鏍煎紡灏嗕笉澶嶅瓨鍦紝闇€瑕侀噸鏂版坊鍔犱富閿拰绱㈠紩锛屽鏋滈渶瑕佺殑璇濄€?

memory 閲嶅惎娉ㄦ剰浜嬮」锛? 5銆佸浜庨噸鍚€犳垚鐨勬暟鎹涪澶憋紝鏈変互涓嬬殑瑙ｅ喅鍔炴硶锛? a銆?鍦ㄤ换浣曟煡璇箣鍓嶏紝鎵ц涓€娆＄畝鍗曠殑鏌ヨ锛屽垽鏂環eap琛ㄦ槸鍚﹀瓨鍦ㄦ暟鎹紝濡傛灉涓嶅瓨鍦紝鍒欐妸鏁版嵁閲嶆柊鍐欏叆锛屾垨鑰匘ROP琛ㄩ噸鏂板鍒舵煇寮犺〃銆傝繖闇€瑕佸鍋氫竴娆℃煡璇€備笉杩囧彲浠ュ啓鎴恑nclude鏂囦欢锛屽湪闇€瑕佺敤璇eap琛ㄧ殑椤甸潰闅忔椂璋冪敤锛屾瘮杈冩柟渚裤€? b銆佸浜庨渶瑕佽heap琛ㄧ殑椤甸潰锛屽湪璇ラ〉闈㈢涓€娆′笖浠呭湪绗竴娆℃煡璇㈣琛ㄦ椂锛屽鏁版嵁闆嗙粨鏋滆繘琛屽垽鏂紝濡傛灉缁撴灉涓虹┖锛屽垯闇€瑕侀噸鏂板啓鍏ユ暟鎹€傝繖鏍峰彲浠ヨ妭鐪佷竴娆℃煡璇€? c銆佹洿濂界殑鍔炴硶鏄湪mysql姣忔閲嶆柊鍚姩鏃惰嚜鍔ㄥ啓鍏ユ暟鎹埌heap锛屼絾鏄渶瑕侀厤缃湇鍔″櫒锛岃繃绋嬫瘮杈冨鏉傦紝閫氱敤鎬у彈鍒伴檺鍒躲€? 6銆佷竴浜涢鏈熷彲鑳界敤鍒扮殑sql璇彞 //濡傛灉琛ㄥ瓨鍦紝鍒欏垹闄? DROP TABLE IF EXISTS `abc`; //澶嶅埗鏁村紶琛▁yz涓篽eap琛╝bc锛堝寘鍚墍鏈夋暟鎹級 CREATE TABLE `abc` type=heap select * from `xyz`; //娣诲姞涓婚敭id ALTER TABLE `abc` ADD PRIMARY KEY (`id`); //娣诲姞绱㈠紩username ALTER TABLE `abc` ADD INDEX `abc` (`username`); 鍏跺畠鍙傝€冿細 瀹樻柟鏂囨。锛欰s 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. 鍙互鐪嬪嚭鏉EMORY纭疄鏄痸ery fast锛宎nd very useful for creating temporary tables .鎶婁复鏃惰〃鍜屽唴瀛樿〃鏀惧湪涓€璧蜂娇鐢ㄧ‘瀹炰細蹇笉灏戯細create table tmp2(id int not null) engine memory; 鍐呭瓨琛ㄧ殑寤虹珛杩樻湁涓€浜涢檺鍒舵潯浠讹細 MEMORY tables cannot contain BLOB or TEXT columns. HEAP涓嶆敮鎸丅LOB/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鎴朌ROP 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閰嶇疆鏂囦欢锛屼娇寰梞ysql閲嶆柊鍚姩鏃跺彉鍔ㄨ兘澶熸寔缁敓鏁堛€? 3. 鏈€鍚庯紝浣犻渶瑕侀噸鏂拌繛涓奙YSQL锛岄噸鏂板垱寤虹浉鍏崇殑鍐呭瓨琛ㄣ€? 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%';