Server configuration for 1.6+ Gb database?

[dzhan_ru]

Hello!

So I got my IPB3 forum running on a dedicated server with following configuration:

AMD Athlon 64 X2 4200+
2 GB DDR2 RAM
2x 300 GB SATA HDD (Software-RAID 1)

Software is Debian 5.0 Lenny 64 bit with nginx+Apache+php+eAccelerator+MySQL.

MySQL database it around 1.6 Gb with around 500k posts and 10k users. We got up to 4k visitors daily getting up to 25k pageviews.

It has been working more or less OK until past few days, but now the server looks as it has gone totally fubar. Load average sometimes gets around 50-100, users keep geting timeout errors, host-tracker.com reports uptime of ~73% for today. Rebooting apache and/or the server itself helps for a while, but in a few hours we get the same load and access problems again.

After doing some server voodoo magic for a few days (i.e. playing around with different settings and rarely telling me what's going on) hoster's support came to a conclusion that the MySQL database is too big and thus is causing too high load on disks resulting in server's bad performance. They're suggesting either to get a faster HDD or to move MySQL database to another server. But to be honest I kinda doubt their qualification because they've been feeding me some BS these days, at some points they looked totally clueless, and I know for sure that they have zero experience in configuring servers for IPB.

So the question is if this really is a hardware problem and if this server is indeed not good enough to run such a forum? Or the server can handle our load and the problems are caused by poorly configured/not optimized software (or malfunctioning hardware maybe)? And if I do need a better server than what config would you reccomend for our current needs with ability to handle say twice the current database and load - i.e. up to 3Gb database and 8k visitors daily (really don't wanna hop around servers next couple of years).

Thanks in advance for your help!

[3DKiwi]

Your database seems far too big for 500k posts. I have just over 200k posts and 14,000 members and my database is around 350mb. I think your server's memory is too low. With only 2gb of memory it will be constantly using the harddisk as virtual memory. Mine has 3gb and occasionally uses the harddisk.

3DKiwi

[dzhan_ru]

Your database seems far too big for 500k posts. I have just over 200k posts and 14,000 members and my database is around 350mb.

Dunno, it's just as it is, I haven't done anything to make it bigger on purpose. ;) Also it got around 50% bigger when we upgraded to IPB3, because of posts cache table that didn't exist before (I think).

Atm the biggest tables are:

ibp_posts

Rows 465,058

Type Usage
Data 568.3 MiB
Index 401.7 MiB
Total 970.0 MiB

ipb_content_cache_posts

Rows 249,672

Type Usage
Data 330.0 MiB
Index 5,513.0 KiB
Total 335.4 MiB

[dzhan_ru]

As to memory, this is what top gives me right now when server is performing OK:

Mem: 2075200k total, 1741232k used, 333968k free, 72948k buffers
Swap: 2102456k total, 132604k used, 1969852k free, 1310000k cached

I'll check it when the problems start again.

[3DKiwi]

Here's mine. Notice the swap (harddisk) is used a lot less or that's the way I read it.

Mem: 3088916k total, 2731524k used, 357392k free, 78164k buffers
Swap: 6289436k total, 116k used, 6289320k free, 2313284k cached

I paid IPS to look at my server and optimize it for running IPB 3 when I was having problems last year. It could be worth your while to do the same.

I assume that you optimize your database regularly? One thing worth doing is deleting old pm's. That's done by several MySQL queries listed elsewhere here.

3DKiwi

[dzhan_ru]

3DKiwi, thanks for your suggestions! I asked Gary. to look at my server and optimize it... and it took him less than an hour to fix the load issues that my hoster company's so called "support" wasn't able to deal with for like a week of sending useless tickets back and forth! I'm still watching the load and we'll see how it looks like tomorrow afternoon Moscow time when we usually get highest user activity, but after Gary's tweaks the load average hasn't gone above 1 yet (and we had peaks of 100+ before).

[illusive]

Is there any information that could be shared for me to do to our server? I have a DB of over 6.5GB from an old upgrade of IPB 2.x with upgrades and more upgrades and now I am at 3.1.2 and dont want to apply anymore upgrades until I can fix my issues.
If I try to optimize my tables especially the posts table my SQL server goes bye bye and takes the forums down until I can get it rebooted. I know there is bunches of crap in the DB and I have been working on upgrade issues we had for months now.
Sorry to hi-jack, but you seem to have had the same issues I have got.

[Robulosity2]

You for optimization on a site that large it will need downtime to do it properly

1) turn board offline
2) SSH into server
3) Manually optimize tables (with 6.5GB you wont ever succesfully pull this off via php myadmin or the ACP)

I know there was a mod at one point that I think worked on 3.1.x to clean up junk tables that are from now-non existent modifications.

To give more info we'd need to know your servers spec's (ram, processor, disk, table format aka MyISAM or INNODB)

[illusive]

How would one do this via SSH?

[Zhana]

I highly suggest to hire Gary.

[Gary.]

run this command:

mysqlcheck -u root --auto-repair --optimize --all-databases

[Gary.]

I highly suggest to hire Gary.

Many thank's, Its much appreciated !!

[EquestriansAventure]

Gary worked on my old site !!! Hire him !!!

[Mikorist]

this i have in my crontab



*/30 * * * * sync && echo 3 > /proc/sys/vm/drop_caches
00 01 * * * mysqlcheck -u username -ppassword --auto-repair -o --fast --check --optimize --all-databases


also sysctl config

vm.swappiness=15


and try lighttpd

Soon I will make detailed instructions how to set up (install from 0) IPB and lighttpd

might even make linux distribution adjusted for ipb - with pre-installed server (for my needs for sure)

IPB Linux - and it is not at all.... a bad idea