Re: pg_restore and FK constraints with large dbs

--- ow <oneway_111(at)yahoo(dot)com> wrote:
> Perhaps I should clarify.
>
> First, I ran pg_dump to extract schema and data *together*. Then I ran
> pg_restore to restore the db. It took about 1 hour to create tables and copy
> the data, then about 40 min to create indexes, then pg_restore spent 4.5
> hours
> checking one (1) FK constraint (80M table with FKs against 20K table with
> PKs)
>
> 4.5 hours to check one FK constraint - this is what I meant by bad
> performance.
> I'm looking for a way to suspend FK checks since data is coming from pg_dump
> and, hence, it's clean.
>

No solution? Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Hi ow,

When your using expensive support, like this email list, technical
replies can take some time.
Perhaps those who can help you are on the other side of the globe and
are sleeping.
Or maybe they busy trying to make a living.

Please be patient.
If you do not recieve a reply I suggest you politely re-post you question.

If all this is not fast enough for you I recommend finding a Postgresql
consultant who
is happy to be on call all day and all night for you.

There are several Postgresql consultant's on these lists, you could post
a request seeking their services.

I'm not trying to upset anyone just trying to help you get an answer for
your issues.

Thanks
Kind regards
Rudi.

ow wrote:

>--- ow <oneway_111(at)yahoo(dot)com> wrote:
>
>
>>Perhaps I should clarify.
>>
>>First, I ran pg_dump to extract schema and data *together*. Then I ran
>>pg_restore to restore the db. It took about 1 hour to create tables and copy
>>the data, then about 40 min to create indexes, then pg_restore spent 4.5
>>hours
>>checking one (1) FK constraint (80M table with FKs against 20K table with
>>PKs)
>>
>>4.5 hours to check one FK constraint - this is what I meant by bad
>>performance.
>>I'm looking for a way to suspend FK checks since data is coming from pg_dump
>>and, hence, it's clean.
>>
>>
>>
>
>No solution? Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Protect your identity with Yahoo! Mail AddressGuard
>http://antispam.yahoo.com/whatsnewfree
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>

ow wrote:

>--- ow <oneway_111(at)yahoo(dot)com> wrote:
>
>
>>Perhaps I should clarify.
>>
>>First, I ran pg_dump to extract schema and data *together*. Then I ran
>>pg_restore to restore the db. It took about 1 hour to create tables and copy
>>the data, then about 40 min to create indexes, then pg_restore spent 4.5
>>hours
>>checking one (1) FK constraint (80M table with FKs against 20K table with
>>PKs)
>>
>>4.5 hours to check one FK constraint - this is what I meant by bad
>>performance.
>>I'm looking for a way to suspend FK checks since data is coming from pg_dump
>>and, hence, it's clean.
>>
>>
>>
>
>No solution? Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Protect your identity with Yahoo! Mail AddressGuard
>http://antispam.yahoo.com/whatsnewfree
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>

On Sun, 16 Nov 2003, ow wrote:

> --- ow <oneway_111(at)yahoo(dot)com> wrote:
> > Perhaps I should clarify.
> >
> > First, I ran pg_dump to extract schema and data *together*. Then I ran
> > pg_restore to restore the db. It took about 1 hour to create tables and copy
> > the data, then about 40 min to create indexes, then pg_restore spent 4.5
> > hours
> > checking one (1) FK constraint (80M table with FKs against 20K table with
> > PKs)
> >
> > 4.5 hours to check one FK constraint - this is what I meant by bad
> > performance.
> > I'm looking for a way to suspend FK checks since data is coming from pg_dump
> > and, hence, it's clean.
> >
>
> No solution? Thanks

By the way, what does your schema look like? I created an 80M row fk
table and 20K row pk table with an int4 key between them and indexes on
the two key fields. It took about 25 minutes on my not terribly fast
system using 7.4b5 to make the foreign key between them. It might have
been faster if I'd raised sort_mem to something larger than 8192.

On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:

>
> By the way, what does your schema look like? I created an 80M row fk
> table and 20K row pk table with an int4 key between them and indexes on
> the two key fields. It took about 25 minutes on my not terribly fast
> system using 7.4b5 to make the foreign key between them. It might have
> been faster if I'd raised sort_mem to something larger than 8192.
>

i missed the start of this thread... but unless you are running 7.4 adding an FK (restore) is very slow. ZIt is fixed in 7.4.. especially w/lots of sort mem.

But the need still exists for a faster backup & restore. I've had a couple threads on this in I think -admin and -performance listts.

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Mon, 17 Nov 2003, Jeff wrote:

> On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> >
> > By the way, what does your schema look like? I created an 80M row fk
> > table and 20K row pk table with an int4 key between them and indexes on
> > the two key fields. It took about 25 minutes on my not terribly fast
> > system using 7.4b5 to make the foreign key between them. It might have
> > been faster if I'd raised sort_mem to something larger than 8192.
> >
>
> i missed the start of this thread... but unless you are running 7.4
> adding an FK (restore) is very slow. ZIt is fixed in 7.4.. especially
> w/lots of sort mem.

Yeah. At the start of the discussion I believe 7.4RC2 was mentioned
as the version which is why I'm surprised at the difference in times.

--- Jeff <threshar(at)torgo(dot)978(dot)org> wrote:
> On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> >
> > By the way, what does your schema look like? I created an 80M row fk
> > table and 20K row pk table with an int4 key between them and indexes on
> > the two key fields. It took about 25 minutes on my not terribly fast
> > system using 7.4b5 to make the foreign key between them. It might have
> > been faster if I'd raised sort_mem to something larger than 8192.
> >

PK and FK keys are of "Dkey" domain, "Dkey" domain is mapped to INT4. There's
PK index on 20K pk table, *NO* index on the fk field on 80M fk table. Why no
index on the fk field? Several reasons: (1) app logic does not call for
accessing fk table based solely on the fk (2) fk field is a part of multi-field
AK index (3) unnecessary indexes take space and slow down inserts/updates,
which is a factor for large tables.

> i missed the start of this thread... but unless you are running 7.4 adding an
> FK (restore) is very slow. ZIt is fixed in 7.4.. especially w/lots of sort
> mem.

Am running 7.4.rc2 and it takes me about 4.5 hours to verify one (1) FK
constraint.

> But the need still exists for a faster backup & restore.

Can't agree more. Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

On Mon, 17 Nov 2003, ow wrote:

> --- Jeff <threshar(at)torgo(dot)978(dot)org> wrote:
> > On Mon, 17 Nov 2003 10:40:20 -0800 (PST)
> > Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> >
> > >
> > > By the way, what does your schema look like? I created an 80M row fk
> > > table and 20K row pk table with an int4 key between them and indexes on
> > > the two key fields. It took about 25 minutes on my not terribly fast
> > > system using 7.4b5 to make the foreign key between them. It might have
> > > been faster if I'd raised sort_mem to something larger than 8192.
> > >
>
> PK and FK keys are of "Dkey" domain, "Dkey" domain is mapped to INT4. There's
> PK index on 20K pk table, *NO* index on the fk field on 80M fk table. Why no
> index on the fk field? Several reasons: (1) app logic does not call for
> accessing fk table based solely on the fk (2) fk field is a part of multi-field
> AK index (3) unnecessary indexes take space and slow down inserts/updates,
> which is a factor for large tables.

I assume you're also not modifying the pktable rows (since that would
access the fk table based solely on the fk). Does the multi-field index
start with fk or some other field? Hmm, the changes in 7.4 for alter
probably don't help as much without an index (it saves the cost of making
all those executors, but that's might be it). Doing, schema, turning off
triggers on the table, data might have worked in general, but if you've
got a textual combined dump that'd be a mess for a workaround (it might be
reasonable in pg_restore, but I don't tend to use it, so I couldn't say)

--- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
>
> I assume you're also not modifying the pktable rows (since that would
> access the fk table based solely on the fk).

I do modify the fk table: inserts and updates (but pk is never updated). Also
do not delete records.

> Does the multi-field index start with fk or some other field?
Some other field.

In general, even if I put that additional index and somehow cut down FK
verification time for 1 constraint to, say, 30 min, does that mean that the
problem is solved? Tomorrow, someone may come with 800M fk table against 200K
pk table, or he may have 10 FK constraints like this, etc. How's he going to
restore db in production environment when there's a problem and time is factor?

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> >
> > I assume you're also not modifying the pktable rows (since that would
> > access the fk table based solely on the fk).
>
> I do modify the fk table: inserts and updates (but pk is never updated). Also
> do not delete records.
>
> > Does the multi-field index start with fk or some other field?
> Some other field.
>
> In general, even if I put that additional index and somehow cut down FK
> verification time for 1 constraint to, say, 30 min, does that mean that the
> problem is solved? Tomorrow, someone may come with 800M fk table against 200K
> pk table, or he may have 10 FK constraints like this, etc. How's he going to
> restore db in production environment when there's a problem and time is factor?

Well, I've been trying to deal with the particular case at hand, ie, get
your load in a more reasonable amount of time assuming that you had more
constraints and this was going to be a particular problem for you in the
immediate term. I've mentioned that there's likely to be future
development on the issue in this thread already and that there was a
recent discussion on the topic that occurred a bit late to do anything for
7.4. I can't change the past to suit your desires, sorry, the best I can
do is point you towards getting a solution for the future.

--- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> Well, I've been trying to deal with the particular case at hand, ie, get
> your load in a more reasonable amount of time assuming that you had more
> constraints and this was going to be a particular problem for you in the
> immediate term. I've mentioned that there's likely to be future
> development on the issue in this thread already and that there was a
> recent discussion on the topic that occurred a bit late to do anything for
> 7.4. I can't change the past to suit your desires, sorry, the best I can
> do is point you towards getting a solution for the future.
>

Thanks, you've been very helpful and I appreciate the effort.

In case if there's no immediate solution, the solution could be something like
"yes, we understand the importance of the raised issue and we intent to
implement a fix for it in 7.5/8.0/or whatever".

Personally, I see the fix as the ability for the superuser to issue "SET
suspend_fk_checks(or whatever)=true/false" on the respective connection and
perhaps the respective pg_restore option.

Anything I can do to make this happen? (am not a C-developer but perhaps could
help with testing).

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > Well, I've been trying to deal with the particular case at hand, ie, get
> > your load in a more reasonable amount of time assuming that you had more
> > constraints and this was going to be a particular problem for you in the
> > immediate term. I've mentioned that there's likely to be future
> > development on the issue in this thread already and that there was a
> > recent discussion on the topic that occurred a bit late to do anything for
> > 7.4. I can't change the past to suit your desires, sorry, the best I can
> > do is point you towards getting a solution for the future.
> >
>
> Thanks, you've been very helpful and I appreciate the effort.
>
> In case if there's no immediate solution, the solution could be something like
> "yes, we understand the importance of the raised issue and we intent to
> implement a fix for it in 7.5/8.0/or whatever".

Like I said, it's been discussed and I would expect some form of this for
7.5 although I can't say for certain. Enough people were interested in
the discussion that it's likely to happen with a little championing.

> Personally, I see the fix as the ability for the superuser to issue "SET
> suspend_fk_checks(or whatever)=true/false" on the respective connection and
> perhaps the respective pg_restore option.

That was one of the options mentioned in the thread I talked about before.
There's still some question about whether it'd affect all checks or just
ones from things like alter table.

> Anything I can do to make this happen? (am not a C-developer but perhaps could
> help with testing).

Well, generally looking through the archives and making a reasonable
proposal on -general or -hackers is a good start. The code for this is
unlikely to be difficult, but there's alot of behavior to decide. Things
to think about are things like what checks do we want to disable, who can
do so and what're the mechanisms for doing so, for example, alter time
checks disabled by superusers via a set option. And then thinking about
the limitations.

--- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> Like I said, it's been discussed and I would expect some form of this for
> 7.5 although I can't say for certain. Enough people were interested in
> the discussion that it's likely to happen with a little championing.

Does not appear like it's on the TODO list though ...

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

I'm trying to avoid having to install 7.3, dump 7.2 and reload 7.3, then
install 7.4, dump 7.3 and install to 7.4 ... What's the chances of just using
the 7.4 pg_dumpall on the 7.2 database and having it work 100% ?

Also, I recall that when upgrading from 7.2 to 7.3 there was something that had
to be run to bootstrap the links between sequences and their respective tables
such that if the table was dropped the sequence would go with it. What is that
thing called? Is it included in the 7.4 tarball?

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

On Mon, 17 Nov 2003, ow wrote:

> --- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > Like I said, it's been discussed and I would expect some form of this for
> > 7.5 although I can't say for certain. Enough people were interested in
> > the discussion that it's likely to happen with a little championing.
>
> Does not appear like it's on the TODO list though ...

Hmm, that's somewhat surprising, I'd thought that the consensus was that
we were going to be doing something even if we weren't entirely sure what.
Am I right about that, Bruce?

Stephan Szabo wrote:
>
> On Mon, 17 Nov 2003, ow wrote:
>
> > --- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > > Like I said, it's been discussed and I would expect some form of this for
> > > 7.5 although I can't say for certain. Enough people were interested in
> > > the discussion that it's likely to happen with a little championing.
> >
> > Does not appear like it's on the TODO list though ...
>
> Hmm, that's somewhat surprising, I'd thought that the consensus was that
> we were going to be doing something even if we weren't entirely sure what.
> Am I right about that, Bruce?

Yep, we want to do something, but not sure what. We have:

* Allow triggers to be disabled [trigger]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Chris Gamache writes:

> I'm trying to avoid having to install 7.3, dump 7.2 and reload 7.3, then
> install 7.4, dump 7.3 and install to 7.4 ... What's the chances of just using
> the 7.4 pg_dumpall on the 7.2 database and having it work 100% ?

Pretty good. (At least no worse than going via 7.3.) If it does not
work, it's cause for a bug report.

> Also, I recall that when upgrading from 7.2 to 7.3 there was something that had
> to be run to bootstrap the links between sequences and their respective tables
> such that if the table was dropped the sequence would go with it. What is that
> thing called? Is it included in the 7.4 tarball?

Yes, contrib/adddepend.

--
Peter Eisentraut peter_e(at)gmx(dot)net

--- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> Yep, we want to do something, but not sure what. We have:
>
> * Allow triggers to be disabled [trigger]
>

Will ppl remember some time from now that it also deals with FK constraints and
pg_restore/dump options?

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

ow wrote:
>
> --- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >
> > Yep, we want to do something, but not sure what. We have:
> >
> > * Allow triggers to be disabled [trigger]
> >
>
> Will ppl remember some time from now that it also deals with FK constraints and
> pg_restore/dump options?

We probably need a multi-option approach, where you can disable all
triggers, constraints, and foreign keys depending on your needs.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

--- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> ow wrote:
> >
> > --- Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> > >
> > > Yep, we want to do something, but not sure what. We have:
> > >
> > > * Allow triggers to be disabled [trigger]
> > >

I think the following should be added to the "Allow triggers to be disabled
[trigger]" links:

http://archives.postgresql.org/pgsql-admin/2003-11/msg00323.php
http://archives.postgresql.org/pgsql-admin/2003-11/msg00313.php

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree