Re: choosing the right platform - Mailing list pgsql-performance

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.