Обсуждение: drastic reduction in speed of inserts as the table grows
Hi,
I was comparing speed of inserts in C vs JDBC and
found that as the table size increases the speed
differential decreases till there is no difference (no
problem). However inserts kept getting slower and
slower as the table size increased and the performance
became quite poor. Here is the data including the
table descriptions -
CREATE TABLE some_table(
idx serial,
a_idx int4 NOT NULL,
b_idx int4 NOT NULL,
c_address varchar(20) NOT NULL,
d_address varchar(20) NOT NULL,
PRIMARY KEY(idx),
CONSTRAINT a_fkey1 FOREIGN KEY(a_idx)
REFERENCES a_ref(idx),
CONSTRAINT b_fkey2 FOREIGN KEY(b_idx)
REFERENCES b_ref(idx)
);
CREATE INDEX some_index on some_table (a_idx, b_idx,
c_address, d_address);
Here is the performance statistics on the same table.
Note the fall in performance as the test proceeds.
# of inserts C (in sec) JDBC (in sec)
(as 1 transaction)
500 1 1.7
1000 3 3.4
2000 6 7.5
another 6000 inserts ...
then
10000 70.8
10000 283
(ran vacuum at this point to see if it helped)
10000 355
1000 36 37
100 3.8 3.8
I ran these tests on a Linux machine (299 MHz). I used
postgres v7.0.3 but then I even tried grouping a large
number of inserts in one transaction to reduce the
number of hard-disk writes (it did not make a
difference as shown in the above data)
I am concerned about the drastic fall in performance
with increase of table size. Is this expected behavior
? Would this be related to indexes existing on the
table? I would expect indexes to make inserts slower
but I do not see how it explains such a great fall in
performance with increasing table-size. Is there a way
to avoid this drop in performance ?
Thanks,
Rini
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
Rini Dutta <rinid@rocketmail.com> writes:
> Here is the performance statistics on the same table.
> Note the fall in performance as the test proceeds.
Try 7.1. I think you are running into the lots-of-pending-triggers
problem that was found and fixed awhile back.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Rini Dutta <rinid@rocketmail.com> writes: > > Here is the performance statistics on the same > table. > > Note the fall in performance as the test proceeds. > > Try 7.1. I think you are running into the > lots-of-pending-triggers > problem that was found and fixed awhile back. > > regards, tom lane I'll try it out. Just for my understanding, is the 'lots-of-pending-triggers' problem related to indexes, or to foreign keys ? Rini __________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
Rini Dutta <rinid@rocketmail.com> writes:
> I'll try it out. Just for my understanding, is the
> 'lots-of-pending-triggers' problem related to indexes,
> or to foreign keys ?
Foreign keys. Does your speed problem go away if the table being
inserted into has no foreign keys?
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Rini Dutta <rinid@rocketmail.com> writes: > > Here is the performance statistics on the same > table. > > Note the fall in performance as the test proceeds. > > Try 7.1. I think you are running into the > lots-of-pending-triggers > problem that was found and fixed awhile back. > > regards, tom lane If the degrading performance issue is solved, are JDBC and C still expected to show similar performance in case of inserts ? I"ll probably try it out but just wanted to know if anybody already has an insight in to this. Rini __________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/