Re: Sluggish server performance - Mailing list pgsql-performance

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/