2

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?

Improve this question

2 Answers 2

Reset to default
0

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).

Improve this answer
9
  • Thanks for the tip with the queries. I will take a look at this. And yes the server has indeed more than 700 gigabytes of ram. 755 to be precise. As I wrote the server uses Percona 5.7.18-15. Or is there any other version number that I don't know how to find out?
    – elmo
    Commented Jan 12, 2018 at 13:56
  • unfortunately I can't provide the create table due to company policies. But I can say that it contains a longblob field which is used to save data up to 100,000 bytes. Table status and processlist don't show anything unusual.
    – elmo
    Commented Jan 12, 2018 at 14:19
  • OK, the settings are probably reasonable. Suggest adding innodb_buffer_pool_instances = 16 (though it won't help with the Question).
    – Rick James
    Commented Jan 12, 2018 at 14:36
  • Can you provide SHOW TABLE STATUS for the one table? Does it shrink from 1.2T to 50G when doing the OPTIMIZE?
    – Rick James
    Commented Jan 12, 2018 at 14:37
  • 'large_table', 'InnoDB', '10', 'Dynamic', '19571', '2899028', '56736890880', '0', '3178496', '4194304', '35332', '2018-01-07 17:15:08', '2018-01-12 15:42:21', NULL, 'utf8_general_ci', NULL, '', '' Yes the table shrinks to the normal size if we use OPTIMIZE TABLE it just takes very long.
    – elmo
    Commented Jan 12, 2018 at 14:45
0

We solved this by setting

innodb_purge_threads = 32
innodb_max_purge_lag = 2000000

The database probably wrote data faster than the few default purge threads were able to clean it up.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.