Queries becoming slow under heavy load

От: Anne Rosset
Тема: Queries becoming slow under heavy load
Дата: ,
Msg-id: 945629628BB0174D86709AFE6D1CDEF5016342E3@SP-EXCHMBC.sp.corp.collab.net
(см: обсуждение, исходный текст)
Ответы: Re: Queries becoming slow under heavy load  (Andy Colson)
Re: Queries becoming slow under heavy load  ("Kevin Grittner")
Re: Queries becoming slow under heavy load  (Ivan Voras)
Список: pgsql-performance

Скрыть дерево обсуждения

Queries becoming slow under heavy load  ("Anne Rosset", )
 Re: Queries becoming slow under heavy load  (Andy Colson, )
 Re: Queries becoming slow under heavy load  ("Kevin Grittner", )
 Re: Queries becoming slow under heavy load  ("Ing. Marcos Ortiz Valmaseda", )
 Re: Queries becoming slow under heavy load  (Ivan Voras, )
  Re: Queries becoming slow under heavy load  ("mark", )
 FW: Queries becoming slow under heavy load  ("Anne Rosset", )
  Re: FW: Queries becoming slow under heavy load  (Shaun Thomas, )
   Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )
  Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )
   Re: FW: Queries becoming slow under heavy load  ("Anne Rosset", )
    Re: FW: Queries becoming slow under heavy load  (Shaun Thomas, )
     Re: FW: Queries becoming slow under heavy load  (Mladen Gogala, )
     Re: FW: Queries becoming slow under heavy load  ("Anne Rosset", )
      Re: FW: Queries becoming slow under heavy load  (Scott Marlowe, )

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


В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Дата:
Сообщение: Re: Running PostgreSQL as fast as possible no matter the consequences
От: Ivan Voras
Дата:
Сообщение: Re: Queries becoming slow under heavy load