Re: Postmaster processes running out of control?

Joshua Hoover wrote:
> I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
> problem with my PostgreSQL server. I have a PHP application on a separate
> server accessing the PostgreSQL server. The PostgreSQL server seems to be
> getting hammered, as even simple queries on indexed columns are taking
> FOREVER. When I run top, here I normally see at least 50 entries similar to
> these for postmaster:
>
> 19336 postgres 9 0 92960 90M 92028 S 0.0 9.0 0:18 postmaster
> 19341 postgres 9 0 87996 85M 87140 S 0.0 8.5 0:09 postmaster
> 19355 postgres 9 0 87984 85M 87112 S 11.6 8.5 0:09 postmaster
> 19337 postgres 9 0 87952 85M 87092 S 0.0 8.5 0:09 postmaster

Looks pretty good to me, assuming it's a top(1) output as I
see it on my RedHAT 7.1 system.

Nearly all of the virtual memory of the processes is shared
memory and everything seems to be sucked in (the 90M etc.
column, resident set size).

> The server info from top reads:
>
> 57 processes: 54 sleeping, 3 running, 0 zombie, 0 stopped
> CPU states: 54.3% user, 3.7% system, 0.0% nice, 41.8% idle
> Mem: 1028908K av, 346760K used, 682148K free, 93812K shrd, 8640K
> buff
> Swap: 1048536K av, 0K used, 1048536K free 131796K
> cached

Confirms my above statement, 0K used Swap, so that server has
plenty of unused RAM.

> My postgresql.conf has the following changes/additions to it that change
> from the default:
>
> max_connections = 512
> sort_mem = 1024
> shared_buffers = 12800

1 Gig of physical RAM on a dedicated database server (that's
what you describe at the top, since your PHP app runs on a
different system, so PostgreSQL is the only thing here,
right?).

Why don't you make a serious amount of that memory available
for shared buffers? I would start with 65536 (1/2 GB). Don't
forget to adjust SHMMAX during boot!

> I've run a vacuum on all the tables and still see the same slow responses
> from the database. Does anyone have any suggestions and/or pointers?

Did you run VACUUM only or did you also allow it to ANALYZE
the tables? This is the main pointer, but don't miss the
above optimization hints.

> Thank you,

You're welcome.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com