My website become quite slow recently so I started investigation. Installed devel module and looked at the timings... Looks like the whole slowdown is caused by the counter module which executes 16 SQL queries on each page reload and some queries are quite slow. I believe some of them could be optimised. Below is a copy of the queries I have got from devel module:

1120.221    SELECT counter_date FROM counter ORDER BY counter_date DESC LIMIT 1

0.261    INSERT INTO counter (counter_ip, counter_date, counter_page) VALUES ('81.142.228.219', '2009-11-17 23:21:11', '/admin/settings/devel')

0.261    UPDATE counter_data SET counter_value = counter_value+1 WHERE counter_name='site_counter'

0.241    SELECT * FROM counter_data ORDER BY counter_name

1998.841    SELECT count(*) as total FROM (SELECT counter_ip FROM counter GROUP BY counter_ip) c

0.281    UPDATE counter_data SET counter_value='102664' WHERE counter_name='unique_visitor'

0.31    SELECT count(*) as total FROM users WHERE access<>0 and uid<>0

0.181    UPDATE counter_data SET counter_value='48' WHERE counter_name='registered_user'

0.311    SELECT count(*) as total FROM users WHERE access=0 and uid<>0

0.171    UPDATE counter_data SET counter_value='44' WHERE counter_name='unregistered_user'

0.311    SELECT count(*) as total FROM users WHERE status=0 and uid<>0

0.11    SELECT count(*) as total FROM node WHERE status=1

0.161    UPDATE counter_data SET counter_value='476' WHERE counter_name='published_node'

0.091    SELECT count(*) as total FROM node WHERE status=0

0.161    UPDATE counter_data SET counter_value='12' WHERE counter_name='unpublished_node'

1079.451    SELECT counter_date FROM counter ORDER BY counter_date ASC LIMIT 1

Maybe there is a way to periodically cleanup the database in such a way to still receive proper statistics but to shorten the query execution time....

Overall very good and useful module, thank you.

Comments

kobit’s picture

kobit commented
Category: feature » bug

Just a follow up on the issue....
I have adjusted configuration settings, set Delay before refresh counter data to 30 seconds, Replace 'Since' value with this string: with some initial date but it looks like the module still executes all the queries on each page reload.

For some reason it doesn't respect the 30 seconds delay, and it still queries the database for the data of the first insert (a few times actually). I think what could really help (at least in my case) would be just a real caching the results for the time set instead of loading the data on each page reload.

drupalnesia’s picture

drupalnesia commented

@kobit: interested. i will install devel module to investigate the performance.

Here are the insteresting queries:
-----------------------------------------------------------
1120.221 SELECT counter_date FROM counter ORDER BY counter_date DESC LIMIT 1
1998.841 SELECT count(*) as total FROM (SELECT counter_ip FROM counter GROUP BY counter_ip) c
1079.451 SELECT counter_date FROM counter ORDER BY counter_date ASC LIMIT 1
-----------------------------------------------------------

I think I can improve the performance based on your info. Wait for next release of Counter module soon. Thanks for detail report.

drupalnesia’s picture

drupalnesia commented

6.x-2.3: Bug fix: Performance, add index to counter: counter_date and counter_ip

In my test Counter module performance increase 300%, and only requires < 1 s to execute the slow queries before, as shown below:

-----------------------------------------------------------
0.6 1 SELECT counter_date FROM counter ORDER BY counter_date DESC LIMIT 1
0.77 SELECT count(*) as total FROM (SELECT counter_ip FROM counter GROUP BY counter_ip) c
-----------------------------------------------------------

Please test and report again, thanks.

drupalnesia’s picture

drupalnesia commented
Status: Active » Closed (fixed)

Redundant, look at this post only: http://drupal.org/node/637788