Latest Announcemnent
New thread

Database causing server load

Status
Not open for further replies.
G

GoPantheoN

Active Member
908
2009
19
0
I am having a warez forum with 120K threads, 3.6K members and the db size is around 850 mb. The site is currently on a shared hosting in a server that has 8 cores.

The db got disabled often and I had to open a ticket for enabling the db.

Now they noticed me that my db is using too much of resources and so its getting disabled.

This already happened and so I removed vBseo which caused the high load last time.

I have checked this thread http://www.wjunction.com/showthread.php?t=1187 , is that I should reduce my db size ? Is that the only way ?

So I have a big doubt, is it really that my db is causing high server load or should my forum be moved to a VPS ?
 
24 comments
u can always remove threads with views less than 10 & ur DB would be fred from many useless threads

850 MB DB is too much for an Shared hosting
 
Their's lots of stuff you can do.

If you do those mass PM's to all members or send a welcome PM on registering then I'd advise you to prune all PM's older than say 2 months.
After posts PM's is usually the second largest table.

On the index you can disable users online like WBB, KatzForums and other large sites do.
Disable the currently online and online over the last 24 hours.

Prune the trash bin to remove topics

Try them before you go messing with the search (which on vb4 is a disaster for using loads of resources) and deleting any threads.

If you could post a list of mods your installed to as lots of mods can be resource heavy. eg a Shoutbox that Guests can view but not talk in is a killer.
 
It causes high CPU load and also memory is at 42% RAM.

Host gave me a log info that was related to MySQL, so its due to MySQL only.

Log details

+-----+------------------+-----------+--------------------+--+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------------------+-----------+--------------------+----
| 52 | dbuser| localhost | dbname | Sleep | 2650 | | |
| 70 | dbuser| localhost | dbname | Sleep | 25 | | |
| 80 | dbuser| localhost | dbname | Sleep | 48 | | |
| |
| 257 | dbuser| localhost | dbname | Sleep | 24 | | |
| 269 | dbuser| localhost | dbname | Sleep | 23 | | |
| 286 | watchsop_wrdp1 | localhost | watchsop_wrdp1 | Sleep | 14 | | |
| 331 | dbuser| localhost | dbname | Sleep | 11 | | |
| 347 | dbuser| localhost | dbname | Sleep | 8 |
| 357 | dbuser| localhost | dbname | Sleep | 24 |
|458 | dbuser| localhost | dbname | Sleep | 24 |
| 459 | dbuser| localhost | dbname | Sleep | 24 |
| 500 | dbuser| localhost | dbname | Sleep | 24 |
------------------------------------------------------------------------
Click to expand...
 
1. configue max. processes (optimize lighttpd or apache)
2. optimize your php script and mysql indexes (don't use UNION and similar mysql commands which makes a temp and loads the memoryload skyhigh)
3. disable all plugins / addons / features which you don't need
4.think of a way to code your php script different, more small databases, instead of one big.
5. if none of this helps, buy a better server. simple as that.
 
if your server runs lighttpd then edit your lighttpd.conf to this (i have this configuration with 50 000+ unique visitors daily and it works fine for me. bofore i edited this my cpu load was high too. 80% :D):

fastcgi.server = ( ".php" =>
(( "socket" => "/tmp/php-fastcgi.socket",
"bin-path" => "/usr/bin/php-cgi",
"max-procs" => 10,
"bin-environment" => (
"PHP_FCGI_CHILDREN" => "16",
"PHP_FCGI_MAX_REQUESTS" => "1000"
),
"broken-scriptfilename" => "enable"
))
)

or just ask your hosting if they can do it. this should lower the cpu load.

i don't know what the commands are in apache if you use it, because i use lighttpd always. i think that apache in windows can't even limit it, but in linux it should work. ;)

hope you will fix it. ;)
 
Thank you for suggestions sloddl. I will do them asap.

@ Mr Happy, ya I will first prune the pms, they are the having large size next to posts.

List of mods

[slide]http://lulzimg.com/i3/685fa2f3.jpg[/slide]

I installed the Periodic Pm pruner but its incompatible for vB 4 :(

@ sloddl, the server runs on apache :(
 
The tables at high sizes.

searchcore_text - Data Length : 238.65 MB Index length: 150.96 MB

Post - Data Length : 248.79 MB Index length: 9.01 MB

Does the searchcore_text contains the search data?

@ aishamontreal : Sorry,I forgot to mention that I have enabled even the datastore option in vBulletin
 
I opened ticket to enable the db, then the server load went up to 40, the again my db got disabled and lol now the server load is only 0.8

I guess the only way is moving to a VPS.
 
Status
Not open for further replies.
Back
Top