my question is what be the recommendation for what to increase innodb_buffer_pool_size to from the default?
I did try to do smaller data samples via:
DELETE FROM piwik_log_link_visit_action WHERE server_time < DATE_SUB(NOW(),INTERVAL 5 MONTH) and idsite = 2;
but to no avail, got the same error message. When I changed it to 6 months, it came back saying nothing to do, as I
expected since there is not data that old.
that was given to me from another post. My command script was:
DROP TABLE piwik_archive_blob_2014_01;
DROP TABLE piwik_archive_blob_2014_05;
DROP TABLE piwik_archive_blob_2014_06;
DROP TABLE piwik_archive_blob_2014_07;
DROP TABLE piwik_archive_blob_2014_08;
DROP TABLE piwik_archive_blob_2014_09;
DROP TABLE piwik_archive_blob_2014_10;
DROP TABLE piwik_archive_blob_2014_11;
DROP TABLE piwik_archive_numeric_2014_01;
DROP TABLE piwik_archive_numeric_2014_05;
DROP TABLE piwik_archive_numeric_2014_06;
DROP TABLE piwik_archive_numeric_2014_07;
DROP TABLE piwik_archive_numeric_2014_08;
DROP TABLE piwik_archive_numeric_2014_09;
DROP TABLE piwik_archive_numeric_2014_10;
DROP TABLE piwik_archive_numeric_2014_11;
DELETE FROM piwik_log_visit WHERE idsite = 2;
DELETE FROM piwik_log_link_visit_action WHERE idsite = 2;
DELETE FROM piwik_log_conversion WHERE idsite = 2;
DELETE FROM piwik_log_conversion_item WHERE idsite = 2;
I should have stated this in my original post. From reading the link you gave me, I am right hat this
would not delete all statistics for a given site?
I was able to limit using the limit option since the date option did not work:
DELETE FROM piwik_log_link_visit_action WHERE idsite = 2 limit 1000000;
We increased the ‘innodb_buffer_pool_size’ for mysql to help with the locks and for a speed improvement.
I also tried this after getting ‘innodb_buffer_pool_size’ upped and it worked fine.
Here was an interesting article I found on the net: