Re: what happens when you issue ALTER SERVER in a hot environment?

[Laurenz Albe]

On Tue, 2020-04-07 at 00:53 -0400, Tom Lane wrote:
> "David G. Johnston"  writes:
> > On Monday, April 6, 2020, AC Gomez  wrote:
> > > If you issue an  ALTER SERVER command and there are active connections
> > > with that server in use or new ones are coming in, what happens? Docs on
> > > this command say nothing regarding active processing using the server
> > > context and changes to it. So I assume it's just handled.
> > Not sure if there are exceptions but assume that nothing external will
> > change your current active session’s settings out from underneath you.
> 
> Couple of comments here:
> 
> * ALTER SERVER changes nothing until "pg_ctl reload" or similar is
> issued.

I think you both mixed up ALTER SYSTEM and ALTER SERVER.

The details of when exactly an ALTER SERVER will affect a query that uses
a foreign table on the server will vary depending on the implementation
of the foreign data wrapper, but typically the settings that were in effect
when the query was *planned* will be the ones used.

Typically, queries are planned right before they are executed.  Any query
that is currently executing will continue to do so, but queries planned
after the ALTER SERVER will use the new values.

Sometimes plans are cached, but all cached plans that involve the changed
server will be invalidated and re-planned after the ALTER SERVER.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: How to prevent master server crash if hot standby stops

[Laurenz Albe]

On Tue, 2020-04-07 at 00:50 +0300, Andrus wrote:
> > If you prefer replication to fail silently, don't use replication
> > slots.  Use "wal_keep_segments" instead.
> 
> I desided to give 1 GB to wal. So I added
> 
> wal_keep_segments=60
> 
> After some time Postgres created 80 files with total size 1.3GB. 
> 
> How to fix this so that no more than 1 GB of disk space is used ?
> How to get information how may wal files are yet not processed by slave ?
> How to delete processed wal files so that 1 GB of disk space can used for 
> some other purposes ?

"wal_keep_segments" is the number of old WAL segments the server keeps around
for the standby.  But there are also some segments that are created for future
use (the minimum is governed by "min_wal_size").

All these limits are not hard limits, the server will try to keep them
more or less.  Consider that WAL segments are created as needed, but only
removed during checkpoints.

So, about your first question, you cannot.
Always make sure that there is more disk space available.

About your second question, you also cannot do that.
The primary server has no idea which standby server needs which WAL information.
All you can tell is where the currently connected standby servers are:

  SELECT pg_walfile_name(flush_lsn) FROM pg_stat_replication;

About your third question, you *never* manually mess with the files in pg_wal.
The server does that.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: EINTR while resizing dsm segment.

[Nicola Contu]

So that seems to be a bug, correct?
Just to confirm, I am not using NFS, it is directly on disk.

Other than that, is there a particular option we can set in the
postgres.conf to mitigate the issue?

Thanks a lot for your help.


Il giorno sab 4 apr 2020 alle ore 02:49 Thomas Munro 
ha scritto:

> On Thu, Apr 2, 2020 at 9:25 PM Kyotaro Horiguchi
>  wrote:
> > I provided the subject, and added -hackers.
> >
> > > Hello,
> > > I am running postgres 11.5 and we were having issues with shared
> segments.
> > > So I increased the max_connection as suggested by you guys and reduced
> my
> > > work_mem to 600M.
> > >
> > > Right now instead, it is the second time I see this error :
> > >
> > > ERROR:  could not resize shared memory segment
> "/PostgreSQL.2137675995" to
> > > 33624064 bytes: Interrupted system call
> >
> > The function posix_fallocate is protected against EINTR.
> >
> > | do
> > | {
> > |   rc = posix_fallocate(fd, 0, size);
> > | } while (rc == EINTR && !(ProcDiePending || QueryCancelPending));
> >
> > But not for ftruncate and write. Don't we need to protect them from
> > ENTRI as the attached?
>
> We don't handle EINTR for write() generally because that's not
> supposed to be necessary on local files (local disks are not "slow
> devices", and we document that if you're using something like NFS you
> should use its "hard" mount option so that it behaves that way too).
> As for ftruncate(), you'd think it'd be similar, and I can't think of
> a more local filesystem than tmpfs (where POSIX shmem lives on Linux),
> but I can't seem to figure that out from reading man pages; maybe I'm
> reading the wrong ones.  Perhaps in low memory situations, an I/O wait
> path reached by ftruncate() can return EINTR here rather than entering
> D state (non-interruptable sleep) or restarting due to our SA_RESTART
> flag... anyone know?
>
> Another thought: is there some way for the posix_fallocate() retry
> loop to exit because (ProcDiePending || QueryCancelPending), but then
> for CHECK_FOR_INTERRUPTS() to do nothing, so that we fall through to
> reporting the EINTR?
>

Re: How to prevent master server crash if hot standby stops

[Andrus]

Hi!


About your third question, you *never* manually mess with the files in pg_wal.
The server does that.


Is it OK  to stop server, delete all files in pg_wal directory and re-start 
server ?

Or should default value put back and wait until server frees 1 GB disk space ?

Andrus.

Re: EINTR while resizing dsm segment.

[Nicola Contu]

The only change we made on the disk, is the encryption at OS level.
Not sure this can be something related.

Il giorno mar 7 apr 2020 alle ore 10:58 Nicola Contu 
ha scritto:

> So that seems to be a bug, correct?
> Just to confirm, I am not using NFS, it is directly on disk.
>
> Other than that, is there a particular option we can set in the
> postgres.conf to mitigate the issue?
>
> Thanks a lot for your help.
>
>
> Il giorno sab 4 apr 2020 alle ore 02:49 Thomas Munro <
> thomas.mu...@gmail.com> ha scritto:
>
>> On Thu, Apr 2, 2020 at 9:25 PM Kyotaro Horiguchi
>>  wrote:
>> > I provided the subject, and added -hackers.
>> >
>> > > Hello,
>> > > I am running postgres 11.5 and we were having issues with shared
>> segments.
>> > > So I increased the max_connection as suggested by you guys and
>> reduced my
>> > > work_mem to 600M.
>> > >
>> > > Right now instead, it is the second time I see this error :
>> > >
>> > > ERROR:  could not resize shared memory segment
>> "/PostgreSQL.2137675995" to
>> > > 33624064 bytes: Interrupted system call
>> >
>> > The function posix_fallocate is protected against EINTR.
>> >
>> > | do
>> > | {
>> > |   rc = posix_fallocate(fd, 0, size);
>> > | } while (rc == EINTR && !(ProcDiePending || QueryCancelPending));
>> >
>> > But not for ftruncate and write. Don't we need to protect them from
>> > ENTRI as the attached?
>>
>> We don't handle EINTR for write() generally because that's not
>> supposed to be necessary on local files (local disks are not "slow
>> devices", and we document that if you're using something like NFS you
>> should use its "hard" mount option so that it behaves that way too).
>> As for ftruncate(), you'd think it'd be similar, and I can't think of
>> a more local filesystem than tmpfs (where POSIX shmem lives on Linux),
>> but I can't seem to figure that out from reading man pages; maybe I'm
>> reading the wrong ones.  Perhaps in low memory situations, an I/O wait
>> path reached by ftruncate() can return EINTR here rather than entering
>> D state (non-interruptable sleep) or restarting due to our SA_RESTART
>> flag... anyone know?
>>
>> Another thought: is there some way for the posix_fallocate() retry
>> loop to exit because (ProcDiePending || QueryCancelPending), but then
>> for CHECK_FOR_INTERRUPTS() to do nothing, so that we fall through to
>> reporting the EINTR?
>>
>

Re: EINTR while resizing dsm segment.

[Thomas Munro]

On Tue, Apr 7, 2020 at 8:58 PM Nicola Contu  wrote:
> So that seems to be a bug, correct?
> Just to confirm, I am not using NFS, it is directly on disk.
>
> Other than that, is there a particular option we can set in the postgres.conf 
> to mitigate the issue?

Hi Nicola,

Yeah, I think it's a bug.  We're not sure exactly where yet.

Re: How to prevent master server crash if hot standby stops

[Laurenz Albe]

On Tue, 2020-04-07 at 12:05 +0300, Andrus wrote:
> > About your third question, you *never* manually mess with the files in 
> > pg_wal.
> > The server does that.
> 
> Is it OK  to stop server, delete all files in pg_wal directory and re-start 
> server ?

No.

> Or should default value put back and wait until server frees 1 GB disk space ?

You'd have to set "wal_keep_segments" and "max_wal_size" lower, then have
normal database activity and wait until a couple of checkpoints have passed.

But frankly, 1 GB is ridiculously little.  If you have to worry about that
much disk space, you're must be running PostgreSQL on a toaster.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

PostgreSQL native multi-master

[Vano Beridze]

Hello,

What are the plans to support multi-master natively?
What solution would you recommend at this point? preferably free.

Kind regards,
Vano

Re: what happens when you issue ALTER SERVER in a hot environment?

[AC Gomez]

Thank you for clarifying. Don't you think this is pertinent information
that should be in the ALTER SERVER doc page?

On Tue, Apr 7, 2020, 2:59 AM Laurenz Albe  wrote:

> On Tue, 2020-04-07 at 00:53 -0400, Tom Lane wrote:
> > "David G. Johnston"  writes:
> > > On Monday, April 6, 2020, AC Gomez  wrote:
> > > > If you issue an  ALTER SERVER command and there are active
> connections
> > > > with that server in use or new ones are coming in, what happens?
> Docs on
> > > > this command say nothing regarding active processing using the server
> > > > context and changes to it. So I assume it's just handled.
> > > Not sure if there are exceptions but assume that nothing external will
> > > change your current active session’s settings out from underneath you.
> >
> > Couple of comments here:
> >
> > * ALTER SERVER changes nothing until "pg_ctl reload" or similar is
> > issued.
>
> I think you both mixed up ALTER SYSTEM and ALTER SERVER.
>
> The details of when exactly an ALTER SERVER will affect a query that uses
> a foreign table on the server will vary depending on the implementation
> of the foreign data wrapper, but typically the settings that were in effect
> when the query was *planned* will be the ones used.
>
> Typically, queries are planned right before they are executed.  Any query
> that is currently executing will continue to do so, but queries planned
> after the ALTER SERVER will use the new values.
>
> Sometimes plans are cached, but all cached plans that involve the changed
> server will be invalidated and re-planned after the ALTER SERVER.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: PostgreSQL native multi-master

[Andreas Kretschmer]

Am 07.04.20 um 13:39 schrieb Vano Beridze:

Hello,

What are the plans to support multi-master natively?
What solution would you recommend at this point? preferably free.


BDR3 works well for our customers, but it isn't free. You can ask us for 
more information.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Using of --data-checksums

[BGoebel]

initdb --data-checksums "... help to detect corruption by the I/O system"
There is an (negligible?) impact on performance, ok. 
 
Is there another reason NOT to use this feature ?
Has anyone had good or bad experience with the use of  --data-checksums?

Thanks in advance!

Bernhard



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Estimated resources for a 500 connections instance (VM)

[David Gauthier]

psql (9.6.0, server 11.3) on linux

We've ramped up usage on a PG server (a VM, I have no choice about this)
and are approaching the 100 connections limit.  We could increase the
limit, but I've read that this can lead to a degradation in performance. If
we bump it up to 500, what kind of compute resources would that require
(mem/cpu)?  Any estimates ?

Thanks !

Re: Estimated resources for a 500 connections instance (VM)

[Josef Šimánek]

Hello.

Have you checked
https://wiki.postgresql.org/wiki/Number_Of_Database_Connections?

For raw estimations you can also check http://pgconfigurator.cybertec.at/
 or https://pgtune.leopard.in.ua/#/.

út 7. 4. 2020 v 18:25 odesílatel David Gauthier 
napsal:

> psql (9.6.0, server 11.3) on linux
>
> We've ramped up usage on a PG server (a VM, I have no choice about this)
> and are approaching the 100 connections limit.  We could increase the
> limit, but I've read that this can lead to a degradation in performance. If
> we bump it up to 500, what kind of compute resources would that require
> (mem/cpu)?  Any estimates ?
>
> Thanks !
>

Re: Mixed Locales and Upgrading

[Don Seiler]

On Mon, Mar 30, 2020 at 4:39 PM Don Seiler  wrote:

> On Mon, Mar 30, 2020 at 4:30 PM Tom Lane  wrote:
>
>> Don Seiler  writes:
>> > Actually, would I need to re-index on text columns that we know contain
>> > UUID strings? UUID characters seem to be pretty basic alphanumeric ASCII
>> > characters.
>>
>> I think you're all right with respect to those, since they're the
>> same under any encoding.  It's columns containing non-ASCII characters
>> that you'd want to worry about reindexing.
>
>
Follow-up question, the locale setting on the host would still be set to
en_US (as would the postgres and template0 databases). Should I look to
change that locale on the system to en_US.UTF-8, or even just for the
postgres user that the DB cluster runs as? What are the ramification for
doing (or not doing) so?

Don.

-- 
Don Seiler
www.seiler.us

Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Andrus]

Hi!

Query returns no rows but its execution time in Postgres 12 depends on the 
column expression.

Query with  column expression

coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
where toode=toode.toode and doktyyp='T' and not dok.taidetud and dok.kinnitatud 
and
taitmata is not null),0)

takes  666 ms :

explain analyze select
coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
where toode=toode.toode and doktyyp='T' and not dok.taidetud and dok.kinnitatud 
and
taitmata is not null),0)
from toode
   where toode.ribakood='testmiin'::text
  or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
  or toode.toode ilike '%'||'testmiin'||'%' escape '!'
  or toode.markused ilike '%'||'testmiin'||'%' escape '!'
  or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
or to_tsvector('english',toode.engnimetus) @@  
plainto_tsquery('testmiin')

"Gather  (cost=1000.00..505930.82 rows=153 width=32) (actual time=661.419..661.476 
rows=0 loops=1)"
"  Workers Planned: 1"
"  Workers Launched: 1"
"  ->  Parallel Seq Scan on toode  (cost=0.00..10015.31 rows=90 width=21) (actual 
time=574.922..574.922 rows=0 loops=2)"
"Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~* '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR 
(markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig, (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR 
(to_tsvector('english'::regconfig, (engnimetus)::text) @@ plainto_tsquery('testmiin'::text)))"

"Rows Removed by Filter: 7202"
"  SubPlan 1"
"->  Aggregate  (cost=3234.63..3234.64 rows=1 width=32) (never executed)"
"  ->  Nested Loop  (cost=11.26..3234.52 rows=43 width=3) (never 
executed)"
"->  Bitmap Heap Scan on rid  (cost=10.84..1191.72 rows=270 width=7) 
(never executed)"
"  Recheck Cond: (toode = toode.toode)"
"  Filter: (taitmata IS NOT NULL)"
"  ->  Bitmap Index Scan on rid_toode_pattern_idx  
(cost=0.00..10.77 rows=312 width=0) (never executed)"
"Index Cond: (toode = toode.toode)"
"->  Index Scan using dok_pkey on dok  (cost=0.42..7.57 rows=1 
width=4) (never executed)"
"  Index Cond: (dokumnr = rid.dokumnr)"
"  Filter: ((NOT taidetud) AND kinnitatud AND (doktyyp = 
'T'::bpchar))"
"Planning Time: 2.102 ms"
"JIT:"
"  Functions: 24"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 5.592 ms, Inlining 95.077 ms, Optimization 463.742 ms, 
Emission 277.062 ms, Total 841.473 ms"
"Execution Time: 666.007 ms"


Same query with sime column expression

1

run 3.6 times faster:


explain analyze select 1
from toode
   where toode.ribakood='testmiin'::text
  or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
  or toode.toode ilike '%'||'testmiin'||'%' escape '!'
  or toode.markused ilike '%'||'testmiin'||'%' escape '!'
  or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
or to_tsvector('english',toode.engnimetus) @@
plainto_tsquery('testmiin')

"Gather  (cost=1000.00..11030.61 rows=153 width=4) (actual time=182.414..185.648 
rows=0 loops=1)"
"  Workers Planned: 1"
"  Workers Launched: 1"
"  ->  Parallel Seq Scan on toode  (cost=0.00..10015.31 rows=90 width=4) (actual 
time=155.338..155.339 rows=0 loops=2)"
"Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~* '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR 
(markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig, (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR 
(to_tsvector('english'::regconfig, (engnimetus)::text) @@ plainto_tsquery('testmiin'::text)))"

"Rows Removed by Filter: 7202"
"Planning Time: 1.729 ms"
"Execution Time: 185.674 ms"

If there are more column expressions, perfomance difference is bigger.
rid  table used in column expression contains 1.8 million of rows.
Performance degradation probably occured if upgraded from Postgres 9.1 to 
Postgres 12

Since no data is returned query perfomance should be same.
How to fix it ?

Andrus. 

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Pavel Stehule]

út 7. 4. 2020 v 18:47 odesílatel Andrus  napsal:

> Hi!
>
> Query returns no rows but its execution time in Postgres 12 depends on the
> column expression.
>
> Query with  column expression
>
> coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
> where toode=toode.toode and doktyyp='T' and not dok.taidetud and
> dok.kinnitatud and
> taitmata is not null),0)
>
> takes  666 ms :
>
> explain analyze select
> coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
> where toode=toode.toode and doktyyp='T' and not dok.taidetud and
> dok.kinnitatud and
> taitmata is not null),0)
> from toode
> where toode.ribakood='testmiin'::text
>or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
>or toode.toode ilike '%'||'testmiin'||'%' escape '!'
>or toode.markused ilike '%'||'testmiin'||'%' escape '!'
>or to_tsvector('english',toode.nimetus) @@
> plainto_tsquery('testmiin')
>  or to_tsvector('english',toode.engnimetus) @@
> plainto_tsquery('testmiin')
>
> "Gather  (cost=1000.00..505930.82 rows=153 width=32) (actual
> time=661.419..661.476 rows=0 loops=1)"
> "  Workers Planned: 1"
> "  Workers Launched: 1"
> "  ->  Parallel Seq Scan on toode  (cost=0.00..10015.31 rows=90 width=21)
> (actual time=574.922..574.922 rows=0 loops=2)"
> "Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~*
> '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR
> (markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig,
> (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR
> (to_tsvector('english'::regconfig, (engnimetus)::text) @@
> plainto_tsquery('testmiin'::text)))"
> "Rows Removed by Filter: 7202"
> "  SubPlan 1"
> "->  Aggregate  (cost=3234.63..3234.64 rows=1 width=32) (never
> executed)"
> "  ->  Nested Loop  (cost=11.26..3234.52 rows=43 width=3) (never
> executed)"
> "->  Bitmap Heap Scan on rid  (cost=10.84..1191.72
> rows=270 width=7) (never executed)"
> "  Recheck Cond: (toode = toode.toode)"
> "  Filter: (taitmata IS NOT NULL)"
> "  ->  Bitmap Index Scan on rid_toode_pattern_idx
> (cost=0.00..10.77 rows=312 width=0) (never executed)"
> "Index Cond: (toode = toode.toode)"
> "->  Index Scan using dok_pkey on dok  (cost=0.42..7.57
> rows=1 width=4) (never executed)"
> "  Index Cond: (dokumnr = rid.dokumnr)"
> "  Filter: ((NOT taidetud) AND kinnitatud AND (doktyyp
> = 'T'::bpchar))"
> "Planning Time: 2.102 ms"
> "JIT:"
> "  Functions: 24"
> "  Options: Inlining true, Optimization true, Expressions true, Deforming
> true"
> "  Timing: Generation 5.592 ms, Inlining 95.077 ms, Optimization 463.742
> ms, Emission 277.062 ms, Total 841.473 ms"
> "Execution Time: 666.007 ms"
>
>
on your query there is too slow JIT. Is strange how much. So the best way
is disable JIT probably

set jit to off;

or same field in postgresql.conf

Regards

Pavel


> Same query with sime column expression
>
> 1
>
> run 3.6 times faster:
>
>
> explain analyze select 1
> from toode
> where toode.ribakood='testmiin'::text
>or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
>or toode.toode ilike '%'||'testmiin'||'%' escape '!'
>or toode.markused ilike '%'||'testmiin'||'%' escape '!'
>or to_tsvector('english',toode.nimetus) @@
> plainto_tsquery('testmiin')
>  or to_tsvector('english',toode.engnimetus) @@
>  plainto_tsquery('testmiin')
>
> "Gather  (cost=1000.00..11030.61 rows=153 width=4) (actual
> time=182.414..185.648 rows=0 loops=1)"
> "  Workers Planned: 1"
> "  Workers Launched: 1"
> "  ->  Parallel Seq Scan on toode  (cost=0.00..10015.31 rows=90 width=4)
> (actual time=155.338..155.339 rows=0 loops=2)"
> "Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~*
> '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR
> (markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig,
> (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR
> (to_tsvector('english'::regconfig, (engnimetus)::text) @@
> plainto_tsquery('testmiin'::text)))"
> "Rows Removed by Filter: 7202"
> "Planning Time: 1.729 ms"
> "Execution Time: 185.674 ms"
>
> If there are more column expressions, perfomance difference is bigger.
> rid  table used in column expression contains 1.8 million of rows.
> Performance degradation probably occured if upgraded from Postgres 9.1 to
> Postgres 12
>
> Since no data is returned query perfomance should be same.
> How to fix it ?
>
> Andrus.
>
>
>

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Andrus]

Hi!
>on your query there is too slow JIT. Is strange how much. So the best way is 
>disable JIT probably 

>set jit to off;
>or same field in postgresql.conf

Thank you.

set jit to off  

makes select fast.
I have encountered this issue only in this query in one database

There is  variation of this query running with diferent data in different 
database in same Debian 10 server. It works fast.
Should I disable jit only for this query or in postgresql.conf permanently?

Andrus.

Re: Estimated resources for a 500 connections instance (VM)

[Laurenz Albe]

On Tue, 2020-04-07 at 12:24 -0400, David Gauthier wrote:
> psql (9.6.0, server 11.3) on linux
> 
> We've ramped up usage on a PG server (a VM, I have no choice about this) and 
> are approaching the
> 100 connections limit.  We could increase the limit, but I've read that this 
> can lead to a
> degradation in performance. If we bump it up to 500, what kind of compute 
> resources would that
> require (mem/cpu)?  Any estimates ?

You should upgrade to the latest minor release.

Don't increase max_connections.  The main problem is that the more connections
there are, the greater the likelihood that too many of them will be active,
overloading the database machine.

This can for example happen if a DDL statement has to wait behind a lock.
Then other queries will "pile up" behind it, and as soon as the DDL is done
or canceled, the avalance will break loose.

The better solution is to use a connection pool.  If your application doesn't
have one, use pgBouncer.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Pavel Stehule]

út 7. 4. 2020 v 19:09 odesílatel Andrus  napsal:

> Hi!
> >on your query there is too slow JIT. Is strange how much. So the best way
> is disable JIT probably
> >set jit to off;
> >or same field in postgresql.conf
>
> Thank you.
>
> set jit to off
>
> makes select fast.
> I have encountered this issue only in this query in one database
>
> There is  variation of this query running with diferent data in different
> database in same Debian 10 server. It works fast.
> Should I disable jit only for this query or in postgresql.conf permanently?
>

you can do it in session

SET jit to off;
SELECT ..
SET jit to DEFAULT;

It is really strange why it is too slow. Can you prepare test case? Looks
like bug (maybe not Postgres's bug)

Pavel


> Andrus.
>

Re: Estimated resources for a 500 connections instance (VM)

[David Gauthier]

After looking at some of the factors that can affect this, I think it may
be important to know that most of the connections will be almost idle (in
terms of interacting with the DB).  The "users" are perl/dbi scripts which
connect to the DB and spend the vast majority of the time doing things
other than interacting with the DB.  So a connection is consumed, but it's
not really working very hard with the DB per-se.  I am cleaning up some of
that code by strategically connecting/disconnecting only when a DB
interaction is required.  But for my edification, is it roughly true that 2
connections working with the DB 100% of the time is equivalent to 20
connections @ 10% = 200 connections @ 1 % (if you know what I mean) ?

Re: Estimated resources for a 500 connections instance (VM)

[Sándor Daku]

On Tue, 7 Apr 2020 at 21:52, David Gauthier 
wrote:

> After looking at some of the factors that can affect this, I think it may
> be important to know that most of the connections will be almost idle (in
> terms of interacting with the DB).  The "users" are perl/dbi scripts which
> connect to the DB and spend the vast majority of the time doing things
> other than interacting with the DB.  So a connection is consumed, but it's
> not really working very hard with the DB per-se.  I am cleaning up some of
> that code by strategically connecting/disconnecting only when a DB
> interaction is required.  But for my edification, is it roughly true that 2
> connections working with the DB 100% of the time is equivalent to 20
> connections @ 10% = 200 connections @ 1 % (if you know what I mean) ?
>

Hi,

Every open connection consumes a bit of resources witch is not a big deal
if you keeping open a few more connections than you strictly needed.
However when you keeping a few hundred idle connections those resources add
up quickly. So don't do that if it's possible.
Likewise, establishing a new connection is resource costly process. So
don't do that either if it's possible.
Long story short, if those connections don't use many different users
then(as others already suggested) connection pooling will be the best
solution.

Regards,
Sándor

Re: Estimated resources for a 500 connections instance (VM)

[Tim Cross]

David Gauthier  writes:

> After looking at some of the factors that can affect this, I think it may
> be important to know that most of the connections will be almost idle (in
> terms of interacting with the DB).  The "users" are perl/dbi scripts which
> connect to the DB and spend the vast majority of the time doing things
> other than interacting with the DB.  So a connection is consumed, but it's
> not really working very hard with the DB per-se.  I am cleaning up some of
> that code by strategically connecting/disconnecting only when a DB
> interaction is required.  But for my edification, is it roughly true that 2
> connections working with the DB 100% of the time is equivalent to 20
> connections @ 10% = 200 connections @ 1 % (if you know what I mean) ?

Based on that additional info, I would definitely follow Laurenz's
suggestion. Long time since I used Perl DBI, but I'm pretty sure there
is is support for connection pools or you can use one of the PG
connection pooling solutions.

There is a fixed memory allocation per connection, so 2 connections at
100% is not the same as 20 connections @ 10%.

Using a connection pool is usually the first thing I will setup. If
additional connections are still required, then I would increase the
limit in small jumps - definitely would not go from 100 to 500.

BTW running PG on a virtual is not an issue in itself - this is very
common these days. However, I would ensure you are up-to-date wrt latest
minor release for that version and would use clients with the same
version as the master. 

-- 
Tim Cross

Re: Estimated resources for a 500 connections instance (VM)

[Adrian Klaver]

On 4/7/20 12:51 PM, David Gauthier wrote:
After looking at some of the factors that can affect this, I think it 
may be important to know that most of the connections will be almost 
idle (in terms of interacting with the DB).  The "users" are perl/dbi 
scripts which connect to the DB and spend the vast majority of the time 
doing things other than interacting with the DB.  So a connection is 
consumed, but it's not really working very hard with the DB per-se.  I 
am cleaning up some of that code by strategically 
connecting/disconnecting only when a DB interaction is required.  But 
for my edification, is it roughly true that 2 connections working with 
the DB 100% of the time is equivalent to 20 connections @ 10% = 200 
connections @ 1 % (if you know what I mean) ?


Well to get a sense of the load you could use top, with in top:

1) Hitting u key and entering postgres as user

2) Hitting c key to get full command line

That should result in something like(though formatted better):

 PID USER  PR  NIVIRTRESSHR S  %CPU  %MEM TIME+ 
COMMAND 

  978 postgres  20   0   72072   7688   6548 S 0.000 0.096   0:00.04 
/usr/lib/systemd/systemd --user 

  981 postgres  20   0  117124   2704 52 S 0.000 0.034   0:00.00 
(sd-pam) 

 1201 postgres  20   0  184292  20396  19520 S 0.000 0.254   0:01.19 
/usr/local/pgsql12/bin/postmaster -D /usr/local/pgsql12/data 

 1255 postgres  20   0   38364   4192   3332 S 0.000 0.052   0:00.00 
postgres: logger 

 1263 postgres  20   0  184408   5916   5016 S 0.000 0.074   0:00.00 
postgres: checkpointer 

 1264 postgres  20   0  184424   5520   4640 S 0.000 0.069   0:00.18 
postgres: background writer 

 1265 postgres  20   0  184292   9500   8620 S 0.000 0.118   0:00.18 
postgres: walwriter 

 1266 postgres  20   0  185116   7756   6520 S 0.000 0.096   0:01.32 
postgres: autovacuum launcher 

 1267 postgres  20   0   39488   5316   3780 S 0.000 0.066   0:02.86 
postgres: stats collector 

 1268 postgres  20   0  184844   6064   4980 S 0.000 0.075   0:00.00 
postgres: logical replication launcher 

14478 postgres  20   0  185252   9612   8184 S 0.000 0.119   0:00.00 
postgres: postgres production [local] idle 

14507 postgres  20   0  185348  11380   9848 S 0.000 0.141   0:00.00 
postgres: aklaver task_manager ::1(45202) idle



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Estimated resources for a 500 connections instance (VM)

[Rob Sargent]

On 4/7/20 2:23 PM, Sándor Daku wrote:
On Tue, 7 Apr 2020 at 21:52, David Gauthier > wrote:


After looking at some of the factors that can affect this, I think
it may be important to know that most of the connections will be
almost idle (in terms of interacting with the DB).  The "users"
are perl/dbi scripts which connect to the DB and spend the vast
majority of the time doing things other than interacting with the
DB.  So a connection is consumed, but it's not really working very
hard with the DB per-se.  I am cleaning up some of that code by
strategically connecting/disconnecting only when a DB
interaction is required.  But for my edification, is it roughly
true that 2 connections working with the DB 100% of the time is
equivalent to 20 connections @ 10% = 200 connections @ 1 % (if you
know what I mean) ?


Hi,

Every open connection consumes a bit of resources witch is not a big 
deal if you keeping open a few more connections than you strictly 
needed. However when you keeping a few hundred idle connections those 
resources add up quickly. So don't do that if it's possible.
Likewise, establishing a new connection is resource costly process. So 
don't do that either if it's possible.
Long story short, if those connections don't use many different users 
then(as others already suggested) connection pooling will be the best 
solution.


Regards,
Sándor
And from my experience pg_bouncer if very easy to include in your 
stack.  (If not tried pg_pool.)

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Andrus]

Hi!

>It is really strange why it is too slow. Can you prepare test case? Looks like 
>bug (maybe not Postgres's bug)

Testcase is below.
With  jit on it takes 3.3 sec  and with jit off 1.5 sec.

Andrus.

create temp table toode ( toode char(20), ribakood char(20), 
 nimetus char(50), markused char(50), engnimetus 
char(50) ) on commit drop;
insert into toode (toode) select generate_series(1,14400);
CREATE INDEX ON toode USING gin
(to_tsvector('english'::regconfig, nimetus::text));
CREATE UNIQUE INDEXON toode (ribakood )
WHERE ribakood IS NOT NULL AND btrim(ribakood::text) <> ''::text;
CREATE INDEX ON toode (toode);
CREATE UNIQUE INDEXON toode (upper(toode::text) );
create temp table dok ( dokumnr serial  primary key ) on commit drop;
insert into dok  select generate_series(1,14400);

create temp table rid ( dokumnr int, taitmata numeric, toode char(20)  ) on 
commit drop;
insert into rid  select generate_series(1,144);

CREATE INDEX rid_dokumnr_idxON rid (dokumnr );
-- jit on: 3.3 sec  jit off: 1.5 sec
set jit to off;
select 
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
  (select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) 

from toode 
where toode.ribakood='testmiin'::text
   or toode.nimetus ilike '%'||'testmiin'||'%' escape '!' 
   or toode.toode ilike '%'||'testmiin'||'%' escape '!' 
   or toode.markused ilike '%'||'testmiin'||'%' escape '!'

or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin') 
 or to_tsvector('english',toode.engnimetus) @@
 plainto_tsquery('testmiin')  

Announcing "common" YUM repository

[Devrim Gündüz]

The community PostgreSQL YUM repository does not just include PostgreSQL
packages, but also some extensions which depend on a specific PostgreSQL
version (e.g. PostGIS.), packages in the PostgreSQL world which can be used
against some (or all) PostgreSQL versions  (e.g. pgAdmin4), and the supportive
packages for those extensions (e.g. Proj, GeOS). 

Until yesterday, all of these packages were contained inthe same directory for
each OS / PostgreSQL combination. This increased both build time and the disk
space required.

So, I decided to merge the “common” packages (packages like pgAdmin4 and GEOS
as described above) into one single repository.

Yesterday I released new repo RPMs (42.0-9) which introduce 3 new common
repositories:

* pgdg-common : Includes all of the “common” RPMs.  Enabled by default.

* pgdg-common-testing : testing repo for the common RPMs. Disabled by default,
and  please do not use it in production.

* pgdg-common-srpm-testing: SRPMs for the packages in the  common testing repo.

I will add a pgdg-common-srpms repository later on.

What to do now?

* If you are using the default repo file (without editing it), a yum/dnf update
will automatically update the new repo configuration file. No additional action
required. This applies to the majority of our user base.

* If you have manually edited the repo file, installing the new repo RPM will
not overwrite it . Instead, it will create a new file ending with .rpmnew. You
will need to merge changes from that file to the .repo file.


FAQ:

* What happens when I don’t update the repo file?

Existing installations will continue running, and you will still receive “non-
common” updates, in particular updates to the main PostgreSQL packages.
However, new installations will fail when they need the common packages.

* Will you release a separate repo RPM for the common repo?

No. That will cause more issues.

* Did you really have to do this change?

Yes, as explained above.

* “Package foobar does not exist in the repositories. Did you remove it?”

I moved them to the common repository. Please update the repo file as described
above.

* How can I get more support?

Please send an email to pgsql-pkg-...@lists.postgresql.org  , or submit an
issue in the community issue tracker: 
https://redmine.postgresql.org/projects/pgrpms/issues

Regards,
-- 
Devrim Gündüz
Maintainer, PostgreSQL RPM repositories
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Using of --data-checksums

[Michael Paquier]

On Tue, Apr 07, 2020 at 08:10:13AM -0700, BGoebel wrote:
> initdb --data-checksums "... help to detect corruption by the I/O system"
> There is an (negligible?) impact on performance, ok. 
>  
> Is there another reason NOT to use this feature ?
> Has anyone had good or bad experience with the use of  --data-checksums?

FWIW, I have a good experience with it.  Note that some performance
impact of up to ~1% may be noticeable if you have a large number of
buffer evictions from PostgreSQL shared buffer pool, but IMO the
insurance of knowing that Postgres is not the cause of an on-disk
corruption is largely worth it (in applications where I got that
enabled we did not notice any performance impact even in very heavy
production-like workloads, and this even if we had a rather low shared
buffer setting with a much larger set of hot pages, causing the OS
cache to be filled with most of the hot data).
--
Michael


signature.asc
Description: PGP signature