pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedImproving pg_dump performance when handling large numbers of LOBs
12+ messages / 7 participants
[nested] [flat]
* Improving pg_dump performance when handling large numbers of LOBs
@ 2024-02-05 19:00 Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wyatt Tellis @ 2024-02-05 19:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi,
We've inherited a series of legacy PG 12 clusters that each contain a
database that we need to migrate to a PG 15 cluster. Each database contains
about 150 million large objects totaling about 250GB. When using pg_dump
we've found that it takes a couple of weeks to dump out this much data.
We've tried using the jobs option with the directory format but that seems
to save each LOB separately which makes moving the resulting dump to
another location unwieldy. Has anyone else had to deal with dumping a
database with these many LOBs? Are there any suggestions for how to
improve performance?
Thanks,
Wyatt
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
@ 2024-02-05 19:35 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:53 ` Re: Improving pg_dump performance when handling large numbers of LOBs Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-06 07:31 ` Re: Improving pg_dump performance when handling large numbers of LOBs Shaheed Haque <shaheedhaque@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Ron Johnson @ 2024-02-05 19:35 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com> wrote:
> Hi,
>
> We've inherited a series of legacy PG 12 clusters that each contain a
> database that we need to migrate to a PG 15 cluster. Each database contains
> about 150 million large objects totaling about 250GB.
>
250*10^9 / (150*10^6) = 1667 bytes. That's *tiny*.
Am I misunderstanding you?
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-05 19:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2024-02-05 19:53 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On 2/5/24 11:35 AM, Ron Johnson wrote:
> On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com>
> wrote:
>
> Hi,
>
> We've inherited a series of legacy PG 12 clusters that each
> contain a database that we need to migrate to a PG 15 cluster.
> Each database contains about 150 million large objects totaling
> about 250GB.
>
>
> 250*10^9 / (150*10^6) = 1667 bytes. That's *tiny*.
>
> Am I misunderstanding you?
I think it less about the size of the individual objects then the number
(150 million) of them.
AFAIK that can't be handled by COPY, therefore they have to be
transferred individually.
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-05 19:55 ` Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:15 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 20:38 ` Re: Improving pg_dump performance when handling large numbers of LOBs Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 3 replies; 12+ messages in thread
From: Wyatt Tellis @ 2024-02-05 19:55 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Yes, the LOBs themselves are tiny, but there are a lot of them (~150
million) which seem to be slowing down pg_dump. Note, we did not
design/build this system and agree that use of LOBs for this purpose was
not necessary.
Wyatt
On Mon, Feb 5, 2024 at 11:36 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com>
> wrote:
>
>> Hi,
>>
>> We've inherited a series of legacy PG 12 clusters that each contain a
>> database that we need to migrate to a PG 15 cluster. Each database contains
>> about 150 million large objects totaling about 250GB.
>>
>
> 250*10^9 / (150*10^6) = 1667 bytes. That's *tiny*.
>
> Am I misunderstanding you?
>
>>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
@ 2024-02-05 20:05 ` Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:10 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2024-02-05 20:05 UTC (permalink / raw)
To: Wyatt Tellis <wyatt.tellis@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis <
wyatt.tellis@gmail.com <mailto:wyatt.tellis@gmail.com>>:
Yes, the LOBs themselves are tiny, but there are a lot of them (~150 million)
which seem to be slowing down pg_dump. Note, we did not design/build this
system and agree that use of LOBs for this purpose was not necessary.
Well, the data is there nonetheless, is it an option to convert it to bytea
before migration?
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2024-02-05 20:10 ` Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:17 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wyatt Tellis @ 2024-02-05 20:10 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
No, we don't have the ability to make schema changes and the schema in the
PG15 copy needs to match what's in the PG 12 versions
Wyatt
On Mon, Feb 5, 2024 at 12:05 PM Andreas Joseph Krogh <andreas@visena.com>
wrote:
> På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis <
> wyatt.tellis@gmail.com>:
>
> Yes, the LOBs themselves are tiny, but there are a lot of them (~150
> million) which seem to be slowing down pg_dump. Note, we did not
> design/build this system and agree that use of LOBs for this purpose was
> not necessary.
>
> Well, the data is there nonetheless, is it an option to convert it to
> bytea before migration?
>
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
Attachments:
noname (image/png, 1.9K) download
view image
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:10 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
@ 2024-02-05 20:17 ` Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:32 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ken Marshall <ktm@rice.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2024-02-05 20:17 UTC (permalink / raw)
To: Wyatt Tellis <wyatt.tellis@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis <
wyatt.tellis@gmail.com <mailto:wyatt.tellis@gmail.com>>:
No, we don't have the ability to make schema changes and the schema in the
PG15 copy needs to match what's in the PG 12 versions
Well then, I guess it boils down to how badly the ones in charge wants this
migration…
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:10 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:17 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
@ 2024-02-05 20:32 ` Ken Marshall <ktm@rice.edu>
2024-02-05 20:52 ` Re: Improving pg_dump performance when handling large numbers of LOBs Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 12+ messages in thread
From: Ken Marshall @ 2024-02-05 20:32 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: Wyatt Tellis <wyatt.tellis@gmail.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote:
>
> På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis <
> wyatt.tellis@gmail.com <mailto:wyatt.tellis@gmail.com>>:
>
> No, we don't have the ability to make schema changes and the schema in the
> PG15 copy needs to match what's in the PG 12 versions
>
> Well then, I guess it boils down to how badly the ones in charge wants this
> migration…
>
> --
> Andreas Joseph Krogh
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com;
> <https://www.visena.com;
Hi,
Can you use logical replication? Then you aren't under the gun to make
a fast copy.
Regards,
Ken
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:10 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:17 ` Re: Improving pg_dump performance when handling large numbers of LOBs Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:32 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ken Marshall <ktm@rice.edu>
@ 2024-02-05 20:52 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2024-02-05 20:52 UTC (permalink / raw)
To: Ken Marshall <ktm@rice.edu>; Andreas Joseph Krogh <andreas@visena.com>; +Cc: Wyatt Tellis <wyatt.tellis@gmail.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On 2/5/24 12:32, Ken Marshall wrote:
> On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote:
>>
>> På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis <
>> wyatt.tellis@gmail.com <mailto:wyatt.tellis@gmail.com>>:
>>
>> No, we don't have the ability to make schema changes and the schema in the
>> PG15 copy needs to match what's in the PG 12 versions
>>
>> Well then, I guess it boils down to how badly the ones in charge wants this
>> migration…
>>
>> --
>> Andreas Joseph Krogh
>> CTO / Partner - Visena AS
>> Mobile: +47 909 56 963
>> andreas@visena.com <mailto:andreas@visena.com>
>> www.visena.com <https://www.visena.com;
>> <https://www.visena.com;
>
> Hi,
>
> Can you use logical replication? Then you aren't under the gun to make
> a fast copy.
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
"Large objects (see Chapter 35) are not replicated. There is no
workaround for that, other than storing data in normal tables."
>
> Regards,
> Ken
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
@ 2024-02-05 20:15 ` Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Ron Johnson @ 2024-02-05 20:15 UTC (permalink / raw)
To: pgsql-general
Just checking... and I sympathize with your situation.
On Mon, Feb 5, 2024 at 2:56 PM Wyatt Tellis <wyatt.tellis@gmail.com> wrote:
> Yes, the LOBs themselves are tiny, but there are a lot of them (~150
> million) which seem to be slowing down pg_dump. Note, we did not
> design/build this system and agree that use of LOBs for this purpose was
> not necessary.
>
> Wyatt
>
> On Mon, Feb 5, 2024 at 11:36 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> We've inherited a series of legacy PG 12 clusters that each contain a
>>> database that we need to migrate to a PG 15 cluster. Each database contains
>>> about 150 million large objects totaling about 250GB.
>>>
>>
>> 250*10^9 / (150*10^6) = 1667 bytes. That's *tiny*.
>>
>> Am I misunderstanding you?
>>
>>>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:55 ` Re: Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
@ 2024-02-05 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2024-02-05 20:38 UTC (permalink / raw)
To: Wyatt Tellis <wyatt.tellis@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Wyatt Tellis <wyatt.tellis@gmail.com> writes:
> Yes, the LOBs themselves are tiny, but there are a lot of them (~150
> million) which seem to be slowing down pg_dump. Note, we did not
> design/build this system and agree that use of LOBs for this purpose was
> not necessary.
I don't know of anything much you can do at the user level. But there
is a patchset under development to improve pg_dump's behavior with
tons of blobs:
https://www.postgresql.org/message-id/842242.1706287466@sss.pgh.pa.us
That's intended for v17, and I'm not sure how tough it might be to
adapt to v15 pg_dump, but maybe you could make that happen. In any
case, more eyeballs reviewing that patchset would be welcome.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Improving pg_dump performance when handling large numbers of LOBs
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Re: Improving pg_dump performance when handling large numbers of LOBs Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-06 07:31 ` Shaheed Haque <shaheedhaque@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Shaheed Haque @ 2024-02-06 07:31 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Might it be worth a modest amount of time using some basic profiling to see
where the time is going? A week is a looonnngg time, even for 150e6
operations. For example, if there an unexpectedly high IO load, some
temporary M.2 storage might help?
On Tue, 6 Feb 2024, 01:36 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
> On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis <wyatt.tellis@gmail.com>
> wrote:
>
>> Hi,
>>
>> We've inherited a series of legacy PG 12 clusters that each contain a
>> database that we need to migrate to a PG 15 cluster. Each database contains
>> about 150 million large objects totaling about 250GB.
>>
>
> 250*10^9 / (150*10^6) = 1667 bytes. That's *tiny*.
>
> Am I misunderstanding you?
>
>>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2024-02-06 07:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-05 19:00 Improving pg_dump performance when handling large numbers of LOBs Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 19:35 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 19:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-05 19:55 ` Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:05 ` Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:10 ` Wyatt Tellis <wyatt.tellis@gmail.com>
2024-02-05 20:17 ` Andreas Joseph Krogh <andreas@visena.com>
2024-02-05 20:32 ` Ken Marshall <ktm@rice.edu>
2024-02-05 20:52 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-05 20:15 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-05 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-06 07:31 ` Shaheed Haque <shaheedhaque@gmail.com>
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