All the Dirt on VACUUM
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 29 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19118 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Production Place | Ottawa, Canada |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 20152 / 29
1
2
3
6
10
11
12
13
14
15
17
18
21
22
23
25
26
29
00:00
Information technology consultingClefVacuumVacuumSlide ruleCodeFreezingPerturbation theoryDatabase transactionn-TupelMultiplicationNebenläufigkeitskontrolleRevision controlTupleInformationReplication (computing)Electric currentLogicDatabaseData bufferHome pageIntrusion detection systemRow (database)Subject indexingMemory managementWeb pagePersonal digital assistantMathematicsBounded variationTable (information)Grand Unified TheoryProcess (computing)Control flowDemo (music)3 (number)Lipschitz-StetigkeitDisk read-and-write headOpen setNumberDifferent (Kate Ryan album)Default (computer science)Front and back endsSet (mathematics)Presentation of a groupAxiom of choiceConcurrency (computer science)Intrusion detection systemMultiplicationDatabase transactionSubject indexingMemory managementVacuumPersonal digital assistantDatabaseHecke operatorTupleInformationTable (information)Diallyl disulfideScripting languageFunctional (mathematics)Row (database)Home pageCondition numberPoint (geometry)Address spaceHacker (term)Electronic mailing listRevision controlBit rateShared memoryReplication (computing)Theory of relativitySystem callKey (cryptography)Electric generatorCountingMathematicsBounded variationThresholding (image processing)FreezingDampingBitProcess (computing)Queue (abstract data type)Field (computer science)Constraint (mathematics)Partition (number theory)Port scannerSequenceProjective planeCodierung <Programmierung>Binary codeLoginQuery languageFeedbackStatement (computer science)Ocean currentLevel (video gaming)Communications protocolMultiplication signSynchronizationRepetitionConfiguration spaceSelectivity (electronic)Exception handlingGame controllerSoftware maintenancePhysical systemCausalityPersonal identification numberFrequencyn-TupelGastropod shellBoiling pointMaxima and minimaMereologyLoop (music)Semantics (computer science)Exclusive orOperator (mathematics)CASE <Informatik>Integrated development environmentStructural loadStatisticsMathematical optimizationWeb pagePattern languageShape (magazine)Form (programming)Amsterdam Ordnance DatumData storage deviceAreaSoftware bugWordDependent and independent variablesSpacetimeCodeArithmetic progressionRegular graphInsertion lossWebsiteLimit (category theory)Social classComputer fileMiniDiscBlock (periodic table)Network topologyKernel (computing)Total S.A.Type theoryBuffer solutionBand matrixEntire functionFlow separationSemiconductor memoryFerry CorstenTrailTerm (mathematics)Reading (process)AlgorithmMessage passingDecision theoryScheduling (computing)RAIDView (database)Parameter (computer programming)1 (number)BootingGreatest elementGame theoryPlanningRule of inferenceLogicObservational studyStability theoryDivisorDemosceneSource codeAsynchronous Transfer ModeQuicksortMappingIdentifiabilityEvent horizonCartesian coordinate systemTraffic reportingPlotterDuality (mathematics)Phase transitionProgrammschleifeEndliche ModelltheorieLogical constantFamilyCellular automatonWeightLine (geometry)JSONUML
Transcript: English(auto-generated)
00:00
Talking to you guys about the internals of how vacuum works today Just as kind of a heads up this talk is going to be rather in-depth It was actually I don't see Simon in here I actually got the idea to do this talk seeing Simon do a talk at vacuum at the last PG open and
00:22
it occurred to me that while there's a number of Pieces of information out there about this is how you should tune the different settings and whatnot I hadn't seen anything that really talked in-depth about what vacuum actually does and how it actually works So that's what I'll be covering here today. I do have there are some references to actual
00:46
places in the code in the presenter notes, so if you Go ahead and download this. I think we're making the The slides available Through the website But if not, I'll have it posted at blue trouble calm so
01:04
So today I'll be talking about Mvcc C log multi transactions Some of the machinery that kind of goes into why we have to vacuum I'll discuss what you know when things can be vacuumed
01:24
Freezing hot vacuum itself, obviously, and then some stuff about auto vacuum who knows what Mvcc is Good
01:40
Basically databases you have two choices to ensure consistent data you can do locking which sucks or you can use multi version concurrency control and Really what this Essentially means is that when you do a delete or you do an update? The old data does not get removed when you do an update a new copy of the row is created and when you do
02:05
Delete it's just it's left behind so that's Ultimately the reason the biggest reason that we need vacuum is you you have these rows that are left behind that eventually have to be removed
02:21
The commit log is What Postgres uses to track whether a transaction has been committed or rolled back or whether it's actually still in progress Multi transactions which some of you how many of you have heard about the recent bugs
02:41
Yeah, if you're If you're on nine three or nine four you need to upgrade because there were some data loss bugs that were found revolving around multi Multi x-acts The basically multi x-acts are needed to handle row level locking
03:01
and some conditions with with update and delete The reason these two things are related to vacuum is vacuum is what's responsible for going in and Freeing up space in those two storage areas
03:21
so as I mentioned You're doing updates you're doing deletes you're creating these dead rows, but the big question becomes okay when you have a dead row when Can you actually go in and remove that? and Basically the constraint there is that
03:42
when a transaction When you start a transaction or a select statement in Postgres The database gets what's called a snapshot and that snapshot has Visibility information that determines what rows that individual statement can actually see
04:02
so If you have a snapshot in your database that say has been was taken an hour ago and In since that snapshot was taken you've gone and you've deleted three million rows Those rows cannot be cleaned up by vacuum because that our old
04:21
Select statement could still need to look at that data There is The the other thing that comes into play here is that if you're using streaming replication depending on your configuration Streaming replication can can have that same visibility impact
04:45
Because of the way that streaming rep works it's it's a completely binary level replication protocol so when the master goes and vacuums and removes Those old rows if you had an old select statement on a streaming replica
05:02
You now can have this problem where the master is removed the data and the replica still needs it because it's got a query running So if you have hot standby feedback enabled Your streaming replica will affect the ability for vacuum to do its job on your master
05:25
Prepared transactions These are different from prepared statements Basically the only reason ever turn prepared transactions on is if you're specifically doing two-phase commit And the big thing there is that when you prepare a transaction that transaction has a snapshot that's associated with it
05:43
So that prepared transaction again will prevent vacuum from working Logical decoding can also affect this because again, it's it while it's not a pure binary replication Technology, it's still based on the replay log and it still has this constraint on vacuum
06:09
The So that's kind of the visibility side, but there's other things that affect vacuums ability to do its job Because of how Index scans and sequential scans operates
06:25
you cannot vacuum a Page of data while somebody else is looking at that page. You can't just remove the data out from underneath somebody so there's a very special lock that has to be acquired at a page level for
06:42
vacuum to be able to to remove data The the These these queries can can hold these locks. These are not necessarily short-term locks
07:06
So especially if you have something like a nested loop scan the outer side of that loop Can potentially hold a pin on a page for a very long time and that page while that pin is being held Cannot be vacuumed What this all boils down to is when you have long running stuff happening in your database that
07:26
prevents vacuum from being able to do its job, so if you have a Very high transaction rate database. You need to then be careful about long running stuff and
07:40
This is also not limited strictly to What table is being vacuumed versus what table is being queried because those snapshots if you get a snapshot? that's an hour long and All it's doing is hitting one table Well, the thing is the system has no way to know that that transaction will only hit the one table
08:00
so you may be trying to vacuum some other table and It's not going to be vacuum won't be able to remove the data. Yes
08:25
Yes And unfortunately in All the versions up to nine point four. There's no way to get visibility on that. We did add some additional Verbose logging so if you do vacuum verbose in nine five it will
08:44
Provide feedback to you when it hits when it has Tries to pin. Sorry tries to get the cleanup lock on pages and can't the one exception to this is A freeze vacuum because a freeze vacuum we must scan and clean every single page
09:03
so when vacuum is freezing if it can't acquire the cleanup lock it will sit and wait and That can actually then cause other problems because while it's sitting and waiting for this cleanup lock Anybody else that needs to get that? To get a pin on that page is going to then sit and wait behind the vacuum
09:26
so And and that's actually the way the vacuum just used to work Period I I want to say this was changed in like nine one or nine two Where we changed vacuum so that if it could not immediately acquire the cleanup lock it wouldn't sit there and wait
09:45
Prior to that it would just sit there and wait and then any other queries that came along would get stuck behind vacuum Transaction IDs and multi X act IDs multi transaction IDs
10:01
The way that those are implemented is it's basically a circular counter so it starts at well, I think it starts at two because the first couple values are reserved but It starts at two and it starts increasing and then when it hits two to the 31st all the sudden
10:22
Two is no longer a transaction. That's in the past. It becomes a transaction. That's now in the future so The the problem that that presents is You if you have rows that say oh I was created by transaction number two
10:43
And all of a sudden you get to transaction two to the 31st plus two or whatever now that row becomes invisible again because now the database is thinking oh This row is actually created by a transaction. That's way in the future and it won't show it to you so the way that the system works around this and handles this is
11:05
As This as the transaction count increases Eventually, it has to go through and find all transaction IDs in all tables that are older and
11:20
replace them with a special value that indicates This row is visible. It's visible to everybody it will always be visible to everybody until somebody goes and deletes it that process is known as freezing and this has to be done both for transaction IDs and For multi X act IDs
11:45
and in fact if you Don't handle freezing correctly. Eventually the database will stop Allowing you to create new transactions Specifically to prevent this problem of all the sudden your data starts vanishing. So
12:01
The key takeaway here is that if you have very high rates of update transactions You do a lot of for share locking Because for share lock will always generate a multi transaction a multi X act ID Or if you have a lot of concurrent foreign key checks happening
12:24
That's the other thing that tends to generate multi transaction IDs If you have very high rates of those you can run into problems with being able to freeze your data Heap Sure. Oh how to see what the counter is
12:51
It's Actually, not very easy to see for a transaction there is a there's a function I think it's
13:02
Get current snapshot something like that. There is a function that you can run that will show you what your current Transaction ideas Yes Yes, so you can look at you can call TX ID current that will tell you your
13:21
Transaction ID, which is essentially the most recent one normally Yes. Yes. So you it's not just a raw number So you have to actually interpret it, but you can interpret it and then you can compare it to In PG database. There's a column called
13:43
dat frozen X ID and that tells you the Oldest non frozen transaction ID in each database so you can compare that There are there are monitoring scripts out there to do this. I
14:04
Believe I know there's at least one for Nagios There's probably a few others Yes, it's the oldest non frozen
14:28
Transaction ID. Yeah
14:41
Yes, yes Now when it comes to multi exact IDs there, I don't know of any way to get that information I mean you could write a Excuse me. You could write a C function that would expose it and there has actually been
15:07
a proposal on the hackers mailing list And I don't I think it's gonna get committed but probably in nine Actually, I think it may get into nine five That will add a function that will expose the multi X ID
15:22
the multi X act Information but that again right now there's really Essentially no way to do that other than writing a custom C function In Starting in 9.0. We add in a new feature called heap only tuples. So under certain specific
15:47
Conditions if you update a row We can actually avoid The need to then come back and vacuum the old row the way that this works is that
16:02
If you if you perform an update and the update does not touch any Indexes, it doesn't change any values that are used in any indexes on that table Then we don't need to touch the indexes We don't need to create a new reference in the index to point at the new row that's being generated by the update
16:26
so If you're not touching the indexes and the new row still fits on the same page You can the database will do what's what's called a heap only tuple where it does create a second version
16:40
But it ties these two together and they're tied together through the same basically address So that once the old Row is no longer visible to anybody we can do a heap only cleanup And that heap only cleanup is done
17:03
Whenever pages are read it's a little bit more complex than that the exact conditions But this can This can make a world of difference Especially on tables that have a lot of indexes because it's those indexes that can really make
17:23
vacuum quite expensive so The the takeaway here is that you want to try and avoid referencing Heavily updated columns in a table in your indexes and when I say referencing that means
17:45
Obviously an index on the column itself But also if the column shows up in a where clause or if it's used as a function if you have a functional index Where this really comes into play is if you've got a table that you're trying to use as something like a queue table
18:03
Where you've just got a lot of churn rate that's happening. You don't want to You want to avoid doing things like putting an index on your status field or something like that? Because if you don't do that your updates to those rows they become hot and
18:20
It makes it a lot easier to to keep stuff cleaned up vacuum itself There are four major variations on vacuum Auto vacuum is is a built-in process that tries to just handle everything and Does a pretty good job of it in the most recent versions
18:45
vacuum full completely rebuilds a table from scratch Vacuum is the regular manually run vacuum and then vacuum freeze is a special variation on vacuum Essentially it sets some freeze threshold settings to
19:03
to Ensure that that we that that vacuum run will freeze everything it possibly can As I mentioned auto vacuum it you generally don't have to mess around with it in 9.4 the default settings at this point are pretty good if you're running older versions the
19:24
If you're running an old enough version the one thing that you would probably want to change is Originally auto vacuum used a Threshold of the percentage of table that had to change That was set to like 40% so auto vacuum wouldn't try and vacuum a table until there was at least 40% churn and
19:48
That's a lot So if you're running older versions You would want to change that but I think that we changed that in like nine all the current threshold
20:18
yeah, that's
20:21
That's very low
21:26
What it's not done and now because of the index statistics the optimizer thought well that very will return Maximum one row
21:54
Right and if you are in an environment where you do some kind of a data load or you do some large bulk operation
22:00
It's a good idea to at least run analyze if not vacuum as well depending on what operation you've done immediately after it Because ultimately auto vacuum Generally does a pretty good job, but you can do a lot to give it a leg up to give it a helping hand
22:20
by making strategic use of manual vacuums so The you know one very common case for that is if you've done a bulk operation Another very common case is if you have something like a queue table that you know Gets a lot of churn rate and especially if you're trying to keep that table small
22:42
You want to vacuum that table manually very frequently. I actually typically will go And set up a cron job that try it that vacuums that table once a minute Because the idea there is if the table is small that vacuum is very inexpensive and
23:02
By vacuuming that aggressively you're trying to keep it small if it starts getting big That's when that's when you start having serious problems The other thing that you can do with vacuum that can make a big difference is if you have a website that has Irregular traffic patterns or I say website really any database if your database has a regular
23:26
Traffic pattern so say you don't have as much activity During the night, or you don't have as much activity on weekends It's a good idea to go ahead and schedule a manual vacuum to run During those off-peak hours because the work that the manual vacuum does during that time
23:45
Auto vacuum doesn't then have to try and do in the middle of the day when everything is busy One word of caution Something that some people have tried to do is use auto vacuum nap time To try and constrain and control when auto vacuum will actually run. That's a really bad idea
24:05
First of all it doesn't actually work some people will be I only want auto vacuum to run at night so they set auto vacuum nap time to 24 hours and Maybe that'll work for a while, but then You do a restart you have some problem And you do a restart in the middle of the day and oh look now your 24 hours is occurring in the middle of the day
24:26
And there there's just other problems that that you run into by not running By auto vacuum not running that frequently
25:25
yeah, I think the big challenge there is being able to recognize these patterns of Here's a hot spot over here we need to keep special You know keep a special eye on this one thing that we certainly could do is that right now
25:41
When an auto vacuum worker launches in a database we don't prioritize The tables in any way shape or form it just says here's my list of what I need to vacuum and that's it So that's something that we could potentially do Yes, you can so
26:07
You Can but especially in in a table that needs to be vacuumed very frequently It's really not it's not very likely to help a lot Because what happens is vacuum Gets tied up vacuuming other stuff, and then it just completely doesn't look at the table. Yes
26:39
The big I'll actually cover that but yes
26:44
Um Starting in so prior to 9.0 Running a vacuum full was just a completely horrible idea the way that vacuum full worked is it Took the last tuple in the table and tried to move it to the front
27:03
And then the second to last and tried to move it to the front First of all it did this while holding an exclusive lock on the table so now nobody can do anything with the table The other even bigger problem is that in that process of doing it It was bloating the heck out of the indexes because each one of these tuples that got moved it then had to create new index
27:26
entries And especially with the way that b-tree works that could seriously damage not from a data standpoint, but it could really blow your your indexes Is anyone running anything older than 9.0?
27:44
Upgrade uh-huh, but definitely if you're on eight something nobody's on seven anyone on seven good Now that I've now that I'm publicly humiliating everyone Yeah, if you're if you're prior to nine just do not even think about
28:05
Running vacuum full. It's it's really just not a good idea since 9.0 What we do instead of that is we just create a brand new copy of the table from scratch We recreate all the indexes on it The bad news is this does still require an exclusive lock. So while this is happening
28:25
Nobody else. I know nobody can read can modify the data And I don't think you can even select from the table. Well Yeah so The good news is you get a really nicely efficiently packed table and all your indexes are rebuilt
28:42
It's it's brand new and shiny but while it's running You're out of luck for trying to use it The the if you do need to if you are Finding the need to actually go do that. There is a project online
29:01
at github called PG repack that Basically what that does is it does essentially the same thing It creates a new table copies all the data over and builds all the indexes. The difference is this does it a Few rows at a time or
29:22
Sorry, it's not a few rows at a time, but it creates the table But it puts triggers in place so it can remember. Oh, hey, here's the stuff I haven't copied copies the data over goes in pulls out the last of whatever got done and Then once it's once this new table is completely in sync It then takes a very short
29:43
Exclusive lock to swap the you know, drop the old table rename the new one And then you're up and running again. So if you do need to do this That's a project that can be very useful
30:04
Yeah Oh just just as a heads up though. They're PG repack is a fork from an older project
30:21
That is now defunct. So you want to make sure you're using PG repack. I think the other one was called PG reorg That's the old one. You want to use repack? Yes Yes, it no it locks
30:48
Yeah, yeah, it's So the sorry the question was can you if you're using partitioning can you vacuum fold just a single partition?
31:03
The answer is yes because partitioning Really each partition is a standalone table of its own. It's it's almost essentially just another table You are probably you're likely to run into Problems with locking unless your queries
31:25
Are specific enough that the planner can determine that it doesn't need to read from that table If If you're saying oh where date is greater than you know We're date equals today and you're doing date partitioning and you have the constraint setup and you have constraint exclusion turned on then
31:46
the planner May be smart enough to recognize that. Oh, hey, I'm just not gonna look at the table But I'm not even sure that that's true. So you you still could effectively be it is It is so if you needed to you could just manually query some other partition that would be yeah
32:11
Manual vacuum itself One thing that might surprise you you cannot run it in a transaction. It needs to control transaction semantics So you cannot write a function that's going to manually vacuum. It won't work
32:25
There is a vacuum DB shell command if you're doing shell scripting that can be handy likewise From the cron side What what vacuum actually does is For each table it scans the heap
32:42
remembering all the tuples That need to be removed then it scans through the indexes removing the tuples from the indexes Then it removes the tuples from the heap Finally if you ask for analyze it'll do that and then it'll update The PG database frozen xmin column and the minimum MX ID column
33:07
The the key gotcha here is This part where we scan the heap Remembering a set of tuples and then we go hit the indexes to remove the tuples and then go back to the heap
33:21
How many tuples can be remembered is controlled by maintenance workmen? If you have maintenance workmen that's set too low It starts scanning. It then has to stop do all the indexes Remove those and then keep scanning again and those index scans. That's a full scan of every index
33:40
So you do not want to set maintenance workmen to some really low level because your vacuum will just die It's not driven by the size of the index. So what we have to remember is a CT ID a tuple identifier, which is six bytes. So if you have a million
34:06
Dead rows that need to be vacuumed. That's six megabytes
34:22
possibly But you know that
34:46
So the vacuum function itself by the way, I'm now effectively walking through the code The vacuum function itself calls vacuum rel vacuum relation This function vacuums a single table
35:01
It does a bunch of mundane stuff that doesn't really matter and then it calls either cluster relation If it's a vacuum full or if you've run the cluster command Or it calls lazy vacuum Before returning In a regular vacuum that you've just issued the vacuum command
35:23
It then calls itself again to vacuum the toast table there is a difference here though with auto vacuum Auto vacuum Considers when a toast table needs to be vacuumed Completely separately. It's a totally separate scheduling decision that it makes so that is one nice thing about
35:43
Auto vacuum is if if you're producing a lot of churn in your main table, but nothing in your toast table It's not gonna sit there and keep trying to vacuum the toast table. It'll just leave it alone Lazy vacuum rel
36:01
First thing it does is it figures out do we need to freeze this table? So it looks at some there's a couple of configuration settings that they control this and they basically say if The rel frozen X ID or the rel min MX ID
36:21
column in PG class not PG database in PG class is Old enough and it it falls within Our vacuum threshold it then decides. Okay. This is going to be a freeze vacuum What vacuum freeze does if you say vacuum freeze it basically sets those limits to zero so it'll always be a freeze vacuum
36:46
It scans the heap Using lazy scan heap scans the heap and scans the indexes If it makes sense if we have removed Another if we have freed up enough
37:03
Pages at the end of the table it will try and actually shrink the table on disk It cleans up the free space map and Finally, it'll update PG class With at a minimum it will update the row count
37:24
It can also and if it is a freeze vacuum, it'll update those columns as well Lazy scan heap. This is the function that actually goes through the table page by page by page
37:40
When it When it pulls in a block the It's a little hard to describe this and actually try and make it match the code, but basically if it's not a freeze vacuum and There are at least 32 consecutive pages that are marked as being all visible in the visibility map
38:02
Then it will skip it will just skip ahead to the next Page that is not marked as being all visible so This can be extremely helpful if you have a table that's you know insert mostly So most of the table all the rows are visible and then you just have stuff at the very end
38:24
That's being updated Yes The the terms are used somewhat interchangeably in the code so
38:41
And the the reason for the 32 block limit there is It wants to try and avoid Confusing the read-ahead algorithm in the in the kernel If we have almost Run out of maintenance work memory space to remember tuples. It stops what it's doing
39:02
goes and removes all the index tuples From each index. It basically does the index scans And then it removes the heap tuples Next Thing it attempts to get this cleanup lock if it can't get the cleanup block and it's not a freeze vacuum
39:20
It just says up. Okay next block So that's one as I said earlier, that's something you have to be careful of is If you're holding, you know, there are certain activities that will prevent vacuuming of blocks within a relation And honestly, I don't think anybody has any idea if that's a real problem or not
39:44
Because as I mentioned we don't currently track statistics on it. So If it can get the cleanup lock it calls the Page pruning code which is the same thing that's done for hot cleanup. It's actually this exact same code path
40:02
It remembers What Tuples are dead. It basically puts a set of T IDs in an array Or if there there is a special case here if the table has no indexes it just Run pass through the table and that's it is it identifies the dead tuples
40:23
It just removes them, but most people have at least one index. So It's uh, so remembers the dead tuples it updates the free space and the visibility map And Then it will do
40:41
That's that's basically the end of the for each block loop Once it's processed the entire heap relation, it will update statistics And it does the final pass through the index. Hopefully the only pass through the indexes The the index cleanup function
41:03
Basically Because of the way that indexes work in Postgres you can you can actually create a new index method without Theoretically without modifying any of the Postgres code What that then turns into is that each index has a different method for how it handles vacuuming so I I
41:24
don't really want to I didn't want to try and go into the specific method used for every Index the one thing I will say is that Definitely for B tree indexes and I'm pretty sure this affects other at least some of the other index types as well
41:42
If your index gets bloated, it can be extremely hard to actually shrink that index back down because of how B tree works so don't let bloat happen and if it does get bloated really your best bet is to Either do a reindex command which will lock the table or you can do a concurrent index build
42:04
To create a brand new index and once the new index is built you can drop the old one Lazy vacuum heap is the function that actually Removes the dead tuples from the table itself. So this gets called after we've called lazy vac
42:25
after we've called lazy cleanup index On each index in the table. We then call lazy vacuum heap it Goes to each block that it needs to removes all the the tuples that were marked dead
42:41
and On each block on each page. It will defragment the page and record with the free spaces There is a separate function that handles updating day
43:00
The two columns in PG database in that our naming convention makes it very hard to actually say these in English But dat frozen X ID and dat min X ID min MX ID if there are new if we have new values for either one, which the only way we can have new values is if
43:23
Vacuum managed to scan every single page in the table normally that Generally that probably only happens if it if it was run as a freeze vacuum But it is smart about it to recognize that if it just so happened that it was a plain vacuum
43:41
But it did visit every single page so now we know that we have The most recent up-to-date information on what the oldest Transaction ID and MX ID in the table are it will then still Do the update?
44:02
If so, if that happens, we will update it this at the database level And if we update it at the database level, that is when we call The function to truncate the commit log It updates the shared memory version of the information
44:24
and then the Multi exact files the the the cleanup of the multi exact storage will actually then happen during the next checkpoint That's it for vacuum
44:42
So auto vacuum there's two there's two parts to auto vacuum there's the launcher and there's the workers the launcher prioritizes the databases basically by how Close they are to needing to be frozen First looking at the transaction freeze second looking at the multi transact freeze
45:06
and if nothing's in Danger of if nothing needs to be frozen Then it's just gonna look at whatever database was most was least recently auto vacuumed
45:22
Multiple workers can work on the same database at once And workers will be cancelled if they interfere with another back end So this is another difference between auto vacuum and manual vacuum manual vacuum will never be magically cancelled by the system, but if an auto vacuum worker is
45:42
Doing something and it interferes with another operation in the database the auto vacuum back end can be terminated I'm DDL What can actually cause an auto vacuum worker to be terminated well that
46:20
Well and and in nine for the way that that works is First of all, we won't even try and truncate the heap unless we can get the lock without blocking anybody And then as we're doing the truncation itself if we detect that We are blocking somebody we will stop the truncation at that point and that's true for any
46:54
Yes, that will kill the auto vacuum, but there's actually there
47:00
But but they're actually there actually is some protection for that for manual vacuum as well It does not ignore Postgres it they so the only databases it ignores is ones that are marked as you cannot connect to them Because if you cannot connect to them there shouldn't be anything
47:24
to do But it won't actually unless you say unless you set postgres to be not connect to it will on a vacuum Yes, yes, that's very important
47:44
The workers themselves they get a list of of all the heap tables And materialized views that need to be vacuumed Then it gets a list of toast tables that need to be vacuumed
48:01
It does ignore temp tables. So auto vacuum will never vacuum a temporary table if you want If you're doing updates and stuff on a temp table and you and you think it needs to be vacuumed You must do that by hand It makes no distinction on that
48:21
For each relation it attempts to get the lock if it can't get the lock It just skips the table entirely So this is another thing where you have to be careful with you know This is another opportunity for long-running stuff in your database to Not allow vacuum or in this case not allow auto vacuum to do its work
48:43
Pardon Well, the biggest reason is because the visibility Information for temp tables is handled differently There's some optimizations that we can do when it's a temp table because we know nobody else is gonna read from it
49:02
But that then means that vacuum from another process can't you act it's not actually possible to vacuum Yes, yes
49:42
Yes Right, and since that session can't be doing anything else in the meantime, you can also just run a vacuum full as well So it will go through each Relation
50:01
Once it's run through all the relations it will Call the vac update that frozen X ID function And then it exits it does not sit and loop through the tables Once a worker is run through its entire list of what it identified originally it needed to do it just exits
50:22
Here's a big one auto vacuum does not prioritize tables within the database It just says here's a list of tables that I want to vacuum and that's it It doesn't put any kind of priority and it only runs through the list once The other big thing with auto vacuum is that it can become ineffective for high demand tables or
50:44
If you end up with so if you have three large to hit the normal the default configuration is you have three Auto vacuum workers if you end up with three large tables that all show up on the list and all need to be vacuumed at Once all three workers are gonna be stuck doing that and nothing else is gonna get vacuumed
51:03
Until one of those workers freeze up. So these are some of the things that that factor into why Especially for tables that you're trying to keep small with they'd have the high churn rate. You still want to vacuum those by hand Vacuum cost delay, it's it's pretty well
51:23
Documented really the critical thing is that as certain operations happen in vacuum either reading a page out of shared buffers reading it from the kernel or Dirtying the page each one of those has a cost associated with it We keep a running total of the cost and when the cost a seat exceeds
51:42
the Auto or vacuum cost limit we then sleep for some number of milliseconds The Entire idea here is to prevent vacuum from just sucking down every last available piece of IO that you've got
52:01
One thing you need to be careful with is don't slow vacuum down too much So don't set some huge cost delay or don't set the cost limit really small because if you do that Yes, you're going to save on your IO bandwidth initially by throttling the heck out of vacuum But it then means vacuums not getting the work done that it needs to get done
52:24
And that means that your table is just going to end up over time. It's just going to get more and more bloated You're actually making things worse. So don't do that The other trick here is that if you have assist systems with nice expensive raid controllers with nice raid setups a
52:43
Lot of time it is actually cheaper to write data than it is to read data So if you have a nice expensive raid controller, you may want to set the page dirty vacuum page dirty parameter lower than the page miss parameter
53:03
Because it's cheaper to write the data than to go to the kernel to get So basically just to reiterate the the key points long-running transactions they mess with vacuum High transaction rates used to for share for share lock and
53:24
Lots of concurrent foreign key checks increase the need to freeze because they burn through MX IDs Indexes referencing heavily updated columns they prevent hot from working which
53:40
creates more need for vacuuming Make sure maintenance work memory is large enough. It's very difficult to reduce the size of a bloated database and Auto vacuum can only do so much. It's not magic as much as we might wish that it was I Am out of time, but I'm happy to stick around for questions. I think it's lunchtime at this point
54:06
Thank you