January 20, 2009 at 12:31 pm
please can somebody help me
i run a forum via streamline.net
they have sent me this message
Our engineers have discovered that your MySQL database on this account, mothersmee1, has been causing performance issues on the MySQL database server that this database is hosted on.
It has been identified that your database is running queries that take almost 50 seconds to complete, and examining 1.666.223 rows which is very excessive. An example of such a query is below:
# Time: 090107 12:10:44
# User@Host: mothersmee1[mothersmee1] @ [213.171.218.212]
# Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 1666223
use mothersmee1;
SELECT word_id
FROM phpbb_search_wordmatch
GROUP BY word_id
HAVING COUNT(word_id) > 68249;
and also this
the forum is running this query
SELECT word_id
FROM phpbb_search_wordmatch
GROUP BY word_id
HAVING COUNT(word_id) > 68249;
i don't know where to start, please can anybody help me?????
January 21, 2009 at 2:46 am
We are a Microsoft SQL Server forum, not MySQL, so I'm not sure you'll get much help here.
You would be sure that you have indexes on the columns in your WHERE clause. That should help you.
January 21, 2009 at 2:50 am
sorry i was obviously having a blonde moment, thanks for that though x
January 22, 2009 at 8:56 am
That problem sure looks like your content has grown to the point where searches by word are now taking too long, given the capacity you're paying for. You're no doubt sharing a physical server with other folks that use the same hosting company, and what they're telling you, in effect, is that you're hogging server resources. You'll either need to archive significant amounts of content, such that it's no longer where you can search it by word, or delete a similar quantity of content, as the only other alternative is to pay for a dedicated server from that hosting company.
Also, ask yourself just how much larger your content is going to get, because eventually, 50 seconds, even on a dedicated server, will become significantly longer. You have to have some kind of data archival or content deletion strategy, in order to maintain performance levels. MySQL isn't quite as powerful as SQL Server, given identical hardware - however, your existing content / blog software is clearly written to make use of MySQL, so without a significant change in both your software AND your hosting environment, you're probably not going to be all that motivated to be making that levle of change.
Hope that helps clarify the likely nature of the problem...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply