Re: performance tuning postgresql 9.5.5.10 [enterprisedb]

On Fri, Dec 30, 2016 at 12:06 PM, ajmcello <ajmcello78(at)gmail(dot)com> wrote:

> Reducing worker mem shaved about 12 minutes off the query time.. Thanks
> for the suggestion. I lowered it to 10MB instead of 100MB
>
> [SNIP]
>
> >>> [postgresql.conf]
> >>> max_connections = 100000
> >>> max_files_per_process = 1000000
> >>> shared_buffers = 24GB
> >>> max_locks_per_transaction = 1000
> >>> effective_cache_size = 50GB
> >>> work_mem = 100MB
> >>> maintenance_work_mem = 2GB
> >>> log_min_duration_statement = 10000
> >>> checkpoint_completion_target = 0.9
> >>> wal_buffers = 32MB
> >>> default_statistics_target = 100
> >>> listen_addresses = '*'
> >>> port = 5432
> >>> ssl = off
> >>> wal_sync_method = fdatasync
> >>> synchronous_commit = on
> >>> fsync = off
> >>> wal_level = minimal
> >>> #client_min_messages = fatal
> >>> #log_min_messages = fatal
> >>> #log_min_error_statement = fatal
> >>> datestyle = 'iso, mdy'
> >>> debug_pretty_print = off
> >>> debug_print_parse = off
> >>> debug_print_plan = off
> >>> debug_print_rewritten = off
> >>> default_text_search_config = 'pg_catalog.english'
> >>> enable_bitmapscan = on
> >>> enable_hashagg = on
> >>> enable_hashjoin = on
> >>> enable_indexonlyscan = on
> >>> enable_indexscan = on
> >>> enable_material = on
> >>> enable_mergejoin = on
> >>> enable_nestloop = on
> >>> enable_seqscan = on
> >>> enable_sort = on
> >>> enable_tidscan = on
> >>> from_collapse_limit = 8
> >>> geqo = on
> >>> geqo_threshold = 12
> >>> log_checkpoints = off
> >>>
> >>> log_connections = off
> >>> log_disconnections = off
> >>> log_duration = off
> >>> log_executor_stats = off
> >>> log_hostname = off
> >>> log_parser_stats = off
> >>> log_planner_stats = off
> >>> log_replication_commands = off
> >>> log_statement_stats = off
> >>> log_timezone = 'UTC'
> >>> max_wal_size = 1GB
> >>> min_wal_size = 80MB
> >>> shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen'
> >>> stats_temp_directory = 'pg_stat_tmp'
> >>> timezone = 'US/Pacific'
> >>> track_activities = on
> >>> track_counts = on
> >>> track_io_timing = off
> >>>
> >>>
> >>> Thanks in advance.
> >>>
> >>>
> >>>
> >>>
> >>
> >>
>

The number of connections that you are attempting from Postgres is way too
high. You should be using a connection pooler like pgbouncer, and reduce
the number of connections at postgres level.

Amitabh