Forum exceeding usage limits: SQL (Conqueries) & CPU (Mins)

[chq]

SMF Version: SMF 1.1 RC1
My forum has 667 members and 35,000 posts.

I get about 3,000 page views a day on my forum, 10,000 a day on the entire site.

My hosting plan allows for 262,144 SQL Conqueries a day. 

I'm not sure if that is a high or low amount, but i'd like to know if there are things I can do to reduce the usage, or do I have to upgrade my hosting plan because this is what happens when a forum gets large enough.

I have a coppermine gallery with my SMF forum, with 700 pictures and there are 2000 pictures attached in posts on my forums (all under 1mb). 

Any and all help/advice would be appreciated thanks.

[chq]

Also for some reason my "Seconds before an unused session timeout" was set to 999999 and i just read it shouldn't be higher than 2400.  How much of an impact does that have on the two CPU usage limits my forum is exceeding?

[Joshua Dickerson]

You have over 80 queries per page view? Yeah, that is a big no-no. There is nothing in SMF that has any of those problems so it is definitely not SMF related. Read the stickies in this board and then reply with the checklist.

[chq]

 Opened vs. Open tables:   
(table_cache)    12191.7227 (should be <= 80)

Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)

Thread cache usage:
(thread_cache_size)    0.4375 (should be >= 0.7 and <= 0.9)

Query cache prune rate:
(query_cache_size)    0.6542 (should be <= 0.05)


Queries per second:      126.1819


--------

so how do i get these things back to where they should be??

i changed the amount of space available for attachments to 0.

[chq]

on a shared server, the host is http://hosting.aplus.net/soloxr.html


mysql-server-4.1.21

[qtime]

you should change the table cache setting in the my.cnf file, do you have access to that on your shared server? The file is located in: /etc/mysql

[chq]

you should change the table cache setting in the my.cnf file, do you have access to that on your shared server? The file is located in: /etc/mysql

I'm not sure would  I be able to change it in phpmyadmin?

[青山 素子]

As you are on shared hosting, you will not be able to make those changes.

Looking at the offer for that price, it looks like that company is drastically overselling their capacity. Working out the numbers, which I have done in other posts, that 2 terabyte of transfer costs the host about $300 - $450 a month optimistically. Look at what you are paying and see if you can figure out how they make money or even break even. They can't; not without restricting their service so you never can use the transfer and disk they claim you can use.

The heavy caps on database and CPU usage are a big warning sign (what many overselling hosts use to make sure sites can't kill their overloaded systems). Are they able to tell you the exact process that is causing the high load?

Also, are you sure the limit is 262,144 a day, or is it an hour? Regardless, that would still be about 26 queries for each page view on the site, which is high for SMF, and even Coppermine. What mods do you have installed?

Also for some reason my "Seconds before an unused session timeout" was set to 999999 and i just read it shouldn't be higher than 2400.  How much of an impact does that have on the two CPU usage limits my forum is exceeding?

It shouldn't have any major impact on CPU usage with your traffic, but on a large site it could cause problems.

[chq]

You were right, just got this response from my host:

I am writing to inform you that the change the table cache
setting in the my.cnf file is something that we cannot allow.
Unfortunately we cannot allow global changes on shared hosting
servers. The only way to configure this file is if you have a
dedicated server which doesn't have the restrictions on the
shared hosting.

My table cache (with the forums in maintainence mode) right now is  15462.67 (should be <= 80).  I can't imagine what the # would be like if my forums were active.

Here are the limits regarding SQL Conqueries:

Allowed per week: 1,835,008
Allowed per month: 7,340,032

---------------

In the past week my forums have been in maintainence mode the entire time and I still used 879,223 Conqueries which is 48% of the allowed limit (for one week).  So putting my forums in maintainence mode has basically dropped the Conqueries basically in half. 

For example, last month i used 153% of the allowed limit for SQL Conq's...

So shutting the forums down helps...


Mods i have:

Coppermine gallery is attached (not really a mod)
Karma Mod
Users Online Mod
Customer User Field Mod

and "Lots_o_smileys" was installed but i didn't like so i think i deleted the smileys from my server, but i can't uninstall it from the list (prob cuz the files aren't there)


So besides paying like $400 a year for a larger server or a dedicated one, WHAT can I do??

My forum really isn't that big...

One thing I was thinking wasn't helping was being re-spidered like 35 search engines every other week... but I don't think that would be causing this.

So any ideas what to do?
   

[chq]

My host just emailed me saying that I should get a dedicated server for $50 a month!  ($600 a year) and i was originally paying only like ($125 a year).

I just emailed them for the second time asking if they know the exact process causing the problem...I'll see what they say or if they even answer it as they avoided answering it last time.

Any suggestions?

[chq]

Page created in 0.441 seconds with 11 queries.



^^ Why so many queries for one page?  Is that a problem?

I disabled hostname lookup, will that help at all?

[KGIII]

11 is fine (or should be) and I am not going to badmouth any specific hosting company but I would seriously consider looking to see if you can find alternatives sometime soon if this behavior keeps up and can't be resolved.

[chq]

11 is fine (or should be) and I am not going to badmouth any specific hosting company but I would seriously consider looking to see if you can find alternatives sometime soon if this behavior keeps up and can't be resolved.

yeah, i did a little searching here for other people's experiences with APLUS and EVERYONE says they are horrible, and i have to agree...

any good ones you know of that would help to transfer all the info any everything?

[KGIII]

I dropped you a PM in hopes that you might beta something for me later if you are interested. I will not lower myself to those standards of bashing hosting companies but, if you are interested, you can always check www.webhostingtalk.com for reviews and to find how to avoid the latest overselling company or the likes.

[chq]

i have a website search engine on my site, and i'm thinkin that maybe that could be causing some problems as i only get 3k page views on my forums per day and 10k on the whole site so thats 7,000 page views a day just on the 1,000+ HTML pages i have, and if people search it searches those pages (and hopefully only those) but still it probably isn't helping...

[Joshua Dickerson]

What search engine?

[chq]

it came with my control panel, let me check to see if it has any name...

its just called "Website Search Engine"

here's the code

Code Select Expand

<form action="/SearchEngine/search.pl" method="get"><br><br><select name="mode"><br><br> <option value="all">Match ALLwords</option><br><br>  <option value="any">Match ANY word</option><br><br></select><br><input type="text" name="q" /><input type="submit" value="Search" /><br><br><input type="hidden" name="p" value="1" /><br><inputtype="hidden" name="lang" value="en" /><br> <input type="hidden"name="include" value="" /><br> <input type="hidden" name="exclude"value="" /><br> <input type="hidden" name="penalty" value="0" /><br></form>

I have  Log the queries to an extra file  set as ON. 

However the file for that log is only 2.5mb.  I get around 3,000-5,000 searches a month, and I have over 1,000 unique HTML pages some with pictures (maybe 40-50).

The search engine only searches the folder with the HTML pages (not any SMF files or anything else)


On the FAQ page it says this about the "how the site search engine works"

To speed up the Site Search Engine Add-on performance, it the engine makes a cache while the search is on. After the search is finished, the results are displayed on a new page and each of the searched items is followed by the relevant results. The matches are highlighted. This Site Search Engine is applicable only to non-dynamic websites.

You can manage the searches by "good" words and "bad" words. The words listed as bad words will be disregarded and will produce no results. The words listed as good ones will produce the results.

[Joshua Dickerson]

If you are concerned with that, Google has a site search.

[MazdaClubTR]

I have the same problem with the same host. All of a sudden my queries tripled in number 3 days in a row. Although I have gone through all of my settings and disabled almost any possible option, I can not drop it to the limits.

3 days ago I had too many HTTP 500 errors and after talking to host it has dropped but this time it is the queries.

Yesterday I had 31095 pages corresponding to 812350 queries which is 309%. When I try to read a post it says the post have been created using 18-21 queries (mostly 20)

Is it possible to drop this query number ?

[青山 素子]

That is quite a large number. Do you have any mods installed that display items on pages? Things such as a shoutbox, extra visual enhancements, and similar can increase the number of database queries that need to be made to display a page.