pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Adding foreign key performance
16+ messages / 7 participants
[nested] [flat]

* Adding foreign key performance
@ 2003-10-28 14:16  Jeff <threshar@torgo.978.org>
  0 siblings, 4 replies; 16+ messages in thread

From: Jeff @ 2003-10-28 14:16 UTC (permalink / raw)
  To: pgsql-performance

I recalled seeing a thread on -HACKERS about some major improvements to the speed of adding an FK to an existing table in 7.4. Naturally I was curious and decided to give it a whirl. My findings are not too good. In fact, they are bad. 

Could it be this patch never made it in?

Anyway, here's the info.
Machine: Linux 2.4.18 [stock rh8], p3 500, 512mb, 4x18GB scsi raid 0 

Two tables: members and watchedmembers with 1045720 and 829994 rows respectivly.

freshly vacuum analyze'd for each PG:

7.4b4, 10k shared buff, 256mb effective cache: 485706ms
7.3.4 [same settings]: 412304.76 ms

Now the odd thing during that operation was that the machine was about oh, 50-70% _idle_ during the whole time.  

Then I started thinking more about it and realized hearing if you bump sort_mem up ridiculously high during a foreign key add it helps.  So I did.  Bumped it up
to 256MB.

[again, vacuum analyze'd each beforehand]

7.3.4: 328912ms [cpu pegged]
7.4b4: 298383ms [cpu pegged]

Quite an improvement I'd say.

Perhaps we should make note of this somewhere? Performance guide? Docs?

And this leads to the place we'd get a huge benefit: Restoring backups.. If there were some way to bump up sort_mem while doing the restore.. things would be much more pleasant. [Although, even better would be to disable FK stuff while restoring a backup and assume the backup is "sane"] How we'd go about doing that is the subject of much debate.  

Perhaps add the functionality to pg_restore? ie, pg_restore -s 256MB mybackup.db?
It would just end up issuing a set sort_mem=256000..

What do you guys think?

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 15:21  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Jeff <threshar@torgo.978.org>
  3 siblings, 1 reply; 16+ messages in thread

From: Stephan Szabo @ 2003-10-28 15:21 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance


On Tue, 28 Oct 2003, Jeff wrote:

> I recalled seeing a thread on -HACKERS about some major improvements to
> the speed of adding an FK to an existing table in 7.4. Naturally I was
> curious and decided to give it a whirl. My findings are not too good. In
> fact, they are bad.
>
> Could it be this patch never made it in?

I think it went in between b4 and b5, can you try with b5?




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 15:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jeff <threshar@torgo.978.org>
  3 siblings, 0 replies; 16+ messages in thread

From: Tom Lane @ 2003-10-28 15:51 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance

Jeff <threshar@torgo.978.org> writes:
> I recalled seeing a thread on -HACKERS about some major improvements to the speed of adding an FK to an existing table in 7.4. Naturally I was curious and decided to give it a whirl. My findings are not too good. In fact, they are bad. 
> 7.4b4, 10k shared buff, 256mb effective cache: 485706ms

You are testing the wrong version.  beta5 has the ADD FOREIGN KEY improvement.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 16:33  Jeff <threshar@torgo.978.org>
  parent: Jeff <threshar@torgo.978.org>
  3 siblings, 1 reply; 16+ messages in thread

From: Jeff @ 2003-10-28 16:33 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance

On Tue, 28 Oct 2003 09:16:45 -0500
Jeff <threshar@torgo.978.org> wrote:


> 7.3.4: 328912ms [cpu pegged]
> 7.4b4: 298383ms [cpu pegged]
> 

Just loaded up delicious 7.4b5 and wow... 

sort_mem 8192:  137038ms [lots of tmp file activity]
sort_mem 256000: 83109ms 

That's some good work there Lou, You'll make sargent for that someday.

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:06  John K. Herreshoff <jkherr@centurytel.net>
  parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 16+ messages in thread

From: John K. Herreshoff @ 2003-10-28 18:06 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance

FWIW:  I'm fiddling with that right now, and the FK think was quick...  a few 
seconds...  the tables in question have 1400 records, 343000 records and 7200 
records...  I'm running Beta5...

John.

On Tuesday 28 October 2003 10:21, Stephan Szabo wrote:
> On Tue, 28 Oct 2003, Jeff wrote:
> > I recalled seeing a thread on -HACKERS about some major improvements to
> > the speed of adding an FK to an existing table in 7.4. Naturally I was
> > curious and decided to give it a whirl. My findings are not too good. In
> > fact, they are bad.
> >
> > Could it be this patch never made it in?
>
> I think it went in between b4 and b5, can you try with b5?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:32  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Jeff <threshar@torgo.978.org>
  0 siblings, 2 replies; 16+ messages in thread

From: Stephan Szabo @ 2003-10-28 18:32 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance

On Tue, 28 Oct 2003, Jeff wrote:

> On Tue, 28 Oct 2003 09:16:45 -0500
> Jeff <threshar@torgo.978.org> wrote:
>
>
> > 7.3.4: 328912ms [cpu pegged]
> > 7.4b4: 298383ms [cpu pegged]
> >
>
> Just loaded up delicious 7.4b5 and wow...
>
> sort_mem 8192:  137038ms [lots of tmp file activity]
> sort_mem 256000: 83109ms

Hmm, 298383 -> 83109 (since those are the 256k numbers).  Not as
much as I'd have hoped, but I'll take a factor of 3.



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:34  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: John K. Herreshoff <jkherr@centurytel.net>
  0 siblings, 2 replies; 16+ messages in thread

From: Bruce Momjian @ 2003-10-28 18:34 UTC (permalink / raw)
  To: John K. Herreshoff <jkherr@centurytel.net>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; pgsql-performance

John K. Herreshoff wrote:
> FWIW:  I'm fiddling with that right now, and the FK think was quick...  a few 
> seconds...  the tables in question have 1400 records, 343000 records and 7200 
> records...  I'm running Beta5...

Did those tables have analyze statistics?  Can you try it without
statistics (I think you have to drop the tables to erase the
statistics).

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



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:34  Jeff <threshar@torgo.978.org>
  parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Jeff @ 2003-10-28 18:34 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance

On Tue, 28 Oct 2003 10:32:36 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

> Hmm, 298383 -> 83109 (since those are the 256k numbers).  Not as
> much as I'd have hoped, but I'll take a factor of 3.

Yes. those are the numbers for 256MB of sort_mem.

It seemed to saturate the IO so once I get more disks in here it should
hopefully speed up.

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:38  John K. Herreshoff <jkherr@centurytel.net>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 1 reply; 16+ messages in thread

From: John K. Herreshoff @ 2003-10-28 18:38 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; pgsql-performance

I'm not sure about the analyze stats...  Where would I find that  (in 
postgresql.conf I suppose)  I'll go see what I have set up, and get back to 
you in 30 minutes or less...

John.

On Tuesday 28 October 2003 13:34, Bruce Momjian wrote:
> John K. Herreshoff wrote:
> > FWIW:  I'm fiddling with that right now, and the FK think was quick...  a
> > few seconds...  the tables in question have 1400 records, 343000 records
> > and 7200 records...  I'm running Beta5...
>
> Did those tables have analyze statistics?  Can you try it without
> statistics (I think you have to drop the tables to erase the
> statistics).




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 18:54  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: John K. Herreshoff <jkherr@centurytel.net>
  0 siblings, 0 replies; 16+ messages in thread

From: Bruce Momjian @ 2003-10-28 18:54 UTC (permalink / raw)
  To: John K. Herreshoff <jkherr@centurytel.net>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; pgsql-performance

John K. Herreshoff wrote:
> I'm not sure about the analyze stats...  Where would I find that  (in 
> postgresql.conf I suppose)  I'll go see what I have set up, and get back to 
> you in 30 minutes or less...

They are in pg_statistic.  If you have ever anaylzed the table, there
are stats.  I am interested in the non-analyze case because that's how
the data will load into a fresh db via pg_dump.


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



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 19:04  John K. Herreshoff <jkherr@centurytel.net>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 1 reply; 16+ messages in thread

From: John K. Herreshoff @ 2003-10-28 19:04 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; pgsql-performance

That did not take long... about 13 minutes to reload the tables from an *.mdb 
file, and a second or two for each of the 'alter table foo add foreign 
key...' lines.  I tried to drop a 'referencing' table, and the database would 
not let me, said that something depended on it ;o)

Is there some way to name the foreign key so that it can be dropped later, or 
is there a way to drop the foreign key using information already in the 
database?

John.

On Tuesday 28 October 2003 13:34, Bruce Momjian wrote:
> John K. Herreshoff wrote:
> > FWIW:  I'm fiddling with that right now, and the FK think was quick...  a
> > few seconds...  the tables in question have 1400 records, 343000 records
> > and 7200 records...  I'm running Beta5...
>
> Did those tables have analyze statistics?  Can you try it without
> statistics (I think you have to drop the tables to erase the
> statistics).




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 19:13  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: John K. Herreshoff <jkherr@centurytel.net>
  0 siblings, 0 replies; 16+ messages in thread

From: Bruce Momjian @ 2003-10-28 19:13 UTC (permalink / raw)
  To: John K. Herreshoff <jkherr@centurytel.net>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Jeff <threshar@torgo.978.org>; pgsql-performance

John K. Herreshoff wrote:
> That did not take long... about 13 minutes to reload the tables from an *.mdb 
> file, and a second or two for each of the 'alter table foo add foreign 
> key...' lines.  I tried to drop a 'referencing' table, and the database would 
> not let me, said that something depended on it ;o)
> 
> Is there some way to name the foreign key so that it can be dropped later, or 
> is there a way to drop the foreign key using information already in the 
> database?

You have to use ALTER TABLE DROP CONSTRAINT perhaps.

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



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 19:22  Vivek Khera <khera@kcilink.com>
  parent: Jeff <threshar@torgo.978.org>
  3 siblings, 1 reply; 16+ messages in thread

From: Vivek Khera @ 2003-10-28 19:22 UTC (permalink / raw)
  To: pgsql-performance

>>>>> "J" == Jeff  <threshar@torgo.978.org> writes:

J> And this leads to the place we'd get a huge benefit: Restoring
J> backups.. If there were some way to bump up sort_mem while doing
J> the restore.. things would be much more pleasant. [Although, even

There was a rather substantial thread on this about the time when
7.4b1 was released.

J> better would be to disable FK stuff while restoring a backup and
J> assume the backup is "sane"] How we'd go about doing that is the
J> subject of much debate.

If you're restoring from a pg_dump -Fc (compressed dump) it already
happens for you.  The indexes and foreign keys are not added until the
very end, from what I recall.

J> Perhaps add the functionality to pg_restore? ie, pg_restore -s
J> 256MB mybackup.db?  It would just end up issuing a set
J> sort_mem=256000..

This was essentially my proposal, though I had better speed
enhancement by increasing the number of checkpoint buffers.


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-28 19:25  Jeff <threshar@torgo.978.org>
  parent: Vivek Khera <khera@kcilink.com>
  0 siblings, 0 replies; 16+ messages in thread

From: Jeff @ 2003-10-28 19:25 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

On Tue, 28 Oct 2003 14:22:04 -0500
Vivek Khera <khera@kcilink.com> wrote:

> If you're restoring from a pg_dump -Fc (compressed dump) it already
> happens for you.  The indexes and foreign keys are not added until the
> very end, from what I recall.
> 

This happens with regular dumps - at the end is a pile of alter table's
that create the indices, FK's and triggers.

Is the -Fc method different?

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-29 01:47  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Christopher Kings-Lynne @ 2003-10-29 01:47 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Jeff <threshar@torgo.978.org>; pgsql-performance

>>Just loaded up delicious 7.4b5 and wow...
>>
>>sort_mem 8192:  137038ms [lots of tmp file activity]
>>sort_mem 256000: 83109ms
> 
> 
> Hmm, 298383 -> 83109 (since those are the 256k numbers).  Not as
> much as I'd have hoped, but I'll take a factor of 3.

Hi Jeff,

Could you let us know the load times when you have done:

1. A full ANALYZE
2. A delete all from pg_statistic

So we can see if ANALYZE stats make much difference?

Chris





^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Adding foreign key performance
@ 2003-10-29 14:01  Jeff <threshar@torgo.978.org>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 16+ messages in thread

From: Jeff @ 2003-10-29 14:01 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: sszabo@megazone.bigpanda.com; pgsql-performance

On Wed, 29 Oct 2003 09:47:28 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:

> >>Just loaded up delicious 7.4b5 and wow...
> >>
> >>sort_mem 8192:  137038ms [lots of tmp file activity]
> >>sort_mem 256000: 83109ms
> > 

> 1. A full ANALYZE
> 2. A delete all from pg_statistic
> 
I had previously analyze'd before I ran those numbers.
But I did it again with and without stats. 

With:
Run 1 Time: 80157.21 ms
Run 2 Time: 80763.59 ms

Killed statistics:

Time: 80571.71 ms
Time: 80759.18 ms

Chances are it is going to seq scan regardless so the stats are rather
useless.  Perhaps in other scenarios it would help.


-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 16+ messages in thread


end of thread, other threads:[~2003-10-29 14:01 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-28 14:16 Adding foreign key performance Jeff <threshar@torgo.978.org>
2003-10-28 15:21 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-28 18:06   ` John K. Herreshoff <jkherr@centurytel.net>
2003-10-28 18:34     ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-28 18:38       ` John K. Herreshoff <jkherr@centurytel.net>
2003-10-28 18:54         ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-28 19:04       ` John K. Herreshoff <jkherr@centurytel.net>
2003-10-28 19:13         ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-28 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-28 16:33 ` Jeff <threshar@torgo.978.org>
2003-10-28 18:32   ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-28 18:34     ` Jeff <threshar@torgo.978.org>
2003-10-29 01:47     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-29 14:01       ` Jeff <threshar@torgo.978.org>
2003-10-28 19:22 ` Vivek Khera <khera@kcilink.com>
2003-10-28 19:25   ` Jeff <threshar@torgo.978.org>

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