MySQL eating 100% CPU after 1.1.1 upgrade


#1

My poor server’s taking a real beating since the latest piwik upgrade, MySQL is maxing out one CPU core trying to keep up with the normal demand. The Piwik dash wouldn’t load at all until I upgraded to the latest trunk version 1.1.2b1, now it takes forever to display only the first part of the page, and no websites at all.

Most of the time though I’m still getting this:

Fatal error: Maximum execution time of 45 seconds exceeded in \stats\libs\Zend\Db\Statement\Pdo.php on line 228

Logging on to the server itself gets me the standard piwik pages, but it’s unbearably slow and the CPU’s running pretty hot.

Would love to get my server back to the responsive little monster it used to be!! What can I do to help solve this problem?


(Matthieu Aubry) #2

That is really unexpected!
Can you disable the Live! plugin and see if it improves? there is a known performance issue with the Live plugin: Live! plugin performance issues · Issue #1966 · matomo-org/matomo · GitHub

Otherwise, can you enable mysql slow_queries logging and report here any slow queries from Piwik? we will take a look for sure.


#3

Killing the live plugin stopped the problem instantly, thanks for the suggestion. =D

What’s weird is Piwik didn’t start behaving oddly after 1.1 installed, instead it ran fine for nearly 24 hours before it started getting odd. I don’t know if it’s related, but some time between all things cool and all things crazy, I installed the iphone piwik app. It logged in but refused to load any piwik data. I didn’t think anything of it, and went to bed. The next morning my PC is getting the same lack of response. Probably coincidence but I thought I’d mention it. Anyway, a couple of hours after installing 1.1.2b the problem started switching and maxing 50% total CPU, but seeming to alternate the cores (previously it just maxed out one core solid). After a while, it calmed down… Then went back to alternating again.

But yeah, it’s the live module that’s doing it.

dev.piwik isn’t loading for me at the moment so I can’t read about any possible cure. =/


(Russell797) #4

Same issue here. Server being hammered every few seconds. 51% CPU. Server log filling up fast!


(Russell797) #5

After disabling Live pluggin I still get this from server access.log:

127.0.0.1 - - [06/Jan/2011:09:44:51 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:44:51 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:44:59 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart&minIdVisit=null HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:44:59 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:44:59 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:44:59 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart&minIdVisit=null HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:07 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 90 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:07 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:07 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart&minIdVisit=null HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:15 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:15 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:23 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart&minIdVisit=null HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:23 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:23 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:23 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=getLastVisitsStart&minIdVisit=null HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)"
127.0.0.1 - - [06/Jan/2011:09:45:31 -0500] “GET /piwik/index.php?module=Live&idSite=1&action=ajaxTotalVisitors HTTP/1.1” 200 106 “http://localhost/piwik/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#module=Dashboard&action=embeddedIndex&idSite=1&period=day&date=yesterday” “Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; Tablet PC 2.0)”


(Matthieu Aubry) #6

NZF and Russell797 - what is taking the CPU: apache or mysql?

if mysql, and IF the live plugin is disabled (since there is a known performance issue: Live! plugin performance issues · Issue #1966 · matomo-org/matomo · GitHub ) then there is a bug we don’t about. Enable mysql slow_queries logging MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log and please report here any slow query you find.


(Russell797) #7

I am very sorry…I failed to restart the server after disabling the Live plugin. After restarting the server the load on the CPU and of course the incessant writing to the access.log file ceased.