Sorry for the late response; I was on vacation.
>>> Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> Kevin, what was your original scenario like that led you to
investigate
> this?
We noticed a performance degradation in application code which, within
a database transaction, looped through lar
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
Heikki, would it be reasonable to fix things so that a nonexistent FSM
fork is semantically the same as an empty one, and not create FSM until
there's actually something to put in it?
Possibly, but I'd like to unde
Alvaro Herrera wrote:
Heikki Linnakangas wrote:
oprofile does suggest that more time is spent in the kernel in CVS HEAD.
CVS HEAD:
3738713.9383 no-vmlinux postgres (no
symbols)
8.3:
27264 9.3254 no-vmlinux postgres (
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
What's happening is that there is simply so many temporary tables in
pg_class that when the new backend tries to clear them with
RemoveTempRelations, it runs out of lock space.
What happened to the original owner of the tables? I
Heikki Linnakangas wrote:
> oprofile does suggest that more time is spent in the kernel in CVS HEAD.
> CVS HEAD:
>
> 3738713.9383 no-vmlinux postgres (no
> symbols)
> 8.3:
>
> 27264 9.3254 no-vmlinux postgres (no
> symbol
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> What's happening is that there is simply so many temporary tables in
> pg_class that when the new backend tries to clear them with
> RemoveTempRelations, it runs out of lock space.
What happened to the original owner of the tables? It must have h
Heikki Linnakangas wrote:
Tom Lane wrote:
Try several thousand temp tables within one transaction.
I ran into an interesting problem while doing that. I created a SQL
script with 1 CREATE TEMPORARY TABLE statements. After testing with
that a few times, I got this:
WARNING: out of shar
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
Heikki, would it be reasonable to fix things so that a nonexistent FSM
fork is semantically the same as an empty one, and not create FSM until
there's actually something to put in it?
Possibly, but I'd like to unde
Heikki Linnakangas wrote:
I tried running this:
CREATE TEMPORARY TABLE footemp (id int4);
DROP TABLE footemp;
with pgbench -f, but can't see any meaningful difference between 8.3 and
CVS HEAD. Both can do about 300 tpm, or 700-800 with fsync=off. There
probably is a measurable difference ther
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Heikki, would it be reasonable to fix things so that a nonexistent FSM
>> fork is semantically the same as an empty one, and not create FSM until
>> there's actually something to put in it?
> Possibly, but I'd like to understand w
Tom Lane wrote:
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
That would make the file creation and unlink just under half the load.
Worst possible case :-( ... means that we wouldn't get much improvement
without addressing both aspects.
It strikes me however that this does put some urgency in
Kevin Grittner wrote:
> Note that the 150 disk writes were for the CREATE and the DROP. Does
> that mean that we'd actually shave 84 of 150 writes?
Hmm, you'd shave more than 42 but not 84, because index entries are not
deleted until a later vacuum. (I'd say about 56 -- 42 plus the 14 heap
dele
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> That would make the file creation and unlink just under half the load.
Worst possible case :-( ... means that we wouldn't get much improvement
without addressing both aspects.
It strikes me however that this does put some urgency into the question
of
>>> Tom Lane <[EMAIL PROTECTED]> wrote:
> it's difficult to tell how much
> physical I/O results from the create or unlink syscalls. It might
be
> interesting to make a test program that just creates 4000 files
We use xfs with noatime for our databases.
In a fresh subdirectory of such a mount
Tom Lane wrote:
> One place that I've always wanted to look at was suppressing the
> creation of a btree metapage until there's some useful data in the
> table. We managed to avoid creating a root page until there's data,
> but at the time avoiding the metapage seemed too invasive. (Admittedly,
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> I think a large fraction of the writes you're measuring are coming from
>> the file create/unlink operations. It would certainly be important to
>> identify where the bulk of the cost *really* is before we start
>>> Tom Lane <[EMAIL PROTECTED]> wrote:
> I think a large fraction of the writes you're measuring are coming
from
> the file create/unlink operations. It would certainly be important
to
> identify where the bulk of the cost *really* is before we start
> expending effort on a solution.
Any ideas
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Right -- I don't expect we can make use of such an idea readily. Not
> creating unnecessary pg_attribute entries for system columns is probably
> a lot easier to do.
I seem to recall having proposed that in the past, and getting shot down
on the basis
Kevin Grittner wrote:
> It sounds like you were counting the 8kB pages pushed from the
> PostgreSQL cache to the OS cache, and I was counting the 1kB blocks
> pushed from the OS cache to the RAID controller cache. By watching
> vmstat results after pushing this to a more-or-less steady state, I
>
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Note that the 150 disk writes were for the CREATE and the DROP. Does
> that mean that we'd actually shave 84 of 150 writes?
It really shouldn't be the case that each system catalog tuple insertion
generates a separate write --- especially not for mul
>>> Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Kevin Grittner wrote:
>
>> Note that the 150 disk writes were for the CREATE and the DROP.
Does
>> that mean that we'd actually shave 84 of 150 writes?
>
> Hmm, you'd shave more than 42 but not 84, because index entries are
not
> deleted until a l
>>> Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Kevin Grittner wrote:
>> An idea for a possible enhancement to PostgreSQL: allow creation of
a
>> temporary table without generating any disk I/O. (Creating and
>> dropping a three-column temporary table within a database
transaction
>> currently ge
Kevin Grittner wrote:
> An idea for a possible enhancement to PostgreSQL: allow creation of a
> temporary table without generating any disk I/O. (Creating and
> dropping a three-column temporary table within a database transaction
> currently generates about 150 disk writes).
Most of these are ca
Kevin Grittner wrote:
An idea for a possible enhancement to PostgreSQL: allow creation of a
temporary table without generating any disk I/O. (Creating and
dropping a three-column temporary table within a database transaction
currently generates about 150 disk writes).
If some circumstances don
On Wed, Nov 5, 2008 at 16:00, Kevin Grittner
<[EMAIL PROTECTED]> wrote:
> An idea for a possible enhancement to PostgreSQL: allow creation of a
> temporary table without generating any disk I/O. (Creating and
> dropping a three-column temporary table within a database transaction
> currently gener
An idea for a possible enhancement to PostgreSQL: allow creation of a
temporary table without generating any disk I/O. (Creating and
dropping a three-column temporary table within a database transaction
currently generates about 150 disk writes).
If some circumstances don't make it feasible to a
26 matches
Mail list logo
