On Fri, 12 Jun 2020 at 12:21, Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Thu, Jun 11, 2020 at 7:39 PM Masahiko Sawada > <masahiko.saw...@2ndquadrant.com> wrote: > > > > On Thu, 11 Jun 2020 at 20:02, Amit Kapila <amit.kapil...@gmail.com> wrote: > > > > > > On Thu, Jun 11, 2020 at 3:07 PM Masahiko Sawada > > > <masahiko.saw...@2ndquadrant.com> wrote: > > > > > > > > On Thu, 11 Jun 2020 at 18:11, Amit Kapila <amit.kapil...@gmail.com> > > > > wrote: > > > > > > > > > > On Thu, Jun 11, 2020 at 1:46 PM Masahiko Sawada > > > > > <masahiko.saw...@2ndquadrant.com> wrote: > > > > > > > > > > > > On Thu, 11 Jun 2020 at 12:30, Amit Kapila <amit.kapil...@gmail.com> > > > > > > wrote: > > > > > > > > > > > > > > > > > > > > > Now, thinking about this again, I am not sure if these stats are > > > > > > > directly related to slots. These are stats for logical decoding > > > > > > > which > > > > > > > can be performed either via WALSender or decoding plugin (via > > > > > > > APIs). > > > > > > > So, why not have them displayed in a new view like > > > > > > > pg_stat_logical (or > > > > > > > pg_stat_logical_decoding/pg_stat_logical_replication)? In > > > > > > > future, we > > > > > > > will need to add similar stats for streaming of in-progress > > > > > > > transactions as well (see patch > > > > > > > 0007-Track-statistics-for-streaming at > > > > > > > [1]), so having a separate view for these doesn't sound illogical. > > > > > > > > > > > > > > > > > > > I think we need to decide how long we want to remain these > > > > > > statistics > > > > > > values. That is, if we were to have such pg_stat_logical view, these > > > > > > values would remain until logical decoding finished since I think > > > > > > the > > > > > > view would display only running logical decoding. OTOH, if we were > > > > > > to > > > > > > correspond these stats to slots, these values would remain beyond > > > > > > multiple logical decoding SQL API calls. > > > > > > > > > > > > > > > > I thought of having these till the process that performs these > > > > > operations exist. So for WALSender, the stats will be valid till it > > > > > is not restarted due to some reason or when performed via backend, the > > > > > stats will be valid till the corresponding backend exits. > > > > > > > > > > > > > The number of rows of that view could be up to (max_backends + > > > > max_wal_senders). Is that right? What if different backends used the > > > > same replication slot one after the other? > > > > > > > > > > Yeah, it would be tricky if multiple slots are used by the same > > > backend. We could probably track the number of times decoding has > > > happened by the session that will probably help us in averaging the > > > spill amount. If we think that the aim is to help users to tune > > > logical_decoding_work_mem to avoid frequent spilling or streaming then > > > how would maintaining at slot level will help? > > > > Since the logical decoding intermediate files are written at per slots > > directory, I thought that corresponding these statistics to > > replication slots is also understandable for users. > > > > What I wanted to know is how will it help users to tune > logical_decoding_work_mem? Different backends can process from the > same slot, so it is not clear how user will be able to make any > meaning out of those stats.
I thought that the user needs to constantly monitor them during one process is executing logical decoding and to see the increments. I might not fully understand but I guess the same is true for displaying them w.r.t. process. Since a process can do logical decoding several times using the same slot with a different setting, the user will need to monitor them several times. > OTOH, it is easier to see how to make > meaning of these stats if we display them w.r.t process. Basically, > we have spill_count and spill_size which can be used to tune > logical_decoding_work_mem and also the activity of spilling happens at > process level, so it sounds like one-to-one mapping. Displaying them w.r.t process also seems a good idea but I'm still unclear what to display and how long these values are valid. The view will have the following columns for example? * pid * slot_name * spill_txns * spill_count * spill_bytes * exec_count Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
