MySQL – Managing Extreme Growth of InnoDB .ibd Files

innodbMySQLpercona

We have a database with a few larger tables were one (yes only one) table grows extremely. It contains only 50 gigabytes of data while the .ibd file (we use innodb_file_per_table) takes up about 1.2 terabytes of space after only one week.
There are many many update querys run against the table which this file belongs to so the problem is probably InnoDB saving all the undo states (or whatever this is called).

The server runs on Percona Server 5.7.18

The server settings that we use:

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

I know that we could use OPTIMIZE TABLE or pt_online_schema_change to get the used space back. But doing this once a week is not really an option on a production database which is essential for the company.

Are there any settings that I don't know of or missed that will prevent the .ibd file from blowing up this fast?

Best Answer

Study your queries. It could be that you accidentally have a "cross join" because you left off the ON clause. This could lead to a huge temp table that, in certain circumstances, could bloat the .ibd file.

What version of Percona?

How much RAM do you have?? innodb_buffer_pool_size = 650GB implies that you have at least 700GB! Otherwise, you will swap to death. (This won't explain the .ibd issue, but it is a serious one.)

These are also rather high unless you have a lot of RAM:

thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000

Please provide SHOW CREATE TABLE for that one table, and SHOW TABLE STATUS for it. BLOBs and TEXTs are of interest; other things may be of note.

A query would take a long time to bloat the table. Glance at SHOW FULL PROCESSLIST; you might spot it. And/or, turn on the slowlog, and check it later.

A query (added from comment)

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

To improve the performance of that query, you need INDEX(other_table_id, chunk_index).

Related Question