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

Re: Postgres query completion status?

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To: Thom Brown <thombrown(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres query completion status?
Date: 2009-11-20 20:14:15
Message-ID: 4B06F897.3040709@cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Thom Brown wrote:

> 
> Okay, have you tried monitoring the connections to your database?
> 
> Try: select * from pg_stat_activity;

Tried that - it's very useful as far as it goes. I can see that in most 
cases, the DB is running just the one query.

What I really want to know is, how far through that query has it got?
(For example, if the query is an update, then surely it knows how many 
rows have been updated, and how many are yet to go).

> 
> And this to see current backend connections:
> 
> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>        pg_stat_get_backend_activity(s.backendid) AS current_query
>     FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
> 

This looks identical to just some of the columns from pg_stat_activity.


> It might also help if you posted your postgresql.conf too.

Below (have removed the really non-interesting bits).

Thanks,

Richard



> 
> Thom


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

max_connections = 500                   # (change requires restart)

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 4500MB                 # min 128kB
                                         # (change requires restart)
temp_buffers = 64MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                         # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared
# memory per transaction slot, plus lock space (see
# max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.

work_mem = 256MB                                # min 64kB
maintenance_work_mem = 256MB            # min 1MB
max_stack_depth = 4MB                   # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                         # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms                # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers 
scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1           # 1-1000. 0 disables prefetching


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#fsync = on                             # turns forced synchronization 
on or off
#synchronous_commit = on                # immediate fsync at commit
#wal_sync_method = fsync                # the default is the first option
                                         # supported by the operating 
system:
                                         #   open_datasync
                                         #   fdatasync
                                         #   fsync
                                         #   fsync_writethrough
                                         #   open_sync
#full_page_writes = on                  # recover from partial page writes
wal_buffers = 2MB                       # min 32kB
                                         # (change requires restart)
#wal_writer_delay = 200ms               # 1-10000 milliseconds

commit_delay = 50000                    # range 0-100000, in microseconds
commit_siblings = 5                     # range 1-1000

# - Checkpoints -

checkpoint_segments = 64                # in logfile segments, min 1, 
16MB each (was safe value of 4)
#checkpoint_timeout = 5min              # range 30s-1h
#checkpoint_completion_target = 0.5     # checkpoint target duration, 
0.0 - 1.0
#checkpoint_warning = 30s               # 0 disables

# - Archiving -

#archive_mode = off             # allows archiving to be done
                                 # (change requires restart)
#archive_command = ''           # command to use to archive a logfile 
segment
#archive_timeout = 0            # force a logfile segment switch after this
                                 # number of seconds; 0 disables


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1                      # measured on an arbitrary scale
#random_page_cost = 4                   # same scale as above
#seq_page_cost = 0.25                   # use 0.25, 0.75 for normal
#random_page_cost = 0.75                # but 1 and 4 for wave-deactivate.
seq_page_cost = 0.5                     # It looks as though 0.5 and 2 
(exactly)
random_page_cost = 2                    # will work for both problems. 
(very brittle fix!)
#cpu_tuple_cost = 0.01                  # same scale as above
#cpu_index_tuple_cost = 0.005           # same scale as above
#cpu_operator_cost = 0.0025             # same scale as above
effective_cache_size = 10000MB

# - Genetic Query Optimizer -

#geqo = on
geqo_threshold = 12
geqo_effort = 10                        # range 1-10
#geqo_pool_size = 0                     # selects default based on effort
#geqo_generations = 0                   # selects default based on effort
#geqo_selection_bias = 2.0              # range 1.5-2.0

# - Other Planner Options -

default_statistics_target = 1000        # range 1-10000
#constraint_exclusion = partition       # on, off, or partition
#cursor_tuple_fraction = 0.1            # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8                # 1 disables collapsing of explicit
                                         # JOIN clauses


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------


# - When to Log -

#client_min_messages = notice

#log_error_verbosity = default          # terse, default, or verbose 
messages

#log_min_error_statement = error

log_min_duration_statement = 80

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
log_line_prefix = '%t '

#log_lock_waits = off                   # log lock waits >= deadlock_timeout
#log_statement = 'none'                 # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or 
larger
                                         # than the specified size in 
kilobytes;
                                         # -1 disables, 0 logs all temp 
files
#log_timezone = unknown                 # actually, defaults to TZ 
environment
                                         # setting


#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_functions = none                 # none, pl, all
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on                        # Enable autovacuum subprocess? 
  'on'
autovacuum = on                                 # requires track_counts 
to also be on.
log_autovacuum_min_duration = 1000      # -1 disables, 0 logs all 
actions and
                                         # their durations, > 0 logs only
                                         # actions running at least this 
number
                                         # of milliseconds.
#autovacuum_max_workers = 3             # max number of autovacuum 
subprocesses
#autovacuum_naptime = 1min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 50       # min number of row updates before
                                         # vacuum
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                         # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before 
vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before 
analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced 
vacuum
                                         # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                         # autovacuum, in milliseconds;
                                         # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                         # autovacuum, -1 means use
                                         # vacuum_cost_limit


In response to

Responses

pgsql-performance by date

Next:From: Lorenzo AllegrucciDate: 2009-11-20 20:24:38
Subject: Re: Strange performance degradation
Previous:From: Thom BrownDate: 2009-11-20 19:39:53
Subject: Re: Postgres query completion status?

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