Re: Postmaster processes running out of control? - Mailing list pgsql-general

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@Yahoo.com #



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