Обсуждение: Integrated autovacuum

Поиск
Список
Период
Сортировка

Integrated autovacuum

От
"Joshua D. Drake"
Дата:
Hello,

Just for clarification, will the new integrated autovacuum require that 
statistics are on?

Sincerely,

Joshua D. Drake

-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: Integrated autovacuum

От
Alvaro Herrera
Дата:
On Wed, Jul 27, 2005 at 12:53:40PM -0700, Joshua D. Drake wrote:

> Just for clarification, will the new integrated autovacuum require that 
> statistics are on?

Yes.  Row-level stats too.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I'm always right, but sometimes I'm more right than other times."
(LinusTorvalds)
 


Re: Integrated autovacuum

От
"Joshua D. Drake"
Дата:
Alvaro Herrera wrote:
> On Wed, Jul 27, 2005 at 12:53:40PM -0700, Joshua D. Drake wrote:
> 
> 
>>Just for clarification, will the new integrated autovacuum require that 
>>statistics are on?
> 
> 
> Yes.  Row-level stats too.

Great Thanks... Could I get a better explanation of the following:


#autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before 
analyze


Sincerely,

Joshua D. Drake

> 


-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: Integrated autovacuum

От
Martín Marqués
Дата:
El Mié 27 Jul 2005 17:24, Alvaro Herrera escribió:
> On Wed, Jul 27, 2005 at 12:53:40PM -0700, Joshua D. Drake wrote:
>
> > Just for clarification, will the new integrated autovacuum require that
> > statistics are on?
>
> Yes.  Row-level stats too.

Will there be a way to ballance the amount of stats the autovacuum gets?
Something like the analyze parameters that the contrib version has, but
integrated in postgresql.conf?

I had a select on my development server that took several minutes to complete,
and after running manually analyze on the tables involved the time reduced
dramatically.

Running on a 8.0.3 server with autovacuum running every 5 minutes.

-- 17:52:04 up 25 days,  2:37,  1 user,  load average: 0.90, 1.00, 0.97
-------------------------------------------------
Lic. Martín Marqués   | SELECT 'mmarques' ||
Centro de Telemática  |     '@' || 'unl.edu.ar';
Universidad Nacional  | DBA, Programador,    del Litoral       | Administrador
-------------------------------------------------


Re: Integrated autovacuum

От
Alvaro Herrera
Дата:
On Wed, Jul 27, 2005 at 02:07:28PM -0700, Joshua D. Drake wrote:

> Great Thanks... Could I get a better explanation of the following:
> 
> #autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before vacuum
> #autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before 
> analyze

Sure.  We use a scoring system:

score = X_base_threshold + X_scale_factor * reltuples

where X is one of vacuum or analyze.  reltuples is the number for
pg_class.


We decide to vacuum if the number of dead tuples in the table as
reported to the stats system is higher than the score.  If it isn't, we
decide to analyze if the number of new tuples since last analyze + dead
tuples since last analyze is higher than the score.

This all will become clearer when we have real docs for autovacuum.


Also, somebody (Rod Taylor I think) proposed changed the variable names
to

vacuum_auto_vacuum_scale_factor
vacuum_auto_analyze_scale_factor

etc.  I haven't seen much agreement nor disagreement with the idea.  I
agree with that on principle but you have to admit the above names are
confusing and too long.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)


Re: Integrated autovacuum

От
Alvaro Herrera
Дата:
On Wed, Jul 27, 2005 at 06:05:26PM -0300, Martín Marqués wrote:
> El Mié 27 Jul 2005 17:24, Alvaro Herrera escribió:
> > On Wed, Jul 27, 2005 at 12:53:40PM -0700, Joshua D. Drake wrote:
> > 
> > > Just for clarification, will the new integrated autovacuum require that 
> > > statistics are on?
> > 
> > Yes.  Row-level stats too.
> 
> Will there be a way to ballance the amount of stats the autovacuum gets? 
> Something like the analyze parameters that the contrib version has, but 
> integrated in postgresql.conf?

I'm not sure I understand your question.  If it means what I think, then
yes, you can set the threshold and scale values per table.


> I had a select on my development server that took several minutes to complete, 
> and after running manually analyze on the tables involved the time reduced 
> dramatically.

I think everybody mostly agreed that contrib's pg_autovacuum default
values were too conservative.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)


Re: Integrated autovacuum

От
Martín Marqués
Дата:
El Mié 27 Jul 2005 18:23, Alvaro Herrera escribió:
> On Wed, Jul 27, 2005 at 06:05:26PM -0300, Martín Marqués wrote:
> >
> > Will there be a way to ballance the amount of stats the autovacuum gets?
> > Something like the analyze parameters that the contrib version has, but
> > integrated in postgresql.conf?
>
> I'm not sure I understand your question.  If it means what I think, then
> yes, you can set the threshold and scale values per table.

Yes, that's what I was asking. Will those values be in flat files or in the
cataloge?[1] For what I see, it looks like flat files (presumably
postgresql.conf)

> > I had a select on my development server that took several minutes to
complete,
> > and after running manually analyze on the tables involved the time reduced
> > dramatically.
>
> I think everybody mostly agreed that contrib's pg_autovacuum default
> values were too conservative.

Yes, I noticed that. Anyway, the main aplicacion on which we are working has 2
main data alterations.

1) Mass data update (INSERTs and UPDATEs) on 3 o 4 tables. This doesn't happen
very frecuently, so I'm thinking about adding an ANALYZE at the end of the
transaction.
2) Constant data updates and inserts, still at a low rate, on one table. This
could get analyzed every night with the Backup.

The other tables have so little amount of data, and doesn't get updated that
usual, so there's nothing to bother about.

[1]: Yes I know Alvaro, I should be testing 8.1beta, but thank God I have
8.0.3 now. ;-)


-- 18:23:45 up 25 days,  3:09,  1 user,  load average: 1.12, 1.01, 1.19
-------------------------------------------------
Lic. Martín Marqués   | SELECT 'mmarques' ||
Centro de Telemática  |     '@' || 'unl.edu.ar';
Universidad Nacional  | DBA, Programador,    del Litoral       | Administrador
-------------------------------------------------


Re: Integrated autovacuum

От
Alvaro Herrera
Дата:
On Wed, Jul 27, 2005 at 06:35:31PM -0300, Martín Marqués wrote:
> El Mié 27 Jul 2005 18:23, Alvaro Herrera escribió:
> > On Wed, Jul 27, 2005 at 06:05:26PM -0300, Martín Marqués wrote:
> > > 
> > > Will there be a way to ballance the amount of stats the autovacuum gets? 
> > > Something like the analyze parameters that the contrib version has, but 
> > > integrated in postgresql.conf?
> > 
> > I'm not sure I understand your question.  If it means what I think, then
> > yes, you can set the threshold and scale values per table.
> 
> Yes, that's what I was asking. Will those values be in flat files or in the 
> cataloge?[1] For what I see, it looks like flat files (presumably 
> postgresql.conf)

You set cluster-wide values in postgresql.conf, and table-specific
values in the pg_autovacuum table.

Additionally you can disable autovacuum on a per-table basis.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)


Re: Integrated autovacuum

От
Josh Berkus
Дата:
Alvaro,

> Also, somebody (Rod Taylor I think) proposed changed the variable names
> to
>
> vacuum_auto_vacuum_scale_factor
> vacuum_auto_analyze_scale_factor

I see what Rod's getting at, but I find that version of the option less 
readable ...

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco