Mysql error 1206 - recommendation question

Support & Bugs
1

Hi,

Under 2.8.3 when trying to delete from the command line a site via:

DELETE FROM piwik_log_link_visit_action WHERE idsite = 2;

I got the error:

ERROR 1206 (HY000) at line 18: The total number of locks exceeds the lock table size

From looking at the doc at (look for the 1206 error section):

http://dev.mysql.com/doc/refman/5.1/en/innodb-error-codes.html

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.

Thanks,
Douglas

2

maybe you can use the existing piwik tools to delete old data? these should work as we have designed the code around these limitations: Managing your database’s size - Analytics Platform - Matomo

3

Hi,

Thanks for the quick response. I was deleting all the data for the site id using the information from:

http://piwik.org/faq/how-to/#faq_73

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?

Thanks,
Douglas

4

Hi,

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:

and some mysql pages:

http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html

Hope this helps.
Thanks,
Douglas