0

I have a log table in mysql that inserts 1 000 000 rows per day for the past 6 months. (Approximately 180 000 000 rows)

This table requires 54GB of space whereas rest of tables within the same database only require 2GB.

I plan to delete this log table manually using:

DELETE FROM log_table ORDER BY id asc LIMIT 1000000

It is intended to keep approximately 10 000 000 most recent rows.

Should I use some specific procedure such as table optimization, stopping temporarily traffic to the database, use higher / lower limit, make a copy of database to make sure data is not fragmented on the HDD and so on?

Thank you for your help.

  • 1
    Did you tried pt-archiver? This tool is actually for this kind of requirements. Also, this tool provides advanced controls for streaming replicas delay checking. @ahojvole – 3manuek Sep 19 '16 at 20:52
1

While there is more than one way to do that (including the one you mentioned), if you can temporarily stop the traffic to the DB, I suggest this way:

  1. Stop the traffic (At least to that table if possible)
  2. Create a new table with the same fields, but no indexes: CREATE TABLE new_table AS SELECT * FROM myTable WHERE 1=2;
  3. Insert into this new table all the rows that you want to keep: INSERT INTO new_table SELECT * FROM MyTable WHERE id>123456; -- OR, INSERT INTO new_table SELECT * FROM myTable ORDER BY id DESC limit 10000000;
  4. Create indexes on this new table.
  5. Switch the tables by renaming them: RENAME TABLE myTable TO old_table, new_table to myTable;
  6. Drop the old table
  7. Restart the traffic

If the ID of this table is an auto increment one, and you don't care about its value, you will have the chance to reset it to start from 1, so you don't run out of IDs in the near future (This is totally dependent on other factors)

HTH

|improve this answer|||||
1

SUGGESTION #1

First, stop the App from Logging.

Next, you need to find the last 10 million ids and get the minimum value.

Then, create a temp table with the 10 million rows of those IDs starting from that minimum.

SELECT id INTO @min_id
FROM log_table ORDER BY id DESC LIMIT 10000000 LIMIT 1;
CREATE TABLE log_table_new LIKE log_table;
INSERT INTO log_table_new SELECT * FROM log_table WHERE id >= @min_id;
ALTER TABLE log_table RENAME log_table_zap;
ALTER TABLE log_table_new RENAME log_table;

Finally, Enable Logging in your App

If the process go well, then delete the old table with this:

DROP TABLE IF EXISTS log_table_zap;

SUGGESTION #2

If you want little or no downtime, here is a little riskier approach

SELECT id INTO @min_id FROM log_table ORDER BY id DESC LIMIT 10000000,1;
SELECT id INTO @max_id FROM log_table ORDER BY id DESC LIMIT 1;
CREATE TABLE log_table_new LIKE log_table;
INSERT INTO log_table_new SELECT * FROM log_table WHERE id >= @min_id;
ALTER TABLE log_table RENAME log_table_zap;
INSERT INTO log_table_new SELECT * FROM log_table_zap WHERE id > @max_id;
ALTER TABLE log_table_new RENAME log_table;

EPILOGUE

Do not use CREATE TABLE AS SELECT because it does not preserve indexes for the temp table

|improve this answer|||||
  • should the order be DESC? i.e. SELECT id INTO @min_id FROM log_table ORDER BY id DESC LIMIT 10000000, 1; – Jehad Keriaki Sep 19 '16 at 20:00
  • Question: Are there any gaps in the log table ? Did you ever run a DELETE of any rows in the log table ? – RolandoMySQLDBA Sep 19 '16 at 20:05
  • Never mind, you're right. I meant DESC – RolandoMySQLDBA Sep 19 '16 at 20:08
  • Yes, but should other parts of statement be changed? i.e. SELECT id, and LIMIT to only 1? – Jehad Keriaki Sep 19 '16 at 20:12
  • Ugh, right again. I am off my game today – RolandoMySQLDBA Sep 19 '16 at 20:15
1

Going forward, I recommend having daily PARTITIONs, using BY RANGE(TO_DAYS(...)). This will let you very rapidly DROP PARTITION each day to jettison the 'old' data. A REORGANIZE PARTITION would create a new day's partition for tomorrow. More details on partitioning, especially on time-series

To get to that point, the fastest way is to create a new, partitioned, table and copy the 10M latest rows over into it. Then DROP the old table. But...

To do that with minimal impact start with MAX(id) - 10000000. Then walk through the table 10K rows at a time. See my discussion of chunking. The tricky part comes in getting the last few rows copied...

After copying what seems to be the last 10K chunk, display the last id used ($last_id), and finish thus:

RENAME TABLE real TO old, new TO real;  -- atomic and fast
Check that the new table looks ok   -- manual
INSERT INTO real  SELECT * FROM old WHERE id > $last_id; -- SEE NOTE BELOW
DROP TABLE old;

NOTE: If you are copying over the AUTO_INCREMENT id, this will have a problem. A solution would be to explicitly copy all columns except the id. (This would mess with the chronological ordering of id, but that might not be a big deal.)

|improve this answer|||||

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.