Perfomance problem with high traffic piwik


#1

Hello.
I have a perfomance problem, but my english very bad.
I have a high traffic piwik (100000+ page/action per day). I have 3 piwik node: two node only aggregation data and other one node is web interface and i have a separate server gor mysql database.I need help. Today i have a frezees in my piwik web interface that i haven’t before. But i can’t describe it in english.
.
Who can speek russian and may help me?
P.S. my gtalk is simplebox66@gmail.com


#2

I trying to describe my problem using screenhots.Maybe anybody will understand me.
First of all, I discribe my system:
I have two vm with 8Gb memory and 4 CPUs (Intel® Xeon® CPU E5-2670 0 @ 2.60GHz). On those vm runs a nginx+httpd. Those vm work with only track code (piwik.js and piwik.php). Nginx config is
location /piwik.js {
proxy_pass http://local;
}

    location /piwik.php {
            proxy_pass           http://local;
    }

I called those vm - “aggregators” and I have separate server with nginx, which balance traffic between aggregators. So i collect users data.
Also, I have vm with 8Gb memory and 4 CPUs (Intel® Xeon® CPU E5-2670 0 @ 2.60GHz). I called this - “data viewer”. On this one runs a nginx+httpd. This vm is needed to work with a piwik web interface.
I have a hardware server with 32Gb memory and 16 CPUs (Intel® Xeon® CPU E5620 @ 2.40GHz). On this server runs a mysql database.

I use the following software version:
CentOS release 6.7 (Final)
nginx version: nginx/1.8.0
Server version: Apache/2.2.15
mysql 5.1.73
php 5.5.29
piwik 2.14.3

The database size: 11Gb. I track 100000 page per day. I have 12 users on piwik web interface. “Archive reports when viewed from the browser” is disable, “Archive reports at most every X seconds” - 1800.
I use auto-archiving - “*/30 * * * * /usr/bin/php /var/www/html/<“data viewer”>/html/console core:archive --url=https://<“data viewer”>”.

Secondly, I discribe my first and main problem:
When user want to use segments view, he have a problem. Please look at screenshot 1,2,3 and you understand that I said. I say about error (screenshot 3) and and that the requests are processed for a long time. At this time mysql base is have a problem:
rise MySQL threads connected, rise cpu usage.

My second problem:
Look at screenshot 4,5,6 - it`s normally work. But when I load segments view, the button “view visitor profile” is not working.( look at the screenshot 7)


#3

upd. I attached next screenshots


#4

and the last screenshoot


(Matthieu Aubry) #5

Hello
thanks for the report. this bug is fixed in our latest RC version please upgrade: I would like to test early beta and RC releases, how do I enable automatic updates to use these development versions? - Analytics Platform - Matomo


#6

@matt, You said about my first or my second problem?


(Matthieu Aubry) #7

second problem.

please only report one problem per thread or it gets confusing


#8

Ok, @matt.
What you think about my frst/main problem?
Do you have any resolving? Or I must create a new topic about my main problem?
I find Very slow SQL queries when archiving a segment for an unknown action · Issue #8102 · matomo-org/piwik · GitHub , but i don’t understand does this solve my problem or not? Is this resolving my problem, that may you describe what I need do, because I don’t understand.
P.S. sorry for my bad English