[GENERAL] Do all Postgres queries touch Shared_Buffers at some point?

[Shiv Sharma]

I am puzzled about the extent to which shared_bufferes is used for
different queries.  Do _all_ queries "touch" shared buffers at some point
of their execution?

Many of our warehouse queries are  seq_scan followed by HASH.   I know
work_mem is assigned for HASH joins:  but does this mean that these queries
never touch shared buffers at *all* during their execution?  Perhaps they
are read into shared_buffers and then passed into work_mem HASH areas???

What about updates on big tables? What about inserts  on big tables? What
about append-inserts?

I think I could get these answers from Explain Analyze Buffers but I am on
8.2 :-(

Please tell me which queries use/touch shared_buffers in general terms, or
please point me to documentation.


Shiv

Re: [GENERAL] PG replication across DataCenters

[Bill Moran]

On Tue, 24 Dec 2013 14:39:42 +0800 Sameer Kumar  wrote:
>
> * Cascading replication chains (a really big deal when you want
> 
>   multiple slaves in the secondary facility and don't want to hog
> 
>   your bandwidth)
> 
> Really? which version of Postgres are we talking about? I think cascaded
> replication facility is available since v9.2
> http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION

Nice.  Seems it's been longer than I realized since I last evaluated
what streaming replication is capble of.

> * Quick and easy movement of the master to any of the database in
> 
>   the cluster without destroying replication.
> 
> Again, which version? Re-mastering is made simple in v9.3.

I'm not seeing that in the documentation.  In fact, what I'm finding
seems to suggest the opposite: that each node's master is configured
in a config file, so in the case of a complicated replication setup,
I would have to run around editing config files on multiple servers
to move the master ... unless I'm missing something in the documentation.

> * Seeding of new slaves without interrupting existing nodes (assuming
> 
>   your hardware has a little free capacity)
> 
> AFAIK, streaming replication does not cause any interruption while you add
> a new node.

The process is still significantly more involved than Slony's subscription
commands.  In our lab setups, I've watched junior DBA's fail time and time
again to get a proper seed with streaming replication.

> In general I do not like trigger based (replication) solutions for huge
> clusters [this is my personal opinion and does not necessarily indicate my
> employer's opinion ;-)] and for databases which has huge write volume
> specifically if you do bulk insert/delete/update operations.

There are definitely drawbacks, I'll grant you that.  If it's raw throughput
you need, Slony probably isn't going to cut it for you.

> I think if it's slony or streaming replication will depend on below factors:
> 
> 1) The change-set that you want to replicate contributes how much of your
> total change set? e.g. on a per minute basis if it's 70% or above, I will
> recommend you to go for streaming replication

While this is a strong argument in favor of streaming over Slony, the
70% number seems rather arbitrary, and you're advocating that this point
alone is enough to outweight the other advantages of Slony, which may be
more important in a particular case.

> 2) Do you have too many tables to be added to replication set? lets say
> above 70% of your total tables needs to be replication (unless rest 30%
> have high write operations), then go for streaming replication

Again, this seems arbitrary.  If the management that Slony provides is
needed, then why would I care what percentage of tables are involved?

> 3) Do you too many bulk operations happening on the tables which needs to
> be replicated

This is arguably a shortcoming of trigger-based replication that trumps just
about everything else.  If Slony just can't keep up, then you don't have much
choice.

> 4) To some extent your choice will be influenced by the motivation behind
> replication, DR, HA, reporting application (esp if you are particular about
> replicating only selective tables for reports)

In my experience, this is usually the largest factor.

Once argument in favor of streaming that you missed is when you have no
control over the schema (for example, when it's 3rd party, like an openfire
database).  In those cases, the application frequently omits things like
primary keys (which are required for slony) and has an upgrade process that
assumes it can change database tables without impacting anything else.

> There are few easier ways of managing a slony cluster:
> 
> 1)
> http://shoaibmir.wordpress.com/2009/08/05/setting-up-slony-cluster-with-perltools/
> 
> 2) I think even pgadmin supports slony replication (not sure if its slony-I
> or slony-II)

I'll add dbsteward to this list, as we wrote it (in part) to make slony
management easier on systems that experience frequent change.

-- 
Bill Moran 


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

Re: [GENERAL] Do all Postgres queries touch Shared_Buffers at some point?

[Michael Paquier]

On Sun, Dec 29, 2013 at 9:05 PM, Shiv Sharma  wrote:
> I am puzzled about the extent to which shared_bufferes is used for different
> queries.  Do _all_ queries "touch" shared buffers at some point of their
> execution?
>
> Many of our warehouse queries are  seq_scan followed by HASH.   I know
> work_mem is assigned for HASH joins:  but does this mean that these queries
> never touch shared buffers at *all* during their execution?  Perhaps they
> are read into shared_buffers and then passed into work_mem HASH areas???
>
> What about updates on big tables? What about inserts  on big tables? What
> about append-inserts?
>
> I think I could get these answers from Explain Analyze Buffers but I am on
> 8.2 :-(
>
> Please tell me which queries use/touch shared_buffers in general terms, or
> please point me to documentation.
All your queries that interacts with relations.

shared_buffers is used for data caching across all the backends of the
server, to put it simply pages of the relation involved. Such data can
be relation data, like data of a table you defined yourself, index
data, or some system catalog data, containing definitions of the
database objects. So simply everything that is a relation and contains
physical data might be in shared buffers. Views for example do not
enter in this category.

You could for example use pg_buffercache to have a look at what
contains the shared buffers:
http://www.postgresql.org/docs/devel/static/pgbuffercache.html

Regards,
-- 
Michael


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

Re: [GENERAL] PG replication across DataCenters

[Sameer Kumar]

>> > * Quick and easy movement of the master to any of the database in
>> >
>> >   the cluster without destroying replication.
>> >
>> > Again, which version? Re-mastering is made simple in v9.3.

>> I'm not seeing that in the documentation.  In fact, what I'm finding
>> seems to suggest the opposite: that each node's master is configured
>> in a config file, so in the case of a complicated replication setup,
>> I would have to run around editing config files on multiple servers
>> to move the master ... unless I'm missing something in the documentation.

Well, the pain can be minimized if you can write some simple shell scripts
for this. Or if you can have a floating/virtual IP.


>>> * Seeding of new slaves without interrupting existing nodes (assuming
>>>
>>>   your hardware has a little free capacity)
>>>
>>> AFAIK, streaming replication does not cause any interruption while you
add
>>> a new node.

>>The process is still significantly more involved than Slony's subscription
>>commands.  In our lab setups, I've watched junior DBA's fail time and time
>>again to get a proper seed with streaming replication.

Try the pg_basebackup options in v9.3. Creating a streaming replica has
been made easy. It's still a little painful if you want to move your WALs
to a different LUW/HDD on your replica

>>> I think if it's slony or streaming replication will depend on below
factors:
>>>
>>> 1) The change-set that you want to replicate contributes how much of
your
>>> total change set? e.g. on a per minute basis if it's 70% or above, I
will
>>> recommend you to go for streaming replication

>>While this is a strong argument in favor of streaming over Slony, the
>>70% number seems rather arbitrary, and you're advocating that this point
>>alone is enough to outweight the other advantages of Slony, which may be
>>more important in a particular case.

I gave an example. It will definately vary from case to case and
implementation to implementation.


>>> 4) To some extent your choice will be influenced by the motivation
behind
>>> replication, DR, HA, reporting application (esp if you are particular
about
>>> replicating only selective tables for reports)

>>In my experience, this is usually the largest factor.


>>>To some extent your choice will be influenced <<<
Let me correct myself:
To a large extent your choice will be influnced :)


>>Once argument in favor of streaming that you missed is when you have no
>>control over the schema (for example, when it's 3rd party, like an
openfire
>>database).  In those cases, the application frequently omits things like
>>primary keys (which are required for slony) and has an upgrade process
that
>>assumes it can change database tables without impacting anything else.

That's a good one and quite apt too!


Regards
Sameer
Ashnik Pte. Ltd.
Singapore

Re: [GENERAL] PG replication across DataCenters

[Bill Moran]

On Mon, 30 Dec 2013 00:15:37 +0800 Sameer Kumar  wrote:

> >> > * Quick and easy movement of the master to any of the database in
> >> >
> >> >   the cluster without destroying replication.
> >> >
> >> > Again, which version? Re-mastering is made simple in v9.3.
> 
> >> I'm not seeing that in the documentation.  In fact, what I'm finding
> >> seems to suggest the opposite: that each node's master is configured
> >> in a config file, so in the case of a complicated replication setup,
> >> I would have to run around editing config files on multiple servers
> >> to move the master ... unless I'm missing something in the documentation.
> 
> Well, the pain can be minimized if you can write some simple shell scripts
> for this. Or if you can have a floating/virtual IP.

This is probably the only point that we're not seeing eye to eye on.

Take a real scenario I have to maintain.  There is a single master and 11
replicas spread across 2 datacenters.  Some of these replicas are read-only
for the application, 1 is for analytics, another supports development,
another is a dedicated backup system.  The rest are purely for DR..

Now, in a facility failure scenario, all is well, we just promote
the DR master in the secondary datacenter and go back to work -- this should
be equally easy with either Slony or streaming

What I don't see streaming working for is DR drills.  I need to, in a
controlled manner, move the entire application to the secondary datacenter,
while keeping all the nodes in sync, make sure everything operates properly
from there (which means allowing database updates), then move it all back
to the primary datacenter, without losing sync on any slaves (this is a 2T
database, which I'm sure isn't the largest anyone has dealt with, but it
means that reseeding slaves is a multi-hour endeavour).  With Slony, these
drills are easy: a single slonik command relocates the master to the DR
datacenter while keeping everything in sync, and when testing is complete,
another slonik command puts everything back the way it was, without any
data loss and with minimal chance for human error.

If you feel that the current implementation of streaming replication is
able to do that task, then I'll have to move up my timetable to re-evaluate
it.  It _has_ been a few versions since I've taken a good look at it.

-- 
Bill Moran 


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

Re: [GENERAL] PG replication across DataCenters

[Sameer Kumar]

>
> What I don't see streaming working for is DR drills.  I need to, in a
> controlled manner, move the entire application to the secondary datacenter,
> while keeping all the nodes in sync, make sure everything operates properly
> from there (which means allowing database updates), then move it all back
> to the primary datacenter, without losing sync on any slaves (this is a 2T
> database, which I'm sure isn't the largest anyone has dealt with, but it
> means that reseeding slaves is a multi-hour endeavour).  With Slony, these
> drills are easy: a single slonik command relocates the master to the DR
> datacenter while keeping everything in sync, and when testing is complete,
> another slonik command puts everything back the way it was, without any
> data loss and with minimal chance for human error.


I guess I got your point :)
Agree to you now! :)
With v9.3 I think I would be easy to swap the roles for primary and DR. (I
need to test this before I can say for sure).

But still it will be a pain if one needs to shift all the operations to DR
(which is a a valid case, e.g. you would do that for testing the readiness
of your DR site by doing a mock failover) and then shift back to Primary
Site (assuming while operations were going on on DR site, primary site was
kept down purposefully). This will involve taking a backup from DR to
primary and then swapping the roles.
I guess this limitation will be soon waived off. I guess v9.4 or next one
should have this feature (no backups as long as your wal_keep_segment is
high enough to cater to your testing/mock failover window).

I agree slonik and few other utilities/tool around it
administration/management is quite easy.


If you feel that the current implementation of streaming replication is
> able to do that task, then I'll have to move up my timetable to re-evaluate
> it.  It _has_ been a few versions since I've taken a good look at it.


Given your expectation above, v9.3 is a good candidate. But you can afford
to give it a miss.
You must try once v9.4 is out.


Regards
Sameer
Ashnik Pte. Ltd.
Singapore

Re: [GENERAL] Do all Postgres queries touch Shared_Buffers at some point?

[Shiv Sharma]

Thanks.  We are on Greenplum GP 4.2 (Postgres 8.2).  As per GP suggestions,
we have 6 primary/6 mirror instances on each server. The server has 64 G
RAM, and shared_buffers is at ...125 MB :-).  I suppose the idea is for the
OS buffer cache to do the legwork.

But still...performance is at least "not bad".  If all HASH JOIN queries
touch shared_buffers in some way, I find it non-intuitive that we can have
concurrent hash queries involving big tables (100M+ joined with say 100K),
all apparently using the 125MB shared_buffers in some way, and yet giving
reasonable performance.

Basically what is the anatomy of a hash join involving large tables?
Disk->Shared_Buffers->Hash Join areas? something like that?


On Sun, Dec 29, 2013 at 9:18 AM, Michael Paquier
wrote:

> On Sun, Dec 29, 2013 at 9:05 PM, Shiv Sharma 
> wrote:
> > I am puzzled about the extent to which shared_bufferes is used for
> different
> > queries.  Do _all_ queries "touch" shared buffers at some point of their
> > execution?
> >
> > Many of our warehouse queries are  seq_scan followed by HASH.   I know
> > work_mem is assigned for HASH joins:  but does this mean that these
> queries
> > never touch shared buffers at *all* during their execution?  Perhaps they
> > are read into shared_buffers and then passed into work_mem HASH areas???
> >
> > What about updates on big tables? What about inserts  on big tables? What
> > about append-inserts?
> >
> > I think I could get these answers from Explain Analyze Buffers but I am
> on
> > 8.2 :-(
> >
> > Please tell me which queries use/touch shared_buffers in general terms,
> or
> > please point me to documentation.
> All your queries that interacts with relations.
>
> shared_buffers is used for data caching across all the backends of the
> server, to put it simply pages of the relation involved. Such data can
> be relation data, like data of a table you defined yourself, index
> data, or some system catalog data, containing definitions of the
> database objects. So simply everything that is a relation and contains
> physical data might be in shared buffers. Views for example do not
> enter in this category.
>
> You could for example use pg_buffercache to have a look at what
> contains the shared buffers:
> http://www.postgresql.org/docs/devel/static/pgbuffercache.html
>
> Regards,
> --
> Michael
>

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev  wrote:

> On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk  wrote:
> > I'm running Postgresql 9.3. I have a streaming replication server.
> Someone
> > was running a long COPY query (8 hours) on the standby which halted
> > replication. The replication stopped at 3:30 am. I canceled the
> long-running
> > query at 9:30 am and replication data started catching up.
>
> What do you mean by "COPY on the standby halted replication"?
>

If I run "COPY (select * from complicate_view) to stdout" on the standby,
I've noticed that sometimes halts replication updates to the slave.

For example, that's happening right now and "now() -
pg_last_xact_replay_timestamp()" is 22 minutes. There's many transactions
per second being committed on the master. Once that query is canceled, the
slave catches up immediately.

Joe

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk  wrote:
> On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev  wrote:
>>
>> On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk  wrote:
>> > I'm running Postgresql 9.3. I have a streaming replication server.
>> > Someone
>> > was running a long COPY query (8 hours) on the standby which halted
>> > replication. The replication stopped at 3:30 am. I canceled the
>> > long-running
>> > query at 9:30 am and replication data started catching up.
>>
>> What do you mean by "COPY on the standby halted replication"?
>
> If I run "COPY (select * from complicate_view) to stdout" on the standby,
> I've noticed that sometimes halts replication updates to the slave.
>
> For example, that's happening right now and "now() -
> pg_last_xact_replay_timestamp()" is 22 minutes. There's many transactions
> per second being committed on the master. Once that query is canceled, the
> slave catches up immediately.

And what

\x
select * from pg_stat_repication;

shows?

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


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