Dreamhost is slandering me! They think I suck and have no problem telling people as much. Seriously; I write bad code by them. How do I know? Because Chris Duke over at AppModo told me so. Problem is, Dreamhost was 100% right. Yup; I fucked up.

On Monday I got an email from Chris asking for some help with one of the WordPress plugins I wrote; wp-click-track. Chris was cool enough to let me post the message he received from Dreamhost:

Hello,

I’m writing you about your database “appmodo”, please write me back as soon as possible.  There seems to be a plugin you are running that is poorly coded (missing table joins) and this 1300+ second query that it is running is causing high server load spikes. If this plugin is not fixed, or disabled, and it causes server problems, we may need to disable the wp_tracking_clicks and wp_tracking_links tables.  You can write me at justin@dreamhost.com.

The problem queries:

# Query_time: 2181  Lock_time: 1115  Rows_sent: 1  Rows_examined: 38703 use appmodo;

SELECT date_format(click_date,"%Y-%m-%d") AS first_click,
date_format(click_date,"%j") AS day_of_year FROM wp_tracking_clicks tc,
wp_tracking_links tl WHERE tc.click_id != '0'  GROUP BY first_click ORDER
BY first_click DESC LIMIT 1;

# Query_time: 1995  Lock_time: 924  Rows_sent: 1  Rows_examined: 38703 use appmodo;

SELECT date_format(click_date,"%Y-%m-%d") AS first_click,
date_format(click_date,"%j") AS day_of_year FROM wp_tracking_clicks tc,
wp_tracking_links tl WHERE tc.click_id != '0'  GROUP BY first_click ORDER
BY first_click DESC LIMIT 1;

# Query_time: 1387  Lock_time: 309  Rows_sent: 1  Rows_examined: 38703 use appmodo;

SELECT date_format(click_date,"%Y-%m-%d") AS first_click,
date_format(click_date,"%j") AS day_of_year FROM wp_tracking_clicks tc,
wp_tracking_links tl WHERE tc.click_id != '0'  GROUP BY first_click ORDER
BY first_click DESC LIMIT 1;

# Query_time: 2016  Lock_time: 930  Rows_sent: 1  Rows_examined: 38703 use appmodo;

SELECT date_format(click_date,"%Y-%m-%d") AS first_click,
date_format(click_date,"%j") AS day_of_year FROM wp_tracking_clicks tc,
wp_tracking_links tl WHERE tc.click_id != '0'  GROUP BY first_click ORDER
BY first_click DESC LIMIT 1;

Thanks!
Justin K

Justin did a nice job highlighting what the issue was; I didn’t join the tables together so it would take a really long time to complete the scans if there were a lot of rows in the tables. Another reason to always develop with large data sets; something I obviously didn’t do with wp-click-track.

Your application is useful and popular. Your users love it. Your users love you. But over the next week, something curious happens. As people use the application, it gets progressively slower and slower. Soon, the complaints start filtering in.  Within a few weeks, the app is well-neigh unusable due to all the insufferable delays it subjects users to—and your users turn on you.

Anyway, there was no excuse for that; this was shoddy on my part. The only explanation I have is poor quality on my part.

This particular bug affected the line chart on the admin dashboard. Ever wonder why that particular graph took forever to load? Yup, that’s why.

What’s funny, to me at least, was that I had already fixed this particular issue; I just hadn’t released it yet. This particular bug was planned to be released with 0.8 but 0.8 is taking longer to complete than I initially thought it would. So the bug fix languished and sat there. Unreleased.

So yeah, wp-click-track 0.7.1. It’s a good release to get.