From: | "Erik Rijkers" <er(at)xs4all(dot)nl> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | master and sync-replica diverging |
Date: | 2012-05-17 09:33:50 |
Message-ID: | 879c61028d9ed551eb2da6fe93fa2616.squirrel@webmail.xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
AMD FX 8120 / centos 6.2 / latest source (git head)
It seems to be quite easy to force a 'sync' replica to not be equal to master by
recreating+loading a table in a while loop.
For this test I compiled+checked+installed three separate instances on the same machine. The
replica application_name are names 'wal_receiver_$copy' where $copy is 01, resp. 02.
$ ./sync_state.sh
pid | application_name | state | sync_state
-------+------------------+-----------+------------
19520 | wal_receiver_01 | streaming | sync
19567 | wal_receiver_02 | streaming | async
(2 rows)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------
6564 | on | wal_receiver_01
(1 row)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------
6565 | off |
(1 row)
port | synchronous_commit | synchronous_standby_names
------+--------------------+---------------------------
6566 | off |
(1 row)
The test consists of creating a table and loading tab-separated data from file with COPY and then
taking the rowcount of that table (13 MB, almost 200k rows) in all three instances:
# wget http://flybase.org/static_pages/downloads/FB2012_03/genes/fbgn_annotation_ID_fb_2012_03.tsv.gz
slurp_file=fbgn_annotation_ID_fb_2012_03.tsv.gz
zcat $slurp_file \
| grep -v '^#' \
| grep -Ev '^[[:space:]]*$' \
| psql -c "
drop table if exists $table cascade;
create table $table (
gene_symbol text
, primary_fbgn text
, secondary_fbgns text
, annotation_id text
, secondary_annotation_ids text
);
copy $table from stdin csv delimiter E'\t';
";
# count on master:
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6564
# count on wal_receiver_01 (sync replica):
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6565
# count on wal_receiver_02 (async replica):
echo "select current_setting('port') port,count(*) from $table"|psql -qtXp 6566
I expected the rowcounts from master and sync replica to always be the same.
Initially this seemed to be the case, but when I run the above sequence in a while loop for a few
minutes about 10% of rowcounts from the sync-replica are not equal to the master.
Perhaps not a likely scenario, but surely such a deviating rowcount on a sync replica should not
be possible?
thank you,
Erik Rijkers
From | Date | Subject | |
---|---|---|---|
Next Message | Ants Aasma | 2012-05-17 09:46:54 | Re: Why is indexonlyscan so darned slow? |
Previous Message | Heikki Linnakangas | 2012-05-17 06:28:39 | Re: counting pallocs |