Обсуждение: Why is this system swapping?

"Anjan Dave" <adave@vantage.com> writes:

> Some background:
>
> This is a quad XEON (yes, Dell) with 12GB of RAM, pg 7.4...pretty heavy
> on concurrent usage. With peak traffic (db allows 1000 connections, in
> line with the number of app servers and connection pools for each)
> following is from 'top' (sorted by mem) Shared_buffers is 170MB,
> sort_mem 2MB. Both WAL and pgdata are on separate LUNs on fibre channel
> storage, RAID10.
>
> 972 processes: 971 sleeping, 1 running, 0 zombie, 0 stopped
>
> CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
>            total   57.2%    0.0%   23.2%   0.0%     3.6%   82.8%  232.4%

This looks to me like most of your server processes are sitting around idle
most of the time.

> 21397 postgres  22   0  181M 180M  175M D    25.9  1.5  85:17   0
> postmaster
>
> 23820 postgres  15   0  178M 177M  175M S     0.0  1.5   1:53   3
> postmaster

So each process is taking up 8-11M of ram beyond the shared memory. 1,000 x
10M is 10G. Add in some memory for page tables and kernel data structures, as
well as the kernel's need to keep some memory set aside for filesystem buffers
(what you really want all that memory being used for anyways) and you've used
up all your 12G.

I would seriously look at tuning those connection pools down. A lot. If your
server processes are sitting idle over half the time I would at least cut it
by a factor of 2.

Working the other direction: you have four processors (I guess you have
hyperthreading turned off?) so ideally what you want is four runnable
processes at all times and as few others as possible. If your load typically
spends about half the time waiting on i/o (which is what that top output says)
then you want a total of 8 connections.

Realistically you might not be able to predict which app server will be
providing the load at any given time, so you might want 8 connections per app
server.

And you might have some load that's more i/o intensive than the 50% i/o load
shown here. Say you think some loads will be 80% i/o, you might want 20
connections for those loads. If you had 10 app servers with 20 connections
each for a total of 200 connections I suspect that would be closer to right
than having 1,000 connections.

200 connections would consume 2G of ram leaving you with 10G of filesystem
cache. Which might in turn decrease the percentage of time waiting on i/o,
which would decrease the number of processes you need even further...

--
greg

On Apr 27, 2005, at 1:48 PM, Anjan Dave wrote:

> As you can see the system starts utilizing swap at some point, with so
> many processes. Some time ago we had decided to keep the connections
> from the pool open for longer

You've shown the system has used swap but not that it is swapping.
Having swap in use is fine - there is likely plenty of code and whatnot
that is not being used so it dumped it out to swap. However if you are
actively moving data to/from swap that is bad. Very bad. Especially on
linux.

To tell if you are swapping you need to watch the output of say, vmstat
1 and look at the si and so columns.

Linux is very swap happy and likes to swap things for fun and profit.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Sorry, I didn't attach vmstat, the system does actively swap pages. Not
to the point where it crawls, but for some brief periods the console
becomes a bit unresponsive. I am taking this as a sign to prevent future
problems.

anjan

-----Original Message-----
From: Jeff [mailto:threshar@torgo.978.org]
Sent: Wednesday, April 27, 2005 2:30 PM
To: Anjan Dave
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Why is this system swapping?


On Apr 27, 2005, at 1:48 PM, Anjan Dave wrote:

> As you can see the system starts utilizing swap at some point, with so

> many processes. Some time ago we had decided to keep the connections
> from the pool open for longer

You've shown the system has used swap but not that it is swapping.
Having swap in use is fine - there is likely plenty of code and whatnot
that is not being used so it dumped it out to swap. However if you are
actively moving data to/from swap that is bad. Very bad. Especially on
linux.

To tell if you are swapping you need to watch the output of say, vmstat
1 and look at the si and so columns.

Linux is very swap happy and likes to swap things for fun and profit.

--

Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Apr 27, 2005, at 2:29 PM, Greg Stark wrote:

> "AI would seriously look at tuning those connection pools down. A lot.
> If your
> server processes are sitting idle over half the time I would at least
> cut it
> by a factor of 2.
>

Are you (Anjan) using real or fake connection pooling - ie pgpool
versus php's persistent connections ?  I'd strongly recommend looking
at pgpool. it does connection pooling correctly (A set of X connections
shared among the entire box rather than 1 per web server)

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Yes, HT is turned off (I haven't seen any recommendations to keep it
on).

This is when we were seeing 30 to 50% less traffic (users) than today -
we didn't want the idle connections in the pool to expire too soon
(default 30 secs, after which it goes back to pool) and reopen it
quickly, or not have sufficient available (default 20 conns, we raised
it to 50), so we figured a number per app server (50) and set that to
expire after a very long time, so as to avoid any overhead, and always
have the connection available whenever needed, without opening a new
one.

But now, for *some* reason, in some part of the day, we use up almost
all connections in each app's pool. After that since they are set to
expire after a long time, they remain there, taking up DB resources.

I will be trimming down the idle-timeout to a few minutes first, see if
that helps.

Thanks,
Anjan

-----Original Message-----
From: Greg Stark [mailto:gsstark@mit.edu]
Sent: Wednesday, April 27, 2005 2:29 PM
To: Anjan Dave
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Why is this system swapping?

"Anjan Dave" <adave@vantage.com> writes:

> Some background:
>
> This is a quad XEON (yes, Dell) with 12GB of RAM, pg 7.4...pretty
heavy
> on concurrent usage. With peak traffic (db allows 1000 connections, in
> line with the number of app servers and connection pools for each)
> following is from 'top' (sorted by mem) Shared_buffers is 170MB,
> sort_mem 2MB. Both WAL and pgdata are on separate LUNs on fibre
channel
> storage, RAID10.
>
> 972 processes: 971 sleeping, 1 running, 0 zombie, 0 stopped
>
> CPU states:  cpu    user    nice  system    irq  softirq  iowait
idle
>            total   57.2%    0.0%   23.2%   0.0%     3.6%   82.8%
232.4%

This looks to me like most of your server processes are sitting around
idle
most of the time.

> 21397 postgres  22   0  181M 180M  175M D    25.9  1.5  85:17   0
> postmaster
>
> 23820 postgres  15   0  178M 177M  175M S     0.0  1.5   1:53   3
> postmaster

So each process is taking up 8-11M of ram beyond the shared memory.
1,000 x
10M is 10G. Add in some memory for page tables and kernel data
structures, as
well as the kernel's need to keep some memory set aside for filesystem
buffers
(what you really want all that memory being used for anyways) and you've
used
up all your 12G.

I would seriously look at tuning those connection pools down. A lot. If
your
server processes are sitting idle over half the time I would at least
cut it
by a factor of 2.

Working the other direction: you have four processors (I guess you have
hyperthreading turned off?) so ideally what you want is four runnable
processes at all times and as few others as possible. If your load
typically
spends about half the time waiting on i/o (which is what that top output
says)
then you want a total of 8 connections.

Realistically you might not be able to predict which app server will be
providing the load at any given time, so you might want 8 connections
per app
server.

And you might have some load that's more i/o intensive than the 50% i/o
load
shown here. Say you think some loads will be 80% i/o, you might want 20
connections for those loads. If you had 10 app servers with 20
connections
each for a total of 200 connections I suspect that would be closer to
right
than having 1,000 connections.

200 connections would consume 2G of ram leaving you with 10G of
filesystem
cache. Which might in turn decrease the percentage of time waiting on
i/o,
which would decrease the number of processes you need even further...

--

greg

Using Resin's connection pooling. We are looking into pgpool alongside
slony to separate some reporting functionality.

-anjan

-----Original Message-----
From: Jeff [mailto:threshar@torgo.978.org]
Sent: Wednesday, April 27, 2005 3:29 PM
To: Greg Stark
Cc: Anjan Dave; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Why is this system swapping?


On Apr 27, 2005, at 2:29 PM, Greg Stark wrote:

> "AI would seriously look at tuning those connection pools down. A lot.

> If your
> server processes are sitting idle over half the time I would at least
> cut it
> by a factor of 2.
>

Are you (Anjan) using real or fake connection pooling - ie pgpool
versus php's persistent connections ?  I'd strongly recommend looking
at pgpool. it does connection pooling correctly (A set of X connections
shared among the entire box rather than 1 per web server)

--

Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Jeff <threshar@torgo.978.org> writes:

> Are you (Anjan) using real or fake connection pooling - ie pgpool versus php's
> persistent connections ?  I'd strongly recommend looking at pgpool. it does
> connection pooling correctly (A set of X connections shared among the entire
> box rather than 1 per web server)

Having one connection per web process isn't "fake connection pooling", it's a
completely different arrangement. And there's nothing "incorrect" about it.

In fact I think it's generally superior to having a layer like pgpool having
to hand off all your database communication. Having to do an extra context
switch to handle every database communication is crazy.

For typical web sites where the database is the only slow component there's
not much point in having more web server processes than connections anyways,
All your doing is transferring the wait time from waiting for a web server
process to waiting for a database process.

Most applications that find they need connection pooling are using it to work
around a poorly architected system that is mixing static requests (like
images) and database driven requests in the same web server.

However, your application sounds like it's more involved than a typical web
server. If it's handling many slow resources, such as connections to multiple
databases, SOAP services, mail, or other network services then you may well
need that many processes. In which case you'll need something like pgpool.

--
greg

Greg,

> In fact I think it's generally superior to having a layer like pgpool
> having to hand off all your database communication. Having to do an extra
> context switch to handle every database communication is crazy.

Although, one of their issues is that their database connection pooling is
per-server.    Which means that a safety margin of pre-allocated connections
(something they need since they get bursts of 1000 new users in a few
seconds) has to be maintained per server, increasing the total number of
connections.

So a pooling system that allowed them to hold 100 free connections centrally
rather than 10 per server might be a win.

Better would be getting some of this stuff offloaded onto database replication
slaves.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Apr 27, 2005, at 7:46 PM, Greg Stark wrote:

> In fact I think it's generally superior to having a layer like pgpool
> having
> to hand off all your database communication. Having to do an extra
> context
> switch to handle every database communication is crazy.
>

I suppose this depends on how many machines / how much traffic you have.

In one setup I run here I get away with 32 * 4 db connections instead
of 500 * 4. Pretty simple to see the savings on the db machine. (Yes,
it is a "bad design" as you said where static & dynamic content are
served from the same box. However it also saves money since I don't
need machines sitting around serving up pixel.gif vs
myBigApplication.cgi)

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/