Re: How to declare PG version for compiling extensions.

[Andrew Gierth]

> "GPT" == GPT   writes:

 GPT> Unfortunately, I had not installed the following package:
 GPT> "postgresql-server-dev-11"

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

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

 GPT> warning the user that some files are missing.

It's more reliable to do (if the makefile is correctly written):

make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/11/bin/pg_config

By specifying the pg_config binary explicitly rather than relying on the
PATH, you avoid the chance of picking up an incorrect copy by mistake.
This is why the standard form for pgxs makefiles has an assignment for
PG_CONFIG before the PGXS assignment line; the command-line option
overrides it.

-- 
Andrew (irc:RhodiumToad)

Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi,

I am looking for a way to select all timestamps that are "today" in an
index friendly way. This select should not depend on the concrete value
of "today".

Given a table

create temporary table t (
  id SERIAL primary key,
  ts timestamp not null default now()
);

with some data

insert into t (ts)
select ts
from generate_series(
  '2018-01-01T00:00:01'::timestamp,
  '2018-12-31T23:59:59'::timestamp,
  '2 minutes')
as ts;

and an index

create index on t (ts, id);

I can of course make an explicit select for `ts` values that are
"today":

select ts, id
  from t
 where ts >= '2018-10-23T00:00:00'::timestamp
   and ts <= '2018-10-23T23:59:59'::timestamp;

This uses an Bitmap Index Scan on `t_ts_id_idx`. Good.

But the where conditions depends on concrete values of "today" which
will not return the intended result if I execute it tomorrow. I will
have to change the where condition. Not good.

I am looking for a way to make the where condition independed of the
date of execution. I can create a function

create function is_today(timestamp) returns boolean as $$
select to_char(now(), '-MM-DD') = to_char($1, '-MM-DD');
$$ language sql;

that converts the timestamps to text. But using this function

select * from t where is_today(ts);

will not benefit from the existing index. A Seq Scan on `t` will be
used. Not good.

Is there a way to have both: be independed of the concrete value of
"today" *and* use the index on the timestamp column?

Lutz

Re: Select "todays" timestamps in an index friendly way

[Francisco Olarte]

Hi Lutz.

On Tue, Oct 23, 2018 at 11:38 AM, Lutz Horn  wrote:
> I am looking for a way to select all timestamps that are "today" in an
> index friendly way. This select should not depend on the concrete value
> of "today".

> Given a table
> create temporary table t (
>   id SERIAL primary key,
>   ts timestamp not null default now()
> );
>
> with some data
>
> insert into t (ts)
> select ts
> from generate_series(
>   '2018-01-01T00:00:01'::timestamp,
>   '2018-12-31T23:59:59'::timestamp,
>   '2 minutes')
> as ts;
>
> and an index
>
> create index on t (ts, id);
>
> I can of course make an explicit select for `ts` values that are
> "today":
>
> select ts, id
>   from t
>  where ts >= '2018-10-23T00:00:00'::timestamp
>and ts <= '2018-10-23T23:59:59'::timestamp;

1st remark. Do NOT use closed interval for timestamps. Always use
half-open or you'll run into problems ( i.e., you are going to miss
2018-10-23T23:59:59.25 in that query ). For real like things ( which
timestamps are, they identify a point on the time line ) use half-open
( you can cover a line with non-overlapping half-open segments, not
with closed ones ).

I.e., your query will better be stated as

  where ts >= '2018-10-23T00:00:00'::timestamp
and ts < '2018-10-24T00:00:00'::timestamp;

Which, as a nice bonus, can rely on the time part defaulting to 0:

  where ts >= '2018-10-23'::timestamp
and ts < '2018-10-24'::timestamp;

and then be expressed in other ways, like

  where ts >= '2018-10-23'::timestamp
and ts < ('2018-10-23'::timestamp + '1 day'::interval)

> This uses an Bitmap Index Scan on `t_ts_id_idx`. Good.
>
> But the where conditions depends on concrete values of "today" which
> will not return the intended result if I execute it tomorrow. I will
> have to change the where condition. Not good.
>
> I am looking for a way to make the where condition independed of the
> date of execution. I can create a function
>
> create function is_today(timestamp) returns boolean as $$
> select to_char(now(), '-MM-DD') = to_char($1, '-MM-DD');
> $$ language sql;

This is not a good way to deal with timestamp values, they are just
numbers, play with them as such. Try using something like

date_trunc('day',now()) = date_trunc('day',$1)

which states your purposes more clearly.

> that converts the timestamps to text. But using this function
>
> select * from t where is_today(ts);
>
> will not benefit from the existing index. A Seq Scan on `t` will be
> used. Not good.
>
> Is there a way to have both: be independed of the concrete value of
> "today" *and* use the index on the timestamp column?

Well, if your definition of today is 'same value as now() when
truncated to days' we can use part of what I've written above,
1st calculate today and tomorrow with same timestamp arithmetic and date_trunc:

 select now(), date_trunc('day',now()) as today,
date_trunc('day',now()+'1 day') as tomorrow;
  now  | today  |tomorrow
---++
 2018-10-23 11:58:01.699407+02 | 2018-10-23 00:00:00+02 | 2018-10-24 00:00:00+02
(1 row)

Then plug that result in your query ( using the half-open technique )
described above:

where ts >=  date_trunc('day',now())
and ts < date_trunc('day',now()+'1 day') as tomorrow;

IIRC this should use the index, you can RTFM in case you prefer using
current_timestamp and her cousins, but bear in mind if you use
something like current_date you should convert it to timestamp, not
convert ts to date, to get easy index usage.


Francisco Olarte.

Re: Select "todays" timestamps in an index friendly way

[Thomas Kellerer]

Lutz Horn schrieb am 23.10.2018 um 11:38:
> I can of course make an explicit select for `ts` values that are
> "today":
> 
> select ts, id
>   from t
>  where ts >= '2018-10-23T00:00:00'::timestamp
>and ts <= '2018-10-23T23:59:59'::timestamp;
> 
> This uses an Bitmap Index Scan on `t_ts_id_idx`. Good.
> 
> But the where conditions depends on concrete values of "today" which
> will not return the intended result if I execute it tomorrow. I will
> have to change the where condition. Not good.
> 
> I am looking for a way to make the where condition independed of the
> date of execution. I can create a function
> 
> create function is_today(timestamp) returns boolean as $$
> select to_char(now(), '-MM-DD') = to_char($1, '-MM-DD');
> $$ language sql;
> 
> that converts the timestamps to text. But using this function
> 
> select * from t where is_today(ts);
> 
> will not benefit from the existing index. A Seq Scan on `t` will be
> used. Not good.
> 
> Is there a way to have both: be independed of the concrete value of
> "today" *and* use the index on the timestamp column?

I typically use:

  where ts >= date '2018-10-23'
and ts < date '2018-10-23' + 1

Re: Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi Francisco,

On Tue, Oct 23, 2018 at 12:05:17PM +0200, Francisco Olarte wrote:
> 1st remark. Do NOT use closed interval for timestamps. Always use
> half-open or you'll run into problems

Good point, thanks.

> where ts >=  date_trunc('day',now())
> and ts < date_trunc('day',now()+'1 day') as tomorrow;
> 
> IIRC this should use the index

And it does! Thanks!

Lutz

Re: Select "todays" timestamps in an index friendly way

[Lutz Horn]

Hi Thomas,

On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote:
> I typically use:
> 
>   where ts >= date '2018-10-23'
> and ts < date '2018-10-23' + 1

But here the date is an explicit value. Francisco reworded my question:

> if your definition of today is 'same value as now() when truncated to
> days'

That's what I am (was, thanks to Francisco) looking for.

Lutz

Re: Select "todays" timestamps in an index friendly way

[Thomas Kellerer]

Lutz Horn schrieb am 23.10.2018 um 12:19:
> Hi Thomas,
> 
> On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote:
>> I typically use:
>>
>>   where ts >= date '2018-10-23'
>> and ts < date '2018-10-23' + 1
> 
> But here the date is an explicit value. Francisco reworded my question:
> 
>> if your definition of today is 'same value as now() when truncated to
>> days'
> 
> That's what I am (was, thanks to Francisco) looking for.

Then use current_date:

   where ts >= current_date
 and ts < current_date + 1

Re: Replication question

[Jeff Janes]

On Mon, Oct 22, 2018 at 1:20 PM Scot Kreienkamp <
scot.kreienk...@la-z-boy.com> wrote:

> 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.
>

If the archive partition fills up, then your archive command will start
failing, which means your pg_xlog will also start filling up.  If you can
dedicate the same amount of space to the new pg_xlog as you currently have
dedicated to pg_wal + archive partition, then there should be no increased
risk of running out of disk space.  Except that you can have
archive_command compress the WAL files upon archival, which can save a lot
of space.  Of course there are a variety of reasons that that might not
work, like you use small fast disk for pg_xlog and big slow ones for
archive, or pg_xlog is your problem while archive is some other guy's
problem.  But it is something to consider.


> 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.
>
>
>
Yeah, or rsync or scp.

Cheers,

Jeff

Re: Select "todays" timestamps in an index friendly way

[Hellmuth Vargas]

Hi

El mar., 23 de oct. de 2018 a la(s) 05:41, Thomas Kellerer (
spam_ea...@gmx.net) escribió:

> Lutz Horn schrieb am 23.10.2018 um 12:19:
> > Hi Thomas,
> >
> > On Tue, Oct 23, 2018 at 12:11:55PM +0200, Thomas Kellerer wrote:
> >> I typically use:
> >>
> >>   where ts >= date '2018-10-23'
> >> and ts < date '2018-10-23' + 1
> >
> > But here the date is an explicit value. Francisco reworded my question:
> >
> >> if your definition of today is 'same value as now() when truncated to
> >> days'
> >
> > That's what I am (was, thanks to Francisco) looking for.
>
> Then use current_date:
>
>where ts >= current_date
>  and ts < current_date + 1
>
>
>
this is equally valid?

where ts >= current_date


cordialmente:

Hellmuth Vargas

Re: Postgres 10, slave not catching up with master

[Hellmuth Vargas]

Hi

can share recovery.conf file settings??

El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin (
bo...@infosplet.com) escribió:

> 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
>>
>>
>

-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate

Enabling bdr in multiple databases on the same postgresql instance/cluster

[Daniel Fink (PDF)]

Hi all,



I already have a running cluster of BDR nodes.

Now we want to add an additional database on the same hosts.



Can I just create a new database and then create/join nodes as in this
description:

http://bdr-project.org/docs/1.0.3/quickstart-enabling.html



Best Regards,




*DANIEL FINK*

*Senior Software Engineer*

*tel* (+49) 89.767062.20
*fax*(+49) 89.767062.11
email daniel.f...@pdf.com

*PDF Solutions GmbH*
* (**a PDF Solutions Company)*
Managing Director: Kimon Michaels
Schwanthalerstr. 10
D-80336 München, Germany

München HRB 87307
DE 128214899

*www.pdf.com *

-- 
This message may contain confidential and privileged information. If it has 
been sent to you in error, please reply to advise the sender of the error 
and then immediately permanently delete it and all attachments to it from 
your systems. If you are not the intended recipient, do not read, copy, 
disclose or otherwise use this message or any attachments to it. The sender 
disclaims any liability for such unauthorized use.  PLEASE NOTE that all 
incoming e-mails sent to PDF e-mail accounts will be archived and may be 
scanned by us and/or by external service providers to detect and prevent 
threats to our systems, investigate illegal or inappropriate behavior, 
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any 
concerns about this process, please contact us at legal.departm...@pdf.com 
.

Re: Select "todays" timestamps in an index friendly way

[Francisco Olarte]

On Tue, Oct 23, 2018 at 3:05 PM, Hellmuth Vargas  wrote:
...
>> Then use current_date:
>>
>>where ts >= current_date
>>  and ts < current_date + 1
>
> this is equally valid?
>
> where ts >= current_date

It'is not as the problem was stated. Although ts defaulted to now(),
and it is probably defaulted, nothing prohibits him from inserting
timestamps in the future.

Also, I'll point the table used in the sample ( bigserial+timestamp)
does not seem like a real one and the  "timestamps in today" pattern
is commonly used in calendaring applications, which usually insert
appointments in the future and recover this way to print "todays
schedule".

Francisco Olarte.

Re: Optimizing Postgresql ILIKE while query

[Scottix]

Also leading wildcards can inhibit the use of indexes. Best to try to avoid
LIKE queries similar to '%TERM'

On Mon, Oct 22, 2018 at 12:23 AM Alban Hertroys  wrote:

>
>
> > 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: Select "todays" timestamps in an index friendly way

[Lutz Horn]

On Tue, Oct 23, 2018 at 03:50:14PM +0200, Francisco Olarte wrote:
> It'is not as the problem was stated. Although ts defaulted to now(),
> and it is probably defaulted, nothing prohibits him from inserting
> timestamps in the future.

Yes, this table is only used as an example for the technical question.
In my real use case there are columns like "due_date" which usually
contain future dates inserted by application code.

> the  "timestamps in today" pattern is commonly used in calendaring
> applications, which usually insert appointments in the future and
> recover this way to print "todays schedule".

Exactly. The application must be able to execute queries like "give me
all my tasks due today" without having to use a concrete value for
"today".

Lutz

Re: Enabling bdr in multiple databases on the same postgresql instance/cluster

[Andreas Kretschmer]

On 23 October 2018 14:24:28 WEST, "Daniel Fink (PDF)"  
wrote:
>Hi all,
>
>
>
>I already have a running cluster of BDR nodes.
>
>Now we want to add an additional database on the same hosts.
>
>
>
>Can I just create a new database and then create/join nodes as in this
>description:
>
>http://bdr-project.org/docs/1.0.3/quickstart-enabling.html
>
>
>
>Best Regards,
>
>
>
>
>*DANIEL FINK*
>
>*Senior Software Engineer*
>
>*tel* (+49) 89.767062.20
>*fax*(+49) 89.767062.11
>email daniel.f...@pdf.com
>
>*PDF Solutions GmbH*
>* (**a PDF Solutions Company)*
>Managing Director: Kimon Michaels
>Schwanthalerstr. 10
>D-80336 München, Germany
>
>München HRB 87307
>DE 128214899
>
>*www.pdf.com *

Ja, create extension und so weiter, wie in der doku. Grüße aus Lissabon ;-)
-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Postgres 10, slave not catching up with master

[Boris Sagadin]

Nothing special, just:

standby_mode = 'on'
primary_conninfo = 'host=...  user=repmgr application_name=nodex'
recovery_target_timeline = 'latest'


Boris

On Tue, Oct 23, 2018 at 3:10 PM, Hellmuth Vargas  wrote:

> Hi
>
> can share recovery.conf file settings??
>
> El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin (
> bo...@infosplet.com) escribió:
>
>> 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
>>>
>>>
>>
>
> --
> Cordialmente,
>
> Ing. Hellmuth I. Vargas S.
> Esp. Telemática y Negocios por Internet
> Oracle Database 10g Administrator Certified Associate
> EnterpriseDB Certified PostgreSQL 9.3 Associate
>
>

Re: Select "todays" timestamps in an index friendly way

[Francisco Olarte]

On Tue, Oct 23, 2018 at 3:57 PM, Lutz Horn  wrote:
> On Tue, Oct 23, 2018 at 03:50:14PM +0200, Francisco Olarte wrote:
>> It'is not as the problem was stated. Although ts defaulted to now(),
>> and it is probably defaulted, nothing prohibits him from inserting
>> timestamps in the future.
> Yes, this table is only used as an example for the technical question.
> In my real use case there are columns like "due_date" which usually
> contain future dates inserted by application code.

If your real table uses dates instead of timestamps modify the code
accordingly, they are not the same ( dates are countable, instants in
time are not (they are in the computer, with finite precision, but you
see the difference )) Although I supose they really are timestamps, or
you would have just used "date_column=current_date".


>> the  "timestamps in today" pattern is commonly used in calendaring
>> applications, which usually insert appointments in the future and
>> recover this way to print "todays schedule".
> Exactly. The application must be able to execute queries like "give me
> all my tasks due today" without having to use a concrete value for
> "today".

Been there, done that. With an IBM 84 ( instructional use. It was, not
surprissingly, easier but slower,  ).

Happy hacking.
   Francisco Olarte.

Re: Select "todays" timestamps in an index friendly way

[Mike Rylander]

On Tue, Oct 23, 2018 at 5:38 AM Lutz Horn  wrote:
>
> Hi,
>
> I am looking for a way to select all timestamps that are "today" in an
> index friendly way. This select should not depend on the concrete value
> of "today".
>

Per TFM, https://www.postgresql.org/docs/10/static/datatype-datetime.html
on table 8.13, you can use special input values:

SELECT * FROM t WHERE ts >= 'today'::timestamp AND ts < 'tomorrow'::timestamp;

HTH,

--
Mike Rylander
 | Executive Director
 | Equinox Open Library Initiative
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  mi...@equinoxinitiative.org
 | web:  http://equinoxinitiative.org

Re: Postgres 10, slave not catching up with master

[Hellmuth Vargas]

Hi

which result you get from the following query:

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

source:

https://severalnines.com/blog/postgresql-streaming-replication-deep-dive

El mar., 23 de oct. de 2018 a la(s) 11:28, Boris Sagadin (
bo...@infosplet.com) escribió:

> Nothing special, just:
>
> standby_mode = 'on'
> primary_conninfo = 'host=...  user=repmgr application_name=nodex'
> recovery_target_timeline = 'latest'
>
>
> Boris
>
> On Tue, Oct 23, 2018 at 3:10 PM, Hellmuth Vargas  wrote:
>
>> Hi
>>
>> can share recovery.conf file settings??
>>
>> El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin (
>> bo...@infosplet.com) escribió:
>>
>>> 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


>>>
>>
>> --
>> Cordialmente,
>>
>> Ing. Hellmuth I. Vargas S.
>> Esp. Telemática y Negocios por Internet
>> Oracle Database 10g Administrator Certified Associate
>> EnterpriseDB Certified PostgreSQL 9.3 Associate
>>
>>
>

-- 
Cordialmente,

Re: Postgres 10, slave not catching up with master

[Hellmuth Vargas]

Hi

Both servers are configured with the same date, time and time configuration?

El mar., 23 de oct. de 2018 a la(s) 13:16, Hellmuth Vargas (hiv...@gmail.com)
escribió:

> Hi
>
> which result you get from the following query:
>
> SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
> THEN 0
> ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
> END AS log_delay;
>
> source:
>
> https://severalnines.com/blog/postgresql-streaming-replication-deep-dive
>
> El mar., 23 de oct. de 2018 a la(s) 11:28, Boris Sagadin (
> bo...@infosplet.com) escribió:
>
>> Nothing special, just:
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=...  user=repmgr application_name=nodex'
>> recovery_target_timeline = 'latest'
>>
>>
>> Boris
>>
>> On Tue, Oct 23, 2018 at 3:10 PM, Hellmuth Vargas 
>> wrote:
>>
>>> Hi
>>>
>>> can share recovery.conf file settings??
>>>
>>> El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin (
>>> bo...@infosplet.com) escribió:
>>>
 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
>>

Oracle vs PG

[Ravi Krishna]

Well it is Aurora.

https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html

Re: Oracle vs PG

[Adrian Klaver]

On 10/23/18 12:58 PM, Ravi Krishna wrote:

Well it is Aurora.

https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html



Since the article was almost content-free I not would use it on either 
side of the argument. The only thing I pulled from it was Amazon changed 
databases and hit the learning curve. That will happen in either direction.


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

Re: Oracle vs PG

[Ravi Krishna]

> 
> Since the article was almost content-free I not would use it on either side 
> of the argument. The only thing I pulled from it was Amazon changed databases 
> and hit the learning curve. That will happen in either direction.

I agree but this is the key:

"Savepoints are an important database tool for tracking and recovering 
individual transactions. On Prime Day, an excessive number of savepoints was 
created, and Amazon's Aurora software wasn't able to handle the pressure, 
slowing down the overall database performance, the report said."

Re: Oracle vs PG

[Fabrízio de Royes Mello]

Em ter, 23 de out de 2018 às 17:46, Adrian Klaver 
escreveu:
>
> On 10/23/18 12:58 PM, Ravi Krishna wrote:
> > Well it is Aurora.
> >
> >
https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
> >
>
> Since the article was almost content-free I not would use it on either
> side of the argument. The only thing I pulled from it was Amazon changed
> databases and hit the learning curve. That will happen in either
direction.
>

+1... I completely agree

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Oracle vs PG

[Fabrízio de Royes Mello]

Em ter, 23 de out de 2018 às 17:48, Ravi Krishna 
escreveu:
>
> I agree but this is the key:
>
> "Savepoints are an important database tool for tracking and recovering
individual transactions. On Prime Day, an excessive number of savepoints
was created, and Amazon's Aurora software wasn't able to handle the
pressure, slowing down the overall database performance, the report said."
>

If it's true (I don't know Oracle enough to have a clear opinion) then they
should think better their database transactions design/architecture and not
just move...

And to improve our Savepoint infrastructure we need a more detailed
information.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Oracle vs PG

[Adrian Klaver]

On 10/23/18 1:47 PM, Ravi Krishna wrote:


Since the article was almost content-free I not would use it on either 
side of the argument. The only thing I pulled from it was Amazon 
changed databases and hit the learning curve. That will happen in 
either direction.


I agree but this is the key:

"Savepoints are an important database tool for tracking and recovering 
individual transactions. On Prime Day, an excessive number of savepoints 
was created, and Amazon's Aurora software wasn't able to handle the 
pressure, slowing down the overall database performance, the report said."





Again, pretty much content-free. For all you know some application was 
creating savepoints, needlessly:


https://www.postgresql.org/docs/10/static/sql-savepoint.html

and not cleaning up after itself.

The content is here:

"Following the Prime Day outage, Amazon engineers filled out a 25-page 
report, which Amazon calls a correction of error. It's a standard 
process that Amazon uses to try to understand why a major incident took 
place and how to keep it from happening in the future."


Not sure if that is publicly available or not, though my hunch is no.


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

Re: Oracle vs PG

[Jerry Sievers]

Adrian Klaver  writes:

> On 10/23/18 12:58 PM, Ravi Krishna wrote:
>
>> Well it is Aurora.
>>
>> https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
>>
>
> Since the article was almost content-free I not would use it on either
> side of the argument. The only thing I pulled from it was Amazon
> changed databases and hit the learning curve. That will happen in
> either direction.

Yeah and kudos to them for taking a chance.

I assume what revenue they lost during the incident will be made back
thousands of times over when/if they can avoid paying what it likely an
absurd cost to licence the $big-commercial-db.

FWIW

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Sv: Re: Oracle vs PG

[Andreas Joseph Krogh]

På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver <
adrian.kla...@aklaver.com >:
On 10/23/18 12:58 PM, Ravi Krishna wrote:
 > Well it is Aurora.
 >
 > 
https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
 >

 Since the article was almost content-free I not would use it on either
 side of the argument. The only thing I pulled from it was Amazon changed
 databases and hit the learning curve. That will happen in either direction.
 
Is it so hard to accept commercial databases have advantages?
I find that not one bit surprising.
 
I've used PG since 90's and it's no secret the "big guys" beat PG on certain 
workloads.
 
-- Andreas Joseph Krogh
​

Re: Sv: Re: Oracle vs PG

[Adrian Klaver]

On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote:
På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver 
mailto:adrian.kla...@aklaver.com>>:


On 10/23/18 12:58 PM, Ravi Krishna wrote:
 > Well it is Aurora.
 >
 >

https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
 >

Since the article was almost content-free I not would use it on either
side of the argument. The only thing I pulled from it was Amazon changed
databases and hit the learning curve. That will happen in either
direction.

Is it so hard to accept commercial databases have advantages?


That is entirely possible. My point is that the article does not contain 
enough information to make that determination. As with many media 
articles it was written to support the headline, not to actually shed 
light on the issue.




I find that not one bit surprising.
I've used PG since 90's and it's no secret the "big guys" beat PG on 
certain workloads.

--
Andreas Joseph Krogh
​



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

Sv: Re: Sv: Re: Oracle vs PG

[Andreas Joseph Krogh]

På tirsdag 23. oktober 2018 kl. 23:36:29, skrev Adrian Klaver <
adrian.kla...@aklaver.com >:
On 10/23/18 2:34 PM, Andreas Joseph Krogh wrote:
 > På tirsdag 23. oktober 2018 kl. 22:45:36, skrev Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>>:
 >
 >     On 10/23/18 12:58 PM, Ravi Krishna wrote:
 >      > Well it is Aurora.
 >      >
 >      >
 >    
 
https://www.cnbc.com/2018/10/23/amazon-move-off-oracle-caused-prime-day-outage-in-warehouse.html
 >      >
 >
 >     Since the article was almost content-free I not would use it on either
 >     side of the argument. The only thing I pulled from it was Amazon changed
 >     databases and hit the learning curve. That will happen in either
 >     direction.
 >
 > Is it so hard to accept commercial databases have advantages?

 That is entirely possible. My point is that the article does not contain
 enough information to make that determination. As with many media
 articles it was written to support the headline, not to actually shed
 light on the issue.
 
I think it provides enough.
It's of course entirely up to the reader to ignore, question, or not believe, 
the results of the published report(s).
 
--
 Andreas Joseph Krogh

Re: Select "todays" timestamps in an index friendly way

[David Rowley]

On 24 October 2018 at 07:14, Mike Rylander  wrote:
>
> On Tue, Oct 23, 2018 at 5:38 AM Lutz Horn  wrote:
> > I am looking for a way to select all timestamps that are "today" in an
> > index friendly way. This select should not depend on the concrete value
> > of "today".
>
> Per TFM, https://www.postgresql.org/docs/10/static/datatype-datetime.html
> on table 8.13, you can use special input values:
>
> SELECT * FROM t WHERE ts >= 'today'::timestamp AND ts < 'tomorrow'::timestamp;

Of course, you'd need to be careful never to use that in a view or
even a PREPAREd statement.  Those abbreviations are evaluated when the
query is parsed. In those cases, you'd just get the results for
whatever day you did CREATE VIEW or PREPARE.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Oracle vs PG

[Ravi Krishna]

> 
> Is it so hard to accept commercial databases have advantages?
> I find that not one bit surprising.
>  
> I've used PG since 90's and it's no secret the "big guys" beat PG on certain 
> workloads.
> 

In my previous workplace where they tested EDB to replace PG, they found all 
PL/SQL based codes were running 2x to 3x slower than Oracle.

Re: Oracle vs PG

[Ravi Krishna]

e to handle the pressure, slowing down the overall database performance, the 
report said."
> 
> Again, pretty much content-free. For all you know some application was 
> creating savepoints, needlessly:
> 
> https://www.postgresql.org/docs/10/static/sql-savepoint.html
> 
> and not cleaning up after itself.
> 
> The content is here:
> 
> "Following the Prime Day outage, Amazon engineers filled out a 25-page 
> report, which Amazon calls a correction of error. It's a standard process 
> that Amazon uses to try to understand why a major incident took place and how 
> to keep it from happening in the future."
> 
> Not sure if that is publicly available or not, though my hunch is no.

I think PG gurus in this list can expand on the clue as to what could have gone 
wrong with savepoints in PG under heavy load.

Re: Oracle vs PG

[Ravi Krishna]

> Again, pretty much content-free. For all you know some application was 
> creating savepoints, needlessly:

> https://www.postgresql.org/docs/10/static/sql-savepoint.html

I have hardly used savepoints in any application, but if I understand it 
correctly, isn't it something which is typically used
in a persistent connection.  I wonder how it is applicable in a web based 
stateless application like Amazon.com, unless
even web based application have database level state.

Re: Oracle vs PG

[James Keener]

>I have hardly used savepoints in any application, but if I understand
>it correctly, isn't it something which is typically used
>in a persistent connection.  I wonder how it is applicable in a web
>based stateless application like Amazon.com, unless
>even web based application have database level state.

Doesn't need to be a long running connection, you can trap exceptions, roll 
back, and do something else all in the SQL code or a function from a single 
call.


-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Oracle vs PG

[Michael Nolan]

On Tue, Oct 23, 2018 at 6:36 PM Ravi Krishna  wrote:

>
> I have hardly used savepoints in any application, but if I understand it
> correctly, isn't it something which is typically used
> in a persistent connection.  I wonder how it is applicable in a web based
> stateless application like Amazon.com, unless
> even web based application have database level state.
>

Amazon's web store may be a (mostly) stateless application, that doesn't
mean their back end applications are.
--
Mike Nolan

Re: Oracle vs PG

[Ravi Krishna]

> 
> Amazon's web store may be a (mostly) stateless application, that doesn't mean 
> their back end applications are.
> 

Oh yes.  There is nothing in that article which suggests that the root cause of 
the outage was in the web based apps.
As you indicated, their back end may be the source of the issue and web store 
happens to be the victim.

thanks.

Re: Oracle vs PG

[Tim Cross]

Ravi Krishna  writes:

>> Again, pretty much content-free. For all you know some application was 
>> creating savepoints, needlessly:
>
>> https://www.postgresql.org/docs/10/static/sql-savepoint.html
>
> I have hardly used savepoints in any application, but if I understand it 
> correctly, isn't it something which is typically used
> in a persistent connection.  I wonder how it is applicable in a web based 
> stateless application like Amazon.com, unless
> even web based application have database level state.

No, savepoints and persistent connections are not necessarily related.

Savepoints are really just a way of managing rollback segments. For
example, if you were doing a large number of inserts/updates, things can
become slow if the rollback segment grows really large. One way around
this is to set savepoints, which will allow you to commit more
frequently and prevent the rollback size from growing too large (there
are other benefits as well, such as allowing other transactions to see
partial changes sooner rather than not seeing any change until after a
long running insert/update has completed etc).

I think that article is really just about headline click bait and lacks
any real details. I'm not even convinced that comparison of Oracle and
PG really makes sense anyway - both databases have their pros and cons.

IMO Oracle is a very good database (though most of the 'add ons' are
less beneficial). However, it is extremely expensive, both to license
and to administer. For certain applications, it would be my first choice
(assuming available budget). However, I prefer PG for the majority of
what I need, partially due to the cost, but mainly because it is rock
solid and much, much easier to administer and sufficient for what I
need. As usual, it is more about requirements than brand and choosing
the right tool for the right job.

Tim

-- 
Tim Cross

Should pg 11 use a lot more memory building an spgist index?

[Bruno Wolff III]

While reloading a database cluster to move from 10.5 to 11, I'm getting 
out of memory crashes that I did see when doing reloads on pg 10.

The statement flagged in the log is this:
2018-10-23 16:44:34.815 CDT [126839] STATEMENT:  ALTER TABLE ONLY 
public.iplocation
ADD CONSTRAINT overlap EXCLUDE USING spgist (network WITH &&);

iplocation has 4398722 rows.

This is geolite data where the networks are a partial covering of the total 
address spaces with no overlaps.


Should I expect to have to make config changes to make this work?

Re: Oracle vs PG

[Laurenz Albe]

Ravi Krishna wrote:
> I have hardly used savepoints in any application, but if I understand it 
> correctly, isn't it something which is typically used
> in a persistent connection.  I wonder how it is applicable in a web based 
> stateless application like Amazon.com, unless
> even web based application have database level state.

I have seen people use savepoints in PostgreSQL to emulate Oracle's
"statement rollback" behavior: If a statement fails, only the statement
is undone, but the transaction continues.

If you insert a savepoint before *every* statement in a transaction,
you can get a similar behavior in PostgreSQL, but the performance will
suck.

Perhaps that is what happened in this case.

Of course the correct solution is to redesign and use savepoints only
where you *expect* an error, or at least batch a number of statements
with each savepoint.

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

Re: Postgres 10, slave not catching up with master

[Boris Sagadin]

Yes, times are all identical, set to UTC, ntpd is used.

 log_delay
---
 15.788175

This is delay at this moment, but we graph replication delay and it's
fluctuating between 0 and 30s. Before I turned off wal compression, lag was
much bigger (0 to up to 8 minutes). We have lots of tables (40k) and many
upserts.


Boris

On Tue, Oct 23, 2018 at 8:24 PM, Hellmuth Vargas  wrote:

> Hi
>
> Both servers are configured with the same date, time and time
> configuration?
>
> El mar., 23 de oct. de 2018 a la(s) 13:16, Hellmuth Vargas (
> hiv...@gmail.com) escribió:
>
>> Hi
>>
>> which result you get from the following query:
>>
>> SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
>> THEN 0
>> ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
>> END AS log_delay;
>>
>> source:
>>
>> https://severalnines.com/blog/postgresql-streaming-replication-deep-dive
>>
>> El mar., 23 de oct. de 2018 a la(s) 11:28, Boris Sagadin (
>> bo...@infosplet.com) escribió:
>>
>>> Nothing special, just:
>>>
>>> standby_mode = 'on'
>>> primary_conninfo = 'host=...  user=repmgr application_name=nodex'
>>> recovery_target_timeline = 'latest'
>>>
>>>
>>> Boris
>>>
>>> On Tue, Oct 23, 2018 at 3:10 PM, Hellmuth Vargas 
>>> wrote:
>>>
 Hi

 can share recovery.conf file settings??

 El mar., 23 de oct. de 2018 a la(s) 00:28, Boris Sagadin (
 bo...@infosplet.com) escribió:

> 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   

Re: Oracle vs PG

[Michael Paquier]

On Wed, Oct 24, 2018 at 07:31:57AM +0200, Laurenz Albe wrote:
> I have seen people use savepoints in PostgreSQL to emulate Oracle's
> "statement rollback" behavior: If a statement fails, only the statement
> is undone, but the transaction continues.
> 
> If you insert a savepoint before *every* statement in a transaction,
> you can get a similar behavior in PostgreSQL, but the performance will
> suck.

The Postgres ODBC driver actually does that, and I have seen
applications actually ready to pay the cost of extra round trips to the
server to be able to get this property, even if that costs performance.
You can issue a query through the driver and rollback at will this way
to the previous state of the transaction.
--
Michael


signature.asc
Description: PGP signature