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.
October 27, 2005 at 11:01 am
For what it’s worth, I paid for the lifetime hosting,but, backed out because of lack of service. They happily took the money right away, but, I couldn’t use the service for days after they initially set it up. They were nice enough to refund the money however. It could be that they were experiencing growing pains, but, at pair.com, it just works.
October 28, 2005 at 8:50 am
Actually it only means that it is not slow/heavy queries that are causing the resource consumption (did they say that’s what they based it on?).
It might be that you’re just using many queries per pageview, or maybe your site is getting a couple times more traffic(pageviews) than the other sites on the server which made your site stick out in terms of resource usage?
This is only possible with the mysqli interface, which besides still being relatively new, from my POV is only available at a fraction of hosting companies (it’s only for mysql4.1 and up, too). And I haven’t yet seen many (actually “any”) php/mysql open source projects that make use of that feature. But then, I don’t try out many of them either…
October 28, 2005 at 8:56 am
err, did I say open source? Scratch that from my last sentence. 😉
October 29, 2005 at 10:33 pm
Hi Rafe, this is the error I got one time when I loaded your site:
SQL/DB Error – [User ‘rafecodb’ has exceeded the ‘max_questions’ resource (current value: 3600)]
That error means that you exceeded 3600 queries in an hour. I’m not sure if that was something put in place by your host after their complaint or before, but it seems a bit restrictive considering that putting together a page requires multiple queries.
I know that TextDrive is both a good and capable host and there are a lot of people on their servers who run Movable Type. In fact, TextDrive is one of the few hosts who, to my knowledge, support the excellent FastCGI/lighttpd which happens to make Movable Type SCREAM. MT and TextDrive should really play very well together.
Anyhow, a default MT install should defintely NOT be using a lot of resources on the dynamic side. There is a threshold at which we don’t recommend against dynamic publishing but I can’t imagine for the life of me that you are anywhere near that threshold. We’re talking a MASSIVE number of hits in the league of our most trafficked customers.
Can you tell me (either here or in email) what plugins you have installed? Perhaps there is an one which is making a lot of unnecessary queries, in which case we could help the plugin author tune it.