pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance database for backup/restore
6+ messages / 5 participants
[nested] [flat]
* performance database for backup/restore
@ 2013-05-21 13:18 Jeison Bedoya <jeisonb@audifarma.com.co>
2013-05-21 13:28 ` Re: performance database for backup/restore Evgeny Shishkin <itparanoia@gmail.com>
2013-05-21 15:11 ` Re: performance database for backup/restore Steve Crawford <scrawford@pinpointresearch.com>
2013-05-21 16:11 ` Re: performance database for backup/restore Jeff Janes <jeff.janes@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Jeison Bedoya @ 2013-05-21 13:18 UTC (permalink / raw)
To: pgsql-performance
Hi people, i have a database with 400GB running in a server with 128Gb
RAM, and 32 cores, and storage over SAN with fiberchannel, the problem
is when i go to do a backup whit pg_dumpall take a lot of 5 hours, next
i do a restore and take a lot of 17 hours, that is a normal time for
that process in that machine? or i can do something to optimize the
process of backup/restore.
This is my current configuration
Postgres version 9.2.2
connections 1000
shared buffers 4096MB
work_mem = 2048MB
maintenance_work_mem = 2048MB
checkpoint_segments = 103
the other params are by default.
Thankyou very much
--
Atentamente,
JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.
--
NOTA VERDE:
No imprima este correo a menos que sea absolutamente necesario.
Ahorre papel, ayude a salvar un arbol.
--------------------------------------------------------------------
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que esta limpio.
--------------------------------------------------------------------
Este texto fue anadido por el servidor de correo de Audifarma S.A.:
Las opiniones contenidas en este mensaje no necesariamente coinciden
con las institucionales de Audifarma. La informacion y todos sus
archivos Anexos, son confidenciales, privilegiados y solo pueden ser
utilizados por sus destinatarios. Si por error usted recibe este
mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
notificarle de su error a la persona que lo envio y abstenerse de
utilizar su contenido.
--
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] 6+ messages in thread
* Re: performance database for backup/restore
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
@ 2013-05-21 13:28 ` Evgeny Shishkin <itparanoia@gmail.com>
2013-05-21 15:46 ` Re: performance database for backup/restore ktm@rice.edu <ktm@rice.edu>
2 siblings, 1 reply; 6+ messages in thread
From: Evgeny Shishkin @ 2013-05-21 13:28 UTC (permalink / raw)
To: Jeison Bedoya <jeisonb@audifarma.com.co>; +Cc: pgsql-performance
On May 21, 2013, at 5:18 PM, Jeison Bedoya <jeisonb@audifarma.com.co> wrote:
> Hi people, i have a database with 400GB running in a server with 128Gb RAM, and 32 cores, and storage over SAN with fiberchannel, the problem is when i go to do a backup whit pg_dumpall take a lot of 5 hours, next i do a restore and take a lot of 17 hours, that is a normal time for that process in that machine? or i can do something to optimize the process of backup/restore.
>
I'd recommend you to dump with
pg_dump --format=c
It will compress the output and later you can restore it in parallel with
pg_restore -j 32 (for example)
Right now you can not dump in parallel, wait for 9.3 release. Or may be someone will back port it to 9.2 pg_dump.
Also during restore you can speed up a little more by disabling fsync and synchronous_commit.
> This is my current configuration
>
> Postgres version 9.2.2
> connections 1000
> shared buffers 4096MB
> work_mem = 2048MB
> maintenance_work_mem = 2048MB
> checkpoint_segments = 103
>
> the other params are by default.
>
> Thankyou very much
>
> --
> Atentamente,
>
>
> JEISON BEDOYA DELGADO
> Adm. Servidores y Comunicaciones
> AUDIFARMA S.A.
>
>
> --
> NOTA VERDE:
> No imprima este correo a menos que sea absolutamente necesario.
> Ahorre papel, ayude a salvar un arbol.
>
> --------------------------------------------------------------------
> Este mensaje ha sido analizado por MailScanner
> en busca de virus y otros contenidos peligrosos,
> y se considera que esta limpio.
>
> --------------------------------------------------------------------
> Este texto fue anadido por el servidor de correo de Audifarma S.A.:
>
> Las opiniones contenidas en este mensaje no necesariamente coinciden
> con las institucionales de Audifarma. La informacion y todos sus
> archivos Anexos, son confidenciales, privilegiados y solo pueden ser
> utilizados por sus destinatarios. Si por error usted recibe este
> mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
> notificarle de su error a la persona que lo envio y abstenerse de
> utilizar su contenido.
>
>
>
>
> --
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance database for backup/restore
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
2013-05-21 13:28 ` Re: performance database for backup/restore Evgeny Shishkin <itparanoia@gmail.com>
@ 2013-05-21 15:46 ` ktm@rice.edu <ktm@rice.edu>
0 siblings, 0 replies; 6+ messages in thread
From: ktm@rice.edu @ 2013-05-21 15:46 UTC (permalink / raw)
To: Evgeny Shishkin <itparanoia@gmail.com>; +Cc: Jeison Bedoya <jeisonb@audifarma.com.co>; pgsql-performance
On Tue, May 21, 2013 at 05:28:31PM +0400, Evgeny Shishkin wrote:
>
> On May 21, 2013, at 5:18 PM, Jeison Bedoya <jeisonb@audifarma.com.co> wrote:
>
> > Hi people, i have a database with 400GB running in a server with 128Gb RAM, and 32 cores, and storage over SAN with fiberchannel, the problem is when i go to do a backup whit pg_dumpall take a lot of 5 hours, next i do a restore and take a lot of 17 hours, that is a normal time for that process in that machine? or i can do something to optimize the process of backup/restore.
> >
>
> I'd recommend you to dump with
>
> pg_dump --format=c
>
> It will compress the output and later you can restore it in parallel with
>
> pg_restore -j 32 (for example)
>
> Right now you can not dump in parallel, wait for 9.3 release. Or may be someone will back port it to 9.2 pg_dump.
>
> Also during restore you can speed up a little more by disabling fsync and synchronous_commit.
>
If you have the space and I/O capacity, avoiding the compress option will be
much faster. The current compression scheme using zlib type compression is
very CPU intensive and limits your dump rate. On a system that we have, a
dump without compression takes 20m and with compression 2h20m. The parallel
restore make a big difference as well.
Regards,
Ken
--
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] 6+ messages in thread
* Re: performance database for backup/restore
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
@ 2013-05-21 15:11 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 6+ messages in thread
From: Steve Crawford @ 2013-05-21 15:11 UTC (permalink / raw)
To: Jeison Bedoya <jeisonb@audifarma.com.co>; +Cc: pgsql-performance
On 05/21/2013 06:18 AM, Jeison Bedoya wrote:
> Hi people, i have a database with 400GB running in a server with 128Gb
> RAM, and 32 cores, and storage over SAN with fiberchannel, the problem
> is when i go to do a backup whit pg_dumpall take a lot of 5 hours,
> next i do a restore and take a lot of 17 hours, that is a normal time
> for that process in that machine? or i can do something to optimize
> the process of backup/restore.
It would help to know what you wish to solve. I.e. setting up a test/dev
server, testing disaster-recovery, deploying to a new server, etc. Also,
are you dumping to a file then restoring from a file or dumping to a
pipe into the restore?
If you use the custom format in pg_dump *and* are dumping to a file
*and* restoring via pg_restore, you can set the -j flag to somewhat
fewer than the number of cores (though at 128 cores I can't say where
the sweet spot might be) to allow pg_restore to run things like index
recreation in parallel to help your restore speed.
You can also *temporarily* disable fsync while rebuilding the database -
just be sure to turn it back on afterward.
Copying the files is not the recommended method for backups but may work
for certain cases. One is when you can shut down the database so the
whole directory is quiescent while you copy the files. Also, depending
on your SAN features, you *might* be able to do a snapshot of the
running PostgreSQL data directory and use that.
>
>
>
> Postgres version 9.2.2 ...
...has a nasty security issue. Upgrade. Now.
Cheers,
Steve
--
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] 6+ messages in thread
* Re: performance database for backup/restore
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
@ 2013-05-21 16:11 ` Jeff Janes <jeff.janes@gmail.com>
2013-05-21 20:03 ` Re: performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
2 siblings, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2013-05-21 16:11 UTC (permalink / raw)
To: Jeison Bedoya <jeisonb@audifarma.com.co>; +Cc: pgsql-performance
2013/5/21 Jeison Bedoya <jeisonb@audifarma.com.co>
> Hi people, i have a database with 400GB running in a server with 128Gb
> RAM, and 32 cores, and storage over SAN with fiberchannel, the problem is
> when i go to do a backup whit pg_dumpall take a lot of 5 hours, next i do a
> restore and take a lot of 17 hours, that is a normal time for that process
> in that machine? or i can do something to optimize the process of
> backup/restore.
>
How many database objects do you have? A few large objects will dump and
restore faster than a huge number of smallish objects.
Where is your bottleneck? "top" should show you whether it is CPU or IO.
I can pg_dump about 6GB/minute to /dev/null using all defaults with a small
number of large objects.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance database for backup/restore
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
2013-05-21 16:11 ` Re: performance database for backup/restore Jeff Janes <jeff.janes@gmail.com>
@ 2013-05-21 20:03 ` Jeison Bedoya <jeisonb@audifarma.com.co>
0 siblings, 0 replies; 6+ messages in thread
From: Jeison Bedoya @ 2013-05-21 20:03 UTC (permalink / raw)
To: pgsql-performance
hi jeff thanks by your answer, when you say "database objects" you
talking about the tables?, because i have 1782 tables in my database.
Umm, my boottleneck not is on CPU because the top don´t show something
about that, the memory is used 30%, and the IO not have problem, because
the Fiber channel SAN have capacity of 8GB and tthe i/o transfer to the
disk is no Upper to 1 GB.
can you explainme again how do you do a 6bg/min for pd_dump
thanks
Atentamente,
JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.
El 21/05/2013 11:11 a.m., Jeff Janes escribió:
> 2013/5/21 Jeison Bedoya <jeisonb@audifarma.com.co
> <mailto:jeisonb@audifarma.com.co>>
>
> Hi people, i have a database with 400GB running in a server with
> 128Gb RAM, and 32 cores, and storage over SAN with fiberchannel,
> the problem is when i go to do a backup whit pg_dumpall take a lot
> of 5 hours, next i do a restore and take a lot of 17 hours, that
> is a normal time for that process in that machine? or i can do
> something to optimize the process of backup/restore.
>
>
> How many database objects do you have? A few large objects will dump
> and restore faster than a huge number of smallish objects.
>
> Where is your bottleneck? "top" should show you whether it is CPU or IO.
>
> I can pg_dump about 6GB/minute to /dev/null using all defaults with a
> small number of large objects.
>
> Cheers,
>
> Jeff
>
> --
> NOTA VERDE:
> No imprima este correo
> a menos que sea absolutamente necesario.
> Ahorre papel, ayude a salvar un arbol.
>
> --------------------------------------------------------------------
> Este mensaje ha sido analizado por *MailScanner*
> <http://www.mailscanner.info/;
> en busca de virus y otros contenidos peligrosos,
> y se considera que esta limpio.
>
> --------------------------------------------------------------------
> Este texto fue anadido por el servidor de correo de Audifarma S.A.:
>
> Las opiniones contenidas en este mensaje no necesariamente coinciden
> con las institucionales de Audifarma. La informacion y todos sus
> archivos Anexos, son confidenciales, privilegiados y solo pueden ser
> utilizados por sus destinatarios. Si por error usted recibe este
> mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
> notificarle de su error a la persona que lo envia y abstenerse de
> utilizar su contenido.
--
NOTA VERDE:
No imprima este correo a menos que sea absolutamente necesario.
Ahorre papel, ayude a salvar un arbol.
--------------------------------------------------------------------
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que esta limpio.
--------------------------------------------------------------------
Este texto fue anadido por el servidor de correo de Audifarma S.A.:
Las opiniones contenidas en este mensaje no necesariamente coinciden
con las institucionales de Audifarma. La informacion y todos sus
archivos Anexos, son confidenciales, privilegiados y solo pueden ser
utilizados por sus destinatarios. Si por error usted recibe este
mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
notificarle de su error a la persona que lo envio y abstenerse de
utilizar su contenido.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-05-21 20:03 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-21 13:18 performance database for backup/restore Jeison Bedoya <jeisonb@audifarma.com.co>
2013-05-21 13:28 ` Evgeny Shishkin <itparanoia@gmail.com>
2013-05-21 15:46 ` ktm@rice.edu <ktm@rice.edu>
2013-05-21 15:11 ` Steve Crawford <scrawford@pinpointresearch.com>
2013-05-21 16:11 ` Jeff Janes <jeff.janes@gmail.com>
2013-05-21 20:03 ` Jeison Bedoya <jeisonb@audifarma.com.co>
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