Обсуждение: Postgres DB maintainenance - vacuum and reindex
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.
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.
Autovacuum is your friend for minimal downtime. It is configurable to let you adjust how invasive it will be, and you canhave 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
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