Disabling "CREATE TEMPORARY TABLE"

[Yonkey]

SMF Version: SMF 1.1 RC2
Hello,

My site is recently getting a ton of traffic, and it is resulting in many forum and Apache problems, ranging anywhere from 503 errors, to Server Currently Unavaialble, to pages not showing up, to php files being sent to the browser rather than being processed, etc.  While we aren't exceeding our bandwidth or storage, we are overloading the server through SMF.

I am on SiteGround on a shared server, and this is what my webhost has said:

During the last 24 hours SiteGround has detected that your database hosted on your account postudios.com has overloaded the server several times. This makes your account potentially dangerous for the other users hosted on our standard shared server. That is why we have limited the resource usage of your account. As you can see from the logs below, the queries to the database take too long to execute:

| 6919917 | <login name> | localhost | <database name> | Query | 1 | Sending data | CREATE TEMPORARY TABLE <database name>_topics_posted_in (
PRIMARY KEY (ID_TOPIC)
)
SELECT |

taking the size of the database into account:

118768 /var/lib/mysql/<db name>

this creates the problem with the overload.

In order to have the limitation taken off you need to optimize it so that it no longer creates the server load.

I would like to disable the creation of temporary tables, but I have also asked if they knew of any other way to optimize the forum.  I am now asking you as well.  Here is my information:



    * A link to your forum.
http://www.tsl-game.com/forum/

    * A link to your status.php - You can download it from README: Checklist for performance problems
http://www.tsl-game.com/forum/status.php

    * a link to your phpinfo.php - What is phpinfo.php?
http://www.tsl-game.com/phpinfo.php

    * Is this a dedicated server or shared host. If it is a dedicated server, what spec is the server and what else runs on the server? If it is a shared host post a link to the hosts site the package you are on
Shared and the only have one package, which is indicated on their homepage: http://www.siteground.com/

    * The Number of posts your forum has.
As I post this, there are: 177,813 posts

    * The Average number of users you have online during peak time and over what time period that is over, e.g 400 users over 15 mins.
I guess between 20-30 users.  In the past few days (where it peaked and caused problems due to our game demo going public) the max users online have been: 36, 29 and 21 on July 30, 31, and August 1 respectively.

    * A list of mods which you have installed
I only use my own mods which have never impacted server performance in the past, since they are mostly cosmetic/UI fixes.  The most "intensive" mod I have made is a spoiler tag, but that would only affect posts that make use of the tag, and considering it is controlled by client-side JavaScript, I don't think that's the problem.

    * Do you still have performance issues if you use the default theme?
I haven't tried, but our current theme is based on the classic theme, just with replaced images and css, if that helps.


    * A list of the suggestions you have already implemented from the README: Checklist for performance problems topic.

    * If you're using SMF 1.0.x, make sure the "Check avatar size every time it's displayed" setting is off.  This setting was removed in 1.1.
I am using 1.1

    * Do you have eAccelerator or another optimizer installed?  What is eAccelerator?
No. And there are no optimizers installed on this server that SMF could detect.

    * Do you have a lot of posts?  If so, have you converted any of your tables to InnoDB?
I have already converted all suggested tables to InnoDB, as per this thread on it

    * Have MySQL's settings been tweaked to optimize memory usage and general efficiency?
I'm not sure, but I'm guess so...

    * If you're not on a dedicated server, are you sure others on your server aren't hurting your forum's performance?  If you're not ready for a dedicated server now, think about planning for one when your forum gets to a certain size.
I'm fairly certain it IS our site causing it, especially considering my host has said explicitly said so.  I would love to switch to a dedicated server, but the cost is out of this world.

    * Once you're satisfied of (or properly confused with) the above, if you're still having problems... simply create a new topic in this board, with the status.php file attached uploaded to your server, and some indication of peak times, what your problems are, and what you've done about them so far.
Done!


EDIT: Sorry, part of the quote got cut off.

[Ben_S]

Your only real option here is to disable search as that is what is causing the trouble. .

What search method are you using, fulltext may help if you aren't currently using it, but search is always going to be a pain on a board with more than say 100 k posts

[Yonkey]

Oh, is that all?  Hmm, actually I use the fulltext search regularly so I don't want to disable it, but is it possible for me to reconfigure it so that it does NOT search our largest boards by default?

EDIT: I updated the quote.  It was missing some information.

[Ben_S]

What is your search method set to in admin > search?

There isn't a way around it by avoiding it searching large boards are all the posts are in the same table anyway.

[Yonkey]

It's set to fulltext index.  Here's the entire info:

Space used by forum messages in the database: 57,136 KB
Space used to index messages in the database: 33,480 KB

Search index:

Fulltext index
Index: already created [remove fulltext index]
Size: 33,480 KB

Force the use of a search index: YES
Match whole words only: YES

[Ben_S]

Are you certain your log_search* tables are InnoDB, check they are listed as such with phpMyAdmin.

[Yonkey]

Interesting... two of them are, and two of them aren't:

<db name>_log_search_messages     0     MyISAM     latin1_swedish_ci     1.0 KB     -
<db name>_log_search_results    22,516    InnoDB    latin1_swedish_ci    1.9 MB    -
<db name>_log_search_subjects     9,455    InnoDB    latin1_swedish_ci    672.0 KB    -
<db name>_log_search_topics    0    MyISAM    latin1_swedish_ci    1.0 KB    -

I will convert _messages and _topics now.

EDIT: Done.  And I have just ran Optimize on those four tables as well, just in case.  Thanks again for all your help, Ben_S!

[Ben_S]

Hmm those tables are only used if using the custom smf index rather than fulltext so wont help.

[Yonkey]

Oh... ok, what else do you suggest?

Is there a way to implement a search restriction on a per-board basis?

[Harzem]

BTW, siteground looks like an overseller.

    I pay for 10GB space, 200GB traffic on a VPS at 46$/mo.
You pay for 24GB space, 800GB traffic on a shared host for 5$/mo.

Either I'm an idiot or you are really at risk of getting many many more problems from your host.

Do a google seach about "overseller", also search it here, also in webhositngtalk.com

Oversellers are evils. There is no way to configure your forum for them

Only suggestion: Find another host. Like http://www.liquidweb.com/shared/

[Ben_S]

RC3 will have serach optimisations, these will probably help.

[Yonkey]

Either I'm an idiot or you are really at risk of getting many many more problems from your host.

Actually, my previous host was a huge overseller (MidPhase).  SiteGround has actually been the best web host we have had compared to all others used over the past 4 years, and I highly recommend it for shared hosting. 

It is completely understandable for a shared hosting service to not be able to handle the kind of traffic we've been getting in the past 3 days, but until I have the money to afford a VPS or dedicated server, shared is the only thing I can afford.  So, if I can prevent exccess indexing or whatever the cause of this is, I will try that first.

Thanks for optimizing it in RC3 Ben, but is there any way you could tell me which files I need to modify now?  I need to have this problem fixed immediately on my site.

[Yonkey]

Here is what my host just said:

Hello,

In order your account became appropriate for shared hosting you should optimize your database <db name>. Here is a part from the server reports we have :

report-2006-07-31-12-38: | 6643612 | <db username> | localhost | <db name> | Query | 3 | logging slow query | UPDATE <smf table name>sessions
report-2006-07-31-12-38: | 6643620 | <db username> | localhost | <db name> | Query | 3 | init | DELETE FROM <smf table name>log_online
report-2006-07-31-12-38: | 6643629 | <db username> | localhost | <db name> | Query | 4 | updating | DELETE FROM <smf table name>log_online
report-2006-07-31-12-38: | 6643641 | <db username> | localhost | <db name> | Query | 3 | init | DELETE FROM <smf table name>log_online
report-2006-07-31-12-38: | 6643645 | <db username> | localhost | <db name> | Query | 3 | init | DELETE FROM <smf table name>log_online
report-2006-07-31-13-39: | 6655566 | <db username> | localhost | <db name> | Query | 3 | update | REPLACE INTO <smf table name>log_boards
report-2006-07-31-13-39: | 6655665 | <db username> | localhost | <db name> | Query | 3 | update | REPLACE INTO <smf table name>log_boards
report-2006-07-31-13-39: | 6655756 | <db username> | localhost | <db name> | Query | 3 | update | REPLACE INTO <smf table name>log_boards
report-2006-07-31-13-39: | 6655789 | <db username> | localhost | <db name> | Query | 2 | end | UPDATE <smf table name>log_online
report-2006-08-01-14-34: | 6907316 | <db username> | localhost | <db name> | Query | 4 | update | REPLACE INTO <smf table name>log_boards
report-2006-08-01-14-34: | 6907372 | <db username> | localhost | <db name> | Query | 2 | Sending data | SELECT
report-2006-08-01-14-34: | 6907393 | <db username> | localhost | <db name> | Query | 3 | closing tables | SELECT
report-2006-08-01-14-34: | 6907418 | <db username> | localhost | <db name> | Query | 4 | end | UPDATE <smf table name>members
report-2006-08-01-14-34: | 6907432 | <db username> | localhost | <db name> | Query | 4 | end | UPDATE <smf table name>log_online
report-2006-08-01-14-34: | 6907457 | <db username> | localhost | <db name> | Query | 4 | end | UPDATE <smf table name>log_online
report-2006-08-01-14-34: | 6907464 | <db username> | localhost | <db name> | Query | 3 | end | UPDATE <smf table name>log_online
report-2006-08-01-15-09: | 6913971 | <db username> | localhost | <db name> | Query | 13 | update | INSERT IGNORE INTO <smf table name>log_online
report-2006-08-01-15-41: | 6918941 | <db username> | localhost | <db name> | Query | 3 | end | DELETE FROM <smf table name>log_online
report-2006-08-01-15-41: | 6919019 | <db username> | localhost | <db name> | Query | 3 | end | UPDATE <smf table name>members
report-2006-08-01-15-41: | 6919038 | <db username> | localhost | <db name> | Query | 2 | end | UPDATE <smf table name>topics

We strongly recommend that you use the attached server log, depicting the overloads caused by your website, and consult a professional developer to investigate if your website can be optimized and perform this optimization if possible.

Thank you for your cooperation in advance.

[Yonkey]

Just to update, I've now restricted the maximum number of search results to 5 (there was no maximum before), and have disabled the Who's Online feature. 

Do you know if those two actions will stop these temporary tables from being created/accessed?

[Ben_S]

Thanks for optimizing it in RC3 Ben, but is there any way you could tell me which files I need to modify now?

Compuart did the work on it, I'll see what I can do about getting a copy of it for you.

Here is what my host just said:

Hello,

In order your account became appropriate for shared hosting you should optimize your database <db name>. Here is a part from the server reports we have :
<>

SMF is optimised, the fact is with a large quantity of data, MySQL is going to have to do some work, if the server is already overloaded in the first place then things are going to be slow.

Any chance of a link to status.php on your server - you can get it from the sticky above.

[Yonkey]

Thanks, I greatly appreciate your help!

And I've linked to status.php in my first post, along with all other necessary information:

    * A link to your status.php - You can download it from README: Checklist for performance problems
http://www.tsl-game.com/forum/status.php

    * a link to your phpinfo.php - What is phpinfo.php?
http://www.tsl-game.com/phpinfo.php

Unfortunately, the server is suffering from the problem that I'm trying to fix right now, so you'll only see 503 errors if you try to access anything.

[Ben_S]

Attached is Search.php from SMF 1.1 RC3. Simply upload to your Sources directory overwriting the existing one.

Don't expect this to make much difference though, the fact is your host is wildly overselling and cannot deliver what they are offering.

Ironic really that you are having these problems despite how they claim to be the best solution for hosting SMF http://www.siteground.com/smf-hosting.htm

[Yonkey]

Thanks Ben, but you have a syntax error.

Fatal error: Function name must be a string in /home/<accountname>/public_html/forum/Sources/Search.php on line 512

And I agree with you 100%.  I'm going to be shopping around for VPS and dedicated servers tonight and see which is the most economical. Any recommendations?

[Ben_S]

Odd, seems to work for me on my forum, what phrase did you try searching for?

As for host recommendations, I haven't really got any, although a decent shared host I'd imagine would be fine, but discuss the size of your site and requirements with prospective hosts and expect to spend a fair bit more than you currently do. Try Jason @ Charlottezweb - http://www.simplemachines.org/community/index.php?topic=3559.0 but send him a message to make sure he can support you.

[Yonkey]

Odd, seems to work for me on my forum, what phrase did you try searching for?

I can't remember, but it occurs for any word.  I just tried it with: test.  You can see for yourself.

As for host recommendations, I haven't really got any, although a decent shared host I'd imagine would be fine, but discuss the size of your site and requirements with prospective hosts and expect to spend a fair bit more than you currently do. Try Jason @ Charlottezweb - http://www.simplemachines.org/community/index.php?topic=3559.0 but send him a message to make sure he can support you.

Thanks, I'll contact him tonight.