Thread: Slow dump?
Hello, we recently migrated our system from 8.1.x to 8.2 and when running dumps have noticed an extreme decrease in speed where the dump is concerned (by more than a factor of 2). I was wondering if someone might offer some suggestions as to what may be causing the problem. How important are max_fsm_pages and max_fsm_relations to doing a dump? I was just looking over your config file and that's the only thing that jumped out at me as needing to be changed. Machine info: OS: Solaris 10 Sunfire X4100 XL 2x AMD Opteron Model 275 dual core procs 8GB of ram Pertinent postgres settings: shared_buffers: 50000 work_mem: 8192 maintenance_work_mem: 262144 max_stack_depth: 3048 (default) There doesn't seem to be any other performance degradation while the dump is running (which I suppose is good). Any ideas? -- erik jones <erik@myemma.com> software development emma(r)
Erik Jones <erik@myemma.com> writes:
> Hello, we recently migrated our system from 8.1.x to 8.2 and when
> running dumps have noticed an extreme decrease in speed where the dump
> is concerned (by more than a factor of 2).
That's odd. pg_dump is normally pretty much I/O bound, at least
assuming your tables are sizable. The only way it wouldn't be is if you
have a datatype with a very slow output converter. Have you looked into
exactly which tables are slow to dump and what datatypes they contain?
(Running pg_dump with log_min_duration_statement enabled would provide
useful data about which steps take a long time, if you're not sure.)
regards, tom lane
Tom Lane wrote: > Erik Jones <erik@myemma.com> writes: > >> Hello, we recently migrated our system from 8.1.x to 8.2 and when >> running dumps have noticed an extreme decrease in speed where the dump >> is concerned (by more than a factor of 2). >> > > That's odd. pg_dump is normally pretty much I/O bound, at least > assuming your tables are sizable. The only way it wouldn't be is if you > have a datatype with a very slow output converter. Have you looked into > exactly which tables are slow to dump and what datatypes they contain? > (Running pg_dump with log_min_duration_statement enabled would provide > useful data about which steps take a long time, if you're not sure.) > > regards, tom lane > Well, all of our tables use pretty basic data types: integer (various sizes), text, varchar, boolean, and timestamps without time zone. In addition, other than not having a lot of our foreign keys in place, there have been no other schema changes since the migration. -- erik jones <erik@myemma.com> software development emma(r)