Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs <[EMAIL PROTECTED]> writes: > In general, the hint bits are good. In *some* cases, not. I still seek > control over that as a designer. > Specifically, the scenario I want to optimize is this: > - we load a table with lots of real time measurement data, as one child > out of a large number of similar child tables > - we then immediately create summary tables from the measurements > - after this the detailed data is only sporadically accessed, if ever > - detail data is dropped from the database after a few weeks > - the majority of the database is detail data, so those tables are never > vacuumed since no rows are ever deleted from those tables (the tables > are dropped) nor is access sufficiently frequent to make it sensible to > set hint bits - hence *no* complete database vacuum is run on a regular > basis > In this design, the second step causes the whole detailed data table to > be written out to the database (again) immediately after loading. I > would like to be able to prevent that. This application seems sufficiently off the beaten path to not be worth solving with a knob as klugy and potentially dangerous as suppress_hint_bits. A better way to avoid the write pass, if you must, is to do the summary table creation in the same transaction that loads the data. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs <[EMAIL PROTECTED]> writes: > On Wed, 2005-07-20 at 09:24 -0400, Tom Lane wrote: >> We don't rely on any one write of them to work, but that doesn't mean >> that we can indefinitely postpone writing them. > OK, I think I understand where you're coming from now. Apparently not :-( > When VACUUM freezes the xid, it *does* make sense at that point to > update the hint bits as a performance optimization. The hint bits are not really relevant when xmin = FrozenTransactionId, since any examiner of the tuple would consider that XID committed anyway. Besides, the hint bit is guaranteed set in that scenario; note the Assert where vacuum is setting it: HeapTupleHeaderSetXmin(tuple.t_data, FrozenTransactionId); /* infomask should be okay already */ Assert(tuple.t_data->t_infomask & HEAP_XMIN_COMMITTED); The scenario in which the hint bit *must* be set is where it is for an XID for which we have deleted the relevant section of pg_clog, which we are willing to do well before freeze occurs, if we know that all the relevant XIDs have been hinted. See TruncateCLOG. Your patch breaks that logic by not considering hint-bit updates as changes that must be flushed to disk by checkpoint. > 1. Any block read may attempt to set hint bits, which dirties the block > and must be written out. So *reads* can result in heavier write activity > at checkpoint time. Sure, but the alternative is heavier activity in repeated checks of pg_clog to find out commit state that a previous examiner of the tuple already found out. The patch supposes that one write is worse than N reads, which is clearly a loss at some not-exceedingly-large value of N. If we thought that was a good tradeoff, we might as well not have the hint bits at all. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2005-07-19 at 22:24 -0400, Tom Lane wrote: >> Simon Riggs <[EMAIL PROTECTED]> writes: >>> Short patch enclosed to turn off writing of commit-status hint bits. >> >> Doesn't this entirely destroy the ability to truncate clog, and >> therefore the ability to survive XID wraparound? > I hope not for all our sakes, since the hint bits are not WAL logged and > anything that relies upon them would be fragile. We don't rely on any one write of them to work, but that doesn't mean that we can indefinitely postpone writing them. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs <[EMAIL PROTECTED]> writes: > Short patch enclosed to turn off writing of commit-status hint bits. Doesn't this entirely destroy the ability to truncate clog, and therefore the ability to survive XID wraparound? It probably also breaks subxact and multixact logging, but I haven't looked closely... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
On Sun, 2005-04-24 at 02:28 -0400, Tom Lane wrote:
> In the current code there is no such thing as a hard read-only behavior
> --- for example we will try to update commit-status hint bits no matter
> what. Allowing that to be turned off would be interesting for a number
> of purposes, such as burning a database onto CD.
Short patch enclosed to turn off writing of commit-status hint bits.
I've called this "cache_txn_status_with_data" but I'm open to other
suggestions...
This should allow migration of older child tables to hierarchical
storage when using a large historical table design.
Best Regards, Simon Riggs
Index: src/backend/storage/buffer/bufmgr.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.189
diff -c -c -r1.189 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 19 May 2005 21:35:46 - 1.189
--- src/backend/storage/buffer/bufmgr.c 19 Jul 2005 22:50:25 -
***
*** 63,68
--- 63,69
/* GUC variables */
bool zero_damaged_pages = false;
+ boolcache_txn_status_with_data = true;
double bgwriter_lru_percent = 1.0;
double bgwriter_all_percent = 0.333;
int bgwriter_lru_maxpages = 5;
***
*** 1638,1643
--- 1639,1647
return;
}
+ if (!cache_txn_status_with_data)
+ return;
+
bufHdr = &BufferDescriptors[buffer - 1];
Assert(PrivateRefCount[buffer - 1] > 0);
Index: src/backend/utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.274
diff -c -c -r1.274 guc.c
*** src/backend/utils/misc/guc.c 14 Jul 2005 05:13:42 - 1.274
--- src/backend/utils/misc/guc.c 19 Jul 2005 22:50:34 -
***
*** 487,492
--- 487,502
false, NULL, NULL
},
{
+ {"cache_txn_status_with_data", PGC_USERSET, DEVELOPER_OPTIONS,
+ gettext_noop("Writes transaction status to each row."),
+ gettext_noop("PostgreSQL normally avoids commit log accesses by cacheing"
+ "transaction status though this may not be efficient with write-once,"
+ " then read-only access.")
+ },
+ &cache_txn_status_with_data,
+ true, NULL, NULL
+ },
+ {
{"full_page_writes", PGC_SIGHUP, WAL_SETTINGS,
gettext_noop("Writes full pages to WAL when first modified after a checkpoint."),
gettext_noop("A page write in process during an operating system crash might be "
Index: src/include/storage/bufmgr.h
===
RCS file: /projects/cvsroot/pgsql/src/include/storage/bufmgr.h,v
retrieving revision 1.93
diff -c -c -r1.93 bufmgr.h
*** src/include/storage/bufmgr.h 20 Mar 2005 22:00:54 - 1.93
--- src/include/storage/bufmgr.h 19 Jul 2005 22:50:46 -
***
*** 27,32
--- 27,33
/* in bufmgr.c */
extern bool zero_damaged_pages;
+ extern bool cache_txn_status_with_data;
extern double bgwriter_lru_percent;
extern double bgwriter_all_percent;
extern int bgwriter_lru_maxpages;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
