DISCLAIMER: only try this if you know what you are doing, and don’t blame me if it doesn’t work for you, deletes all your piwik data etc.
my process of migrating from single file tables to the partitioned ones was something like this:
[ol]
[li] create new tables with _new appended to the table name
[/li][li] dump data i want to keep into outfile
[/li][li] load infile the dump into the _new tables (note: having indexes already set made this faster then adding them afterwards)
[/li][li] repeat steps 2) and 3) with the new data added since the initial dump (initial dump/import took hours for me - high traffic sites)
[/li][li] take piwik offline
[/li][li] rename tables so piwik will use the partitioned ones
[/li][li] take piwik online
[/li][/ol]
step 6 is very quick on innodb, don’t know if it would be slow on myisam!
I created the log_visit table with this:
PARTITION BY LIST (MONTH(visit_first_action_time))
(PARTITION m01 VALUES IN (1) ENGINE = InnoDB,
PARTITION m02 VALUES IN (2) ENGINE = InnoDB,
PARTITION m03 VALUES IN (3) ENGINE = InnoDB,
PARTITION m04 VALUES IN (4) ENGINE = InnoDB,
PARTITION m05 VALUES IN (5) ENGINE = InnoDB,
PARTITION m06 VALUES IN (6) ENGINE = InnoDB,
PARTITION m07 VALUES IN (7) ENGINE = InnoDB,
PARTITION m08 VALUES IN (8) ENGINE = InnoDB,
PARTITION m09 VALUES IN (9) ENGINE = InnoDB,
PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
PARTITION m12 VALUES IN (12) ENGINE = InnoDB)
and the log_link_visit_action with:
PARTITION BY LIST (MONTH(server_time))
(PARTITION m01 VALUES IN (1) ENGINE = InnoDB,
PARTITION m02 VALUES IN (2) ENGINE = InnoDB,
PARTITION m03 VALUES IN (3) ENGINE = InnoDB,
PARTITION m04 VALUES IN (4) ENGINE = InnoDB,
PARTITION m05 VALUES IN (5) ENGINE = InnoDB,
PARTITION m06 VALUES IN (6) ENGINE = InnoDB,
PARTITION m07 VALUES IN (7) ENGINE = InnoDB,
PARTITION m08 VALUES IN (8) ENGINE = InnoDB,
PARTITION m09 VALUES IN (9) ENGINE = InnoDB,
PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
PARTITION m12 VALUES IN (12) ENGINE = InnoDB)
then when I want purge a month drop the partition for the month and create a new(empty) one (eg October):
ALTER TABLE traffic_log_visit DROP PARTITION m10;
ALTER TABLE traffic_log_visit ADD PARTITION (PARTITION m10 VALUES IN (10) ENGINE = InnoDB);
ALTER TABLE traffic_log_link_visit_action DROP PARTITION m10;
ALTER TABLE traffic_log_link_visit_action ADD PARTITION (PARTITION m10 VALUES IN (10) ENGINE = InnoDB);