MyBB Community Forums

I have been hosting my myBB forum for more than 2 years with Hostgator without any issue on their Business plan. Here is the URL -> http://www.learnqtp.com/forums/ with 1.6.0 myBB version.

Today my main website was getting timed out frequently. I reported the same to LIVE chat and requested them to monitor it. Within 2 mins of asking, I received a message (probably an automated trigger) from Hostgator saying that I am using too many resources and hence they have restricted my DB. I ran a Optimize DB query through PHPMyAdmin and things got sorted out. Finally i asked them what else they think could be the reason for that trigger and they have sent me this message.

Quote:Hello,

The database is being restricted based on the high amount of rows being read with a low amount of select requests. As you can see below, you pulled over 1 million rows of data with only a thousand select requests.

DB_USER: xxx_abc -- TOTAL_CONNECTIONS: 87 -- CONNECTED_TIME: 944 -- CPU_TIME: 1 -- TABLE_ROW_READS: 115188 -- SELECT_COMMANDS: 1171 -- UPDATE_COMMANDS: -- BUSY_TIME: 920 -- BYTES_SENT: 9481868 -- BYTES_RECEIVED: 492454 -- WAIT_TIME (IO): 919

Here is an output of your current forum database and the amount of rows within each table. I would highly recommend you work towards reducing the number in tables such as mybb_google_seo, mybb_posts, and any other tables you feel can be reduced.

Database: xxx_abc
+---------------------------+----------+------------+
| Tables | Columns | Total Rows |
+---------------------------+----------+------------+
| mybb_adminlog | 6 | 105 |
| mybb_adminoptions | 8 | 3 |
| mybb_adminsessions | 7 | 1 |
| mybb_adminviews | 11 | 1 |
| mybb_announcements | 10 | 3 |
| mybb_attachments | 12 | 914 |
| mybb_attachtypes | 6 | 15 |
| mybb_awaitingactivation | 7 | 266 |
| mybb_badwords | 3 | 0 |
| mybb_banfilters | 5 | 26 |
| mybb_banned | 10 | 86 |
| mybb_board_messages | 5 | 3 |
| mybb_calendarpermissions | 6 | 5 |
| mybb_calendars | 12 | 1 |
| mybb_captcha | 3 | 913 |
| mybb_datacache | 2 | 27 |
| mybb_delayedmoderation | 8 | 0 |
| mybb_events | 14 | 0 |
| mybb_forumpermissions | 18 | 0 |
| mybb_forums | 42 | 11 |
| mybb_forumsread | 3 | 40 |
| mybb_forumsubscriptions | 3 | 161 |
| mybb_google_seo | 4 | 26462 |
| mybb_groupleaders | 5 | 0 |
| mybb_helpdocs | 8 | 7 |
| mybb_helpsections | 6 | 2 |
| mybb_icons | 3 | 12 |
| mybb_joinrequests | 5 | 0 |
| mybb_mailerrors | 9 | 0 |
| mybb_maillogs | 10 | 108 |
| mybb_mailqueue | 6 | 0 |
| mybb_massemails | 14 | 3 |
| mybb_moderatorlog | 8 | 1426 |
| mybb_moderators | 10 | 6 |
| mybb_modtools | 7 | 0 |
| mybb_mycode | 7 | 0 |
| mybb_polls | 12 | 20 |
| mybb_pollvotes | 5 | 13 |
| mybb_posts | 18 | 20973 |
| mybb_privatemessages | 17 | 0 |
| mybb_profilefields | 10 | 3 |
| mybb_promotionlogs | 7 | 0 |
| mybb_promotions | 17 | 0 |
| mybb_reportedposts | 8 | 29 |
| mybb_reputation | 7 | 0 |
| mybb_searchlog | 10 | 179 |
| mybb_sessions | 10 | 4218 |
| mybb_settinggroups | 6 | 34 |
| mybb_settings | 9 | 305 |
| mybb_smilies | 6 | 9 |
| mybb_spamalyser_log | 18 | 0 |
| mybb_spamalyser_sfs_cache | 4 | 0 |
| mybb_spiders | 7 | 10 |
| mybb_stats | 4 | 1239 |
| mybb_tasklog | 4 | 7348 |
| mybb_tasks | 14 | 13 |
| mybb_templategroups | 3 | 39 |
| mybb_templates | 7 | 611 |
| mybb_templatesets | 2 | 1 |
| mybb_themes | 7 | 2 |
| mybb_themestylesheets | 7 | 8 |
| mybb_threadprefixes | 5 | 0 |
| mybb_threadratings | 5 | 324 |
| mybb_threads | 24 | 6181 |
| mybb_threadsread | 3 | 628 |
| mybb_threadsubscriptions | 6 | 5640 |
| mybb_threadviews | 1 | 0 |
| mybb_upgrade_data | 2 | 4 |
| mybb_userfields | 4 | 4678 |
| mybb_usergroups | 64 | 7 |
| mybb_users | 80 | 4684 |
| mybb_usertitles | 5 | 5 |
| mybb_warninglevels | 3 | 0 |
| mybb_warnings | 14 | 37 |
| mybb_warningtypes | 4 | 0 |
| wp_commentmeta | 4 | 3 |
| wp_comments | 16 | 192 |
| wp_links | 14 | 6 |
| wp_options | 5 | 228 |
| wp_postmeta | 4 | 154 |
| wp_posts | 24 | 119 |
| wp_term_relationships | 3 | 166 |
| wp_term_taxonomy | 6 | 54 |
| wp_terms | 4 | 51 |
| wp_usermeta | 4 | 14 |
| wp_users | 10 | 1 |
| wp_w3tc_cdn_queue | 6 | 0 |
| wp_wsc_gocodes | 5 | 3 |
| wp_yarpp_keyword_cache | 4 | 29 |
| wp_yarpp_related_cache | 4 | 46 |
+---------------------------+----------+------------+

I request you all to please see what can be done.

Thanks!

If you need hosting for your forum, I will be happy to host it on one of my servers in Germany.

Thanks for your offer but I am not looking for hosting at this point. I'm looking for possible explanations and resolution to the issue I have quoted above.

well
the main reason is what it does select and what returns that amount

if it are the posts , then it means that your users are gathering a big post (like archivement mode) that does not split the posts up in differend topics (its a guess)

same for the subscriptions , but mainly they work with a select based on forum or based on a other unic id

you could optimize it with assigning certain keys , but i am not that skilled with mysql to say the right solution (and not sure if it is db setting or that it is script depending)

on other side it is possible because the high amount of posts that the scritp keeps reading over and over the database until it find the post and then returns the post ( so starting at row 0 until it found the right post id and then abort the connection)

so the only thing i can suggest is to see if you can make a index so the posts will be found faster in the database with out the need to read the database over and over to find that single post

it is also possible that that is something that mybb need to do , or that it is pure mysql related and only you can do , but my skills are to limited at current moment to be sure about it

alteast you got now a idea where you can look after to see if it get solved

edit

8.5.3. How MySQL Uses Indexes Wrote:Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

more info at http://dev.mysql.com/doc/refman/5.0/en/m...dexes.html

Greetings From PowerChaos

20k post forum?

"you pulled over 1 million rows of data with only a thousand select requests" = bad plugin

@ Labrocca,

I didn't understand the "20k post forum" exclamation you noted above. Do you have any specific query around it? Here is the URL http://www.learnqtp.com/forums/

To me it looks like the Hostgator staff was pointing at

Quote:TABLE_ROW_READS: 115188 -- SELECT_COMMANDS: 1171

but the table row reads here are not 1 million rather 1/10th of a million.

Also they have specifically pointed out mybb_google_seo plugin. As I understand this is a renowned plugin being used my many myBB users. Can this be a problem?

Thanks!