Re: Still problems with memory swapping and server load

On Wed, Jun 26, 2002 at 02:50:56PM +0200, Markus Wollny wrote:
> Hi!
>
> I'm still "being hosed over big time" as Curt Sampson put it. It's still
> the same machine and database: 1GB RAM, 4xPIII550Xeon, dumpall.sql is
> ~300MB (see "[GENERAL] Urgent: Tuning strategies?"). It all starts with
> a humble 8MB swap being used (I expect that's just the empty swap with
> nothing in it but some system overhead). Then after a short time, memory
> usage climbs slow but continuously until it hits physical RAM ceiling
> and starts using swap - with not very nice results for the database.
> Swap sometimes amounts to 200MB or more.

Well, there's your problam. As soon as you swap, all performance goes out
the window. Looking below there is five processes using over 200MB of RAM
each. Lookup the PIDs in the log file to see what query it is. Sound's like
you have one whopping big query dragging everything down.

> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
> 6848 postgres 16 0 247M 247M 246M R 93.6 24.6 4:06 postmaster
> 6903 postgres 12 0 204M 204M 203M R 10.2 20.3 0:27 postmaster
> 6837 postgres 9 0 251M 251M 250M S 9.8 25.0 0:37 postmaster
> 6852 postgres 9 0 227M 227M 226M R 7.8 22.6 0:12 postmaster
> 6894 postgres 9 0 247M 247M 246M S 2.1 24.6 0:27 postmaster

> Any ideas? Some other way out than "get more RAM"? How effective can I
> expect more RAM to be in my situation - wouldn't it just clutter up and
> overflow into swap like the first 1GB? How come system-processingtime
> gets so high? I followed the other suggestions and tracked slow queries
> using logging - now there's hardly anything left with >0.5s execution
> time elapsed, but the backends still seem to run amok.

Well, that first postmaster there seems to have been going for 4 minutes
already. Postgres has various stats functions to allow you to work out what
query each backend is currently executing. Are you sure you don't have an
unconstrained join or something?

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.