On Fri, Jun 12, 2020 at 11:20 AM Masahiko Sawada <masahiko.saw...@2ndquadrant.com> wrote: > > On Fri, 12 Jun 2020 at 12:21, Amit Kapila <amit.kapil...@gmail.com> wrote: > > > > > 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. >
Yeah, I think we might not be able to get exact measure but if we divide total_size spilled by exec_count, we will get some rough idea of what should be the logical_decoding_work_mem for that particular session. For ex. consider the logical_decoding_work_mem is 100bytes for a particular backend and the size spilled by that backend is 100 then I think you can roughly keep it to 200bytes if you want to avoid spilling. Similarly one can compute its average value over multiple executions. Does this make sense to you? > > 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. > I feel till the lifetime of a process if we want to display the values at process level but I am open to hear others (including yours) views on this. > The view > will have the following columns for example? > > * pid > * slot_name > * spill_txns > * spill_count > * spill_bytes > * exec_count > Yeah, these appear to be what I have in mind. Note that we can have multiple entries of the same pid here because of slotname, there is some value to display slotname but I am not completely sure if that is a good idea but I am fine if you have a reason to include slotname? -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
