pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres DB maintainenance - vacuum and reindex
4+ messages / 4 participants
[nested] [flat]
* Postgres DB maintainenance - vacuum and reindex
@ 2010-03-16 05:30 Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
2010-03-16 16:58 ` Re: Postgres DB maintainenance - vacuum and reindex Scott Marlowe <scott.marlowe@gmail.com>
2010-03-16 17:06 ` Re: Postgres DB maintainenance - vacuum and reindex Ben Chobot <bench@silentmedia.com>
2010-03-16 17:29 ` Re: Postgres DB maintainenance - vacuum and reindex Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu>
0 siblings, 3 replies; 4+ messages in thread
From: Meena_Ramkumar @ 2010-03-16 05:30 UTC (permalink / raw)
To: pgsql-performance
How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
be made without shutting the server? If so, then what will be performance
degradation percentage?
--
View this message in context: http://old.nabble.com/Postgres-DB-maintainenance---vacuum-and-reindex-tp27913694p27913694.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres DB maintainenance - vacuum and reindex
2010-03-16 05:30 Postgres DB maintainenance - vacuum and reindex Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
@ 2010-03-16 16:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2010-03-16 16:58 UTC (permalink / raw)
To: Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>; +Cc: pgsql-performance
On Mon, Mar 15, 2010 at 11:30 PM, Meena_Ramkumar
<winmeena_ramkumar@yahoo.co.in> wrote:
>
> How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
> be made without shutting the server? If so, then what will be performance
> degradation percentage?
vacuum can be tuned by the various vacuum_* parameters in the
postgresql.conf file to have little or no impact on other processes
running. Depending on your IO subsystem, you can tune it up or down
to fit your needs (speed versus impact on other processes). reindex
however tends to be more intrusive to the system, and may cause some
performance degradation, which will be very dependent on your IO
subsystem (i.e. a single 7200RPM SATA drive system is more likely to
notice and be slowed down by reindexing than a 48 disk 15krpm SAS
RAID-10 array.
The more important question is what problem are you trying to solve,
and are there other, better approaches than the ones you're trying.
Without more info, no one can really say.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres DB maintainenance - vacuum and reindex
2010-03-16 05:30 Postgres DB maintainenance - vacuum and reindex Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
@ 2010-03-16 17:06 ` Ben Chobot <bench@silentmedia.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ben Chobot @ 2010-03-16 17:06 UTC (permalink / raw)
To: Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>; +Cc: pgsql-performance
Autovacuum is your friend for minimal downtime. It is configurable to let you adjust how invasive it will be, and you can have different settings per table if you wish.
As for the reindex, why do you think you will be reindexing regularly?
On Mar 15, 2010, at 10:30 PM, Meena_Ramkumar wrote:
>
> How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
> be made without shutting the server? If so, then what will be performance
> degradation percentage?
> --
> View this message in context: http://old.nabble.com/Postgres-DB-maintainenance---vacuum-and-reindex-tp27913694p27913694.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres DB maintainenance - vacuum and reindex
2010-03-16 05:30 Postgres DB maintainenance - vacuum and reindex Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
@ 2010-03-16 17:29 ` Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu>
2 siblings, 0 replies; 4+ messages in thread
From: Ing. Marcos Ortiz Valmaseda @ 2010-03-16 17:29 UTC (permalink / raw)
To: Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>; +Cc: pgsql-performance
Meena_Ramkumar escribió:
> How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
> be made without shutting the server? If so, then what will be performance
> degradation percentage?
>
To execute vacuum, you can´t stop the server, is another process of it.
If you are using a recent version of PostgreSQL, you can use autovacuum
on the server and this process is charged of this or to use VACUUM with
the right schedule. You should avoid to use VACUUM FULL, because is very
slow and it requires exclusive locks of the tables that you are
executing this, and it reduces the table size on the disc but It doesn´t
reduce the index size, but iit can make indexes larger.
With autovacuum = on, you can avoid to use VACUUM frecuently
The performance degradation depends of the quantity of tables and
databases that you have on your server.
REINDEX is another task that you can execute periodicly on you server,
but if you don´t want to affect the production task, the best thing yo
do is to drop the index and reissue the CREATE INDEX CONCURRENTLY command.
Regards
--
--------------------------------------------------------
-- Ing. Marcos Luís Ortíz Valmaseda --
-- Twitter: http://twitter.com/@marcosluis2186 --
-- FreeBSD Fan/User --
-- http://www.freebsd.org/es --
-- Linux User # 418229 --
-- Database Architect/Administrator --
-- PostgreSQL RDBMS --
-- http://www.postgresql.org --
-- http://planetpostgresql.org --
-- http://www.postgresql-es.org --
--------------------------------------------------------
-- Data WareHouse -- Business Intelligence Apprentice --
-- http://www.tdwi.org --
--------------------------------------------------------
-- Ruby on Rails Fan/Developer --
-- http://rubyonrails.org --
--------------------------------------------------------
Comunidad Técnica Cubana de PostgreSQL
http://postgresql.uci.cu
http://personas.grm.uci.cu/+marcos
Centro de Tecnologías de Gestión de Datos (DATEC)
Contacto:
Correo: centalad@uci.cu
Telf: +53 07-837-3737
+53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-03-16 17:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-16 05:30 Postgres DB maintainenance - vacuum and reindex Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
2010-03-16 16:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-16 17:06 ` Ben Chobot <bench@silentmedia.com>
2010-03-16 17:29 ` Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu>
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