Thread: Foreign key performance
I'm using 7.3.2 on Linux, with a decent amount of muscle behind it (1.5 GHz PPro CPU, 1G mem, 20M/sec disks, xlog on different disk than data). I've got a database that has several foreign keys, and I'm copying a bunch of data from an MS-SQL server into it via Perl DBI. I noticed that inserts into this database are very slow, on the order of 100 per second on this hardware. All the inserts are happening in a single transaction. The postmaster I'm connected to appears to be CPU limited, as it's pegging the CPU at a constant 85 percent or more. I have no problem with that under normal circumstances (i.e., the foreign key constraints are actively being enforced): it may well be the nature of foreign keys, but the problem is this: all the keys are DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction. If I remove all the foreign key constraints, my performance goes up to 700 inserts per second! Why isn't the insert performance with all the constraints deferred approximating that of the performance I get without the foreign keys?? If anything, I should get a big delay at transaction commit time while all the foreign key constraints are checked (and, indeed, I get that too), but the performance during the transaction prior to the commit should be the same as it is without the foreign key constraints. It's almost as if the foreign key constraints are being invoked and the results ignored during the inserts... In essence, this smells like a bug to me, but I don't know enough about the internals to really call it that. Any ideas on what can be done about this? -- Kevin Brown kevin@sysexperts.com
On Thu, 17 Apr 2003, Kevin Brown wrote: > I have no problem with that under normal circumstances (i.e., the > foreign key constraints are actively being enforced): it may well be > the nature of foreign keys, but the problem is this: all the keys are > DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program > will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction. > > If I remove all the foreign key constraints, my performance goes up to > 700 inserts per second! > > Why isn't the insert performance with all the constraints deferred > approximating that of the performance I get without the foreign keys?? It appears (from some not terribly scientific experiments - see below) that it's likely to be related to managing the deferred trigger queue given that in my case at least running the constraints non-deferred was negligible in comparison. On batch inserts to three tables each with a foreign key to a table containing one row (and inserts of lots of that value), I saw a ratio of approximately 1:1.7:7 for normal inserts:non-deferred fk:deferred fk on my 7.4 dev server.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> It appears (from some not terribly scientific experiments - see below)
> that it's likely to be related to managing the deferred trigger queue
> given that in my case at least running the constraints non-deferred was
> negligible in comparison.
At one time the deferred-trigger queue had an O(N^2) behavioral problem
for large N = number of pending trigger events. But I thought we'd
fixed that. What's the test case exactly? Can you get a profile with
gprof?
regards, tom lane
On Fri, 18 Apr 2003, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > It appears (from some not terribly scientific experiments - see below) > > that it's likely to be related to managing the deferred trigger queue > > given that in my case at least running the constraints non-deferred was > > negligible in comparison. > > At one time the deferred-trigger queue had an O(N^2) behavioral problem > for large N = number of pending trigger events. But I thought we'd > fixed that. What's the test case exactly? Can you get a profile with > gprof? I'm going to tomorrow hopefully - but it looks to me that we fixed one, but possibly not another place where we read through the list unnecessarily AFAICS. I think deferredTriggerInvokeEvents (when called with immediate_only = true) is going to go through the entire list looking for immediate triggers to fire after each statement. However, excepting set constraints, any immediate triggers for any event added prior to this statement will by necessity have already been run unless I'm missing something, which means that we're often looking through entries that aren't going to have any triggers to run now in any case. Keeping a pointer to the end of the list as of last statement and going through the list from there cut the time for the deferred case in half in my simple test (about 3.3x the no fk and just under 2x the immediate).