Re: choosing the right platform

On Wed, 9 Apr 2003, Jim C. Nasby wrote:

> On Wed, Apr 09, 2003 at 10:51:34AM -0600, scott.marlowe wrote:
> > Secondly, look carefully at using persistant connections in large numbers.
> >
> > While persistant connections DO represent a big savings in connect time,
> > the savings are lost in the noise of many PHP applications.
> >
> > i.e. my dual PIII swiss army knife server can initiate single persistant
> > connections at 1,000,000 a second (reusing the old ones of course).
> > non-persistant connects happen at 1,000 times a second. Most of my
> > scripts run in 1/10th of a second or so, so the 1/1000th used to connect
> > is noise to me.
>
> My $0.02 from my experience with Sybase and DB2:
> It's not the connection *time* that's an issue, it's the amount of
> resources (mostly memory) used by each database connection. Each db2
> connection to a database uses 4-8 meg of memory;

Agreed.

> on my pgsql system,
> each connection appears to be using about 4M. This is the resident set,
> which I believe indicates memory that basically can't be shared. All
> this memory is memory that can't be used for buffering/caching; on a
> system with a hundred connections, it can really start to add up.

If I run "select * from logs" from two different psql sessions on my
backup box hitting my main box (psql would hold the result set and throw
the results off if I ran it on the main box) I get this output from top:

No (pgsql) load:

8:58am up 9 days, 22:43, 4 users, load average: 0.65, 0.54, 0.35
169 processes: 168 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.1% user, 0.1% system, 0.0% nice, 99.1% idle
CPU1 states: 32.1% user, 3.2% system, 0.0% nice, 64.0% idle
Mem: 1543980K av, 1049864K used, 494116K free, 265928K shrd, 31404K buff
Swap: 2048208K av, 0K used, 2048208K free 568600K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 9 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4444 4444 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster

1 psql select *:
9:03am up 9 days, 22:48, 2 users, load average: 0.71, 0.71, 0.46
166 processes: 165 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU1 states: 0.1% user, 2.0% system, 0.0% nice, 97.3% idle
Mem: 1543980K av, 1052188K used, 491792K free, 265928K shrd, 32036K buff
Swap: 2048208K av, 0K used, 2048208K free 570656K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 10 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4448 4448 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster
18026 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster
18035 postgres 10 0 5832 5732 5096 S 0.0 0.3 0:00 postmaster

2 psql select *:
9:03am up 9 days, 22:49, 2 users, load average: 0.58, 0.66, 0.45
166 processes: 165 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 2.2% system, 0.0% nice, 97.2% idle
CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
Mem: 1543980K av, 1053152K used, 490828K free, 265928K shrd, 32112K buff
Swap: 2048208K av, 0K used, 2048208K free 570684K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10241 postgres 8 0 4216 4216 4136 S 0.0 0.2 0:05 postmaster
10242 postgres 9 0 4448 4448 4156 S 0.0 0.2 0:00 postmaster
10243 postgres 9 0 4812 4812 4148 S 0.0 0.3 0:00 postmaster
18026 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster
18035 postgres 9 0 236M 236M 235M S 0.0 15.6 0:12 postmaster

The difference between SIZE and SHARE is the delta, which is only
something like 3 or 4 megs for the initial select * from logs, but the
second one is only 1 meg. On average, the actual increase in memory usage
for postgresql isn't that great, usually about 1 meg.

Running out of memory isn't really a problem with connections<=200 and 1
gig of ram, as long as sort_mem isn't too high. I/O contention is the
killer at that point, as is CPU load.