Re: Freeze avoidance of very large table.
От | Sawada Masahiko |
---|---|
Тема | Re: Freeze avoidance of very large table. |
Дата | |
Msg-id | CAD21AoATWdzDC9PwPB0r2LRzZftZ2E8hy_c8j1=kOjts+=SrdQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Freeze avoidance of very large table. (Sawada Masahiko <sawada.mshk@gmail.com>) |
Ответы |
Re: Freeze avoidance of very large table.
(Bruce Momjian <bruce@momjian.us>)
Re: Freeze avoidance of very large table. (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
Список | pgsql-hackers |
On Tue, Apr 7, 2015 at 11:22 AM, Sawada Masahiko <sawada.mshk@gmail.com> wrote: > On Tue, Apr 7, 2015 at 7:53 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: >> On 4/6/15 5:18 PM, Greg Stark wrote: >>> >>> Only I would suggest thinking of it in terms of two orthogonal boolean >>> flags rather than three states. It's easier to reason about whether a >>> table has a specific property than trying to control a state machine in >>> a predefined pathway. >>> >>> So I would say the two flags are: >>> READONLY: guarantees nothing can be dirtied >>> ALLFROZEN: guarantees no unfrozen tuples are present >>> >>> In practice you can't have the later without the former since vacuum >>> can't know everything is frozen unless it knows nobody is inserting. But >>> perhaps there will be cases in the future where that's not true. >> >> >> I'm not so sure about that. There's a logical state progression here (see >> below). ISTM it's easier to just enforce that in one place instead of a >> bunch of places having to check multiple conditions. But, I'm not wed to a >> single field. >> >>> Incidentally there are number of other optimisations tat over had in >>> mind that are only possible on frozen read-only tables: >>> >>> 1) Compression: compress the pages and pack them one after the other. >>> Build a new fork with offsets for each page. >>> >>> 2) Automatic partition elimination where the statistics track the >>> minimum and maximum value per partition (and number of tuples) and treat >>> then as implicit constraints. In particular it would magically make read >>> only empty parent partitions be excluded regardless of the where clause. >> >> >> AFAICT neither of those actually requires ALLFROZEN, no? You'll need to >> uncompact and re-compact for #1 when you actually freeze (which maybe isn't >> worth it), but freezing isn't absolutely required. #2 would only require >> that everything in the relation is visible; not frozen. >> >> I think there's value here to having an ALLVISIBLE state as well as >> ALLFROZEN. >> > > Based on may suggestions, I'm going to deal with FM at first as one > patch. It would be simply mechanism and similar to VM, at first patch. > - Each bit of FM represent single page > - The bit is set only by vacuum > - The bit is un-set by inserting and updating and deleting > > At second, I'll deal with simply read-only table and 2 states, > Read/Write(default) and ReadOnly as one patch. ITSM the having the > Frozen state needs to more discussion. read-only table just allow us > to disable any updating table, and it's controlled by read-only flag > pg_class has. And DDL command which changes these status is like ALTER > TABLE SET READ ONLY, or READ WRITE. > Also as Alvaro's suggested, the read-only table affect not only > freezing table but also performance optimization. I'll consider > including them when I deal with read-only table. > Attached WIP patch adds Frozen Map which enables us to avoid whole table vacuuming even when full scan is required: preventing XID wraparound failures. Frozen Map is a bitmap with one bit per heap page, and quite similar to Visibility Map. A set bit means that all tuples on heap page are completely frozen, therefore we don't need to do vacuum freeze that page. A bit is set when vacuum(or autovacuum) figures out that all tuples on corresponding heap page are completely frozen, and a bit is cleared when INSERT and UPDATE(only new heap page) are executed. Current patch adds new source file src/backend/access/heap/frozenmap.c which is quite similar to visibilitymap.c. They have similar code but are separated for now. I do refactoring these source code like adding bitmap.c, if needed. Also, when skipping vacuum by visibility map, we can skip at least SKIP_PAGE_THESHOLD consecutive page, but such mechanism is not in frozen map. Please give me feedbacks. Regards, ------- Sawada Masahiko
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Etsuro FujitaДата:
Сообщение: Re: Optimization for updating foreign tables in Postgres FDW