Hello, maybe this is a suggestion that is impossible to apply…
What about to archive old data to file instead of database?
Example 1: I have a limited MySQL quota and piwik_archives tables are becoming too big. I don’t want to loose old statistics, but I need to free up space and I have unlimited file space. I don’t need to look every day to old months/years statistics, so maybe old archives can be saved and can be automatically called from file when I need them, freeing up the database.
Example 2: I want to purge old log entries as suggested in the wiki. But in the future if I need to rebuild archives I don’t want to loose data. Maybe old log entries can be periodically saved to file before deleting them from the log.
copy the archive tables (real table, the *.frm files in your MySQL folder)
dump the tables to generate *.sql script so you can run it again to generate the tables
If you store the archives, it makes no sense to rebuild it again, so I doubt you need to backup log_visit. But if you insist, do a conditional mysqldump with conditions you want, e.g.: mysqldump … --where="‘visit_server_date’ between ‘2010-01-01’ and ‘2010-12-31’"
What I mean is the ability for “old” (user defined value) archived statistics to use a sort of mysql emulation on file, like an old project I found named FFQL, instead of just eat database space… for who have an hosting with unlimited webspace and limited database space it can be useful. And since old statistics are not accessed every day it’s not important if this method is slower.
But I know it’s a big work, also beacuse FFQL is a dead project since two years and there isn’t a ready to use solution…
Am I missing something? I don’t get the point of “emulating” MySQL as file… They are already files, with *.frm extension. If you meant TEXT file, simple mysqldump it and g/zip for archiving purposes. It will save a LOT of space. That’s what I do.