Page 2 of 5

Re: FORUM Extremely SLOW

Posted: Fri Oct 08, 2010 5:16 pm
by Benjamin
I didn't set this up and I haven't researched how everything is configured. Honestly, I doubt there's a whole lot I can do insofar as increasing the resources available to MySQL, which is the bottleneck here. I do know that the server is using squid, and I'm not sure what purpose it serves because I don't think that reduces queries in any way. Robert is currently in charge of donations and paying for hosting fees.

Re: FORUM Extremely SLOW

Posted: Fri Oct 08, 2010 5:58 pm
by josh
CPU or memory? Big difference. Can you post the top reports?

Re: FORUM Extremely SLOW

Posted: Fri Oct 08, 2010 9:47 pm
by Benjamin
Memory of course. Not publicly. If you want to help optimize it we can do so privately.

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 3:43 am
by josh
Benjamin wrote:Memory of course. Not publicly. If you want to help optimize it we can do so privately.
Benjamin wrote:I doubt there's a whole lot I can do insofar as increasing the resources available to MySQL, which is the bottleneck here.
Sounds like mysql is not the bottleneck, no available memory for any of the system services sounds like the real bottleneck. The cause of the bottleneck may very well by mysql. Please feel free to PM me and I'd be glad to help. I agree the forums are terribly slow and there's no real reason for it. I think the real issue is not "increasing the resources" mysql is using, but rather "decreasing the resources" mysql uses.

That can be done by decreasing the amount of buffering. Without the top reports you're not helping me help you though, so please do PM them. There's not really any sensitive info in a top report anyways, you'd find out the user and pid of httpd. As you can see below from this example report of a "typical" VPS. This dedicated server has 4GB of total memory, and is running default distro config files. What you get is a bunch of sleeping processes eating up memory. A couple "innocent" spare servers, and and a naively high maxRequestsPerChild means we're paying for 2GB of memory to sit around allocated to apache even when there is no traffic. The box is unnecessarily being held back from it's true potential. It doesn't matter if you pay for 250MB of ram or 64GB if your server is configured to allocate all the memory to the wrong processes. The amount under the "free" column should be at least 2/3rds of your memory as a really informal rule of thumb. On this box they pay for 4GB, the machine is 99.98% idle with a load average of 0.01 and and there is only 1GB free 8O

Code: Select all

19627 nobody    15   0  343m 156m 6888 S  0.0  4.0   0:12.93 httpd
21448 nobody    15   0  342m 155m 6876 S  0.0  4.0   0:17.51 httpd
19635 nobody    15   0  342m 155m 6864 S  0.0  3.9   0:14.15 httpd
19629 nobody    15   0  342m 155m 6852 S  0.0  3.9   0:14.74 httpd
20167 nobody    15   0  342m 155m 6788 S  0.0  3.9   0:22.52 httpd
19632 nobody    15   0  341m 155m 6876 S  0.0  3.9   0:20.47 httpd
23716 nobody    15   0  341m 155m 6872 S  0.0  3.9   0:06.93 httpd
19634 nobody    18   0  341m 154m 6896 S  0.0  3.9   0:11.87 httpd
 2636 mysql     15   0  481m 148m 4764 S  0.0  3.8  32:50.07 mysqld
12637 root      18   0  174m 134m 5696 S  0.0  3.4   2:11.54 clamd
 2786 root      18   0  304m 119m 7672 S  0.0  3.0   0:42.08 httpd
19621 root      17   0  288m 110m  260 S  0.0  2.8   0:00.00 httpd
25502 root      15   0  110m  43m 2540 S  0.0  1.1   0:00.63 spamd
25515 root      15   0  111m  43m 1748 S  0.0  1.1   0:03.75 spamd
25516 root      18   0  110m  42m 1200 S  0.0  1.1   0:00.00 spamd

Now here is a report from my VPS that I consider "properly configured" (at least to not run out of memory and crash every hour). I pay for 384MB. Notice how 325MB of that is free. My machine is using ~50MB to idle, where as the badly configured dedicated server is using 3GB to idle.

Code: Select all

top - 04:00:45 up 38 days, 14:47,  1 user,  load average: 0.00, 0.00, 0.00
Tasks:  21 total,   1 running,  20 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    393216k total,    57332k used,   335884k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11704 mysql     18   0  136m  35m 5712 S  0.0  9.2   2:46.87 mysqld
 3400 root      15   0 25736  12m 3920 S  0.0  3.2   1:44.98 httpd
30641 apache    20   0 25736  10m 1652 S  0.0  2.6   0:00.00 httpd
31990 apache    16   0 25736  10m 1652 S  0.0  2.6   0:00.00 httpd
32196 apache    15   0 25736  10m 1652 S  0.0  2.6   0:00.00 httpd
32583 apache    15   0 25736  10m 1652 S  0.0  2.6   0:00.00 httpd
32762 apache    18   0 25736  10m 1652 S  0.0  2.6   0:00.00 httpd
 5561 root      15   0 10788 2820 2264 S  0.0  0.7   0:00.03 sshd
 3347 root      18   0  9292 1904  816 S  0.0  0.5   0:03.56 sendmail
 3358 smmsp     15   0  8244 1520  648 S  0.0  0.4   0:00.05 sendmail
 5756 root      18   0  4004 1508 1224 S  0.0  0.4   0:00.03 bash
11633 root      19   0  3868 1260 1096 S  0.0  0.3   0:00.00 mysqld_safe
 6016 nscd      15   0  107m 1160  828 S  0.0  0.3   0:44.11 nscd
 3417 root      18   0  4488 1108  568 S  0.0  0.3   0:00.83 crond
 3115 root      16   0  7176 1060  664 S  0.0  0.3   5:06.40 sshd
 4035 root      18   0  2452  972  784 R  0.0  0.2   0:00.21 top
 3143 root      18   0  2828  868  700 S  0.0  0.2   0:00.00 xinetd
 3431 root      18   0  5668  708  436 S  0.0  0.2   0:00.00 saslauthd
    1 root      18   0  2152  660  568 S  0.0  0.2   0:15.30 init
 1621 root      15  -4  2240  552  344 S  0.0  0.1   0:00.00 udevd
 3432 root      18   0  5668  424  152 S  0.0  0.1   0:00.00 saslauthd

Right now on phpbb there are "In total there are 9 users online :: 8 registered, 0 hidden and 1 guest (based on users active over the past 5 minutes)". Its taking >1 minute to load this thread, 1 minute to pull up login page. 1 minute to load to forum index. Then I click "view your posts" and it pops up in 1 second. It seems a LOT like we're just being too frugal with our minimal resources. Its not certain actions that cause it. Because sometimes those certain actions take 1 second and other times just loading the forum index seems to take 10s of minutes. I've literally linked people threads and they weren't able to read them until 10 minutes later.

No speed = no donations. No donations = no hardware - sorry gentlemen, catch 22 ;-)

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 8:21 am
by Eran
To me it sounds like MySQL is the bottleneck. Once working set doesn't fit in memory, it starts making random reads from disk and performance drops by several factors. The forums slows down mainly when someone is performing a search on posts (including search for your own posts or some other user's posts), and MySQL needs to fetch from data that doesn't fit in memory. Of course, lack of memory affects other processes as well, but MySQL takes it the hardest

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 10:12 am
by josh
pytrin wrote: The forums slows down mainly when someone is performing a search on posts (including search for your own posts or some other user's posts), and MySQL needs to fetch from data that doesn't fit in memory.
Everyone keeps saying this, yet this page loads in 10 seconds tops for me. Yet other times *all* pages take 1+ minute to load. Lots of forums are much larger than us, run on similar hardware and don't have these issues ;-)

Things seem noticeably faster this morning. Sub second page loads even on searches. 7 Users online...

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 11:05 am
by Eran
Lots of forums are much larger than us, run on similar hardware and don't have these issues
Can you name those forums and their hardware? or are you just guessing?

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 5:36 pm
by josh
http://www.theadminzone.com/forums/show ... hp?t=56982
http://www.theadminzone.com/forums/show ... stcount=11
http://www.bokt.nl/forums/

This guy's phpBB has 60 Million posts and 500 users online. It loads faster than ours.
Current hardware consists of:
- Two dualcore Athon 64 X2 3800+, 2GB memory
- One dual dualcore Xeon 5130, 16GB memory, hardware RAID 10 (4x WD Raptor), hardware RAID5 (4x 500GB WD)
- One singlecore Athon which acts as fileserver (NFS :\) and proxy for static files (Squid)

This to handle 30M pageviews/month, 50GB database, 40M posts, 100k registered users, 1800 concurrent users. DB server is way overkill (except for the 16GB of memory), could do with 1 webserver except during some serious spikes in traffic.
Now.. we have less than 1 Million posts. That's nothing. Sure we could do with 512MB or even 1GB of ram but lots of people are doing this with a smaller post-count-to-hardware ratio than us.

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 6:14 pm
by Eran
post-count-to-hardware ratio? first time I ever heard that. Is that a technical term?

Seriously though, they have much better hardware. When you have their kind of hardware you have a lot more room to optimize. Performance is not a factor of post-count-to-hardware-ratio. When you have much stronger CPU, RAID 10 array of Raptors - even when the working set doesn't fit in memory and someone fetches older data, you won't notice that much of a hit in performance. That is certainly not the case with this machine.

Not saying it can't be optimized further (I haven't seen the actual configuration), but there's a very hard limit to the benefits it would gain

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 6:43 pm
by josh
pytrin wrote:RAID 10 array of Raptors - even when the working set doesn't fit in memory and someone fetches older data, you won't notice that much of a hit in performance. That is certainly not the case with this machine.
Most modern VPS are running RAID 10, like I pointed out a few posts back. If you go years without upgrading you could be running on the drives of 3yrs ago.

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 7:12 pm
by Eran
An upgrade is necessary, question is who will sponsor it. Maybe it's time to start another donation round?

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 7:14 pm
by Benjamin
I'm on the market for a good VPS anyway. I can throw the site up on there. Donations would be good, to help offset the costs. I'll need to speak with the other admins/mods.

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 8:56 pm
by josh
pytrin wrote:An upgrade is necessary
So we're just going to ignore my idea about minimizing the amount of memory it consumes while idle? IMO we should make sure its all configured ideally before upgrading.

Re: FORUM Extremely SLOW

Posted: Sat Oct 09, 2010 8:59 pm
by mikosiko
Benjamin wrote:I'm on the market for a good VPS anyway. I can throw the site up on there. Donations would be good, to help offset the costs. I'll need to speak with the other admins/mods.
post the options/cost

Re: FORUM Extremely SLOW

Posted: Sun Oct 10, 2010 3:31 am
by Eran
So we're just going to ignore my idea about minimizing the amount of memory it consumes while idle? IMO we should make sure its all configured ideally before upgrading.
I think it should go hand in hand. You should talk to Benjamin about getting access to private data, I'll be doing the same.

I think we have some discount at rackspace for all the clients we refer to them, so I'll see what kind of package we can get from them.