{"id":32353,"date":"2019-10-31T21:46:32","date_gmt":"2019-10-31T18:46:32","guid":{"rendered":"https:\/\/prohoster.info\/blog\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting\/"},"modified":"2019-10-31T21:46:32","modified_gmt":"2019-10-31T18:46:32","slug":"monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","title":{"rendered":"Performance monitoring of PostgreSQL queries. Part 1 \u2014 Reporting","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Engineer \u2014 derived from Latin \u2014 means inspired.
\nAn engineer can do everything. (c) R.Diesel.
\nEpigraphs.<\/i>
\n\"Performance
\nOr a story about why a database administrator should remember their programming past. <\/i><\/p>\n

Preface<\/h2>\n

\nAll names are changed. Any resemblance is purely coincidental. The material represents solely the author's personal opinion.<\/p>\n

Disclaimer of warranties:<\/b> The planned series of articles will not provide detailed and precise descriptions of the tables and scripts used. The materials cannot be used 'AS IS' right away.
\nFirstly, due to the large volume of material,
\nsecondly, due to the focus on the production database of the actual client.
\nTherefore, the articles will present only ideas and descriptions in the most general terms.
\nPerhaps in the future, the system will grow to the level of being uploaded to GitHub, or perhaps not. Time will tell.<\/i><\/p><\/blockquote>\n

\nThe beginning of the story - \"Do you remember how it all began?<\/a><\/noindex>\u00bb.
\nWhat resulted, in broad terms- \"Synthesis as one of the methods for improving PostgreSQL performance<\/a><\/noindex>\u00bb<\/p>\n

Why all this for me?<\/h2>\n

\nWell, firstly, so I don't forget myself, recalling the glorious days in retirement.
\nSecondly, to systematize what I've written. For I sometimes start to get confused and forget separate parts. <\/p>\n

And most importantly \u2014 perhaps someone might find it useful and help avoid reinventing the wheel and stepping on rakes. In other words, improve one\u2019s karma (not the Habr kind). For the most valuable thing in this world is ideas. The key is to find an idea. Bringing an idea to reality is purely a technical matter.<\/p>\n

So, let\u2019s begin, slowly\u2026<\/p>\n

Task statement.<\/h2>\n

<\/p>\n

Available: <\/h3>\n

\nPostgreSQL database (10.5), mixed load type (OLTP+DSS), of medium-small load, located in AWS cloud.
\nDatabase monitoring is absent, infrastructure monitoring is provided by AWS's native tools in minimal configuration.<\/p>\n

Required:<\/h3>\n

\nTo monitor the performance and state of the database, to find and have initial information for optimizing heavy queries to the DB.
\n<\/a><\/noindex><\/p>\n

A brief preface or analysis of solution options.<\/h2>\n

\nFirst, let's try to analyze the solution options from the perspective of a comparative analysis of benefits and drawbacks for engineers, while the management can handle the benefits and losses, as per the staffing schedule.<\/p>\n

Option 1 - \"Working on demand\"<\/h3>\n

\nWe keep everything as is. If the client is dissatisfied with something regarding the functionality or performance of the database or application, they will notify the DBA engineers via email or by creating an incident in the ticketing system.
\nThe engineer, upon receiving the notification, will investigate the issue, propose a solution, or postpone the problem, hoping it will resolve itself, and anyway, everything will soon be forgotten.
\nCookies and pastries, bruises and bumps<\/b>Cookies and pastries:<\/b>
\n1. No unnecessary actions are needed
\n2. There is always a way to dodge responsibility and slack off.
\n3. A lot of time can be spent at one's discretion.
\nBruises and bumps:<\/b>
\n1. Sooner or later, the client will ponder the essence of existence and cosmic justice in this world, and once again ask themselves the question \u2014 why am I paying these people with my money? The outcome is always the same \u2014 the only question is when the client will get bored and wave goodbye. And the feeding trough will be empty. It's sad.
\n2. The engineer's development is zero.
\n3. Challenges in planning work and load. <\/p>\n

Option 2 - \"Dancing with tambourines, selling and fitting\"<\/h3>\n

\nPoint 1<\/b>- Why do we need a monitoring system? We will get everything through requests. Launching a bunch of requests to the data dictionary and dynamic views, turning on various counters, collating everything into tables, periodically analyzing lists and tables. As a result, we have beautiful or not-so-beautiful graphics, tables, reports. The main thing is to have more and more.
\nPoint 2<\/b>- Generate activity - start analyzing all of this.
\nPoint 3<\/b>- Prepare a certain document, simply call this document \u2014 \"how we should organize the database.\"
\nPoint 4<\/b>- The client, seeing all this splendor of graphics and numbers, remains in childlike naive confidence \u2014 now everything will work out soon. And they easily and painlessly part with their money. Management is also confident \u2014 our engineers work tremendously. Load is at maximum.
\nPoint 5<\/b>- Regularly repeat Point 1.
\nCookies and pastries, bruises and bumps<\/b>Cookies and pastries: <\/b>
\n1. The life of managers and engineers is simple, predictable, and filled with activity. Everything buzzes, everyone is busy.
\n2. The client's life is also not bad \u2014 they always believe that they just need to endure a little longer and everything will be fine. It doesn't work out, well, what can you do \u2014 this world is unfair, maybe they'll have better luck in the next life.
\nBruises and bumps:<\/b>
\n1. Sooner or later, there will be a quicker provider of a similar service who will do the same thing for a bit less. If the result is the same, why pay more? This will again lead to the loss of the income stream.
\n2. It's boring. Just like any meaningless activity.
\n3. Just like in the previous option \u2014 no development whatsoever. But for an engineer, the downside is that unlike the first option, here you need to constantly generate a database. Which takes time. Time that could be better spent on yourself. Because if you don't take care of yourself, no one else will.<\/p>\n

Option 3 \u2014 There's no need to reinvent the wheel, just buy it and ride.<\/h3>\n

\nEngineers at other companies don't eat pizza washed down with beer for nothing (ah, the good old days of 90s Petersburg). Let's use monitoring systems that are built, debugged, and working, and that actually deliver benefits (at least to their creators).
\nCookies and pastries, bruises and bumps<\/b>Cookies and pastries:<\/b>
\n1. There's no need to waste time coming up with something that has already been invented. Just take it and use it.
\n2. Monitoring systems are not written by fools, and they are definitely useful.
\n3. Working monitoring systems typically provide useful filtered information.
\nBruises and bumps:<\/b>
\n1. In this case, an engineer is not really an engineer, but merely a user of someone else's product. Or a user.
\n2. You need to convince the client of the necessity to buy something they really don't want to deal with and shouldn't have to, especially since the budget for the year has been set and won't change. Then you have to allocate a separate resource to adjust it for a specific system. So first, you need to pay, pay, and pay again. And the client is stingy. That's the norm of this life.<\/p>\n

What to do \u2014 Chernyshevsky? Your question is quite pertinent. (c)<\/h2>\n

\nIn this specific case and situation, you can approach it just a bit differently \u2014 let's create our own monitoring system. <\/b>
\n\"Performance
\nWell, not a system in the full sense of the word; that's too grand a term and presumptuous, but at least we can simplify the task a bit and gather more information for resolving performance incidents. So we don't find ourselves in a situation where you say, 'Go there, I don't know where, find that, I don't know what.'<\/p>\n

What are the pros and cons of this option:<\/h4>\n

Pros:<\/b>
\n1. It's interesting. At least it's more interesting than constant 'shrink datafile, alter tablespace, etc.'
\n2. These are new skills and new development. This will eventually lead to well-deserved rewards sooner or later.
\nCons:<\/b>
\n1. You will have to work. Work a lot.
\n2. You will need to regularly explain the meaning and prospects of all activities.
\n3. Something will have to be sacrificed, as the only resource available to an engineer \u2014 time \u2014 is limited by the universe.
\n4. The most frightening and unpleasant thing<\/b> \u2014 is that it might result in something like \"Not a mouse, not a frog, but an unknown creature.\"<\/p>\n

Those who don't take risks don't drink champagne.<\/b>
\nSo \u2014 the most interesting part begins.<\/p>\n

The general idea \u2014 schematically<\/h2>\n

\n\"Performance
\n(The illustration is taken from the article<\/i> \u00abSynthesis as one of the methods for improving PostgreSQL performance<\/a><\/noindex>\u00bb)<\/p>\n

Explanation:<\/p>\n