1

We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily data pruning on a certain table that takes the bulk of the occupied space. The table is called SECURITY_CODES. This table is used to store One Time Pins (OTP) for transactions on a Digital Bank. Each one time pin has a record in the table and is only valid for 5 minutes.

Current number of records in table: estimated at 215 million
No. of new records added per day: estimated at 1.4 million

Upon doing research, we found out that this kind of scenario recommends usage of database partitions to aid data pruning. While it is recommended to use partitions to aid in data pruning for big volumes of data — a recommendation that applies to this situation, our requirement does not call for the archiving of outdated, unnecessary records. In this case, would database partitions still be advised? All we want is to delete old records and not affect the table's current performance while the delete cron job is running.

Yes, we know it is not wise to store these PINs if they would expire in 5 mins. Its a design fault by the previous team that worked on the application. All we can do now is move forward and fix this mess.

8
  • Why are you storing all these millions of old PINs if they expire after five minutes?
    – mustaccio
    Feb 8 at 14:46
  • Its a fault by the previous team that created the application. I know its very wrong but all we can do now is move forward and fix this mistake Feb 8 at 14:48
  • 1
    So why don't you drop the old table and begin maintaining its new incarnation, removing expired PINs after five minutes?
    – mustaccio
    Feb 8 at 14:58
  • 1
    I wouldn't use a database table at all for a PIN that expires after 5 minutes. Use a key-value store that supports automatic expiration of data entries. For example, Redis. See redis.io/commands/expiretime Feb 8 at 16:57
  • Xmus, Consider posting TEXT results of SHOW CREATE TABLE security_codes; for us to see the data you are dealing with. Do you have a MAINTENANCE window any day of the month? For how many minutes? Nothing needs to be partitioned - you would just be wasting time. Feb 9 at 2:15

2 Answers 2

1

A solution that I have used in some of my projects is

  • To flag the expired records.
  • Use a procedure to insert a new record, which uses an expired record if it exists, otherwise it creates a new one.

This way, there is no deletion involved. And it requires a minor change to the code and database.

2
  • Rohit, Could you post the example code that accomplished this goal, along with the SHOW CREATE TABLE tbl_name; that allows this to be accomplished in an elegant manner? Feb 9 at 1:50
  • Ugh! How do you find an expired row to reuse? It sounds like there would be 60K rows in the table even if pruned after 5 minutes.
    – Rick James
    Feb 9 at 20:02
0

DO NOT USE DELETE on that huge table; it could take hours and would probably overflow disk. And interfere with the other activity!

Instead, Create a new table for the 60K rows you need to keep for the last 5 minutes, then swap tables.

Then (with no real preference)...

Plan A: Partition by minute with about 7 partitions. See Partition

Plan B: Have a job that runs in a loop deleting 100 rows at a time. 100 is probably enough to keep up and not too high that it would interfere with the other activity.

Plan C: Rotate 3 tables every 5 minutes: The first is being dropped; the other two are being checked for the pin (with a UNION); the last is being inserted into.

Please provide the current SHOW CREATE TABLE and how you INSERT and check pins. I may have further thoughts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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