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