怎樣解決ibdata1共享表空間文件一直增加的問(wèn)題

這期內容當中小編將會(huì )給大家帶來(lái)有關(guān)ibdata1共享表空間文件一直增加的，文章內容豐富且以專(zhuān)業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

數據庫磁盤(pán)使用率一直上升，其中ibdata1使用有323G，并且一直增加持續了31天

1、首先看了實(shí)例innodb_file_per_table是開(kāi)啟的

2、5.6版本undo信息

show variables like '%undo%';
1    innodb_undo_directory    .
2    innodb_undo_logs    128
3    innodb_undo_tablespaces    0

那么ibdata1文件增大的原因有如下因素：

InnoDB 引擎表由于支持多版本并發(fā)控制（MVCC），因此會(huì )將查詢(xún)所需的Undo信息保存在系統文件ibdata1 中。

如果存在對一個(gè)InnoDB表長(cháng)時(shí)間不結束的查詢(xún)，而且在查詢(xún)過(guò)程中表有大量的數據變化，

則會(huì )生成大量的Undo信息，導致 ibdata1文件尺寸增加。

由于 內部機制的限制，ibdata1文件目前是不支持收縮的。

因此出現這樣的情況，只能通過(guò)切換主備，或者遷移，再或者增大存儲空間解決。

建議：監控和清理執行時(shí)間過(guò)長(cháng)的會(huì )話(huà)或事務(wù)。

那么今天的例子大致如下：

通過(guò)show processlist 看到一個(gè)sql執行了31天，并且還處于query，Sending data狀態(tài)，SQL本身三個(gè)表關(guān)聯(lián)，沒(méi)有關(guān)聯(lián)條件，后面kill掉此sql，通過(guò)切換實(shí)例，新備重新搭建

1.2 數據文件

對于數據文件占用空間高的情況，可以通過(guò)清理數據的方式來(lái)減少空間占用情況，比如通過(guò) drop table 和 truncate table 來(lái)清理不再需要的數據。

說(shuō)明 3 個(gè)常見(jiàn)問(wèn)題：

1.2.1 information_schema.tables 查詢(xún)的數據容量

information_schema.tables 提供的是根據采樣獲取的表的部分統計信息，因此通過(guò)下面的查詢(xún)獲取的表、庫數據尺寸和實(shí)際數據文件占用尺寸間會(huì )有出入（通常要小于實(shí)際數據文件占用空間）

select table_name, concat(round((data_length + index_length)/1024/1024,2),'MB') from
    information_schema.tables where
    table_schema = 'TESTDB' and table_name = 'TESTTABLE';

下圖中可以看到：在收集表的統計信息前后反饋出的表數據量大小存在差異。

注：即使通過(guò) analyze table 命令，重新收集統計信息，得到的數值通常也小于實(shí)際數據文件占用空間；比如本例的 16143 MB 也小于該表的數據文件實(shí)際占用空間。

由于數據文件在頻繁的 DML 后會(huì )出現數據空洞的現象，比較接近實(shí)際數據文件占用空間的計算方法請參考：

select 
    sum(data_length + index_length + data_free) / 1024 / 1024from
    information_schema.tables;

注：
因為 information_schema.tables 中提供的是采樣統計數據，因此該計算方式在統計數據比較接近實(shí)際的情況下，才會(huì )比較接近真實(shí)空間占用情況。

1.2.2 delete 刪除數據

delete 操作不能夠直接回收被刪除數據占用的數據文件空間，這就好比排空泳池中水但泳池的占地面積不會(huì )發(fā)生改變一樣。而且 delete 操作會(huì )生成相應的 Binlog 文件，會(huì )進(jìn)一步惡化空間使用情況。在 delete 操作刪除數據后，需要通過(guò) optimize table tab_name; 操作來(lái)回收空間。

1.2.3  刪除備份

自建MySQL可能存在備份占用空間的問(wèn)題，但是云上RDS 備份放置在后臺 OSS 上，不占用用戶(hù)的 RDS 實(shí)例空間，因此刪除備份不能解決實(shí)例的空間問(wèn)題。而且刪除備份會(huì )影響實(shí)例的可恢復性，強烈建議任何情況下不要考慮刪除備份。

1.3 臨時(shí)文件

臨時(shí)文件會(huì )隨查詢(xún)的結束或者會(huì )話(huà)的終止而自動(dòng)釋放，因此如果是臨時(shí)文件導致實(shí)例空間滿(mǎn)而鎖定，可以通過(guò)終止會(huì )話(huà)來(lái)釋放空間。

遇見(jiàn)過(guò)一個(gè)案例，客戶(hù)排序操作導致ibtmp1很大，占用空間很高，需要釋放，那么只能重啟，也是切換成備庫，然后重啟新的備釋放掉了

ib_logfile 日志文件：

ib_logfile0 和 ib_logfile1 日志文件保存 InnoDB 引擎表的事務(wù)日志信息，其文件大小尺寸固定，不可以改變。較大的尺寸在高并發(fā)事務(wù)的場(chǎng)景下有利于減少事務(wù)日志文件切換的次數，提高實(shí)例性能。