Обсуждение: Recommend dba maintenance tasks on a regular bases
Hello, We are currently using Postgresql 8.3.3 on Red Hat 4 and our largest database is around 8454 MB. I have recommend the below to my group but not sure if reindexing should be involved since autovacuum is on? How can I be sure auto vacumming is working fine? We haven't had any problems plus I do a query and it does list all the tables and shows the last update of auto vacuum and auto analyze. Is that it? 1- pg_dump - binary dump every midday and nightly 2 - auto vacuum autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 3 autovacuum_naptime = 1min autovacuum_vacuum_threshold = 50 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 3- rotate data logs -- Regards, Barbara Stephenson EDI Specialist/Programmer Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30507 tel: (678)989-3020 fax: (404)935-6171 barbara@turbocorp.com www.ohlogistics.com
On Fri, Sep 12, 2008 at 11:49:46AM -0400, Barbara Stephenson wrote: > I have recommend the below to my group but not sure if reindexing should be > involved since autovacuum is on? No, there's no reason to reindex regularly if everything is working as expected. > > How can I be sure auto vacumming is working fine? Check the pg_statitistic_all_tables entries in last_autovacuum and last_autoanalyze. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
On Fri, 12 Sep 2008, Barbara Stephenson wrote: > 1- pg_dump - binary dump every midday and nightly > 2 - auto vacuum > 3- rotate data logs You should also consider running a script which does a VACUUM VERBOSE weekly or twice monthly and emails you the last 8 lines of output. This will allow you to keep your FSM settings up to date. Jim Nasby's article here: http://decibel.org/~decibel/pervasive/fsm.html has some good info about the Free Space Map if you're not familiar with it. In addition, it's probably worth setting log_min_duration_statement to something like 500 or 1000 (500ms or 1s) so that you log slow queries. Note that 500ms is just an example, set it to a value you consider slow so that it will log your slow queries. Then, after you've gathered some data, run it through pgfouine. http://pgfouine.projects.postgresql.org/ -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
On Fri, Sep 12, 2008 at 9:49 AM, Barbara Stephenson <barbara@turbocorp.com> wrote: > Hello, > > We are currently using Postgresql 8.3.3 on Red Hat 4 and our largest database > is around 8454 MB. > > I have recommend the below to my group but not sure if reindexing should be > involved since autovacuum is on? > > How can I be sure auto vacumming is working fine? We haven't had any problems > plus I do a query and it does list all the tables and shows the last update > of auto vacuum and auto analyze. Is that it? > > > 1- pg_dump - binary dump every midday and nightly If uptime is critical, also look at setting up a PITR server. > 2 - auto vacuum > autovacuum = on > log_autovacuum_min_duration = 0 > autovacuum_max_workers = 3 > autovacuum_naptime = 1min > autovacuum_vacuum_threshold = 50 > autovacuum_vacuum_scale_factor = 0.2 > autovacuum_analyze_scale_factor = 0.1 What's your autovacuum sleep? 10 to 20 is pretty reasonable. If you set it much higher autovacuum may not run fast enough to keep up. Run regular vacuum verbose on the database and examine the last 20 or so lines (need to be superuser I think) to see how many free space map slots you need. FSM slots are super cheap (6 bytes each) but they do come from shared memory. A good setting for a database in your size range with high updates will be between the default 200k or so to 1M. > 3- rotate data logs pgsql has this ability built in, as does your OS. I prefer letting pgsql log as I like the options better than syslog, plus it seems to be faster. Also, don't forget to add long term periodic maintenance. Things like taking the server down every 6 or 12 months to blow out dust, run memory and drive tests, etc...
The help of reindex say:
Also, for B-tree indexes a freshly-constructed index is somewhat faster to access than one that has been updated many times, because logically adjacent pages are usually also physically adjacent in a newly built index. (This consideration does not currently apply to non-B-tree indexes.) It might be worthwhile to reindex periodically just to improve access speed.
I have a reindex of my databases periodically
Sorry for the English translation of google
I have PostgreSQL 8.1.x
The databases that we have has many updates and were being slow, revising found that some index were bigger than it should be, did not agree to the size of the table. I made a reindex of the table and consultation is his much faster and the index remained very small.
We therefore began a scheduled task that reidex my batadase periodically.
I have PostgreSQL 8.1.x
The databases that we have has many updates and were being slow, revising found that some index were bigger than it should be, did not agree to the size of the table. I made a reindex of the table and consultation is his much faster and the index remained very small.
We therefore began a scheduled task that reidex my batadase periodically.
Mensaje original:
Tengo postgresql 8.1.x
Las bases de datos que nosotros tenemos tienen muchas actualizaciones y se estaban poniendo lentas, revisando encontre que unos indices estaban mas grandes de lo que deberian de estar,no estaba de acuerdo al tamaño de la tabla. Aplique un reindex a la tabla y la consulta se hiso mucho mas rapida y el indice quedo muy pequeño.
Por lo tanto puse una tarea programada que me reidex mis bases de datos periodicamente.
> Date: Fri, 12 Sep 2008 12:40:19 -0400
> From: ajs@commandprompt.com
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Recommend dba maintenance tasks on a regular bases
>
> On Fri, Sep 12, 2008 at 11:49:46AM -0400, Barbara Stephenson wrote:
> > I have recommend the below to my group but not sure if reindexing should be
> > involved since autovacuum is on?
>
> No, there's no reason to reindex regularly if everything is working as
> expected.
>
> >
> > How can I be sure auto vacumming is working fine?
>
> Check the pg_statitistic_all_tables entries in last_autovacuum and
> last_autoanalyze.
>
> A
>
>
> --
> Andrew Sullivan
> ajs@commandprompt.com
> +1 503 667 4564 x104
> http://www.commandprompt.com/
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
Conoce el perfil completo de todos tus amigos de Windows Live Messenger justo aquí: Windows Live Spaces
On Fri, Sep 12, 2008 at 12:51 PM, Fabricio <fabrixio1@hotmail.com> wrote: > > I have a reindex of my databases periodically > > Sorry for the English translation of google > I have PostgreSQL 8.1.x > The databases that we have has many updates and were being slow, revising > found that some index were bigger than it should be, did not agree to the > size of the table. I made a reindex of the table and consultation is his > much faster and the index remained very small. > We therefore began a scheduled task that reidex my batadase periodically. This is generally a sign you aren't vacuuming aggresively enough. some usage patterns, however, lend themselves to off hours vacuuming instead of during the day. If your machine doesn't have enough bandwidth to handle vacuuming during the day, then it's probably better in the long run to look at faster I/O subsystems.
HI Jeff,
Thank you for the link to explain FSM however I understand the concept where it would be faster to know where to store data based on an insert or an update but the results of the last few lines of the verbose I still don't get it.
Our max_fsm_relations = 1000 and max_fsm_pages=153600
We have auto vacuum running and below is the last few lines from a vacuum verbose statement. Can you explain and do I need to adjust our settings?
INFO: free space map contains 51228 pages in 501 relations
DETAIL: A total of 57328 page slots are in use (including overhead).
57328 page slots are required to track all free space.
Current limits are: 153600 page slots, 1000 relations, using 965 kB.
Jeff Frost wrote:
> On Fri, 12 Sep 2008, Barbara Stephenson wrote:
> > 1- pg_dump - binary dump every midday and nightly
> > 2 - auto vacuum
> > 3- rotate data logs
>
> You should also consider running a script which does a VACUUM VERBOSE
> weekly or twice monthly and emails you the last 8 lines of output. This
> will allow you to keep your FSM settings up to date.
>
> Jim Nasby's article here: http://decibel.org/~decibel/pervasive/fsm.html
> has some good info about the Free Space Map if you're not familiar with it.
>
> In addition, it's probably worth setting log_min_duration_statement to
> something like 500 or 1000 (500ms or 1s) so that you log slow queries.
> Note that 500ms is just an example, set it to a value you consider slow so
> that it will log your slow queries. Then, after you've gathered some
> data, run it through pgfouine. http://pgfouine.projects.postgresql.org/
--
Regards,
Barbara Stephenson
EDI Specialist/Programmer
Turbo, division of OHL
2251 Jesse Jewell Pkwy
Gainesville, GA 30507
tel: (678)989-3020 fax: (404)935-6171
barbara@turbocorp.com
www.ohl.com
That output looks good with your settings. It means you are using 501 of the 1000 max_fsm_relations and 57328 of the 153600 max_fsm_pages. The check_postgres.pl nagios plugin that Greg Sabino Mullane maintains recently added a check for FSM settings. Check it out here: http://bucardo.org/check_postgres/check_postgres.pl.html#fsm_pages On Mon, 6 Oct 2008, Barbara Stephenson wrote: > HI Jeff, > Thank you for the link to explain FSM however I understand the concept where > it would be faster to know where to store data based on an insert or an > update but the results of the last few lines of the verbose I still don't get > it. > > Our max_fsm_relations = 1000 and max_fsm_pages=153600 > > We have auto vacuum running and below is the last few lines from a vacuum > verbose statement. Can you explain and do I need to adjust our settings? > > INFO: free space map contains 51228 pages in 501 relations > DETAIL: A total of 57328 page slots are in use (including overhead). > 57328 page slots are required to track all free space. > Current limits are: 153600 page slots, 1000 relations, using 965 kB. > > Jeff Frost wrote: >> On Fri, 12 Sep 2008, Barbara Stephenson wrote: >>> 1- pg_dump - binary dump every midday and nightly >>> 2 - auto vacuum >>> 3- rotate data logs >> >> You should also consider running a script which does a VACUUM VERBOSE >> weekly or twice monthly and emails you the last 8 lines of output. This >> will allow you to keep your FSM settings up to date. >> >> Jim Nasby's article here: http://decibel.org/~decibel/pervasive/fsm.html >> has some good info about the Free Space Map if you're not familiar with it. >> >> In addition, it's probably worth setting log_min_duration_statement to >> something like 500 or 1000 (500ms or 1s) so that you log slow queries. >> Note that 500ms is just an example, set it to a value you consider slow so >> that it will log your slow queries. Then, after you've gathered some >> data, run it through pgfouine. http://pgfouine.projects.postgresql.org/ > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032