Skip site navigation (1) Skip section navigation (2)

Queries becoming slow under heavy load

From: "Anne Rosset" <arosset(at)collab(dot)net>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Queries becoming slow under heavy load
Date: 2011-01-25 21:37:54
Message-ID: 945629628BB0174D86709AFE6D1CDEF5016342E3@SP-EXCHMBC.sp.corp.collab.net (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2011-01-25 22:12:24
Subject: Re: Queries becoming slow under heavy load
Previous:From: Mark KirkwoodDate: 2011-01-25 21:29:33
Subject: Re: Bloat issue on 8.3; autovac ignores HOT page splits?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group