Reducing worker mem shaved about 12 minutes off the query time.. Thanks for the suggestion. I lowered it to 10MB instead of 100MB > On Dec 29, 2016, at 8:07 PM, Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> wrote: > > Forwarding to list. > > -----Original Message----- > From: ajmcello [mailto:ajmcello78@xxxxxxxxx] > Sent: Freitag, 30. Dezember 2016 07:05 > To: Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> > Subject: Re: 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 cannot connect > to server due to load increasing because of server connections. But I'm more interested in tuning the server for better query > 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@xxxxxxxxxxxx> wrote: >> >> Hello >> >>> -----Original Message----- >>> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of ajmcello >>> Sent: Freitag, 30. Dezember 2016 05:54 >>> To: POSTGRES <pgsql-general@xxxxxxxxxxxxxx> >>> Subject: 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 working memory > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
