Internal statistics from the database are the secondary source of system health information, after logs. The combination of the two data sources (logs and statistics) provides access to a complete picture of the cluster operations.
The standard implementation of the statistics collector built into PostgreSQL is illogical and troublesome to use. Therefore, EuroDB has been equipped with an efficient rational database analysis module based on the statistics collector built into the PostgreSQL engine. The statistics analyzer provided by EuroDB generates consise reports ready for use by administrators, developers, and business departments.
Collected statistics allow for the status verification of the cluster on 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 of 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, Database Administrator (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 the reduction of the system maintenance costs.
By reviewing last week statistics, DBA observed a significant increase in the number of blocks read. The analysis of data from the statistics report, enabled the identification of an employee who abused their permissions and retrieved excess data from the database.
When observing the weekly report, DBA noticed a change in the number of failed transactions. Further analysis enabled it to identify incorrectly implemented integration, which generated the aforementioned rollbacks.