Re: [HACKERS] Optimizer Question/Suggestion

2002-11-03 Thread Hannu Krosing
Philip Warner kirjutas P, 03.11.2002 kell 15:41:
> At 03:25 PM 3/11/2002 +0500, Hannu Krosing wrote:
> >a separate backend in a loop that
> >kept doing VACUUM TABLE with only 5 seconds sleep between
> 
> Good grief! I thought 5 minutes was bad enough. Can't wait for b/g vacuum. 
> Thanks for the input; I'll wait for a day or so to get some figures as you 
> suggest.

The 5 sec number was for case when tens of worker threads were updating
as fast as they could a table with just a few of hundreds of rows. I
guess your case is not _that_ intensive, so you can probably use much
bigger intervals.

---
Hannu



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] Optimizer Question/Suggestion

2002-11-03 Thread Philip Warner
At 03:25 PM 3/11/2002 +0500, Hannu Krosing wrote:

a separate backend in a loop that
kept doing VACUUM TABLE with only 5 seconds sleep between


Good grief! I thought 5 minutes was bad enough. Can't wait for b/g vacuum. 
Thanks for the input; I'll wait for a day or so to get some figures as you 
suggest.




Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [HACKERS] Optimizer Question/Suggestion

2002-11-03 Thread Hannu Krosing
Philip Warner kirjutas P, 03.11.2002 kell 06:30:
> At 09:36 AM 2/11/2002 -0500, Tom Lane wrote:
> 
> >Why not do frequent non-full vacuums on only that table, perhaps every
> >five minutes or so?  That's certainly the direction that development is
> >headed in (we just haven't automated the vacuuming yet).
> 
> Done this now, and I'll wait for a new high load time to see how big the 
> table gets.

You should find the best interval by testing.

I guess the interval could be smaller than 5 min for high loads - I
remember testing this situation for getting top update performance when
several threads were doing updates at full speed and the best
performance was achieved by running a separate backend in a loop that
kept doing VACUUM TABLE with only 5 seconds sleep between .

-
Hannu



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])



Re: [HACKERS] Optimizer Question/Suggestion

2002-11-02 Thread Tom Lane
Philip Warner <[EMAIL PROTECTED]> writes:
> At 09:36 AM 2/11/2002 -0500, Tom Lane wrote:
>> BTW, the system does not actually have any stats about dead tuples.
>> What it knows about are live tuples and total disk pages occupied by
>> the table.

> So what made it choose the index scan?

Well, the main component of the seqscan cost estimate is the total
number of disk pages, while the indexscan cost estimate is driven
by the number of tuples expected to be retrieved.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])



Re: [HACKERS] Optimizer Question/Suggestion

2002-11-02 Thread Philip Warner
At 09:36 AM 2/11/2002 -0500, Tom Lane wrote:


Why not do frequent non-full vacuums on only that table, perhaps every
five minutes or so?  That's certainly the direction that development is
headed in (we just haven't automated the vacuuming yet).


Done this now, and I'll wait for a new high load time to see how big the 
table gets.

Definitely looking forward integrated on-line vacuum!


Ideally we should never let a table get so overloaded with dead space
that this strategy would be profitable.


I suspect it would be more common that you might hope, both because of 
incompetance/changed database usage (as in this case) and archival 
strategies (ie. deleting data periodically, but *not* doing a full vacuum). 
I come from a background where pre-allocating unused space for table data 
is a good strategy, not a performance killer, and I'm probably not alone.

If it was not hard, I thought adding a PK scan as a possible strategy when 
considering seqscan was an interesting option. I suppose the other option 
in this case would be to modify seqscan to only look at pages we know have 
records (if we keep that data?).


BTW, the system does not actually have any stats about dead tuples.
What it knows about are live tuples and total disk pages occupied by
the table.


So what made it choose the index scan? Does it make guesses about tuple 
sizes, and predict empty space?






Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [HACKERS] Optimizer Question/Suggestion

2002-11-02 Thread Tom Lane
Philip Warner <[EMAIL PROTECTED]> writes:
> This comes about because we have a table with 800 rows, one more more of 
> which are updated every second of most days. The result in normal usage is 
> that the table contains about 1 tuples one hour after vacuuming. Also, 
> the databases tries to be 24x7, and the table concerned is a core table, so 
> vacuum/full once per hour is not an option.

Why not do frequent non-full vacuums on only that table, perhaps every
five minutes or so?  That's certainly the direction that development is
headed in (we just haven't automated the vacuuming yet).

> ISTM that if a table has a PK, then a bogus index scan should be introduced 
> if a table has more than a 'RandomPageCost/SequentialPageCost' ratio of 
> dead:live tuples.

The ratio would have to be higher than that, because ordinarily you
expect to get more than one tuple per sequential page read.  But I think
this is going in the wrong direction anyway.  Ideally we should never
let a table get so overloaded with dead space that this strategy would
be profitable.

BTW, the system does not actually have any stats about dead tuples.
What it knows about are live tuples and total disk pages occupied by
the table.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] Optimizer Question/Suggestion - numbers after

2002-11-02 Thread Doug McNaught
Philip Warner <[EMAIL PROTECTED]> writes:

> At 07:39 PM 2/11/2002 +1100, Philip Warner wrote:

> The latter time is actually quote good; when the machine is more
> heavily loaded it goes up to 1ms.
> 
> We currently vacuum/analyze daily, and analyze hourly.

Why not vacuum hourly (regular non-blocking vacuum, not FULL)?

-Doug

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] Optimizer Question/Suggestion - numbers after

2002-11-02 Thread Philip Warner
At 07:39 PM 2/11/2002 +1100, Philip Warner wrote:

To give some numbers:


And some more numbers, directly after a vacuum and analyze:

mail=# explain analyze select * from often_updated where id between 
'-1' and '10';
Index Scan using barnet_users_id on often_updated  (cost=0.00..3095.66 
rows=750 width=205) (actual time=0.15..41.04 rows=750 loops=1)
Total runtime: 44.81 msec

mail=# explain analyze select * from often_updated;
Seq Scan on often_updated  (cost=0.00..49273.50 rows=750 width=205) (actual 
time=1.93..1710.01 rows=750 loops=1)
Total runtime: 1714.32 msec

The latter time is actually quote good; when the machine is more heavily 
loaded it goes up to 1ms.

We currently vacuum/analyze daily, and analyze hourly.



Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org