Mysql error 1206 - recommendation question

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

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

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

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