Problem with my host - just got this email...

[CassandraCox]

First, I apologize if this is the wrong place to post this.   

We just got this email from our host -

----
Notice Type ............: Terms of Service (TOS) Warning Specific Term...........: Excessive Resource Usage Warning Attributed to ..: theislandforums.com [nofollow] Response Required By ...: 12:00 AM EDT/EST, Thursday June 16, 2005 Result of Not Responding: Possible suspension of MySQL privileges
----

Greetings,

Upon watching your usage on the MySQL server over a period of time, it has been determined that the queries on your tables are most likely in need of proper indexing and/or optimization... As your site grows your resource usage is increasingly causing delays and contention for other site owners using the same shared MySQL server..

======
Below is a very small sample from the slow query log for your MySQL
Account:
# Time: 050614  6:30:40
# User@Host: xtheislandforums[xtheislandforums] @ scooter.futurequest.net [nofollow] [69.5.23.3] # Query_time: 20  Lock_time: 0  Rows_sent: 1  Rows_examined: 4716 use xtheislandforums;

                        SELECT COUNT(DISTINCT t.ID [nofollow]_TOPIC), MIN(t.ID_LAST_MSG)
                        FROM smf_topics AS t, smf_boards AS b, smf_messages AS ml, smf_messages AS m
                                LEFT JOIN smf_log_topics AS lt ON (lt.ID_TOPIC = t.ID [nofollow]_TOPIC AND lt.ID [nofollow]_MEMBER = 49)
                                LEFT JOIN smf_log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID [nofollow]_BOARD AND lmr.ID [nofollow]_MEMBER = 49)
                        WHERE m.ID [nofollow]_TOPIC = t.ID [nofollow]_TOPIC
                                AND m.ID [nofollow]_MEMBER = 49
                                AND ml.ID [nofollow]_MSG = t.ID [nofollow]_LAST_MSG
                                AND b.ID [nofollow]_BOARD = t.ID [nofollow]_BOARD
                                AND (FIND_IN_SET(26, b.memberGroups) OR FIND_IN_SET(8, b.memberGroups))
                                AND b.ID [nofollow]_BOARD != 62
                                AND ml.posterTime >= 1107096249
                                AND IFNULL(lt.logTime, IFNULL(lmr.logTime, 0)) < ml.posterTime; # Time: 050614  7:12:13 # User@Host: xtheislandforums[xtheislandforums] @ scooter.futurequest.net [nofollow] [69.5.23.3] # Query_time: 30  Lock_time: 0  Rows_sent: 1  Rows_examined: 18274

                        SELECT COUNT(DISTINCT t.ID [nofollow]_TOPIC), MIN(t.ID_LAST_MSG)
                        FROM smf_topics AS t, smf_boards AS b, smf_messages AS ml, smf_messages AS m
                                LEFT JOIN smf_log_topics AS lt ON (lt.ID_TOPIC = t.ID [nofollow]_TOPIC AND lt.ID [nofollow]_MEMBER = 18)
                                LEFT JOIN smf_log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID [nofollow]_BOARD AND lmr.ID [nofollow]_MEMBER = 18)
                        WHERE m.ID [nofollow]_TOPIC = t.ID [nofollow]_TOPIC
                                AND m.ID [nofollow]_MEMBER = 18
                                AND ml.ID [nofollow]_MSG = t.ID [nofollow]_LAST_MSG
                                AND b.ID [nofollow]_BOARD = t.ID [nofollow]_BOARD
                                AND (FIND_IN_SET(10, b.memberGroups) OR FIND_IN_SET(12, b.memberGroups))
                                AND b.ID [nofollow]_BOARD != 62
                                AND ml.posterTime >= 1092413716
                                AND IFNULL(lt.logTime, IFNULL(lmr.logTime, 0)) < ml.posterTime; # Time: 050614  7:15:07 # User@Host: xtheislandforums[xtheislandforums] @ scooter.futurequest.net [nofollow] [69.5.23.3] # Query_time: 30  Lock_time: 0  Rows_sent: 1  Rows_examined: 378681

                SELECT ID_BOARD, MAX(ID_MSG) AS ID_MSG, MAX(posterTime) AS posterTime
                FROM smf_messages
                WHERE ID_BOARD = 1
                GROUP BY ID_BOARD;
======

The queries produced by the software are very inefficient. Particularly the queries that use the GROUP BY clause are extremely inefficient.
Because the queries cannot take advantage of MySQL's features such as table indexes, the MySQL server has to read every row of the table on each query.

Your queries, on a shared MySQL server, should never take more than a maximum of 10 seconds each...

Please visit and read the chapter entitled: "MySQL Optimisation"
http://www.mysql.com/documentation/mysql/bychapter/ [nofollow]

All further optimization technical support will need to either:
a) Be done by yourself
b) Hire/Contract a professional DBA designer
c) Try our Community Support Forums: http://www.aota.net/forums/ [nofollow]

All optimization and testing should be done on your local MySQL development server, as our MySQL servers are only meant for tested production code...

If this resource issue is not resolved in a timely manner (48 hours), suspension of your account's MySQL access may be required to guarantee smooth operation for others...

Thank you for your swift response to this matter...

Sooooo....  Whaddaya think?   

Thanks for any input!!

[Isaac]

Do you pay for your hosting?

[CassandraCox]

Do you pay for your hosting?

Yup! 

[Isaac]

Do you pay for your hosting?

Yup! 

And you have not exceeded your Disk Space/ Bandwidth?

[CassandraCox]

Do you pay for your hosting?

Yup! 

And you have not exceeded your Disk Space/ Bandwidth?

Not even close.

[[Unknown]]

Well, the times you're given for the first two queries would be caused by any one of the following:

1. A very large forum, of at least 150,000 posts in size.
2. A very badly configured installation of MySQL.
3. A shared server being used by many others in a way that is slowing down your performance.

The last query is frankly incredulous.  That query should NEVER take so long, unless your forum is MUCH LARGER than this one is here, or much more likely because someone else is at fault.

If your host would kick your or take away your MySQL usage because of queries taking a long time, they clearly have no idea what they are doing.  Much of the time, a query will be slow because the server is overloaded, caused by SOMEONE ELSE.  Citing the slow query log means they are most likely novice hosters.

Unless you are attached to this hosting, you may wish to get a new host.  Preferrably, one that knows something about server administration.

-[Unknown]

[CassandraCox]

Well, the times you're given for the first two queries would be caused by any one of the following:

1. A very large forum, of at least 150,000 posts in size.
2. A very badly configured installation of MySQL.
3. A shared server being used by many others in a way that is slowing down your performance.

The last query is frankly incredulous.  That query should NEVER take so long, unless your forum is MUCH LARGER than this one is here, or much more likely because someone else is at fault.

If your host would kick your or take away your MySQL usage because of queries taking a long time, they clearly have no idea what they are doing.  Much of the time, a query will be slow because the server is overloaded, caused by SOMEONE ELSE.  Citing the slow query log means they are most likely novice hosters.

Unless you are attached to this hosting, you may wish to get a new host.  Preferrably, one that knows something about server administration.

-[Unknown]

Thank you for your help!
Yeah, it's a large forum - 354,641 Posts at this point.  We're a busy bunch.  LOL!

I suppose we will be looking into a new host!!

[[Unknown]]

In that case, it is *possible* you may be able to change things.  Still, I don't like that they would cite the slow query log - that is like me looking at the traffic (I live in LA) and getting mad at the person IMMEDIATELY in front of me for it, even if that person can't drive forward either.  It's just poor practice.

Anyway, are you using InnoDB for any of your tables?  Can you ask your host if the server is properly configured for InnoDB?

Or would you rather try to find a more knowledgable host?

-[Unknown]

[CassandraCox]

Or would you rather try to find a more knowledgable host?

I think we'll just be doing this.  We've been with this host for over a year now, and I'm pretty pissed that we've been given 48 hours after they've been watching us for "sometime now"...

Thanks!!

[Isaac]

Or would you rather try to find a more knowledgable host?

I think we'll just be doing this.  We've been with this host for over a year now, and I'm pretty pissed that we've been given 48 hours after they've been watching us for "sometime now"...

Thanks!!

Check your PMs...

[peterstannard]

What sort of bandwidth/disk space are you using?

[CassandraCox]

This is the package that we have -

http://www.futurequest.net/Services/Packages/Odyssey.php [nofollow]

1,500 Megabytes of Unrestricted Disk Space
50 Gigabytes of Unrestricted Transfer/Bandwidth

We've upgraded to this one after exceeding the bandwidth limits on lower priced packages.

[Christian A. Herrnboeck]

LOL! That package is extremely small for the price they charge... I've PMed you an offer for hosting... As I think that even if you were to get the MySQL query errors resolved, you still wouldn't be out of the woods with an incompetent hosting company like that...

I would like to hear what they mean by "Unrestricted Disk Space", if you pay for 1.5GB, you have 1.5GB. It is restricted... You are restricted to 1.5GB (out of the say... 80GB on the server)

-Christian

[CassandraCox]

LOL! That package is extremely small for the price they charge... I've PMed you an offer for hosting... As I think that even if you were to get the MySQL query errors resolved, you still wouldn't be out of the woods with an incompetent hosting company like that...

I would like to hear what they mean by "Unrestricted Disk Space", if you pay for 1.5GB, you have 1.5GB. It is restricted... You are restricted to 1.5GB (out of the say... 80GB on the server)

-Christian

Thank you!!  I did get your message and I'm discussing it with my "co-whatever you would call them". 

I'll be in contact soon.

[Christian A. Herrnboeck]

LOL! That package is extremely small for the price they charge... I've PMed you an offer for hosting... As I think that even if you were to get the MySQL query errors resolved, you still wouldn't be out of the woods with an incompetent hosting company like that...

I would like to hear what they mean by "Unrestricted Disk Space", if you pay for 1.5GB, you have 1.5GB. It is restricted... You are restricted to 1.5GB (out of the say... 80GB on the server)

-Christian

Thank you!!  I did get your message and I'm discussing it with my "co-whatever you would call them". 

I'll be in contact soon.

Sounds Great! You can contact me through: sales@<NOSPAM>montecarlohosting.<NOSPAM>net, I check that inbox every ~ 2 minutes ;-)

I Look forward to hearing from you,
Christian A. Herrnbock

[Isaac]

My God, you pay $50 a month for that?  Sheesh.

[johnm]

my god i pay less than that for a vps server!!!

if your willing to pay so much iw would suggest you getting a vps plan as you have the benefit of learning how to run your own box (and breaking it: so make sure they come with free support like http://powervps.com)

[AchoHosting]

If i can BOLDY recomment Acho Hosting Solutions. I am certain we can handle whatever your hosting needs may be. www.achohosting.com to purchase one of our performance packages or email [email protected] to customize something to your individual needs.