Re: [PERFORM] Stalls on PGSemaphoreLock

[Takashi Horikawa]

On Mar 25, 2014, at 8:46 AM, Matthew Spilich wrote:
> Has any on the forum seen something similar?  

I think I reported similar phenomenon in my SIGMOD 2013 paper (Latch-free
data structures for DBMS: design, implementation, and evaluation,
). 

> - 47245 -
> 0x0037392eb197 in semop () from /lib64/libc.so.6
> #0  0x0037392eb197 in semop () from /lib64/libc.so.6
> #1  0x005e0c87 in PGSemaphoreLock ()
> #2  0x0061e3af in LWLockAcquire ()
> #3  0x0060aa0f in ReadBuffer_common ()
> #4  0x0060b2e4 in ReadBufferExtended ()
...

> - 47257 -
> 0x0037392eb197 in semop () from /lib64/libc.so.6
> #0  0x0037392eb197 in semop () from /lib64/libc.so.6
> #1  0x005e0c87 in PGSemaphoreLock ()
> #2  0x0061e3af in LWLockAcquire ()
> #3  0x0060aa0f in ReadBuffer_common ()
> #4  0x0060b2e4 in ReadBufferExtended ()
...

These stack trace results indicate that there was heavy contention of
LWLocks for buffers. What I observed is that, in a similar situation, there
was also heavy contention on spin locks that ensure mutual exclusion of
LWLock status data. Those contentions resulted in a sudden increase in CPU
utilization, which is consistent with the following description.
> At the time of the event, we see a spike in system CPU and load average,
but we do not see a corresponding spike in disk reads or writes which would
indicate IO load.

If the cause of the problem is the same as what I observed, a possible
instant countermeasure is increasing the value of 'NUM_BUFFER_PARTITIONS'
defined in src/include/storage/lwlock.h from 16 to, for example, 128 or 256,
and build the binary.
# Using latch-free buffer manager, proposed in my paper, would take long
time, since it is not unincorporated in the upstream.

--
Takashi Horikawa, Ph.D.,
Knowledge Discovery Research Laboratories,
NEC Corporation.



smime.p7s
Description: S/MIME cryptographic signature

Re: [PERFORM] Stalls on PGSemaphoreLock

[Emre Hasegeli]

2014-03-25, Matthew Spilich :

>  Has any on the forum seen something similar?   Any suggestions on what
> to look at next?If it is helpful to describe the server hardware, it's
> got 2 E5-2670 cpu and 256 GB of ram, and the database is hosted on 1.6TB raid
> 10 local storage (15K 300 GB drives).   The workload is predominantly
> read and the queries are mostly fairly simple selects from a single large
> table generally specifying the primary key as part of the where clause
> along with a few other filters.
>
I have seen something similar. It was because of
large shared_buffers.

Re: [PERFORM] Stalls on PGSemaphoreLock

[Matthew Spilich]

Thanks all: 

Ray:  Thanks, we started to look at the hardware/firmware, but didn't get to 
the the level of detail or running sar.   I will probably collect more detail 
in this area if I continue to see issues.

Pavy - I hope that you are right that the hugepage setting is the issue.   I 
was under the impression that I had it disabled already because this has been 
an known issue for us in the past, but it turns out this was not the case for 
this server in question.   I have disabled it at this time, but it will take a 
few days of running without issue before I am comfortable declaring that this 
is the solution.   Can you elaborate on the change you mention to "upgrade the 
semaphore configuration"?   I think this is not something I have looked at 
before.

Ashutosh - Thanks for the reply, I started to do that at first.   I turned on 
log_statement=all for a few hours and I generated a few GB of log file, and I 
didn't want to leave it running in that state for too long because the issue 
happens every few days, and not on any regular schedule, so I reverted that 
after collecting a few GB of detail in the pg log.   What I'm doing now to 
sample every few seconds is I think giving me a decent picture of what is going 
on with the incident occurs and is a level of data collection that I am more 
comfortable will not impact operations.  I am also logging at the level of 
'mod' and all duration > 500ms.   I don't see that large write operations are a 
contributing factor leading up to these incidents.

I'm hoping that disabling the hugepage setting will be the solution to this.  
I'll check back in a day or two with feedback.

Thanks,
Matt



From: Pavy Philippe [philippe.p...@worldline.com]
Sent: Tuesday, March 25, 2014 1:45 PM
To: Ray Stell; Matthew Spilich
Cc: pgsql-performance@postgresql.org
Subject: RE : [PERFORM] Stalls on PGSemaphoreLock

Hello

Recently I have a similar problem. The first symptom was a freeze of the 
connection and 100% of CPU SYS during 2 et 10 minutes, 1 or 2 times per day.
Connection impossible, slow query. The strace on one backend show a very long 
system call on semop().
We have a node with 48 cores dans 128 Go of memory.

We have disable the hugepage and upgrade the semaphore configuration, and since 
that time, we no longer have any problem of freeze on our instance.

Can you check the hugepage and semaphore configuration on our node ?

I am interested in this case, so do not hesitate to let me make a comeback. 
Thanks.

excuse me for my bad english !!!


De : pgsql-performance-ow...@postgresql.org 
[pgsql-performance-ow...@postgresql.org] de la part de Ray Stell [ste...@vt.edu]
Date d'envoi : mardi 25 mars 2014 18:17
À : Matthew Spilich
Cc : pgsql-performance@postgresql.org
Objet : Re: [PERFORM] Stalls on PGSemaphoreLock

On Mar 25, 2014, at 8:46 AM, Matthew Spilich wrote:

The symptom:   The database machine (running postgres 9.1.9 on CentOS 6.4) is 
running a low utilization most of the time, but once every day or two, it will 
appear to slow down to the point where queries back up and clients are unable 
to connect.  Once this event occurs, there are lots of concurrent queries, I 
see slow queries appear in the logs, but there doesn't appear to be anything 
abnormal that I have been able to see that causes this behavior.
...
Has any on the forum seen something similar?   Any suggestions on what to look 
at next?If it is helpful to describe the server hardware, it's got 2 
E5-2670 cpu and 256 GB of ram, and the database is hosted on 1.6TB raid 10 
local storage (15K 300 GB drives).


I could be way off here, but years ago I experienced something like this (in 
oracle land) and after some stressful chasing, the marginal failure of the raid 
controller revealed itself.  Same kind of event, steady traffic and then some 
i/o would not complete and normal ops would stack up.  Anyway, what you report 
reminded me of that event.  The E5 is a few years old, I wonder if the raid 
controller firmware needs a patch?  I suppose a marginal power supply might 
cause a similar "hang."  Anyway, marginal failures are very painful.  Have you 
checked sar or OS logging at event time?


Ce message et les pièces jointes sont confidentiels et réservés à l'usage 
exclusif de ses destinataires. Il peut également être protégé par le secret 
professionnel. Si vous recevez ce message par erreur, merci d'en avertir 
immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant 
être assurée sur Internet, la responsabilité de Worldline ne pourra être 
recherchée quant au contenu de ce message. Bien que les meilleurs efforts 
soient faits pour maintenir cette transmission exempte de tout virus, 
l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne 
saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential 

Re: [PERFORM] Stalls on PGSemaphoreLock

[Ray Stell]

On Mar 25, 2014, at 8:46 AM, Matthew Spilich wrote:

> The symptom:   The database machine (running postgres 9.1.9 on CentOS 6.4) is 
> running a low utilization most of the time, but once every day or two, it 
> will appear to slow down to the point where queries back up and clients are 
> unable to connect.  Once this event occurs, there are lots of concurrent 
> queries, I see slow queries appear in the logs, but there doesn't appear to 
> be anything abnormal that I have been able to see that causes this behavior.
...
> Has any on the forum seen something similar?   Any suggestions on what to 
> look at next?If it is helpful to describe the server hardware, it's got 2 
> E5-2670 cpu and 256 GB of ram, and the database is hosted on 1.6TB raid 10 
> local storage (15K 300 GB drives).  



I could be way off here, but years ago I experienced something like this (in 
oracle land) and after some stressful chasing, the marginal failure of the raid 
controller revealed itself.  Same kind of event, steady traffic and then some 
i/o would not complete and normal ops would stack up.  Anyway, what you report 
reminded me of that event.  The E5 is a few years old, I wonder if the raid 
controller firmware needs a patch?  I suppose a marginal power supply might 
cause a similar "hang."  Anyway, marginal failures are very painful.  Have you 
checked sar or OS logging at event time?

Re: [PERFORM] pg_dump vs pg_basebackup

[Claudio Freire]

On Tue, Mar 25, 2014 at 12:22 PM, Joshua D. Drake  
wrote:
> On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
>>
>>
>> Joshua,
>>
>> that is really good point: an alternative is to use pg_basebackup
>> through ssh tunnel with compression, but rsync is much simpler.
>
>
> Or rsync over ssh. The advantage is that you can create backups that don't
> have to be restored, just started. You can also use the differential
> portions of rsync to do it multiple times a day without much issue.


rsync's delta transfer isn't relly very effective with postgres. You
don't save any I/O, just network traffic, and in general the
bottleneck is I/O (unless you have a monster I/O subsys or a snail of
a network one).

There were some musing about making delta transfer more efficient in
pg in hackers, but I don't think anything tangible came out of that,
so it's basically equivalent to a full transfer. The only reason to
leverage rsync's delta transfer would be to decrease the time between
pg_start_backup and pg_stop_backup, which could only matter if you're
low on WAL space, but the reduction, in my experience, isn't stellar.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Alan Hodgson]

On Tuesday, March 25, 2014 03:48:07 PM Graeme B. Bell wrote:
> Postgresql rsync backups require the DB to be shutdown during the 'second'
> rsync.
> 
> 1. rsync the DB onto the backup filesystem  (produces e.g. 95-99.99%
> consistent DB on the backup filesystem) 2. shut down the DB
> 3. rsync the shut down DB onto the backup filesystem(synchronises the
> last few files to make the DB consistent, and is usually very fast) 4.
> start the DB up again
> 
> Is there any way to notify postgres to pause transactions (and note that
> they should be restarted), and flush out write buffers etc, instead of
> doing a full shutdown? e.g. so that the second rsync call would bring the
> backup filesystem's representation of the DB into a recoverable state
> without needing to shutdown the production DB completely.
> 

You use pg_start_backup() before rsync, and pg_stop_backup() after. And keep 
all your WAL log files. No need to pause transactions; whatever happens during 
the rsync just gets replayed during recovery (as I understand it). You do need 
to do a PITR restore to make use of this rsync copy.

That's basically what pg_basebackup does, I believe (I haven't used it, I only 
do rsyncs).



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Joshua D. Drake]

On 03/25/2014 08:21 AM, Magnus Hagander wrote:

I would say that's the one thing that rsync is *not*. pg_basebackup
takes care of a lot of things under the hood. rsync is a lot more
complicated, in particular in failure scenarios, since you have to
manually deal with pg_start/stop_backup().

There are definitely reasons you'd prefer rsync over pg_basebackup, but
I don't believe simplicity is one of them.

//Magnus


Good God man... since when do you top post!

Well there are tools that use rsync to solve those issues :P. We even 
have one that does multi-threaded rsync so you can pull many Terabytes 
in very little time (relatively).


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Graeme B. Bell]

Postgresql rsync backups require the DB to be shutdown during the 'second' 
rsync.

1. rsync the DB onto the backup filesystem  (produces e.g. 95-99.99% consistent 
DB on the backup filesystem)
2. shut down the DB
3. rsync the shut down DB onto the backup filesystem(synchronises the last 
few files to make the DB consistent, and is usually very fast)
4. start the DB up again

Is there any way to notify postgres to pause transactions (and note that they 
should be restarted), and flush out write buffers etc, instead of doing a full 
shutdown? 
e.g. so that the second rsync call would bring the backup filesystem's 
representation of the DB into a recoverable state without needing to shutdown 
the production DB completely. 

G

On 25 Mar 2014, at 16:29, Ilya Kosmodemiansky 
 wrote:

> Joshua,
> 
> On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake  
> wrote:
> The advantage is that you can create backups that don't
>> have to be restored, just started. You can also use the differential
>> portions of rsync to do it multiple times a day without much issue.
> 
> Are you sure, that it is a nice idea on a database with heavy write workload?
> 
> And also Im not sure, that differential backups using rsync will be
> recoverable, if you have actually meant that.
> 
>> 
>> 
>> JD
>> 
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
>> PostgreSQL Support, Training, Professional Services and Development
>> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> Political Correctness is for cowards.
> 
> 
> 
> -- 
> Ilya Kosmodemiansky,
> 
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> i...@postgresql-consulting.com
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

OK, agreed. Ive got your point;-)

On Tue, Mar 25, 2014 at 4:40 PM, Magnus Hagander  wrote:
> Oh, I agree it's good that you should know both methods. I only disagree
> with that the choice of rsync be made with the argument of simplicity.
> Simplicity is one of the main reasons to choose the *other* method
> (pg_basebackup), and the rsync method is for more advanced usecases. But
> it's definitely good to know both!
>
> //Magnus
>
>
>
> On Tue, Mar 25, 2014 at 4:37 PM, Ilya Kosmodemiansky
>  wrote:
>>
>> Magnus,
>>
>> That is correct, but I'am afraid that such all-in-one functionality
>> also hides from one how backup really works. Probably such sort of
>> knowledge is so essential for a DBA, that it is better to learn both
>> methods, at least to be able to choose correctly? But maybe it is a
>> rhetorical question.
>>
>> On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander 
>> wrote:
>> > I would say that's the one thing that rsync is *not*. pg_basebackup
>> > takes
>> > care of a lot of things under the hood. rsync is a lot more complicated,
>> > in
>> > particular in failure scenarios, since you have to manually deal with
>> > pg_start/stop_backup().
>> >
>> > There are definitely reasons you'd prefer rsync over pg_basebackup, but
>> > I
>> > don't believe simplicity is one of them.
>> >
>> > //Magnus
>> >
>> >
>> > On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
>> >  wrote:
>> >>
>> >> Joshua,
>> >>
>> >> that is really good point: an alternative is to use pg_basebackup
>> >> through ssh tunnel with compression, but rsync is much simpler.
>> >>
>> >> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake 
>> >> wrote:
>> >> >
>> >> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
>> >> >>
>> >> >>
>> >> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston 
>> >> >> wrote:
>> >> 
>> >>  Hai,
>> >> 
>> >>  Can anyone tell me the difference and performance between pgdump
>> >>  and
>> >>  pg_basebackup if I want to backup a large database.
>> >> 
>> >> >
>> >> > Honestly,
>> >> >
>> >> > Neither is particularly good at backing up large databases. I would
>> >> > look
>> >> > into PITR with rsync.
>> >> >
>> >> > JD
>> >> >
>> >> >
>> >> > --
>> >> > Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
>> >> > PostgreSQL Support, Training, Professional Services and Development
>> >> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> >> > Political Correctness is for cowards.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Sent via pgsql-performance mailing list
>> >> > (pgsql-performance@postgresql.org)
>> >> > To make changes to your subscription:
>> >> > http://www.postgresql.org/mailpref/pgsql-performance
>> >>
>> >>
>> >>
>> >> --
>> >> Ilya Kosmodemiansky,
>> >>
>> >> PostgreSQL-Consulting.com
>> >> tel. +14084142500
>> >> cell. +4915144336040
>> >> i...@postgresql-consulting.com
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-performance mailing list
>> >> (pgsql-performance@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-performance
>> >
>> >
>> >
>> >
>> > --
>> >  Magnus Hagander
>> >  Me: http://www.hagander.net/
>> >  Work: http://www.redpill-linpro.com/
>>
>>
>>
>> --
>> Ilya Kosmodemiansky,
>>
>> PostgreSQL-Consulting.com
>> tel. +14084142500
>> cell. +4915144336040
>> i...@postgresql-consulting.com
>
>
>
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Magnus Hagander]

Oh, I agree it's good that you should know both methods. I only disagree
with that the choice of rsync be made with the argument of simplicity.
Simplicity is one of the main reasons to choose the *other* method
(pg_basebackup), and the rsync method is for more advanced usecases. But
it's definitely good to know both!

//Magnus


On Tue, Mar 25, 2014 at 4:37 PM, Ilya Kosmodemiansky <
ilya.kosmodemian...@postgresql-consulting.com> wrote:

> Magnus,
>
> That is correct, but I'am afraid that such all-in-one functionality
> also hides from one how backup really works. Probably such sort of
> knowledge is so essential for a DBA, that it is better to learn both
> methods, at least to be able to choose correctly? But maybe it is a
> rhetorical question.
>
> On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander 
> wrote:
> > I would say that's the one thing that rsync is *not*. pg_basebackup takes
> > care of a lot of things under the hood. rsync is a lot more complicated,
> in
> > particular in failure scenarios, since you have to manually deal with
> > pg_start/stop_backup().
> >
> > There are definitely reasons you'd prefer rsync over pg_basebackup, but I
> > don't believe simplicity is one of them.
> >
> > //Magnus
> >
> >
> > On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
> >  wrote:
> >>
> >> Joshua,
> >>
> >> that is really good point: an alternative is to use pg_basebackup
> >> through ssh tunnel with compression, but rsync is much simpler.
> >>
> >> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake 
> >> wrote:
> >> >
> >> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
> >> >>
> >> >>
> >> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston 
> >> >> wrote:
> >> 
> >>  Hai,
> >> 
> >>  Can anyone tell me the difference and performance between pgdump
> and
> >>  pg_basebackup if I want to backup a large database.
> >> 
> >> >
> >> > Honestly,
> >> >
> >> > Neither is particularly good at backing up large databases. I would
> look
> >> > into PITR with rsync.
> >> >
> >> > JD
> >> >
> >> >
> >> > --
> >> > Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
> >> > PostgreSQL Support, Training, Professional Services and Development
> >> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> >> > Political Correctness is for cowards.
> >> >
> >> >
> >> >
> >> > --
> >> > Sent via pgsql-performance mailing list
> >> > (pgsql-performance@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >>
> >>
> >> --
> >> Ilya Kosmodemiansky,
> >>
> >> PostgreSQL-Consulting.com
> >> tel. +14084142500
> >> cell. +4915144336040
> >> i...@postgresql-consulting.com
> >>
> >>
> >> --
> >> Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >
> >
> >
> >
> > --
> >  Magnus Hagander
> >  Me: http://www.hagander.net/
> >  Work: http://www.redpill-linpro.com/
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> i...@postgresql-consulting.com
>



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

Magnus,

That is correct, but I'am afraid that such all-in-one functionality
also hides from one how backup really works. Probably such sort of
knowledge is so essential for a DBA, that it is better to learn both
methods, at least to be able to choose correctly? But maybe it is a
rhetorical question.

On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander  wrote:
> I would say that's the one thing that rsync is *not*. pg_basebackup takes
> care of a lot of things under the hood. rsync is a lot more complicated, in
> particular in failure scenarios, since you have to manually deal with
> pg_start/stop_backup().
>
> There are definitely reasons you'd prefer rsync over pg_basebackup, but I
> don't believe simplicity is one of them.
>
> //Magnus
>
>
> On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
>  wrote:
>>
>> Joshua,
>>
>> that is really good point: an alternative is to use pg_basebackup
>> through ssh tunnel with compression, but rsync is much simpler.
>>
>> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake 
>> wrote:
>> >
>> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
>> >>
>> >>
>> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston 
>> >> wrote:
>> 
>>  Hai,
>> 
>>  Can anyone tell me the difference and performance between pgdump and
>>  pg_basebackup if I want to backup a large database.
>> 
>> >
>> > Honestly,
>> >
>> > Neither is particularly good at backing up large databases. I would look
>> > into PITR with rsync.
>> >
>> > JD
>> >
>> >
>> > --
>> > Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
>> > PostgreSQL Support, Training, Professional Services and Development
>> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> > Political Correctness is for cowards.
>> >
>> >
>> >
>> > --
>> > Sent via pgsql-performance mailing list
>> > (pgsql-performance@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>>
>> --
>> Ilya Kosmodemiansky,
>>
>> PostgreSQL-Consulting.com
>> tel. +14084142500
>> cell. +4915144336040
>> i...@postgresql-consulting.com
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

Joshua,

On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake  wrote:
The advantage is that you can create backups that don't
> have to be restored, just started. You can also use the differential
> portions of rsync to do it multiple times a day without much issue.

Are you sure, that it is a nice idea on a database with heavy write workload?

And also Im not sure, that differential backups using rsync will be
recoverable, if you have actually meant that.

>
>
> JD
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Joshua D. Drake]

On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:


Joshua,

that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.


Or rsync over ssh. The advantage is that you can create backups that 
don't have to be restored, just started. You can also use the 
differential portions of rsync to do it multiple times a day without 
much issue.


JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Magnus Hagander]

I would say that's the one thing that rsync is *not*. pg_basebackup takes
care of a lot of things under the hood. rsync is a lot more complicated, in
particular in failure scenarios, since you have to manually deal with
pg_start/stop_backup().

There are definitely reasons you'd prefer rsync over pg_basebackup, but I
don't believe simplicity is one of them.

//Magnus


On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky <
ilya.kosmodemian...@postgresql-consulting.com> wrote:

> Joshua,
>
> that is really good point: an alternative is to use pg_basebackup
> through ssh tunnel with compression, but rsync is much simpler.
>
> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake 
> wrote:
> >
> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
> >>
> >>
> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston 
> wrote:
> 
>  Hai,
> 
>  Can anyone tell me the difference and performance between pgdump and
>  pg_basebackup if I want to backup a large database.
> 
> >
> > Honestly,
> >
> > Neither is particularly good at backing up large databases. I would look
> > into PITR with rsync.
> >
> > JD
> >
> >
> > --
> > Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
> > PostgreSQL Support, Training, Professional Services and Development
> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> > Political Correctness is for cowards.
> >
> >
> >
> > --
> > Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> i...@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

Joshua,

that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.

On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake  wrote:
>
> On 03/25/2014 05:05 AM, Claudio Freire wrote:
>>
>>
>> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston  wrote:

 Hai,

 Can anyone tell me the difference and performance between pgdump and
 pg_basebackup if I want to backup a large database.

>
> Honestly,
>
> Neither is particularly good at backing up large databases. I would look
> into PITR with rsync.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[desmodemone]

2014-03-25 15:56 GMT+01:00 Joshua D. Drake :

>
> On 03/25/2014 05:05 AM, Claudio Freire wrote:
>
>>
>> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston  wrote:
>>
>>> Hai,

 Can anyone tell me the difference and performance between pgdump and
 pg_basebackup if I want to backup a large database.


> Honestly,
>
> Neither is particularly good at backing up large databases. I would look
> into PITR with rsync.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


For large database it's possible also to consider , also, to change
database status in backup mode and after take a snapshoot and returning to
normal mode, saving also all archive after you finish the backup.

With that snapshoot you could easy  mount it and restore on another machine
or open in readonly mode (hot standby and after do a logical dump ) , a lot
of storage have these capabilities and also filesystem or volume manager.

I think these is the fater  option you have.

Mat Dba

Re: [PERFORM] pg_dump vs pg_basebackup

[Joshua D. Drake]

On 03/25/2014 05:05 AM, Claudio Freire wrote:


On Tue, Mar 25, 2014 at 4:39 AM, David Johnston  wrote:

Hai,

Can anyone tell me the difference and performance between pgdump and
pg_basebackup if I want to backup a large database.



Honestly,

Neither is particularly good at backing up large databases. I would look 
into PITR with rsync.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Stalls on PGSemaphoreLock

[Matthew Spilich]

Hi everyone!


I've been working on a puzzling issue for a few days am am hoping that someone 
has seen something similar or can help.  There have been some odd behaviors on 
one of my production facing postgres servers.


version info from postgres: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, 
compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit


The symptom:   The database machine (running postgres 9.1.9 on CentOS 6.4) is 
running a low utilization most of the time, but once every day or two, it will 
appear to slow down to the point where queries back up and clients are unable 
to connect.  Once this event occurs, there are lots of concurrent queries, I 
see slow queries appear in the logs, but there doesn't appear to be anything 
abnormal that I have been able to see that causes this behavior.  The event 
will occur just long enough for monitoring to alarm.   We will respond to 
alerts to take a look, but within a minute or three at most, load returns back 
to normal levels and all running queries complete in expected times.


At the time of the event, we see a spike in system CPU and load average, but we 
do not see a corresponding spike in disk reads or writes which would indicate 
IO load.   Initial troubleshooting to monitor active processes led us to see a 
flurry of activity in ps waiting on semtimedop.   Our efforts internally to 
diagnose this problem are to sample pg_locks and pg_stat_activity every 5s plus 
running a script to look for at least one postgres process waiting on a 
semaphore, and if it finds one, it gets a stack trace of every running postgres 
processes with GDB.  It also uses strace on 5 processes to find out which 
semaphore they're waiting on.


What we were catching in the following stack trace seems to be representative 
of where things are waiting when we see an event - here are two examples that 
are representative - lots of threads will appear to be in this state:


- 47245 -
0x0037392eb197 in semop () from /lib64/libc.so.6
#0  0x0037392eb197 in semop () from /lib64/libc.so.6
#1  0x005e0c87 in PGSemaphoreLock ()
#2  0x0061e3af in LWLockAcquire ()
#3  0x0060aa0f in ReadBuffer_common ()
#4  0x0060b2e4 in ReadBufferExtended ()
#5  0x0047708d in _bt_relandgetbuf ()
#6  0x0047aac4 in _bt_search ()
#7  0x0047af8d in _bt_first ()
#8  0x00479704 in btgetbitmap ()
#9  0x006e7e00 in FunctionCall2Coll ()
#10 0x00473120 in index_getbitmap ()
#11 0x005726b8 in MultiExecBitmapIndexScan ()
#12 0x0057214d in BitmapHeapNext ()
#13 0x0056b18e in ExecScan ()
#14 0x00563ed8 in ExecProcNode ()
#15 0x00562d72 in standard_ExecutorRun ()
#16 0x0062ce67 in PortalRunSelect ()
#17 0x0062e128 in PortalRun ()
#18 0x0062bb66 in PostgresMain ()
#19 0x005ecd01 in ServerLoop ()
#20 0x005ef401 in PostmasterMain ()
#21 0x00590ff8 in main ()

- 47257 -
0x0037392eb197 in semop () from /lib64/libc.so.6
#0  0x0037392eb197 in semop () from /lib64/libc.so.6
#1  0x005e0c87 in PGSemaphoreLock ()
#2  0x0061e3af in LWLockAcquire ()
#3  0x0060aa0f in ReadBuffer_common ()
#4  0x0060b2e4 in ReadBufferExtended ()
#5  0x0047708d in _bt_relandgetbuf ()
#6  0x0047aac4 in _bt_search ()
#7  0x0047af8d in _bt_first ()
#8  0x004797d1 in btgettuple ()
#9  0x006e7e00 in FunctionCall2Coll ()
#10 0x0047339d in index_getnext ()
#11 0x00575ed6 in IndexNext ()
#12 0x0056b18e in ExecScan ()
#13 0x00563ee8 in ExecProcNode ()
#14 0x00562d72 in standard_ExecutorRun ()
#15 0x0062ce67 in PortalRunSelect ()
#16 0x0062e128 in PortalRun ()
#17 0x0062bb66 in PostgresMain ()
#18 0x005ecd01 in ServerLoop ()
#19 0x005ef401 in PostmasterMain ()
#20 0x00590ff8 in main ()


Has any on the forum seen something similar?   Any suggestions on what to look 
at next?If it is helpful to describe the server hardware, it's got 2 
E5-2670 cpu and 256 GB of ram, and the database is hosted on 1.6TB raid 10 
local storage (15K 300 GB drives).   The workload is predominantly read and the 
queries are mostly fairly simple selects from a single large table generally 
specifying the primary key as part of the where clause along with a few other 
filters.


Thanks,

Matt

Re: [PERFORM] pg_dump vs pg_basebackup

[Claudio Freire]

On Tue, Mar 25, 2014 at 4:39 AM, David Johnston  wrote:
>> Hai,
>>
>> Can anyone tell me the difference and performance between pgdump and
>> pg_basebackup if I want to backup a large database.
>>
>> Thanks
>
> Yes.  And many of their words have been written down in the documentation in
> a chapter named "Backup and Restore".  Do you have a specific question about
> what is written there?
>
> I'll add that comparing the performance of both is relatively meaningless.
> You need to understand how each works then choose the correct tool for your
> situation.


I don't know if meaningless is the right word here. I have a ~450G
database, and the difference is quite meaningful to me, as it is
measured in days.

The difference being, pg_basebackup is dumber and using it is harder,
but its performance is only limited by sequential I/O capacity (which
is usually quite high). It is also used in conjunction with PITR to
get not only that, but also incremental backups, which is something
you really want for big databass. pg_dump, on the other hand, will
only do full dumps and it will be limited both by I/O and CPU power,
because the reformatting involved in making a dump is considerable. In
my experience, a base backup takes hours, while a dump takes days.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[David Johnston]

gianfranco caca wrote
> Hai,
> 
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
> 
> Thanks

Yes.  And many of their words have been written down in the documentation in
a chapter named "Backup and Restore".  Do you have a specific question about
what is written there?

I'll add that comparing the performance of both is relatively meaningless.
You need to understand how each works then choose the correct tool for your
situation.

Lastly, you should actually do both, on a development database, and measure
the time and effort while practicing both routines (backup and restoring)
yourself.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-dump-vs-pg-basebackup-tp5797351p5797364.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

Yes, you need to set recovery_target_time in your recovery.conf while
performing recovery
(http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html).
That could be a tricky thing - depends on that exactly you need. All
those transactions, which were not committed at given timestamp, will
be rollbacked, so read url above carefully.

On Tue, Mar 25, 2014 at 8:19 AM, gianfranco caca  wrote:
> Hai ilya,
>
> Thanks for the respond. The database is estimated over 100gb and the
> workload will be high. Can we use a pg_basebackup with pitr to restore based
> on transaction time?
>
> Thanks
>
>
> On Tuesday, 25 March 2014, 15:13, Ilya Kosmodemiansky
>  wrote:
> Hi gianfranco,
>
>
> How exactly large is your database and how heavy is a workload on it?
> Usually if you have more than ~200Gb, better to use pg_basebackup
> because pg_dump will take too long time. And please take in mind, that
> pg_dump makes dump, which is  actually not the same thing as a backup.
>
> Best regards,
> Ilya
>
> On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca  wrote:
>> Hai,
>>
>> Can anyone tell me the difference and performance between pgdump and
>> pg_basebackup if I want to backup a large database.
>>
>> Thanks
>
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> i...@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[gianfranco caca]

Hai ilya,

Thanks for the respond. The database is estimated over 100gb and the workload 
will be high. Can we use a pg_basebackup with pitr to restore based on 
transaction time?

Thanks




On Tuesday, 25 March 2014, 15:13, Ilya Kosmodemiansky 
 wrote:
 
Hi gianfranco,


How exactly large is your database and how heavy is a workload on it?
Usually if you have more than ~200Gb, better to use pg_basebackup
because pg_dump will take too long time. And please take in mind, that
pg_dump makes dump, which is  actually not the same thing as a backup.

Best regards,
Ilya


On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca  wrote:
> Hai,
>
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
>
> Thanks



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] pg_dump vs pg_basebackup

[Ilya Kosmodemiansky]

Hi gianfranco,


How exactly large is your database and how heavy is a workload on it?
Usually if you have more than ~200Gb, better to use pg_basebackup
because pg_dump will take too long time. And please take in mind, that
pg_dump makes dump, which is  actually not the same thing as a backup.

Best regards,
Ilya

On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca  wrote:
> Hai,
>
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
>
> Thanks



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] slow join not using index properly

[Ilya Kosmodemiansky]

Hi Stefan,

stupid me - Ive missed some

with RECURSIVE qq(cont_key, anc_key) AS
  (

SELECT
  a1.context_key, ancestor_key
FROM
  virtual_ancestors a1
UNION select ( -- here, in the union
  SELECT
a1.context_key, a1.ancestor_key
  FROM
virtual_ancestors a1
  WHERE
a1.context_key > cont_key
  ORDER BY
a1.context_key LIMIT 1
)
from qq where cont_key is not null -- and here
  )
  SELECT
distinct a.cont_key
  FROM
qq a, collection_data, virtual_ancestors a2
  WHERE
a.cont_key IS NOT NULL
AND a.anc_key = collection_data.context_key
AND collection_data.collection_
>
> context_key = a2.context_key
> AND a2.ancestor_key = 1072173;

sorry for disorientating

On Mon, Mar 24, 2014 at 7:40 PM, Stefan Amshey  wrote:
> Hi Ilya-
>
> Thanks so much for taking a stab at optimizing that query.  I had to fiddle
> a bit with your proposed version in order to get it function. Here's what I
> came up with in the end:
>>
>> with RECURSIVE qq(cont_key, anc_key) AS
>>   (
>>
>> SELECT
>>   a1.context_key, ancestor_key
>> FROM
>>   virtual_ancestors a1
>> UNION (
>>   SELECT
>> a1.context_key, a1.ancestor_key
>>   FROM
>> virtual_ancestors a1, qq
>>   WHERE
>> context_key > qq.cont_key
>>   ORDER BY
>> context_key LIMIT 1
>> )
>>   )
>>   SELECT
>> distinct a.cont_key
>>   FROM
>> qq a, collection_data, virtual_ancestors a2
>>   WHERE
>> a.cont_key IS NOT NULL
>> AND a.anc_key = collection_data.context_key
>> AND collection_data.collection_context_key = a2.context_key
>> AND a2.ancestor_key = 1072173;
>
>
> I had to drop the MIN( a1.context_key ) and LIMIT 1 from your version off of
> the first select statement in order to avoid syntax issues or other errors.
> The version above does produce the same counts as the original, but in the
> end it wasn't really a win for us. Here's the plan it produced:
>
>> HashAggregate  (cost=707724.36..707726.36 rows=200 width=4) (actual
>> time=27638.844..27639.706 rows=3522 loops=1)
>>Buffers: shared hit=79323, temp read=49378 written=47557
>>CTE qq
>>  ->  Recursive Union  (cost=0.00..398869.78 rows=10814203 width=8)
>> (actual time=0.018..20196.397 rows=10821685 loops=1)
>>Buffers: shared hit=74449, temp read=49378 written=23779
>>->  Seq Scan on virtual_ancestors a1  (cost=0.00..182584.93
>> rows=10814193 width=8) (actual time=0.010..2585.411 rows=10821685 loops=1)
>>  Buffers: shared hit=74443
>>->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual
>> time=7973.297..7973.298 rows=1 loops=1)
>>  Buffers: shared hit=6, temp read=49378 written=1
>>  ->  Nested Loop  (cost=0.00..30881281719119.79
>> rows=389822567470830 width=8) (actual time=7973.296..7973.296 rows=1
>> loops=1)
>>Join Filter: (a1.context_key > qq.cont_key)
>>Rows Removed by Join Filter: 22470607
>>Buffers: shared hit=6, temp read=49378 written=1
>>->  Index Scan using virtual_context_key_idx on
>> virtual_ancestors a1  (cost=0.00..18206859.46 rows=10814193 width=8) (actual
>> time=0.018..0.036 rows=3 loops=1)
>>  Buffers: shared hit=6
>>->  WorkTable Scan on qq  (cost=0.00..2162838.60
>> rows=108141930 width=4) (actual time=0.008..1375.445 rows=7490203 loops=3)
>>  Buffers: temp read=49378 written=1
>>->  Hash Join  (cost=25283.37..308847.31 rows=2905 width=4) (actual
>> time=449.167..27629.759 rows=13152 loops=1)
>>  Hash Cond: (a.anc_key = collection_data.context_key)
>>  Buffers: shared hit=79323, temp read=49378 written=47557
>>  ->  CTE Scan on qq a  (cost=0.00..216284.06 rows=10760132
>> width=8) (actual time=0.021..25265.179 rows=10821685 loops=1)
>>Filter: (cont_key IS NOT NULL)
>>Buffers: shared hit=74449, temp read=49378 written=47557
>>  ->  Hash  (cost=25282.14..25282.14 rows=98 width=4) (actual
>> time=373.836..373.836 rows=2109 loops=1)
>>Buckets: 1024  Batches: 1  Memory Usage: 75kB
>>Buffers: shared hit=4874
>>->  Hash Join  (cost=17557.15..25282.14 rows=98 width=4)
>> (actual time=368.374..373.013 rows=2109 loops=1)
>>
>>  Hash Cond: (a2.context_key =
>> collection_data.collection_context_key)
>>  Buffers: shared hit=4874
>>  ->  Index Only Scan using virtual_ancestors_pkey on
>> virtual_ancestors a2  (cost=0.00..238.57 rows=272 width=4) (actual
>> time=0.029..1.989 rows=1976 loops=1)
>>
>>Index Cond: (ancestor_key = 1072173)
>>Heap Fetches: 917
>>Buffers: shared hit=883
>>  ->  Hash