Re: xlogdump fixups and WAL log question.
От | Theo Schlossnagle |
---|---|
Тема | Re: xlogdump fixups and WAL log question. |
Дата | |
Msg-id | D04777A3-CD8C-4702-BA3C-87B83A451A6A@omniti.com обсуждение исходный текст |
Ответ на | Re: xlogdump fixups and WAL log question. ("Simon Riggs" <simon@2ndquadrant.com>) |
Список | pgsql-hackers |
On Oct 21, 2006, at 4:40 PM, Simon Riggs wrote: > On Sat, 2006-10-21 at 15:17 -0400, Theo Schlossnagle wrote: >> On Oct 21, 2006, at 3:12 PM, Simon Riggs wrote: >> >>> On Sat, 2006-10-21 at 09:00 -0400, Theo Schlossnagle wrote: >>>> On Oct 21, 2006, at 6:08 AM, Martijn van Oosterhout wrote: >>>> >>>>> On Sat, Oct 21, 2006 at 10:37:51AM +0100, Simon Riggs wrote: >>>>>> Turning off WAL is a difficult topic. Without it you have no >>>>>> crash >>>>>> recovery, which IMHO everybody says they don't care about until >>>>>> they >>>>>> crash, then they realise. It's hard to be selective about >>>>>> writing WAL >>>>>> for specific operations also. >>>>> >>>>> It's been discussed before. One idea is to declare tables without >>>>> logging. The idea being that during recovery those tables and >>>>> related >>>>> indexes are simply truncated. No foreign keys allowed. Obviously >>>>> they >>>>> will not be saved via PITR either. >>>>> >>>>> Put another way, the table structure is saved in WAL, but the data >>>>> isn't. >>>> >>>> This is exactly what I'd like. Simon suggested turning off WAL >>>> during the loads as a possible hack solution. The reason this >>>> won't >>>> work is that we snap all the time, lots of tables. We have between >>>> 2000 and 4000 snapshot operations per day (throughout). At the >>>> same >>>> time we have reporting queries running (that create and/or populate >>>> other tables) that last from 5 minutes to 18 hours. It is >>>> important >>>> that we run everything but the snapshots with WAL on (as we must >>>> have >>>> PITR -- sans snapshots) >>> >>> These tables are loaded once then read-only, yes? >> >> No, they are loaded, and then reloaded, and then reloaded. Queries >> that use them will get the most recently loaded version of them. It >> meets a business rule like: table foo on the warehouse should be >> representative of version of table foo on OLTP no older than 30 >> minutes. > > But they can be re-created anew with the same name each time? Or I > guess > not, but you redefine a view every 30 minutes to point to the latest > one? closest to the latter. A view is redefined when the new snapshot is complete. > If so, then I have a patch that will speed up COPY when in the same > transaction as the table that created it. I've finally fixed a bug > in my > earlier prototypes that seems to make that work now, in all cases. > > I was being slightly slow before; I thought this was a new requirement > but its just the old one slightly restated. We don't use COPY. We directly INSERT INTO target_snap SELECT * from remote_select(...) t(cast); remote_select is part of dbi-link. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
В списке pgsql-hackers по дате отправления: