[GENERAL] Confusing with commit time usage in logical decoding
If you received this message twice, sorry for annoying since I did not
subscribe successfully previously due to conflicting email domain.
Dear postgresql general mailing list,
I am currently using the logical decoding feature (version 9.6 I think
as far as I found in the source, wal_level: logical,
max_replication_slot: > 1, track_commit_timestamp: on, I am not sure
whether this will help or not).
Following the online documentation, everything works fine until I input
SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL,
NULL, 'include-timestamp', 'on');
I always got 1999-12-31 16:00 as the commit time for arbitrary
transactions with DML statements.
After several tries, I realize that the txn->commit_time returned was
always 0.
Could you help me by indicating me what could be wrong in my case? Any
missing parameters set?
Thank you in advance,
Kind Regards,
Weiping
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Confusing with commit time usage in logical decoding
Dear postgresql general mailing list,
I am currently using the logical decoding feature (version 9.6 I think
as far as I found in the source, wal_level: logical,
max_replication_slot: > 1, track_commit_timestamp: on, I am not sure
whether this will help or not).
Following the online documentation, everything works fine until I input
SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL,
'include-timestamp', 'on');
I always got 1999-12-31 16:00 as the commit time for arbitrary
transactions with DML statements.
After several tries, I realize that the txn->commit_time returned was
always 0.
Could you help me by indicating me what could be wrong in my case? Any
missing parameters set?
Thank you in advance,
Kind Regards,
Weiping
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] commit time in logical decoding
Dear postgresql general mailing list,
I am currently using the logical decoding feature (version 9.6 I think
as far as I found in the source, wal_level: logical,
max_replication_slot: > 1, track_commit_timestamp: on, I am not sure
whether this will help or not).
Following the online documentation, everything works fine until I input
SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL,
NULL, 'include-timestamp', 'on');
I always got 1999-12-31 16:00 as the commit time for arbitrary
transactions with DML statements.
After several tries, I realize that the txn->commit_time returned was
always 0.
Could you help me by indicating me what could be wrong in my case? Any
missing parameters set?
Thank you in advance,
Kind Regards,
Weiping
--
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] commit time in logical decoding
Hello Artur,
Thank you for your reply.
Should it work in a stable version like Postgresql 9.4, since it's
enough for me and I don't care whether it's 9.6 or 9.5.
Nevertheless I will try it using 9.4.
Regards,
Weiping
On 01.03.2016 22:04, Artur Zakirov wrote:
Hello, Weiping
It seems that it is a bug. Thank you for report. I guess it will be
fixed soon.
On 01.03.2016 17:36, Weiping Qu wrote:
Dear postgresql general mailing list,
I am currently using the logical decoding feature (version 9.6 I think
as far as I found in the source, wal_level: logical,
max_replication_slot: > 1, track_commit_timestamp: on, I am not sure
whether this will help or not).
Following the online documentation, everything works fine until I input
SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL,
NULL, 'include-timestamp', 'on');
I always got 1999-12-31 16:00 as the commit time for arbitrary
transactions with DML statements.
After several tries, I realize that the txn->commit_time returned was
always 0.
Could you help me by indicating me what could be wrong in my case? Any
missing parameters set?
Thank you in advance,
Kind Regards,
Weiping
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question regarding logical replication
Dear postgresql community, I have a question regarding understanding the implementation logic behind logical replication. Assume a replication slot created on the master node, will more and more data get piled up in the slot and the size of replication slot continuously increase if there is no slave reading/dequeuing data out of this slot or very slowly, thus incurring high I/Os and slow down the transaction throughput? Looking forward to your explanation. Kindly review and please share your comments on this matter. -- 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] Question regarding logical replication
Hi. Thank you very much for such detailed explanation. :) We are currently testing the overhead of log-based Change Data Capture method (i.e. logical decoding) over Postgresql. The test setting consists of one processing running TPC-C on node1, which issued transactions against a database residing on node2, which is accessed by three CDC processes running on three different nodes, say node3-5. The difference between three CDC processes are only different table sets they are interested while the commonness is that each of they would sleep periodically during keeping capturing changes from node2. We always measured the impact of CDC on original TPC-C workload by looking into the transaction throughput on node1. We selected 0ms, 200ms and 400ms as three different sleeping periods for CDC processes to control their impact on node2. We expect that the longer a sleeping period is set for CDC, the less impact is incurred over Postgresql, since less I/Os are triggered to fetch data from xlog. However, the plots showed different trend (currently I don't have plots on my laptop) which shows that the more frequently are the CDC processes reading from logical slots, the less overhead is incurred over PostgreSQL, which leads to higher throughput. That's the reason why I asked the previous question, whether logical slot is implemented as queue. Without continuous dequeuing the "queue" get larger and larger, thus lowering the OLTP workload. Regards; Weiping On 26.10.2017 21:42, Alvaro Aguayo Garcia-Rada wrote: Hi. I've had experience with both BDR & pglogical. For each replication slot, postgres saves a LSN which points to the last xlog entry read by the client. When a client does not reads xlog, for example, if it cannot connect to the server, then the distance between such LSN(pg_replication_slots.restart_lsn) and the current xlog location(pg_current_xlog_insert_location()) will enlarge over the time. Not sure about the following, but postgres will not clear old xlog entries which are still pending to be read on any replication slot. Such situation may also happen, in lower degree, if the client cannot read WAL as fast as it's produced. Anyhow, what will happen is xlog will grow more and more. However, that will probably not impact performance, as xlog is written anyway. But if you don't have enough free space, you could get your partition full of xlog. Regards, Alvaro Aguayo Operations Manager Open Comb Systems E.I.R.L. Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248 Web: www.ocs.pe - Original Message - From: "Weiping Qu" To: "PostgreSql-general" Sent: Thursday, 26 October, 2017 14:07:54 Subject: [GENERAL] Question regarding logical replication Dear postgresql community, I have a question regarding understanding the implementation logic behind logical replication. Assume a replication slot created on the master node, will more and more data get piled up in the slot and the size of replication slot continuously increase if there is no slave reading/dequeuing data out of this slot or very slowly, thus incurring high I/Os and slow down the transaction throughput? Looking forward to your explanation. Kindly review and please share your comments on this matter. -- 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] Question regarding logical replication
That's a good point and we haven't accounted for disk caching. Is there any way to confirm this fact in PostgreSQL? Weiping On 27.10.2017 11:53, Francisco Olarte wrote: On Thu, Oct 26, 2017 at 10:20 PM, Weiping Qu wrote: However, the plots showed different trend (currently I don't have plots on my laptop) which shows that the more frequently are the CDC processes reading from logical slots, the less overhead is incurred over PostgreSQL, which leads to higher throughput. Have you accounted for disk caching? Your CDC may be getting log from the cache when going with little lag but being forced to read from disk (make the server do it ) when it falls behind. Francisco Olarte. -- 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] Question regarding logical replication
Thanks, Francisco. From the plots we got the same feeling, cache reads with little lags and high cache hits really don't put extra burden on the original write throughput for OLTP transactions. And log-based is the most efficient and harm-less one as compared to trigger-based and timestamp based change data capture. Weiping On 27.10.2017 14:03, Francisco Olarte wrote: On Fri, Oct 27, 2017 at 12:04 PM, Weiping Qu wrote: That's a good point and we haven't accounted for disk caching. Is there any way to confirm this fact in PostgreSQL? I doubt, as it names indicates cache should be hidden from the db server. You could monitor the machine with varying lags and see the disk-cache hit ratio , or monitor the throughput loss, a disk-cache effect should exhibit a constant part for little lags, where you mostly do cache reads, then a rising part as you begin reading from disks stabilizing asyntotically ( as most of the fraction of reads comes from disk, but it could also exhibit a jump if you are unlucky and you evict pages you'll need soon ), but it is not a simple thing to measure, specially with a job mix and long delays. The xlog can do strange things. IIRC it is normally write-only ( only used on crash recovery, to archive (ship) it and for log based replication slots ), but postgres recycles segments ( which can have an impact on big memory machines ). I do not know to what extent a modern OS can detect the access pattern and do things like evict the log pages early after sync. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] wal configuration setting for fast streaming replication with logical decoding
Hi, I intend to increase the speed of streaming replication with logical decoding using following configuration: wal_level = logical fsync = on synchronous_commit = off wal_sync_method = fdatasync wal_buffers = 256MB wal_writer_delay = 2seconds checkpoint_timeout = 15min max_wal_size=10GB The intention is to first let WAL records to be buffered in WAL buffers (with increasing wal_buffers as 256MB) by turning off synchronous_commit and increasing the wal_writer_delay to 2 second. Target WAL records are wished to be directly fetched from RAM through streaming replication to external nodes, thus reducing I/Os. Besides, to avoid expensive checkpoints, its timeout and max_wal_size are also increased. However, as suggested online, wal_buffers should be not more than one WAL segment file which is 16MB. and wal_writer_delay should be at millisecond level. Therefore, I would like to listen to your opinions. Besides, I would also like to fetch WAL records periodically (say per 150 ms) which would cause pile-up of WAL records in memory at each wal_writer_delay interval. As also introduced online, when XLogInsertRecord is called, a new record is inserted in to WAL buffers, if no space, then a few WAL records would be moved to kernel cache (buffer cache). Shall I also set vm.dirty_background_ratio = 5 and vm.dirty_ratio = 80 to avoid disk I/Os? Looking forward to your kind help. Best, Weiping -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ways of monitoring logical decoding performance
Dear all, I'd like to monitor the resource utilization of logical decoding (e.g. in version 9.5). For example, I'd like to see the wal buffer hit ratio, i.e. how much reading for logical decoding is from in-memory pages. This can be set by blks_hit/(blks_read+blks_hit) from pg_stat_database. But this values might include numbers incurred by other concurrent sessions. Is there any clear manner to entirely focus on the performance of logical decoding? Regards, Weiping -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
