Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#2220 closed Bug (fixed)

Visitor tracking becomes really slow on large installation (v1.2)

Reported by: pdfforge Owned by:
Priority: critical Milestone: Piwik 1.3
Component: Core Keywords:
Cc: Sensitive: no

Description

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:1 Changed 3 years ago by pdfforge

a first info I would be interested in is if it is safe to move data to an archive table or if it affects the accuracy of the reports.

comment:2 Changed 3 years ago by vipsoft (robocoder)

  • Milestone set to 1.3 - Piwik 1.3

Reports are affected if raw data (e.g., piwik_log_visit) is moved/purged before archives have been generated for the period (e.g., unique visitors for the past month or week).

Are there a lot of config_id collisions?

Is order important in the index? e.g.,

 INDEX index_idsite_datetime_config (idsite, visit_last_action_time, config_id)

vs

 INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time)

comment:3 Changed 3 years ago by vipsoft (robocoder)

To answer my own question, yes, order of columns is important in the index. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

We'll add the latter index (above), and see if there other queries that depend on the former index. (If not, we can remove that one.)

comment:4 Changed 3 years ago by vipsoft (robocoder)

It looks like the archiving queries benefit from the former index, but can probably be simplified by removing the config_id column. So index_idsite_datetime_config would be replaced by these indexes:

INDEX index_idsite_datetime (idsite, visit_last_action_time)

INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time)

comment:5 Changed 3 years ago by matt (mattab)

vipsoft I think the same as http://dev.piwik.org/trac/ticket/2220#comment:4

indeed order is very important in INDEXes, sorry I missed this issue with new schema in 1.2...

pdfforge, would be able to test these INDEXes by any chance? It would be great to test on your high traffic Piwik the following

  • Shut down apache to stop tracking - or set in the config file:
    [Tracker] 
    record_statistics			= 0
    
  • Run the following query to set the correct INDEXes
    ALTER TABLE piwik_log_visit 
    DROP INDEX index_idsite_datetime_config,
    ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
    ADD INDEX index_idsite_datetime (idsite, visit_last_action_time)
    

I think this should fix the issue.

However it is still not ideal since the INDEX index_idsite_config_datetime will be rather large, but this will not be an issue once we can implement log purge #5

pddfforge, please let us know if you can test these changes, thanks!

comment:6 Changed 3 years ago by pdfforge

It made a slight improvement. Before the update, mysql had 150% CPU-Utilization and caused a load average of 2.5. Now it is 0.7% and 0.2 util.

that's really significant... Thank you very much!

I will keep watching how it develops over the day, but it looks very good.

comment:7 Changed 3 years ago by vipsoft (robocoder)

That looks like an idle box. Did you re-enable tracking after the index change, i.e., record_statistics=1 ?

comment:8 Changed 3 years ago by pdfforge

yes, I re-enabled tracking and the cpu usage really is very low. The above query now only checks one row instead of 100k. Most of the time is now spent with the php processes.

The data also look good (100k visitors yesterday spread over the whole day in server time).

comment:9 Changed 3 years ago by matt (mattab)

Thx for the follow up. Glad it's all working fine again!

comment:10 Changed 3 years ago by matt (mattab)

pdfforge do you still experience very good performance since the INDEX change? there is no other problem?

comment:11 Changed 3 years ago by matt (mattab)

  • Resolution set to fixed
  • Status changed from new to closed

(In [4204]) Fixes #2220

  • Dropping unused INDEX index_idsite_idvisit (the primary key will do just as good, no need for this INDEX)
  • Dropping bad INDEX & adding proper one, as per discussion in ticket

comment:12 Changed 3 years ago by pdfforge

@matt: yes, the performance is still excellent with cpu load around 0.3 oder 0.4. Thank you very much for this quick fix

Note: See TracTickets for help on using tickets.