URGENT HELP NEEDED (HOSTING COMPANY WARNED 2 SUSPEND SITE)

[Baloch]

Hello there,

I hv freshly installed SMF 2.0 RC4 and have only few packages

1.    SimplePortal    2.3.3    [ Uninstall ] [ List Files ] [ Delete ]
2.    Users Online Today    1.5.6    [ Uninstall ] [ List Files ] [ Delete ]
3.    Sitemap    2.2.0    [ Uninstall ] [ List Files ] [ Delete ]
4.    SMF Links    2.3    [ Uninstall ] [ List Files ] [ Delete ]
5.    Aeva Media    1.4b    [ Uninstall ] [ List Files ] [ Delete ]
6.    Ad Managment    2.3.6.3    [ Install Mod ] [ List Files ] [ Delete ]


but got complain from hosting company that my smf index file is increasing CPU resource usage.
They said there is an issue in php file but in which file they cant help.

They give me following report. Please help me out to resolve this issue as soon as possible.

We have checked MySQL queries generated by your SMF and found the following ones that can possibly use a lot of resources:
================================================================================================
# Time: 110215 3:38:13
# User@Host: baaskcom_smf3[baaskcom_smf3] @ localhost []
# Query_time: 81 Lock_time: 0 Rows_sent: 0 Rows_examined: 39221
use baaskcom_smf3;
INSERT IGNORE INTO smf_log_search_results
(id_search, relevance, id_topic, id_msg, num_matches)
SELECT
100,
1000 * (30 * COUNT(*) / (MAX(t.num_replies) + 1) + 25 * CASE WHEN MAX(m.id_msg) < 26119 THEN 0 ELSE (MAX(m.id
_msg) - 26119) / 11195 END + 20 * CASE WHEN MAX(t.num_replies) < 200 THEN MAX(t.num_replies) / 200 ELSE 1 END + 15 * 0 + 10 * CASE WHEN MIN(m.id_msg) = MAX(t.id_firs
t_msg) THEN 1 ELSE 0 END + 0 * MAX(t.is_sticky)) / 100 AS relevance,
t.id_topic,
MAX(m.id_msg) AS id_msg,
COUNT(*) AS num_matches
FROM smf_topics AS t
INNER JOIN smf_messages AS m ON (m.id_topic = t.id_topic)
WHERE m.body LIKE '%hashemi%'
AND m.id_board IN (1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 13, 14, 15, 18, 19, 21, 22, 23, 24, 26, 28, 30, 31, 32, 34
, 35, 36, 37, 38, 39, 40, 43, 44, 45, 46, 48, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64)
GROUP BY t.id_topic
ORDER BY null
LIMIT 6000;

# Time: 110215 3:38:13
# User@Host: baaskcom_smf3[baaskcom_smf3] @ localhost []
# Query_time: 81 Lock_time: 0 Rows_sent: 0 Rows_examined: 39221
use baaskcom_smf3;
INSERT IGNORE INTO smf_log_search_results
(id_search, relevance, id_topic, id_msg, num_matches)
SELECT
100,
1000 * (30 * COUNT(*) / (MAX(t.num_replies) + 1) + 25 * CASE WHEN MAX(m.id_msg) < 26119 THEN 0 ELSE (MAX(m.id
_msg) - 26119) / 11195 END + 20 * CASE WHEN MAX(t.num_replies) < 200 THEN MAX(t.num_replies) / 200 ELSE 1 END + 15 * 0 + 10 * CASE WHEN MIN(m.id_msg) = MAX(t.id_firs
t_msg) THEN 1 ELSE 0 END + 0 * MAX(t.is_sticky)) / 100 AS relevance,
t.id_topic,
MAX(m.id_msg) AS id_msg,
COUNT(*) AS num_matches
FROM smf_topics AS t
INNER JOIN smf_messages AS m ON (m.id_topic = t.id_topic)
WHERE m.body LIKE '%hashemi%'
AND m.id_board IN (1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 13, 14, 15, 18, 19, 21, 22, 23, 24, 26, 28, 30, 31, 32, 34
, 35, 36, 37, 38, 39, 40, 43, 44, 45, 46, 48, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64)
GROUP BY t.id_topic
ORDER BY null
LIMIT 6000;

# Time: 110215 3:38:13
# User@Host: baaskcom_smf3[baaskcom_smf3] @ localhost []
# Query_time: 81 Lock_time: 0 Rows_sent: 0 Rows_examined: 39221
use baaskcom_smf3;
INSERT IGNORE INTO smf_log_search_results
(id_search, relevance, id_topic, id_msg, num_matches)
SELECT
100,
1000 * (30 * COUNT(*) / (MAX(t.num_replies) + 1) + 25 * CASE WHEN MAX(m.id_msg) < 26119 THEN 0 ELSE (MAX(m.id
_msg) - 26119) / 11195 END + 20 * CASE WHEN MAX(t.num_replies) < 200 THEN MAX(t.num_replies) / 200 ELSE 1 END + 15 * 0 + 10 * CASE WHEN MIN(m.id_msg) = MAX(t.id_firs
t_msg) THEN 1 ELSE 0 END + 0 * MAX(t.is_sticky)) / 100 AS relevance,
t.id_topic,
MAX(m.id_msg) AS id_msg,
COUNT(*) AS num_matches
FROM smf_topics AS t
INNER JOIN smf_messages AS m ON (m.id_topic = t.id_topic)
WHERE m.body LIKE '%hashemi%'
AND m.id_board IN (1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 13, 14, 15, 18, 19, 21, 22, 23, 24, 26, 28, 30, 31, 32, 34
, 35, 36, 37, 38, 39, 40, 43, 44, 45, 46, 48, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64)
GROUP BY t.id_topic
ORDER BY null
LIMIT 6000;

# Time: 110127 2:40:42
# User@Host: baaskcom_smf3[baaskcom_smf3] @ localhost []
# Query_time: 44 Lock_time: 0 Rows_sent: 100 Rows_examined: 21824
use baaskcom_smf3;
SELECT t.id_topic, t.num_replies, t.num_views, t.id_board,
m.subject, IFNULL(mem.real_name, m.poster_name) as poster, b.name,
m.poster_time as first_time, mes.poster_time
FROM smf_topics as t
INNER JOIN smf_messages as m ON (m.id_msg = t.id_first_msg)
INNER JOIN smf_boards as b ON (b.id_board = t.id_board)
INNER JOIN smf_messages as mes ON (mes.id_msg = t.id_last_msg)
LEFT JOIN smf_members as mem ON (mem.id_member = t.id_member_started)
WHERE (FIND_IN_SET(-1, b.member_groups) != 0)
ORDER BY t.id_topic DESC
LIMIT 0, 100;

# Time: 110127 2:33:02
# User@Host: baaskcom_smf3[baaskcom_smf3] @ localhost []
# Query_time: 28 Lock_time: 4 Rows_sent: 6 Rows_examined: 877
use baaskcom_smf3;
SELECT
a.id_article, a.id_category, a.id_message, a.approved, c.name as cname, c.picture, m.id_member,
IFNULL(mem.real_name, m.poster_name) AS poster_name, m.icon, m.subject, m.body, m.poster_time,
m.smileys_enabled, t.id_topic, t.num_replies, t.num_views, t.locked, b.id_board, b.name as bname,
mem.avatar, at.id_attach, at.attachment_type, at.filename
FROM smf_sp_articles AS a
INNER JOIN smf_sp_categories AS c ON (c.id_category = a.id_category)
INNER JOIN smf_messages AS m ON (m.id_msg = a.id_message)
INNER JOIN smf_topics AS t ON (t.id_first_msg = a.id_message)
INNER JOIN smf_boards AS b ON (b.id_board = m.id_board)
LEFT JOIN smf_members AS mem ON (mem.id_member = m.id_member)
LEFT JOIN smf_attachments AS at ON (at.id_member = mem.id_member)
WHERE (FIND_IN_SET(-1, b.member_groups) != 0)
AND a.approved = 1
AND publish = 1
ORDER BY a.id_message DESC
LIMIT 0, 6;
================================================================================================

You can analyze them and find the scripts which generated that queries and optimize or disable them to decrease resource usage.

Looking forward for your prompt response

Raji

[NanoSector]

Tried disabling the search function?

[Baloch]

Where is it?

[Illori]

who is your host? sounds like your host is an overseller and you will have further issues if you stay with them.

[Baloch]

I am on webhostingbuzz and I have renewed my site for next two year but since I had renewed I am getting this issue. About a month ago they suspended my site because of SMF then I had discarded my forum and freshly installed everything with old database.

At this time they have advised me to move from shared server to VPN but I cant afford its cost as my site is a non profitable site.

I hv disable following from FEATURES AND OPTIONS.

Search engine friendly URLs
Apache/Lighttpd only!

[Illori]

looking on google your host seems to be an overseller, not much more i can tell you other then try to find another host and get your money back from this one.

[Baloch]

looking on google your host seems to be an overseller, not much more i can tell you other then try to find another host and get your money back from this one.

Please advice good hosting company

[Illori]

do some reading here http://www.simplemachines.org/community/index.php?board=4.0

[Baloch]

Until I go through these reviews, Please advice to resolve above mentioned queries that are causing load on SERVER CPU

[Kimmen]

You could have a look at www.one.com

Atleast they dont have issues with smf and they are cheap. 

[larryhyman]

Read this Topic,

http://www.simplemachines.org/community/index.php?topic=293441.0

All of it, take your time as there is no fast fix....

also, try this site

http://www.smfforfree.com/

They may be able to host your forums for you

[Adish - (F.L.A.M.E.R)]

Is this your website? http://baask.com/diwwan/index.php

If so, disable your talk box at the bottom. It has heavy impact on your CPU usage.

After disabling search, could you ask your host if there is still a problem?

[Baloch]

Is this your website? http://baask.com/diwwan/index.php

If so, disable your talk box at the bottom. It has heavy impact on your CPU usage.

After disabling search, could you ask your host if there is still a problem?

From where can I disable search?

[Adish - (F.L.A.M.E.R)]

Admin --> Search --> Settings --> Untick everyone from "Membergroups with access to the search function:"

[Baloch]

Admin --> Search --> Settings --> Untick everyone from "Membergroups with access to the search function:"

At the moment I have disabled Search for the guest.
I want to know if I create a search index ( Fulltext index ), is it improve the performance?

[Adish - (F.L.A.M.E.R)]

After disabling search, could you ask your host if there is still a problem?

The search index can help, but there will be momentary very high cpu usage during the process which is what your host will start complaining about. (This happens only on oversellers.)

[roonekoos]

You could have a look at www.one.com

Atleast they dont have issues with smf and they are cheap. 

I have this one too, no data use limit and cheap and 24/7 online support.

[Illori]

if there is no limit, unlimited, then they are most likely an overseller and will restrict you at some point.

[Adish - (F.L.A.M.E.R)]

I wouldn't recommend one.com as I have been through them quite a few years back. They are oversellers aswell and host websites very small in size. (Mainly static) You can easily end up into trouble as soon as you get noticed.

[SlammedDime]

The biggest thing you can do to help yourself for now is to create a full text index.  It will eat some CPU load and I'd advise putting your forum into maintenance mode while it builds.  You'll also want to inform your host you'll be doing that.

I would also disable message approval, as that is known to incur some extra load.

One of the queries above is from the Sitemap mod, but the query is properly constructed and uses limits and won't cause an issue, it's just locked because of the search queries locking things.

If things still don't improve after that, you'll need to look at upgrading your hosting plan, either to a less restrictive shared host or to a VPS.

(and a note to everyone that just starts pointing out 'OVERSELLER', please take the time to analyze the information given, and if you don't know how, don't comment until you do).