WordPress.org

Support

Support » Plugins and Hacks » AdRotate » [Resolved] AdRotate query issues (easily solved too!)

[Resolved] AdRotate query issues (easily solved too!)

  • I do quite a bit of tech work, and on one site, I found that this plugin was causing serious performance issues and filling up the slow query log. Examples:

    # Time: 120214 10:16:04
    # User@Host: db_user[db_name] @ localhost []
    # Query_time: 5.142186  Lock_time: 0.000077 Rows_sent: 1  Rows_examined: 145277
    SELECT SUM('clicks') as 'clicks', SUM('impressions') as 'impressions' FROM 'wp_adrotate_stats_tracker' WHERE 'ad' = '591';

    145k rows to read for a single ad and I’m seeing this query fire multiple times a second. And the worst thing is that adding a very simple index solves the problem forever and drops the number of rows to 700 or so.

    CREATE INDEX ad_idx ON wp_adrotate_stats_tracker (ad);

    Here is another:

    # Time: 111007  9:01:19
    # User@Host: db_user[db_name] @ localhost []
    # Query_time: 8.488923  Lock_time: 0.000106 Rows_sent: 1  Rows_examined: 49715
    SELECT COUNT(*) FROM 'wp_adrotate_tracker' WHERE 'ipaddress' = '66.249.72.14' AND 'stat' = 'i' AND 'timer' < '1317999371' AND '>bannerid' = '55' LIMIT 1;

    By adding the following index, It is now examining 1 row instead of 50k:

    CREATE INDEX ip_idx ON wp_adrotate_tracker (ipaddress);

    I didn’t explore the other tables, but just a few simple indexes would make a staggering performance improvement. (And other plugin users – if the developers don’t add the indexes, you can still add them yourself to your own install.)

    Ed

    http://wordpress.org/extend/plugins/adrotate/

Viewing 15 replies - 1 through 15 (of 20 total)
  • yea thats interesting.. i should try that maybe this weekend.. indexing does speed querys thanks for sharing

    We’ll look into your idea and do some tests.

    I’m not really familiar with indexes. Can you email me at info@adrotateplugin.com so we can discuss this further? If you have time that is. It would be much appreciated!

    Thanks!

    Where do you put the above code?

    Newchik – there are two ways to do it.

    First, if you have shell access, you can run mysql from the command line and enter it in that interface. (That is pretty tricky so keep reading)

    Second, if you have phpMyAdmin, select your database and then click the SQL tab and paste the query in the textbox and click Execute.

    Ed

    Thank you- I got it done 🙂

    Add an index on timer also,
    CREATE INDEX timer_idx ON wp_adrotate_tracker (timer);

    there is quite some improvement with an index on ipaddress and timer

    We have been experiencing loads of issues with AdRotate on one of the largest Dutch blogs,
    what seems to work is a modification of adrotate-functions.php

    On line 395 of adrotate-functions.php trackerdata gets cleaned, we noticed loads (hundreds of thousands) of ‘unknown’ ip addresses in adrotate_tracker we therefore added the following line to adrotate-functions.php:

    see pastebin: http://pastebin.com/ryesWc1j

    This immediately improved the site performance, no more locked tables or slow queries..
    Not sure if Arnan approves or sees problems by adding this line though

    This issue has been addressed in the upcoming version by not running the cleanup as much and put it on a schedule to run only once every 3 hours.

    But, the ‘unknown’ ip address didn’t stick out in our setups so perhaps it’s useful to add it as a precaution… to avoid flooding the db in the long run.

    FOr more info what i’m working on see here: http://adrotateplugin.com/page/get/changelog.php

    Yeah, I noticed the changelog and the upcoming changes, very good!
    The indexes and this particular trick solved many headaches..

    I will do some tests, however, the sites i have aren’t that heavy on traffic so for things like this i’m very dependent on community input since the mentioned issues often are not noticeable for me.
    If i don’t get feedback or complaints an issue can exist for months without me knowing about it.

    Input like this is very very very helpful!

    An idea that is forming now is to not save unknown ip’s at all, they’re useless for the timers anyway. So why bother storing them.

    Can you email me a few records of those unknown IP’s so i can write a few lines to exclude them from being saved at all? If you include real IP’s you might wanna obfuscate them.

    info@adrotateplugin.com

    Thanks!

    @aran – I am very looking forward to this release as I was going to remove Adrotate due to performance; yes…it has been great with low traffic but now I notice performance issues with bigger traffic & P3 Profiler shows Adrotate taking up 1/3 of all plugin runtime!

    For now I might deactivate, however I am very interested to know when you release your update! 🙂

    We are testing a stable beta now on various servers.
    No clear eta on when it will be released though.

    First of all, thanks so much! You’ve started solving one of the issues I was having on a site. The first index addition doesn’t work for me though, in the sql, it tells me

    The following indexes appear to be equal and one of them should be removed: ad, ad_idx

    Any idea how to make it work? I need the wp_adrotate_stats_tracker to be optimized like wp_adrotate_tracker (which dropped from like 30 megs to 250kbs!). Thanks in advance!

    Plugin Author Arnan de Gans

    @adegans

    ad_idx is not something AdRotate uses.
    Indexes have been a part of AdRotate for months now. So I’m not sure what you’re trying to do.

    Arnan, I’m trying to optimize a site that’s killing the server it’s on. The response time was at 58s at its peak.

    The wp_adrotate_stats_tracker is more than 200k rows long, and I only have 3 ads. Like I said earlier, my wp_adrotate_tracker was like 30 megs, and the little indexes I created from this thread
    dropped it down to 700 or so rows.

    This is what Debug objects tells me for adrotate queries:

    ` Time: 52.5ms (0.052487850189209s)
    Query:
    SELECT
    SUM(clicks) as clicks,
    SUM(impressions) as impressions
    FROM
    wp_adrotate_stats_tracker
    WHERE
    ad = 5
    AND thetime >= 1340409600
    AND thetime <= 1371945600
    ;
    Function: W3_Db->query()`

    That’s not right, is it? I had a similar result for wp_adrotate_tracker, and now, it’s down to this:

    Time: 0.1ms (0.00010395050048828s)
        Query: SELECT
        <code>id</code>,
        <code>bannercode</code>,
        <code>tracker</code>,
        <code>link</code>,
        <code>image</code>,
        <code>timeframe</code>,
        <code>timeframelength</code>,
        <code>timeframeclicks</code>,
        <code>timeframeimpressions</code>
        FROM
        <code>wp_adrotate</code>
        WHERE
        <code>id</code> = '5'
        AND <code>type</code> = 'active'
        ;
        Function: W3_Db->query()

    Since I added those two indexes on timer and ipaddress, my response time dropped to about 30ms, with a max of 8 secs when I was changing things around. Any clue as to why it’s doing that?

Viewing 15 replies - 1 through 15 (of 20 total)
  • The topic ‘[Resolved] AdRotate query issues (easily solved too!)’ is closed to new replies.
Skip to toolbar