2

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 |                |         |
+------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
  • Possibly fragmented indexes? – dwjv Oct 20 '15 at 9:46
  • 1
    Not sure but this could possibly be use-case of partitioning. Create partition and drop them instead of deleting!! – mysql_user Oct 20 '15 at 11:10
  • Whats the mysql version? Are there any long running transactions? (not probable as the pt-online-schema-change should not be able to finish in such case). – jkavalik Oct 20 '15 at 11:19
  • Maybe mysql 5.5.43 displays it differently than mariadb, but it looks like describe jids doesn't match show indexes in jids; none of the shown indexes are PRIMARY. Either way, it looks like The index jids.idx_jid is redundant since jids.jid (probably the primary key) is the same. – G-Nugget Oct 20 '15 at 16:53
  • Please use SHOW CREATE TABLE; it is more descriptive than describe. Also please provide SHOW TABLE STATUS. – Rick James Oct 20 '15 at 17:34
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.
  • Yes, I've heard of the partition table, but these tables can't be locked for much time at all without taking the application offline. Hence why i used pt-online-schema-change for optimizing. – bwizzy Oct 20 '15 at 18:43
  • OPTIMIZE TABLE is usually a waste of time. – Rick James Oct 20 '15 at 19:28
  • If you PARTITION by, say, "hour" and can drop the partition with data that is 49 hours old, then it solves all the problems mentioned. Locking is very brief; the ibd won't bloat (since you are inserting into new partition and dropping old partition); etc. – Rick James Oct 21 '15 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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