Changing the Database from MyISAM to Innodb (and maybe archive)


#1

Hello everyone,

at the moment i’m using piwik on roughly 30 Websites with ~ 12k-15k new entries in piwik_log_link_visit_action a day.

This is not a Problem but because it is planned to use piwik on some more websites the (near) future the visits a day will double or later even triple .

So i thought of some problems i maybe or will encounter.

Because i lock the database to make backups (so they are fully consistent) i would like to convert them to Innodb: In My Plan, i simply convert them, and change the value ‘schema = “Myisam”’ in the config.php to innodb

The table piwik_log_link_visit_action is quite big so i would delete old entries of it, as i understood generated reports and statistics won’t be affected of it, or? so it wouldnt matter if i delete all records older than a year and i can still see how many users were on a specific website 2 years ago ?

About the third thing, i haven’t really thought about but it came to my mind when i watched at the tables.
Really old statistics aren’t accessed often. and when the contents of piwik_log_link_visit_action get deletet after a year nothing will be ever written again to tables like piwik_archive_blob_2008_* or piwik_archive_numeric_2008_* . Am i right with this conclusion ?
If yes, it would be no Problem to convert such tables to the ARCHIVE engine which does not support things like UPDATE or DELETE but stores the contents in a quite efficient and compressed format.
At the moment this would almost be no use (the tales from 2008 are quite small) but, lets say in a year or 2 this could safe me quite some storage capacity.
So what do you think of my plans ?