MySQL Abuse

M_i · 2006-06-28 08:39:31

Hello all,

I’ve been running my (very modest) site on Textpattern for a couple of months now, without any problems, but today my host informed me it’s putting too much of load on their server, and asked me to please optimize my MySQL queries. They also sent me a file with evidence of my abuse, which I’ve pasted in below.

My MySQL knowledge is completely based on trial & error: as soon as it works, I’m happy. And using Textpattern, I never needed anything more – Textpattern took care of all database interaction for me. So, frankly, I don’t have the first clue about what’s going wrong here.

Any help will be most appreciated!

- M_I

<br />
These are the plugins I use:

ajw_admin_workflow
ajw_clean_feed
bos_author
chh_article_custom
chh_article_lib
chh_related_articles
dca_pop
EOP Manager
etz_pg
hak_article_image
mdn_recent_comments_by_section
Newsletter Manager
ob1_title
rss_admin_db_manager
rsx_frontend_edit_link
sed_comment_pack
swf_if_empty
zem_contact_lang
zem_contact_reborn

<br />
The offending queries:

Sencer · 2006-06-28 09:56:42

There’s nothing especially bad about those queries. In fact most of them are very simple, and recurring SELECTs that would probably be handled by the mysql query cache (at least if you are running mysql4.0 and up). I wonder how they came up with that list… There’s also a few quries that look like they are not from Textpattern at all, like:

SELECT p.*, m.uid, m.fam, m.username, m.groupid, m.regdate, m.lastactivity, m.posts, m.digestposts, …

My advice w.r.t to textpattern would be the following:

Update to the latest version of textpattern, if you haven’t already done so
Turn on testing mode in preferences, and then check the bottom of the source code of your generated pages on the front. There will be information in the comments, like:






What will be interesting to you is the number of queries and the query time. Given the flexibility of textpattern, it is possible to create pages in textpatter that generate a lot of queries, epsecially when using some plugins. You should check all the different pages. You will likely find a few pages that have a high number of queries. Now you have two choices, either you’ll try to redo the page in a different way that uses less queries, or you can use a plugin like zem_cache (or asy_jpcache) that caches some stuff. zem_cache has the advantage that it can be applies very slectively (it’s a txp-tag and only caches that which you wrap in it), and that it is easier to install. asy_jpcche is usually better on sites that have a lot of traffic (also it can only cache full pages and can only be applied to the whole textpattern site).

M_i · 2006-06-28 10:42:26

Sencer,

Thank you very much for you help. I’m glad there’s nothing particularly wrong with the queries my host gave me – I was really puzzled and started to doubt myself. Now, I’m rather starting to doubt my host…
I think that one query you quote, isn’t even mine at all, but from a user called “lawison_ssttc”. Not that finding out that “it’s not me, but them,” is all that comforting for the future health of my site.. but anyway;

I switched to testing mode and checked my pages, as you suggested. The highest number of queries I could find was 45 – is that too much? What is a reasonable number to strive for?

Caching sounds like a good option. I’ll give that a try.

Thanks again!

wet · 2006-06-28 11:02:58

From my experience, even a modest template with no special structure runs 30 to 40 queries. If that is a case for caching depends mainly on the number of pageviews. Do you have any estimates on that figure?

Last edited by wet (2006-06-28 11:03:09)

Sencer · 2006-06-28 11:09:44

The highest number of queries I could find was 45 – is that too much? What is a reasonable number to strive for?

There is really no one answer fits all, it all depends on what is displayed on the page (complex pages usually require more queries) and what your host is wiling to tolerate. Also I may have given the wrong impression by focusing too much on the number – it’s just that’s the place where one can sport something is wrong the easiest. A lowe number of queries doesn’t necessairly (not even usually) mean everything is ok. It can also be certain individual queries that are problematic/slow/cause problems.

You can enable debug mode, and see more info about how the page is assembled and how individual queries do (this is especially interesting if you have a high query time as well – it will help you find out whether it’s a single query slowing things down, or whether all queries shown are equally slow).

If I was you, I’d experiment with zem_cache to bring down the number of queries, and I would try to encapsulate especially the parts that deal with complex/slow queries, or plugins, or parts that change very seldomly (IIRC you can adjust the timeout for each cached block).

It is possible that your host is mistaken (I’ve seen it happen multiple times), but it can never hurt to cooperate, make some effort and show good will and point to concrete things you have done to improve the situation. If your trust is shaken you may still look for alternative hosts in the medium-term.

@wet: The sites I run on txp usually have around 20 queries – without any caching.

Last edited by Sencer (2006-06-28 11:12:29)

Mary · 2006-06-28 11:16:35

@wet: The sites I run on txp usually have around 20 queries – without any caching.

Ditto. I usually run quite minimal sites though.

M_i · 2006-06-28 11:23:20

wet wrote:

From my experience, even a modest template with no special structure runs 30 to 40 queries. If that is a case for caching depends mainly on the number of pageviews. Do you have any estimates on that figure?

The total number of pageviews is about 2000 per day for the whole site; the most popular pages up to 150 per day.

M_i · 2006-06-28 11:38:05

Sencer wrote:

You can enable debug mode, and see more info about how the page is assembled and how individual queries do (this is especially interesting if you have a high query time as well – it will help you find out whether it’s a single query slowing things down, or whether all queries shown are equally slow).

Right. I’ll investigate.

If I was you, I’d experiment with zem_cache to bring down the number of queries, and I would try to encapsulate especially the parts that deal with complex/slow queries, or plugins, or parts that change very seldomly (IIRC you can adjust the timeout for each cached block).

I’ll do that. Most of my pages are nearly static for the most part anyway.

It is possible that your host is mistaken (I’ve seen it happen multiple times), but it can never hurt to cooperate, make some effort and show good will and point to concrete things you have done to improve the situation. If your trust is shaken you may still look for alternative hosts in the medium-term.

Oh yes, I’m being very cooperative. Even got all of you to cooperate! (and really- I’m ever so grateful for that! Love this forum!)

And my host is crap, I know that. I’ve thought of moving so many times before… but they’re so cheap.
But then again, if you add up all the time I’ve spent dealing with situations like this, what’s an extra $100 or so a year?

@wet: The sites I run on txp usually have around 20 queries – without any caching.

Most of my pages have around 20 queries too. Just this one page with a list of the latest articles clocked up 45.
Guess that’s my starting point for further investigations, eh?

Cheers and thanks again,

mi

net-carver · 2006-06-28 11:51:06

M_i

Has your ‘heavy’ page got a lot of comments on it? Sounds like it’s just an article list.

Thanks,

M_i · 2006-06-28 12:09:12

net-carver wrote:

Has your ‘heavy’ page got a lot of comments on it? Sounds like it’s just an article list.

Yup, it’s an article list. But an article list with a lot of ifs and buts (for example, articles in one section display differently from articles in another, since some in sections articles are mainly text while others only images), and the same page also sports a txp:recent_comments list. It’s the page with all things new.

That page uses 45 queries, while very similar pages listing new articles in only one section (without the recent comments) use only 25.

So there’s a lot of room for improvement there, especially since that site-wide article list is of course the page with the highest number of hits…

net-carver · 2006-06-28 13:31:39

M_i

Just out of interest, and if you have time to take a look, could you let me know how many queries are you getting on a heavily commented page (plus the comment count on that page)?

I see you are running my comment pack as part of the setup and I have been considering optimising some of its checking so I’m kinda curious about this.

Thank you.

M_i · 2006-06-28 13:47:14

net-carver wrote:

Just out of interest, and if you have time to take a look, could you let me know how many queries are you getting on a heavily commented page (plus the comment count on that page)?

Your comment pack is the most recent addition I made – hope that didn’t tip the scales ;)

I tried to find two articles, one with, one without comments, that were otherwise identical in complexity. The one with comments, 9 in this case, came up with 43 queries; the one with 0 comments, needed 34.

One per comment. Seems fair.

Textpattern CMS

Textpattern CMS support forum

#1 2006-06-28 08:39:31

MySQL Abuse

#2 2006-06-28 09:56:42

Re: MySQL Abuse

#3 2006-06-28 10:42:26

Re: MySQL Abuse

#4 2006-06-28 11:02:58

Re: MySQL Abuse

#5 2006-06-28 11:09:44

Re: MySQL Abuse

#6 2006-06-28 11:16:35

Re: MySQL Abuse

#7 2006-06-28 11:23:20

Re: MySQL Abuse

#8 2006-06-28 11:38:05

Re: MySQL Abuse

#9 2006-06-28 11:51:06

Re: MySQL Abuse

#10 2006-06-28 12:09:12

Re: MySQL Abuse

#11 2006-06-28 13:31:39

Re: MySQL Abuse

#12 2006-06-28 13:47:14

Re: MySQL Abuse

Board footer