Search Postgresql Archives

Re: performance tuning postgresql 9.5.5.10 [enterprisedb]

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

 





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 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux