0

I am taking over a database that is... not great. When this DB was originally built, it was built without too much understanding about the volumes of data that were going to be involved. The initial estimates were off by... a lot.

So, this DB is extremely simple. It has one chunky table that gets written to consistently by a number of application hosts. This DB has one way replication and sits behind a keepalived VIP. We have a BI host that logs in once an hour and reads the data entered in the last hour.

Once a day, the BI host runs a delete on all data that is older than 7 days. We are required to keep the data for 7 days, but not for longer.

The problem is that we were expecting the DB to get thousands of lines per day, instead we are getting millions and millions. Space is not an issue, but the delete is now taking 20-25 minutes, during which time the application hosts are not able to write to the DB as the table is locked for the delete.

I need to improve this DB performance and I am unsure how of to do it PROPERLY. I am not a DBA by trade, We are a small shop and my experience in database usage has kind of landed me this particular task.

I am thinking that some form of table partitioning on this table might work, but I have never really implemented this on a production system (I have a staging host I can use though for testing).

If I were to create a system of partitions, would I make 31 partitions, one for each day of the month, and then use a date function to store the record (which has a datetime field of course) and segregate the data day by day?

Is it reasonable to create 31 partitions on a table in mysql 8 for this kind of utilization? Should I do a mod 10 and 10 partitions instead?

Would this improve the speed of my delete, or at least make the rest of the table unlocked? Am I missing some super obvious system such as adding a (NOLOCK) hint to my delete?

Thank you all in advance for any help. Mark

Improve this question
5
  • Daily partitioning and TRUNCATE/DROP PARTITION from daily event procedure. Of course, a lot of partitions for future days must always be checked/created by this event procedure. "31 partitions" scheme is not good idea.
    – Akina
    Mar 9 at 10:15
  • 1
    We do the same thing on our databases here. Partition by day, then drop the oldest one each day (and create a new one). If you only need 7 days data I would suggest you only need 9 partitions, 7 days in the past, today, tomorrow. I always include tomorrow as it 'should' always be empty and it's a lot quicker to split an empty partition than a full one.
    – IGGt
    Mar 9 at 13:02
  • Ok, so you suggest creating an event that runs daily, which re-partitions the table? Would this not be fairly taxing and lock the DB up much? I would assume that this would run at our current clean up time, i.e 14.15, and it would prepare the partition for tomorrow, and truncate and drop the 7+ day old partition. Would this make sense?
    – M.Barbara
    Mar 9 at 13:39
  • @IGGt I see your comment. Ok, that makes sense. So the partition split would occur on an empty partition. Makes sense. I'll need to read up on this.
    – M.Barbara
    Mar 9 at 13:42
  • The first thing you should read about partitioning is the rule about primary and unique keys: dev.mysql.com/doc/refman/en/… This limitation might make partitioning unusable for your table, so you need to understand this before you invest a lot of time into this strategy.
    – Bill Karwin
    Mar 9 at 16:56

1 Answer 1

Reset to default
0
  • PARTITION BY RANGE TO_DAYS(...)) (Note that only certain functions apply here.)
  • Rows being INSERTed will automatically go into the right partition.
  • Maintain 9 partitions -- 1 a week old (a full day, awaiting DROP); 6 full ones for the week, 1 for today (under construction), and an empty partition for future.
  • Daily DROP PARTITION (virtually instantaneous; don't worry about locks)
  • Daily REORGANIZE PARTITION future INTO [tomorrow], future ... (Instantaneous, assuming future is empty.)
  • Details: http://mysql.rjweb.org/doc.php/partitionmaint
  • Instead of playing with 31 partition names, programmatically give each partition a name with the full date.
  • Once the partitioning is set up, you do not need to "repartition the table", only do the DROP and REORGANIZE, which briefly touch parts of the table.
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.