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?