Обсуждение: Comparing primary/HS standby in tests

Hi,

I've regularly wished we had automated tests that setup HS and then
compare primary/standby at the end to verify replay worked
correctly.

Heikki's page comparison tools deals with some of that verification, but
it's really quite expensive and doesn't care about runtime only
differences. I.e. it doesn't test HS at all.

I every now and then run installcheck against a primary, verify that
replay works without errors, and then compare pg_dumpall from both
clusters. Unfortunately that currently requires hand inspection of
dumps, there are differences like:
-SELECT pg_catalog.setval('default_seq', 1, true);
+SELECT pg_catalog.setval('default_seq', 33, true);

The reason these differences is that the primary increases the
sequence's last_value by 1, but temporarily sets it to +SEQ_LOG_VALS
before XLogInsert(). So the two differ.

Does anybody have a good idea how to get rid of that difference? One way
to do that would be to log the value the standby is sure to have - but
that's not entirely trivial.

I'd very much like to add a automated test like this to the tree, but I
don't see wa way to do that sanely without a comparison tool...

Greetings,

Andres Freund

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

On 03/03/2015 07:49 AM, Andres Freund wrote:
> I'd very much like to add a automated test like this to the tree, but I
> don't see wa way to do that sanely without a comparison tool...

We could use a comparison tool anyway.  Baron Schwartz was pointing out
that Percona has a comparison tool for MySQL, and the amount of "drift"
and corruption that they find in a large replication cluster is
generally pretty alarming, and *always* present.  While our replication
isn't as flaky as MySQL's, networks are often lossy or corrupt.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Wed, Mar 4, 2015 at 12:49 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> I every now and then run installcheck against a primary, verify that
> replay works without errors, and then compare pg_dumpall from both
> clusters. Unfortunately that currently requires hand inspection of
> dumps, there are differences like:
> -SELECT pg_catalog.setval('default_seq', 1, true);
> +SELECT pg_catalog.setval('default_seq', 33, true);
> Does anybody have a good idea how to get rid of that difference? One way
> to do that would be to log the value the standby is sure to have - but
> that's not entirely trivial.

SEQ_LOG_VALS has been added some time ago, so perhaps time have
changed and we could live without it:
commit: 741510521caea7e1ca12b4db0701bbc2db346a5f
author: Vadim B. Mikheev <vadim4o@yahoo.com>
date: Thu, 30 Nov 2000 01:47:33 +0000
XLOG stuff for sequences.
CommitDelay in guc.c

However performance is really a problem, for example with the patch
attached and the following test case:
DO $$DECLARE count integer; count2 integer;
begin
for count in 1 .. 1000000
loop
select nextval('toto') into count2;
end loop;
END$$;

Patched, this takes 9.5ms and generates 191 MB of WAL on my laptop.
With master unpatched, this generates 6MB of WAL (records are divided
by 32) and takes 7.5s.

There are a couple of other possibilities we could consider as well:
1) Trick pg_dump such as it does not dump the current value of master
but one consistent with what a standby would expect. We would need
then something like nextval_standby() or similar.
2) Filter out lines with pg_catalog.setval in a home-made wrapper.

> I'd very much like to add a automated test like this to the tree, but I
> don't see a way to do that sanely without a comparison tool...

That's definitely worth having IMO.

Regards,
--
Michael

On 2015-03-04 08:41:23 -0800, Jeff Janes wrote:
> Couldn't we just arbitrarily exclude sequence internal states from the
> comparison?

Not sure what you mean? You mean just not dump them? I guess we could by
editing the contents of a custom format dump? A bit annoying to have a
script doing that...

> How many similar issues have you seen?

That's usually the only difference.

> In the case where you have a promoted replica and put the same through
> workflow through both it and the master, I've seen "pg_dump -s" dump
> objects in different orders, for no apparent reason.  That is kind of
> annoying, but I never traced it back to the cause (nor have I excluded
> PEBCAK as the real cause).

I'm not surprised. Independent runs - which you seem to be describing -
are quite dependent on on-disk order, and effects of concurrency. Oids
get assigned in different orders and such.

Greetings,

Andres Freund

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

Hi,

On 2015-03-03 16:49:22 +0100, Andres Freund wrote:
> I every now and then run installcheck against a primary, verify that
> replay works without errors, and then compare pg_dumpall from both
> clusters. Unfortunately that currently requires hand inspection of
> dumps, there are differences like:
> -SELECT pg_catalog.setval('default_seq', 1, true);
> +SELECT pg_catalog.setval('default_seq', 33, true);
> 
> The reason these differences is that the primary increases the
> sequence's last_value by 1, but temporarily sets it to +SEQ_LOG_VALS
> before XLogInsert(). So the two differ.
> 
> Does anybody have a good idea how to get rid of that difference? One way
> to do that would be to log the value the standby is sure to have - but
> that's not entirely trivial.

I found a way that's actually fairly simple. On the primary call nextval
often enough to use up all the cached values. The below query does so:

DO $$
DECLARE
    s regclass;
BEGIN
    FOR s IN SELECT oid::regclass FROM pg_class WHERE relkind = 'S' LOOP
        EXECUTE format($s$SELECT nextval(%s), generate_series(1, log_cnt) FROM %s;$s$, s::oid, s::text);
    END LOOP;
END;$$;

After that dumps on master generate the same dump on primary / standby
for me, after running a regression test.

Greetings,

Andres Freund