Please help me to check the problem(:P)


#1

when I used the archiving function, I found the piwik would excute the following sql:

SELECT sub.*
FROM (
SELECT *
FROM statistic_log_visit AS log_visit

		WHERE log_visit.idsite = '2'  
			[b]AND log_visit.visit_last_action_time > '2011-06-07 09:11:07'  
			AND log_visit.visit_last_action_time >= '2011-06-05 16:00:00'[/b]
				
				ORDER BY idsite, visit_last_action_time DESC
				LIMIT 10
			) AS sub
			GROUP BY sub.idvisit
			ORDER BY sub.visit_last_action_time DESC;

you can check it out in attachment. my question is whether the sql is right or not.
thanks


(Thomas Seifert) #2

Do you have any problem with it or is that just a generic “is my system working correctly” question?


#3

I think it’s a problem, the following are my reasons:
1: I want to archive the data of the last 1 or day, and I configure the archive.sh : CMD="$PHP_BIN -q $PIWIK_PATH – module=API&method=VisitsSummary.getVisits&idSite=$idsite&period=$period&date=last2&format=xml&token_auth=$TOKEN_AUTH"
2:I monitor the DB, found the sql I mentioned before was excuted. I think the logical of the sql statement is not well, it should be
AND log_visit.visit_last_action_time < '2011-06-07 09:11:07’
AND log_visit.visit_last_action_time >= ‘2011-06-05 16:00:00’

ps:
1: According to my usecase, the configuration is right or not.(date=last2)
2:my sites have 2 million pv per day, can you give me some advice to improve the performance

thanks for your attention


(Matthieu Aubry) #4

Piwik archives data for each day, and then will sum each day of data for the last2 days.

2Million pages per day will not work well on Piwik I’m afraid!! Usually, it works fine up to 200k pages…


#5

thanks for your attention