Re: Postgres 10, slave not catching up with master

[Boris Sagadin]

Yes, turning wal_compression off improves things. Slave that was mentioned
unfortunately lagged too much before this setting was applied and was
turned off. However the remaining slave lags less now, although still
occasionally up to a few minutes. I think single threadedness of recovery
is a big slowdown for write heavy databases. Maybe an option to increase
wal_size beyond 16MB in v11 will help.

In the meantime we'll solve this by splitting the DB to 2 or 3 clusters or
maybe trying out some sharding solution like Citus.


Boris

On Sun, Oct 21, 2018 at 9:06 AM, Boris Sagadin  wrote:

> Hello,
>
> I have a database running on i3.8xlarge (256GB RAM, 32 CPU cores, 4x 1.9TB
> NVMe drive) AWS instance with about 5TB of disk space occupied, ext4,
> Ubuntu 16.04.
>
> Multi-tenant DB with about 4 tables, insert heavy.
>
> I started a new slave with identical HW specs, SR. DB started syncing from
> master, which took about 4 hours, then it started applying the WALs.
> However, it seems it can't catch up. Delay is still around 3 hours
> (measured with now() - pg_last_xact_replay_timestamp()), even a day
> later. It goes a few 100s up and down, but it seems to float around 3h mark.
>
> Disk IO is low at about 10%, measured with iostat, no connected clients,
> recovery process is at around 90% CPU single core usage.
>
> Tried tuning the various parameters, but with no avail. Only thing I found
> suspicious is stracing the recovery process constantly produces many errors
> such as:
>
> lseek(428, 0, SEEK_END) = 780124160
> lseek(30, 0, SEEK_END)  = 212992
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(680, 0, SEEK_END) = 493117440
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(774, 0, SEEK_END) = 583368704
>
> ...[snip]...
>
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(774, 0, SEEK_END) = 583368704
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(277, 0, SEEK_END) = 502882304
> lseek(6, 516096, SEEK_SET)  = 516096
> read(6, 
> "\227\320\5\0\1\0\0\0\0\340\7\246\26\274\0\0\315\0\0\0\0\0\0\0}\0178\5&/\260\r"...,
> 8192) = 8192
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(735, 0, SEEK_END) = 272809984
> read(9, 0x7ffe4001f557, 1)  = -1 EAGAIN (Resource temporarily
> unavailable)
> lseek(277, 0, SEEK_END) = 502882304
>
> ls -l fd/9
> lr-x-- 1 postgres postgres 64 Oct 21 06:21 fd/9 -> pipe:[46358]
>
>
> Perf top on recovery produces:
>
>  27.76%  postgres[.] pglz_decompress
>9.90%  [kernel][k] entry_SYSCALL_64_after_swapgs
>7.09%  postgres[.] hash_search_with_hash_value
>4.26%  libpthread-2.23.so  [.] llseek
>3.64%  libpthread-2.23.so  [.] __read_nocancel
>2.80%  [kernel][k] __fget_light
>2.67%  postgres[.] 0x0034d3ba
>1.85%  [kernel][k] ext4_llseek
>1.84%  postgres[.] pg_comp_crc32c_sse42
>1.44%  postgres[.] hash_any
>1.35%  postgres[.] 0x0036afad
>1.29%  postgres[.] MarkBufferDirty
>1.21%  postgres[.] XLogReadRecord
> [...]
>
> Tried changing the process limits with prlimit to unlimited, but no change.
>
> I can turn off the WAL compression but I doubt this is the main culprit.
> Any ideas appreciated.
>
> Regards,
> Boris
>
>

Re: Are indices used for creating check constraints?

[Tom Lane]

Dinko Papak  writes:
> Here are 3 interesting (to me) numbers:
> 1. creating index on expression (func(timestamp)) takes 5 seconds
> 2. creating check constraint on the same expression takes 10 seconds
> 3. adding partition table based on the same expression without check 
> expression takes 20 seconds (this has been resolved by answer from David 
> Rowley)

Hmm ...

> Is it possible to use indices to create check expressions?

No.

> Why is 2. twice as long as 1.?

Good question.  Could you provide a concrete test case?

regards, tom lane

Are indices used for creating check constraints?

[Dinko Papak]

Here are 3 interesting (to me) numbers:

1. creating index on expression (func(timestamp)) takes 5 seconds

2. creating check constraint on the same expression takes 10 seconds

3. adding partition table based on the same expression without check expression 
takes 20 seconds (this has been resolved by answer from David Rowley)


I tried various kind of indices to optimize 2. but nothing helps. When creating 
check constraint on column values directly is very fast (seems analyze helps), 
but creating check constraint on expression is very slow, even twice as long as 
creating index on the same expression.


Is it possible to use indices to create check expressions?

Why is 2. twice as long as 1.?

Why is 3. twice as long as 2.? (when there is no appropriate check constraint 
for 3.)


Thank you,

Dinko


Sent from Outlook

Re: Replication question

[Andres Freund]

Hi,

On 2018-10-22 13:53:40 +, Scot Kreienkamp wrote:
> We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long).  In 
> 9.1 I had to make the archive location (NFS in my case) available to all the 
> mirrors running PG so that they could catch up whenever they fell behind.  I 
> thought I read somewhere that in 9.6, as long as the WAL log is available on 
> disk or in the archive the replication server will provide that to the 
> replication client, and my archive NFS mount didn't have to be available to 
> all replication clients.  It doesn't seem to be operating that way though.  
> Did I completely remember that wrong or did I misunderstand something?

You can configure it that way with replication slots.  That obviously
requires enough space. It also, as the data is stored on the primary,
can't protect against loosing the entire primary (nor will an nfs served
archive if it's hosted on the primary).


> This message is intended only for the individual or entity to which it is 
> addressed.  It may contain privileged, confidential information which is 
> exempt from disclosure under applicable laws.  If you are not the intended 
> recipient, you are strictly prohibited from disseminating or distributing 
> this information (other than to the intended recipient) or copying this 
> information.  If you have received this communication in error, please notify 
> us immediately by e-mail or by telephone at the above number. Thank you.

GNGNGNG.

Greetings,

Andres Freund

RE: Replication question

[Scot Kreienkamp]

I remember thinking it was pulling from archive with the restore command if 
necessary to augment what it had on disk.  If that was the case I wanted to 
configure it.  I don’t care for the replication slots due to the possible disk 
space issue as we don’t run shifts around the clock.  So I’ll have to mount the 
archive via NFS like I had before, not a big deal.

As an alternative to NFS I was thinking about making the archives available via 
HTTPD and using wget or curl in my script instead of a copy from NFS.  That 
seems like it would work better from the remote sites.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Jeff Janes [mailto:jeff.ja...@gmail.com]
Sent: Monday, October 22, 2018 11:43 AM
To: Scot Kreienkamp 
Cc: Postgres General 
Subject: Re: Replication question


On Mon, Oct 22, 2018, 9:54 AM Scot Kreienkamp 
mailto:scot.kreienk...@la-z-boy.com>> wrote:
Hi everyone,

We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long).  In 
9.1 I had to make the archive location (NFS in my case) available to all the 
mirrors running PG so that they could catch up whenever they fell behind.  I 
thought I read somewhere that in 9.6, as long as the WAL log is available on 
disk or in the archive the replication server will provide that to the 
replication client, and my archive NFS mount didn’t have to be available to all 
replication clients.  It doesn’t seem to be operating that way though.  Did I 
completely remember that wrong or did I misunderstand something?

The master won't read from the archives for you in order to send to an replica. 
 But using replication slots, you can keep the needed log files right in 
pg_xlog/pg_wal until all replicas get what they need (assuming the disk is 
large enough).  Then you don't need an archive at all for replication purposes, 
still might for pitr purposes.

Perhaps this is what you heard about.

Cheers,

Jeff



This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: Replication question

[Jeff Janes]

On Mon, Oct 22, 2018, 9:54 AM Scot Kreienkamp 
wrote:

> Hi everyone,
>
>
>
> We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long).
> In 9.1 I had to make the archive location (NFS in my case) available to all
> the mirrors running PG so that they could catch up whenever they fell
> behind.  I thought I read somewhere that in 9.6, as long as the WAL log is
> available on disk or in the archive the replication server will provide
> that to the replication client, and my archive NFS mount didn’t have to be
> available to all replication clients.  It doesn’t seem to be operating that
> way though.  Did I completely remember that wrong or did I misunderstand
> something?
>

The master won't read from the archives for you in order to send to an
replica.  But using replication slots, you can keep the needed log files
right in pg_xlog/pg_wal until all replicas get what they need (assuming the
disk is large enough).  Then you don't need an archive at all for
replication purposes, still might for pitr purposes.

Perhaps this is what you heard about.

Cheers,

Jeff

>
>

RE: Replication question

[Scot Kreienkamp]

Dang, I thought that sounded too good to be true.  Oh well.

Thanks for setting me straight.


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Don Seiler [mailto:d...@seiler.us]
Sent: Monday, October 22, 2018 9:58 AM
To: Scot Kreienkamp 
Cc: Postgres General 
Subject: Re: Replication question

I thought I read somewhere that in 9.6, as long as the WAL log is available on 
disk or in the archive the replication server will provide that to the 
replication client, and my archive NFS mount didn’t have to be available to all 
replication clients.

Streaming replication will only read from the WAL files in the $PGDATA/pg_xlog 
directory. It will not read from archives. So, yes, you would need your NFS 
mount on the replica (or otherwise copy the archive files to the replica).

Don.

--
Don Seiler
www.seiler.us

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: Replication question

[Andreas Kretschmer]

Am 22.10.2018 um 15:53 schrieb Scot Kreienkamp:
I thought I read somewhere that in 9.6, as long as the WAL log is 
available on disk or in the archive the replication server will 
provide that to the replication client, and my archive NFS mount 
didn’t have to be available to all replication clients.  It doesn’t 
seem to be operating that way though.  Did I completely remember that 
wrong or did I misunderstand something?


no, but you can define a "restore_command" within your recovery_conf.

https://www.postgresql.org/docs/current/static/continuous-archiving.html


Regards, Andreas

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

Re: How to declare PG version for compiling extensions.

[Tom Lane]

GPT  writes:
> Both PG versions 10.5 and 11 are installed.
> I have been trying to compile extensions for PG11 by using:
> PATH=/.../11/bin:PATH make USE_...
> but unfortunately PG10 is always being used (the `make` output always
> shows PG10 and refers to `pg_config`).

> 1) Does it have to do with pg_config?

If you're using PGXS, then yes, your makefile should be querying
pg_config to find out where Postgres' headers etc are installed.
You might want to check whether "pg_config --pgxs" points to the
correct place.  Also make sure your makefile is indeed doing
something like

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

and not just hard-wiring where to look.

regards, tom lane

Re: How to declare PG version for compiling extensions.

[Adrian Klaver]

On 10/22/18 6:53 AM, GPT wrote:

Unfortunately, I had not installed the following package:

"postgresql-server-dev-11"

By the way, shouldn't a warning message appear while trying to run:

`PATH=/usr/lib/postgresql/11/bin:$PATH make USE_PGXS=1`

warning the user that some files are missing.


Except there weren't. In $PATH there where the PG 10 files that could be 
used. Now they where not the ones you wanted, but make did not 'know' that.




Tia


On 10/22/18, GPT  wrote:

Hi,

Both PG versions 10.5 and 11 are installed.

I have been trying to compile extensions for PG11 by using:

PATH=/.../11/bin:PATH make USE_...

but unfortunately PG10 is always being used (the `make` output always
shows PG10 and refers to `pg_config`).

1) Does it have to do with pg_config?
2) How can I declare which PG version to be used in real time?
3) How can I change which PG version to be used permanently?

Tia







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

Re: Replication question

[Don Seiler]

>
> I thought I read somewhere that in 9.6, as long as the WAL log is
> available on disk or in the archive the replication server will provide
> that to the replication client, and my archive NFS mount didn’t have to be
> available to all replication clients.
>

Streaming replication will only read from the WAL files in the
$PGDATA/pg_xlog directory. It will not read from archives. So, yes, you
would need your NFS mount on the replica (or otherwise copy the archive
files to the replica).

Don.

-- 
Don Seiler
www.seiler.us

Replication question

[Scot Kreienkamp]

Hi everyone,

We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long).  In 
9.1 I had to make the archive location (NFS in my case) available to all the 
mirrors running PG so that they could catch up whenever they fell behind.  I 
thought I read somewhere that in 9.6, as long as the WAL log is available on 
disk or in the archive the replication server will provide that to the 
replication client, and my archive NFS mount didn't have to be available to all 
replication clients.  It doesn't seem to be operating that way though.  Did I 
completely remember that wrong or did I misunderstand something?



Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162  | * 734-384-6403 | | * 7349151444 
| *  scot.kreienk...@la-z-boy.com
www.la-z-boy.com | 
facebook.com/lazboy
 | twitter.com/lazboy | 
youtube.com/lazboy

[cid:lzbVertical_hres.jpg]



This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: How to declare PG version for compiling extensions.

[GPT]

Unfortunately, I had not installed the following package:

"postgresql-server-dev-11"

By the way, shouldn't a warning message appear while trying to run:

`PATH=/usr/lib/postgresql/11/bin:$PATH make USE_PGXS=1`

warning the user that some files are missing.

Tia


On 10/22/18, GPT  wrote:
> Hi,
>
> Both PG versions 10.5 and 11 are installed.
>
> I have been trying to compile extensions for PG11 by using:
>
> PATH=/.../11/bin:PATH make USE_...
>
> but unfortunately PG10 is always being used (the `make` output always
> shows PG10 and refers to `pg_config`).
>
> 1) Does it have to do with pg_config?
> 2) How can I declare which PG version to be used in real time?
> 3) How can I change which PG version to be used permanently?
>
> Tia
>

How to declare PG version for compiling extensions.

[GPT]

Hi,

Both PG versions 10.5 and 11 are installed.

I have been trying to compile extensions for PG11 by using:

PATH=/.../11/bin:PATH make USE_...

but unfortunately PG10 is always being used (the `make` output always
shows PG10 and refers to `pg_config`).

1) Does it have to do with pg_config?
2) How can I declare which PG version to be used in real time?
3) How can I change which PG version to be used permanently?

Tia

Re: Optimizing Postgresql ILIKE while query

[Alban Hertroys]

> On 22 Oct 2018, at 7:56, aman gupta  wrote:
> 
> Issue:
> 
> We have the base table which contains 22M records and we created a view on 
> top of it while querying the view with ILIKE clause it took 44 seconds and 
> with LIKE Clause 20 Seconds
> 
> Query:
> 
> fm_db_custom_db=# EXPLAIN (ANALYZE, TIMING OFF)
> select 
> destination,hostname,inputfilename,inputtime,logicalservername,outputfilename,outputtime,processinglink,source,totalinputbytes,totalinputcdrs,totaloutputbytes,totaloutputcdrs
>  from mmsuper.test_20m_view  where inputfilename ilike 
> '%SDPOUTPUTCDR_4001_BLSDP09_ADM_4997_18-10-15-02549.ASN%';

Perhaps, when you have a question about timing, you shouldn't turn off the 
timing in the query plan? Now we can't see where the time is spent.

> 

That's all sequential scans that each remove a significant amount of rows. That 
probably costs a significant amount of time to do.

It looks like you don't have any indices on the underlying table(s) at all. I'd 
start there and then look at the ILIKE problem again. By that time, Pavel's 
suggestion for a trigram index on that text field is probably spot-on.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Optimizing Postgresql ILIKE while query

[Pavel Stehule]

Hi

po 22. 10. 2018 v 7:57 odesílatel aman gupta  napsal:

> Hi Team,
>
> Greetings for the day!!
>
> Platform:
>
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
>
>
> Issue:
>
>
> We have the base table which contains 22M records and we created a view on
> top of it while querying the view with ILIKE clause it took 44 seconds and
> with LIKE Clause 20 Seconds
>
> Query:
>
> fm_db_custom_db=# EXPLAIN (ANALYZE, TIMING OFF)
> select
> destination,hostname,inputfilename,inputtime,logicalservername,outputfilename,outputtime,processinglink,source,totalinputbytes,totalinputcdrs,totaloutputbytes,totaloutputcdrs
> from mmsuper.test_20m_view  where inputfilename ilike
> '%SDPOUTPUTCDR_4001_BLSDP09_ADM_4997_18-10-15-02549.ASN%';
>
> Also attaching the comparison for both ILIKE and LIKE test performed.
>
> Expectation:
>
> How can we optimize our ILIKE query, since it is hardcoded in the
> application and we can't use any other keyword than ILIKE .
>

look on trigram index

https://www.postgresql.org/docs/11/static/pgtrgm.html

Regards

Pavel

>
> BR//
> Aman Gupta
> +918447611183
> amangp...@gmail.com
>
>