Upgrade from Piwik 1.0 to 2.x Performance degradation


#1

Hi everybody,

Recently we got an upgrade from 1.0 to 2.0.3 facing some performances issues. Finally we upgraded to 2.2.2.

Currently we use Piwik on a quite large site (600K page visits/360K unique visitors/6.600K actions all values per day) and we noticed a huge performance degradation, moving from the older to the latest version, especially on the database.

We are focusing on the concurrent connections vs the DB seeing the 1.0 50-100 connections raised up to 1500 of the 2.x.x thus locking the DB.

We tried to work on MySQL parameters (current settings are “innodb_buffer_pool_size” = 65536, “innodb_flush_log_at_trx_commit” = 2), we made a memory upgrade of the server (from 32 GB to 64 GB) and tried to follow your guidelines to tune Piwik ( increasing “tracker_cache_file_ttl” = 7200, disabling some Tracker plugins like “PrivacyManager” and “Provider”) but we still have the performance issue. At today we archive data hourly (“enable_browser_archiving_triggering” = 0) but we have to stop the batch because of the huge performance degradation.

Following the configuration data of the MySQL machine:

CPU: n. 2 Intel® Xeon® E5504 @ 2.00GHz (Intel® Xeon® Processor E5504 (4M Cache, 2.00 GHz, 4.80 GT/s Intel® QPI) Product Specifications)
RAM: 64 GByte
STORAGE: root=54GByte (60% free) - data=356GByte (47% free) - var=9.7GByte (74% free) - boot=996MByte (93% free) - /dev/shm=16GByte (100% free) - /MYSQLDATA=166GByte (29% free)
OPERATIVE SYSTEM: Red Hat Enterprise Linux Server release 5.9.0.2 (Tikanga) 64bit
MYSQL SERVER VERSION: 5.5.10 MySQL Community Server (GPL)

Thanks in advance for your support.


(Matthieu Aubry) #2

Have you noticed whether the performance drop affects the Tracker or the Archiver ?

There is a ticket for performance regression in tracker, see; Tracking servers CPU saturation after upgrade from 2.1.0 to 2.2.0 · Issue #5019 · matomo-org/matomo · GitHub

There is a forum post for regression in the archiver: 301 Moved Permanently

please comment in either to consolidate the bug report. We are not sure what could cause this