Re: db server load

On Fri, Dec 12, 2008 at 3:07 AM, Stefano Nichele
<stefano.nichele@gmail.com> wrote:
> Hi All,
> I would like to ask to you, how many connections a db server can handle. I
> know the question is not so easy, and actually I don't want to known a
> "number" but something like:
> - up to 100 connections: small load, low entry server is enough
> - up to 200 connections: the db server starts to sweat, you need a dedicated
> medium server
> - up to 300 connections: hard work, dedicated server
> - up to 500 connections: hard work, dedicated high level server
>
> I would like just to understand when we can talk about small/medium/high
> load.

Well, it's of course more than just how many connections you have.
What percentage of the connections are idle?  Are you running small
tight multi-statement transactions, or huge reporting queries?  The db
server we have at work routinely has 100+ connections, but of those,
there are only a dozen or so actively running, and they are small and
transactional in nature.  The machine handling this is very
overpowered, with 8 opteron cores and 12 disks in a RAID-10 for data
and 2 in another RAID-10 for everything else (pg_xlog, logging, etc)
under a very nice hardware RAID card with battery backed cache.  We've
tested it to much higher loads and it's held up quite well.

With the current load, and handling a hundred or so transactions per
second, the top of top looks like this:

top - 14:40:55 up 123 days,  2:24,  1 user,  load average: 1.08, 0.97, 1.04
Tasks: 284 total,   1 running, 283 sleeping,   0 stopped,   0 zombie
Cpu0  :  2.8%us,  0.4%sy,  0.0%ni, 96.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  2.5%us,  0.3%sy,  0.0%ni, 97.2%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  2.5%us,  0.2%sy,  0.0%ni, 97.1%id,  0.1%wa,  0.1%hi,  0.0%si,  0.0%st
Cpu3  : 10.0%us,  0.7%sy,  0.0%ni, 89.0%id,  0.1%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu4  : 13.0%us,  0.9%sy,  0.0%ni, 85.9%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Cpu5  : 13.5%us,  0.9%sy,  0.0%ni, 85.3%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Cpu6  : 16.2%us,  1.1%sy,  0.0%ni, 82.2%id,  0.3%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu7  : 34.3%us,  2.4%sy,  0.0%ni, 61.3%id,  0.1%wa,  0.4%hi,  1.5%si,  0.0%st

single line cpu looks like this:

Cpu(s):  6.1%us,  0.8%sy,  0.0%ni, 92.9%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st

a line from vmstat 30 looks like this:

 1  0  12548 2636232 588964 27689652    0    0     0  3089 3096 4138
9  2 89  0  0

which shows us writing out at ~3M/sec.  This machine, running pgbench
on a db twice the size of the one it currently runs on, can get
throughput of 30 to 50 megabytes per second.  peaks at about 60,
random access.

> At the moment I'm using a quad-proc system with a 6 disk 1+0 RAID array and
> 2 separate disks for the OS and write-ahead logs.

Run some realistic load tests and monitor the machine with vmstat and
top and iostat, etc...  then compare those numbers to your day to day
numbers to get an idea how close to max performance you're running to
see how much headroom you have.