Обсуждение: insertion becoming slow
i have seen that after insertion say (20 k rows) the insertion to tables becomes slow..why is it?
is there any way in which u can stop the performance from degrading
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 Mon, 2005-09-26 at 19: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.
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.