log tables (like customer_visitor) make the system very slow

[fabianus]

Hello everybody !

I changed from magento 1.9 to Magento 2.1 some months ago. We use magento for over 6 years and in all that time the db had about 33MB. Now about 3 months later its size has grown to more than 10 times (599MB). This causes the memory to run out of space and the whole system colapses. 

I had a look at the tables and discovered that mainly the logs got so large. So what I am looking for is to clean those logs. I only want to keep the logs that are necessary for the individual custumer experience, but for the analysis of access data I use google analytics and do not need those data in magento. 

Thanks a lot for any help !

Best regards, 

Fabianus

[Damian Culotta]

Hi @fabianus,

I guess you'll find this issue relevant: https://github.com/magento/magento2/issues/7512

[fabianus]

Hi @Damian Culotta
thanks for your reply !

I saw this chat, but unfortunatly it doesn't give any solution and the refered pages in the magento docs do not exist anymore.

Here is what I suggest to do - but I am not sure about the impacts:

DELETE FROM customer_visitor WHERE last_visit_at < DATE_SUB(NOW(), INTERVAL 3 DAY);

DELETE FROM report_event WHERE logged_at < DATE_SUB(NOW(), INTERVAL 3 DAY);

DELETE FROM report_viewed_product_index WHERE added_at < DATE_SUB(NOW(), INTERVAL 3 DAY);

And then :
OPTIMIZE TABLE customer_visitor
...