pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRecommend dba maintenance tasks on a regular bases
8+ messages / 5 participants
[nested] [flat]
* Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 15:49 Barbara Stephenson <barbara@turbocorp.com>
0 siblings, 3 replies; 8+ messages in thread
From: Barbara Stephenson @ 2008-09-12 15:49 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 16:40 Andrew Sullivan <ajs@commandprompt.com>
parent: Barbara Stephenson <barbara@turbocorp.com>
2 siblings, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2008-09-12 16:40 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 16:55 Jeff Frost <jeff@frostconsultingllc.com>
parent: Barbara Stephenson <barbara@turbocorp.com>
2 siblings, 1 reply; 8+ messages in thread
From: Jeff Frost @ 2008-09-12 16:55 UTC (permalink / raw)
To: Barbara Stephenson <barbara@turbocorp.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 17:26 Scott Marlowe <scott.marlowe@gmail.com>
parent: Barbara Stephenson <barbara@turbocorp.com>
2 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2008-09-12 17:26 UTC (permalink / raw)
To: Barbara Stephenson <barbara@turbocorp.com>; +Cc: pgsql-admin
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 18:51 Fabricio <fabrixio1@hotmail.com>
parent: Andrew Sullivan <ajs@commandprompt.com>
0 siblings, 1 reply; 8+ messages in thread
From: Fabricio @ 2008-09-12 18:51 UTC (permalink / raw)
To: pgsql-admin
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.
Mensaje original:
Tengo postgresql 8.1.xLas 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
_________________________________________________________________
Live Search premia tus búsquedas, llévate hasta ¡Un Auto!
http://www.ganabuscando.com/Default.aspx=
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-09-12 19:55 Scott Marlowe <scott.marlowe@gmail.com>
parent: Fabricio <fabrixio1@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2008-09-12 19:55 UTC (permalink / raw)
To: Fabricio <fabrixio1@hotmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-10-06 19:09 Barbara Stephenson <barbara@turbocorp.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 8+ messages in thread
From: Barbara Stephenson @ 2008-10-06 19:09 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Recommend dba maintenance tasks on a regular bases
@ 2008-10-06 19:22 Jeff Frost <jeff@frostconsultingllc.com>
parent: Barbara Stephenson <barbara@turbocorp.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Frost @ 2008-10-06 19:22 UTC (permalink / raw)
To: Barbara Stephenson <barbara@turbocorp.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-10-06 19:22 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-09-12 15:49 Recommend dba maintenance tasks on a regular bases Barbara Stephenson <barbara@turbocorp.com>
2008-09-12 16:40 ` Andrew Sullivan <ajs@commandprompt.com>
2008-09-12 18:51 ` Fabricio <fabrixio1@hotmail.com>
2008-09-12 19:55 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-09-12 16:55 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-10-06 19:09 ` Barbara Stephenson <barbara@turbocorp.com>
2008-10-06 19:22 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-09-12 17:26 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox