View Full Version : Forum Disabled for "Mysql Abuse" -- how to streamline my forum
My forums on my website were shut down by powweb today. The message they sent me about it said this:
mysql abuse.. need to fix db before we can re-enable.
I emailed them back asking the WHY but until they reply I will go on the assumption of the most common cause...hogging the server power, although I didn't think my forum was overly busy (statistics: about 300 members visit daily, maximum about 60 online users at any one time, about 500 posts daily).
While I await more information from powweb I'd like to ask HOW I can reduce the mysql drain of my forum. The rest of my website is okay, including other mysql databases (including one that hosts 4000 images) so am I correct in understanding that it is not bandwidth usage that is the problem? In other words....what and where do I streamline? The number of users on at one time? The number of images on each page of the forum (ie. if I get rid of emoticons will that help?). I'm already thinking of limiting viewership to members only...that would cut down the users online to half (since it's usually half members, half guests on at any one time). Please let me know what I need to do, and what are the best ways to make my forum use the least cpu resources.
And finally, how in tarnation do I enact these changes (ie. limiting to members only) if I don't have access to my forums admin panel? Plead with powweb to turn me on again so I can make the changes?
That is a lot of activity for a forum. Most likely what you need to do is cut down the number of queries thrown at the database, or the scope of the queries. Honestly, I'm surprised you didn't run out of queries long before this occurred. The are not (and should not) enable your site until you stop the db pounding script. You don't need to get in the forum admin panel to do this, just disable the forum altogether. Move it to a different directory, slap .htaccess protection on it, etc. Just keep everyone out until you get it it working the way you need to. Once you have that done, have them re-enable your account, and start playing with stuff with just you on the forums.
As for what to change, turn off any bells and whistles you can. Remove any modules you have added. Also ask in the forums for your software to see if they have recommendations. I wrote a script to count your mysql query usage. The phpbb version is easy to implement (if you happen to be using phpbb). There is also a generic version, but you should stay away from that unless you want to get your feet wet with programming. If you use either of these scripts, make sure you back up first.
Pig, thank you for your reply, I really appreciate the help. I haven't heard back from powweb yet so I don't know the details at their end of it.
I've read your reply several times trying to make sure I understand. It's the queries, in your opinion, causing the problem? See, I don't totally understand that....since queries are limited by an hourly amount (72,000 or 72,000x3 if you set it up with 3 database users). In that situation, doesn't the system just kick in and cut you off for an hour? That has never happened to me. This is powweb themselves physically stepping in and shutting me down. So it has to be MORE than just queries...yes, I've got a high query count, but not the maximum allowed. That's what makes me think other things comes in to play...like I allow avatars, emoticons and posting images in threads....get 50 people online opening a thread with 20 images in it and that's a lot of drain...not necessarily mysql drain, but drain. So I'm trying to figure out what features to cut that will satisfy powweb in reducing drain. But I feel like I'm in the dark because I don't know exactly where and what I should be streamlining. And I don't want to prohibit a feature (for example, avatars) thinking it might help when really it doesn't.
As for queries....I do have it so each page generated on my forum displays the query count...so I know how many queries each page requires....what I don't know is the cumulative query count of all my users (but like I said, I've never reached the per hour maximum).
another question:
do you think pruning the size of the database would help at all? ie. culling inactive accounts and deleting old threads no one looks at? or is it just current usage by active members causing the problem (in other words, old posts and inactive members take up database space but do they affect affect overall 'drain' as well)?
I'll try to answer in order. Your forum only interacts with the database via queries. There is nothing else that could cause this. It is not just the number of queries, but the nature of them. You could have a single query that is 10 times more resource intesive than 10 simpler queries combined. Regarding emoticons and avatars, those are typically stored as files, rather than stored within the the database. Allowing them increases the load on PHP, but not on your database. The features you would want to disable are ones that have to go get extra information from the database. When the script fetches a response in a thread, it must get the users profile and it is no extra load to get the avatar field while it is at it. On the other hand, a feature that displays extra information on a page is making seperate calls to the database. Something like that would be a good place to start. If you want, you can post up the features you are thinking of toggling off, and I or someone else can tell you if they are likely to help. Another note, it is a very bad idea to store images in a database (as opposed to in the directory). I don't know if your forum software does this, but I would put that as the "A #1" item on my hit list to check and irradicate. Regarding the size of your database, the more data is in a database, the more of strain it is on the database server and the slower the results. You can start by going into phpmyadmin and cleaning up the overhead. I'm not sure the buttons to get there, but if you click on the database name or the table names, I'm sure you can find it. If there is crap you can delete that you don't need, I would certainly do it, but I would not start deleting threads just because they are old. Any insight you can get from PW would help.
I hope I answered your questions clearly.
Pig, I can't thank you enough for all your help on this. You're in the top 5 of my favourite people on this site! (have to remember to give you some reputation points!). I got through to powweb this morning and they turned me back on, but I have closed the forum from my end while I work on reducing the query count.
Here are some ideas I've come up with....let me know if you think they are the best options:
- limiting "guests" to only a few forums, thus reserving the query counts for participating members
- disable the forum search feature (this uses up a lot of queries, doesn't it?)
- get rid of some queries on my forum homepage....top 10 posters, things like that
- Currently the 10 most recent threads display on my site homepage (thread title, # replies, last poster)....getting rid of that would save queries when my homepage loads?
About the avatars....I have a sinking feeling they are in the database, not a directory. I'm using vbulletin, like here, only not this exact version. Can you look at your avatar above....does it look like it's in the database (it has a dynamic url).
Again, thanks for all your help.
Those all sound like good ideas to me. For the avatars, I really couldn't say. The href of the avatar is a php page that appears to interact with the database, but whether the database is serving the path to the image or the image itself, I couldn't say. I would ftp around your database directory and see if you can find any likely folders. You can also ask on the vbulletin forums. If they are stored in the server, see if there are any mods that will convert it to using a file system instead.
Something else you should consider, although Powweb is a great host for the average website, it is possible that you have outgrown them. If you find that turning off features compromises the quality of the website, you may want to consider a higher end solution.
What forum package are you running?
Also, just wanted to mention that you should also review access to your forums by the various search engine spiders. For example, Yahoo! Slurp will allocate numerous "concurrent" spiders on your site if you let them; this can impact your guest count if you have a large forum. You can control this with a robots.txt file. Here is Yahoo!s faq on this:
http://help.yahoo.com/help/us/ysearch/slurp/slurp-03.html
That is an excellent point.
RTH10260
10-6-05, 01:08 PM
- Currently the 10 most recent threads display on my site homepage (thread title, # replies, last poster)....getting rid of that would save queries when my homepage loads?Any of these 'top hits' scripts can be resource hogs when coded badly.
The worst case is a query like 'select * from sometable order by datetime descending limit 10'. Such a query would pull the full content of the table, get it sorted in order but only return the first 10 rows. The retrieval and handling of all ignored rows is a large waste of resource. The approach would be to have some additional limiting specfication, like considering only rows from within the last month and have an index on the date column.
In general any query that retrieves all columns using 'select *' is a candidate for review to limit to retrieve only actually used content, optimizing the data volume to be handled on the server and transferred to the client.
All search procedures that take user keys should first run as 'select count(*) from...', and when the hit count is too large, have the user refine the request. Nobody will be viewing many more than about 500 items (eg 10 pages at 50 items). Only when the hit count is acceptable, run the query to retrieve the actual data. The second access is likely to be fast, as the index and data blocks are possibly still cached. Only the small data volume will be transferred from the server to the host.
Jack and Richard, thank you for your additional suggestions. I had forgotten about the spiders on the forum and will look into using a robots.txt file to keep them off (I don't really care if my forum gets indexed). So far I have managed to eliminate 8 queries off of my forum homepage and am scrutinizing my scripts for other query-cutting measures...knowing that 'select *' queries are particularly draining will help me with that too.
RTH10260
10-6-05, 08:19 PM
Jack and Richard, thank you for your additional suggestions. I had forgotten about the spiders on the forum and will look into using a robots.txt file to keep them off (I don't really care if my forum gets indexed). So far I have managed to eliminate 8 queries off of my forum homepage and am scrutinizing my scripts for other query-cutting measures...knowing that 'select *' queries are particularly draining will help me with that too.Rereading my post I am not sure if I made my point: using 'select *' can be fine if you are using the data from all columns, but wasteful if eg using only data from three columns out of thirty.
Something that wasn't mentioned yet: be sure to have indexes on columns that are used for retrieval and searches. A missing index can trigger a full table scan for the content. Using EXPLAIN in phpmyadmin with a query text will show the access path.
vBulletin v3.6.0, Copyright ©2000-2009, Jelsoft Enterprises Ltd.