Re: descriptions of pg_stat_user_functions and pg_stat_slru
On 2020/05/27 12:17, Fujii Masao wrote: On 2020/05/25 14:23, Fujii Masao wrote: On 2020/05/22 22:35, Fujii Masao wrote: On 2020/05/21 4:53, Tom Lane wrote: Fujii Masao writes: On 2020/05/20 22:32, Tom Lane wrote: OK by me --- that, too, would be more like the existing catalogs chapter. Yeah, so I'd like to propose the attached patch. Hmmm ... I'm not exactly convinced about sticking xreflabels onto the s as you've done here. Presumably that would make s render like "pg_stat_slru" not "Section 27.2.3", which I think is not consistent with our practice elsewhere. I'd be inclined to leave the id attributes on the s, and add xreflabels there if we want them. I see that catalogs.sgml doesn't really match either of those approaches, though. Not sure if we want to change it. It looks like people have tended to use to substitute text for xref's to the catalog sections, so maybe it would be better to add xreflabels there too and simplify the references. Yeah, since I think that using is simpler than , I added xlabel in . But if we don't do that for the consistency with catalog.sgml, I think that there are two approaches. (1) Replace with when referencing to the monitoring views docs. For example, add and replace with . (2) Leave as it is. In this case, for example, references to the table of pg_stat_replication instead of the section. I prefer (1) because it's better to reference to the section rather than the table. There are thirty for monitoring views in the docs and they need to be updated. Attached (monitoring_docfix_v2.patch) is the patch for (1). Barring any objection, I will commit this patch. Pushed. Thanks! Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
Re: descriptions of pg_stat_user_functions and pg_stat_slru
On 2020/05/22 22:35, Fujii Masao wrote: On 2020/05/21 4:53, Tom Lane wrote: Fujii Masao writes: On 2020/05/20 22:32, Tom Lane wrote: OK by me --- that, too, would be more like the existing catalogs chapter. Yeah, so I'd like to propose the attached patch. Hmmm ... I'm not exactly convinced about sticking xreflabels onto the s as you've done here. Presumably that would make s render like "pg_stat_slru" not "Section 27.2.3", which I think is not consistent with our practice elsewhere. I'd be inclined to leave the id attributes on the s, and add xreflabels there if we want them. I see that catalogs.sgml doesn't really match either of those approaches, though. Not sure if we want to change it. It looks like people have tended to use to substitute text for xref's to the catalog sections, so maybe it would be better to add xreflabels there too and simplify the references. Yeah, since I think that using is simpler than , I added xlabel in . But if we don't do that for the consistency with catalog.sgml, I think that there are two approaches. (1) Replace with when referencing to the monitoring views docs. For example, add and replace with . (2) Leave as it is. In this case, for example, references to the table of pg_stat_replication instead of the section. I prefer (1) because it's better to reference to the section rather than the table. There are thirty for monitoring views in the docs and they need to be updated. Attached (monitoring_docfix_v2.patch) is the patch for (1). I also attached the patch (update_release_note_for_monitoring_stats_views_v1.patch) that updates the tags referencing to pg_stat_xxx views in release note. I will propose this to Bruce if we can agree with the changes in monitoring.sgml. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9851ef2713..632e6d23f7 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -10455,8 +10455,9 @@ SCRAM-SHA-256$iteration count: The pid column can be joined to the - pid column of the pg_stat_activity + pid column of the + + pg_stat_activity view to get more information on the session holding or awaiting each lock, for example diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a2694e548a..4eef970d41 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3889,9 +3889,9 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows The synchronous standbys will be those whose names appear in this list, and that are both currently connected and streaming data in real-time -(as shown by a state of streaming in the pg_stat_replication -view). +(as shown by a state of streaming in the + +pg_stat_replication view). Specifying more than one synchronous standby can allow for very high availability and protection against data loss. @@ -4244,8 +4244,8 @@ ANY num_sync ( pg_stat_replication + + pg_stat_replication view. The standby will report the last write-ahead log location it has written, the last position it has flushed to disk, and the last position it has applied. @@ -6694,8 +6694,10 @@ local0.*/var/log/postgresql The backend type corresponds to the column - backend_type in the view , but additional types can appear + backend_type in the view + + pg_stat_activity, + but additional types can appear in the log that don't show in that view. @@ -7191,7 +7193,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; platforms. You can use the tool to measure the overhead of timing on your system. I/O timing information is -displayed in , in the output of +displayed in +pg_stat_database, in the output of when the BUFFERS option is used, and by . Only superusers can change this setting. diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 805e100055..65c3fc62a9 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -895,7 +895,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' You can retrieve a list of WAL sender processes via the - view. Large differences between + + pg_stat_replication view. Large differences between pg_current_wal_lsn and the view's sent_lsn field might indicate that the master server is under heavy load, while differences between sent_lsn and @@ -904,7 +905,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
Re: descriptions of pg_stat_user_functions and pg_stat_slru
On 2020/05/21 4:53, Tom Lane wrote: Fujii Masao writes: On 2020/05/20 22:32, Tom Lane wrote: OK by me --- that, too, would be more like the existing catalogs chapter. Yeah, so I'd like to propose the attached patch. Hmmm ... I'm not exactly convinced about sticking xreflabels onto the s as you've done here. Presumably that would make s render like "pg_stat_slru" not "Section 27.2.3", which I think is not consistent with our practice elsewhere. I'd be inclined to leave the id attributes on the s, and add xreflabels there if we want them. I see that catalogs.sgml doesn't really match either of those approaches, though. Not sure if we want to change it. It looks like people have tended to use to substitute text for xref's to the catalog sections, so maybe it would be better to add xreflabels there too and simplify the references. Yeah, since I think that using is simpler than , I added xlabel in . But if we don't do that for the consistency with catalog.sgml, I think that there are two approaches. (1) Replace with when referencing to the monitoring views docs. For example, add and replace with . (2) Leave as it is. In this case, for example, references to the table of pg_stat_replication instead of the section. I prefer (1) because it's better to reference to the section rather than the table. There are thirty for monitoring views in the docs and they need to be updated. Other than that markup quibble, this looks fine to me. Thanks for the review! Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
Re: descriptions of pg_stat_user_functions and pg_stat_slru
Fujii Masao writes: > On 2020/05/20 22:32, Tom Lane wrote: >> OK by me --- that, too, would be more like the existing catalogs >> chapter. > Yeah, so I'd like to propose the attached patch. Hmmm ... I'm not exactly convinced about sticking xreflabels onto the s as you've done here. Presumably that would make s render like "pg_stat_slru" not "Section 27.2.3", which I think is not consistent with our practice elsewhere. I'd be inclined to leave the id attributes on the s, and add xreflabels there if we want them. I see that catalogs.sgml doesn't really match either of those approaches, though. Not sure if we want to change it. It looks like people have tended to use to substitute text for xref's to the catalog sections, so maybe it would be better to add xreflabels there too and simplify the references. Other than that markup quibble, this looks fine to me. > - 6644 | LWLock | ProcArrayLock > + 6644 | LWLock | ProcArray > I found "ProcArrayLock" is still used in monitoring.sgml though > it was renamed to ProcArray. So the patch also includes the above change. Ooops, my oversight. regards, tom lane
Re: descriptions of pg_stat_user_functions and pg_stat_slru
On 2020/05/20 22:32, Tom Lane wrote: Fujii Masao writes: Also I don't like that all the stats views are packed in one section currently. Which makes the docs difficult to read, I'm afraid. Thought? If we change the layout entirely, at the same time, what about separating each view into different section by adding ? OK by me --- that, too, would be more like the existing catalogs chapter. Yeah, so I'd like to propose the attached patch. - 6644 | LWLock | ProcArrayLock + 6644 | LWLock | ProcArray I found "ProcArrayLock" is still used in monitoring.sgml though it was renamed to ProcArray. So the patch also includes the above change. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index acc6e2bc31..41ac5d2b67 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -608,8 +608,22 @@ postgres 27093 0.0 0.0 30096 2752 ?Ss 11:34 0:00 postgres: ser into the kernel's handling of I/O. + - + + pg_stat_activity + + + pg_stat_activity + + + + The pg_stat_activity view will have one row + per server process, showing information related to + the current activity of that process. + + + pg_stat_activity View @@ -889,12 +903,6 @@ postgres 27093 0.0 0.0 30096 2752 ?Ss 11:34 0:00 postgres: ser - - The pg_stat_activity view will have one row - per server process, showing information related to - the current activity of that process. - - The wait_event and state columns are @@ -2135,15 +2143,31 @@ postgres 27093 0.0 0.0 30096 2752 ?Ss 11:34 0:00 postgres: ser SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL; - pid | wait_event_type | wait_event ---+-+--- + pid | wait_event_type | wait_event +--+-+ 2540 | Lock| relation - 6644 | LWLock | ProcArrayLock + 6644 | LWLock | ProcArray (2 rows) - + + + + pg_stat_replication + + + pg_stat_replication + + + + The pg_stat_replication view will contain one row + per WAL sender process, showing statistics about replication to that + sender's connected standby server. Only directly connected standbys are + listed; no information is available about downstream standby servers. + + + pg_stat_replication View @@ -2457,13 +2481,6 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i - - The pg_stat_replication view will contain one row - per WAL sender process, showing statistics about replication to that - sender's connected standby server. Only directly connected standbys are - listed; no information is available about downstream standby servers. - - The lag times reported in the pg_stat_replication view are measurements of the time taken for recent WAL to be written, @@ -2503,7 +2520,22 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i - + + + + pg_stat_wal_receiver + + + pg_stat_wal_receiver + + + + The pg_stat_wal_receiver view will contain only + one row, showing statistics about the WAL receiver from that receiver's + connected server. + + + pg_stat_wal_receiver View @@ -2668,13 +2700,23 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + + + pg_stat_subscription + + + pg_stat_subscription + + - The pg_stat_wal_receiver view will contain only - one row, showing statistics about the WAL receiver from that receiver's - connected server. + The pg_stat_subscription view will contain one + row per subscription for main worker (with null PID if the worker is + not running), and additional rows for workers handling the initial data + copy of the subscribed tables. - + pg_stat_subscription View @@ -2776,14 +2818,25 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + + + pg_stat_ssl + + + pg_stat_ssl + + - The pg_stat_subscription view will contain one - row per subscription for main worker (with null PID if the worker is - not running), and additional rows for workers handling the initial data - copy of the subscribed tables. + The pg_stat_ssl view will contain one row per + backend or WAL sender process, showing statistics about SSL usage on + this connection. It can be joined to pg_stat_activity + or pg_stat_replication on the + pid column to get more details about the + connection. - + pg_stat_ssl View @@ -2896,16 +2949,25 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
Re: descriptions of pg_stat_user_functions and pg_stat_slru
Fujii Masao writes: > Also I don't like that all the stats views are packed in one section > currently. Which makes the docs difficult to read, I'm afraid. Thought? > If we change the layout entirely, at the same time, what about separating > each view into different section by adding ? OK by me --- that, too, would be more like the existing catalogs chapter. regards, tom lane
Re: descriptions of pg_stat_user_functions and pg_stat_slru
On 2020/05/20 11:05, Tom Lane wrote: Fujii Masao writes: In monitoring.sgml, there are the tables and corresponding descriptions for pg_stat_user_functions and pg_stat_slru views. I found that those descriptions are located in opposite places. For example, the description for pg_stat_user_functions wrongly comes just after the table for pg_stat_slru view. Attached patch fixes this issue. Hm, I see your point, but I think there is a bigger problem here: this entire section was written with great disregard for our normal conventions of how to lay out text around tables. The standard thing, as seen in Chapter 9 or the system catalog section, is that first you have an introductory paragraph --- preferably containing an actual link to the table --- then you have the table, and then if you feel a need for any footnote-like comments on the table, you put those below it. Yeah, I agree to change the layout that way. Also I don't like that all the stats views are packed in one section currently. Which makes the docs difficult to read, I'm afraid. Thought? If we change the layout entirely, at the same time, what about separating each view into different section by adding ? Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
Re: descriptions of pg_stat_user_functions and pg_stat_slru
Fujii Masao writes: > In monitoring.sgml, there are the tables and corresponding descriptions > for pg_stat_user_functions and pg_stat_slru views. I found that those > descriptions are located in opposite places. For example, the description > for pg_stat_user_functions wrongly comes just after the table for > pg_stat_slru view. Attached patch fixes this issue. Hm, I see your point, but I think there is a bigger problem here: this entire section was written with great disregard for our normal conventions of how to lay out text around tables. The standard thing, as seen in Chapter 9 or the system catalog section, is that first you have an introductory paragraph --- preferably containing an actual link to the table --- then you have the table, and then if you feel a need for any footnote-like comments on the table, you put those below it. I seem to recall Peter E. explaining that this is important because some presentations might put the table out-of-line entirely, so you should have an to it. What we've mostly got in 27.2 as it stands is that the explanations are below the tables, which is just backwards. I think the discrepancy you spotted is because I added some text about the pg_stat_slru view and just automatically added it above the table, not noticing that that wasn't what the surrounding stuff did. regards, tom lane
descriptions of pg_stat_user_functions and pg_stat_slru
Hi, In monitoring.sgml, there are the tables and corresponding descriptions for pg_stat_user_functions and pg_stat_slru views. I found that those descriptions are located in opposite places. For example, the description for pg_stat_user_functions wrongly comes just after the table for pg_stat_slru view. Attached patch fixes this issue. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index acc6e2bc31..4810c4a0f1 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4249,16 +4249,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i - - SLRU - - - PostgreSQL accesses certain on-disk information - via SLRU (simple least-recently-used) caches. - The pg_stat_slru view will contain - one row for each tracked SLRU cache, showing statistics about access - to cached pages. + The pg_stat_user_functions view will contain + one row for each tracked function, showing statistics about executions of + that function. The parameter + controls exactly which functions are tracked. @@ -4362,11 +4357,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + SLRU + + - The pg_stat_user_functions view will contain - one row for each tracked function, showing statistics about executions of - that function. The parameter - controls exactly which functions are tracked. + PostgreSQL accesses certain on-disk information + via SLRU (simple least-recently-used) caches. + The pg_stat_slru view will contain + one row for each tracked SLRU cache, showing statistics about access + to cached pages.
