FW: Queries becoming slow under heavy load

Sorry it seems like the postgres configuration didn't come thru the
first time.

name | setting
--------------------------------- +
--------------------------
add_missing_from | off
allow_system_table_mods | off
archive_command | (disabled)
archive_mode | off
archive_timeout | 0
array_nulls | on
authentication_timeout | 1min
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 250
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_naptime | 5min
autovacuum_vacuum_cost_delay | 20ms
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 500
backslash_quote | safe_encoding
bgwriter_delay | 200ms
bgwriter_lru_maxpages | 100
bgwriter_lru_multiplier | 2
block_size | 8192
bonjour_name |
check_function_bodies | on
checkpoint_completion_target | 0.5
checkpoint_segments | 3
checkpoint_timeout | 5min
checkpoint_warning | 30s
client_encoding | UTF8
client_min_messages | notice
commit_delay | 250
commit_siblings | 10
constraint_exclusion | off
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
custom_variable_classes |
DateStyle | ISO, MDY
db_user_namespace | off
deadlock_timeout | 1s
debug_assertions | off
debug_pretty_print | off
debug_print_parse | off
debug_print_plan | off
debug_print_rewritten | off
default_statistics_target | 10
default_tablespace |
default_text_search_config | pg_catalog.simple
default_transaction_isolation | read committed
default_transaction_read_only | off
default_with_oids | off
effective_cache_size | 4000000kB
enable_bitmapscan | on
enable_hashagg | on
enable_hashjoin | on
enable_indexscan | on
enable_mergejoin | off
enable_nestloop | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
escape_string_warning | on
explain_pretty_print | on
extra_float_digits | 0
from_collapse_limit | 8
fsync | on
full_page_writes | on
geqo | off
geqo_effort | 5
geqo_generations | 0
geqo_pool_size | 0
geqo_selection_bias | 2
geqo_threshold | 12
gin_fuzzy_search_limit | 0
ignore_system_indexes | off
integer_datetimes | off
join_collapse_limit | 8
krb_caseins_users | off
krb_server_hostname |
krb_srvname | postgres
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
lc_messages | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_time | en_US.UTF-8
listen_addresses | 127.0.0.1,208.75.198.149
local_preload_libraries |
log_autovacuum_min_duration | -1
log_checkpoints | off
log_connections | off
log_destination | stderr
log_disconnections | off
log_duration | off
log_error_verbosity | default
log_executor_stats | off
log_hostname | off
log_line_prefix |
log_lock_waits | off
log_min_duration_statement | -1
log_min_error_statement | error
log_min_messages | notice
log_parser_stats | off
log_planner_stats | off
log_rotation_age | 0
log_rotation_size | 0
log_statement | none
log_statement_stats | off
log_temp_files | -1
log_timezone | Asia/Kolkata
log_truncate_on_rotation | off
logging_collector | on
maintenance_work_mem | 256MB
max_connections | 100
max_files_per_process | 1000
max_fsm_pages | 500000
max_fsm_relations | 500
max_function_args | 100
max_identifier_length | 63
max_index_keys | 32
max_locks_per_transaction | 64
max_prepared_transactions | 5
max_stack_depth | 5MB
password_encryption | on
port | 5432
post_auth_delay | 0
pre_auth_delay | 0
random_page_cost | 4
regex_flavor | advanced
search_path | "$user",public
seq_page_cost | 1
server_encoding | UTF8
server_version | 8.3.8
server_version_num | 80308
session_replication_role | origin
shared_buffers | 240MB
silent_mode | off
sql_inheritance | on
ssl | off
standard_conforming_strings | off
statement_timeout | 0
superuser_reserved_connections | 3
synchronize_seqscans | on
synchronous_commit | on
syslog_facility | LOCAL0
syslog_ident | postgres
tcp_keepalives_count | 9
tcp_keepalives_idle | 7200
tcp_keepalives_interval | 75
temp_buffers | 1024
temp_tablespaces |
TimeZone | Asia/Kolkata
timezone_abbreviations | Default
trace_notify | off
trace_sort | off
track_activities | on
track_counts | on
transaction_isolation | read committed
transaction_read_only | off
transform_null_equals | off
unix_socket_group |
unix_socket_permissions | 511
update_process_title | on
vacuum_cost_delay | 50ms
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
vacuum_freeze_min_age | 100000000
wal_buffers | 10MB
wal_sync_method | fdatasync
wal_writer_delay | 200ms
work_mem | 64MB
xmlbinary | base64
xmloption | content
zero_damaged_pages | off
(176 rows)

Today we did more analysis and observed postgress processes that
continually reported status 'D' in top. The corresponding vmstat showed
a proportionate amount of processes under the 'b' column,
"uninterruptible" state.

We've been able to match long running database queries to such
processes. This occurs under relatively low load average (say 4 out of
8) and can involve as little as 1 single sql query.
It seems that many queries get into that state and that is causing our
load average to spike very high.
Queries are finishing even though we continue to see an increase in
postgres processes in 'D' state.
Are we facing some serious db locking? What could lead to this?
(The box has 8G and 8 cores)

Thanks for any help,
Anne

-----Original Message-----
From: Andy Colson [mailto:andy(at)squeakycode(dot)net]
Sent: Tuesday, January 25, 2011 2:13 PM
To: Anne Rosset
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Queries becoming slow under heavy load

On 1/25/2011 3:37 PM, Anne Rosset wrote:
> 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:
>

<SNIP>

> 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
>

While I applaud your attempt to get us lots of information,
unfortunately the the one property you ask about (shared_buffers), I
can't seem to find.

So, maybe you could post a bit more:

1) how many concurrent clients?
2) can we see an explain analyze for a query when its fast, and then
again when its slow?
3) Is this box dedicated to PG or are there other services running?
4) Looks like you have 8 Gig of ram, so I assume this is a 64 bit OS,
can you tell us what you have for:

shared_buffers
effective_cahce_size
work_mem

5) Once many clients start hitting the db, it might not all fit into ram
and start hitting the HD, can you tell us what sort of IO you have
(sata, scsi, raid, # of disks, etc).

The stats from /proc/meminfo:
SwapTotal: 2097112 kB
SwapFree: 2096612 kB

Was this run when the system was busy? Looks like you are not using any
swap, so thats good at least. Oh, wait, there are two cat
/proc/meminfo's. Is one when its fast and one when its slow?

Looks to me, in both cases, you are not using much memory at all. (if
you happen to have 'free', its output is a little more readable, if you
wouldn't mind posting it (only really need it for when the box is slow)

-Andy