Huge Query Causing Server Load Problems - Host disabled database

angelar · 2008-02-01 00:04:05

I have been using Textpattern for a while now, and love it! But recently, my web host had to disable my database because he said the main index.php page had way too many queries. The web host tech sent me a link to a page to display the query, and told me to try and figure out how to reduce this query to a normal size. The link is below:

http://testtest123.dreamhosters.com/tmp/angroh.html

I don’t know a whole lot of database/query stuff. I am basically a web designer and HTML coder that was introduced to Textpattern a couple of years ago and who starting using databases along with Textpattern sites I created. So when I see the link above, I don’t really know what to make of it or how I would go about fixing the query.

I am also posting the code that I have on that page that is creating the query…

<txp:rss_unlimited_categories_article_list form=“default” limit=“18” category=”“ section=“article” sortby=“Posted” sortdir=“desc” time=“past” />

<div class=“gallerybottom”>
<div class=“gallerybottomleft”>
<txp:newer>« Previous</txp:newer>
</div>
<div class=“gallerybottomright”>
<txp:older>Next »</txp:older>
</div>
</div>

So I am posting here in hopes that someone will be able to help me with this problem. I am running version 4.0.3. I don’t feel good about upgrading because I am using quite a few plugins and I am worried about things not working right or having to change code, etc. but I have a feeling that may be the suggestion if that is the only fix for this.

Thanks for the help!

Last edited by angelar (2008-02-01 00:06:45)

jsoo · 2008-02-01 01:22:43

First thing I’d try is to take the plugin tag out of the page and/or contact the plugin developer. Of course if Dreamhost has already disabled the site, troubleshooting is rather contstrained, to put it mildly. Do you have a local or development version of the site you can work on?

I’d upgrade, even though it might involve some reworking. You might even find that some of what you are doing with plugins has become part of the default TXP functionality.

Good luck.

angelar · 2008-02-01 03:02:33

Well they enabled it for a little while until I can find a solution to the problem, so I am hoping I do soon!

I have contacted the creator of the plugin too.

Do you think it could be something as simple as the time=“past” in the code of that plugin? It seems like I added that in later when I was trying to do something, so if it had not been there before maybe this problem wouldn’t be happening now. I doubt it, but I really have no idea. I also don’t know how to see what that Dreamhost page above displays. I would like to see if removing that time=“past” doesn’t go through all of the past entries in the query but I wouldn’t know how to see that output.

Manfre · 2008-02-01 05:58:26

time=“past” didn’t change anything. That is a default value for the tag. What version of rss_unlimitied_categories are you using? If it’s not version 0.7.4 of the plugin, then I recommend you upgrade to it. With 0.7.4, the query you linked is not possible, which implies it was a resolved bug.

ruud · 2008-02-01 10:31:08

Without a tag trace we can only guess what is causing all the queries. Angelar, please post a tag trace
Also, post a list of the plugins you have installed. Perhaps upgrading is no problem (wait for 4.0.6, due next week)

Last edited by ruud (2008-02-01 10:32:11)

angelar · 2008-02-01 13:51:11

Thanks! Here is the tag trace. I had to omit most because it just repeated until 1748 in the ID number OR area.

<code>
[SQL (0.013466): select name from `txp_lang` where lang=‘en-gb’ limit 1]
[SQL (-0.479586): select name, data from `txp_lang` where lang=‘en-gb’ AND ( event=‘public’ OR event=‘common’)]
[SQL (0.023896): select name, code from `txp_plugin` where status=‘1’]
[SQL (0.007340): select * from `txp_section` where name = ‘default’ limit 1]
[SQL (0.007285): select host from `txp_log` where ip=‘99.129.18.10’ limit 1]
[SQL (0.008368): insert into `txp_log` set `time`=now(),page=’/’,ip=‘99.129.18.10’,host=‘adsl-99-129-18-10.dsl.rcsntx.sbcglobal.net’,refer=’‘,status=‘200’,method=‘GET’]
[SQL (0.008002): select user_html from `txp_page` where name=‘default’]
[Page: default]
<txp:output_form form=“global_header” /> [SQL (0.008859): select form from `txp_form` where `name` = ‘global_header’ limit 1] <txp:if_section name=“jobs”> [<txp:if_section name=“jobs”>: false] </txp:if_section> <txp:if_section name=“submit,increase_rank”> [<txp:if_section name=“submit,increase_rank”>: false] </txp:if_section> <txp:if_section name=“submit-firm”> [<txp:if_section name=“submit-firm”>: false] </txp:if_section>
<txp:output_form form=“global_newsletter” /> [SQL (0.005967): select form from `txp_form` where `name` = ‘global_newsletter’ limit 1]
<txp:output_form form=“global_nav” /> [SQL (0.010783): select form from `txp_form` where `name` = ‘global_nav’ limit 1]
<txp:output_form form=“random_designers” /> [SQL (0.013327): select form from `txp_form` where `name` = ‘random_designers’ limit 1] <txp:article_custom section=“designers” featured=“yes” form=“sidebar_random_designer” listform=“sidebar_random_designer” limit=“2” sortby=“rand()” sortdir=”“ /> [SQL (0.016278): select *, unix_timestamp(Posted) as uPosted from `textpattern` where 1 and Status=‘4’ and Posted < now() and Section = ‘designers’ and custom_3 like ‘yes’ order by rand() limit 0, 2] [SQL (0.512229): select Form from `txp_form` where `name` = ‘sidebar_random_designer’ limit 1] [Form: sidebar_random_designer] [article 392] <txp:zem_prblock target=”_blank” rel=“follow”> <txp:custom_field name=“url” /> <txp:article_image style=“margin-bottom:8px” /> [SQL (0.011481): select * from `txp_image` where id=‘384’] </txp:zem_prblock> [article 387] <txp:zem_prblock target=”_blank” rel=“follow”> <txp:custom_field name=“url” /> <txp:article_image style=“margin-bottom:8px” /> [SQL (0.009300): select * from `txp_image` where id=‘250’] </txp:zem_prblock>
<txp:output_form form=“global_links” /> [SQL (0.011752): select form from `txp_form` where `name` = ‘global_links’ limit 1]
<txp:output_form form=“global_news” /> [SQL (0.015219): select form from `txp_form` where `name` = ‘global_news’ limit 1]
<txp:rss_unlimited_categories_article_list form=“default” limit=“18” category=”“ section=“article” sortby=“Posted” sortdir=“desc” time=“past” /> [SQL (-0.341955): SELECT DISTINCT t.ID, tc.category_id FROM textpattern as t LEFT JOIN textpattern_category as tc ON t.ID = tc.article_id LEFT JOIN txp_category as cat ON cat.id = tc.category_id WHERE Status=4 AND ( (section = ‘article’) ) ] [SQL (1.631748): select count(*) from `textpattern` where 1=1 AND ( ID = ‘9’ OR ID = ‘9’ OR ID = ‘9’ OR ID = ‘9’ OR ID = ‘10’ OR ID = ‘10’ OR ID = ‘10’ OR ID = ‘10’ OR ID = ‘10’ OR ID = ‘11’ OR ID = ‘11’ OR ID = ‘11’ OR ID = ‘11’ OR ID = ‘12’ OR ID = ‘12’ OR ID = ‘12’ OR ID = ‘12’ OR ID = ‘12’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘13’ OR ID = ‘14’ OR ID = ‘14’ OR ID = ‘14’ OR ID = ‘15’ OR ID = ‘15’ OR ID = ‘15’ OR ID = ‘15’ OR ID = ‘16’ OR ID = ‘16’ OR ID = ‘16’ OR ID = ‘16’ OR ID = ‘17’ OR ID = ‘17’ OR ID = ‘17’ OR ID = ‘5’ OR ID = ‘5’ OR ID = ‘5’ OR ID = ‘5’ OR ID = ‘6’ OR ID = ‘6’ OR ID = ‘6’ OR ID = ‘6’ OR ID = ‘7’ OR ID = ‘7’ OR ID = ‘7’ OR ID = ‘7’ OR ID = ‘8’ OR ID = ‘8’ OR ID = ‘8’ OR ID = ‘8’ OR ID = ‘73’ OR ID = ‘73’ OR ID = ‘73’ OR ID = ‘107’ OR ID = ‘107’ OR ID = ‘107’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘380’ OR ID = ‘381’ OR ID = ‘381’ OR ID = ‘381’ OR ID = ‘381’ OR ID = ‘381’ OR ID = ‘381’ OR ID = ‘382’ OR ID = ‘382’ OR ID = ‘382’ OR ID = ‘382’ OR ID = ‘382’ OR ID = ‘382’ OR ID = ‘383’ OR ID = ‘383’ OR ID = ‘383’ OR ID = ‘383’ OR ID = ‘383’ OR ID = ‘383’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘384’ OR ID = ‘385’ OR ID = ‘385’ OR ID = ‘385’ OR ID = ‘385’ OR ID = ‘385’ OR ID = ‘385’ OR ID = ‘385’
</code>

I am using these plugins:

rss_admin_show_adv_opts – 0.1
rss_unlimited_categories – 0.7.4
zem_prblock – 0.1

Last edited by angelar (2008-02-01 13:52:02)

Manfre · 2008-02-01 14:50:39

Odd. Your trace indicates that there were multiple releases as version 0.7.4. Redownload from his site and install the plugin again.

The query in your trace. The problem is the “, tc.category_id”. This is causing each ID to appear many times in the resulting query.

$q = "SELECT DISTINCT t.ID, tc.category_id FROM ".PFX."textpattern as t
		LEFT JOIN ".PFX."textpattern_category as tc ON t.ID = tc.article_id
		LEFT JOIN ".PFX."txp_category as cat ON cat.id = tc.category_id
		WHERE ".$sections." Status=4 ".$categories.$ids.$filtersql.$time;
		$rsc = getRows($q);

The code from 0.7.4 that I downloaded last night, which does not have the same error.

$q = "SELECT DISTINCT t.ID FROM ".PFX."textpattern as t
		LEFT JOIN ".PFX."textpattern_category as tc ON t.ID = tc.article_id
		LEFT JOIN ".PFX."txp_category as cat ON cat.id = tc.category_id
		WHERE ".$sections." Status=4 ".$categories.$ids.$filtersql.$time;
		$rsc = getRows($q);

Note: This will reduce the size of 2 queries, but may not have a significant effect on the query time for each page. You should consider using some form of caching. Look in to asy_jpcache and see if that can be used with your site.

Last edited by Manfre (2008-02-01 15:29:51)

angelar · 2008-02-01 15:38:26

Thanks! I will download the plugin again and see what happens.

angelar · 2008-02-01 15:51:08

I re-installed the plugin, but it did not make the huge query go away, and then it broke parts of the site where the plugin is used, so I guess I just have to keep things as is and then pay for a dedicated server or something. I also contacted the creator of the plugin to see what he says.

Last edited by angelar (2008-02-01 15:51:55)

ruud · 2008-02-01 16:32:46

Without looking at the plugin code, I’d say that all those ID conditions are the result using 2 SQL queries instead of 1, where the results of the first are used in the second. Using just 1 query is often more efficient: let MySQL do the work instead of doing it in the script. I know that I heavily modified that plugin to make it useable on TXP.org.

masa · 2008-02-01 17:09:42

angelar wrote:

I am running version 4.0.3. I don’t feel good about upgrading because I am using quite a few plugins and I am worried about things not working right or having to change code, etc.

Just be aware, that there were substantial changes to textile from 4.0.3 to 4.0.4 and later, that have caused problems for several users including myself

Cheers Martin

ruud · 2008-02-01 17:39:45

But unless you save the articles again, you’ll only notice this in new articles. The new textile version does also fix a few bugs.

Textpattern CMS

Textpattern CMS support forum

#1 2008-02-01 00:04:05

Huge Query Causing Server Load Problems - Host disabled database

#2 2008-02-01 01:22:43

Re: Huge Query Causing Server Load Problems - Host disabled database

#3 2008-02-01 03:02:33

Re: Huge Query Causing Server Load Problems - Host disabled database

#4 2008-02-01 05:58:26

Re: Huge Query Causing Server Load Problems - Host disabled database

#5 2008-02-01 10:31:08

Re: Huge Query Causing Server Load Problems - Host disabled database

#6 2008-02-01 13:51:11

Re: Huge Query Causing Server Load Problems - Host disabled database

#7 2008-02-01 14:50:39

Re: Huge Query Causing Server Load Problems - Host disabled database

#8 2008-02-01 15:38:26

Re: Huge Query Causing Server Load Problems - Host disabled database

#9 2008-02-01 15:51:08

Re: Huge Query Causing Server Load Problems - Host disabled database

#10 2008-02-01 16:32:46

Re: Huge Query Causing Server Load Problems - Host disabled database

#11 2008-02-01 17:09:42

Re: Huge Query Causing Server Load Problems - Host disabled database

#12 2008-02-01 17:39:45

Re: Huge Query Causing Server Load Problems - Host disabled database

Board footer