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 | | |
+------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
pt-online-schema-change
should not be able to finish in such case).describe jids
doesn't matchshow indexes in jids
; none of the shown indexes arePRIMARY
. Either way, it looks like The indexjids.idx_jid
is redundant sincejids.jid
(probably the primary key) is the same.SHOW CREATE TABLE
; it is more descriptive thandescribe
. Also please provideSHOW TABLE STATUS
.