The compression statistics shared here are from a very specific system.
Take them with a grain of salt and test to see how compression benefits your systems.

The system where I implemented InnoDB table compression contains more than one million read-only tables.  Some time ago, those tables were stored in a single MySQL instance, without using InnoDB compression (the size was 200+ TiB) and without using MySQL replication.  The storage was external (disk array accessed via a SAN) and was supporting snapshots.  At the very beginning, the amount of data (and tables) was much smaller, it slowly grew to its current size and it is still growing.  Because administration was becoming tedious, the system was sharded on servers with local storage and replication was used for redundancy.  To reduce the number of shards needed to store all this data, I implemented InnoDB table compression.

One particularity of this system is that the data arrives in MySQL in chunks (tables) that are never updated after being stored (after being fully written, the tables are read-only).  In the new sharded system, the data is written on the master in an InnoDB tables (non-compressed) and it reaches slaves via classical MySQL replication.  Once the table is fully written, a cron job running on all nodes of the replication hierarchy compresses the data locally (not writing to binary logs).

The compression script is trying many KEY_BLOCK_SIZE for finding the one resulting in the smallest footprint on disk.  The script works as follows for each table to compress:

1. it copies the table twice in a temporary schema (let's call those copies A and B),
2. it compresses table B with KEY_BLOCK_SIZE of 8,
3. if compression does not succeed, it keeps table A then exits,
4. if table B (just compressed) is larger than table A, it keeps table A then exits,
5. it swaps tables A and B,
6. it goes back to step #2 using KEY_BLOCK_SIZE of 4, 2 and 1.

The compression script stores the compression state of a table and the resulting sizes of each step in a log table having following structure:

> show create table compression_logs\G
*************************** 1. row ***************************
       Table: compression_logs
Create Table: CREATE TABLE `compression_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  `initial_size` bigint(20) unsigned NOT NULL,
  `state` enum([...]) NOT NULL,
  `copied_size` bigint(20) unsigned DEFAULT NULL,
  `kbs_8_size` bigint(20) unsigned DEFAULT NULL,
  `kbs_4_size` bigint(20) unsigned DEFAULT NULL,
  `kbs_2_size` bigint(20) unsigned DEFAULT NULL,
  `kbs_1_size` bigint(20) unsigned DEFAULT NULL,
  `final_size` bigint(20) unsigned DEFAULT NULL,
  `final_kbs` tinyint(4) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
1 row in set (0.03 sec)

Note that finding the size on disk of an InnoDB table is not straightforward.  Looking at the size of the ibd file (ls, du or stat) does not work as some data might still be in the Buffer Pool (not yet flushed to disk).  Also, the output of SHOW TABLE STATUS or the content of information_schema.TABLEonly shows an approximation of the data and index lengths.  I used the command FLUSH TABLES ... FOR EXPORT for solving that.  Below is an extract from the manual, which is exactly what we need to be able to use ls, du or stat for getting the table size:

[FLUSH TABLES ... FOR EXPORT] ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running.

I will come back later below to compression failing and to table size going the wrong direction (growing instead of shrinking after compression).  Now, let's look at the compression statistics.  For this post, I am only looking at a single shard of the new system (I am assuming that all shards have similar behavior).  The query below gives a good overview of the InnoDB table compression benefits on the amount of data on disk:

> SELECT nb_tables, initial_gib, final_gib,
    ->   ROUND(initial_gib/final_gib, 2) AS ratio
    -> FROM (SELECT COUNT(*) AS nb_tables,
    ->              ROUND(SUM(initial_size) / POW(2,30), 2) AS initial_gib,
    ->              ROUND(SUM(final_size)   / POW(2,30), 2) AS final_gib
    ->       FROM compression_logs) AS c;
+-----------+-------------+-----------+-------+
| nb_tables | initial_gib | final_gib | ratio |
+-----------+-------------+-----------+-------+
|    248670 |    20701.84 |   4566.50 |  4.53 |
+-----------+-------------+-----------+-------+
1 row in set (0.14 sec)

The results above have been obtained with the default value of innodb_compression_level (6).  Using the highest value (9) might give better results in the general case but the tests I did on that system do not show significant improvements (I might share more about that in another post).  So I kept the default for all the shards of this system.

An average compression ratio of 4.53 is very good but it might vary for different KEY_BLOCK_SIZE.  Let's look at more detailed data with this query (a final_kbs of 16 means no compression):

> SELECT final_kbs, nb_tables, initial_gib, final_gib,
    ->   ROUND(initial_gib/final_gib, 2) AS ratio
    -> FROM (SELECT final_kbs, COUNT(final_kbs) AS nb_tables,
    ->              ROUND(SUM(initial_size) / POW(2,30), 2) AS initial_gib,
    ->              ROUND(SUM(final_size)   / POW(2,30), 2) AS final_gib
    ->       FROM compression_logs GROUP BY final_kbs) AS c;
+-----------+-----------+-------------+-----------+-------+
| final_kbs | nb_tables | initial_gib | final_gib | ratio |
+-----------+-----------+-------------+-----------+-------+
|         1 |     40863 |     1940.72 |    265.15 |  7.32 |
|         2 |    152215 |    14937.60 |   3033.26 |  4.92 |
|         4 |     38862 |     3387.78 |   1048.73 |  3.23 |
|         8 |     13896 |      434.14 |    217.76 |  1.99 |
|        16 |      2834 |        1.60 |      1.60 |  1.00 |
+-----------+-----------+-------------+-----------+-------+
5 rows in set (0.23 sec)

As we see above, we end-up with tables of all KEY_BLOCK_SIZE/final_kbs but at this point, we do not know if compression stopped because ALTER is failing or because the table size grew with the next KEY_BLOCK_SIZE.  The query below is telling us when the next KEY_BLOCK_SIZE resulted in a bigger table:

The result above shows that, if we want the smallest footprint on disk, it is important to use the right KEY_BLOCK_SIZE:

• if using 8 instead of uncompressed (final_kbs of 16), 9.17 GiB is wasted (this might not look like much but it is 5.7 times the initial size),
• if 4 instead of 8, 51.82 GiB is wasted, which is an increase of 23%,
• and the increase is 31% for 2 instead of 1.

The attentive reader might have spotted a gap between the two tables above.  In the first tables, we have 152,215 tables that are compressed with a KEY_BLOCK_SIZE of 2 and in the second tables, we have only 112,278 tables.  This is because some tables failed to alter with a KEY_BLOCK_SIZE of 1.  Let's confirm that with the following query.