Not so high traffic hitting 100% CPU


#1

Hi,

I would need some help to resolve this problem.
Picik sits on a WIndows 2003 server with iis and PHP with 4 gb or ram.
Picik 1.6, upgraded from 1.0

It is used to monitor an intranet with an organisation of 500 people max. Not all at the same time on the intranet. So the traffic is not that huge.

I have disabled archiving from the browser and set up time limit of 3600 seconds for reports

The server still hits 100% cpu with the processes showing
mysqld.exe around 90%
php-cgi.exe around 10%

The only way to get that down is for me to disable logging by setting in the conf
[Tracker]
record_statistics = 0
but of course this does not help because i am not tracking anyone then.

I have also tried to run the windows powershell script with
powershell.exe –Noninteractive –Noprofile –Command “&{E:\piwik\misc\cron\archive.windows.ps1}”

and it returns:
Starting Piwik archiving…
Piwik archiving finished.
Starting Scheduled tasks…

Fatal error: Exception thrown without a stack frame in Unknown on line 0

Finished Scheduled tasks.

Any help would be welcomed.
Thanks
Nic


(Matthieu Aubry) #2

can you see on the mysql server what requests are being executed when CPU is working hard?

for example using SELECT FULL PROCESSLIST


#3

This is what comes up with SHOW FULLPROCESSLIST\G;

Info: SELECT
site.idsite,
site.main_url,
site.sitesearch_url,
site.sitesearch_parameter
FROM
piwik_site AS site
LEFT JOIN
piwik_log_visit AS visit
ON site.idsite = visit.idsite
LEFT JOIN
piwik_log_link_visit_action AS link
ON visit.idvisit = link.idvisit
LEFT JOIN
piwik_log_action AS action
ON action.idaction = link.idaction_url
WHERE
action.idaction = 33051


(Matthieu Aubry) #4

Please enable Mysql slow query log, and then post here your slow query log if you have any entry?


#5

Thanks Matt
Here is a portion of the log file generated

Time: 120208 9:35:09

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 10.094008 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1672391

SET timestamp=1328664909;
SELECT
site.idsite,
site.main_url,
site.sitesearch_url,
site.sitesearch_parameter
FROM
piwik_site AS site
LEFT JOIN
piwik_log_visit AS visit
ON site.idsite = visit.idsite
LEFT JOIN
piwik_log_link_visit_action AS link
ON visit.idvisit = link.idvisit
LEFT JOIN
piwik_log_action AS action
ON action.idaction = link.idaction_url
WHERE
action.idaction = 33064;

Time: 120208 9:35:15

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 12.140935 Lock_time: 0.000000 Rows_sent: 5 Rows_examined: 1672393

SET timestamp=1328664915;
SELECT
site.idsite,
site.main_url,
site.sitesearch_url,
site.sitesearch_parameter
FROM
piwik_site AS site
LEFT JOIN
piwik_log_visit AS visit
ON site.idsite = visit.idsite
LEFT JOIN
piwik_log_link_visit_action AS link
ON visit.idvisit = link.idvisit
LEFT JOIN
piwik_log_action AS action
ON action.idaction = link.idaction_url
WHERE
action.idaction = 33061;

Time: 120208 9:35:17

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 145.519350 Lock_time: 0.000000 Rows_sent: 513089 Rows_examined: 1672367

SET timestamp=1328664917;
SELECT
site.idsite,
site.main_url,
site.sitesearch_url,
site.sitesearch_parameter
FROM
piwik_site AS site
LEFT JOIN
piwik_log_visit AS visit
ON site.idsite = visit.idsite
LEFT JOIN
piwik_log_link_visit_action AS link
ON visit.idvisit = link.idvisit
LEFT JOIN
piwik_log_action AS action
ON action.idaction = link.idaction_url
WHERE
action.idaction = 10;


(Matthieu Aubry) #6

That’s quite surprising, it should work better. Because you are using piwik since 1.0, maybe you had once some problems with the upgrade?

My guess is that one of your table piwik_log_XXXX is missing an INDEX. please compare your schema to the official schema at: http://dev.piwik.org/trac/browser/trunk/core/Db/Schema/Myisam.php#L162 and create the missing INDEX if any?