Piwik performance for many sites

Support & Bugs
1

We have Piwik installed on one of our servers. (Shared hosting I believe).

We have about 800 sites loaded… we’ve only been tracking for about a month now. The database is over 2 GB or so right now.

It frequently times out. We can’t seem to do a backup, due to the size of the system.

We’ve already setup the archiving to run on a CRON job. That helped some earlier, but not now.

We are running .62 - trying to do a backup before we upgrade to .63

I think it stems from this issue: dev.piwik.org/trac/ticket/1077

Is piwik a non-viable platform for more than 500 sites or so? We still have another 300 or so to load into Piwik.

2

sounds like some sluggish database server.
I’m running piwik with around 6000 sites and only the archiving is giving me some problems as it runs for hours now but the visiting the dashboard or other backend parts works fine for me.
For backup purposes I switched the tables to innodb and I’m using xtrabackup to backup those without service interruption.

3

[quote=Thomas Seifert @ Jul 6 2010, 11:08 PM]sounds like some sluggish database server.
I’m running piwik with around 6000 sites and only the archiving is giving me some problems as it runs for hours now but the visiting the dashboard or other backend parts works fine for me.
For backup purposes I switched the tables to innodb and I’m using xtrabackup to backup those without service interruption.[/quote]

Hi Thomas,

Thanks for the info. I’ll look into what our server is. I’m pretty sure it’s a shared package… Which isn’t the best for anything like this anyhoww…

I’ve never heard of InnoDB. I just found their site via Google… How hard is to switch to using InnoDB?

Also… whats the server spec for your Piwik server?

4

Innodb is a database engine in mysql. Usually you have it installed with mysql itself.
Its easy to switch to it, just an “alter table …” command. Keep in mind, that the database size will nearly double with switching to innodb because of different storage formats.

I got a quite beefy server which is running as the main database server for all my services, not just piwik.
Its a dual quad-xeon machine with 16GB ram and a RAID10 with 4 disks.
PHP is delivered from other servers but piwiks load is mainly on the database.

Regards,

Thomas

5

Thanks Thomas,

I think I’ll be trying to get our Piwik install moved here soon. We have a MySQL server we could move to for the database…

6

[quote=Eric Burdo @ Jul 8 2010, 11:40 AM]Thanks Thomas,

I think I’ll be trying to get our Piwik install moved here soon. We have a MySQL server we could move to for the database…[/quote]

Hi, I am working with Eric on this.

We now have it on a dedicated server and the database set as INNODB by default so all tables are now in that format, even the ones created by the archiving script.

This seems to have got things working, but we are still unable to access the “all websites” as it just times out and I have it set at 600 seconds.

Should this be the case for a site with just over 700 sites at the moment, this will be increasing though.

Stephen.

7

This is being adressed in "All Websites dashboard" should load fast even when tracking hundreds of websites! MultiSites · Issue #1077 · matomo-org/matomo · GitHub

8

disable auto-archiving via the ui and let the archive job run every night.

@matt & vipsoft:
what about disabling “Reports for today will be processed at most every” if “Allow Piwik archiving to trigger when reports are viewed from the browser” is disabled?

9

Archiving is setup as a cron job already, so this should not be triggering from the UI.

I think it is a bug as Fabian suggests, that is awaiting a fix.

Can anyone confirm if they are using piwik with 700+ websites and able to access the multisite “All Websites” Daskboard.

Stephen.

10

Yeah, the all websites panel doesn’t work well with many sites. its simply running too many queries and it will still do that in the future, even with the enhancements if you have LOTS of sites style_emoticons/<#EMO_DIR#>/wink.gif.

11

Not exactly true, it can be optimized and fast. This is planned, but Maciej is busy! you can hire him to if you want to get that fixed

12

As far as I understand the ticket its supposed to merge 6 api requests per website into 1 per website. That might help for < 1000 sites but with 6000 sites its still 6000 queries which is probably slow :-).
Well, sorry if I get it wrong but I’m not planning for the all websites panel for now.

thomas