MyBB Community Forums

Hello, i received this mail from my hosting, but i didnt understand it , and how to resolve this problem,

Quote:This message is to advise you of a temporary block placed on your database. The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case. Requests to this database may become degraded by limiting the maximum number of queries or connections for a limited amount of time, or if there are sustained issues, ultimately we may be forced to block access to this database until the issue has been resolved.

Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.

~~~
Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or complex query], © huge table copies/maintenance during peak hours.

NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator. They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL queries consume. As always, it's best to backup any data before making any changes or adjustments.

First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel. Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the drop-down next to it, and carefully select .Optimize table..

Additionally, adding indexes to your table(s) may improve performance. If you're not sure what you're doing, it's best not to modify any table; caution is recommended. There are various articles (http://www.developer.com/db/article.php/...tabase.htm and http://www.databasejournal.com/features/...ndexes.htm). It may be best to Google for something like [Your Software Name] MySQL indexes for suggestions.

If you reply back to this with your IP address (http://www.hostgator.com/ip.shtml) we will be more than happy to go ahead enable HTTP access for you, so that you can safely work on the script without it causing further issues. Please let us know how you would like to proceed.

CPU_TIME:415 table_rows_read:96668065 SELECTS:1733 ROWS_UPDATED:491 ROWS_FETCHED:0 BUSY_TIME:434 ONNECTED_TIME:463 BYTES_SENT:64972872 BYTES_RECEIVED:682312 WAIT_TIME:19

Top table row reads:
DB_USER: haifa_usersite -- TOTAL_CONNECTIONS: 256 -- CONNECTED_TIME: 459 -- CPU_TIME: 415 -- TABLE_ROW_READS: 96668065 -- SELECT_COMMANDS: 1542 -- UPDATE_COMMANDS: -- BUSY_TIME: 434 -- BYTES_SENT: 65231618 -- BYTES_RECEIVED: 677262 -- WAIT_TIME (IO): 19
DB_USER: haifa_gallery -- TOTAL_CONNECTIONS: 192 -- CONNECTED_TIME: 4 -- CPU_TIME: 0 -- TABLE_ROW_READS: 0 -- SELECT_COMMANDS: 192 -- UPDATE_COMMANDS: -- BUSY_TIME: 0 -- BYTES_SENT: 0 -- BYTES_RECEIVED: 6528 -- WAIT_TIME (IO): 0

Top WAIT (IO) TIME:
DB_USER: haifa_usersite -- TOTAL_CONNECTIONS: 256 -- CONNECTED_TIME: 459 -- CPU_TIME: 415 -- TABLE_ROW_READS: 96668065 -- SELECT_COMMANDS: 1542 -- UPDATE_COMMANDS: -- BUSY_TIME: 434 -- BYTES_SENT: 65231618 -- BYTES_RECEIVED: 677262 -- WAIT_TIME (IO): 19
DB_USER: haifa_gallery -- TOTAL_CONNECTIONS: 192 -- CONNECTED_TIME: 4 -- CPU_TIME: 0 -- TABLE_ROW_READS: 0 -- SELECT_COMMANDS: 192 -- UPDATE_COMMANDS: -- BUSY_TIME: 0 -- BYTES_SENT: 0 -- BYTES_RECEIVED: 6528 -- WAIT_TIME (IO): 0

haifa 18213 0.2 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 18214 0.2 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 19004 1.3 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 19103 13.0 0.2 41652 18980 ? SN 01:52 0:00 /usr/bin/php /home/haifa/public_html/fanclub/memberlist.php
haifa 19115 0.0 0.2 41652 19040 ? RN 01:52 0:00 /usr/bin/php /home/haifa/public_html/fanclub/index.php

Thu Feb 16 01:52:43 CST 2012
Running Processes:
haifa 18213 0.2 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 18214 0.2 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 19004 1.3 0.0 0 0 ? ZN 01:52 0:00 [php] <defunct>
haifa 19103 13.0 0.2 41652 18980 ? SN 01:52 0:00 /usr/bin/php /home/haifa/public_html/fanclub/memberlist.php
haifa 19115 0.0 0.2 42168 19344 ? RN 01:52 0:00 /usr/bin/php /home/haifa/public_html/fanclub/index.php

Running Queries:
*************************** 1. row ***************************
USER: haifa_usersite
DB: haifa_mybb
STATE:
TIME: 0
COMMAND: Sleep
INFO: NULL
*************************** 2. row ***************************
USER: haifa_usersite
DB: haifa_mybb
STATE: Sending data
TIME: 0
COMMAND: Query
INFO: SELECT u.*, f.*
FROM mybb_users u
LEFT JOIN mybb_userfields f ON (f.ufid=u.uid)
WHERE 1=1 AND u.username LIKE 'B%'
ORDER BY u.regdate DESC
LIMIT 40000, 20

Current Site Requests:
109.200.175.54 haifafans.net /uploader/image-483C_4AD5ACAA.jpg
109.200.175.54 haifafans.net /uploader/image-AABE_4B0D24E0.jpg
110.80.34.182 haifafans.net /fanclub/member.php
111.92.142.12 haifafans.net /fanclub/css.php?stylesheet=129
121.242.117.145 haifafans.net /fanclub/images/2011/2012-bg.jpg
121.242.117.145 haifafans.net /fanclub/images/2011/footer/footer-10.jpg
121.242.117.145 haifafans.net /fanclub/images/2011/header/haifa-header-2012.jpg
121.242.117.145 haifafans.net /fanclub/images/2011/header/tw.jpg
124.148.47.79 haifafans.net /fanclub/css.php?stylesheet=129
124.148.47.79 haifafans.net /fanclub/css.php?stylesheet=130
124.148.47.79 haifafans.net /fanclub/task.php
180.76.5.138 haifafans.net /gallery/displayimage.php?album=lastup&cat=4&pos=11
188.52.60.4 haifafans.net /fanclub/jscripts/prototype.js?ver=1400
212.118.143.36 haifafans.net /fanclub/
212.118.143.36 haifafans.net /uploader/image-2BB2_4AD5ABB4.jpg
212.118.143.36 haifafans.net /uploader/image-3368_4AD5ABB4.jpg
212.118.143.36 haifafans.net /uploader/image-76D6_4AD5ACAA.jpg
212.118.143.36 haifafans.net /uploader/image-96A9_4AD5ABB4.jpg
217.69.133.29 haifafans.net /gallery/displayimage.php?album=lastup&cat=-14&pos=
217.69.133.29 haifafans.net /gallery/displayimage.php?album=lastup&cat=-14&pos=
217.69.133.29 haifafans.net /gallery/displayimage.php?album=lastup&cat=-14&pos=
217.69.133.29 haifafans.net /gallery/displayimage.php?album=lastup&cat=-14&pos=
217.69.133.29 haifafans.net /gallery/displayimage.php?album=topn&cat=-16&pos=4
217.69.133.29 haifafans.net /gallery/displayimage.php?album=topn&cat=-162&pos=1
217.69.133.29 haifafans.net /gallery/displayimage.php?album=topn&cat=-162&pos=2
217.69.133.29 haifafans.net /gallery/displayimage.php?album=topn&cat=-162&pos=3
217.69.133.29 haifafans.net /gallery/displayimage.php?album=topn&cat=-162&pos=4
62.150.209.182 haifafans.net /uploader/image-D8F7_4A8F3217.jpg
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/memberlist.php?sort=regdate&order=descending&perpa
66.249.72.1 haifafans.net /fanclub/ratethread.php?tid=12217&rating=5&my_post_key=6cf0
66.249.72.1 haifafans.net /fanclub/search.php?action=finduserthreads&uid=1442816
66.249.72.1 haifafans.net /fanclub/search.php?action=finduserthreads&uid=1442919
66.249.72.1 haifafans.net /fanclub/search.php?action=results&sid=9104dffcdeb426a82699
66.249.72.1 haifafans.net /fanclub/thread-10550-post-134283.html
66.249.72.1 haifafans.net /fanclub/thread-3069-nextnewest.html
66.249.72.1 haifafans.net /fanclub/thread-3078-post-32093.html
66.249.72.1 haifafans.net /fanclub/thread-3298-post-34416.html
66.249.72.1 haifafans.net /fanclub/thread-9275-post-112383.html
66.249.72.1 haifafans.net /fanclub/user-1272499.html
66.249.72.1 haifafans.net /fanclub/user-1295520.html
74.125.16.146 haifafans.net /gallery/albums/P2/Haifa%20Photo%20Session%20For%20Al%20Cha
74.125.16.149 haifafans.net /uploader/image-0ACE_4B0D24E0.jpg
74.125.16.152 haifafans.net /gallery/albums/P/Haifa%20in%20Blue/normal_1_(2).jpg
74.125.16.152 haifafans.net /gallery/albums/P2/Haifa%20in%20yellow%20baby%20dress/norma
74.125.16.153 haifafans.net /uploader/image-931F_4AD5ACAA.jpg
74.125.16.154 haifafans.net /gallery/albums/P/Haifa%20In%20Black%20Dress/normal_1_(3).j
74.125.16.157 haifafans.net /uploader/image-2BB2_4AD5ABB4.jpg
74.125.16.161 haifafans.net /uploader/image-45E7_4AD5ACAA.jpg
74.125.78.102 haifafans.net /uploader/image-79AA_4AD5ABB4.jpg
74.125.78.103 haifafans.net /uploader/image-3368_4AD5ABB4.jpg
74.125.78.80 haifafans.net /gallery/albums/P/Haifa%20In%20Red/normal_PHOTO_SHOP_(294).
74.125.78.88 haifafans.net /gallery/albums/P2/Haifa%20Photo%20Session%20For%20Al%20Cha
74.125.78.88 haifafans.net /uploader/image-FD15_4AD5ACAA.jpg
74.125.78.89 haifafans.net /uploader/image-76D6_4AD5ACAA.jpg
74.125.78.93 haifafans.net /uploader/image-96A9_4AD5ABB4.jpg
74.125.78.96 haifafans.net /uploader/image-AABE_4B0D24E0.jpg
74.125.78.97 haifafans.net /uploader/image-E446_4B0D24E0.jpg
78.101.173.145 haifafans.net /Uploader/files/26/Haifa%20Without%20Makeup.jpg
78.101.173.145 haifafans.net /fanclub/css.php?stylesheet=129
78.101.173.145 haifafans.net /fanclub/images/2011/icons/register.png
78.101.173.145 haifafans.net /fanclub/jscripts/prototype.js?ver=1400
79.140.79.245 haifafans.net /uploader/image-6DB0_4A4AA2AA.jpg
80.191.218.134 haifafans.net /uploader/image.php?id=2413_4DD93247
80.191.218.134 haifafans.net /uploader/image.php?id=4522_4DD93213

Cordially,

please list your active plugins

You may very likely have experienced a DDOS attack but here is bad query.

COMMAND: Query
INFO: SELECT u.*, f.*
FROM mybb_users u
LEFT JOIN mybb_userfields f ON (f.ufid=u.uid)
WHERE 1=1 AND u.username LIKE 'B%'
ORDER BY u.regdate DESC
LIMIT 40000, 20

A 40000 limit is a huge query. Unsure if that's from the DDOS or a bad plugin. You need to track that down and fix it imho.

First, from your site's signature you are still running v1.4.1, consider upgrading to the latest release to not only secure your board but also take advantage of the continuous optimizations the newer releases offer. It will also be difficult for us to provide proper support if you are running an older version.

However, looking at the info you have posted above, if you are hitting your host's limits then you should consider disabling features that are taking up resources and that are not as important to the site. An example is the memberlist which is showing up in the running processes and also where the above query with the LIMIT 40000, 20 is being ran. Although the query is only returning 20 rows, it is scanning 40000+ rows. So you can either set a hard limit on the search results returned by the memberlist search or disable it entirely except for some usergroups that you choose. Also from the access log I see that the IP accessing the memberlist is the google bot so consider blocking the bots from non-seo essential places of the site such as the calendar, memberlist and so on.

EDIT: Actually now that I take a closer look at your site I realize that you have commented out the Powered by MyBB message which is required to get support here. (I was looking at the source code before where I saw it but just realized its commented out so doesn't show on the live view). So I'll be closing this thread until you add it back.

Hello there,

The forum you have posted does not have the minimum "Powered by MyBB" notice we require for technical support here. In order for us to give you further support, please add the following code to the footer template of your forum:

Admin CP --> Templates & Style --> *expand your template set* --> Footer Templates --> footer

Powered by <a href="http://www.mybb.com">MyBB</a>

I will close this thread until you have reinstated the Powered By line. Please post in the Private Inquiries forum to ask to have this thread reopened, where you can also raise any questions you have about this policy.

With regards,
MyBB Support Team

MyBB Community Forums

TayssirCh

pavemen

User 2877

- G33K -

- G33K -