@mattab opened this Issue on October 14th 2008 Member


This one was closed

This ticket is a place holder for all performance related notes/thoughts/feedback.

There are already a number of interesting performance improvements tickets
Here is the list:
* Piwik creates one cookie per install which is not scalable, we should build a server side DB based cookiestore: #409
* Piwik logs should be rotated into a yearly table: #5
* Bulk load Piwik logs (with a documented API) which would improve tracking performance, and make it easy to do performance testing #134
* Fix memory leak error during Piwik archiving task for larger piwik setup #766
* All Websites dashboard should work when Piwik has thousands of registered websites (currently only scales to a few hundreds websites) #1077

Building a regression and performance testing environment for Piwik
Partly described in #134.

The objective of this project is to build a automatic and reusable performance testing infrastructure, that would make it easy to generate lots of hits and big data sets

  • Setup performance testing server (called "preprod") with monitoring
  • Replaying logs from previous days (see #5554)
  • Use preprod to have a precise idea of load that Piwik can handle (pages per day, per month), the size of data in mysql
  • Use preprod to run Archiving and determine where Archiving is slow, or fails (via profiling).
  • Use preprod to optimize the tracking mechanism (denormalize tables, review index strategy)
  • Implement quick wins and plan for bigger changes 1 day

Once we have a system to assess performance, we could answer in a specific documentation page a few of the more common questions

  • Does it use a lot of bandwith on your site?
  • Does it take a lot of space/memory in the MySQL database?
  • Does it make your site load (and work) slower?
  • Expected DB size for a website with 1k, 10k, 100k visits and 1M pages.
    List server configuration required to run Piwik: on a shared server / on a dedicated server.
    List archiving processing time examples for large traffic.
  • Suggest best setting configuration for small & medium (default config) and higher traffic installs

Interesting read on scalability & performance

*      [Rasmus Lerdorf on scaling web apps with PHP](http://www.niallkennedy.com/blog/2006/07/rasmus-lerdorf-php-web20.html)

*      [Scaling PHP/MySQL, Hardware only, by Flickr](http://www.scribd.com/doc/21593/Scaling-PHPMySQL)

*      [Scaling at Digg](http://eliw.crossbows.biz/conference/zendcon-2007-high-perf.pdf)

*      [How Flickr Handles Moving You to Another Shard](http://highscalability.com/how-flickr-handles-moving-you-another-shard)

*      [Real World Web:Performance & Scalability](http://develooper.com/talks/rww-mysql-2008.pdf)

*     [Flickr architecture details](http://highscalability.com/flickr-architecture)

*      [Flickr architecture details](http://highscalability.com/flickr-architecture)

*     [Mysql performance blog](http://www.mysqlperformanceblog.com/)

To study

* [Mysql query profiler](http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html)

* [Optimizing php with APC (by a Facebook dev)](http://c7y.phparch.com/c/entry/1/art,apc_facebook)

* [ Various Mysql performance reporting tools](http://www.mysqlperformanceblog.com/tools/)

* Monitoring tools: nagios, mrtg, puppet, munin, ganglia.sourceforge.net (graph across multiple servers)

Keywords: performance fast scalability high traffic

@mattab commented on October 14th 2008 Member
      o DB sharding
            + This is expected to be the most useful and powerful feature. This is a required step to build a scalable Piwik: being able to partition the data horizontally on different mysql servers.

            + The first step would be to isolate a DB layer in Piwik, make sure that all database queries are located inside disctinct classes. We can then easily change the target server of these sql queries depending on the idsite. For example, all sites from id=1 to id=1,000 would go on server1, all sites from id=1001 to id=2001 would go to server2

            + we want to keep all the logic in php/mysql so we have to make the partition/server lookup in the code (in the DB layer)

      o Tools to manage sharding

            + when a website is too big to fit on existing shard, how do we move to other shard? This will happen. We can't afford downtime in the logging process

            + Moving the partitions from one server to another.

      o Parallelize archiving process (for example, archiving siteA and siteB can be done in parallel)

      o At some point, archiving data for a single website could possibly take several hours... can we split this process and do it every hour instead? Then sum the data for a the 24hours? (this is quite a big change in the php logic code)
@mattab commented on October 14th 2008 Member

getDateStart getDateEnd in *period are not optimized. could somehow be cached.

@mattab commented on October 29th 2008 Member

when the plugins are not used in the piwik.php logging script, don't load the related files
(that was #19)

@mattab commented on November 17th 2008 Member

Find memory leaks in PHP

If the image processing extension uses emalloc() style allocation, then you can compile php with --enable-debug and you will get a report of all leaks and their location at the end of the script. Note that you must finish with "return" or nothing to get the report, not with "exit".

But that won't pick up malloc() leaks. For that though you can use http://www.valgrind.org if you are running under unix.


Type: integer, Default value: 0
When this setting is set to something != 0 Xdebug's human-readable generated trace files will show the difference in memory usage between function calls. If Xdebug is configured to generate computer-readable trace files then they will always show this information.

@mattab commented on November 19th 2008 Member

One other idea would be to remove the count(distinct idvisitor) in the archiving query. other products like GA don't give the count of unique for each metric; that could eventually be a setting to decide to count unique or not.

we would still count uniques for the global stat, per month, week, day.

@mattab commented on December 5th 2008 Member
@anonymous-matomo-user commented on February 28th 2009

I have set up a git branch at github to help port piwik to postgresql :


Edit : I made a mistake with branch naming.

The github is here : http://github.com/klando/pgpiwik/tree/master

Just do that to grab it : git clone git://github.com/klando/pgpiwik.git

@mattab commented on March 22nd 2009 Member

see also #620: Piwik should use autoload to automatically load all classes intead of using require_once

@mattab commented on April 7th 2010 Member

plandem, see the thread on piwik-hackers for some thinking around alternative nosql databases in piwik: http://lists.piwik.org/pipermail/piwik-hackers/2010-February/000829.html

@robocoder commented on April 7th 2010 Contributor

There's also an interesting FAQ/blog post re: Infinidb's column-oriented storage engine for MySQL vs "NoSQL":

@mattab commented on April 7th 2010 Member

Infinidb sounds like something we should definitely investigate first, as it might be much (much) easier to use with the current Piwik architecture. Are there limitations when "dropping it" in instead of mysql?

@robocoder commented on April 8th 2010 Contributor

There's now a migration guide for InfiniDB. The relevant section starts at page 17.

The only limitation to the open source, community edition is the limit to a single machine (not CPUs, RAM, or concurrent users). Theoretically, you can build a fairly powerful box (think: multi-core, multi-processor boards) before you have to think about adding nodes (and license fees for the enterprise edition).

@anonymous-matomo-user commented on April 16th 2010

It looks like you have a good handle on where to start looking at InfiniDB. I took a quick look at your schema and don't see any fundamental problem with the log_visit fact table or the queries that reference it above. We do not (yet) support blob for your archive tables. The only other quick note I would add is that we aren't optimal for web/oltp style loads. However you could easily select * into outfile from existing schema, load data infile into InfiniDB to get good load rates, or use select into outfile, + cpimport (our bulk load) to get excellent load rates. 100k or more rows/second possible but will vary significantly based on disk and table definition.

One additional note, our current parallization distributes ranges of 8 million rows to each thread, so smaller tables won't show the same benefits from many cores as larger tables.

Anyway, currently signed up to follow this discussion, let me know if you have any questions or comments. Thanks - Jim Tommaney

@mattab commented on May 5th 2010 Member

See percona paper about Goal driven performance optimization: http://www.percona.com/files/white-papers/goal-driven-performance-optimization.pdf

@mattab commented on July 23rd 2010 Member

we will tackle critical issues (#409, probably #1077), and postpone others to post 1.0

@mattab commented on December 28th 2010 Member

It might be good to look into storing json encoded data tables rather than serialized php tables. This would improve portability. See http://stackoverflow.com/questions/1306740/json-vs-serialized-array-in-database as reference. Speed of json decoding large arrays VS unserialize should be tested.

@mattab commented on January 11th 2011 Member

I created a summary ticket from this one, as this ticket became unclear. See #1999

This Issue was closed on January 17th 2011
Powered by GitHub Issue Mirror