Re: [PERFORM] Postgres DB maintainenance - vacuum and reindex

2010-03-16 Thread Ing. Marcos Ortiz Valmaseda

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: centa...@uci.cu
   Telf: +53 07-837-3737
 +53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu 





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: [PERFORM] Postgres DB maintainenance - vacuum and reindex

2010-03-16 Thread Ben Chobot
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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Re: [PERFORM] Postgres DB maintainenance - vacuum and reindex

2010-03-16 Thread Scott Marlowe
On Mon, Mar 15, 2010 at 11: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?

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.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[PERFORM] Postgres DB maintainenance - vacuum and reindex

2010-03-16 Thread Meena_Ramkumar

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