Re: [PERFORM] encourging bitmap AND

2011-01-02 Thread Jim Nasby 
On Dec 26, 2010, at 11:24 AM, Tom Lane wrote:
> If you're doing interval queries enough to worry about having an index
> for them, you really want an indexing structure that is designed to do
> interval queries efficiently.

BTW, one way to accomplish that is to transform your data into geometric shapes 
and then index them accordingly. Prior to the work Jeff Davis has done on time 
intervals it was common to treat time as points and ranges as lines or boxes. 
While we no longer need to play those games for time, I don't think there's an 
equivalent for non-time datatypes.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] CPU bound

2011-01-02 Thread Jim Nasby 
On Dec 20, 2010, at 12:47 AM, Mladen Gogala wrote:
> Good time accounting is the most compelling reason for having a wait event 
> interface, like Oracle. Without the wait event interface, one cannot really 
> tell where the time is spent, at least not without profiling the database 
> code, which is not an option for a production database.

Out of curiosity, have you tried using the information that Postgres exposes to 
dtrace? I suspect it comes close to what you can get directly out of Oracle...
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to turn autovacuum prevent wrap around run faster?

2011-01-02 Thread marc.hsiao 
Hi Scot Marlowe:

> Set an autovacuum max cost much higher than the vacuum max cost
> (10 or so) and drop autovac cost delay to 0, then restart
> autovacuum.

I had set " autovacuum_vacuum_cost_delay=0".
BTW, how to set " autovacuum max cost" ?


Regards
Marc


-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Scott Marlowe
Sent: Tuesday, December 28, 2010 2:12 PM
To: marc.hsiao
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to turn autovacuum prevent wrap around run
faster?

On Mon, Dec 27, 2010 at 10:53 PM, marc.hsiao 
wrote:
> Hi ALL:
>
> My Database is for logs only, I almost have 30G data growth in my
database.
>
> I Use partition table to store those data, those tables are partitioned by
> time column daily.
>
> My data only keep for three days.
>
> I will dump those data into dump file and drop the partition table after
> three days.
>
> My question is :
>
> The partition table that I have to backup and drop is running a long
> Prevent-Wraparound-Autovaccuum,
>
> Is any way to let the vacuum faster?
>
> The Prevent-Wraparound-Autovaccuum run very slow, almost 36 hours.
> My Disk IO is low.
> My Server config for vacuum list below:
> maintenance_work_mem = 192MB    # min 1MB
>
> max_stack_depth = 4MB # min 100kB
>
>
>
> vacuum_cost_delay = 50ms    # 0-100 milliseconds

That's a pretty high regular vacuum cost delay.  Just sayin, autovac
doesn't use it.

> vacuum_cost_page_hit = 6    # 0-1 credits
>
> vacuum_cost_limit = 1000    # 1-1 credits

And that's pretty low.

> # AUTOVACUUM PARAMETERS
>
>
#---
---
> autovacuum = on # Enable autovacuum subprocess?  'on'
> autovacuum_max_workers = 3    # max number of autovacuum subprocesses
> autovacuum_naptime = 1    # time between autovacuum runs
> autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before
vacuum
> autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for

Set an autovacuum max cost much higher than the vacuum max cost
(10 or so) and drop autovac cost delay to 0, then restart
autovacuum.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance