Thread: Sluggish server performance
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?
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
Hi, At 20:20 28/03/2005, Patrick Hatcher wrote: >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? Check that your queries use optimal plans, which usually (but not always) means they should use indexes rather than sequential scans. You can check for this by using EXPLAIN <query> or EXPLAIN ANALYZE <query>. You can also check the pg_stat_* and pg_statio_* tables to get a feel of what kind of accesses are done. You also might want to find out if your system is limited by IO or by the CPU. Most probably the former. You can also check the "performance tips" section of the manual. Also you shared_buffers setting seems to be pretty low given your configuration. Hope that helps, Jacques.
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/