Default SQL queries optimization

Support & Bugs
1

Hi,

I am using Piwik since 1 year now and I have a lot of data into my database.

When new visitors are coming on my website, some inserts / updates queries are running on MySQL. My problem is that when I analyze my datas, I make a lot of select queries and MySQL locked a lot of insert queries (untill all the answers are done).

My suggestion was: what do you think to change the Piwik queries from:
INSERT INTO piwik_log_visit…
by
INSERT DELAYED…

Do you have some optimization problems you too ?
Did you ever try the “delayed” option ?

Thankss

2

jonathan, you should try to setup auto archiving: How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo this will solve this problem by processing reports once an hour only (for today) or once a day for other reports.

3

jonathan23: “insert delayed” could cause an “update” to fail for that row (and there is no “update delayed”).

4

@matt: The problem will be the same if I ask for the stats of the current day, I am right ?
@Anthon: yep…good point.

What do you think to have a database for insert / delete, and a replica only to read and make stats ?

5

This is approximatly the number of lines into my database:

~ piwik_log_visit = 6 800 000
~ piwik_log_link_visit_action = 19 000 000

Some of you have already make stats on bigger database than mine ?

6

Jonathan, can you please post your stats in the post: 301 Moved Permanently

Stats for today, should be processed maximum once an hour with such huge traffic. Maybe even less if you can?

When did you start using Piwik, how long did it take to track 19M pages?