pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedspeedup pg_dumpall
3+ messages / 2 participants
[nested] [flat]
* speedup pg_dumpall
@ 2012-02-22 06:30 Michael Monnerie <lists.michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Monnerie @ 2012-02-22 06:30 UTC (permalink / raw)
To: pgsql-admin
On one database server, we have both MySQL und PostgreSQL running, and
via script make a fulldump. We see that MySQL is more than twice as fast
for that:
mysql 5.1: 100s (1.66 Min) 1110224519 Bytes, 10.58 MB/s
postgres 8.4: 133s (2.21 Min) 654205642 Bytes, 4.69 MB/s
The commands are:
1) mysqldump --single-transaction --flush-logs --master-data=2 --all-
databases --add-drop-table
2) pg_dumpall -c
What can I do to improve pg_dump performance?
--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc
it-management Internet Services: Protéger
http://proteger.at [gesprochen: Prot-e-schee]
Tel: +43 660 / 415 6531
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: speedup pg_dumpall
@ 2012-02-23 17:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Michael Monnerie <lists.michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 3+ messages in thread
From: Kevin Grittner @ 2012-02-23 17:08 UTC (permalink / raw)
To: Michael Monnerie <lists.michael.monnerie@is.it-management.at>; pgsql-admin
Michael Monnerie <lists.michael.monnerie@is.it-management.at> wrote:
> What can I do to improve pg_dump performance?
I can't help wondering why you're running it. For routine backups
it is generally not your best option in PostgreSQL. PITR or warm
standby are generally more useful for routine backups of any
database big enough to worry about run time.
For my purposes, pg_dump is mostly useful for dumping schema as SQL
statements or dumping data from individual tables. Prior to the
availability of pg_upgrade our main use was for upgrades from one
major release to another, but I now see it as a pretty narrow niche
utility -- at least for my shop.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: speedup pg_dumpall
@ 2012-03-07 16:52 Michael Monnerie <lists.michael.monnerie@is.it-management.at>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Monnerie @ 2012-03-07 16:52 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Am Donnerstag, 23. Februar 2012, 11:08:29 schrieb Kevin Grittner:
> > What can I do to improve pg_dump performance?
>
>
> I can't help wondering why you're running it. For routine backups
> it is generally not your best option in PostgreSQL. PITR or warm
> standby are generally more useful for routine backups of any
> database big enough to worry about run time.
We're a hoster, so usually each database belongs to a different
customer. Each customer can have a "get a dump of our database daily"
dump. Also, restoring a single db is simpler when you got a dump of it.
> For my purposes, pg_dump is mostly useful for dumping schema as SQL
> statements or dumping data from individual tables. Prior to the
> availability of pg_upgrade our main use was for upgrades from one
> major release to another, but I now see it as a pretty narrow niche
> utility -- at least for my shop.
Agreed, for a classical db server this could be better. We need the
per-db dumps, so pg_dump performance tuning would be nice. It's quite
slow currently. Someone got hints what to do against that?
--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc
it-management Internet Services: Protéger
http://proteger.at [gesprochen: Prot-e-schee]
Tel: +43 660 / 415 6531
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-03-07 16:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-22 06:30 speedup pg_dumpall Michael Monnerie <lists.michael.monnerie@is.it-management.at>
2012-02-23 17:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-03-07 16:52 ` Michael Monnerie <lists.michael.monnerie@is.it-management.at>
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