Обсуждение: Integrated autovacuum
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/
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)
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/
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 -------------------------------------------------
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)
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)
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 -------------------------------------------------
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)
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