that problem must be somewhere else
i have a database which tracker tables size is about 6GB and report tables size is about 500MB
i tried to debug this problem so i made dump of archive tables, imported it into my pc and ran optimize and it was done under 3 minutes.
when i ran the same command on my website with about 1000 active users (at the same time) it freezes whole database and i must kill mysql
because innodb optimize behaves very differently from myisam
it creates tmp table and inserts optimized data into it and then drops old table
so i think this can be problem on live websites which tries to insert and select from table which is dumped into tmp table at the same time
based on this i think solution can be setting piwik into maintence mode at the start of archive script (or only in optimize part) and turning it off at the end of this script
here’s that optimize command
OPTIMIZE TABLE archive_numeric_2011_01,archive_numeric_2011_05,archive_numeric_2011_06,archive_numeric_2011_07,archive_numeric_2011_08,archive_numeric_2011_09,archive_numeric_2011_10,archive_numeric_2011_11,archive_numeric_2011_12,archive_numeric_2012_01,archive_numeric_2012_02,archive_numeric_2012_03,archive_numeric_2012_04,archive_numeric_2012_05,archive_numeric_2012_06,archive_numeric_2012_07,archive_numeric_2012_08,archive_blob_2011_01,archive_blob_2011_05,archive_blob_2011_06,archive_blob_2011_07,archive_blob_2011_08,archive_blob_2011_09,archive_blob_2011_10,archive_blob_2011_11,archive_blob_2011_12,archive_blob_2012_01,archive_blob_2012_02,archive_blob_2012_03,archive_blob_2012_04,archive_blob_2012_05,archive_blob_2012_06,archive_blob_2012_07,archive_blob_2012_08;