 Great, my name is Kevin Kempter. I work for a company named consistent state Today, I'm going to be talking about monitoring and statistics in Particular we're going to do a walk through the majority of the postgres system catalogs. This is not a talk about by our tool or By our GUI. It's a deep dive into the postgres stats and a little I'm going to try to keep it somewhat light We're not going to go column by column by column and read the document to you or read the docs to you guys can do that yourselves I am going to highlight Some of the key columns and some of the things that we do on a day-to-day basis with the system catalogs That really helps us figure out what's going on with our clients databases and therefore Maybe pass some truly relevant knowledge your way as opposed to Hey by our tool So before we jump into it a little shameless plug for the company consistent state We are a basically a postgres centric DevOps shop We help clients not only with postgres Consulting and managed services, but we get into The full DevOps stack so we get into hardware and networking and application connectivity It's all database centric, but you can't really be an effective DBA or effective database admin company Without getting into the DevOps space and really helping your clients and and having some sort of knowledge when you come to the Table about DevOps. So when you throw out a solution, it's thought through from an infrastructure perspective as opposed to Hey, we use this in the past. So maybe it'll work here kind of thing we Have a number of managed services in fact managed services are sort of What we really try to focus on or it's sort of our showcase We do consulting and we also do training, but managed services. I think our our Primary efforts if you will we have a best practices review that's sort of an extended DevOps based health check. It's a three week effort. It's for a fixed cost We have a conversion kickstart. So if you guys are migrating from Oracle or SQL server or even my SQL We will come in for a fixed cost It's also a three week effort and we'll take one of every one of your constructs We'll take a table and a user-defined type and a store procedure and a package and so forth and Actually convert it for you and show you here's how you replicate the behavior that you had in the past Going forward with postgres and we also throw in some Here's what your architecture might need to look like as opposed to what it looked like before Now that you're running on the postgres platform We have a total care service where we effectively take over your prod database and just whatever it needs We take care of it. We don't bill hourly. It's just a fixed cost We have a product of trending service where it's just monitoring and we provide feedback And then we have a devops service where we get engaged with companies early on and try to drive their architecture In such a way that three or four or five years into their life cycle They're not saying what the heck were we thinking or now we need to migrate everything to postgres or whatever The if might be we try to help drive that architecture from a devops perspective So they can scale and they can get the performance they need and they can split off reporting down the road Just giving them some guidance in terms of real Planning for the future based on our experience in the field We also do training. We do live training. I obviously speak at the conferences and we do online training so Lastly we do traditional consulting we can engage hourly and do you know architecture and database design and admin tasks and so forth So enough about my company plug monitoring and stats so Monitoring stats for postgres is going to come down to a couple of key areas One is the postgres statistics collector and the statistics views Another is the database logs and the OS Tools that we have available and then lastly there are some third-party tools that we generally use on a regular basis And we'll talk about those at the end in particular A tool called PG badger and another tool called PG clue the database logs Do take a little bit of tweaking in terms of your config file to make sure that you're getting enough information logged into the database logs However, in general, we should be monitoring our logs in some fashion if nothing else put together a little grep script That will grep for certain keywords and send out an email if it gets a hit It's really important to be looking in your logs for Fatal keywords and error keywords and sometimes even the warning keywords Postgres will give you hints for example on the bottom there if your checkpoints are occurring too frequently That has to do with your checkpoint timeout setting And your checkpoint warning setting so if your checkpoint warning is set to 30 seconds Which is the default then if you get more than one checkpoint within a 30 second time frame you'll get these Warning log entries and it'll give you the hint that hey, you know your checkpoints are occurring too frequently Just as a side note. We like to set our checkpoint timeout to something more like 30 minutes Therefore giving the background writer time to do its job And we also like to set checkpoint warning to the same thing which set checkpoint timeout to the reason is if You're running multiple checkpoints in a 30 second time frame as a DBA. You know, you've got issues It's like, you know, somebody throwing out the obvious Whereas if you've said it to say 30 minutes and you're running multiple checkpoints within 30 minutes It truly is an early warning as opposed to oh by the way as your database is going down in flames We're running too many checkpoints. So it gives you time to look into your checkpointing system and figure out You know, are we truly running more checkpoints than we should or is it a one-off thing because of a spike and so forth? So leverage your logs. It's really important to not only leveraging some logging capability But also be monitoring those logs I can't tell you how many clients we've gone into and There's been a some sort of critical error happening for months and nobody bothered to ever look at the logs Which of course none of you guys would ever do but it happens so Like I mentioned the checkpoint business we can look for application errors in the logs Long-running queries if we configure the engine properly meaning we set log min duration statement to some value Therefore any queries that run longer than that value in terms of milliseconds We could log for example all the queries that run longer than half a second And then if we're logging the queries appropriately We can start to use tools in particular like PG Badger, which will give us some real insight as a Post-process or trailing indicator set of metrics in terms of what our database internals are looking like on a day-to-day basis By the way, if anybody has any questions feel free to interrupt me. I tend to Rambel and ramble and then jump on to the next slide. So Just throw up your hand and we can deal with questions as we go When it comes to monitoring the OS itself, I'm assuming we're running on Lennox here But obviously Postgres runs on Windows and runs on multiple other platforms Most platforms Lennox VSD Mac OS will be able to leverage these types of utilities PS and top IOS that and net stat can help us figure out Not only if we're having some performance issues where the bottlenecks are actually happening and it'll also help us if we can put together some scripting and set it up in cron and Fire out some email reports on a weekly basis. We can figure out Are we moving closer and closer to a bottleneck and sort of headed off at the past as opposed to waiting for it to become an issue? The ps command I'm assuming a fair number of you are pretty familiar with it We can grep for for example Postgres and I can see all of the Postgres processes that are currently running if I had a Thousand sessions each launching a query for reporting reasons. I would see a Process for each of those thousand queries The top command that will also help us figure out is Postgres Using the most resources on the system, which usually it is But is it more than than usual? We can look at the amount of memory that's being leveraged and the amount of swap space if any that's being leveraged And this can help us figure out from a memory slash resource usage perspective Do are we Experiencing a bottleneck or are we trending towards a bottleneck? I'll stat is a great utility in this case if I run I'll stat To space 5 it'll run I'll stat five times with a two-second delay in between each run I'll stat gives us some great information in particular It can help us figure out if we're experiencing a bottle experiencing a bottleneck at the CPU Level or at the IO level when we're looking at the device stands for the CPU Metrics it'll show us the percentage of CPU utilization at the application level At the with nice priority it shows us the system level. It shows us the CPU IO CPU requests. We're actually waiting on an IO operation it also shows us the percentage time spent in Voluntary weight and this is generally a virtual machine issue Or it shows us the percentage of time idle so we can Leverage these values and try to figure out If I run say an IO stat to five and then look at the differences between each of those two second delayed runs I can figure out what is my IO queue look like in general and then What's often more important is I can also look at the IO stats and figure out per device How much data am I reading and writing to that IO device and does that advice appear to be? Hitting its maximum theoretical or anywhere close so we can start to monitor things like per device How many blocks were read and written per second as well as the total number of blocks that were read and written In our IO channel, so this will really help us figure out Do I have an IO bottleneck or am I trending towards an IO bottleneck and hopefully we can catch it early enough that we can start to spin off some external table spaces on separate mounts and move some of our high-use tables and Really be able to keep the database from becoming an IO bottleneck Which is of course the number one bottleneck that you're going to have in any relational database postgres is no exception It's all about IO in terms of performance Well, it's not all about IO, but it's 80 percent about IO And here's an example and if you were to tally up the numbers on our database specific volume Which you just have to know which volume your databases on we've read a total of 360 blocks, which is 180k and we've written a total of 29,200 and some blocks, which is 145 meg and that's just within those, you know Basically 10 seconds for during that run So if we're running writing a hundred and some mag every 10 seconds We can calculate that out and look at our throughput on an hourly or a daily basis and try to figure out Not only do we how do we head off this IO issue? But what are our needs going to be a month from now or six months from now? Assuming that our traffic profile is on a Somewhat linear scale, which of course it's not but if we assume that we're kind of addressing worst-case scenario Net stat will also show us information if in fact you're running into issues where Your master and slave and a replication scenario or having difficulty Communicating or applications or having issues logging into your postgres database We can start looking at net stat and figure out Is the server actually listening where it's supposed to be listening? Is there some issue in the network layer that's causing postgres not to lessen even though maybe postgres is up and running? This is one of the steps you would you would lean on once you validated the obvious for example, we see many many scenarios of fire up a postgres database and Help us we can't connect and we go look at the config file and listen addresses is still set to local host So, you know, we need to look at the obvious But once we've dealt with the obvious then we probably want to start looking at net stat next we'll look at the postgres statistics collector and Likewise the postgres statistics catalogs or the postgres statistics views here's some of the the Postgresql.com related values that are specific to the statistics collector track activities Simply enables the collection of information on active currently executing commands So the default is on and we obviously want that on if we're going to be able to track Information about active queries the track activity query size defaults to 1024 This is the max size that it will show us for example If I've got a query and I'm looking at the PG stat activity table and this is you know an oracle style 14-page query with 22 nested tables within it I'm only going to see the first 10 1,024 characters now You can tweak that and you do need to restart your database But in most cases you can figure out from the first thousand characters what specific query that is Likewise, there's a track Counts variable that it's also defaulted on which also allows Information to be gathered in particular about the auto vacuum daemon And then those counts are uploaded into in particular the PG stat user tables or PG stat all tables There's also a PG stat system tables, which are all basically three different views based on the same Underlying query just with different filters the all tables of course is all tables and the user tables being only the user defined tables and Likewise the system tables There's a track IO timing value that is generally by default. It's off and generally. It's a good idea to leave it off We have gone in and turned this setting on this actually tracks IO timing for your active Queries that are running against the database However, we only turn this on when we're trying to do some debugging. We're running IO stat We're we're kind of seeing a bottleneck, but we can't quite figure out which queries are the culprits for this bottleneck Then we come back in and we'll turn the timing on and it'll help us sort of make that last connection to What session or what application or what set of queries are actually causing us the grief in this scenario? And then there's a track function It's an enum basically allows us to track timing for procedural language functions so once we've Defined the settings that we want in our config file then we can start to look at the PG System catalog views and we can start to look at activity that's going on within our database and have some real insight Into what's actually going on on a day-to-day basis? PG stat activity is probably the most common used view Which basically shows us one row for every session connected to your database currently current active sessions It could be an idle connection. It could be idle in transaction Might be an actual query running, but it'll show us all of the active sessions Here's sort of the key columns as we go through these slides note that in some cases I list all the columns for the table, but I don't always list them all because I don't want this to turn into Let me read the columns to you I just want to highlight some of the key things we like to do and Allow you guys to go back to your shops and and maybe have some new queries that you can run that are truly helpful So in this case we get the OID of the database That this session is connected to we also get that database name We get the process ID, which is in fact the OS process ID So if I'm looking at PG stat activity and I see that some rogue developer has launched this giant query at the You know the middle of my core business hours, and it's been running for 18 hours he launched it say last night at midnight I can grab that process ID and go out to the command line and simply run a kill command It's I there's also some postgres built-in functions that allow me to cancel that query or To terminate that query, which is a little bit more forceful However, all three methods are fairly safe as long as you never run a kill Dash nine if you run a kill dash nine The next time that the postmaster tries to communicate with that child process and sees that it's gone There is a potential data integrity issue therefore the postmaster will commit suicide and Forced the database to go into recovery mode the next time you start it it'll walk through the wall files Therefore validating or ensuring that data integrity is still intact. So never ever ever do a kill-dash nine We get the OID in the name of the user the database role that's running or that's Executing this particular connection. We get an application name Application name is fairly new in the last few versions if I'm running. Yes There's some other subtle differences because they're both safe But when you run the functions either the terminate or the cancel back end It actually tells the postmaster We're getting rid of this process and allows the postmaster to do the housekeeping necessary to make sure that data integrity is intact It all comes down to date integrity A kill is safe, but as we move forward in versions. We're kind of moving away from Just doing a kill And really it's better to do the functions. Oh, right. It'll restart of course, but it still commits suicide Any other questions? So I was saying the application name for example if I'm running a PG dump And it's doing a copy out from all my tables in the current database. I will see the application name as PG dump There are ways for example if I'm using JDBC I can plug into my connection string the application name and therefore I can start to filter my PG stat activity on the Various applications that I might have connecting to my database We also get the IP address of the incoming client as well as the host name We get the client port client port will sometimes be minus one meaning it's a local connection or a Unix socket connection as opposed to a TCP IP connection Also, we get the the back-end start time the transaction start time and the query start time So with these three columns in particular I can start to run some queries and figure out how long is this session been around or how long is this query been running How long is then this transaction been in a non-committed state? For example, I could select, you know now minus query start time and figure out How long is this this active query been running and I can put that in a script and start to look for queries that run longer than x X might be say three hours and I can start to safely kill some of these queries that might start to create backlogs Because other queries start to wait on these transactions if they're running too long Also, there's a state and it'll show me that the current connection is either active meaning It's actively running a query. It might be idle or idle in transaction Idle in transaction aborted and so forth idle in transaction is something to keep an eye on especially if you're doing some of the non-streaming replication types of Replicating for example with Sloney I think P.G. Logical might have some similar issues meaning in Order for something like Sloney or P.G. Logical to work order is very important the order of the transactions Serial style has to be the order that those transactions are applied on the slave and if I've got a Transaction that's been started and then went idle for a significant period of time all the transactions that came after that begin We'll start to sort of backlog in my replication queue because it's waiting on this transaction We saw issues. We did some work with Stanford University's astrophysics lab on a NASA project and they were Distributing this data about the solar data observatory project all these images of the Sun and these scientists and these labs would go in and They'd start a transaction and then they would update a couple tables and then they would read some data And they would go off literally for two and three weeks and analyze this data and leave that transaction open And it just wreaked havoc with Sloney So we had to do some reeducation about you know close your transaction or we'll close it for you kind of thing There's also the last time the state changed which might or might not be important to you The actual query text is in the table and the waiting column as a Boolean is pretty critical meaning I can go in and look at how many of my queries are actually in a waiting state Therefore I can figure out who are they waiting on and how long of that been how long has that query been running and Therefore decide if I need to take action or not Here's an example of a query that will show me if I have multiple waiting queries This will show me the query that other queries are waiting on meaning. This is the blocker. I Could put that query for example in a script and fire and then up as a cron job And I could run it say once an hour and look for do I have waiting queries? And who are they waiting on and has that query that everybody's waiting on been running in excessively long time? And if necessary automatically kill that process Some of the things that we typically do with PG stat activity or things like validating or reviewing running processes obviously Looking for blocked and waiting queries monitoring for idle connections We don't want to run into scenarios where our match connections has been reached and 80% of those connections are sitting idle Likewise monitoring for long running queries looking for how long these queries have been running and so forth Another key table is our PG stat replication table if in fact you guys are doing built-in streaming Replication or you're using rep manager to manage streaming replication. This table will be critical It contains one row for every wall sender process. That's replicating to a downstream slave some of the columns It the table includes connection information columns the PID and the username and the application name and so forth It also has the back-end start time. It has the current wall sender state The sync priority meaning what's the priority of this standby in terms of Who's the next in the in the list to be? Promoted to be the master in particular with rep manager. You can define who the What the priority is and rep manager when it fail over is necessary will automatically pick the The database server or those standby. That's the highest in priority There's also a sync state which gives us the synchronization state of the standby server not of the master We also have a number of location-based columns and these are key in figuring out how far behind and in general How efficient is my standby database running? In most cases it's a non-issue but if we're leveraging that standby as a read-only slave and we're doing Significant amounts of for example reporting queries against that database We can start to create scenarios where that database is lagging and we might even get into the scenario where we're getting the wrong answer Because there's transactions that haven't been applied because that these other queries are running and blocking or slowing down replication So there's a number of ways we can address that the first thing we need to do is basically monitor Another thing we can do is we can actually pause replication Let's say we have a suite of reports that we're on from say 2 a.m. And they don't finish until noon We can pause replication So we have a static snapshot at midnight and run our suite of reporting queries and then unpause Replication and have the slave catch up and then the next night go through the same process again. Yes Yeah, it doesn't actively block But what it can do is because it's flooding our IO channel It can it can create sort of a pseudo block it because we have IO wait happening So we have a sent location, which is the last transaction Log position that was sent to this standby. We have a right location We have a flush location and a replay location so we can use those to try to figure out You know from a writing perspective or from a applying my transactions from the master perspective What's the state of my standby? And here's a couple of example queries this first one We're comparing the last X log receive and the last X log replay locations and we're going to get back Location IDs which aren't very helpful for us other than we can see are they the same or not? If they're not the same then obviously there's some sort of lag it might be milliseconds, but there's some sort of lag going on Here's an example of We want to check the timestamp of the last replay transaction on the master So we would run this and actually get the last transaction Timestamp, I'm sorry. I get the timestamp of the last transaction replay So we can do for example now minus this and see what's my delay looking like and That's specifically for the replay so meaning The the master is going to send the right and then it's going to flush that right and then the standby is going to apply Or replay that transaction onto itself. So replay is sort of the last stage At which point that transaction has been applied to the standby server Also, here's an example of checking how old the current How old from current or from now the last replay transaction is and like I just mentioned now minus PG last transaction replay timestamp in This case were 3.7 seconds delayed Here's an example of checking the standby lag in bytes and then finally we've got an example of checking the standby lag in seconds Checking it in seconds is a sort of a two-stage process first I need to check my replay location my my current transaction log insert location And the replay location from the PG stat replication table on the master if in fact that lag time is zero Then I'm fully caught up, but if it's not zero and only if it's not zero Then I need to run the second query on the standby to truly figure out what my lag time in seconds looks like There's a table called PG stat archiver, which is also a key table that we use very often We'll go into clients and often set up wall archiving for a variety of reasons One would be for doing point-in-time recovery another might be to make those wall files available to a standby server Therefore if the standby ever got too far behind IE the transaction logs the active transaction logs on the master of wrapped Then we can tell that standby server to automatically revert to wall shipping and once it's got itself caught back up Then it can go back to streaming replication That was something we did on a pretty regular basis. However With 9.5 and even 9.4 we can use replication slots and that eliminates the possibility because the master via replication slot will be aware of which downstream slaves are in what state meaning what what Transactions they have yet to pull and it will actually keep the wall farther I'm sorry the transaction log files around longer until all the downstream slaves have gotten all the data that they need So replication slots kind of eliminates that but we still do the wall archiving for point-in-time base recovery the wall archiver the PG stat archiver table Shows us basically what's happening with respect to archiving individual transaction logs So it shows us an archived count. This is a cumulative count. So unless we do a stats reset If we check it on Monday, and it's a thousand and we check it on Tuesday, and it's 2,000 We have to figure out the diff to see how many files were actually Archived on Tuesday unless we reset the stats one of the tools we use is PG Clue Every time PG Clue pulls a set of statistics it actually executes a stats reset Therefore when it pipes the data through its R or D engine It doesn't have to do any Sort of diff math to figure out the right values Also, there's a a The name of the last wall file that was archived and the time stamp of the last successful operation There's a failed count the name of the last failed file and the time stamp of the last failed file So if we do point-in-time recovery, and we're archiving our while files We will almost always deploy a script that at minimum Looks for the failed count and the last failed wall file in the last time stamp So if in fact we start to get wall archive failures we can alert Pronto as opposed to waiting for some other Side-effect catastrophe to take place for example your database file system fills up because It can't archive the transaction logs. Therefore it keeps them around in the PGx log directory So we can use that as an early warning system to figure out Are we have an issues and likewise? We can Basically assume if we're not getting the error emails that our wall archiving is happening properly And it gives us a warm and fuzzy that Things are working as expected Obviously we would use this table to validate the wall archive process itself Debugging wall archive failures. This is helpful And also looking at our throughput we can monitor our wall archive throughput on a day-to-day basis and start to build a Trend and then therefore start to look at from a scalability perspective Where does our bandwidth? IO and disk base need to be 18 months from now and start to planning start planning for it today as Opposed to start planning 17 months from now for it There's another table PG stat BG writer This table gives us insight into checkpoints and the background writer and the in-memory buffer pool It has time checkpoints requested checkpoints the right time and sync time for checkpoints It's also got some background writer particular values in particular max written clean as a value we look for if the background writer wakes up and writes the Maximum number of pages. It's allowed to write and then has to go back to sleep Even though there's more dirty pages this max written clean value will be incremented Therefore we can start to look for issues in terms of keeping our checkpoint system tuned. Well over time Obviously we're looking for IO for checkpoints and buffers that are actually being written by back-end processes meaning Somebody ran a select star from a big table and there were not enough Pages in the buffer pool that were clean so it has to flush out dirty pages at runtime We can track when that's happening as well. Therefore manage basically manage our buffer pool Appropriately there's a PG stat database table and we're not going to get through all of them. I Well post these slides up for so everybody can download them the PG stat database table We generally use to look for what's my hit ratio because I can look at my blocks read and my blocks hit Meaning how often did I have to go to disk versus how often did I find those pages in memory? Therefore I can use these hit ratios to figure out is my memory sized Appropriately are my queries as efficient as they should be and so forth We're running up against time anybody have any questions Yes Yeah, so the two tools we use more than anything our PG badger and And and PG clue so PG badger is a log parser. It's open source download it deploy it and Basically PG badger. Let me just jump over there and show you. I think this will be Very helpful So PG badger once you parse your log file will give you an HTML based report and it'll show us things like the Longest-running queries and it does the top 20 by default you can modify that it'll show us the most frequent queries And it'll show us the queries that generated the most temp files because we ran out of our sort space and had to do our sorting on disk and Which queries are tied to those temp files and what is our connections look like over time and how many queries per second? Were we running and so on and so forth and here's just a few examples of What the PG badger report will show us also the PG clue report It's a slightly different tool in that instead of parsing the log file It actually connects to the database and it pulls the stats Let's say we pull the stats on a daily basis once every five minutes And then at the end of the day say at midnight we actually generate the report It also generates an HTML based report that looks like this and it'll show us our hit ratio Basically, what's our caching rate look like and it shows us information about the background writer and our Replication lag it'll show us any errors it'll actually look at the log file and see if there's any errors going on PG badger does that as well It shows us the system metrics the memory and IO and and so forth It'll show us spikes and we can go back and investigate certain events It's actually a top-notch tool with the one downside that it's a trailing indicator utility It's not really a alert me now when something happens utility. Yes Yes, it is Yeah So what I would do is first I would look at the total breadth of what all is running in stats activity Are there any other queries that are waiting because it could be causing contention because another query is doing Some of its prep work before it needs to hit whatever is being updated by the query that's running so long Then I would start to look at the system stats I would look at the IO in particular and see are we flooding our IO channel more often than not We find that it's actually an IO issue. Maybe there's a checkpoint running or maybe the background writer We've we've configured it to be too aggressive and it's running too often. Therefore. We're flooding our IO channel. We don't have Quote waiting queries, but they're waiting none the less because they're waiting for requests in the your IO queue Or it might be requests. We're waiting on in the CPU queue That would be what I would do is first see our is anybody actively waiting and then figure out what all tables is everybody hitting and are there some common tables and Then look at my IO channel and my CPU queue to see if I'm blocking at the system level Any other questions? Yes We don't really have a cookbook because it's it's different in every scenario But we do get into a lot of scenarios where for example, we're helping Time Warner Cable do They're moving everything into open stack and from an open stack perspective We're moving everything we need in terms of a standard master slave replication within a Particular region and being able to fell over within that region as well as fell across regions and we're moving it all into puppet So it's all based on roles and I could probably provide some Some recipes if you well in terms of roles, but that's not necessarily the full picture So all we need to do to fell over is update that puppet role and then puppet takes care of the rest for us kind of thing Yes Yeah, actually I like datadog Which you know, they should thank me for the plug we actually use datadog for some of our clients and We actually like New Relic as well. Those are our two favorite sort of New Relic New Relic is is real light when it comes to the Postgres plugins But it's enough that if there were an issue that I need to be alerted of right now It's got enough information that we can get those type of alerts from New Relic And of course with datadog We can create our own custom alerts and be alerted on anything we want and their Postgres plugin is quite extensive Any any more questions? Yes Not not not per se Information that we would find in the system catalogs There is monitoring of that nature that you can do and we do it But it's more related to you know Are my backups running properly and can I recover and so forth and usually it's a custom Scenario we're out of time apparently. Hey, thanks everybody. I appreciate your time