Hi List;
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of
xsegment_dim below)
I'm running the join shown below and it takes > 10 hours and
eventually runs out of disk space on a 1.
>>> 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
Any system catalog views I can check for wait events causing slower
response times.
Thanks in advance.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Subbiah
Stalin
Sent: Thursday, May 15, 2008 9:28 AM
To: Jeffrey Baker; pgsql-performance@postgresql.org
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
Yes we are updating one of indexed timestamp columns which gets unique
value on every update. We tried setting autovacuum_vacuum_scale_factor =
0.1 from default to make autovacuum bit aggressive, we see bloating on
both table and it's indexes but it's creeping up slowly though.
Anyways, even with
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
"Joshua D. Drake" writes:
> Guillaume Cottenceau wrote:
>> Matthew Wakeling writes:
>
>> It is still relevant, as with 5% margin, you can afford changing
>> that to 0% with tune2fs, just the time for you to start PG and
>> remove some data by SQL, then shutdown and set the margin to 5%
>> again.
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
Guillaume Cottenceau wrote:
Matthew Wakeling writes:
It is still relevant, as with 5% margin, you can afford changing
that to 0% with tune2fs, just the time for you to start PG and
remove some data by SQL, then shutdown and set the margin to 5%
again.
I find that if you actually reach that
Matthew Wakeling writes:
> On Thu, 15 May 2008, Guillaume Cottenceau wrote:
>> Also, IIRC when PG writes data up to a full filesystem,
>> postmaster won't be able to then restart if the filesystem is
>> still full (it needs some free disk space for its startup).
>>
>> Or maybe this has been fixed
On Thu, 15 May 2008, Guillaume Cottenceau wrote:
Also, IIRC when PG writes data up to a full filesystem,
postmaster won't be able to then restart if the filesystem is
still full (it needs some free disk space for its startup).
Or maybe this has been fixed in recent versions?
Ah, the "not enoug
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
Craig James writes:
> Matthew Wakeling wrote:
>> Probably of more use are some of the other settings:
>>
>> -m reserved-blocks-percentage - this reserves a portion of the filesystem
>> that only root can write to. If root has no need for it, you can kill
>> this by setting it to zero. Th
Matthew Wakeling wrote:
Probably of more use are some of the other settings:
-m reserved-blocks-percentage - this reserves a portion of the filesystem
that only root can write to. If root has no need for it, you can kill
this by setting it to zero. The default is for 5% of the disc to b
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
On Wed, May 14, 2008 at 6:31 PM, Subbiah Stalin-XCGF84
<[EMAIL PROTECTED]> wrote:
> Hi All,
>
> We are doing some load tests with our application running postgres 8.2.4. At
> times we see updates on a table taking longer (around
> 11-16secs) than expected sub-second response time. The table in ques
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
On Thu, 15 May 2008, [EMAIL PROTECTED] wrote:
IIRC postgres likes to do 1M/file, which isn't very largeas far as the -T
setting goes.
ITYF it's actually 1GB/file.
think twice about this. ext2/3 get slow when they fill up (they have
fragmentation problems when free space gets too small), this
On Thu, 15 May 2008, Matthew Wakeling wrote:
On Thu, 15 May 2008, Philippe Amelant wrote:
using mkfs.ext3 I can use "-T" to tune the filesytem
mkfs.ext3 -T fs_type ...
fs_type are in /etc/mke2fs.conf (on debian)
If you look at that file, you'd see that tuning really doesn't change that
muc
On Thu, 15 May 2008, Philippe Amelant wrote:
using mkfs.ext3 I can use "-T" to tune the filesytem
mkfs.ext3 -T fs_type ...
fs_type are in /etc/mke2fs.conf (on debian)
If you look at that file, you'd see that tuning really doesn't change that
much. In fact, the only thing it does change (if y
Hi all,
using mkfs.ext3 I can use "-T" to tune the filesytem
mkfs.ext3 -T fs_type ...
fs_type are in /etc/mke2fs.conf (on debian)
is there a recommended setting for this parameter ???
thanks
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
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
36 matches
Mail list logo
