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