Stuck Transaction IDs, pgBouncer Auth, UUIDs, Monitoring | Scaling Postgres 46

In this episode of Scaling Postgres, we review articles covering stuck transaction ids, pgBouncer authentication, sequential UUIDs, and monitoring.

Content Discussed

• Zombies!! Dealing with a Case of Stuck TransactionIDs
• pgbouncer authentication made easy
• Sequential UUID Generators on SSD
• Webinar: pglogical and Postgres-BDR Update [Follow Up]
• Monitoring Postgres with Prometheus
• Using Prometheus and Grafana to build a Postgres Dashboard
• Looking Back at Postgres

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about stuck transaction IDs, PGbouncer authentication, sequential UUIDs and monitoring. I'm Kristen Jameson, and this isscaling postgres episode 46.

[00:00:22] All right, our first article is Zombies dealing with a case of Stuck Transaction IDs. Andthis is from Richen.com. And basically the story is that everything is seemingly working well witha PostgreSQL database. But then you start seeing a warning and a hint. And the warning isoldest X Men is far in the past in hint close open transactions soon to avoid wraparoundproblems. And he says, okay, this is strange. There's no open transactions. And looking in PGStat activity, you don't see anything that leads you to what's going on. And then he proceeds todiscuss the panic you go through and you start looking everywhere in terms of auto, vacuum,freeze, maxage, Ralph, frozen ID prepared statements, I mean, nothing. You try vacuuming thewhole database, no difference. And even restarting the database doesn't help. Now, the origin ofthis problem lies in PG replication slots. So someone set up a slot but wasn't using it. Nowtypically you're going to get your wall files growing extremely large, and here he says, quote,we've seen this problem before where the PGx log with many wall files would breed down theservic as it would exceed the size of the partition that it's on. But in this particular case, it wassufficiently sized. The problem didn't bubble up there or wasn't seen there. And basically overtime, Xids continue to advance and quote, the stray replication slot is waiting for a subscriber tosend all its stashed away data to, leading to the warning and hints as seen above. So basicallythe conclusion of this he says, is quote, clean up after yourself. Basically, if you're not using aslot, you need to delete it or you're going to usually run into disk space problems. But as shownhere, it can actually cause wraparound problems, which requires you to shut down your wholedatabase to do a vacuum to resolve the situation. So definitely a good public service message tokeep in mind.

[00:02:27] The next post is PG. Bouncer authentication made easy. And this is from CyberTechpostgresql.com. And basically it's talking about a Pgbouncer, which is a connection pooler forpostgres. And basically once you get up to more than 200 or close to 300 connections onpostgres, you want to switch over to some sort of connection pooler. And PG Bouncer is the onethat I tend to go for. There's also PG Pool, which does a lot more stuff. But this post talks aboutPG Bouncer and how to set it up. And they talk about basically three ways. The simplest way isjust creating a UserList TXT file and you place the users that are going to be connecting to yourdatabase and then the password for it. Now, how PG Bouncer works is you actually connect tothe PG Bouncer as if it were a PostgreSQL instance. So you need the same username andpassword so that then it can accept that connection and pass it on to the database down theline. Now, once you have this user list file in place for PG Bouncer, you then configure PGBouncer by saying the Auth type will be MD Five and then where the Auth file is located thatyou've set up your UserList TXT file. So that's the simplest way to get PG Bouncer up andrunning. And for a lot of applications that's all that you need.

[00:03:53] Now if you start having a lot more users then they're going into this second techniquewhere a client does this authentication about Pgbouncer. But what PG Bouncer does is thenquery the PG shadow table in PostgreSQL to get what the current username sorry, get what thecurrent password is for that user to then determine whether to allow connections to it and thenthe data that it can access. And they go through the process here basically create a new rolethat can query the PG shadow database. In this case they're using a PG Bouncer role or PGBouncer user, set up a password for it and they created a dedicated function that will query thePG shadow database. And then they're using this security definer as well as part of theirfunction. And then they revoke access to this function from the public schema, but they grant itto this individual user. So now you can configure PG Bouncer. Again, you use the same MD FiveAuth type, you can have an Auth file, but the important part is what user you want to use to doauthentication and what query to use. And again they'r --e querying the function to get the username and password to allow connections to thedatabase through Pgbouncer. And then they talk about an advanced authentication methodusing Pghba.com. Now in this scenario, Pgbouncer is set up using a Pghba.com file format. Soin this case the Pgbouncer is accepting connections from two different systems at specific IPaddresses, but it is using an app user and the method MD Five. So it does have the Auth file asthe user list, but it's also doing an Auth HBA file and the Auth type two HBA. So that's anotherway to use authentication with Pgbouncer. So if you're using PG Bouncer or you're realizingyou're going to start using, it definitely a blog post to check out.

[00:05:48] The next post is sequential. UUID. Generators on. SSD. And this is from the secondquadrant.com blog. Now this is a follow on from a previous post we covered a few weeks agoabout sequential UUID benchmarks where they were saying random UUIDs can causeperformance problems on inserts as it's searching around for where particularly if you're havingindexes on it as it's searching for where to insert that ID. And you can cause slowdowns intransaction throughput with regard to writing and actually increase the size of the wall files. Nowon his previous benchmark test he was looking at he was using magnetic storage, so not SSDs.And someone had made the point, well, the SSDs are better at random I O, so what would thebenchmarks look like with that? So they went ahead and did this post. So again, they took asmall data size, medium and a large, and looking at the differences, they had the random soentirely random UUIDs. This case where by time there's a certain part of that UUID that issequential with regards to time, and then two sequential where the first part of it is sequentialand then a remaining portion of it is random. And looking at small and medium, there's not toomuch of a difference. But then when it gets to the large data set size, you do start to see animpact of that random or entirely random UUID on their performance with regard to transactionsprocessed per second. Now, it's not as overt as it was with the magnetic storage, but as you cansee, you do get about a 50% performance hit once you start increasing the data size, even withSSDs with the random I O. And the other thing that still happens is that you do have a lot of fullpage image writes that happen to the wall. So your wall size still increases dramaticallycompared to doing the initial part of the UUID sequenced with regard to time or generalsequence. And then they also tested a much more powerful server with an Intel Optane SSDand they got similar results, although some of the sequence level UUIDs were not as good. So ingeneral, it's still even with SSDs, it still allows higher throughput and it definitely reduces theamount of wall produced. So definitely something to keep in mind if you're using UUIDs andwant to use them in a sequential manner.

[00:08:22] The next post is Webinar PG Logical and postgres BDR update. So this is a generalupdate for second Quadrant's BDR product, their bi directional replication. So this is basicallytheir Master Master PostgreSQL offering. I haven't had a chance to actually review this webinaryet, but if their Pgological as well as Master to Master replication or BDR of interest to you,definitely a webinar to check out and sign up for.

[00:08:51] The next post is Monitoring postgres with Prometheus. This is from DatafulBlogspot.com, and basically this is a post about monitoring postgres with Prometheus basedupon a talk he gave in Lisbon at PGConf EU 2018. And I went and got the slides form and thelink are right here for the PDF of the slides. Basically it's using Prometheus and Grafano, agraphing tool to build a postgres dashboard. And he talks about kind of the different tools he'susing. Prometheus Alert Manager, node exporter, postgres exporter, these are things that workwith Prometheus to grab certain statistics, using Mtail to actually tell some of the logs, I believe.And then what you see is what you get dashboard Grafana and he talks about the different dataflow of what's needed. So wanting to look at the different statistical views, wanting to look at thelogs, some other tables such as PG Stat statements and basically talking about the differenttools that will enable you to capture that. So the postgres exporter grabs the statistical views andthe other PG Stat stateme --nt type tables entail does the logs and you feed it into Prometheus. And then you can also usea net node exporter to grab operating system related information or even general systemmetrics, CPU and things of that nature. Those go into Prometheus and then you send them intoGrafana. And then Prometheus also allows alerting by email PagerDuty IRC Slack so, definitelyan interesting post to check out about how he set up his monitoring system using these tools.And he goes into some of the different settings for rates and errors and different things that he'sadded. So if you're interested in building a monitoring solution using these tools, definitely a blogpost to check out.

[00:10:48] The last post is just something of general interest. So if you are interested in history,and particularly PostgreSQL's history, there is this post that has been put up that is calledLooking Back at Postgres by Joseph Hellerstein and it's basically looking back at the history ofthe postgres project and how it got started from the mid eighty s to the mid 90s. So if that'ssomething of interest to you, definitely a blog post to check out that does it. For this episode ofScaling Postgres, you can get links to all the content mentioned in the show notes. Be sure tohead over to Scalingposgres.com where you can sign up to receive weekly notifications of eachepisode, or you could subscribe via YouTube or itunes. Thanks. --