I'm responsible for a production database using MySQL 8.0 (InnoDb engine). It has become necessary for me to archive data from a sizeable table (the data file takes up approximately 50% of the hard drive).
I've learnt that using
DELETE FROM x WHERE y is a horrible idea and doesn't necessarily reduce disk space usage. My new strategy is to:
- Export the data I need to archive to CSV (I was doing this before)
- Select the data I want to keep in the DB and place it in a new table (using
CREATE TABLE ... SELECT)
- Apply the PK AI NN BIGINT primary key that I have in the existing table to the new table
- Delete the non-FK indices from the current table (to reduce disk usage)
- Apply FK indices to the new table
- Apply non-FK indices to the new table
- Rename the old table and new table
- Verify new table is working
- Drop old table
My problem / question really begins at step 3: my raw table consumes 44.6 GB before applying the primary key, and as I am typing this the replacement file is pushing 52.2 GB. That seems like a lot of space for a primary key that's technically based on data that's already there.
Could anyone explain why it's taking so much space? I'm running out of hard drive space now and don't know how to estimate how large the final data file might be.
CREATE statement I used to generate the temporary table:
CREATE TABLE ts_temp SELECT * FROM timeseries WHERE (type_id IN (1, 4, 5) AND date > CURDATE() - INTERVAL 24 MONTH) OR (type_id IN (8) AND date > CURDATE()) OR (type_id IN (10, 11) AND date > CURDATE() - INTERVAL 9 MONTH) OR (type_id IN (12, 13, 14, 15, 22) AND date > CURDATE() - INTERVAL 6 MONTH) OR (type_id IN (16, 17, 19, 20, 21) AND date > CURDATE() - INTERVAL 2 MONTH) OR (type_id IN (2, 6) AND date > CURDATE() - INTERVAL 1 MONTH);
Getting a subsequent
CREATE statement from MySQL Workbench for this table would look like this:
CREATE TABLE `ts_temp` ( `id` bigint unsigned NOT NULL DEFAULT '0', `item_id` int DEFAULT NULL, `date` date DEFAULT NULL, `type_id` int DEFAULT NULL, `node_id` int DEFAULT NULL, `value` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Then, I applied the PK using this:
ALTER TABLE `ts_temp` CHANGE COLUMN `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`);
I should perhaps mention that this is a timeseries table and we ran out of AI numbers when it was a (default) signed INT field, which I subsequently converted to BIGINT unsigned.