Re: performance tuning postgresql 9.5.5.10 [enterprisedb]

Amitabh Kant <amitabhkant@xxxxxxxxx> · Sat, 31 Dec 2016 09:15:41 +0530

On Fri, Dec 30, 2016 at 12:06 PM, ajmcello <ajmcello78@xxxxxxxxx> 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 