WordPress.org

Ready to get started?Download WordPress

Forums

AdRotate
[resolved] AdRotate query issues (easily solved too!) (21 posts)

  1. Trionic Labs
    Member
    Posted 2 years ago #

    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/

  2. NPSites
    Member
    Posted 2 years ago #

    yea thats interesting.. i should try that maybe this weekend.. indexing does speed querys thanks for sharing

  3. Arnan de Gans
    Member
    Posted 2 years ago #

    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!

  4. Newchik
    Member
    Posted 2 years ago #

    Where do you put the above code?

  5. Trionic Labs
    Member
    Posted 2 years ago #

    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

  6. Newchik
    Member
    Posted 2 years ago #

    Thank you- I got it done :)

  7. taeke
    Member
    Posted 2 years ago #

    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

  8. taeke
    Member
    Posted 2 years ago #

    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

  9. Arnan de Gans
    Member
    Posted 2 years ago #

    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

  10. taeke
    Member
    Posted 2 years ago #

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

  11. Arnan de Gans
    Member
    Posted 2 years ago #

    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!

  12. phirefly
    Member
    Posted 1 year ago #

    @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! :)

  13. Arnan de Gans
    Member
    Posted 1 year ago #

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

  14. ZohraS
    Member
    Posted 1 year ago #

    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!

  15. Arnan de Gans
    Member
    Plugin Author

    Posted 1 year ago #

    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.

  16. ZohraS
    Member
    Posted 1 year ago #

    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?

  17. ZohraS
    Member
    Posted 1 year ago #

    Sorry, this is the one it changed:

    Time: 0.5ms (0.0004580020904541s)
        Query: SELECT <code>timer</code> FROM <code>wp_adrotate_tracker</code> WHERE <code>ipaddress</code> = '41.225.162.15' AND <code>stat</code> = 'i' AND <code>bannerid</code> = '5' ORDER BY <code>timer</code> DESC LIMIT 1;
        Function: W3_Db->query()
  18. Arnan de Gans
    Member
    Plugin Author

    Posted 1 year ago #

    I see nothing wrong with a 52ms response on a larger table. Sure it could be faster but your 58s loading time is not caused by that.

  19. ZohraS
    Member
    Posted 1 year ago #

    200K rows for 3 ads seems right for you?

    I pretty much tried everything, and the little snippets of code from here already helped a LOT.

    The site is being monitored, how about I come back tomorrow, after 24 hours of the change, and I'll let you know how the responsiveness etc has been impacted, and we'll work from there?

  20. Arnan de Gans
    Member
    Plugin Author

    Posted 1 year ago #

    That's what i said, the little snippets from this thread have been part of AdRotate for months. Added in version 3.7 released on July 5 2012. Look at the changelog.

    It's no use adding them again. Hence why you had a duplicate key in the first place.

    Version 3.8 will add a bunch more to further increase speed.

    If your site is slow, sure if the site is has tons of visitors AdRotate can cause a slowdown. But creating double indexes will not solve your problem.

    Did you look at looping JS, too? Often that's a problem. Large (megabytes) images, too.

  21. ZohraS
    Member
    Posted 1 year ago #

    Arnan, from what I saw, the little snippet added by taeke in the php wasn't added. I also added that one earlier. I'm looking at the stats now, and it's like a miracle happened. I've had 2 "spikes" in the last 9 hours. Both of them were about 8000ms (like I said before, the older ones were 58000ms). If the 4 coming tests (usually HUGE spikes at 9:13, 9:44, 10:24 and 10:45) come up the same, I'll probably be dancing like a dumbass.
    No looping JS, already looked. I pretty much already looked at anything that would be the cause, and came up empty, which was frustrating as hell. I've developed thousands of WP sites, I usually can tell where the issue is, but this one's got me completely stumped. I did a couple of other things, but, for some reason, the second I added taeke's code and cleaned the old data, the site started flying.
    Trust me, I'm also confused as to why a plugin would cause that, but this is what I'm seeing at the moment. And dear god, I hope it lasts, cause then, no moving to a new server, and no work during the holidays for me. :) I'll keep you posted!

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags