Database causing server load

[GoPantheoN]

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.besthostingforums.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 ?

GoPantheoN Reviewed by GoPantheoN on 30th Aug 2010. Database causing server load 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 Rating: 5

[NewEraCracker]

Yes. You should move to a VPS as soon as possible.

[GoPantheoN]

Will 850mb pwn shared hosting ?

[CJ]

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

[GoPantheoN]

Oh ok I will start to remove the older threads with less views. Thank you for the suggesion humour.

[sloddl]

1. does it cause high CPU load or memory?

2. is PHP the problem or MYSQL?

that info would help.

[Mr Happy]

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.

[GoPantheoN]

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 |
------------------------------------------------------------------------

[sloddl]

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.

[sloddl]

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% ):

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.