Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#1981 closed Bug (fixed)

Scheduled PDF Reports very slow when thousands of websites in Piwik

Reported by: Cyril Owned by:
Priority: normal Milestone: Piwik 1.2
Component: Core Keywords:
Cc: Sensitive: no


My Piwik installation has around 15000 sites and users. Calling the tracker took around 70ms in 1.0 (I test in command line, using curl). After upgrading to 1.1.1, the very same request takes nearly 800ms.

I have both my 1.0 and 1.1.1 installations in parallel (using the same database, same config file), so it's perfecty reproductible.

I've tried disabling the Live plugin, it doesn't change anything.

I've enabled the slow query log; my 1.0 install has zero slow query, the 1.1.1 has this one:

SELECT * FROM piwik_site WHERE idsite IN (1, 63, 64, 65, 66, 67, 68, 69, 70, 71, ...)

(thousands of IDs). This query isn't that slow (0.07), but I suspect Piwik 1.1.1, for some reason, iterates over all sites to do something.

MySQL CPU usage has increased x3, PHP processes are using 100% CPU, so PHP is definitely the limiting factor.

Change History (10)

comment:2 Changed 3 years ago by matt (mattab)

Yes this is probably the cause, however this should only be triggered via the browser if you did not enable cron archiving.
Are you using Cron archiving and did you disable 'trigger archiving via browser' in the admin setings?

Also reading the code runScheduledTasks() it is designe to run once maximum in parallel, but maybe it fails to ensure this condition.

comment:3 Changed 3 years ago by matt (mattab)

Julien, if you want to replicate, you can use the script in misc/test_cookies_GenerateHundredsWebsitesAndVisits.php to generate thousands of websites and replicate the issue.

comment:4 Changed 3 years ago by Cyril (cbay)

Thanks, disabling the PDF report plugin indeed solves the issue.

I am not using cron archiving because it's too slow, unfortunately (we're processing a huge number of requests every day, and even a 8-core dedicated server with SSD can't cope with that). Since many of our Piwik users never login to Piwik, triggering archiving via the browser avoids archiving all the sites whose statistics are never viewed.

I'll probably write a Piwik plugin that stores the date of the last visit for each user, and use that information to run archiving via cron only for users that have login recently.

comment:5 Changed 3 years ago by JulienM (JulienMoumne)

casting a vote for implementing suggested solution in 1836#comment:7 :

It is not the best performance wise (each call to runTasks() will result in selecting all websites and comparing all timezones), but it will work fine. To make it fast for a Piwik server with thousands of websites, we could for example record, on each website update, the earliest and latest timezone available in the _option table (but this will be a feature request when we hit problems, not yet the case :)

comment:6 Changed 3 years ago by matt (mattab)

Cyril can you please contact me by email, maybe I can use your dataset for performance testing and improving Archiving speed.

comment:8 Changed 3 years ago by matt (mattab)

  • Milestone set to 1.2 Piwik 1.2

comment:9 Changed 3 years ago by JulienM (JulienMoumne)

  • Resolution set to fixed
  • Status changed from new to closed

(In [3684]) fixes #1981, adding getUniqueSiteTimezones to SitesManager API

comment:10 Changed 3 years ago by matt (mattab)

  • Summary changed from Tracker performance divided by 10 after upgrading from 1.0 to 1.1.1 to Scheduled PDF Reports very slow when thousands of websites in Piwik
Note: See TracTickets for help on using tickets.