Re: Freeze avoidance of very large table.
От | Jim Nasby |
---|---|
Тема | Re: Freeze avoidance of very large table. |
Дата | |
Msg-id | 5522EA6C.8060105@BlueTreble.com обсуждение исходный текст |
Ответ на | Re: Freeze avoidance of very large table. (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-hackers |
On 4/6/15 1:28 PM, Josh Berkus wrote: > On 04/06/2015 10:07 AM, Jim Nasby wrote: >> Actually, I would start simply with ReadOnly and ReadWrite. >> >> As I understand it, the goal here is to prevent huge amounts of periodic >> freeze work due to XID wraparound. I don't think we need the Freeze >> state to accomplish that. >> >> With a single bit per page in the Frozen Map, checking a 800GB table >> would require reading a mere 100MB of FM. That's pretty tiny, and >> largely accomplishes the goal. >> >> Obviously it would be nice to eliminate even that 100MB read, but I >> suggest you leave that for a 3rd patch. I think you'll find that just >> getting the first 2 accomplished will be a significant amount of work. >> >> Also, note that you don't really even need the ReadOnly patch. As long >> as you're not actually touching the table at all the FM will eventually >> read as everything is frozen; that gets you 80% of the way there. So I'd >> suggest starting with the FM, then doing ReadOnly, and only then >> attempting to add the Frozen state. > > +1 > > There was some reason why we didn't have Freeze Map before, though; > IIRC these were the problems: > > 1. would need to make sure it gets sync'd to disk and/or WAL-logged Same as VM. > 2. every time a page is modified, the map would need to get updated Not everytime, just the first time if FM for a page was set. It would only be set by vacuum, just like VM. > 3. Yet Another Relation File (not inconsequential for the cases we're > discussing). Sure, which is why I think it might be interesting to either allow for more than one page per bit, or perhaps some form of compression. That said, I don't think it's worth worrying about too much because it's still a 64,000-1 ratio with 8k pages. If you use 32k pages it becomes 256,000-1, or 4GB of FM for 1PB of heap. > Also, given that the Visibility Map necessarily needs to have the > superset of the Frozen Map, maybe combining them in some way would make > sense. The thing is, I think in many workloads the paterns here will actually be radically different, in that it's way easier to get a page to be all-visible than it is to freeze it. Perhaps there's something we can do here when we look at other ways to reduce space usage for FM (and maybe VM too), but I don't think now is the time to put effort into this. > I agree with Jim that if we have a trustworthy Frozen Map, having a > ReadOnly flag is of marginal value, unless such a ReadOnly flag allowed > us to skip updating the individual row XIDs entirely. I can think of > some ways to do that, but they have severe tradeoffs. Aside from Alvaro's points, I think many users would find it useful as an easy way to ensure no one is writing to a table, which could be valuable for any number of reasons. As long as the patch isn't too complicated I don't see a reason not to do it. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
В списке pgsql-hackers по дате отправления: