Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb] - Mailing list pgsql-general

Reducing worker mem shaved about 12 minutes off the query time.. Thanks for the suggestion. I lowered it to 10MB
insteadof 100MB 



> On Dec 29, 2016, at 8:07 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:
>
> Forwarding to list.
>
> -----Original Message-----
> From: ajmcello [mailto:ajmcello78@gmail.com]
> Sent: Freitag, 30. Dezember 2016 07:05
> To: Charles Clavadetscher <clavadetscher@swisspug.org>
> Subject: Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]
>
> There are no connections except one cli when running the query. After that finishes then I get connection refused  or
cannotconnect 
> to server due to load increasing because of server connections. But I'm more interested in tuning the server for
betterquery 
> response time. Is there anything in the configuration that would help?
>
> Sent from my iPhone
>
>> On Dec 29, 2016, at 7:35 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:
>>
>> Hello
>>
>>> -----Original Message-----
>>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of ajmcello
>>> Sent: Freitag, 30. Dezember 2016 05:54
>>> To: POSTGRES <pgsql-general@postgresql.org>
>>> Subject: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]
>>>
>>>
>>> I am trying to optimize and tune my server for fastest simple queries with highest connection to server possible.
>>> Basically, a SELECT item from table takes 30 minutes on a machine with SSD drives. The table has 900K entries and
12
>>> columns.  Using that SELECT query, I then have the ability to make about 500 simultaneous connections to the server
>>> before errors start to occur. So, I can live with 500, but the slow query gives me grief.
>>
>> From previous posts of other users, I assume that in order to get help you will need to provide some more
information.Here the 
> questions that come to my mind.
>>
>> What errors do you get from the server when you reach the 500 connections?
>>
>> How long does it take to run the query without heavy load, e.g. just one user connected?
>>
>> \timing on
>> query
>>
>> How does the execution plan look like? There you may see if the index is used at all.
>>
>> EXPLAIN ANALYZE query;
>>
>>> I have a GIST index for the table. I tried btree, but according to performance tests, GIST was faster. So I went
>>> with GIST.
>>>
>>> The system drive and pgsql drive are separate. I can separate them further if need to be. Total ram is 56GB. I
added
>>> 32GB of swap.
>>>
>>> Is there anything I can do to speed up the SELECT statement?
>>
>> Could you provide the statement and the table definition?
>>
>>> Here is what I have:
>>>
>>> [sysctl.conf]
>>> net.ipv4.conf.default.rp_filter=1
>>> net.ipv4.conf.all.rp_filter=1
>>> net.ipv4.tcp_syncookies=1
>>> net.ipv4.ip_forward=1
>>> net.ipv6.conf.all.forwarding=1
>>> net.ipv4.conf.all.accept_redirects=0
>>> net.ipv6.conf.all.accept_redirects=0
>>> net.ipv4.conf.all.accept_source_route=0
>>> net.ipv6.conf.all.accept_source_route=0
>>> net.ipv4.conf.all.log_martians=1
>>> kernel.sysrq=0
>>> kernel.shmmax=2147483999999
>>> kernel.shmall=2097159999999
>>> #32GBkernel.shmmax=17179869184
>>> #32GBkernel.shmall=4194304
>>> kernel.shmmni=999999999
>>> kernel.shmmin=1
>>> kernel.shmseg=10
>>> semmsl, semmns, semopm, semmni kernel.sem=250 32000 100 128
>>> fs.file-max=65536
>>> kern.maxfiles=50000
>>> kern.maxfilesperproc=50000
>>> net.ipv4.ip_local_port_range=1024 65535
>>> net.ipv4.tcp_tw_recycle=1
>>> net.ipv4.tcp_fin_timeout=10
>>> net.ipv4.tcp_tw_reuse=1
>>> net.core.rmem_max=16777216
>>> net.core.wmem_max=16777216
>>> net.ipv4.tcp_max_syn_backlog=4096
>>> net.ipv4.tcp_syncookies=1
>>> kernel.sched_migration_cost_ns=5000000
>>> kernel.sched_migration_cost_ns=5000000
>>> kernel.sched_autogroup_enabled=0
>>> vm.swappiness=10
>>>
>>>
>>
>> Here are some helpful informations on the settings below:
>> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>>
>> I am not really the big expert, but 100'000 max_connections and work_mem of 100MB seems to me to be a problem:
>>
>> From the link mentioned right above:
>>
>> "This size (work_mem) is applied to each and every sort done by each user, and complex queries can use multiple
workingmemory 
> sort buffers. Set it to 50MB, and have 30 users submitting queries, and you are soon using 1.5GB of real memory."
>>
>> This is:
>> SELECT * FROM pg_size_pretty((50.0*(2^20)*30.0)::BIGINT);
>> -[ RECORD 1 ]--+--------
>> pg_size_pretty | 1500 MB
>>
>> Applied to your settings:
>>
>> SELECT * FROM pg_size_pretty((100.0*(2^20)*100000.0)::BIGINT);
>> -[ RECORD 1 ]--+--------
>> pg_size_pretty | 9766 GB
>>
>> This could explain the errors you get from the server. You may be trying to use much more memory than you have.
>>
>> Regards
>> Charles
>>
>>> [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.
>>>
>>>
>>>
>>>
>>
>>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general