Piwik might have caused my site to get taken offline

Hi. I run a pretty simple site made up primarily of an imageboard and piwik to track visitors. I always keep a piwik tab open in my browser to check up on the traffic. Last night I refreshed the tab … and all I got in the piwik dashboard were all the widghets with the content “loading…” in them. I refreshed again and got 403 errors everywhere on my site. I get an email from the hosting support (I use hostgator) saying that

I apologize, but I was forced to suspend the directory /home/XXXXXX/public_html/ as multiple scripts inside were causing high loading issues on the server. Due to it affecting all of the other accounts on the system, we had to take immediate action for the health of the server.

and then I got a list of running queries:
thr first 4 looked like

USER: XXXXX
DB: XXXXX
STATE:
TIME: 0
COMMAND: Sleep
INFO: NULL

but the 5th one was

[i]USER: XXXXX
DB: XXX_visitors
STATE: Copying to tmp table
TIME: 14
COMMAND: Query
INFO: SELECT

CASE
WHEN counter = 50001 THEN '-1’
ELSE idaction
END AS idaction
,
CASE
WHEN counter = 50001 THEN '-1’
ELSE name
END AS name

, url_prefix, 1, sum(12) AS 12, sum(2) AS 2, min(28) AS 28, sum(29) AS 29, sum(30) AS 30, sum(31) AS 31, min(32) AS 32, max(33) AS 33, type
FROM (
SELECT
idaction, name,

CASE
WHEN type = 1 AND @counter1 = 50001 THEN 50001
WHEN type = 1 THEN @counter1:=@counter1+1
WHEN type = 2 AND @counter2 = 50001 THEN 50001
WHEN type = 2 THEN @counter2:=@counter2+1
WHEN type = 3 AND @counter3 = 50001 THEN 50001
WHEN type = 3 THEN @counter3:=@counter3+1
WHEN type = 4 AND @counter4 = 50001 THEN 50001
WHEN type = 4 THEN @counter4:=@counter4+1
WHEN type = 8 AND @counter8 = 50001 THEN 50001
WHEN type = 8 THEN @counter8:=@counter8+1
ELSE 0
END
AS counter
, url_prefix, 1, 12, 2, 28, 29, 30, 31, 32, 33, type
FROM
( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4, ( SELECT @counter8:=0 ) initCounter8,
(
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix,
count(distinct log_link_visit_action.idvisit) as 2,
count(distinct log_link_visit_action.idvisitor) as 1,
count(*) as 12,
sum(
case when custom_float is null
then 0
else custom_float
end
) / 1000 as 30,
sum(
case when custom_float is null
then 0
else 1
end
) as 31,
min(custom_float) / 1000
as 32,
max(custom_float) / 1000
as 33
,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = 0 AND log_link_visit_action.custom_var_k5 = ‘_pk_scount’) THEN 1 ELSE 0 END AS 28,
SUM(CASE WHEN log_action_name_ref.type = 8 THEN 1 ELSE 0 END) AS 29
FROM
piwik_log_link_visit_action AS log_link_visit_action
LEFT JOIN piwik_log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction
LEFT JOIN piwik_log_action AS log_action_name_ref ON log_link_visit_action.idaction_name_ref = log_action_name_ref.idaction
WHERE
log_link_visit_action.server_time >= '2013-07-04 21:00:00’
AND log_link_visit_action.server_time <= '2013-07-05 20:59:59’
AND log_link_visit_action.idsite = '1’
AND log_link_visit_action.idaction_url IS NOT NULL
GROUP BY
log_action.idaction
ORDER BY
12 DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, type[/i]

I also got a list with the site requests whch were just regular stuff (visitors loading images or index pages) plus these piwik requests

SOME_IP_1 MY_DOMAIN /piwik/piwik.php?action_name=The%20best%20fansite (this is my site’s title)

SOME_IP_2 MY_DOMAIN /piwik/?module=API&method=Live.getCounters&idSite=1&lastMin

On the site I have a visitors counter which I get using
MY_DOMAIN/piwik/?module=API&method=Live.getCounters&idSite=1&lastMinutes=30&format=xml&token_auth=xxxxxxxxxxxxxxx

MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=Actions&action=get
MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=Actions&action=get
MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=Actions&action=get
MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=Live&action=widget
MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=Referers&action=ge
MY_IP MY_DOMAIN /piwik/index.php?date=2013-07-05&module=VisitsSummary&acti

What could have caused this issue?

how many pages do you track per day?

It sounds like your mysql server is maybe too slow, or mis configured. See also: Optimize and Scale Piwik - Analytics Platform - Matomo