> > > > 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. > > > > > > OK, how about this: > > > > > > Use MyISAM and MERGE tables. Keep one table per day. E.g.: > > > > > > log_2005_01_15 > > > log_2005_01_16 > > > log_2005_01_17 > > > log_2005_01_18 > > > > > > etc. > > > > > > Use MERGE tables to give you the 60 day (and perhaps e.g. 14 day, 30 > > > day, 7 day, etc.) read views that you need, like so: > > > > > > CREATE TABLE log_view_7day ( > > > ... > > > ) TYPE=MERGE UNION=( > > > log_2005_01_12, > > > You can then do all of your inserts from the log writer into the > "today" > > > table, and do your reads against the various MERGEs. > > > > > > Every day at exactly midnight, you would use ALTER TABLE (which is > > > atomic) to redefine the UNION of the MERGE definition of the various > > tables. > > > > Modifying metadata because you need a different view at your > > data. > > > > Am I the only one to which this sound ugly? > > It sounds just fine to me. IMHO this is an excellent usage of MySQL > features to solve a technical issue. The original poster wants to maintain > only 60 days worth of data. He also needs really fast inserts. MyISAM can > perform concurrent, non-blocking inserts so long as there are no deletion > gaps in the table to which it is inserting. One solution to this is to > create what is in effect a "view" that spans 60 days worth of data, each > day's data in its own table. However, since views wont be officially > available until later, a MERGE table is an effective substitute.
I understand the usage or MERGE here, but I have my doubts at the "table per day" ... Besides: >There are 50 million inserts into table LOG a day This doesn't sound right -- how can this be alright: >The primary index on the table is seconds from 1971 ? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
