Thread: 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
Attachment
On Tue, Mar 3, 2015 at 7:49 AM, Andres Freund <andres@2ndquadrant.com> wrote:
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...
Couldn't we just arbitrarily exclude sequence internal states from the comparison?
That wouldn't work where the standby has been promoted and then used in a way that draws on the sequence (with the same workload being put through the now-promoted standby and the original-master), though, but I don't think that that was what you were asking about.
How many similar issues have you seen?
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).
Cheers,
Jeff
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