0

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.

  1. Seperates the tables into partitions by month, then drop old partitions.
  2. Makes a schedular which deletes old rows by DATETIME field(with WHERE logDate < ...).
  3. 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 fileId will 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.
3
  • About 1M rows are inserted per day. - i.e. ~50k rows per hour. I'd prefer hourly event procedure which will delete the rows which are older than 6 months. But delete them not by datetime column but by autoincremented primary key value - this may improve the deletion slightly. Also you may need to run OPTIMIZE TABLE sometimes (for example, monthly) for to defragment the table.
    – Akina
    Jun 10 at 11:48
  • The partitioning is not available on MyISAM on MySQL 8.0. But you may use MERGE Engine (MRG_MyISAM). Of course, there are a lot of disadvantages while comparing with partitioning, but it may be useful in your case nevertheless. Investigate: dev.mysql.com/doc/refman/8.0/en/merge-storage-engine.html
    – Akina
    Jun 10 at 11:52
  • @Akina - I used MERGE once, about 20 years ago. I got burned when it bumped up against a 4GB limit that was unexpected.
    – Rick James
    Jun 21 at 1:00

1 Answer 1

0

PARTITION BY RANGE(TO_DAYS(...)) and use DROP PARTITION and REORGANIZE PARTITION. Details here: Partition

I would use weekly partitions and have a cron (or EVENT) job running just before midnight to do the DROP and/or REORGANIZE when necessary.

Very efficient. DROP PARTITION is orders of magnitude faster than DELETE. Never need OPTIMIZE TABLE.

Your 3 options are currently ordered from best to worst. (Cascading deleted sounds terrrrrible.)

In spite of what you might have read, use InnoDB, not MyISAM. The disk footprint will be 2x-3x bigger, but power failures won't be a hassle.

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.