Porblems with VisitsSummary on heavily loaded server


(cajax) #1

Hello!
My Piwik 0.5.4 running on VPS with 500mhz and 256Mb of RAM which was enough all this time. Yesterday i added new website with higher load (about 25K visitors/day). It was working fine and server processed everythings untill today. Now on dashboard Visitors wigdet shows only yesterday data and part of VisitsSummary widgets are not working. Logs are working as well as Plugins and ScreenResolution. And all new requests is working too.

Now processor load is around 15-20% and RAM increased to 400 megs (100 of them are still free).

So, appently engine can’t calculate stats like uniq_visitors because of some bottlenecks in MySQL server configuration.
Does anyone can give advices or sample configuration for MySQL to handle correctly such kind of load?

And one more question. Data calculated by VisitsSummary plugin, is stored precalculated when archiving or it will be recalculated each time someone request it?


(Matthieu Aubry) #2

did you setup auto archiving? see http://piwik.org/docs/setup-auto-archiving/

After you do that, please enable mysql slow query logging and report here the slow queries that are ran. thx


(cajax) #3

Hello again.

Appears like increased RAM changed situation. Also i increased archiving interval and load reduced significally.

Here is one of slow queries

# Query_time: 2  Lock_time: 0  Rows_sent: 3  Rows_examined: 175458
SELECT  name,
                                                        type,
                                                        count(distinct t1.idvisit) as nb_visits,
                                                        count(distinct visitor_idcookie) as nb_uniq_visitors,
                                                        count(*) as nb_hits
                                        FROM (piwik_log_visit as t1
                                                LEFT JOIN piwik_log_link_visit_action as t2 USING (idvisit))
                                                        LEFT JOIN piwik_log_action as t3 ON (t2.idaction_url = t3.idaction)
                                        WHERE visit_server_date = '2010-03-26'
                                                AND idsite = '86'
                                        GROUP BY t3.idaction
                                        ORDER BY nb_hits DESC;

Also now i understand where was my mistakes. First - i did archiving too often and the second - is public rating page. Statistics (visits, unique visits and hits) was calculated each minute for all websites what added even more load.

matthieu,
Thank you for pointing. style_emoticons/<#EMO_DIR#>/smile.gif