How to stop Mysql ibd files from continuously growing

Question

I have a mysql 5.5.44-0ubuntu0.14.04.1-log DB that has 2 tables that get a lot of data written to them then deleted within 48 hours, it is for a Salt system. The my.cnf is specifying innodb_file_per_table and the problem is that the .ibd files for the 2 tables keep growing and filling up the disk space.

I'm using this query to check the usage of the tables space and compare to the .ibd file on disk:

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024 / 1024), 2) "Size in GB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";

For the jids table that returns 30.51 GB but 57 GB is the file size, and for the salt_returns table it shows 24.40 GB but the file size is 79 GB.

On the surface it would seem like the table space is just not using all the innodb allocated space but the problem is that those files continue to grow when it would seem they shouldn't. I've been using pt-online-schema-change to reclaim some of the disk space but doing so requires ample space that I'm now running out of.

Here is the composition of the tables:

mysql> describe jids;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| jid   | varchar(255) | NO   | PRI | NULL    |       |
| load  | mediumtext   | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

mysql> show indexes in jids;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| jids  |          0 | jid      |            1 | jid         | A         |      174836 |     NULL | NULL   |      | BTREE      |         |               |
| jids  |          1 | idx_jid  |            1 | jid         | A         |        3067 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> describe salt_returns;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| fun        | varchar(50)  | NO   | MUL | NULL              |       |
| jid        | varchar(255) | NO   | MUL | NULL              |       |
| return     | mediumtext   | NO   |     | NULL              |       |
| id         | varchar(255) | NO   | MUL | NULL              |       |
| success    | varchar(10)  | NO   |     | NULL              |       |
| full_ret   | mediumtext   | NO   |     | NULL              |       |
| alter_time | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+--------------+------+-----+-------------------+-------+

mysql> show indexes in salt_returns;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| salt_returns |          1 | id       |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| salt_returns |          1 | jid      |            1 | jid         | A         |      221863 |     NULL | NULL   |      | BTREE      |         |               |
| salt_returns |          1 | fun      |            1 | fun         | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Show create table and table status info:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jids  | CREATE TABLE `jids` (
  `jid` varchar(255) NOT NULL,
  `load` mediumtext NOT NULL,
  UNIQUE KEY `jid` (`jid`),
  KEY `idx_jid` (`jid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                               |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| salt_returns | CREATE TABLE `salt_returns` (
  `fun` varchar(50) NOT NULL,
  `jid` varchar(255) NOT NULL,
  `return` mediumtext NOT NULL,
  `id` varchar(255) NOT NULL,
  `success` varchar(10) NOT NULL,
  `full_ret` mediumtext NOT NULL,
  `alter_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `id` (`id`),
  KEY `jid` (`jid`),
  KEY `fun` (`fun`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name                   | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| jids                   | InnoDB |      10 | Compact    | 352170 |          67763 | 23864442880 |               0 |      7864320 | 35754344448 |           NULL | 2015-10-15 15:45:52 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| salt_returns           | InnoDB |      10 | Compact    | 127156 |          74548 |  9479258112 |               0 |     12632064 | 73631006720 |           NULL | 2015-10-12 09:12:50 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

Answer 1

Does "Salt" continue to insert rows? And never delete them? If so, the .ibd files will continue to grow.

For purging "old" data, PARTITIONing is an excellent tool. Here are details for such: http://mysql.rjweb.org/doc.php/partitionmaint Keep in mind that converting to a partitioned table via ALTER TABLE will lock the table for a long time and need enough disk space for an extra copy. If this is not viable; we can discuss alternatives.

Side notes (probably Salt's fault):

• varchar(255) makes an awful PRIMARY KEY.
• I assume the mediumtext columns are bulky? If so, it would be more efficient to compress them in the client and store them in mediumblob. For normal text, this would shrink the disk footprint by 3x.