On Mon, 2008-05-26 at 11:36 -0400, Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> > Decibel! wrote:
> >> Also, Simon and Tom had an idea at PGCon: Don't set hint-bits in the
> >> back-end if the page isn't already dirty.
>
> > Or even better: set the hint-bits, but don't d
On May 18, 2008, at 1:28 AM, Greg Smith wrote:
I just collected all the good internals information included in
this thread and popped it onto http://wiki.postgresql.org/wiki/
Hint_Bits where I'll continue to hack away at the text until it's
readable. Thanks to everyone who answered my questi
On May 18, 2008, at 1:28 AM, Greg Smith wrote:
I just collected all the good internals information included in
this thread and popped it onto http://wiki.postgresql.org/wiki/
Hint_Bits where I'll continue to hack away at the text until it's
readable. Thanks to everyone who answered my questi
t;[EMAIL PROTECTED]>
To: Luke Lonergan
Cc: Pavan Deolasee <[EMAIL PROTECTED]>; Greg Smith <[EMAIL PROTECTED]>; Alvaro
Herrera <[EMAIL PROTECTED]>; pgsql-performance@postgresql.org
Sent: Thu May 22 12:10:02 2008
Subject: Re: [PERFORM] I/O on select count(*)
On Thu, 2008-05
The real question in my mind is why this turns into a bottleneck before
the similar task of cleaning the 16MB XLOG segment does. I expected
that one would need to be cracked before the CLOG switch time could
possibly be an issue, but reports from the field seem to suggest
otherwise.
H
Greg Smith <[EMAIL PROTECTED]> writes:
> The real question in my mind is why this turns into a bottleneck before
> the similar task of cleaning the 16MB XLOG segment does.
Because we do the latter off-line, or at least try to.
regards, tom lane
--
Sent via pgsql-perform
On Mon, 19 May 2008, Matthew Wakeling wrote:
Does it really take that long to zero out 8kB of RAM? I thought CPUs were
really quick at doing that!
You don't get the whole CPU--you get time slices of one. Some of the
cases complaints have come in about have over a thousand connections all
fi
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Matthew Wakeling wrote:
>> Does it really take that long to zero out 8kB of RAM? I thought CPUs were
>> really quick at doing that!
> Yea, that was my assumption too.
You have to write the page (to be sure there is space for it on disk)
not only zero i
Matthew Wakeling wrote:
> On Fri, 16 May 2008, Tom Lane wrote:
> > One additional point: this means that one transaction in every 32K
> > writing transactions *does* have to do extra work when it assigns itself
> > an XID, namely create and zero out the next page of pg_clog. And that
> > doesn't j
Alvaro Herrera <[EMAIL PROTECTED]> writes:
pg_clog is allocated in pages of 8kB apiece(*). On allocation, pages are
zeroed, which is the bit pattern for "transaction in progress". So when
a transaction starts, it only needs to ensure that the pg_clog page that
corresponds to it is allocated, bu
I just collected all the good internals information included in this
thread and popped it onto http://wiki.postgresql.org/wiki/Hint_Bits where
I'll continue to hack away at the text until it's readable. Thanks to
everyone who answered my questions here, that's good progress toward
clearing up
Tom Lane wrote:
One additional point: this means that one transaction in every 32K
writing transactions *does* have to do extra work when it assigns itself
an XID, namely create and zero out the next page of pg_clog. And that
doesn't just slow down the transaction in question, but the next few
g
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Greg Smith wrote:
>> After reading the code and that documentation a bit, the part I'm still
>> not sure about is whether the CLOG entry is created when the XID is
>> assigned and then kept current as the state changes, or whether that
>> isn't even
Alvaro Herrera wrote:
> pg_clog is allocated in pages of 8kB apiece(*). On allocation, pages are
> zeroed, which is the bit pattern for "transaction in progress". So when
> a transaction starts, it only needs to ensure that the pg_clog page that
> corresponds to it is allocated, but it need not
Greg Smith wrote:
> After reading the code and that documentation a bit, the part I'm still
> not sure about is whether the CLOG entry is created when the XID is
> assigned and then kept current as the state changes, or whether that
> isn't even in CLOG until the transaction is committed. It
On Fri, 2008-05-16 at 14:05 -0400, Greg Smith wrote:
> After reading the code and that documentation a bit, the part I'm
> still not sure about is whether the CLOG entry is created when the XID
> is assigned and then kept current as the state changes, or whether
> that isn't even in CLOG until the
On Thu, 15 May 2008, Alvaro Herrera wrote:
Starting a transaction does not write anything to pg_clog.
For Matt and others, some details here are in
src/backend/access/transam/README:
"pg_clog records the commit status for each transaction that has been
assigned an XID."
"Transactions and
>>> On Thu, May 15, 2008 at 5:11 PM, in message
<[EMAIL PROTECTED]>, James Mansion
<[EMAIL PROTECTED]> wrote:
> Alvaro Herrera wrote:
>> Hint bits are used to mark tuples as created and/or deleted by
>> transactions that are know committed or aborted. To determine the
>> visibility of a tuple wi
"Luke Lonergan" <[EMAIL PROTECTED]> writes:
> BTW we¹ve removed HINT bit checking in Greenplum DB and improved the
> visibility caching which was enough to provide performance at the same level
> as with the HINT bit optimization, but avoids this whole ³write the data,
> write it to the log also
Alvaro Herrera wrote:
Hint bits are used to mark tuples as created and/or deleted by
transactions that are know committed or aborted. To determine the
visibility of a tuple without such bits set, you need to consult pg_clog
and possibly pg_subtrans, so it is an expensive check. On the other
Tom Lane wrote:
Hmm, the problem would be trying to figure out what percentage of writes
could be blamed solely on hint-bit updates and not any other change to
the page. I don't think that the bufmgr currently keeps enough state to
know that, but you could probably modify it easily enough, since
Robert Lor <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> It's certainly true that hint-bit updates cost something, but
>> quantifying how much isn't easy.
> Maybe we can instrument the code with DTrace probes to quantify the
> actual costs.
Hmm, the problem would be trying to figure out what
Tom Lane wrote:
It's certainly true that hint-bit updates cost something, but
quantifying how much isn't easy.
Maybe we can instrument the code with DTrace probes to quantify the
actual costs. I'm not familiar with the code, but if I know where to
place the probes, I can easily do a quick tes
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Heikki Linnakangas escribió:
>> We know what kind of a relation we're dealing with in ReadBuffer, so we
>> could add a flag to BufferDesc to mark heap pages.
> Hmm, I was thinking that it would need access to the catalogs to know
> where the tuples ar
Matthew Wakeling wrote:
Aside from the rest of commentary, a slight clarification:
> So, as I understand it, Postgres works like this:
>
> 1. You begin a transaction. Postgres writes an entry into pg_clog.
Starting a transaction does not write anything to pg_clog.
--
Alvaro Herrera
Heikki Linnakangas escribió:
> Alvaro Herrera wrote:
>> The problem is that the bgwriter does not understand about the content
>> of the pages it is writing -- they're opaque pages for all it knows. So
>> it cannot touch the hint bits.
>
> We know what kind of a relation we're dealing with in Rea
On Thu, 15 May 2008, Heikki Linnakangas wrote:
There's not much point optimizing something that only helps with aborted
transactions.
That's fair enough, but this list method is likely to speed up index
writes anyway.
The general problem with any idea that involves keeping a list of changes
Alvaro Herrera wrote:
Greg Smith escribió:
On Thu, 15 May 2008, Pavan Deolasee wrote:
I had suggested in the past that whenever we set hint bits for a tuple,
we should check all other tuples in the page and set their hint bits
too to avoid multiple writes of the same page. I guess the idea go
Matthew Wakeling wrote:
On Thu, 15 May 2008, Heikki Linnakangas wrote:
> Is it really safe to update the hint bits in place? If there is a >
power cut in the middle of writing a block, is there a guarantee from
> the disc that the block will never be garbled?
Don't know, to be honest. We've n
Matthew Wakeling <[EMAIL PROTECTED]> writes:
> Hmm. That problem is what WAL full-page-writes is meant to handle, isn't
> it? So basically, if you're telling people that WAL full-page-writes is
> safer than partial WAL, because it avoids updating pages in-place, then
> you shouldn't be updating
Greg Smith escribió:
> On Thu, 15 May 2008, Pavan Deolasee wrote:
>
>> I had suggested in the past that whenever we set hint bits for a tuple,
>> we should check all other tuples in the page and set their hint bits
>> too to avoid multiple writes of the same page. I guess the idea got
>> rejecte
Matthew Wakeling wrote:
On Thu, 15 May 2008, Luke Lonergan wrote:
...HINT bit optimization, but avoids this whole ³write the data,
write it to the log also, then write it again just for good measure²
...
The hint data will be four bits per tuple plus overheads, so it could be
made very compact
On Thu, 15 May 2008, Heikki Linnakangas wrote:
> Is it really safe to update the hint bits in place? If there is a
> power cut in the middle of writing a block, is there a guarantee from
> the disc that the block will never be garbled?
Don't know, to be honest. We've never seen any reports of
On Thursday 15 May 2008 03:02:19 Tom Lane wrote:
> "Jan de Visser" <[EMAIL PROTECTED]> writes:
> > Obviously, this issue is tied to the slow count(*) one, as I found out
> > the hard way. Consider the following scenario:
> > * Insert row
> > * Update that row a couple of times
> > * Rinse and repea
On Thu, 15 May 2008, Luke Lonergan wrote:
BTW we¹ve removed HINT bit checking in Greenplum DB and improved the
visibility caching which was enough to provide performance at the same level
as with the HINT bit optimization, but avoids this whole ³write the data,
write it to the log also, then wr
Matthew Wakeling wrote:
Is it really safe to update the hint bits in place? If there is a power
cut in the middle of writing a block, is there a guarantee from the disc
that the block will never be garbled?
Don't know, to be honest. We've never seen any reports of corrupted data
that would su
On Wed, 14 May 2008, Alvaro Herrera wrote:
Hint bits are used to mark tuples as created and/or deleted by
transactions that are know committed or aborted. To determine the
visibility of a tuple without such bits set, you need to consult pg_clog
and possibly pg_subtrans, so it is an expensive che
Greg Smith <[EMAIL PROTECTED]> writes:
> ... To put that another way: if I've run a manual vacuum, is it true
> that it will have updated all the hint bits to XMIN_COMMITTED for all the
> tuples that were all done when the vacuum started?
Any examination whatsoever of a tuple --- whether by vac
"Jan de Visser" <[EMAIL PROTECTED]> writes:
> Obviously, this issue is tied to the slow count(*) one, as I found out
> the hard way. Consider the following scenario:
> * Insert row
> * Update that row a couple of times
> * Rinse and repeat many times
> Now somewhere during that cycle, do a select
Hi,
Luke Lonergan wrote:
BTW – we’ve removed HINT bit checking in Greenplum DB and improved the
visibility caching which was enough to provide performance at the same
level as with the HINT bit optimization, but avoids this whole “write
the data, write it to the log also, then write it again j
On Thu, 15 May 2008 10:52:01 +0800
Luke Lonergan <[EMAIL PROTECTED]> wrote:
> BTW we¹ve removed HINT bit checking in Greenplum DB and improved the
> visibility caching which was enough to provide performance at the
> same level as with the HINT bit optimization, but avoids this whole
> ³write th
On Thu, 15 May 2008, Pavan Deolasee wrote:
I had suggested in the past that whenever we set hint bits for a tuple,
we should check all other tuples in the page and set their hint bits too
to avoid multiple writes of the same page. I guess the idea got rejected
because of lack of benchmarks to
BTW we¹ve removed HINT bit checking in Greenplum DB and improved the
visibility caching which was enough to provide performance at the same level
as with the HINT bit optimization, but avoids this whole ³write the data,
write it to the log also, then write it again just for good measure²
behavior
On Thu, May 15, 2008 at 7:51 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
>
>
> So is vacuum helpful here because it will force all that to happen in one
> batch? To put that another way: if I've run a manual vacuum, is it true
> that it will have updated all the hint bits to XMIN_COMMITTED for all
On 5/14/08, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Wed, 14 May 2008, Alvaro Herrera wrote:
>
>
> > If neither of the bits is set, then the transaction is either in progress
> (which you can check by examining the list of running transactions in shared
> memory) or your process is the first one
On Wed, 14 May 2008, Alvaro Herrera wrote:
If neither of the bits is set, then the transaction is either in
progress (which you can check by examining the list of running
transactions in shared memory) or your process is the first one to check
(in which case, you need to consult pg_clog to kno
Greg Smith wrote:
> On Wed, 14 May 2008, Kevin Grittner wrote:
>
>> If this is the first time that the rows are being read since they were
>> inserted (or since the database was loaded, including from backup), it
>> may be rewriting the rows to set hint bits, which can make subsequent
>> access fas
On Wed, 14 May 2008, Kevin Grittner wrote:
If this is the first time that the rows are being read since they were
inserted (or since the database was loaded, including from backup), it
may be rewriting the rows to set hint bits, which can make subsequent
access faster.
This is the second time
>>> Doug Eck <[EMAIL PROTECTED]> wrote:
> I am attempting to run a query to determine the number of rows for a
given
> day using something like "select count(*) from tbl1 where ts between
> '2008-05-12 00:00:00.000' and '2008-05-12 23:59:59.999'". Explain
tells me
> that the query will be do
- Original Message
From: Merlin Moncure <[EMAIL PROTECTED]>
To: Doug Eck <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, May 14, 2008 3:38:23 PM
Subject: Re: [PERFORM] I/O on select count(*)
On Wed, May 14, 2008 at 4:09 PM, Doug Eck <[EMAIL PR
On Wed, May 14, 2008 at 4:09 PM, Doug Eck <[EMAIL PROTECTED]> wrote:
> I have a large table (~ 2B rows) that contains an indexed timestamp column.
> I am attempting to run a query to determine the number of rows for a given
> day using something like "select count(*) from tbl1 where ts between
> '2
I have a large table (~ 2B rows) that contains an indexed timestamp column. I
am attempting to run a query to determine the number of rows for a given day
using something like "select count(*) from tbl1 where ts between '2008-05-12
00:00:00.000' and '2008-05-12 23:59:59.999'". Explain tells me
52 matches
Mail list logo
