My service is writing text file logs and I would like to make a system to store log messages from text files into MySQL. I wrote a C# Console Application, which automatically parses text files with regex and run
INSERT query. And what I have to do next is deleting old data(like older than 6 months) automatically.
I found that there is several ways to achieve this, but I could't determine what is the best way in real service.
- Seperates the tables into partitions by month, then drop old partitions.
- Makes a schedular which deletes old rows by
WHERE logDate < ...).
- Makes a table including the information of original text files. And makes foreign key from each log table which refers to
fileId(primary key of file table). If I delete any row from file table then data having its
fileIdwill be deleted cascadingly.
What will be the best solution? Or is there any idea that can help to choose one?
- I am using MySQL 8.0, and MyISAM for table engine.
- About 1M rows are inserted per day.