how to purge / shrink .idb tables

I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables?

I have tried to use optimize table table_name but after the query the .idb tables stays the same size.

This is my configuration at [mysql] in /etc/my.cnf:

[mysqld]
max_allowed_packet=500M
innodb_buffer_pool_size = 15G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit=2
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=134217728
innodb_file_per_table=1

Am i missing something?

To shrink an .ibd file it's enough to run ALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE does exactly the same internally. ALTER TABLE however is preferred because you can use pt-online-schema-change to avoid blocking the table.

To get better understanding how data use the tablespace I would recommend InnoDB tools https://github.com/jeremycole/innodb_ruby

For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:

# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▄▂░░                                                         │
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3   42.86%
  █ Index 15                                                            1   14.29%
  █ Index 16                                                            1   14.29%
  ░ Free space                                                          2   28.57%

StackExchange Database Administrators Q#139237, answer score: 4