Size of partition table space
I'm having space problems, so I need to delete large amounts of data every while, lets say every day. I decide to have the table partitioned, so I will get somo space back to de SO. To do so, I dump the data, create the table partitioned and reloaded the records, but I found that the new partitioned table waste more disk space than the original one. This, I think, is because while I'm inserting records, the file(s) are growing with diferent gaps, and at the end, there is a huge amount of disk claimed but not used.
Is there any way to tell MySQL the size of each file ?
What I have
CREATE TABLE `tbl` (
`Id` bigint(20) NOT NULL, -- A serial from a "sequence"
`tId` bigint(20) NOT NULL,
`reqDatetime` datetime NOT NULL, -- when it was inserted
other stuff
PRIMARY KEY (`Id`),
UNIQUE KEY `Idx_RSEvent_1` (`reqDatetime`,`Id`),
KEY `Idx_RSEvent_RSRoamingTransaction_FK1` (`tId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(id)
(PARTITION tbl_20000000 VALUES LESS THAN (20000000) ENGINE = InnoDB,
PARTITION tbl_20250000 VALUES LESS THAN (20250000) ENGINE = InnoDB,
PARTITION tbl_20500000 VALUES LESS THAN (20500000) ENGINE = InnoDB,
PARTITION tbl_20750000 VALUES LESS THAN (20750000) ENGINE = InnoDB,
......
but, when I check the files (quering the partitions table of the information_schema )
-----------------------+-------------+--------------+-----------+
PARt_DES | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
-----------------------+-------------+--------------+-----------+
20000000 | 1582668 | 107626496 | 208633856 | 7340032 |
20250000 | 250299 | 17350656 | 35241984 | 6291456 |
20500000 | 250299 | 17350656 | 35241984 | 6291456 |
20750000 | 250299 | 17350656 | 35241984 | 6291456 |
21000000 | 250056 | 17350656 | 35241984 | 6291456 |
21250000 | 250056 | 17350656 | 35241984 | 6291456 |
21500000 | 250299 | 17350656 | 34193408 | 7340032 |
21750000 | 250299 | 17350656 | 35241984 | 6291456 |
22000000 | 250299 | 17350656 | 35241984 | 6291456 |
22250000 | 250299 | 17350656 | 35241984 | 6291456 |
22500000 | 250299 | 17350656 | 35241984 | 6291456 |
22750000 | 250299 | 17350656 | 34193408 | 7340032 |
23000000 | 250299 | 17350656 | 35241984 | 6291456 |
23250000 | 250299 | 17350656 | 35241984 | 6291456 |
23500000 | 250299 | 17350656 | 34193408 | 7340032 |
23750000 | 250299 | 17350656 | 35241984 | 6291456 |
Because this table is use only to insert rows, the data_free will always be free, and lost.
Don't hesitate asking for more information if you need it !!
Thanks in advance !!
Gustavo
PD: Sorry, I don`t know haw to make the table look like a table