Search Postgresql Archives

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

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

 



Yes, Michael, that I have noticed too, but should have written more in my original mail.

The query when I try it does run in 15ms, but evening logs show the query (I think only small percentage of it) running 1-3s.

At the same time my CentOS 8 server with 64 GB RAM is never loaded, the load average show by top is 0.35-0.45.

What could be the reason, does PostgreSQL 13.2 has some scheduled task maybe?

I have only changed few lines in the postgresql.conf:

# diff postgresql.conf.OLD postgresql.conf
64c64
< max_connections = 100                 # (change requires restart)
---
> max_connections = 120                 # (change requires restart)
130c130
< #work_mem = 4MB                               # min 64kB
---
> work_mem = 8MB                                # min 64kB
132c132
< #maintenance_work_mem = 64MB          # min 1MB
---
> maintenance_work_mem = 128MB          # min 1MB
393c393
< #effective_cache_size = 4GB
---
> effective_cache_size = 8GB
410,411c410,411
< #from_collapse_limit = 8
< #join_collapse_limit = 8              # 1 disables collapsing of explicit
---
> from_collapse_limit = 24
> join_collapse_limit = 24              # 1 disables collapsing of explicit
469c469
< #log_min_messages = warning           # values in order of decreasing detail:
---
> log_min_messages = notice             # values in order of decreasing detail:
497c497
< #log_min_duration_statement = -1      # -1 is disabled, 0 logs all statements
---
> log_min_duration_statement = 1000     # -1 is disabled, 0 logs all statements

And I have pgbouncer in front of the PostgreSQL:

 diff pgbouncer.ini.OLD pgbouncer.ini
12a13,15
> words_de = host=/tmp user=xxx password=xxx dbname=words_de
> words_en = host=/tmp user=xxx  password=xxx dbname=words_en
> words_ru = host=/tmp user=xxx password=xxx dbname=words_ru
115a119
> ;; auth_type = md5
148c152
< ;server_reset_query = DISCARD ALL
---
> server_reset_query = DISCARD ALL
156c160
< ;ignore_startup_parameters = extra_float_digits
---
> ignore_startup_parameters = extra_float_digits
180c184
< ;max_client_conn = 100
---
> max_client_conn = 1000
185c189
< ;default_pool_size = 20
---
> default_pool_size = 100

Or is maybe everything ok...? The daily gzipped dump grows big:

939343358 Dec 31 01:33 words_ru-Dec.sql.gz
1221682336 Jan 31 01:33 words_ru-Jan.sql.gz
1423324283 Feb 20 01:34 words_ru-Feb.sql.gz

and being an inexperienced pg admin I am a bit worried.

Thank you
Alex

[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 Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux