pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joshua D. Drake <jd@commandprompt.com>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sluggish server performance
Date: Mon, 28 Mar 2005 10:39:48 -0800
Message-ID: <1112035188.26232.28.camel@jd2.commandprompt.com> (raw)
In-Reply-To: <OFCA5F4B6E.961A6875-ON88256FD2.0062D121-88256FD2.0065040E@fds.com>
References: <OFCA5F4B6E.961A6875-ON88256FD2.0062D121-88256FD2.0065040E@fds.com>

On Mon, 2005-03-28 at 10:20 -0800, Patrick Hatcher wrote:
> 
> 
> 
> Pg:  7.4.5
> RH 7.3
> Raid 0+1  (200G 15k RPM)
> Quad Xeon
> 8G ram
> 
> 95% Read-only
> 5% - read-write
> 
> I'm experiencing extreme load issues on my machine anytime I have more than
> 40 users connected to the database.  The majority of the users appear to be
> in an idle state according TOP, but if more than3 or more queries are ran
> the system slows to a crawl. The queries don't appear to the root cause
> because they run fine when the load drops.  I also doing routine vacuuming
> on the tables.
> 
> Is there some place I need to start looking for the issues bogging down the
> server?


Well your shared buffers seems a little low but beyond that you may have
a couple of queries that run fine until you get into a highly concurrent
situation.

I would turn on statement, duration and pid logging. See if there is
a query that takes say 400ms, if that query needs to be executed before
a bunch of other queries then you will get immediately slow down in a
highly concurrent environment.

Also I didn't see your statistics target listed... What level is that
at?

Lastly you may be able to get away with a lower random_page_cost.

Sincerely,

Joshua D. Drake


> 
> 
> Here are some of my settings.  I can provide more as needed:
> 
> 
> cat /proc/sys/kernel/shmmax
> 175013888
> 
> max_connections = 100
> 
> #---------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
> #---------------------------------------------------------------------------
> 
> # - Memory -
> 
> shared_buffers = 2000           # min 16, at least max_connections*2, 8KB
> each
> sort_mem = 12288                # min 64, size in KB
> #vacuum_mem = 8192              # min 1024, size in KB
> 
> # - Free Space Map -
> 
> max_fsm_pages = 3000000         # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 500         # min 100, ~50 bytes each
> 
> 
> #---------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #---------------------------------------------------------------------------
> 
> # - Settings -
> 
> #fsync = true                   # turns forced synchronization on or off
> #wal_sync_method = fsync        # the default varies across platforms:
>                                 # fsync, fdatasync, open_sync, or
> open_datasync
> wal_buffers = 32                # min 4, 8KB each
> 
> # - Checkpoints -
> 
> checkpoint_segments = 50        # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 1800       # range 30-3600, in seconds
> 
> 
> # - Planner Cost Constants -
> 
> effective_cache_size =  262144  # typically 8KB each
> #effective_cache_size = 625000  # typically 8KB each
> random_page_cost = 2            # units are one sequential page fetch cost
> #cpu_tuple_cost = 0.01          # (same)
> #cpu_index_tuple_cost = 0.001   # (same)
> #cpu_operator_cost = 0.0025     # (same)
> 
> 
> Patrick Hatcher
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/




view thread (3+ messages)

Message-ID: <1112035188.26232.28.camel@jd2.commandprompt.com>
Permalink:  ../1112035188.26232.28.camel@jd2.commandprompt.com/
Also on:    postgresql.org/message-id/1112035188.26232.28.camel@jd2.commandprompt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jd@commandprompt.com, PHatcher@macys.com
  Subject: Re: Sluggish server performance
  In-Reply-To: <1112035188.26232.28.camel@jd2.commandprompt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox