RE: [Proposal] Add accumulated statistics - Mailing list pgsql-hackers

Hi all,

I think sampling like Oracle ASH should work for the DBA to find probable bottlenecks in many cases (, so I hope
PostgreSQLwill incorporate it...)  On the other hand, it seems to have the following disadvantages, some of which
othershave already pointed out:
 

1. Doesn't provide precise data
Sampling could miss intermittent short waits, e.g., buffer content lock waits during checkpoints.  This might make it
difficultor impossible to solve transient performance problems, such as infrequent 100 millisecond response times while
thenormal response time is a few milliseconds.
 
The proposed wait event collection doesn't miss anything.

2. Overuses resources
We may be able to shorten the sampling interval to 10 ms or even 1 ms to detect short periods of problems.  However,
thesampled data of active sessions become voluminous in memory and storage.  It would take longer to analyze those
samples. Also, the background sampling process prevents the CPU core from becoming idle to save power, which bgwriter
andwalwriter tries to avoid by hibernation.
 
The proposed wait event collection just records what actually happened.  No waste.  Would it use many resources if
waitshappen frequently?  That leads to our motivation to reduce waits.
 

3. Cannot determine the impact or illness of waits just by sampling or counting without time
As the following MySQL and Oracle manual articles describe, precise measurement of wait count and time helps to judge
theimpact and justify the remedy.  They can measure the whole SQL execution and its various processing steps (parse,
plan,sort, etc.) as well as waits, so that the most significant areas can be determined.
 
Also, sampling cannot tell if a single wait took long or the same waits occurred repeatedly in succession (at least
easily.) Do the sampled waits indicate an abnormal I/O (which took 2 ms while the normal time is 50 us)?
 


[MySQL]

Chapter 26 MySQL Performance Schema
https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html
--------------------------------------------------
The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been
instrumentedso that timing information can be collected. In general, an event could be a function call, a wait for the
operatingsystem, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of
statements.Event collection provides access to information about synchronization calls (such as for mutexes) file and
tableI/O, table locks, and so forth for the server and for several storage engines.
 

Current events are available, as well as event histories and summaries. This enables you to determine how many times
instrumentedactivities were performed and how much time they took. Event information is available to show the
activitiesof specific threads, or activity associated with particular objects such as a mutex or file.
 
--------------------------------------------------


[Oracle]


https://docs.oracle.com/en/database/oracle/oracle-database/18/tdppt/automatic-database-performance-monitoring.html#GUID-32E92AEC-AF1A-4602-B998-3250920CD3BE
--------------------------------------------------
The goal of database performance tuning is to reduce the DB time of the system for a given workload. By reducing DB
time,the database can support more user requests by using the same or fewer resources. ADDM reports system resources
thatare using a significant portion of DB time as problem areas and sorts them in descending order by the amount of
relatedDB time spent.
 
--------------------------------------------------


Instance Tuning Using Performance Views

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/instance-tuning-using-performance-views.html#GUID-07982549-507F-4465-8843-7F753BCF8F99
--------------------------------------------------
Wait event statistics include the number of times an event was waited for and the time waited for the event to
complete.If the initialization parameter TIMED_STATISTICS is set to true, then you can also see how long each resource
waswaited for.
 
To minimize user response time, reduce the time spent by server processes waiting for event completion. Not all wait
eventshave the same wait time. Therefore, it is more important to examine events with the most total time waited rather
thanwait events with a high number of occurrences. Usually, it is best to set the dynamic parameter TIMED_STATISTICS to
trueat least while monitoring performance.
 
--------------------------------------------------



https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/measuring-database-performance.html#GUID-811E9E65-C64A-4028-A90E-102BBFF6E68F
5.2.3 Using Wait Events without Timed Statistics
--------------------------------------------------
If TIMED_STATISTICS is set to FALSE, then the amount of time spent waiting for an event is not available. Therefore, it
isonly possible to order wait events by the number of times each event was waited for. Although the events with the
largestnumber of waits might indicate a potential bottleneck, they might not be the main bottleneck. This situation can
happenwhen an event is waited for a large number of times, but the total time waited for that event is small.
Conversely,an event with fewer waits might be a bigger bottleneck if the wait time accounts for a significant
proportionof the total wait time. Without the wait times to use for comparison, it is difficult to determine whether a
waitevent is worth investigating.
 
--------------------------------------------------


10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
--------------------------------------------------
The most effective way to use wait event data is to order the events by the wait time. This is only possible if
TIMED_STATISTICSis set to true. Otherwise, the wait events can only be ranked by the number of times waited, which is
oftennot the ordering that best represents the problem.
 

To get an indication of where time is spent, follow these steps:

1. Examine the data collection for V$SYSTEM_EVENT. The events of interest should be ranked by wait time.
Identify the wait events that have the most significant percentage of wait time. ...
Alternatively, look at the Top 5 Timed Events section at the beginning of the Automatic Workload Repository report.
Thissection automatically orders the wait events (omitting idle events), and calculates the relative percentage:
 

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                % Total
Event                                         Waits    Time (s) Call Time
-------------------------------------- ------------ ----------- ---------
CPU time                                                    559     88.80
log file parallel write                       2,181          28      4.42
SQL*Net more data from client               516,611          27      4.24
db file parallel write                       13,383          13      2.04
db file sequential read                         563           2       .27

2. Look at the number of waits for these events, and the average wait time. For example, for I/O related events, the
averagetime might help identify whether the I/O system is slow. The following example of this data is taken from the
WaitEvent section of the AWR report:
 

                                                             Avg
                                                Total Wait   wait     Waits
Event                           Waits  Timeouts   Time (s)   (ms)      /txn
--------------------------- --------- --------- ---------- ------ ---------
log file parallel write         2,181         0         28     13      41.2
SQL*Net more data from clie   516,611         0         27      0   9,747.4
db file parallel write         13,383         0         13      1     252.5

3. The top wait events identify the next places to investigate. A table of common wait events is listed in Table 10-1.
Itis usually a good idea to also have quick look at high-load SQL. 

4. Examine the related data indicated by the wait events to see what other information this data provides. Determine
whetherthis information is consistent with the wait event data. In most situations, there is enough data to begin
developinga theory about the potential causes of the performance bottleneck.
 

5. To determine whether this theory is valid, cross-check data you have examined with other statistics available for
consistency.The appropriate statistics vary depending on the problem, but usually include load profile-related data in
V$SYSSTAT,operating system statistics, and so on. Perform cross-checks with other data to confirm or refute the
developingtheory.
 
--------------------------------------------------



So, why don't we have the proposed wait event count/time data?  I hope we can nurture this to become a database
profilingtool like MySQL and Oracle.  This is the first step.  I think it would be useful to have both sampling and
precisestatistics.  Oracle has both, and MySQL has the latter (I don't know why MySQL doesn't provide sampling, because
thePerformance Schema should probably have been developed after Oracle's ASH.)
 

What would make us conservative about doing this?  Skimming the old thread, the remaining concern is the timer
overhead. As the following article suggests, some lightweight timers seem to be available.  We can turn the timing off
bydefault if they aren't light enough.
 


Performance Schema Timers
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-timing.html


Regards
Takayuki Tsunakawa