Here's my scenario - five years worth of data in two tables, several tens of million records. For sake of discussion, the tables are "arrivals to the website" and "pages viewed during visit".
We need to keep the tables online - we sometimes come up with new analyses for the data. This means we can't remove the tables from the server, and we can't really produce some summary table (although I wouldn't discount that option totally). Sometimes the structure of the tables have to change to handle new features or remove old ones (like altering the values in an ENUM column, or adding/removing an index).
Yes, mostly the design is at fault here (eg use a reference table rather than an enum) but we have to handle the legacy of this design for the time being.
CURRENT SITUATION
--------------------
What we have done to help with database maintenance & speed is:-
o Query out old records into tables thatcontain a year's data (eg arrivals_2004, arrivals_2003). These tables are only every queries (select) or altered (alter table).
o Introduce a MERGE table that combines the years' tables together for easy querying (select count(*) from arrivals_merge where...)
THE PROBLEM
-------------
The above works well - but the size of the year tables are a problem. We looked at using myisampack, except the maintenance is too much trouble (taking the table offline to pack it, running myisampack, rebuilding the indexes after the packing ... then reversing and repeating the process when ever the table need an "alter table")
THE SOLUTION - NEARLY :(
--------------------------
We just discovered the ARCHIVE storage engine and it seemed to be the solution for us. Nicely compressed tables that could be included in a MERGE union and still be altered via SQL "alter table".
Unfortunately, no indexes are supported with this engine. I'm confused - why are no indexes supported? Having data compressed is a really good idea for those of us who need tables online but don't need to access the data often but no indexes suggests to me that querying the tables would take a very, very long time, yes?
Questions:-
o Will indexes be supported in a future version?
o If not - what is the reasoning not to include support for indexes?
o Is "alter table" supported in this engine?
o Can an ARCHIVE table be used in a MERGE table union?
o Documentation says "A straight INSERT just pushes rows into a compression buffer, and that buffer flushes as it needs. " ... how do we force the buffer to flush (as leaving records in a buffer seem very dangerous practice to me - servers do crash!). Or is the documentation misleading (http://dev.mysql.com/doc/mysql/en/archive-storage-engine.html) and the data is flushed after an insert operation?
Ta very much,
Dean
Incidentally,
http://dev.mysql.com/doc/mysql/en/archive-storage-engine.html ends with "For the ARCHIVE storage engine, there's a dedicated forum available on
http://forums.mysql.com/list.php?112 ". The only entry in the dedicated forum for Archive Storage discussions has one entry ... pointing back to
http://dev.mysql.com/doc/mysql/en/archive-storage-engine.html. Is no-one really interested in the Archive engine?! Boo!
Edited 1 time(s). Last edit at 08/29/2005 04:30AM by Dean Richardson.