{"id":56075,"date":"2020-02-04T00:00:00","date_gmt":"2020-02-03T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/osnovy-monitoringa-postgresql-aleksej-lesovskij"},"modified":"2020-02-18T14:04:16","modified_gmt":"2020-02-18T11:04:16","slug":"osnovy-monitoringa-postgresql-aleksej-lesovskij","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/osnovy-monitoringa-postgresql-aleksej-lesovskij","title":{"rendered":"Fundamentals of PostgreSQL Monitoring. Alexey Lesovsky","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

I would like to present to you the transcript of the report by Alexey Lesovsky from Data Egret titled 'Fundamentals of PostgreSQL Monitoring'.<\/strong><\/p>\n

<\/p>\n

In this report, Alexey Lesovsky will discuss the key aspects of PostgreSQL statistics, what they mean, and why they should be included in monitoring; he will talk about what charts should be in monitoring, how to add them, and how to interpret them. The report will be useful for database administrators, system administrators, and developers interested in troubleshooting Postgres.<\/p>\n

\n

\r\n \"Play\r\n
<\/div>\r\n <\/div><\/center><\/a><\/noindex><\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

My name is Alexey Lesovsky, and I represent Data Egret. <\/p>\n

<\/p>\n

A few words about myself. I started out long ago as a system administrator. <\/p>\n

<\/p>\n

I administered various Linux environments, dealing with different Linux-related tasks, such as virtualization, monitoring, and working with proxies. But at some point, I shifted my focus more towards databases, particularly PostgreSQL, which I found very appealing. Eventually, I began dedicating most of my working time to PostgreSQL. Gradually, I became a PostgreSQL DBA.<\/p>\n

<\/p>\n

Throughout my career, I have always been interested in topics like statistics, monitoring, and telemetry collection. When I was a system administrator, I worked extensively with Zabbix and created a small set of scripts called zabbix-extensions<\/a><\/noindex>. It was quite popular in its time and allowed monitoring various critical components, not just Linux but also other elements.<\/p>\n

<\/p>\n

Now I focus on PostgreSQL. I am developing another tool that helps manage PostgreSQL statistics. It\u2019s called pgCenter<\/a><\/noindex> (article on Habr \u2014 PostgreSQL statistics without stress or hassle<\/a><\/noindex>). <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

A brief introduction. What situations do our customers, our clients, face? An accident related to the database occurs. Once the database is restored, the department head or the development manager says, \"Friends, we need to monitor the database because something bad happened, and we need to prevent this in the future.\" This is where the interesting process of selecting a monitoring system or adapting the existing monitoring system begins so that we can monitor our database\u2014PostgreSQL, MySQL, or others. Colleagues start suggesting, \"I heard there is such a database. Let's use it.\" Colleagues begin to argue with each other. In the end, we choose a certain database, but PostgreSQL monitoring in it is quite weak, and we always have to modify something. We end up taking some repositories from GitHub, cloning them, adapting scripts, and reconfiguring them. Ultimately, this results in manual work. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Therefore, in this presentation, I will try to provide you with some knowledge on how to choose monitoring not only for PostgreSQL but also for the database in general. I will share insights that will help you improve your monitoring to derive some benefits, such that you can monitor your database effectively and be alerted to potential emergency situations that may arise. <\/p>\n

<\/p>\n

The ideas presented in this talk can be directly adapted to any database, whether it is a relational database or a noSQL database. So, not only PostgreSQL will be covered, but there will also be many recipes on how to implement this in PostgreSQL. Examples of queries and entities available in PostgreSQL for monitoring will be shown. If your database has similar features that can be incorporated into monitoring, you can adapt and include them as well, and it will work well.<\/p>\n

<\/p>\n

\"FundamentalsIn this presentation, I will not
\ndiscuss how to deliver and store metrics. I will not talk about post-processing data or providing it to the user. Nor will I cover alerting.
\nThroughout the narrative, I will be displaying various screenshots of existing monitoring tools, along with my critiques of them. However, I will try not to name specific brands so as not to create any promotional or anti-promotional content for these products. Therefore, any coincidences are purely coincidental and left to your imagination.
\n\"Fundamentals
\nFirst, let's clarify what monitoring is. Monitoring is a very important aspect that everyone should have. This is well understood. However, monitoring is not considered a business product and does not directly impact a company's profit, which is why time for monitoring is often allocated as an afterthought. If we have time, we will engage in monitoring; if not, that\u2019s okay, we will put it in the backlog and return to those tasks someday. <\/p>\n

<\/p>\n

Thus, based on our experience, when we approach clients, monitoring is often underdeveloped and lacks interesting features that would help us work better with databases. Therefore, monitoring always needs improvement. <\/p>\n

<\/p>\n

Databases are complex systems that also need monitoring, as they are information repositories. This information is crucial for a company, and it must not be lost in any way. However, databases themselves are very complicated pieces of software. They consist of many components, and many of these components need to be monitored. <\/p>\n

<\/p>\n

\"FundamentalsWhen we talk specifically about PostgreSQL, it can be represented as a diagram consisting of many components. These components interact with each other. Additionally, PostgreSQL has a subsystem known as Stats Collector, which allows collecting statistics about the operation of these subsystems and provides an interface for the administrator or user to view this statistics. <\/p>\n

<\/p>\n

This statistics is presented in the form of a set of functions and views (view). They can also be referred to as tables. That is, with a regular psql client, you can connect to the database, perform a select on these functions and views, and obtain specific figures about the operation of PostgreSQL subsystems. <\/p>\n

<\/p>\n

You can add these figures to your favorite monitoring system, create graphs, add functions, and gain long-term analytics. <\/p>\n

<\/p>\n

However, in this report, I will not cover all these functions in detail, as it could take an entire day. I will focus on just two to four aspects and explain how they help improve monitoring.
\n\"Fundamentals
\nWhen it comes to database monitoring, what should we keep an eye on? First and foremost, we need to monitor availability, because the database is a service that provides access to data for clients, and we need to ensure its accessibility while also tracking its qualitative and quantitative characteristics. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

We also need to monitor the clients connecting to our database since they can be either normal clients or harmful ones that may damage the database. Their activity must be monitored and tracked as well.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

When clients connect to the database, it is clear that they begin to interact with our data. Therefore, we need to monitor how clients engage with the data: which tables they work with, and to a lesser extent, which indexes. In other words, we need to assess the workload created by our clients.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

However, the workload is primarily made up of queries. Applications connect to the database and access data through queries, so it's important to evaluate which queries we have in the database, track their adequacy, ensure they are well-structured, and identify if any options need rewriting to improve speed and performance. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

And since we are discussing databases, it's important to note that databases always involve background processes. These processes help maintain the database performance at a high level, which requires a certain amount of resources. At the same time, they can overlap with the resources needed for client queries, so greedy behavior from background processes can directly impact the performance of client requests. Thus, they also need to be monitored to ensure there are no imbalances regarding these background processes. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

All of this regarding database monitoring remains within system metrics. However, considering that most of our infrastructure is moving to the cloud, the system metrics of individual hosts often take a backseat. Nevertheless, they are still relevant in databases, and monitoring system metrics is certainly necessary. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

System metrics are generally in good shape; all modern monitoring systems already support these metrics. However, there are still some components that are lacking, and certain aspects need to be added. I will touch on these as well, with a few slides dedicated to them. <\/p>\n

<\/p>\n

\"Fundamentals
\nThe first point of the plan is availability. What is availability? In my understanding, availability is the database's ability to service connections, meaning the database is up and can accept connections from clients as a service. This availability can be assessed through certain characteristics, which are conveniently displayed on dashboards. <\/p>\n

<\/p>\n

\"Fundamentals
\nEveryone knows what dashboards are. It's when you take a glance at a screen that consolidates the necessary information. You can immediately determine whether there is an issue with the database or not.
\nThus, the availability of the database and other key characteristics must always be displayed on dashboards, so this information is readily available to you. Additional details, which help in investigating incidents or critical situations, should be included on secondary dashboards or hidden in drilldown links that lead to external monitoring systems. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

An example of a well-known monitoring system. It's a very powerful monitoring system. It collects a lot of data, but from my perspective, it has a peculiar concept of dashboards. There is a link to 'create dashboard.' However, when you create a dashboard, you're essentially creating a list consisting of two columns, a list of graphs. When you need to look for something, you start clicking with your mouse, scrolling, trying to find the desired graph. This takes time; in essence, there are no real dashboards\u2014only lists of graphs.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

What should be added to these dashboards? You can start with a metric like response time. PostgreSQL has the view pg_stat_statements. By default, it's disabled, but it's one of the important system views that should always be enabled and used. It holds information about all the executed queries that have run in the database. <\/p>\n

<\/p>\n

Accordingly, we can start with the total execution time of all queries and divide it by the number of queries using the aforementioned fields. However, this is just an average temperature for the hospital. We can also look at other fields: the minimum execution time, the maximum, and the median. We can even build percentiles since PostgreSQL has corresponding functions for this. We can obtain some figures that characterize the response time of our database based on previously executed queries, meaning we are not executing a dummy query 'select 1' to check the response time, but rather analyzing the response times from already executed queries and either representing them as a single figure or plotting a graph based on them. <\/p>\n

<\/p>\n

It\u2019s also important to monitor the number of errors generated by the system at any given moment. For this, we can use the view pg_stat_database. We focus on the xact_rollback field. This field not only shows the number of rollbacks occurring in the database, but also accounts for the number of errors. Essentially, we can display this number on our dashboard to see how many errors we have at this moment. If there are many errors, it\u2019s a good reason to check the logs and see what these errors are and why they occur, and then investigate and resolve them.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

You could add a metric like a Tachometer. This measures the number of transactions per second and queries per second. Essentially, you can use these figures as the current performance of your database and observe whether there are peaks in queries, peaks in transactions, or conversely, if the database is underloaded because some backend has failed. It\u2019s crucial to always monitor this number and remember that for our project, such performance is normal, while values above or below it indicate potential issues that require further investigation.<\/p>\n

<\/p>\n

To evaluate the number of transactions, we can once again refer to the pg_stat_database view. We can sum the number of commits and rollbacks to get the number of transactions per second. <\/p>\n

<\/p>\n

Everyone understands that multiple requests can be included within a single transaction, right? Therefore, TPS and QPS are slightly different. <\/p>\n

<\/p>\n

The number of requests per second can be obtained from pg_stat_statements by simply calculating the sum of all executed queries. Clearly, we compare the current value with the previous one, subtract it, and get the delta to determine the count.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

You can add additional metrics if desired, which also help assess the availability of our database and monitor if there have been any downtimes. <\/p>\n

<\/p>\n

One of these metrics is uptime. However, uptime in PostgreSQL is a bit tricky. Let me explain why. When PostgreSQL starts, it begins counting uptime. But if, at some point\u2014for instance, during the night\u2014something executes a task, and the OOM-killer forcibly terminates a PostgreSQL child process, in this case, PostgreSQL disconnects all clients, resets the sharded memory area, and begins recovery from the last checkpoint. While this recovery from the checkpoint takes place, the database does not accept connections, meaning this situation can be considered downtime. However, the uptime counter will not reset because it counts the time since the postmaster was launched from the very beginning. Therefore, such situations can be overlooked.<\/p>\n

<\/p>\n

It is also important to monitor the number of vacuum workers. Does everyone know what autovacuum is in PostgreSQL? It is an interesting subsystem within PostgreSQL. Many articles have been written about it, countless presentations have been done. There has been much discussion about vacuum and how it should operate. Many consider it an inevitable evil. And that\u2019s true. It's somewhat analogous to a garbage collector that cleans up outdated row versions that are no longer needed by any transactions and frees up space in tables and indexes for new rows. <\/p>\n

<\/p>\n

Why should we monitor it? Because vacuum can sometimes cause significant harm. It consumes a large amount of resources, leading to client requests suffering as a result. <\/p>\n

<\/p>\n

You should monitor it through the view pg_stat_activity, which I will discuss in the next section. This view shows the current activity in the database. Through this activity, we can track the number of vacuums that are running right now. We can monitor the vacuums and see that if we exceed the limit, it's a reason to look into the PostgreSQL settings and somehow optimize the vacuum's operation. <\/p>\n

<\/p>\n

Another feature of PostgreSQL is that PostgreSQL suffers greatly from long transactions. Especially from transactions that hang for a long time and do nothing. These are called stat idle-in-transaction. Such a transaction holds locks and prevents the vacuum from working. As a consequence, the tables bloat and increase in size. Queries that work with these tables start to run slower because they need to sift through all the old versions of rows from memory to disk and back.<\/strong> Therefore, the duration of the longest transactions and the longest vacuum requests should also be monitored. If we see any processes that have been running for a very long time, more than 10-20-30 minutes for OLTP load, we need to pay attention to them and terminate them forcibly, or optimize the application to prevent them from being invoked and hanging for so long.<\/strong> For analytical loads, 10-20-30 minutes is normal; sometimes they can take even longer. <\/p>\n

<\/p>\n

\"Fundamentals
\nNext, we have the option with connected clients. Once we have formed the dashboard and displayed key availability metrics, we can also add additional information about connected clients. <\/p>\n

<\/p>\n

Information about connected clients is important because, from PostgreSQL's perspective, clients can be different. There are good clients and bad clients. <\/p>\n

<\/p>\n

A simple example. By client, I mean an application. The application connects to the database and immediately starts sending its queries there, which the database processes and executes, returning the results to the client. These are good and correct clients. <\/p>\n

<\/p>\n

Sometimes, a client connects and holds the connection but does nothing. It remains in an idle state. <\/p>\n

<\/p>\n

But there are bad clients. For example, the same client connected, opened a transaction, did something in the database, and then went into the code, say, to access an external source or to process the received data. But they did not close the transaction. As a result, the transaction hangs in the database and holds a lock on a row. This is a bad state. If the application crashes somewhere internally with an exception, the transaction may remain open for a long time. This directly affects the performance of PostgreSQL. PostgreSQL will run slower. Therefore, it is important to monitor such clients in a timely manner and forcibly terminate their work. Additionally, you need to optimize your application to avoid such situations. <\/p>\n

<\/p>\n

Another type of bad clients is waiting clients. They become bad due to circumstances. For instance, a simple idle transaction can open a transaction, take locks on some rows, and then crash somewhere in the code, leaving a hanging transaction. Another client comes in, requests the same data, but encounters a lock because the hanging transaction already holds locks on some needed rows. Consequently, the second transaction will hang waiting for the first transaction to complete or be forcibly closed by an administrator. Thus, waiting transactions can accumulate and exceed the database connection limit. When the limit is exceeded, the application can no longer interact with the database. This becomes a critical situation for the project. Therefore, bad clients need to be monitored and addressed promptly. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Another example of monitoring. Here we have a decent dashboard. There is information on the connections at the top. DB connection \u2013 8 in total. And that\u2019s all. We have no information on which clients are active, which clients are just idle, doing nothing. There is no information on hanging transactions and waiting connections; in other words, it\u2019s just a number indicating the count of connections, and that\u2019s it. The rest is up to you to guess.
\n\"Fundamentals
\nTo add this information to monitoring, you need to refer to the system view pg_stat_activity. If you spend a lot of time in PostgreSQL, this is a great view that should become your friend because it shows current activity in PostgreSQL, that is, what's happening within it. Each process has a separate line that displays information about that process: from which host the connection was made, under which user it is running, under what name, when the transaction started, what query is currently being executed, and what was the last query executed. Accordingly, we can assess the client's status by the stat field. In a way, we can group by this field and obtain the current stats available in the database and the number of connections associated with this stat in the database. We can then send these figures to our monitoring and create graphs from them.
\nIt's also important to assess the duration of transactions. I've mentioned that evaluating the duration of vacuums is crucial, but transactions are assessed in the same way. There are fields xact_start and query_start. These, in essence, show the start time of the transaction and the start time of the query. We take the now() function, which displays the current timestamp, and subtract the transaction and query timestamps. This gives us the duration of the transaction and the duration of the query. <\/p>\n

<\/p>\n

If we observe long transactions, we should terminate them. For OLTP load, long transactions are those lasting more than 1-2-3 minutes.<\/strong>. For OLAP load, long transactions are normal, but if they run for more than two hours, it indicates that there is some imbalance somewhere.<\/strong> <\/p>\n

<\/p>\n

\"Fundamentals
\nWhen clients connect to the database, they begin working with our data. They access tables and indices to retrieve data from the table. It's important to evaluate how clients interact with this data.<\/p>\n

<\/p>\n

This is necessary to evaluate our workload and to understand which tables are the most 'active'. For instance, this is important when we want to place 'hot' tables on some fast SSD storage. Conversely, archival tables that we no longer use can be moved to a 'cold' archive on SATA disks, where they can remain until needed. <\/p>\n

<\/p>\n

It is also useful for detecting anomalies after various releases and deployments. Suppose the project rolled out a new feature. For example, added new functionality for working with the database. If we build usage graphs of the tables, we can easily spot these anomalies on these graphs, such as spikes in updates or deletes. This will be very visible.<\/p>\n

<\/p>\n

Anomalies in 'degraded' statistics can also be detected. What does this mean? PostgreSQL has a very strong and efficient query planner, and developers spend a lot of time enhancing it. How does it work? To create effective plans, PostgreSQL periodically collects statistics about the data distribution in the tables. This includes the most common values: the number of unique values and information about NULLs in the table, along with a wealth of information. <\/p>\n

<\/p>\n

Based on this statistics, the planner constructs several queries, selects the most optimal one, and uses this query plan to execute the query and return data. <\/p>\n

<\/p>\n

Sometimes, however, the statistics can become 'degraded'. The quality and quantity of data in the table may have changed, but the statistics were not updated. Therefore, the generated plans may turn out to be suboptimal. If our plans are suboptimal based on the collected monitoring data for the tables, we will be able to see these anomalies. For example, if data quality has changed significantly, a sequential scan of the table may be used instead of the index, meaning that if the query needs to return only 100 rows (with a limit of 100), a full scan will be performed. This always negatively impacts performance. <\/p>\n

<\/p>\n

We can see this in the monitoring. We can look at this query, execute an explain for it, gather statistics, and build a new additional index. We can then respond to this issue. That\u2019s why this is important. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Another example of monitoring. I think many of you recognize this one because it\u2019s very popular. Who uses it in their projects? Prometheus<\/a><\/noindex>? \u0410 \u043a\u0442\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442 \u044d\u0442\u043e\u0442 \u043f\u0440\u043e\u0434\u0443\u043a\u0442 \u0441\u043e\u0432\u043c\u0435\u0441\u0442\u043d\u043e \u0441 Prometheus? \u0414\u0435\u043b\u043e \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u0432 \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u043c \u0440\u0435\u043f\u043e\u0437\u0438\u0442\u043e\u0440\u0438\u0438 \u044d\u0442\u043e\u0433\u043e \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u0435\u0441\u0442\u044c \u0434\u0430\u0448\u0431\u043e\u0440\u0434 \u0434\u043b\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 PostgreSQL \u2013 postgres_exporter<\/a><\/noindex> Prometheus. But there's one downside here. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

There are several graphs. The units are specified in bytes, meaning there are five graphs: Insert data, Update data, Delete data, Fetch data, and Return data. The measurement unit listed is bytes. However, the statistics in PostgreSQL return data in tuples (rows). Consequently, these graphs are a very good way to underestimate your workload by several times, even by orders of magnitude, because a tuple is not a byte\u2014it\u2019s a string, it consists of many bytes, and it always has variable length. So, calculating workload in bytes using tuples is an unrealistic or very complex task. Therefore, when you use a dashboard or built-in monitoring, it's always important to understand that it works correctly and returns accurately evaluated data. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

How can we obtain statistics on these tables? For this, PostgreSQL has a certain family of views. The main view is pg_stat_user_tables<\/a><\/noindex>. User_tables means the tables created by the user. In contrast, there are system views that are used by PostgreSQL itself. There is also a summary table, Alltables, which includes both system and user tables. You can start from any of them that you prefer.<\/p>\n

<\/p>\n

Using the above fields, we can estimate the number of inserts, updates, and deletes. The dashboard example I used specifically employs these fields to assess workload characteristics. Therefore, we can also base our evaluations on them. But it's important to remember that these are tuples, not bytes, so we cannot simply convert them into bytes.<\/p>\n

<\/p>\n

Based on this data, we can create what we call TopN tables. For example, Top-5, Top-10. We can track those hot tables that are being utilized more than others. For instance, the five 'hottest' tables for inserts. We evaluate our workload based on these TopN tables and can assess workload spikes following various releases, updates, and deployments. <\/p>\n

<\/p>\n

It is also important to evaluate the size of the tables, as sometimes developers roll out a new feature, and our tables start to swell in size because they decided to add additional data, without anticipating how this would affect the size of the database. Such cases can also come as a surprise to us. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Now, here's a little question for you. What question arises when you notice a load on the database server? What is the next question that comes to mind? <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

But the actual question is this: What queries are causing the load? In other words, it's not interesting to look at the processes that are causing the load. It's clear that if the host has a database, then the database is running there, and only the databases will utilize that. If we open Top, we will see a list of processes in PostgreSQL that are doing something. From Top, it will not be clear what they are doing. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Therefore, it is necessary to identify the queries that are causing the most load because query tuning typically yields more benefits than tuning the PostgreSQL configuration, the operating system, or even the hardware. In my estimation, this is about 80-85-90%. And it\u2019s done much faster. It\u2019s quicker to adjust a query than to fix the configuration, plan a restart, especially if the database cannot be restarted, or to add hardware. It's simpler to rewrite a query or add an index to achieve better results from that query. <\/p>\n

<\/p>\n

\"Fundamentals
\nConsequently, it is necessary to monitor the queries and their adequacy. Let's take another example of monitoring. Here, too, it seems there's great monitoring. There is information about replication, bandwidth, locks, and resource utilization. Everything is great, but there's no information on the queries. It\u2019s unclear what queries are being executed in our database, how long they take, and how many of those queries there are. We always need to have this information in monitoring. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

To obtain this information, we can use the pg_stat_statements module. Based on this, we can create a variety of graphs. For example, we can get data on the most frequent queries, that is, those that are executed most often. Yes, it is also very helpful to review this after deployments to understand if there is any spike in queries. <\/p>\n

<\/p>\n

We can monitor the longest-running queries, which are the ones that take the most time to execute. They consume CPU resources and involve input\/output operations. We can assess this using the total_time, mean_time, blk_write_time, and blk_read_time fields. <\/p>\n

<\/p>\n

We can evaluate and monitor the heaviest queries in terms of resource usage; these are the ones that read from disk, those that consume memory, or, conversely, create some write load.<\/p>\n

<\/p>\n

We can assess the most generous queries. These are the queries that return a large number of rows. For example, it could be a query where the limit was forgotten, and it simply returns all the content of the table or data from the requested tables.<\/p>\n

<\/p>\n

We can also monitor queries that use temporary files or temporary tables. <\/p>\n

<\/p>\n

\"Fundamentals
\nAnd we have background processes remaining. Background processes are primarily checkpoints or control points, autovacuum, and replication. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Another example of monitoring. There is a Maintenance tab on the left; we go there hoping to see something useful. But it only shows the duration of the vacuum and statistics collection, nothing more. This is very limited information, so it is always necessary to know how the background processes are operating in our database and if there are any issues arising from their operation. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

When we consider checkpoints, it is important to remember that checkpoints write 'dirty' pages from shared memory to disk and then create a checkpoint. This checkpoint can subsequently be used as a recovery point if PostgreSQL is terminated unexpectedly. <\/p>\n

<\/p>\n

To clear all \"dirty\" pages to disk, a certain amount of writing must be performed. Generally, on systems with large amounts of memory, this can be quite substantial. If checkpoints occur very frequently within a short time frame, disk performance may significantly degrade. Client requests will suffer from a lack of resources, struggling for resources and experiencing insufficient performance. <\/p>\n

<\/p>\n

Through pg_stat_bgwriter, we can monitor the number of checkpoints occurring in the specified fields. If there are many checkpoints in a given time frame (10-15-20 minutes, half an hour), for example, 3-4-5, this may indicate a problem. We should investigate the database and configuration to determine what is causing such a high number of checkpoints. There might be a large write operation. Based on workload, we can assess since workload graphs are already added. We can fine-tune checkpoint parameters to minimize their impact on query performance.<\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

I return to autovacuum once again because, as I mentioned, it can significantly affect the performance of both disks and queries. Therefore, it is always important to evaluate the number of autovacuum operations. <\/p>\n

<\/p>\n

The number of autovacuum workers in the database is limited. By default, there are three, so if all three workers are constantly running in the database, it indicates that autovacuum is underconfigured. We need to increase the limits, reassess autovacuum settings, and delve into the configuration.
\nIt's essential to evaluate which vacuum workers are running. This may be initiated by a user, where a DBA has manually started a vacuum, resulting in a load. A problem may arise there. Alternatively, it's the number of vacuums that impact the transaction counter. For certain versions of PostgreSQL, these can be heavy vacuums. They can significantly degrade performance because they scan the entire table, examining all blocks in that table. <\/p>\n

<\/p>\n

Of course, there's the duration of vacuums. If we have long vacuums that run for an extended period, it indicates that we may need to revisit the vacuum configuration and perhaps adjust its settings. This is because a situation may arise where the vacuum works on a table for a long time (3-4 hours), but during its operation, a large number of dead tuples may accumulate again in the table. As soon as the vacuum completes, it will need to vacuum the table once more. This leads us to a situation of an endless vacuum. In such cases, the vacuum fails to do its job, and the tables gradually start to bloat in size, even though the volume of useful data within remains unchanged. Therefore, during long vacuums, we always examine the configuration and strive to optimize it, ensuring that client query performance does not suffer. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

Currently, there are hardly any PostgreSQL installations without streaming replication. Replication is the process of transferring data from the master to the replica.<\/p>\n

<\/p>\n

Replication in PostgreSQL is structured through the transaction log. The master generates the transaction log. This transaction log is sent over the network to the replica, where it is then applied. It's quite straightforward. <\/p>\n

<\/p>\n

Accordingly, the pg_stat_replication view is used for monitoring replication lag. However, it's not all straightforward. In version 10, the view underwent several changes. First, some fields were renamed, and additional fields were added. Version 10 introduced fields that allow for evaluating replication lag in seconds, which is very convenient. Prior to version 10, it was only possible to assess replication lag in bytes. This option remains in version 10, meaning you can choose whichever is more convenient for you\u2014assessing lag in bytes or in seconds. Many do both.<\/p>\n

<\/p>\n

Nevertheless, in order to assess replication lag, you need to know the position of the log in the transaction. These transaction log positions are available in the pg_stat_replication view. In a way, using the pg_xlog_location_diff() function, we can take two points in the transaction log, calculate the delta between them, and obtain the replication lag in bytes. This is very convenient and simple. <\/p>\n

<\/p>\n

In version 10, this function was renamed to pg_wal_lsn_diff(). In general, in all functions, views, and utilities where the term 'xlog' was used, it has been replaced with 'wal'. This applies to both views and functions. This is a notable change. <\/p>\n

<\/p>\n

Additionally, version 10 introduced specific lines that indicate lag, namely write lag, flush lag, and replay lag. It is important to monitor these metrics. If we observe replication lag, we need to investigate its origin and resolve the issue. <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

System metrics are mostly in good shape. Any monitoring starts with system metrics such as CPU, memory, swap, network, and disk utilization. However, many parameters are not included by default. <\/p>\n

<\/p>\n

While process utilization is fine, there are issues with disk utilization. Typically, monitoring tools include information on throughput, which may be measured in IOPS or bytes. However, they often overlook latency and utilization of disk devices. These are more critical parameters that help assess how loaded the disks are and how much they are slowing down performance. High latency indicates that there are issues with the disks. High utilization means that the disks are struggling.<\/strong> These are more qualitative metrics than throughput.<\/p>\n

<\/p>\n

Although this statistics can also be obtained from the \/proc filesystem, as done for CPU utilization, I don't know why this information is not included in monitoring tools. Nonetheless, it's important to have this in your monitoring.<\/strong> <\/p>\n

<\/p>\n

The same goes for network interfaces. There is information about network throughput in packets and bytes, but there is no information on latency or utilization, which is also valuable information.<\/strong> <\/p>\n

<\/p>\n

\"Fundamentals<\/p>\n

<\/p>\n

All monitoring tools have drawbacks. No matter which tool you choose, it will always fall short of some criteria. However, they evolve, new features get added, so pick one and enhance it. <\/p>\n

<\/p>\n

To enhance it, you must always understand what the provided statistics mean and how they can help resolve issues. <\/p>\n

<\/p>\n

And a few key points:<\/p>\n

<\/p>\n