mysql High server load - site suspended - Help

[bklynjava]

Hello,

My host has suspended my site due to too high a server load from my database.

Please help cause I have no idea how to fix this.

I figure maybe I can move the DB to another host and point to it.

I am not sure if thats what I need to do.

Any help is appreciated and Needed ASAP as My site is down.

Thank you

Update: My host has restored my site while I work on a sollution.

Currently my attachments are stored to DB.

Is it possible to setup the db or the attachments in a filesystem on another host and have my site point to them?

Thank you

[Jake Bunce]

The attachment directory must be local.

The load problem was with your database? Moving attachments to the file system might help. You should also enable the view counter queues if your forum is busy:

Admin CP -> vBulletin Options -> Server Settings and Optimization Options -> Update Thread Views Immediately and Update Attachment Views Immediately

Disabling the search feature will also reduce access if you can go without it.

[bklynjava]

The attachment directory must be local.

The load problem was with your database? Moving attachments to the file system might help. You should also enable the view counter queues if your forum is busy:

Admin CP -> vBulletin Options -> Server Settings and Optimization Options -> Update Thread Views Immediately and Update Attachment Views Immediately

Disabling the search feature will also reduce access if you can go without it.

I will try it - I just downloaded the dump and its quite large 2,441,338kb.

Thank you

[Jake Bunce]

Oh, and database read marking can be a big load:

Admin CP -> vBulletin Options -> General Settings -> Thread/Forum Read Marking Type

The Inactivity option is much cheaper but less intuitive.

[bklynjava]

Hello,

The host suspended the site again and sent me an abuse email.

what can I do to resolve this?

| 47384 | nexteln_myname | localhost | nexteln_vbullet | Query | 613 | Sorting result | SELECT * FROM attachment where filename like '%myke%elps%.mp3' ORDER BY filename LIMIT 200 |
| 47464 | nexteln_myname | localhost | nexteln_vbullet | Query | 586 | Sorting result | SELECT * FROM attachment where filename like '%myke%elps%.mp3' ORDER BY filename LIMIT 200 |
| 47514 | nexteln_myname | localhost | nexteln_vbullet | Query | 574 | Sorting result | SELECT * FROM attachment where filename like '%chamillionaire%.mp3' ORDER BY filename LIMIT 200 |
| 47597 | nexteln_myname | localhost | nexteln_vbullet | Query | 547 | Sorting result | SELECT * FROM attachment where filename like '%chamillionaire%.mp3' ORDER BY filename LIMIT 200 |
| 47666 | nexteln_myname | localhost | nexteln_vbullet | Query | 520 | Sorting result | SELECT * FROM attachment where filename like '%money%mike%.mp3' ORDER BY filename LIMIT 200 |
| 47722 | nexteln_java229 | localhost | nexteln_vbullet | Query | 500 | Sorting result | SELECT * FROM attachment where filename like '%comewith%me%.wav' ORDER BY filename LIMIT 200 |
| 47733 | nexteln_myname | localhost | nexteln_vbullet | Query | 495 | Sorting result | SELECT * FROM attachment where filename like '%come%with%me%.wav' ORDER BY filename LIMIT 200 |
| 47740 | nexteln_myname | localhost | nexteln_vbullet | Query | 493 | Sorting result | SELECT * FROM attachment where filename like '%money%mike%.mp3' ORDER BY filename LIMIT 200 |
| 47816 | nexteln_myname | localhost | nexteln_vbullet | Query | 469 | Sorting result | SELECT * FROM attachment where filename like '%come%with%me%.wav' ORDER BY filename LIMIT 200 |
| 47826 | nexteln_myname | localhost | nexteln_vbullet | Query | 466 | Sorting result | SELECT * FROM attachment where filename like '%money%mike%.mp3' ORDER BY filename LIMIT 200 |
| 47872 | nexteln_myname | localhost | nexteln_vbullet | Query | 447 | Sorting result | SELECT * FROM attachment where filename like '%chiggy%.mp3' ORDER BY filename LIMIT 200 |
| 47883 | nexteln_myname | localhost | nexteln_vbullet | Query | 442 | Sorting result | SELECT * FROM attachment where filename like '%come%with%me%.wav' ORDER BY filename LIMIT 200 |
| 47934 | nexteln_myname | localhost | nexteln_vbullet | Query | 420 | Sorting result | SELECT * FROM attachment where filename like '%chiggy%.mp3' ORDER BY filename LIMIT 200 |
| 48367 | nexteln_myname | localhost | nexteln_vbullet | Query | 295 | Sorting result | SELECT * FROM attachment where filename like '%informating%for%webjal%.wav' ORDER BY filename LIMIT |
| 48410 | nexteln_myname | localhost | nexteln_vbullet | Query | 284 | Sorting result | SELECT * FROM attachment where filename like '%decert%i930%.wav' ORDER BY filename LIMIT 200 |
| 48663 | nexteln_myname | localhost | nexteln_vbullet | Query | 142 | Sorting result | SELECT * FROM attachment where filename like '%rock%.mp3' ORDER BY filename LIMIT 200 |
| 48721 | nexteln_myname | localhost | nexteln_vbullet | Query | 115 | Sorting result | SELECT * FROM attachment where filename like '%rock%.mp3' ORDER BY filename LIMIT 200 |
| 48833 | superdug_blog | localhost | superdug_wordpress | Query | 54 | closing tables | SELECT `ip` FROM `dugblog_bad_behavior_log` WHERE `ip` LIKE '64.57.162.243' AND `http_response` = 40 |
| 48834 | nexteln_myname | localhost | nexteln_vbullet | Query | 6 | closing tables | REPLACE INTO search (userid, ipaddress, personal, searchuser, forumchoice, sortby, sortorder, search |
| 48938 | jschwoeb_wp002 | localhost | jschwoeb_wp002 | Query | 32 | Copying to tmp table | SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND (((post_title LIKE '%stay%') OR (post_content LIKE '% |
| 49157 | superdug_blog | localhost | superdug_wordpress | Query | 25 | closing tables | SELECT `ip` FROM `dugblog_bad_behavior_log` WHERE `ip` LIKE '72.30.252.101' AND `http_response` = 40 |
| 49159 | markd_gllr1 | localhost | markd_gllr1 | Query | 1 | closing tables | SELECT COUNT(id), SUM(num_replies) FROM puntopics WHERE moved_to IS NULL AND forum_id=9 |
| 49195 | nexteln_myname | localhost | nexteln_vbullet | Query | 21 | Sorting result | SELECT * FROM attachment where filename

[Jake Bunce]

Almost all of those are selections from the attachment able. If you still get these errors with attachments in the file system, then you may need to upgrade to a better server.

[bklynjava]

I am at a real Loss now.

I have done everything stated and even renamed - stopped access to all attachments.

Yet they say its still overloading the server - with - no search - no attachments nothing I don't get it.

Ive added the NIX to 5 and followed the other adjustments as well.

Maybe there is something I am missing here.? Scratchin my head LOL

Please advise.

Thank you

[Jake Bunce]

If you continue to have load problems then I recommend you post in the Server Configuration forum. Post the info requested in this thread and they will be able to make recommendations.

[vbullfan]

Moving attachments to the file system might help.

Jake, does moving the attachments to file system improve the db performance EVEN IF noone is downloading attachments at that very moment? in other words, is it only when downloading an attachment that there is less load on db when using file system? or will we get an overall performance increase from movinig to file system?

[Jake Bunce]

It will decrease the size of your database. I don't think that alone will help performance. The main hit comes when attachments are downloaded. When attachments are in the file system the reads are made on the file system instead of the database.