insertion becoming slow

On Mon, Sep 26, 2005 at 05:55:18PM +0530, surabhi.ahuja wrote:

i have seen that after insertion say (20 k rows) the insertion to tables becomes slow..why is it?

Most likely due to indexes.

is there any way in which u can stop the performance from degrading

If you're loading from scratch, don't create the indexes until after the
load is done.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Mon, 2005-09-26 at 12:58, Jim C. Nasby wrote:

On Mon, Sep 26, 2005 at 05:55:18PM +0530, surabhi.ahuja wrote:

i have seen that after insertion say (20 k rows) the insertion to tables becomes slow..why is it?

Most likely due to indexes.

is there any way in which u can stop the performance from degrading

If you're loading from scratch, don't create the indexes until after the
load is done.

And don't forget the corallary, don't analyze an empty table then insert
thousands of rows.

(I know Jim knows this, this is for surabhi)

Let's say we do:

truncate table;
analyze table; (alternately, analyze parent table)
insert into table ... (repeat 20,000 times)

And that this table has fk references or check constraints that need to
be checked on insert. Now the query planner looks at the stats, says,
they table has only 1 or so rows, so I'll sequentially scan it for
matches.

Well, it did have 0 or 1 rows when we started, but somewhere along the
line, as we approached 20,000 rows, the planner needed to switch to an
index scan.

The simple fix is:

Don't analyze an empty table before insertion.

Slightly less simple fix:

analyze your table every 1,000 or so inserts, especially at the
beginning.

Hi all,

I've recently asked a similar question, which received no useful answer
yet, so I'll drop in too.

In my case, the table I was inserting to was a quite big one already to
start with (and analyzed so), so I was expecting that it will not slow
down due to indexes, as they were quite big to start with as I said.

What I mean is that I expected that the speed will be more or less
constant over the whole inserting. But the result was that after a while
the average insert speed dropped considerably and suddenly, which I
can't explain and would like to know what caused it...
The table was ~100 million live rows and quite often updated, and the
insert was ~40 million rows. After ~10 million rows the average speed
dropped suddenly about 4 times.

My only suspicion would be that the table had a quite big amount of free
space in it at the beginning due to the fact that it is quite often
updated, and then the free space was exhausted. So the speed difference
might come from the difference in using free space versus creating new
pages ? Or the same thing for the b-tree indexes.

Is there any other reasonable explanation for this ? As I see this kind
of behavior consistently, speed OK on start of inserting, and then slow
down, and I would like to know if I can tune my DB to cope with it or
just accept that it works like this...

Cheers,
Csaba.

On Tue, Sep 27, 2005 at 10:24:02AM +0200, Csaba Nagy wrote:

Hi all,

I've recently asked a similar question, which received no useful answer
yet, so I'll drop in too.

In my case, the table I was inserting to was a quite big one already to
start with (and analyzed so), so I was expecting that it will not slow
down due to indexes, as they were quite big to start with as I said.

What I mean is that I expected that the speed will be more or less
constant over the whole inserting. But the result was that after a while
the average insert speed dropped considerably and suddenly, which I
can't explain and would like to know what caused it...
The table was ~100 million live rows and quite often updated, and the
insert was ~40 million rows. After ~10 million rows the average speed
dropped suddenly about 4 times.

My only suspicion would be that the table had a quite big amount of free
space in it at the beginning due to the fact that it is quite often
updated, and then the free space was exhausted. So the speed difference
might come from the difference in using free space versus creating new
pages ? Or the same thing for the b-tree indexes.

Is there any other reasonable explanation for this ? As I see this kind
of behavior consistently, speed OK on start of inserting, and then slow
down, and I would like to know if I can tune my DB to cope with it or
just accept that it works like this...

Cheers,
Csaba.

I can't think of any explanation for this off-hand. Can you re-run the
test on a table that doesn't have a bunch of free space in it to see if
that's what the issue was?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

OK, it's quite some time from when the original question was posted, but
now I have more data... see below.

On Thu, 2005-09-29 at 19:24, Jim C. Nasby wrote:

On Tue, Sep 27, 2005 at 10:24:02AM +0200, Csaba Nagy wrote:

Hi all,

I've recently asked a similar question, which received no useful answer
yet, so I'll drop in too.

In my case, the table I was inserting to was a quite big one already to
start with (and analyzed so), so I was expecting that it will not slow
down due to indexes, as they were quite big to start with as I said.

What I mean is that I expected that the speed will be more or less
constant over the whole inserting. But the result was that after a while
the average insert speed dropped considerably and suddenly, which I
can't explain and would like to know what caused it...
The table was ~100 million live rows and quite often updated, and the
insert was ~40 million rows. After ~10 million rows the average speed
dropped suddenly about 4 times.

My only suspicion would be that the table had a quite big amount of free
space in it at the beginning due to the fact that it is quite often
updated, and then the free space was exhausted. So the speed difference
might come from the difference in using free space versus creating new
pages ? Or the same thing for the b-tree indexes.

Is there any other reasonable explanation for this ? As I see this kind
of behavior consistently, speed OK on start of inserting, and then slow
down, and I would like to know if I can tune my DB to cope with it or
just accept that it works like this...

Cheers,
Csaba.

I can't think of any explanation for this off-hand. Can you re-run the
test on a table that doesn't have a bunch of free space in it to see if
that's what the issue was?

So the issue was that the system had other scheduled heavy activities
running I was not aware of. So when they started, the insert performance
dropped... so I guess it is all clear now, at least for me... it's the
typical case of the right hand doesn't know what the left hand does, and
the head spends a lot of time figuring out what both were doing ;-)

Cheers,
Csaba.