insert performance on fragmented table
A user on IDS 10.0.FC6 had a large SAP table round-robin fragmented across dbspaces; once two fragments hit the 32GB limit, inserts slowed sharply because only one fragment could still accept rows. Suggestions included adding dbspaces in pairs (page-level contention theory) and the idea that the engine keeps trying full fragments and only moves on after failing, so each insert wastes attempts. The poster simply added another dbspace and reported insert time halved; others noted rebuilding/reloading the table is the more thorough fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm running IDS 10.0.FC6. I fragmented the glpca table in my SAP database into 2 fragments by round robin. The fragments started to reach the 32 GB limit. So I added another dbspace. Now the first two fragments have reached the 32 GB limit and are no longer being written to. The table inserts appear to be running much slower now that it's only writting to one dbspace. Has anyone seen this problem. Regards, Paul Sullivan
Hi, The symptom doesn't surprise me. Wtih two spaces, the chances of two inserts needing to go into the same page are far less than with one space. With two spaces, two consecutive inserts will go in two different pages, one in each space. With one space, those same inserts both go in the same page. So if the inserts are happening at close to the same time, the second may have to wait for the transaction doing the first insert to finish before its insert can proceed. The cure is to add dbspaces in pair so the round-robining can continue. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "PAUL SULLIVAN" <paul.sullivan@getronics.com> To: ids@iiug.org Date: 08/27/2008 04:42 PM Subject: insert performance on fragmented table [13225] I'm running IDS 10.0.FC6. I fragmented the glpca table in my SAP database into 2 fragments by round robin. The fragments started to reach the 32 GB limit. So I added another dbspace. Now the first two fragments have reached the 32 GB limit and are no longer being written to. The table inserts appear to be running much slower now that it's only writting to one dbspace. Has anyone seen this problem. Regards, Paul Sullivan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Richard, I believe this would only happen if the table lock level was PAGE, which hopefully is not the case (can and should be verified). Maybe the insert doesn't know that the fragments are full and it keeps trying to insert... only when it fails will it try the next one. So, I believe Art's suggestion is the way to go... Altough it will probably be painful. Regards. On Wed, Aug 27, 2008 at 10:19 PM, Richard Snoke <dsnoke@us.ibm.com> wrote: > Hi, > > The symptom doesn't surprise me. Wtih two spaces, the chances of two > inserts needing to go into the same page are far less than with one space. > With two spaces, two consecutive inserts will go in two different pages, > one in each space. With one space, those same inserts both go in the same > page. So if the inserts are happening at close to the same time, the > second may have to wait for the transaction doing the first insert to > finish before its insert can proceed. The cure is to add dbspaces in pair > so the round-robining can continue. > > Cheers, > Dick Snoke > IBM Data Management - ChannelWorks > dsnoke@us.ibm.com > (404) 487-1595 > > From: > "PAUL SULLIVAN" <paul.sullivan@getronics.com> > To: > ids@iiug.org > Date: > 08/27/2008 04:42 PM > Subject: > insert performance on fragmented table [13225] > > I'm running IDS 10.0.FC6. I fragmented the glpca table in my SAP database > into > 2 fragments by round robin. The fragments started to reach the 32 GB > limit. So > I added another dbspace. Now the first two fragments have reached the 32 > GB > limit and are no longer being written to. The table inserts appear to be > running much slower now that it's only writting to one dbspace. Has anyone > > seen this problem. > > Regards, > > Paul Sullivan > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
I added another dbspace and the insert time was cut in half. Thank you Paul Sullivan
You reduced the chances of hitting one of the two full fragments. Before, nearly 66% of the time you hit a full fragment. Now, it should be 50%... Wonder why it was cut into half... On the other hand, before, 33% of the rows were a clean insert.... Now there should be 50%... Mathmatic is a funny thing :) Regards. On Thu, Aug 28, 2008 at 1:10 PM, PAUL SULLIVAN <paul.sullivan@compucom.com>wrote: > I added another dbspace and the insert time was cut in half. > > Thank you > > Paul Sullivan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Hi, I asked the same questions a week ago. My conclusion was to recreate the table and unload/load. The new discussion points in the direction that the effort could be saved: A simple adding of some fragments to the round robin strategy seems to be enough...? Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > Fernando Nunes > Sent: Thursday, August 28, 2008 2:22 PM > To: ids@iiug.org > Subject: Re: insert performance on fragmented table [13238] > > > You reduced the chances of hitting one of the two full fragments. > Before, nearly 66% of the time you hit a full fragment. Now, > it should be > 50%... Wonder why it was cut into half... > On the other hand, before, 33% of the rows were a clean > insert.... Now there > should be 50%... Mathmatic is a funny thing :) > Regards. > > On Thu, Aug 28, 2008 at 1:10 PM, PAUL SULLIVAN > <paul.sullivan@compucom.com>wrote: > > > I added another dbspace and the insert time was cut in half. > > > > Thank you > > > > Paul Sullivan > > > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >