AdRotate query issues (easily solved too!)

  • Resolved Trionic Labs

    (@blogrescue)


    13 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

    https://www.ads-software.com/extend/plugins/adrotate/

Viewing 5 replies - 16 through 20 (of 20 total)

  • 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()
    Plugin Author Arnan de Gans

    (@adegans)

    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.

    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?

    Plugin Author Arnan de Gans

    (@adegans)

    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.

    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!

Viewing 5 replies - 16 through 20 (of 20 total)
  • The topic ‘AdRotate query issues (easily solved too!)’ is closed to new replies.