Mysql

MySQL InnoDB .ibd 文件變得極端

  • March 10, 2022

我們有一個帶有幾個較大表的數據庫,其中一個(是的,只有一個)表增長得非常快。它僅包含 50 GB 的數據,而 .ibd 文件(我們使用 innodb_file_per_table)僅在一周後就佔用了大約 1.2 TB 的空間。

有許多更新查詢針對該文件所屬的表執行,因此問題可能是 InnoDB 保存了所有撤消狀態(或任何所謂的)。

伺服器在 Percona Server 5.7.18 上執行

我們使用的伺服器設置:

innodb_log_files_in_group = 2
innodb_log_file_size = 10G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

binlog_format = MIXED
gtid-mode=ON
enforce_gtid_consistency=ON

log_slave_updates = 1
expire_logs_days = 7
sync_binlog = 1
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 10000
innodb_thread_concurrency = 0
transaction-isolation = READ-COMMITTED
thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000
open_files_limit = 1024000
innodb_lock_wait_timeout = 30
innodb_buffer_pool_size =  650GB
max_heap_table_size = 32M
tmp_table_size = 32M
innodb_log_buffer_size = 64M
query_cache_type = off

我知道我們可以使用 OPTIMIZE TABLE 或 pt_online_schema_change 來取回已用空間。但是,在對公司至關重要的生產數據庫上,每週執行一次並不是真正的選擇。

是否有任何我不知道或錯過的設置會阻止 .ibd 文件如此快速地爆炸?

我們通過設置解決了這個問題

innodb_purge_threads = 32
innodb_max_purge_lag = 2000000

數據庫寫入數據的速度可能比少數預設清除執行緒能夠清理它的速度更快。

研究您的查詢。可能是因為您遺漏了該ON子句,因此您意外地進行了“交叉連接”。這可能會導致一個巨大的臨時表,在某些情況下,可能會使.ibd文件膨脹。

什麼版本的 Percona?

你有多少記憶體??innodb_buffer_pool_size = 650GB意味著你至少有700GB!否則,你將被交換至死。(這不會解釋 .ibd 問題,但它是一個嚴重的問題。)

除非你有很多 RAM,否則這些也相當高:

thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000

請提供SHOW CREATE TABLE那一張桌子,並SHOW TABLE STATUS為它。 BLOBs並且TEXTs感興趣;其他事情可能值得注意。

查詢需要很長時間才能使表膨脹。一覽SHOW FULL PROCESSLIST;你可能會發現它。和/或,打開慢日誌,稍後再檢查。

查詢(從評論中添加)

select  table0_.id as id,
               table0_.created as created,
               table0_.modified as modified,
               table0_.chunk_index as chunk,
               table0_.content as content,
               table0_.other_table_id as export
   from  table table0_
   where  table0_.other_table_id=8305
   order by  table0_.chunk_index ASC

要提高該查詢的性能,您需要INDEX(other_table_id, chunk_index).

引用自:https://dba.stackexchange.com/questions/195265