Internal statistics of the database are the second source of the system health information, next to the logs. The combination of two data sources (logs and statistics) provides access to the full picture of the cluster operations.
The standard implementation of the statistics collector built into PostgreSQL is unnatural and troublesome to use. Therefore, EuroDB has been equipped with an efficient database analysis module based on the statistics collector built into the PostgreSQL engine. The statistics analyzer provided by EuroDB generates readable reports ready for use by administrators, developers, and business departments.
Collected statistics allow for verification of the status of the cluster in the selected dates and date ranges. Average and peak values as well as activity trends are returned. An important advantage of the analyzer is the ability to compare data from selected periods. It is an invaluable help in the analysis of all types of irregularities.
For the monthly summary, statistics include:
- Block reads
- Cache hits
- User inserts
- User updates
- User deletes
- Sequential scans
- Indexed scans.
For each of these groups, the report includes:
- Change from the previous period
- Peak value
- Peak occurrence time (with drill-down capability)
- Daily average
- Hourly average
- Minute average
- Second average.
Example of a tool application:
- After deploying the new version of the application, DBA observed a significant increase in sequential scans. By comparing the analogous periods, it was found that the change in the number of scans occurs only after the deployment date. Correlation of these data with data from the log analysis module led to the detection of a suboptimal query. Its correction resulted in a reduction of the system maintenance costs.
- By reviewing the last week statistics, DBA observed a significant increase in the number of blocks read. The analysis of data from the statistics report enabled to identify an employee who abused their permissions and retrieved too much data from the database.
- When observing the weekly report, DBA noticed a change in the number of failed transactions. Further analysis enabled to identify incorrectly implemented integration, which generated the aforementioned rollbacks.