MySql - banned by host

Hi,

My host just banned my database saying that I am max resource user. Following is what was sent to me, I could not understand much.



CPU_TIME:0 table_rows_read:1676443 SELECTS:997 ROWS_UPDATED:334 ROWS_FETCHED:0 BUSY_TIME:2247 ONNECTED_TIME:2326 BYTES_SENT:20966039 BYTES_RECEIVED:510159 WAIT_TIME:2247


Top table row reads:
DB_USER: userdb_neweds -- TOTAL_CONNECTIONS: 14 -- CONNECTED_TIME: 251 -- CPU_TIME: 0 -- TABLE_ROW_READS: 1114090 -- SELECT_COMMANDS: 230 -- UPDATE_COMMANDS: -- BUSY_TIME: 235 -- BYTES_SENT: 8816849 -- BYTES_RECEIVED: 156477 -- WAIT_TIME (IO): 235
DB_USER: userdb_piag -- TOTAL_CONNECTIONS: 28 -- CONNECTED_TIME: 991 -- CPU_TIME: 0 -- TABLE_ROW_READS: 319473 -- SELECT_COMMANDS: 165 -- UPDATE_COMMANDS: -- BUSY_TIME: 953 -- BYTES_SENT: 1417792 -- BYTES_RECEIVED: 75064 -- WAIT_TIME (IO): 953
DB_USER: userdb_wallxsy -- TOTAL_CONNECTIONS: 67 -- CONNECTED_TIME: 582 -- CPU_TIME: 0 -- TABLE_ROW_READS: 223510 -- SELECT_COMMANDS: 468 -- UPDATE_COMMANDS: -- BUSY_TIME: 562 -- BYTES_SENT: 491091 -- BYTES_RECEIVED: 230130 -- WAIT_TIME (IO): 562
DB_USER: userdb_ined -- TOTAL_CONNECTIONS: 5 -- CONNECTED_TIME: 9 -- CPU_TIME: 0 -- TABLE_ROW_READS: 15118 -- SELECT_COMMANDS: 85 -- UPDATE_COMMANDS: -- BUSY_TIME: 5 -- BYTES_SENT: 3928967 -- BYTES_RECEIVED: 25694 -- WAIT_TIME (IO): 5
DB_USER: userdb_fum -- TOTAL_CONNECTIONS: 16 -- CONNECTED_TIME: 491 -- CPU_TIME: 0 -- TABLE_ROW_READS: 2511 -- SELECT_COMMANDS: 28 -- UPDATE_COMMANDS: -- BUSY_TIME: 490 -- BYTES_SENT: 6014545 -- BYTES_RECEIVED: 20124 -- WAIT_TIME (IO): 490
DB_USER: userdb_ceb -- TOTAL_CONNECTIONS: 1 -- CONNECTED_TIME: 2 -- CPU_TIME: 0 -- TABLE_ROW_READS: 1741 -- SELECT_COMMANDS: 21 -- UPDATE_COMMANDS: -- BUSY_TIME: 2 -- BYTES_SENT: 296795 -- BYTES_RECEIVED: 2670 -- WAIT_TIME (IO): 2



Top WAIT (IO) TIME:
DB_USER: userdb_piag -- TOTAL_CONNECTIONS: 28 -- CONNECTED_TIME: 991 -- CPU_TIME: 0 -- TABLE_ROW_READS: 319473 -- SELECT_COMMANDS: 165 -- UPDATE_COMMANDS: -- BUSY_TIME: 953 -- BYTES_SENT: 1417792 -- BYTES_RECEIVED: 75064 -- WAIT_TIME (IO): 953
DB_USER: userdb_wallxsy -- TOTAL_CONNECTIONS: 67 -- CONNECTED_TIME: 582 -- CPU_TIME: 0 -- TABLE_ROW_READS: 223510 -- SELECT_COMMANDS: 468 -- UPDATE_COMMANDS: -- BUSY_TIME: 562 -- BYTES_SENT: 491091 -- BYTES_RECEIVED: 230130 -- WAIT_TIME (IO): 562
DB_USER: userdb_fum -- TOTAL_CONNECTIONS: 16 -- CONNECTED_TIME: 491 -- CPU_TIME: 0 -- TABLE_ROW_READS: 2511 -- SELECT_COMMANDS: 28 -- UPDATE_COMMANDS: -- BUSY_TIME: 490 -- BYTES_SENT: 6014545 -- BYTES_RECEIVED: 20124 -- WAIT_TIME (IO): 490
DB_USER: userdb_neweds -- TOTAL_CONNECTIONS: 14 -- CONNECTED_TIME: 251 -- CPU_TIME: 0 -- TABLE_ROW_READS: 1114090 -- SELECT_COMMANDS: 230 -- UPDATE_COMMANDS: -- BUSY_TIME: 235 -- BYTES_SENT: 8816849 -- BYTES_RECEIVED: 156477 -- WAIT_TIME (IO): 235
DB_USER: userdb_ined -- TOTAL_CONNECTIONS: 5 -- CONNECTED_TIME: 9 -- CPU_TIME: 0 -- TABLE_ROW_READS: 15118 -- SELECT_COMMANDS: 85 -- UPDATE_COMMANDS: -- BUSY_TIME: 5 -- BYTES_SENT: 3928967 -- BYTES_RECEIVED: 25694 -- WAIT_TIME (IO): 5
DB_USER: userdb_ceb -- TOTAL_CONNECTIONS: 1 -- CONNECTED_TIME: 2 -- CPU_TIME: 0 -- TABLE_ROW_READS: 1741 -- SELECT_COMMANDS: 21 -- UPDATE_COMMANDS: -- BUSY_TIME: 2 -- BYTES_SENT: 296795 -- BYTES_RECEIVED: 2670 -- WAIT_TIME (IO): 2



The databases are for 2 scripts only
1) Wordpress
2) 4 Images

I am being told to optimize the DB in order to stay alive at the host.

I have already put the scripts on cache and pre-cache. What more can be done?

Please help.

 

Most of the times this happening on a CMS like wordpress means you may need to check the data you have or move hosts. What do you mean by 4 images? And how many rows of data do you have?

 

4images is a gallery script with 9000 rows of data in one of the tables. (9000 images, 200 categories)
Wordpress has around 1800 posts and 400 posts.

 

The numbers dont look large but there seems to be an issue with the number of rows read from what I can see. That might mean some search command with gives too many results. Like allowing a user to do a full text search with just a single letter.

They probably have an issue with more than a million row reads : DB_USER: userdb_neweds -- TOTAL_CONNECTIONS: 14 -- CONNECTED_TIME: 251 -- CPU_TIME: 0 -- TABLE_ROW_READS: 1114090 -- SELECT_COMMANDS: 230 -- UPDATE_COMMANDS: -- BUSY_TIME: 235 -- BYTES_SENT: 8816849 -- BYTES_RECEIVED: 156477 -- WAIT_TIME (IO): 235

Ask them if they are able to lidentify the exact query that caused the problem . That might give an idea as to whats wrong and how it can be fixed.

 

In my opinion 4images is the culprit. You need to use some caching for this script.

 

4images is the culprit i would say, use an alternative.

Also, its not the hosts responsibility to find the exact problem and solution. Its the developer of the websites responsibility, the webhost server systems administrators/engineers just identify resource hog accounts and find the stats for such an issue using resources and bring it to the clients attention via support.

 

I would say they have better tools to find which query [slow query log] and which file [through active processes with a bit of code]. Especially since the scripts being used are readymade scripts. When you provide one click installations, you are trying to attract the technically challenged and you need to keep it that way instead of just saying go find the needle in the haystack!