1 00:00:00,000 --> 00:00:03,635 Michael: Hello, and welcome to Postgres film, a weekly show about all things Postgres. 2 00:00:03,935 --> 00:00:08,825 I am Michael founder of PG mustard, and this is my cohost Nicola, founder of Postgres AI. 3 00:00:09,095 --> 00:00:10,795 Hey Nilay and what we are talking about today. 4 00:00:12,085 --> 00:00:12,957 Nikolay: Hi, Michael. 5 00:00:12,957 --> 00:00:15,230 Uh, Today we are going to talk monitoring. 6 00:00:15,432 --> 00:00:20,749 And I think, uh, we are touching very, very, wide wide areas, uh, of Postgres. 7 00:00:20,749 --> 00:00:26,969 And probably we will revisit each one of them trying to go in some, uh, specific sub areas. 8 00:00:26,969 --> 00:00:27,239 Right. 9 00:00:27,239 --> 00:00:29,829 But every project every project should have. 10 00:00:30,211 --> 00:00:31,238 Michael: Well, do you mean? 11 00:00:31,238 --> 00:00:32,923 So let's check that first. 12 00:00:32,923 --> 00:00:37,123 So we naturally for large enterprises, we, we definitely want monitoring. 13 00:00:37,123 --> 00:00:39,163 We definitely want database specific things being 14 00:00:39,575 --> 00:00:41,615 Nikolay: Sometimes couple, couple of tools. 15 00:00:41,615 --> 00:00:49,270 So like sometimes I saw big projects using three monitoring and all three tools were monitoring Postgres. 16 00:00:49,335 --> 00:00:53,122 None of them were perfect to be enough alone. 17 00:00:53,122 --> 00:00:53,332 Right? 18 00:00:53,332 --> 00:00:57,695 So we needed to combine parts from three places. 19 00:00:58,085 --> 00:00:58,985 It's it happens. 20 00:00:58,985 --> 00:01:00,845 I think by, by the way, it's not a problem. 21 00:01:00,845 --> 00:01:04,585 If you have a couple of tools used, if you know that overhead is low, right? 22 00:01:05,110 --> 00:01:06,040 Michael: Yeah, absolutely 23 00:01:06,265 --> 00:01:08,395 Nikolay: we are talking about Pogs monitoring. 24 00:01:08,433 --> 00:01:09,603 This is huge topic. 25 00:01:09,843 --> 00:01:14,413 And, my main statement is, there is no good product developed. 26 00:01:15,204 --> 00:01:18,549 Michael: no perfect product maybe or no good product. 27 00:01:18,628 --> 00:01:19,998 Nikolay: if even, okay. 28 00:01:20,228 --> 00:01:20,518 Okay. 29 00:01:20,523 --> 00:01:20,758 Good. 30 00:01:20,758 --> 00:01:21,898 Is maybe, okay. 31 00:01:21,898 --> 00:01:23,398 No great products. 32 00:01:23,428 --> 00:01:30,123 I don't see great products because, , I understand what should be present in any Postgres monitoring. 33 00:01:30,183 --> 00:01:32,346 And it's, it's not my point of view. 34 00:01:32,346 --> 00:01:36,296 We, a couple of years ago, we started some community work. 35 00:01:36,296 --> 00:01:38,426 So we started to discuss various tools. 36 00:01:38,526 --> 00:01:41,591 We, we had several, sessions in Russian community. 37 00:01:41,591 --> 00:01:51,446 We had several sessions reviewing various tools, both open source and commercial, and then we created called monitoring checklist, post 38 00:01:51,446 --> 00:02:01,091 this monitoring checklist, collecting ideas what should be present in monitoring and what should be, must have, or nice to have and so on. 39 00:02:01,451 --> 00:02:05,111 And, uh, then some people, outside of rush community joined as well. 40 00:02:05,111 --> 00:02:14,095 And, and eventually we, I think we, we've got very good understanding what should be present like a hundred 41 00:02:14,095 --> 00:02:17,635 percent, uh, if you want your monitoring be very useful. 42 00:02:17,687 --> 00:02:26,763 And, I don't see any product, commercial or open source or maybe even in house, sometimes in house products I see very good ones, but none 43 00:02:26,763 --> 00:02:32,367 of them meet these criteria even 80%, they usually it's below 50. 44 00:02:32,728 --> 00:02:33,148 Michael: So, yeah. 45 00:02:33,148 --> 00:02:41,188 So we are talking about things that you consider probably almost a must have for most projects that people care about the uptime of. 46 00:02:41,288 --> 00:02:46,418 Making sure they have each of those covered in, is it monitoring, monitoring and alerts? 47 00:02:46,448 --> 00:02:48,128 How, like, do you count that as the same thing? 48 00:02:48,471 --> 00:02:51,681 Nikolay: So well alerts, it's like a, a slightly different topic. 49 00:02:51,686 --> 00:02:57,637 I'm talking about troubleshooting sessions when we open monitoring and try to understand what's happening, what's wrong because 50 00:02:57,787 --> 00:03:00,907 we have some complaints from users or from our coworkers. 51 00:03:00,907 --> 00:03:01,207 And we. 52 00:03:01,717 --> 00:03:06,007 Something wrong with POS happening, and we need to understand what exactly is wrong. 53 00:03:06,097 --> 00:03:10,597 And I see two big areas which must present in any monitoring. 54 00:03:10,957 --> 00:03:19,152 First area is of course we should have physical metrics like CPU, memory, disco network. 55 00:03:19,452 --> 00:03:20,172 It should be covered. 56 00:03:20,172 --> 00:03:22,495 And almost always it's it's covered. 57 00:03:22,675 --> 00:03:24,115 And it's like, it's separate. 58 00:03:24,783 --> 00:03:25,933 PSG specifics. 59 00:03:25,938 --> 00:03:35,129 And here we can recall, uh, Alex Soki P starts do deaf, this interactive picture, which shows you architecture of PSGs and 60 00:03:35,147 --> 00:03:42,811 what system views and extensions are, or tools are responsible for monitoring particular part of Postgres architecture. 61 00:03:42,811 --> 00:03:43,681 It's it's a great tool. 62 00:03:43,681 --> 00:03:46,561 We will provide link in description I'm sure. 63 00:03:47,259 --> 00:03:49,011 So, this should be present in monitoring. 64 00:03:49,041 --> 00:03:51,975 And this first part, I call dashboard number one. 65 00:03:52,085 --> 00:03:59,945 The goal of dashboard number one is like you have like 30 or maximum 60 seconds and you need to perform very wide 66 00:04:00,005 --> 00:04:10,635 and very shallow overview of all Pogo components, all Pogs, uh, , areas and understand which area looks not well. 67 00:04:10,695 --> 00:04:16,575 So we need to go deeper using other dashboards probably, or maybe manually looking at Pogs. 68 00:04:16,575 --> 00:04:19,815 So, and second component is, is query monitoring. 69 00:04:19,875 --> 00:04:24,828 Query monitoring it's like not a king, it's a queen of, of, uh, observability, right? 70 00:04:25,648 --> 00:04:27,088 It should be present always. 71 00:04:27,088 --> 00:04:29,098 It's it's this whole different topic. 72 00:04:29,158 --> 00:04:30,928 It should be present in any monitoring. 73 00:04:30,958 --> 00:04:35,398 And again, I don't see all like some, some systems. 74 00:04:35,993 --> 00:04:43,763 Improving for example, data though currently is very well improving in this area, particularly, and by the way, they do it for all databases. 75 00:04:43,763 --> 00:04:44,933 Not only for PSGs. 76 00:04:44,938 --> 00:04:50,963 I just see how well they are compared to two years ago, but they have big mistakes as well. 77 00:04:51,063 --> 00:04:58,203 Like not mistakes, but disadvantages like missing parts, which don't allow me to work with it, but probably we 78 00:04:58,203 --> 00:05:01,603 should, postpone a discussion about, query analysis. 79 00:05:01,633 --> 00:05:08,265 It's like maybe different talk, but let's focus on this first, like wide and shallow solo dashboard. 80 00:05:08,625 --> 00:05:12,925 Like I want everything quickly help of each component. 81 00:05:12,974 --> 00:05:19,791 Michael: So the use case is, my pager's just gone off or I've got a notification or, or a user mentioned something that doesn't sound good. 82 00:05:20,031 --> 00:05:23,241 And I want to look at something and see, where is the problem? 83 00:05:23,391 --> 00:05:24,921 Like what, where do I even start 84 00:05:24,981 --> 00:05:25,312 Nikolay: Right. 85 00:05:25,312 --> 00:05:36,332 So either some people say there are errors related to Pogo or some performance degradation and we want like to have 10 86 00:05:36,452 --> 00:05:41,732 or like dozen of like 12 metrics present on single dashboard. 87 00:05:42,272 --> 00:05:48,832 So we quickly understand are we okay compared to like one hour ago or one day ago or one week ago. 88 00:05:48,999 --> 00:05:50,949 Or we are not okay in particular areas. 89 00:05:50,949 --> 00:05:51,969 So let's dive deeper. 90 00:05:52,015 --> 00:06:01,066 So let's probably quickly have some overview of these metrics because I have a list and I also, we can attach this list to our episode, right? 91 00:06:01,776 --> 00:06:02,046 Michael: Yep. 92 00:06:02,619 --> 00:06:03,974 So what's the top of your list? 93 00:06:04,211 --> 00:06:12,691 Nikolay: At the top of the list, there's, two major things which represent the behavior of any multi-user system, throughput and Latin. 94 00:06:13,178 --> 00:06:16,768 Not only much user, but where network is also, involved. 95 00:06:17,068 --> 00:06:25,818 So throughput in throughput, uh, if we talk about databases per particularly can be measured in several ways, but two ways are most interesting to us. 96 00:06:25,818 --> 00:06:29,598 It's TPS and QS transactions per second and queries per second. 97 00:06:29,688 --> 00:06:34,818 And unfortunately posts doesn't allow to measure the second one really well. 98 00:06:34,968 --> 00:06:38,508 TPS are present in PPG star database or PPG database. 99 00:06:38,508 --> 00:06:41,538 I always mix it exact commit and exact rollback. 100 00:06:41,548 --> 00:06:46,579 We observe these two numbers and some of how fast is increasing. 101 00:06:46,669 --> 00:06:48,259 This is our TPS. 102 00:06:48,347 --> 00:06:49,787 Michael: Or decreasing, right. 103 00:06:49,847 --> 00:06:51,617 If it's, if there's some problems where it 104 00:06:51,698 --> 00:06:53,858 Nikolay: It cannot decrease it's accumulative. 105 00:06:53,891 --> 00:06:58,396 It's just a counter of committed transactions and rolled back transactions. 106 00:06:58,691 --> 00:06:59,051 Michael: Sorry. 107 00:06:59,056 --> 00:07:00,301 I thought you were talking about TPS still. 108 00:07:00,351 --> 00:07:01,341 Nikolay: So right. 109 00:07:01,391 --> 00:07:07,561 What can decrease if we divided by the duration of like, we, we need two snapshots as always, right. 110 00:07:07,861 --> 00:07:09,961 And we need to understand how many seconds between them. 111 00:07:09,961 --> 00:07:13,900 So we divide and of course, this like speed can go up and down. 112 00:07:14,050 --> 00:07:19,141 TPSs can go up and down, but Q PS, unfortunately, Postgres, system use don't. 113 00:07:19,586 --> 00:07:27,675 And, uh, usual trick is to use statements, but it's not exact number, unfortunately, because statements, as I remember do 114 00:07:27,675 --> 00:07:32,055 max, uh, statements dot max parameter by default is 5,000. 115 00:07:32,102 --> 00:07:35,625 if I'm not mistaken, it can be tuned to 10,000 or hundred. 116 00:07:35,925 --> 00:07:38,265 It means that we don't register all queries. 117 00:07:38,542 --> 00:07:43,971 So we probably see only the tip of the iceberg and QPS can be wrong if we use this approach. 118 00:07:44,241 --> 00:07:47,091 If we use calls metric from PJI segments. 119 00:07:47,309 --> 00:07:57,019 When I have doubts, I usually usually like most of the cases we have P bouncer, so I check P bouncer logs and it reports Q PS like fair 120 00:07:57,019 --> 00:08:01,359 number of QPS, but this is like maybe already too deeper about it. 121 00:08:01,472 --> 00:08:04,652 Michael: Yeah, let's focus on the things we can, uh, can measure in Postgres. 122 00:08:05,106 --> 00:08:05,376 Nikolay: Right. 123 00:08:05,376 --> 00:08:08,376 This, this is throughput, Latin seeds, query duration. 124 00:08:08,406 --> 00:08:11,259 Again, usually it's measured from PC statements timing. 125 00:08:11,529 --> 00:08:19,749 And we like why it's important because we, we need to understand, for example, if QPS and TPS dropped significantly, maybe it's 126 00:08:19,749 --> 00:08:23,679 just a problem with application Aeros on application cost. 127 00:08:23,739 --> 00:08:25,374 Uh, like our load dropped. 128 00:08:25,874 --> 00:08:32,224 Maybe it's a problem related to like, uh, CPU a hundred, all CPUs are a hundred percent who have situation or 129 00:08:32,224 --> 00:08:36,969 this situation on that's why we process fewer T TPS. 130 00:08:37,119 --> 00:08:39,189 And latency of course, very important. 131 00:08:39,189 --> 00:08:42,824 And I see in many cases we don't have it in monitoring. 132 00:08:43,124 --> 00:08:44,384 These two metrics are. 133 00:08:45,094 --> 00:08:54,104 It's it's our general health of our database should be always like we have problem let's check CPS and latency, average duration of queries. 134 00:08:54,291 --> 00:08:56,550 So next, , very, very, very, very important. 135 00:08:56,550 --> 00:09:03,763 And I don't also see like more than 50 systems are not good with, with this it's connection monitoring. 136 00:09:03,923 --> 00:09:12,496 My ideal is just one single stacked graph of connections, active item, transaction idle for, I don't remember. 137 00:09:12,736 --> 00:09:16,096 So, but these three already tell them, tell everything. 138 00:09:16,096 --> 00:09:16,396 Right. 139 00:09:16,576 --> 00:09:17,226 So active. 140 00:09:17,806 --> 00:09:19,006 We process something. 141 00:09:19,366 --> 00:09:22,926 If active goes above number of CPU, it's already bed sign. 142 00:09:22,936 --> 00:09:32,014 So if you have, uh, 12 course, and we see active 20, well probably already too much work to do for this server. 143 00:09:32,014 --> 00:09:34,994 And probably we have already huge degradation in performance. 144 00:09:35,264 --> 00:09:44,814 It's not always so, but in many cases, so, adults and connections, adult transaction connections also very important, especially before Pogs. 145 00:09:45,669 --> 00:09:46,779 13 or 14. 146 00:09:46,779 --> 00:09:48,909 were optimizations 14. 147 00:09:48,909 --> 00:09:49,179 Right? 148 00:09:49,179 --> 00:09:49,359 Right. 149 00:09:49,539 --> 00:09:55,119 So I saw, sometimes people say, okay, we have a couple of thousand more idol connections. 150 00:09:55,209 --> 00:09:55,989 Don't worry. 151 00:09:56,229 --> 00:09:57,189 They are idol. 152 00:09:57,219 --> 00:10:03,477 But you know, overhead is big and I think Andre had a very good couple of posts. 153 00:10:03,507 --> 00:10:03,897 Also. 154 00:10:03,897 --> 00:10:05,037 We should provide links. 155 00:10:05,037 --> 00:10:05,337 Right. 156 00:10:05,396 --> 00:10:06,568 explaining overhead 157 00:10:06,691 --> 00:10:09,012 Michael: I think he did a lot of the work in 14. 158 00:10:09,012 --> 00:10:09,722 I think it was. 159 00:10:10,148 --> 00:10:10,898 Nikolay: optimizing. 160 00:10:10,932 --> 00:10:16,872 It, it was related not to memory consumption as I remember, but, uh, how work with snapshots is organized. 161 00:10:16,919 --> 00:10:21,182 Usually people think a couple of thousand connections give us a lot of memory overhead. 162 00:10:21,182 --> 00:10:24,902 No, it's more about how work with snapshots organized. 163 00:10:25,072 --> 00:10:30,022 But what I like about stack graphs is that we can quickly understand the total as well. 164 00:10:30,028 --> 00:10:30,446 Right? 165 00:10:30,447 --> 00:10:34,017 And, uh, sometimes people split to multiple charts, multiple graphs. 166 00:10:34,017 --> 00:10:39,987 It's also fine, but, uh, I like to see connection and overview in one place. 167 00:10:40,707 --> 00:10:43,887 So, and, and of course I don't, I don't in transaction. 168 00:10:44,245 --> 00:10:45,775 sessions or connections. 169 00:10:45,775 --> 00:10:47,635 Connections sessions is the same, basically. 170 00:10:47,635 --> 00:10:55,432 They are, very dangerous sometimes because if application started transaction and went to talk to some external API, for example, 171 00:10:55,437 --> 00:10:58,534 or to do some other work, it's, it can be very dangerous. 172 00:10:58,534 --> 00:11:03,864 So we should also understand like, we should have some threshold and not, not allow system to go. 173 00:11:04,484 --> 00:11:05,084 Michael: So, yeah. 174 00:11:05,144 --> 00:11:08,024 So at this point, I guess we're talking about long transactions. 175 00:11:08,589 --> 00:11:19,230 Nikolay: No, well, sometimes these things correlate, but not always because we can have, for example, at each moment we have 200 in transaction sessions. 176 00:11:19,260 --> 00:11:19,590 Right. 177 00:11:19,599 --> 00:11:22,389 But duration of each transaction is less than one. 178 00:11:22,653 --> 00:11:23,403 It might happen. 179 00:11:23,433 --> 00:11:30,250 Very brief transactions, but they do something and keep connections, idle it also possible. 180 00:11:30,389 --> 00:11:33,584 But next item is long transactions actually. 181 00:11:33,944 --> 00:11:39,412 And, this is actually a big topic as well, because usually long transactions have two dangers. 182 00:11:39,442 --> 00:11:43,080 One danger is to lock something and block others. 183 00:11:43,095 --> 00:11:47,995 It's one point, another point is, uh, disturb auto vacuum work. 184 00:11:48,040 --> 00:11:55,386 But in the letter we should talk about not long transactions, but about XME horizon, it should be different. 185 00:11:55,409 --> 00:11:56,583 And I didn't see it. 186 00:11:56,588 --> 00:12:01,023 Never like no money talking distinguishes these two things yet. 187 00:12:01,173 --> 00:12:10,113 Of course I didn't see all existing systems of course, but over the last couple of years, observing various systems, like dozens of them. 188 00:12:10,374 --> 00:12:11,479 Michael: Should we talk about that then? 189 00:12:11,479 --> 00:12:12,109 Straight away. 190 00:12:12,109 --> 00:12:20,309 So the X Xmen horizon being to avoid, transaction ID wraparound, which is, you know, there've been some famous blog posts that I can include on that. 191 00:12:20,482 --> 00:12:29,432 Nikolay: Right, transaction idea around is one from horizon being stuck in the, in very like past, but also accumulating 192 00:12:29,432 --> 00:12:33,269 a lot of, deads and then it converts to blo also a problem. 193 00:12:33,272 --> 00:12:33,362 Right. 194 00:12:33,407 --> 00:12:34,487 Michael: See previous episode. 195 00:12:34,495 --> 00:12:35,515 Nikolay: Right, right, exactly. 196 00:12:35,515 --> 00:12:38,915 We discussed it already, but, again, like simple graph. 197 00:12:38,975 --> 00:12:43,205 What is the maximum age of the longest action right now? 198 00:12:43,235 --> 00:12:47,039 Excluding vacuum because actually regular vacuum it's by the way, it's tricky. 199 00:12:47,155 --> 00:12:49,912 a regular vacuum doesn't hold extreme horizon. 200 00:12:49,929 --> 00:12:53,895 It doesn't block anyone except we've analyzed it. 201 00:12:54,310 --> 00:12:56,014 Hold XME horizon. 202 00:12:56,263 --> 00:13:01,390 And if the vacuum is working to prevent transaction idea around it can block others. 203 00:13:01,390 --> 00:13:04,360 So in each area. 204 00:13:04,390 --> 00:13:07,030 There are interesting, ifs, right? 205 00:13:07,090 --> 00:13:11,620 If then, if then, so it's quite difficult to build ideal monitoring of course. 206 00:13:11,670 --> 00:13:13,950 But anyway, like I want to have a graph. 207 00:13:13,964 --> 00:13:17,000 do we understand the maximum edge of transaction? 208 00:13:17,000 --> 00:13:17,210 Right. 209 00:13:17,454 --> 00:13:19,670 usually people say, I don't know. 210 00:13:19,720 --> 00:13:21,430 Michael: what kind of number are you worried about there? 211 00:13:21,820 --> 00:13:22,480 Nikolay: right, right. 212 00:13:22,480 --> 00:13:23,710 In TP systems. 213 00:13:23,715 --> 00:13:29,080 And by the way, we forgot to mention that we, again, discuss TP systems monitoring for them. 214 00:13:29,085 --> 00:13:29,320 Right. 215 00:13:29,320 --> 00:13:32,274 Because for analytical systems, things are different. 216 00:13:32,283 --> 00:13:37,800 for OTP, I would like to have everything below, ideally , under one second. 217 00:13:37,844 --> 00:13:43,544 again, it's related to human perception, which is like we can distinguish 200 milliseconds roughly. 218 00:13:43,670 --> 00:13:52,174 So one second feels slow, but of course, , sometimes we need slightly longer transactions, a few seconds, but transaction one 219 00:13:52,179 --> 00:13:55,154 minute, if it can block someone else, it's already a problem. 220 00:13:55,154 --> 00:13:55,964 We should split. 221 00:13:56,060 --> 00:13:58,544 To batches, but, 222 00:13:58,544 --> 00:14:02,204 uh, we, we discussed it briefly last time. 223 00:14:02,444 --> 00:14:10,521 If you create an index, uh, unfortunately you hold the extreme horizon and of course, if your table is big, need to, to spend 224 00:14:10,521 --> 00:14:13,873 like sometimes an hour or maybe even more to build an index. 225 00:14:13,883 --> 00:14:19,163 And that's why partitioning is needed again, to have this time say. 226 00:14:19,475 --> 00:14:23,785 Michael: So we are, about halfway through our, nearly halfway through our, uh, dashboard. 227 00:14:23,995 --> 00:14:24,385 Nikolay: Good. 228 00:14:24,395 --> 00:14:27,756 So next thing is, um, , the ratio of error. 229 00:14:27,756 --> 00:14:28,866 So it's can measure. 230 00:14:29,106 --> 00:14:34,286 We, we already discussed this exact, , from PTA database, exact commit exec roll bank. 231 00:14:34,343 --> 00:14:35,933 How many transactions are committed? 232 00:14:35,963 --> 00:14:40,366 How many transactions are canceled and, uh, we can have a ratio. 233 00:14:40,401 --> 00:14:43,311 To understand the percentage of successful transactions. 234 00:14:43,338 --> 00:14:50,115 And it's interesting sometimes, I see like system produces like only 50%, or even fewer successful transactions. 235 00:14:50,195 --> 00:14:52,305 But developers said this is an intentional. 236 00:14:52,762 --> 00:14:56,346 this is by design and it triggers interesting questions. 237 00:14:56,376 --> 00:15:01,553 What negative consequences can be if application uses roll back too often. 238 00:15:01,553 --> 00:15:02,803 It's like separate 239 00:15:03,278 --> 00:15:08,233 Michael: But the main reason you want this ratio is to see if it changes from your baseline. 240 00:15:08,233 --> 00:15:12,099 So if you've got normally a very high ratio, you just want to tell 241 00:15:12,133 --> 00:15:12,423 Nikolay: Right. 242 00:15:12,429 --> 00:15:13,468 99%. 243 00:15:13,468 --> 00:15:17,878 And suddenly we have 70%, obviously it means we have a spike of errors. 244 00:15:18,088 --> 00:15:26,928 So we need to go to logs probably, and understand which errors, or if you use, extension called log errors, you can have counters, right inside database. 245 00:15:26,933 --> 00:15:27,853 So you can select. 246 00:15:27,859 --> 00:15:29,209 Aggregated by error type. 247 00:15:29,466 --> 00:15:38,857 So next thing is, uh, transaction idea up around, uh, separate metric, here people always like, okay, not always 248 00:15:38,857 --> 00:15:42,547 in 90% of cases, people forget about not exact, , idea up. 249 00:15:42,924 --> 00:15:43,884 they just don't check it. 250 00:15:44,024 --> 00:15:51,590 Like it's more exotic, like transaction idea around is exotic, but when it happens, it's a big disaster, but multi exec idea up 251 00:15:51,595 --> 00:15:56,607 around it's even more, it's even like very, very rare, very unusual, 252 00:15:56,757 --> 00:16:01,956 Michael: Is this the thing that you mentioned in the GitLab, blog post about, sub transactions? 253 00:16:02,047 --> 00:16:02,747 Nikolay: uh, briefly. 254 00:16:03,462 --> 00:16:05,982 Yes briefly there is connection to it. 255 00:16:06,042 --> 00:16:15,339 Yes, because, , if we use select for, share and sometimes select for update, we might have, increasing, uh, multi, exact ideas and 256 00:16:15,489 --> 00:16:22,072 risks of, uh, well, if we use foreign keys, we have not exact ideas. 257 00:16:22,072 --> 00:16:22,552 That's it? 258 00:16:22,576 --> 00:16:23,116 Michael: Of course. 259 00:16:23,176 --> 00:16:28,246 So are we talking about two graphs here, then one for like, what do we actually need for our dashboard? 260 00:16:28,822 --> 00:16:36,804 Nikolay: It can be one, but just the idea is we, we should not forget about this second ID and it also can be wrapped around. 261 00:16:36,866 --> 00:16:38,081 It has the same risk. 262 00:16:38,167 --> 00:16:42,677 So we should monitor both risks and, uh, alert, when it's already. 263 00:16:42,913 --> 00:16:44,773 Above threshold and so on. 264 00:16:45,403 --> 00:16:45,763 Okay. 265 00:16:45,763 --> 00:16:52,333 Then, the health of replication, again, like for this dashboard, we need only brief analysis. 266 00:16:52,333 --> 00:16:57,930 Like, do we have at least one standby, which is legging physical or logical? 267 00:16:58,087 --> 00:17:00,547 We, we should define our thresholds and. 268 00:17:01,267 --> 00:17:09,277 That's it like if we see a spike or leg also a problem you can get out of disc space event or something not good. 269 00:17:09,937 --> 00:17:11,847 Then, a few things on the left. 270 00:17:12,127 --> 00:17:16,142 By the way I realized we, in this list, we will provide this list. 271 00:17:16,142 --> 00:17:22,495 As I've said, we don't have, vacuum behavior autobi behavior and we don't have checkpoint or, and visual writer behavior. 272 00:17:22,765 --> 00:17:24,475 We have it, uh, in our systems. 273 00:17:24,475 --> 00:17:25,435 We have it on D. 274 00:17:25,730 --> 00:17:34,453 Two, but I think we probably should move it to dashboard one as well, because these two big components of Pogo, vacuuming and chick pointing. 275 00:17:34,513 --> 00:17:34,813 Right. 276 00:17:35,353 --> 00:17:37,453 So we should have it here. 277 00:17:37,758 --> 00:17:42,588 Michael: before you move on this one, I see you also had unused replication slots, which I thought was really interesting. 278 00:17:42,618 --> 00:17:50,868 I've heard of some issues where, like if, if you've got a static number of those, if, if it decreases, if, if something 279 00:17:50,868 --> 00:17:55,608 ends up using one of those replication slots that you weren't expecting, that's that could be sign of a problem as well. 280 00:17:55,608 --> 00:17:55,848 Right? 281 00:17:55,943 --> 00:17:57,683 Nikolay: The problem is that like, right. 282 00:17:57,713 --> 00:18:03,023 So we don't have leg per se here, but, uh, if slot is unused, it's accumulating. 283 00:18:04,028 --> 00:18:07,748 so it's easy to be out of this space soon. 284 00:18:08,528 --> 00:18:15,382 So, and use the, uh, slots are dangerous, definitely, but it can be, everything can be here combined on one or couple of graphs. 285 00:18:15,387 --> 00:18:21,347 So, but as I've said, we want to keep this, dashboard quite small so we can quickly overview everything. 286 00:18:21,470 --> 00:18:25,905 And then like, after application, I'd like to talk about legs related to archiving. 287 00:18:25,930 --> 00:18:27,961 Very often, people don't have it. 288 00:18:28,137 --> 00:18:29,547 They don't monitor the leg. 289 00:18:29,667 --> 00:18:32,007 Like how many walls are not yet archived? 290 00:18:32,307 --> 00:18:33,867 Why is this important? 291 00:18:34,042 --> 00:18:37,642 If you try to stop positives, we restart or just stop. 292 00:18:38,242 --> 00:18:40,102 And it has a lot of archived. 293 00:18:41,167 --> 00:18:49,717 you know, like when we shut down POG, there is so called implicit shut down checkpoint, and the same way PSG tries to archive them mold. 294 00:18:50,167 --> 00:18:57,517 So trans archive command of all painting walls and can take our, if we accumulate a thousands of walls, it happens. 295 00:18:57,791 --> 00:18:58,511 You wait 296 00:18:59,045 --> 00:19:05,005 Michael: Well, it's not just if you try, if you want to upgrade or something, it could also be if there's a crash, right. 297 00:19:05,065 --> 00:19:07,285 That it's how, how long is it gonna take you to recover? 298 00:19:07,515 --> 00:19:08,775 Nikolay: Well, recovery is different. 299 00:19:08,780 --> 00:19:10,905 If it crashed, it'll recover quite soon. 300 00:19:10,910 --> 00:19:12,895 It just, replay, redo, what's needed. 301 00:19:12,895 --> 00:19:13,375 And that's it. 302 00:19:13,735 --> 00:19:22,918 I'm talking about like, intentional restart or, or shut down, for example, if, uh, Petroni wants to perform switch or, or 303 00:19:22,923 --> 00:19:26,358 failover due to some reasons it tries to shut down the primary. 304 00:19:26,693 --> 00:19:30,593 until some version not long ago, because I told KKI about it. 305 00:19:30,593 --> 00:19:34,343 And it was a surprise because we had it on production in some system. 306 00:19:34,673 --> 00:19:41,283 So Petro failed to perform fell over because a lot of walls were, were, painting to be archived. 307 00:19:41,611 --> 00:19:47,711 and, uh, co KKU can fix it, in, in the recent versions of Petron upgrade, upgrade. 308 00:19:48,251 --> 00:19:49,751 It's quite, it's quite new thing. 309 00:19:49,781 --> 00:19:51,191 Uh, quite new bug fix. 310 00:19:51,401 --> 00:19:52,451 Well, it's not bug fix. 311 00:19:52,456 --> 00:19:54,671 It's like, uh, improvement, Petron. 312 00:19:54,671 --> 00:19:58,111 Doesn't wait, and like tries to perform fell over. 313 00:19:58,281 --> 00:20:01,591 There is a trade door because what what to do with these walls. 314 00:20:02,128 --> 00:20:07,358 I don't remember details there because if we don't archive those, this drops our, , Dr. 315 00:20:07,358 --> 00:20:10,268 Strategy disaster recovery strategy, right. 316 00:20:10,268 --> 00:20:12,489 We need to keep backups in good shape. 317 00:20:12,809 --> 00:20:22,054 So this is interesting question as well, but, , anyway, monitoring should have this number of painting walls, or also maybe the rate of our kit. 318 00:20:22,359 --> 00:20:30,336 how, how, like how many walls we archive per second, for example, it's very, very interesting metric as well, to understand like our. 319 00:20:30,425 --> 00:20:31,925 Michael: You've got that next on the list. 320 00:20:33,051 --> 00:20:33,651 Nikolay: Ah, okay. 321 00:20:33,651 --> 00:20:33,951 Yeah. 322 00:20:33,951 --> 00:20:34,251 Okay. 323 00:20:34,251 --> 00:20:43,694 Well generation rates and, uh, yeah, as I recently learned, if you have few walls per second, probably you won't be able to 324 00:20:43,694 --> 00:20:52,152 use logical replication because wall center will, will be, will set rate single CPU, , core and finally locks and deadlocks. 325 00:20:52,182 --> 00:20:55,602 Uh, well maybe they should not go together because. 326 00:20:56,322 --> 00:20:58,152 There's different things. 327 00:20:58,482 --> 00:20:58,842 Right. 328 00:20:59,172 --> 00:21:07,122 But, and locks, of course we like sometimes people draw both, um, exclusive and Sherlocks there, there are many kinds of locks here. 329 00:21:07,127 --> 00:21:12,702 We talk about, , like heavy locks, not lightweight locks or latches and so heavy locks. 330 00:21:12,702 --> 00:21:16,222 So we are locking rows and locking, tables. 331 00:21:16,461 --> 00:21:24,681 And here, sometimes people try to draw everything, but we should focus on, uh, maybe only on exclusive locks, 332 00:21:24,686 --> 00:21:28,641 which can be reasonable for blocking others first reason. 333 00:21:29,031 --> 00:21:33,361 Well, it's also like there are many , problems there. 334 00:21:33,541 --> 00:21:36,671 This, this chart alone can be difficult to implement properly. 335 00:21:36,671 --> 00:21:42,371 But, uh, anyway, we, we want to have something, I, I just saw sometimes application engineers. 336 00:21:42,621 --> 00:21:44,671 They say, oh, we had a spike of locks. 337 00:21:45,061 --> 00:21:48,031 You look at it or you have had spike of share locks. 338 00:21:48,031 --> 00:21:51,541 So it's okay because you had some deployment it's normal. 339 00:21:51,871 --> 00:21:52,651 It's not a problem. 340 00:21:52,981 --> 00:21:53,401 So. 341 00:21:53,559 --> 00:21:56,049 this chart is also like interesting. 342 00:21:56,369 --> 00:21:57,299 What, what should be there? 343 00:21:57,419 --> 00:22:05,706 And that locks, of course, we want to understand how many deadlocks, so many other things are missing here, but I'm, I'm not a big fan of 344 00:22:05,706 --> 00:22:16,259 combining physical and this, this logical, like, for example, in data, doc, if you check database monitoring, you will see CPU combined with TPS. 345 00:22:16,619 --> 00:22:17,669 It's a mix of everything. 346 00:22:17,674 --> 00:22:19,289 It's Not not right. 347 00:22:19,649 --> 00:22:20,669 Host stats. 348 00:22:20,729 --> 00:22:24,089 I would like to see separately from database stats. 349 00:22:24,889 --> 00:22:25,579 What do you think. 350 00:22:26,089 --> 00:22:26,779 Michael: Interesting. 351 00:22:27,379 --> 00:22:36,398 Well, I think in the early days I've seen a lot of teams get really far looking at application stats and then adding some database things into it. 352 00:22:36,518 --> 00:22:39,608 They don't have some of these like real scaling issues, I guess. 353 00:22:39,638 --> 00:22:44,198 Uh, I think a lot of people implement monitoring after a big issue after a big outage. 354 00:22:44,198 --> 00:22:46,778 They do they think, ah, maybe we do need it now. 355 00:22:47,118 --> 00:22:51,438 so probably people put this in, in general, uh, a little bit too. 356 00:22:51,845 --> 00:22:59,405 but yeah, I can see, I can see an argument for keeping it where the application monitoring is, especially from the, the query performance 357 00:22:59,405 --> 00:23:01,295 point of view, at least, especially from the performance monitoring. 358 00:23:01,505 --> 00:23:05,645 Maybe not so much from the troubleshooting, we've had an outage perspective. 359 00:23:05,974 --> 00:23:06,274 Nikolay: Right. 360 00:23:06,274 --> 00:23:14,494 Well, well, again, we just didn't discuss here, uh, query analysis of it's another half an hour, at least it's 361 00:23:14,494 --> 00:23:17,194 like, it's very interesting and very deep topic, but. 362 00:23:17,811 --> 00:23:24,671 I also like, forgot to mention that, uh, we have, we took PG watch too, from cyber tech. 363 00:23:25,271 --> 00:23:31,516 And, uh, I remember when we first, uh, reviewed it, it didn't feel well like strange dashboards to us. 364 00:23:31,521 --> 00:23:36,941 Like very, very, like, Like you explain, oh, why, why not add this? 365 00:23:36,941 --> 00:23:38,291 Why not add that? 366 00:23:38,561 --> 00:23:42,881 But the fact that it's open source, good components like Grafana and so on. 367 00:23:42,881 --> 00:23:45,761 Like, it can be, it can use Pogs and timescale. 368 00:23:46,241 --> 00:23:46,901 So we. 369 00:23:47,881 --> 00:23:54,371 Vitality from it team implemented, the, initially the set of dashboards following this philosophy. 370 00:23:54,641 --> 00:24:02,471 So dashboard number one is for quick and very shallow troubleshooting, understanding which areas need further analysis dashboard. 371 00:24:02,476 --> 00:24:03,251 Number two. 372 00:24:03,671 --> 00:24:06,971 More interesting things for DBA dashboard. 373 00:24:06,971 --> 00:24:10,151 Number three is query analysis and it's available. 374 00:24:10,151 --> 00:24:11,741 So it's open source available. 375 00:24:11,746 --> 00:24:17,756 And, uh, recently another contributor helped us to support, , timescale version. 376 00:24:17,786 --> 00:24:19,796 So POG and timescale can be used as storage. 377 00:24:20,816 --> 00:24:21,926 And I like to. 378 00:24:22,131 --> 00:24:29,061 Like I encourage everyone to consider it as a second secondary monitor probably already have something like data doc. 379 00:24:29,251 --> 00:24:29,611 Right. 380 00:24:29,921 --> 00:24:33,101 Which is like usually, or, or anything else which usually like 381 00:24:33,651 --> 00:24:33,871 Michael: APM. 382 00:24:34,501 --> 00:24:37,741 Nikolay: like organization already adopted some tool. 383 00:24:38,116 --> 00:24:40,576 For everything, not for databases, not for Pogo. 384 00:24:41,356 --> 00:24:48,651 And of course it's worth having it everywhere because it's like unified solution, but, , it's missing a lot of things. 385 00:24:48,711 --> 00:24:54,752 And I hope, uh, I convinced our listeners today that there are many things that should be present there. 386 00:24:55,592 --> 00:24:58,462 So pitch watch two can be added like a secondary monitor. 387 00:24:59,129 --> 00:25:03,095 Very gentle without big observer effect. 388 00:25:03,232 --> 00:25:14,642 and that can provide a lot of insights for database experts and actually not, not only experts this TPS QPS plus latency, this, throughput plus 389 00:25:14,642 --> 00:25:19,022 latency it's it's alone already, something very good, which many, systems 390 00:25:19,022 --> 00:25:19,442 missing. 391 00:25:19,696 --> 00:25:20,476 Michael: Yeah, absolutely. 392 00:25:21,036 --> 00:25:27,736 So yeah, so I guess if you don't have some of these things, , maybe look into how, how much work it would be to add them 393 00:25:27,766 --> 00:25:30,466 and it, hopefully it would save you or make it a lot easier. 394 00:25:30,466 --> 00:25:33,206 Next time you have a big issue, to spot what's going wrong. 395 00:25:33,554 --> 00:25:33,974 Nikolay: Right. 396 00:25:33,974 --> 00:25:43,104 And if you develop, uh, your tool in, in house or your developer of monitoring this, uh, list, it's a product of community. 397 00:25:43,745 --> 00:25:49,835 like several months of work we discussed, I discussed it on Twitter either during our sessions in Russian community. 398 00:25:49,835 --> 00:25:59,275 And so on, this list was like, I think it's very, very useful for like checklist for everyone who wants to develop great progress monitoring. 399 00:25:59,785 --> 00:26:02,425 So please use it as a reference. 400 00:26:02,785 --> 00:26:03,745 What should be added. 401 00:26:05,130 --> 00:26:05,550 Michael: Awesome. 402 00:26:05,890 --> 00:26:08,980 Nikolay: I think it's, I think it will be useful, right. 403 00:26:09,340 --> 00:26:12,010 Probably one day we will discuss query monitoring as 404 00:26:12,090 --> 00:26:12,450 Michael: Yep. 405 00:26:12,730 --> 00:26:14,800 Nikolay: I have something to say about it too. 406 00:26:15,755 --> 00:26:17,465 Michael: L definitely part two coming. 407 00:26:17,701 --> 00:26:18,151 Brilliant. 408 00:26:18,211 --> 00:26:19,921 Well, thanks everyone for joining us again. 409 00:26:20,161 --> 00:26:21,301 Uh, we really appreciate it. 410 00:26:21,331 --> 00:26:25,201 Let us know if there's anything else you want to make sure we covered or anything you think we missed. 411 00:26:25,561 --> 00:26:26,611 Uh, be very helpful. 412 00:26:27,041 --> 00:26:33,381 And yeah, check out the show notes for, well, I I'll include links to all the tools we mentioned or, various things as well, including this list. 413 00:26:33,717 --> 00:26:34,017 Nikolay: Yeah. 414 00:26:34,047 --> 00:26:36,927 And thank you again for feedback feedback. 415 00:26:37,797 --> 00:26:41,247 Very important thing to have Twitter and other places. 416 00:26:41,757 --> 00:26:52,557 And please subscribe like and share, share, share everywhere in, in your social networks and groups where you discuss engineering and databases. 417 00:26:52,557 --> 00:26:53,037 And so, 418 00:26:53,496 --> 00:26:54,096 Michael: Wonderful. 419 00:26:54,096 --> 00:26:54,816 Well, thank you, Nick. 420 00:26:54,816 --> 00:26:55,176 Eli. 421 00:26:55,396 --> 00:26:57,526 good to see uh, speak to you next 422 00:26:57,737 --> 00:26:59,647 Nikolay: Thank you Michael, till next time. 423 00:26:59,917 --> 00:27:00,097 Bye. 424 00:27:00,097 --> 00:27:00,367 Bye. 425 00:27:00,586 --> 00:27:00,856 Michael: Cheers. 426 00:27:00,856 --> 00:27:00,916 Bye.