Performance issue with serendipity_referrers

Discussion corner for Developers of Serendipity.
Post Reply
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Performance issue with serendipity_referrers

Post by ads »

Hi,

the table "serendipity_referrers" contains all referrers which ever visited a blog. During performance analysis of my blog i found out, that the runtime for this query:

Code: Select all

SELECT scheme, host, SUM(count) AS total
  FROM serendipity_referrers
 GROUP BY scheme, host
 ORDER BY total DESC, host
   LIMIT 10
is constantly increasing, because this query has to scan the whole table each and every time. That's bad :mrgreen:

Can this query somehow modified to not scan the whole table but instead use an index?
garvinhicking
Core Developer
Posts: 30022
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: Performance issue with serendipity_referrers

Post by garvinhicking »

Hi!

AFAIK a full table scan is required due to the "SUM(count)", so it cannot be sped up with an index. You could delete old referrers though:

DELETE FROM serendipity_referrers WHERE day <= (unix timestamp of the day you want all earlier entries be removed)

HTH,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Re: Performance issue with serendipity_referrers

Post by ads »

Yes, of course, i could do that. But this only fixes the problem for my blog - and i don't have a very high number of visitors. The same problem applies to every s9y blog so it would make sense to find a general solution.

What about a regular (cron)job which removes old entries, based on some configuration settings in the blog?
Don Chambers
Regular
Posts: 3652
Joined: Mon Feb 13, 2006 2:40 am
Location: Chicago, IL, USA
Contact:

Re: Performance issue with serendipity_referrers

Post by Don Chambers »

How about turning off the option to enable referrer tracking?
=Don=
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Re: Performance issue with serendipity_referrers

Post by ads »

Dom Chambers: you asked this question to me?
Don Chambers
Regular
Posts: 3652
Joined: Mon Feb 13, 2006 2:40 am
Location: Chicago, IL, USA
Contact:

Re: Performance issue with serendipity_referrers

Post by Don Chambers »

ads wrote:Dom Chambers: you asked this question to me?
Yes, sorry. My suggestion is only valid if you do not need the info. I have personally never found any significant benefit from this particular table, so I always disable the configuration option which enables referrer tracking. I presume this eliminates the query, but have never looked at the code to be certain.
=Don=
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Re: Performance issue with serendipity_referrers

Post by ads »

I want this feature enabled. But scanning through all old rows seems useless.
garvinhicking
Core Developer
Posts: 30022
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: Performance issue with serendipity_referrers

Post by garvinhicking »

Hi!

Currently, there are a few tasks that you need to perform on s9y database tables that no plugin currently does. Most importantly, truncating spamblocklog tables, referrers and visitors.

I'd appreciate if someone wants to develop a maintenance plugin; I even believe someone did a rough work on that a few months ago, but I haven't heard of any final results...

A cronjob is hard because most s9y users do not know how to set one up, and tasks that can take up a long time most probably won't work with the s9y crontab plugin...

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Re: Performance issue with serendipity_referrers

Post by ads »

Added to my basket.

Let's see, how deep i can dive into the s9y code ...
onli
Regular
Posts: 2825
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: Performance issue with serendipity_referrers

Post by onli »

I made such a plugin. Not a cronjob, but a cleanup on click:
http://board.s9y.org/viewtopic.php?f=10 ... ndb#p92554

If there is demand, I'd enhance it if necessary if someone tests it (I did on my system and it worked), introduce it on the plugin-system and eventually upload it to spartacus.
ads
Regular
Posts: 93
Joined: Sun Oct 29, 2006 11:39 am

Re: Performance issue with serendipity_referrers

Post by ads »

Some configuration settings would be nice, also a way to call this script from cron (like the update checker for the plugins).

I would love to test this.
onli
Regular
Posts: 2825
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: Performance issue with serendipity_referrers

Post by onli »

Hi
Improved it a bit - added the referrers-table and made the tables to clean selectable. What's missing is the cronjob, maybe a better optical design, and a configurable timespan of entries not to be deleted. Anything else?

I won't work on it over the long weekend, so thought it's a good time to show the latest version and get your feedback. Are tables missing, working so far for you? You probably are able to help with the sql :)
Attachments
serendipity_event_dbclean-0.1.9.tar.gz
(2.55 KiB) Downloaded 435 times
Post Reply