I have a curious issue here, maybe someone can help.
I have a single process that inserts data into tables that contain purely logging
information. This table is then searched by our Care department to troubleshoot issues.
I am looking for the best way to store this data, and the structure on the backend.
There are 50 million inserts into table LOG a day. The primary index on the table is
seconds from 1971. I only need to keep 60 days worth of data, and the table is only used
for read purposes. This is my design criteria, but my problem is how to delete old data
without crashing the log writer that is atteched to the table.
I would prefer to use MyIsam, since it is simple data, and as such it is much faster than
an untuned InnoDB table. But what would I do when it is time to delete data? The delete
would lock the table, hence freeze the application, and I can not have that.
I thought of using a combo of InnoDB and Merge tables, where the LOG table is InnoDB, and
the LOG_ARCH tables are Merge. The application would know to read from both, and I can
just migrate data from the InnoDB table to the Merge tables, and then delete from the LOG
table and not affect the app. since the delete is running against an InnoDB table.
I would use truncate table, but there will always be a certain amount of time that will
elapse between copying data from the live version to the archive, hence the need for a
selective delete on the original.
If anyone with experience with large logging apps can chime in here, I would be most