Synthesis as one of PostgreSQL performance improvement methods

Philosophical introduction

As you know, there are only two methods for solving problems:

1. The analysis method or deduction method, or from general to specific.
2. The method of synthesis or the method of induction, or from the particular to the general.

To solve the problem of “improving database performance,” this might look like this.

Analysis - we analyze the problem into separate parts and, by solving them, we try to improve the performance of the database as a whole as a result.

In practice, the analysis looks like this:
')

• There is a problem (performance incident)
• We collect statistical information about the state of the database
• We are looking for bottlenecks
• Solving problems from bottlenecks

Database bottlenecks - infrastructure (CPU, Memory, Disks, Network, OS), settings (postgresql.conf), queries:

Infrastructure : the possibilities of influence and change for the engineer are almost nil.

Database settings : the possibilities for changes are slightly more than in the previous case, but as a rule, all the same are rather difficult, especially in the clouds.

Database queries : the only area for maneuvers .

Synthesis — we improve the performance of individual parts, expecting that, as a result, database performance will improve.

Lyrical introduction or why all this is necessary

How does the process of resolving performance incidents occur if the database performance is not monitored:

Customer - “everything is bad for us, for a long time, make us well”
Engineer— ”how bad is that?”
The customer is “like this now (an hour ago, yesterday, last business was), slowly”
Engineer - “when was it good?”
The customer - “a week (two weeks) ago was not bad. “(This is lucky)
The customer is “I don’t remember when it was good, but now it’s bad.” (The usual answer)

The result is a classic picture:

Who is to blame and what to do?

The first part of the question is the easiest to answer - the DBA engineer is always to blame.

The second part of the answer is also not too difficult - you need to implement a database performance monitoring system.

The first question arises - what to monitor?

Path 1. We will monitor EVERYTHING



The CPU load, the number of disk read / write operations, the size of the allocated memory, and another megaton of different counters that any more or less monitoring system can provide.

The result is a bunch of graphs, pivot tables, and continuous alerts to the mail and 100% employment of the engineer by solving a bunch of identical tickets, however, as a rule with the standard wording - “Temporary issue. No action need. ” But everyone is busy, and there is always something to show the customer - the work is in full swing.

Path 2. Monitor only what is needed, and what is not needed, do not need to be monitored

You can monitor, in a slightly different way, only entities and events:

• Which DBA Engineer Can Influence
• For which there is an algorithm of actions in the event of an event or entity change.

Based on this assumption and recalling the “ Philosophical Introduction ” in order to avoid regular repetition of “ Lyrical Introduction or Why All This Needs ”, it would be advisable to monitor the performance of individual queries for optimization and analysis, which ultimately should lead to improved performance of the entire database.

But in order to improve a heavy query that affects the overall performance of the database, you must first find it.

So, there are two interrelated questions:

• which query is considered heavy
• how to look for heavy queries.

Obviously, a heavy query is a query that uses a lot of OS resources to get the result.

Moving on to the second question - how to search and then monitor heavy queries?

What are the possibilities for monitoring queries in PostgreSQL?

Compared to Oracle, there are few possibilities, but still something can be done.

PG_STAT_STATEMENTS

To search and monitor heavy queries in PostgreSQL, the standard extension pg_stat_statements is intended.

After installing the extension, the same name appears in the target database, which should be used for monitoring purposes.

Target columns pg_stat_statements for building a monitoring system:

• queryid Internal hash code calculated from the operator's parse tree
• max_time Maximum time spent per operator, in milliseconds

Having accumulated and using statistics on these two columns, it is possible to build a monitoring system.

How pg_stat_statements are used to monitor PostgreSQL performance

To monitor query performance, use:
On the target database side - pg_stat_statements view
On the server and monitoring database side, a set of bash scripts and service tables.

Stage 1 - the collection of statistical data

On the monitoring host, a script is run regularly on the crown that copies the contents of the pg_stat_statements view from the target database to the pg_stat_history table in the monitoring database.

Thus, a history of the execution of individual queries is formed, which can be used for generating performance reports and setting up metrics.

Stage 2 - Setting Performance Metrics

Based on the collected data, we select the requests whose execution is most critical / important for the client (application). In coordination with the customer, we set the values ​​of the performance metrics using the queryid and max_time fields.

The result is the start of performance monitoring.

1. The monitoring script, at startup, checks the configured performance metrics by comparing the max_time value of the metric with the value from the pg_stat_statements view in the target database.
2. If the value in the target database exceeds the metric value - a warning is generated (incident in the ticket system)

Additional feature 1

Query Plans History

For the subsequent resolution of performance incidents, it is very good to have a history of changing query execution plans.

To store the history, the service table log_query is used. The table is filled in when analyzing a PostgreSQL loaded log file. Since the log file, in contrast to the pg_stat_statements view, contains the full text with the values ​​of the execution parameters and not the normalized text, it is possible to log not only the time and duration of requests, but also to store execution plans for the current time.

Additional opportunity 2

Continuous performance improvement process

Monitoring individual queries is generally not intended for solving the problem of continuous improvement of the performance of the database as a whole, since it controls and solves performance problems for individual queries only. However, you can extend the method and configure monitoring requests for all databases.

To do this, enter additional performance metrics:

• In recent days
• For the base period

The script selects requests from the pg_stat_statements view in the target database and compares the max_time value with the average max_time value, in the first case for the last days or for the selected time period (baseline), in the second case.

Thus, in case of performance degradation for any request, a warning will be generated automatically, without manual analysis of the reports.

And what have the synthesis?

In the described approach, as the synthesis method implies - by improving individual parts of the system, we improve the system as a whole.

• Query executed by database - thesis
• Modified query - antithesis
• Change system state - synthesis

System development

• Expansion of statistics collected by adding history to the system view pg_stat_activity
• Expansion of collected statistics by adding history for statistics of individual tables participating in queries
• Integration with AWS Cloud Monitoring System
• And yet, you can think of something ...