Hi Matt,
I know but as mentioned above the Apache error log doesn´t contain messages regarding to Piwik. I have only entries for other sites on the server.
Thanks
Nobs
Do you have any odd browser plugins you could turn off?
The windows server is it a 32 or 64 bit system?
Are you using any sort of proxy redirect?
Piwik runs on linux,
My PC is a Win 7 Enterprise 64-Bit and I tried several browser, I also tried my laptop with win 8 pro 64-Bit and also several browsers.
No proxies…
Nobs
This may sound crazy but is there any way you can use a native 32bit machine to access your piwik install?
is the server you run piwik on a 32 or 64 bit os box?
it’s likely a server issue nothing to do with client, since there’s an error in the log file…
I actually ran into the same issue when I updated to 1.10.1
I had to go just use the reset password feature and was able to log in again.
But then this put me into new issues. I was getting time out errors on pretty much every page and when archive was being run. I even tried the archive links directly in multiple browsers.
Started seeing the usual you are out of memory error that is common with piwik. So since this was what solved my issue last time by upping it. I figured I would just clone the install and move to a new server with 128GB of memory. Well still was getting that wonderful out of memory error. I thought to myself this is nuts if I give php 100GB of memory and this is still not enough to be able to stop the timeouts and memory error message from coming up there has to be another issue at hand. Well I changed the default Timeout of Apache2 to from 300 to 3000 just to test. Now nothing times out, I was ablt to move my php memory allowed for piwik back down to its usual amount and everything has been running fine for days now.
I know both of these issues do not really tie in together but they both hit at the same time. So if you keep getting memory issues try changing your Timeout to something much higher and see if it solves the issue.
when you up the memory did you check that the error message was also increasing? it’s possible the memory limit changes in php were not applied maybe because 100G should be enough I hope >:D<
yeah it was applied verified in a php settings test page, inside piwik settings check page and also by running it from the command line passing the -d flag to php.
as so
php -d memory_limit=100G COMMANDS
Nice smiling flip off. Like I stated had nothing to do with not enough memory, the whole process never consumed more than 9G. It’s all about the requests timing out, once I made it so regardless of how long the request took, it automagically worked. >:D< why dont you fix the many slow queries that are left in sql? >:D<
what slow sql do you see there? they are probably hard to fix since they should be GROUP BY requests already optimized!
This is only the top 10.
With this as the winner
Count: 53 Time=231.94s (12292s) Lock=0.00s (0s) Rows=1.0 (53)
And this log is only from the last 6 hours Piwik has been running.
Count: 23 Time=37.93s (872s) Lock=0.00s (0s) Rows=124590.6 (2865583), blah[blah]@localhost
SELECT
log_visit.referer_type AS referer_type, log_visit.referer_name AS referer_name, log_visit.referer_keyword AS referer_keyword, log_visit.referer_url AS referer_url,
count(distinct log_visit.idvisitor) as `N`,
count(*) as `N`,
sum(log_visit.visit_total_actions) as `N`,
max(log_visit.visit_total_actions) as `N`,
sum(log_visit.visit_total_time) as `N`,
sum(case log_visit.visit_total_actions when N then N when N then N else N end) as `N`,
sum(case log_visit.visit_goal_converted when N then N else N end) as `N`
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.visit_last_action_time >= 'S'
AND log_visit.visit_last_action_time <= 'S'
AND log_visit.idsite = 'S'
GROUP BY
log_visit.referer_type, log_visit.referer_name, log_visit.referer_keyword, log_visit.referer_url
Count: 18 Time=13.43s (241s) Lock=0.00s (0s) Rows=6.6 (119), blah[blah]@localhost
SELECT
idsite
FROM
piwik_site s
WHERE EXISTS (
SELECT N
FROM piwik_log_visit v
WHERE v.idsite = s.idsite
AND visit_last_action_time > 'S'
AND visit_last_action_time <= 'S'
LIMIT N)
Count: 16 Time=15.57s (249s) Lock=0.00s (0s) Rows=184.0 (2944), blah[blah]@localhost
SELECT
CASE
WHEN counter = N THEN 'S'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = N THEN 'S'
ELSE `name`
END AS `name`
, `url_prefix`, `N`, sum(`N`) AS `N`, sum(`N`) AS `N`, min(`N`) AS `N`, sum(`N`) AS `N`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = N AND @counter1 = N THEN N
WHEN `type` = N THEN @counter1:=@counter1+N
WHEN `type` = N AND @counter2 = N THEN N
WHEN `type` = N THEN @counter2:=@counter2+N
WHEN `type` = N AND @counter3 = N THEN N
WHEN `type` = N THEN @counter3:=@counter3+N
WHEN `type` = N AND @counter4 = N THEN N
WHEN `type` = N THEN @counter4:=@counter4+N
WHEN `type` = N AND @counter8 = N THEN N
WHEN `type` = N THEN @counter8:=@counter8+N
ELSE N
END
AS counter
, `url_prefix`, `N`, `N`, `N`, `N`, `N`, `type`
FROM
( SELECT @counter1:=N ) initCounter1, ( SELECT @counter2:=N ) initCounter2, ( SELECT @counter3:=N ) initCounter3, ( SELECT @counter4:=N ) initCounter4, ( SELECT @counter8:=N ) initCounter8,
(
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix,
count(distinct log_link_visit_action.idvisit) as `N`,
count(distinct log_link_visit_action.idvisitor) as `N`,
count(*) as `N`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = N AND log_link_visit_action.custom_var_k5 = 'S') THEN N ELSE N END AS `N`,
SUM(CASE WHEN log_action_name_ref.type = N THEN N ELSE N END) AS `N`
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 >= 'S'
AND log_link_visit_action.server_time <= 'S'
AND log_link_visit_action.idsite = 'S'
AND log_link_visit_action.idaction_url IS NOT NULL
GROUP BY
log_action.idaction
ORDER BY
`N` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
Count: 1 Time=43.18s (43s) Lock=0.00s (0s) Rows=0.0 (0), blah[blah]@localhost
DELETE
FROM piwik_archive_blob_2013_01
WHERE idarchive IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
Count: 1 Time=104.59s (104s) Lock=0.00s (0s) Rows=0.0 (0), blah[blah]@localhost
DELETE
FROM piwik_archive_blob_2013_02
WHERE idarchive IN (N,,{repeated 419 times}N)
Count: 1855 Time=24.81s (46022s) Lock=0.00s (0s) Rows=1000.0 (1855000), blah[blah]@localhost
SELECT idvisit, location_ip, location_country,location_region,location_city,location_latitude,location_longitude
FROM piwik_log_visit
LIMIT N, N
Count: 53 Time=231.94s (12292s) Lock=0.00s (0s) Rows=1.0 (53), blah[blah]@localhost
SELECT
count(distinct log_visit.idvisitor) as nb_uniq_visitors
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.visit_last_action_time >= 'S'
AND log_visit.visit_last_action_time <= 'S'
AND log_visit.idsite = 'S'
Count: 18 Time=14.75s (265s) Lock=0.00s (0s) Rows=20.4 (368), blah[blah]@localhost
SELECT
CASE
WHEN counter = N THEN 'S'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = N THEN 'S'
ELSE `name`
END AS `name`
, `url_prefix`, `N`, sum(`N`) AS `N`, sum(`N`) AS `N`, min(`N`) AS `N`, sum(`N`) AS `N`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = N AND @counter1 = N THEN N
WHEN `type` = N THEN @counter1:=@counter1+N
WHEN `type` = N AND @counter2 = N THEN N
WHEN `type` = N THEN @counter2:=@counter2+N
WHEN `type` = N AND @counter3 = N THEN N
WHEN `type` = N THEN @counter3:=@counter3+N
WHEN `type` = N AND @counter4 = N THEN N
WHEN `type` = N THEN @counter4:=@counter4+N
WHEN `type` = N AND @counter8 = N THEN N
WHEN `type` = N THEN @counter8:=@counter8+N
ELSE N
END
AS counter
, `url_prefix`, `N`, `N`, `N`, `N`, `N`, `type`
FROM
( SELECT @counter1:=N ) initCounter1, ( SELECT @counter2:=N ) initCounter2, ( SELECT @counter3:=N ) initCounter3, ( SELECT @counter4:=N ) initCounter4, ( SELECT @counter8:=N ) initCounter8,
(
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix,
count(distinct log_link_visit_action.idvisit) as `N`,
count(distinct log_link_visit_action.idvisitor) as `N`,
count(*) as `N`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = N AND log_link_visit_action.custom_var_k5 = 'S') THEN N ELSE N END AS `N`,
SUM(CASE WHEN log_action_name_ref.type = N THEN N ELSE N END) AS `N`
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_name = 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 >= 'S'
AND log_link_visit_action.server_time <= 'S'
AND log_link_visit_action.idsite = 'S'
AND log_link_visit_action.idaction_name IS NOT NULL
GROUP BY
log_action.idaction
ORDER BY
`N` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
Count: 10 Time=14.19s (141s) Lock=0.00s (0s) Rows=15827.5 (158275), blah[blah]@localhost
SELECT
log_visit.location_country AS location_country, log_visit.location_region AS location_region, log_visit.location_city AS location_city,
count(distinct log_visit.idvisitor) as `N`,
count(*) as `N`,
sum(log_visit.visit_total_actions) as `N`,
max(log_visit.visit_total_actions) as `N`,
sum(log_visit.visit_total_time) as `N`,
sum(case log_visit.visit_total_actions when N then N when N then N else N end) as `N`,
sum(case log_visit.visit_goal_converted when N then N else N end) as `N`, MAX(log_visit.location_latitude) as location_latitude,
MAX(log_visit.location_longitude) as location_longitude
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.visit_last_action_time >= 'S'
AND log_visit.visit_last_action_time <= 'S'
AND log_visit.idsite = 'S'
GROUP BY
log_visit.location_country, log_visit.location_region, log_visit.location_city
And here are some more from today. Honestly my slow query logs get huge each day. Every day all day I get tons of slow queries and not all are the GROUPBY issue you claim. Again these are ONLY my top slow queries, if you want a full list of all non GROUPBY ones I can post that as well.
Also can you explain how its optimized but yet will always return a slow query, to me a slow query is not optimized.
Count: 1 Time=45.53s (45s) Lock=0.00s (0s) Rows=0.0 (0), blah[blah]@localhost
DELETE
FROM piwik_archive_blob_2013_02
WHERE idarchive IN (N,,{repeated 671 times}N)
Count: 10 Time=25.07s (250s) Lock=0.00s (0s) Rows=152097.1 (1520971), blah[blah]@localhost
SELECT
log_visit.referer_type AS referer_type, log_visit.referer_name AS referer_name, log_visit.referer_keyword AS referer_keyword, log_visit.referer_url AS referer_url,
count(distinct log_visit.idvisitor) as `N`,
count(*) as `N`,
sum(log_visit.visit_total_actions) as `N`,
max(log_visit.visit_total_actions) as `N`,
sum(log_visit.visit_total_time) as `N`,
sum(case log_visit.visit_total_actions when N then N when N then N else N end) as `N`,
sum(case log_visit.visit_goal_converted when N then N else N end) as `N`
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.visit_last_action_time >= 'S'
AND log_visit.visit_last_action_time <= 'S'
AND log_visit.idsite = 'S'
GROUP BY
log_visit.referer_type, log_visit.referer_name, log_visit.referer_keyword, log_visit.referer_url
Count: 7 Time=12.99s (90s) Lock=0.00s (0s) Rows=21.4 (150), blah[blah]@localhost
SELECT
CASE
WHEN counter = N THEN 'S'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = N THEN 'S'
ELSE `name`
END AS `name`
, `url_prefix`, `N`, sum(`N`) AS `N`, sum(`N`) AS `N`, min(`N`) AS `N`, sum(`N`) AS `N`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = N AND @counter1 = N THEN N
WHEN `type` = N THEN @counter1:=@counter1+N
WHEN `type` = N AND @counter2 = N THEN N
WHEN `type` = N THEN @counter2:=@counter2+N
WHEN `type` = N AND @counter3 = N THEN N
WHEN `type` = N THEN @counter3:=@counter3+N
WHEN `type` = N AND @counter4 = N THEN N
WHEN `type` = N THEN @counter4:=@counter4+N
WHEN `type` = N AND @counter8 = N THEN N
WHEN `type` = N THEN @counter8:=@counter8+N
ELSE N
END
AS counter
, `url_prefix`, `N`, `N`, `N`, `N`, `N`, `type`
FROM
( SELECT @counter1:=N ) initCounter1, ( SELECT @counter2:=N ) initCounter2, ( SELECT @counter3:=N ) initCounter3, ( SELECT @counter4:=N ) initCounter4, ( SELECT @counter8:=N ) initCounter8,
(
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix,
count(distinct log_link_visit_action.idvisit) as `N`,
count(distinct log_link_visit_action.idvisitor) as `N`,
count(*) as `N`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = N AND log_link_visit_action.custom_var_k5 = 'S') THEN N ELSE N END AS `N`,
SUM(CASE WHEN log_action_name_ref.type = N THEN N ELSE N END) AS `N`
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_name = 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 >= 'S'
AND log_link_visit_action.server_time <= 'S'
AND log_link_visit_action.idsite = 'S'
AND log_link_visit_action.idaction_name IS NOT NULL
GROUP BY
log_action.idaction
ORDER BY
`N` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
Count: 6 Time=18.01s (108s) Lock=0.00s (0s) Rows=9.5 (57), blah[blah]@localhost
SELECT
idsite
FROM
piwik_site s
WHERE EXISTS (
SELECT N
FROM piwik_log_visit v
WHERE v.idsite = s.idsite
AND visit_last_action_time > 'S'
AND visit_last_action_time <= 'S'
LIMIT N)
Count: 1 Time=23.50s (23s) Lock=0.00s (0s) Rows=0.0 (0), blah[blah]@localhost
DELETE
FROM piwik_archive_blob_2013_01
WHERE idarchive IN (N,,{repeated 135 times}N)
Count: 6 Time=12.06s (72s) Lock=0.00s (0s) Rows=238.0 (1428), blah[blah]@localhost
SELECT
CASE
WHEN counter = N THEN 'S'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = N THEN 'S'
ELSE `name`
END AS `name`
, `url_prefix`, `N`, sum(`N`) AS `N`, sum(`N`) AS `N`, min(`N`) AS `N`, sum(`N`) AS `N`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = N AND @counter1 = N THEN N
WHEN `type` = N THEN @counter1:=@counter1+N
WHEN `type` = N AND @counter2 = N THEN N
WHEN `type` = N THEN @counter2:=@counter2+N
WHEN `type` = N AND @counter3 = N THEN N
WHEN `type` = N THEN @counter3:=@counter3+N
WHEN `type` = N AND @counter4 = N THEN N
WHEN `type` = N THEN @counter4:=@counter4+N
WHEN `type` = N AND @counter8 = N THEN N
WHEN `type` = N THEN @counter8:=@counter8+N
ELSE N
END
AS counter
, `url_prefix`, `N`, `N`, `N`, `N`, `N`, `type`
FROM
( SELECT @counter1:=N ) initCounter1, ( SELECT @counter2:=N ) initCounter2, ( SELECT @counter3:=N ) initCounter3, ( SELECT @counter4:=N ) initCounter4, ( SELECT @counter8:=N ) initCounter8,
(
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix,
count(distinct log_link_visit_action.idvisit) as `N`,
count(distinct log_link_visit_action.idvisitor) as `N`,
count(*) as `N`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = N AND log_link_visit_action.custom_var_k5 = 'S') THEN N ELSE N END AS `N`,
SUM(CASE WHEN log_action_name_ref.type = N THEN N ELSE N END) AS `N`
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 >= 'S'
AND log_link_visit_action.server_time <= 'S'
AND log_link_visit_action.idsite = 'S'
AND log_link_visit_action.idaction_url IS NOT NULL
GROUP BY
log_action.idaction
ORDER BY
`N` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
Count: 56 Time=206.88s (11585s) Lock=0.00s (0s) Rows=1.0 (56), blah[blah]@localhost
SELECT
count(distinct log_visit.idvisitor) as nb_uniq_visitors
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.visit_last_action_time >= 'S'
AND log_visit.visit_last_action_time <= 'S'
AND log_visit.idsite = 'S'
Hi again,
some time ago I was able to solve my issue. It was some kind of misconfiguration.
I really underrated the amount of data Piwik has to process with my monitored sites and so I haven´t activated the cron to archive the stats…
If you are interested in further details, view my blog post (German) about it: http://www.nobbis.net/de/nobbis-blog/themen-aus-dem-web/155-piwik-und-mein-problem-mit-dem-login.html
Cheers and thanks for trying to help me…
Nobs