Re: default_statistics_target

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



HI Greg,


Thanks for the insight. How much more of a server's resources will be consumed by an ANALYZE with default_statistics_target = 100?

We have two environments hosting the same data. One is our "live" server, which serves the web site, and this hosts our published data, not more than 200 - 300 tables.

PRODUCTION: The data warehouse consisting of our published data, as well as our "input resources" which are transformed via ETL processes into our published data. It is these "input resources" which currently consist of about 8,000 tables and growing. Don't really require analysis, as they are typically run once in a linear read when importing.they are typically read linearly, and rarely more than once. They are kept for auditing and rollbacks.

LIVE: Hosts just the published data, copied over from the production server. Because the data does not get written to very often, older stats from ANALYZE are likely to still be valid. Our concern is that with the older setting of default_statistics_target = 10 it has not gone deep enough into these tables (numbering in the millios of rows) to really represent the data distribution properly.

Given that it looks like you're running 8.3 from past messages I've seen from you, I'd also be concerned that you've overrun your max_fsm_pages, so that VACUUM is growing increasing ineffective for you, and that's contributing to your headache.

Below are the config values of our production server (those not listed are those stubbed out) . Sadly, in an attempt to improve the server's performance, someone wiped out all of the changes I had made to date, along with comments indicating previous values, reason for the change, etc. What do they call that again? Oh, yeah. Documentation.

# CENTOS 5.4
# Linux mdx_octo 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
# pgsql 8.3.10, 8 CPUs, 48GB RAM
# RAID 10, 4 Disks
autovacuum = on   # Enable autovacuum subprocess?  'on'
autovacuum_analyze_scale_factor = 0.05 # fraction of table size before analyze
autovacuum_analyze_threshold = 1000
autovacuum_naptime = 1min  # time between autovacuum runs
autovacuum_vacuum_cost_delay =  50 # default vacuum cost delay for
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_vacuum_threshold = 1000
bgwriter_lru_maxpages = 100  # 0-1000 max buffers written/round
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
checkpoint_warning = 290s  # 0 is off
client_min_messages =  debug1 # values in order of decreasing detail:
datestyle = 'iso, mdy'
default_statistics_target = 250         # range 1-1000
default_text_search_config = 'pg_catalog.english'
lc_messages = 'C'   # locale for system error message
lc_monetary = 'C'   # locale for monetary formatting
lc_numeric = 'C'   # locale for number formatting
lc_time = 'C'    # locale for time formatting
listen_addresses = '*'  # what IP address(es) to listen on;
log_destination = 'stderr'  # Valid values are combinations of
log_error_verbosity =  verbose  # terse, default, or verbose messages
log_line_prefix = '%t '   # special values:
log_min_error_statement =  debug1 # values in order of decreasing detail:
log_min_messages = debug1  # values in order of decreasing detail:
logging_collector = on  # Enable capturing of stderr and csvlog
maintenance_work_mem = 256MB
max_connections = 100   # (change requires restart)
max_fsm_relations = 1000  # min 100, ~70 bytes each
max_locks_per_transaction = 128  # min 10
port = 5432    # (change requires restart)
shared_buffers = 4096MB
shared_preload_libraries = '$libdir/plugins/plugin_debugger.so' # (change requires restart)
track_counts = on
vacuum_cost_delay = 5   # 0-1000 milliseconds
wal_buffers = 4MB
wal_sync_method = open_sync
work_mem = 64MB

Carlo


"Greg Smith" <greg@xxxxxxxxxxxxxxx> wrote in message news:4B9E33AF.2020608@xxxxxxxxxxxxxxxxxx
Carlo Stonebanks wrote:
The whole topic of messing with stats makes my head spin but I am concerned about some horridly performing queries that have had bad rows estimates and others which always choose seq scans when indexes are available. Reading up on how to improve planner estimates, I have seen references to default_statistics_target being changed from the default of 10 to 100.

Our DB is large, with thousands of tables

Stop right there for a second. Are you sure autovacuum is working well here? With thousands of tables, it wouldn't surprise me to discover your planner estimates are wrong because there hasn't been a recent enough ANALYZE on the relevant tables. If you haven't already, take a look at pg_stat_user_tables and make sure that tables that have the bad estimates have actually been analyzed recently. A look at the live/dead row counts there should be helpful as well.

If all that's recent, but you're still getting bad estimates, only then would I suggest trying an increase to default_statistics_target. In the situation where autovacuum isn't keeping up with some tables because you have thousands of them, increasing the stats target can actually make the problem worse, because the tables that are getting analyzed will take longer to process--more statistics work to be done per table.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@xxxxxxxxxxxxxxx   www.2ndQuadrant.us


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux