Hi,
I’m experiencing All Websites dashboard performance issues. The report takes a long time to show and often it fails with
WARNING: /home/pcsolcom/public_html/piwik/libs/Zend/Db/Statement/Mysqli.php(70): Warning - Error while sending STMT_PREPARE packet. PID=728414 - Piwik 2.14.3 - Please report this message in the Piwik forums: http://forum.piwik.org (please do a search first as it might have been reported already)
I was trying to understand the reason for that and here is the picture. I’ve discovered huge differences in number of rows and in table sizes of archive_blob_* tables. I’ve compared 2014 with 2015 - http://screencast.com/t/dfH1ycHZI0z It was no dramatic changes in traffic between January and February 2015, but Jan. table is 2.3GiB(!) and Feb. is only 200MB
The only remedy and slightly helps is OPTIMIZE table archive_blob_* It takes time to run, but reduces the table sizes for about a half. (For InnoDB it actually created table duplicate and moves all the data in there). Same effect was just to reset engine to the same innoDB.:
ALTER TABLE table_name ENGINE=InnoDB;
The problem with huge tables is that it is not enough memory server can provide to do such a costly operation. And even manual tables recreation seems promising, such a huge tables should not be created in the first place. What may cause such system behavior?
Any ideas?
Thanks!