Queries becoming slow under heavy load

Hi,

We are running some performances tests. With a lot of concurrent
access, queries get very slow. When there is no load, those queries run
fast.

We kind of see a trend about these queries: it seems like the ones that
become very slow have an ORDER BY or MAX in them.

Here are our config settings:

name | setting |
description
---------------------------------+--------------------------+-----------
------------------------------------------------------------------------
--------------------------------------------
add_missing_from | off |
Automatically adds missing table references to FROM clauses.
allow_system_table_mods | off | Allows
modifications of the structure of system tables.
archive_command | (disabled) | Sets the
shell command that will be called to archive a WAL file.
archive_mode | off | Allows
archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a
switch to the next xlog file if a new file has not been started within N
seconds.
array_nulls | on | Enable
input of NULL elements in arrays.
authentication_timeout | 1min | Sets the
maximum allowed time to complete client authentication.
autovacuum | on | Starts the
autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of
tuple inserts, updates or deletes prior to analyze as a fraction of
reltuples.
autovacuum_analyze_threshold | 250 | Minimum
number of tuple inserts, updates or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at
which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the
maximum number of simultaneously running autovacuum worker processes.
autovacuum_naptime | 5min | Time to
sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost
delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost
amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of
tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 500 | Minimum
number of tuple updates or deletes prior to vacuum.
backslash_quote | safe_encoding | Sets
whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background
writer sleep time between rounds.
bgwriter_lru_maxpages | 100 | Background
writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Background
writer multiplier on average buffers to scan per round.
block_size | 8192 | Shows the
size of a disk block.
bonjour_name | | Sets the
Bonjour broadcast service name.
check_function_bodies | on | Check
function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.5 | Time spent
flushing dirty buffers during checkpoint, as fraction of checkpoint
interval.
checkpoint_segments | 3 | Sets the
maximum distance in log segments between automatic WAL checkpoints.
checkpoint_timeout | 5min | Sets the
maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s | Enables
warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the
client's character set encoding.
client_min_messages | notice | Sets the
message levels that are sent to the client.
commit_delay | 250 | Sets the
delay in microseconds between transaction commit and flushing WAL to
disk.
commit_siblings | 10 | Sets the
minimum concurrent open transactions before performing commit_delay.
constraint_exclusion | off | Enables the
planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the
planner's estimate of the cost of processing each index entry during an
index scan.
cpu_operator_cost | 0.0025 | Sets the
planner's estimate of the cost of processing each operator or function
call.
cpu_tuple_cost | 0.01 | Sets the
planner's estimate of the cost of processing each tuple (row).
custom_variable_classes | | Sets the
list of known custom variable classes.
DateStyle | ISO, MDY | Sets the
display format for date and time values.
db_user_namespace | off | Enables
per-database user names.
deadlock_timeout | 1s | Sets the
time to wait on a lock before checking for deadlock.
debug_assertions | off | Turns on
various assertion checks.
debug_pretty_print | off | Indents
parse and plan tree displays.
debug_print_parse | off | Prints the
parse tree to the server log.
debug_print_plan | off | Prints the
execution plan to server log.
debug_print_rewritten | off | Prints the
parse tree after rewriting to server log.
default_statistics_target | 10 | Sets the
default statistics target.
default_tablespace | | Sets the
default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.simple | Sets
default text search configuration.

and the box info:

> cat /proc/meminfo

MemTotal: 8177116 kB
MemFree: 2830212 kB
Buffers: 83212 kB
Cached: 2385740 kB
SwapCached: 32 kB
Active: 4037560 kB
Inactive: 1082912 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 8177116 kB
LowFree: 2830212 kB
SwapTotal: 2097112 kB
SwapFree: 2096612 kB
Dirty: 4548 kB
Writeback: 72 kB
AnonPages: 2651288 kB
Mapped: 311824 kB
Slab: 173968 kB
PageTables: 20512 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 6185668 kB
Committed_AS: 3602784 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 263672 kB
VmallocChunk: 34359474295 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

> cat /proc/meminfo

MemTotal: 8177116 kB
MemFree: 2830212 kB
Buffers: 83212 kB
Cached: 2385740 kB
SwapCached: 32 kB
Active: 4037560 kB
Inactive: 1082912 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 8177116 kB
LowFree: 2830212 kB
SwapTotal: 2097112 kB
SwapFree: 2096612 kB
Dirty: 4548 kB
Writeback: 72 kB
AnonPages: 2651288 kB
Mapped: 311824 kB
Slab: 173968 kB
PageTables: 20512 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 6185668 kB
Committed_AS: 3602784 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 263672 kB
VmallocChunk: 34359474295 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

It seems to me that we should try increasing shared_buffers. But do you
have any other suggestions? Or do you see anything wrong in our config?

Thanks,

Anne