MySQL Forums
Forum List  »  Partitioning

Size of partition table space
Posted by: gustavo guido
Date: June 29, 2012 02:46PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Size of partition table space
7429
June 29, 2012 02:46PM
2566
July 01, 2012 12:32PM
2124
July 02, 2012 07:46AM
2254
July 03, 2012 12:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.