When TextDrive notified me that my site is a big resource hog and that they had to throttle my usage of MySQL, I asked them for the slow query log for my site. Whenever a database query takes more than two seconds, MySQL automatically logs it to the slow query log, so when you’re having database-related performance issues, it’s the first place to look. A TextDrive sysadmin copied the slow query log for my queries into my home directory — it’s a 700k file.
Needless to say, this seemed like a big clue that my site really was having big problems. When I started looking at the file, I didn’t see any horrible looking queries. The ones that were in there would, for the most part, only be slow if the database were not properly indexed. The next step was to look at the database to make sure that it is properly indexed. I would have been shocked if Six Apart failed in this regard, and a little investigation revealed that their indexes are fine. In fact, when I started running the queries from the slow query log in the MySQL client, they returned their results in times like .02 seconds rather than the 9 or 10 seconds I sometimes saw in the slow query log.
What this means to me is that I’m getting blamed for problems that aren’t my fault. Bottom line, I think that my brief but fractious relationship with TextDrive will be ending soon. And to think I came this close to paying $400 for a lifetime hosting agreement with these guys.
On a related note, Movable Type does not seem to use parameterized queries. That can leave you vulnerable to SQL injection attacks if you’re not extremely careful when you sanitize user input. I’m a bit surprised by that.