Visitor tracking becomes really slow on large installation (v1.2)
|Reported by:||pdfforge||Owned by:|
We are using piwik for a quite large installation (2.5 - 3 million visitors per month). Since the update to 1.2 we have severe problems with the performance. The problem seems to be a select statement. Here is an example from our slow query log:
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 99728 SELECT [...] FROM piwik_log_visit WHERE visit_last_action_time >= '2011-03-21 22:01:07' AND idsite = '1' AND config_id = '...' ORDER BY visit_last_action_time DESC LIMIT 1;
The problems seems to be that about 100k rows have to be examined to find a single row, though indexes are properly set and used.
To keep performance impact low, we are currently moving data from log_visitor which is older than yesterday to an archive table. At the moment we have about 250.000 entries in the table. Before the archiving, we hav 20 million with tracking started in april 2009.
Change History (12)
comment:11 Changed 3 years ago by matt (mattab)
- Resolution set to fixed
- Status changed from new to closed