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
