Re: insert performance problem

On Wed, 27 Sep 2000 15:17:30 +0100, Thanh <tqma@my-deja.com> wrote: >Oh thanks. > >Taking some advice from Frank, I played around and got some >improvement but not much, I am not sure why the write cache >is %99.xx when I first bounce the server, but then it gradually >dropped out. Below is the latest onconfig and onstat's output. >Note that this is a non-logging database. We have UnixWare 7.1.1 and >AIX-4.3.3. NOAGE and KAIO is not supported in UnixWare 7. Turn them >on in AIX did not make a difference though. > >Would puting all the indexes for this table on a different dbspace >(on the same drive - this is a single-drive server) make a difference ? Single drive servers are on a hiding to nothing! However check to see if you *can* drop any indexes - after all if the report runs overnight does it matter if it takes a bit longer? Also check to see that the distribution of keys in each index is even - you don't have a single-column index with a preponderance of keys having one value. This has caused problems for us in the past. Our example was where the possible values were something like REC, ORD, AUT, PRT and COM. We needed an index on this column as we needed to be able to go straight to the ones at AUT (for example), but they all ended up as COM. Adding an extra column (which happened to be a serial column) resolved the problem. I have seen this as a problem in Ingres as well. I am also worried about indexing the VARCHARS - these kind of columns are IMHO not normally good candidates. <mammoth snip>

In article <39d9da19.681764315@comino-leeds>, sally.woolrich@comino.com (Sally Woolrich) wrote: > On Wed, 27 Sep 2000 15:17:30 +0100, Thanh <tqma@my-deja.com> wrote: > > >Oh thanks. > > > >Taking some advice from Frank, I played around and got some > >improvement but not much, I am not sure why the write cache > >is %99.xx when I first bounce the server, but then it gradually > >dropped out. Below is the latest onconfig and onstat's output. > >Note that this is a non-logging database. We have UnixWare 7.1.1 and > >AIX-4.3.3. NOAGE and KAIO is not supported in UnixWare 7. Turn them > >on in AIX did not make a difference though. > > > >Would puting all the indexes for this table on a different dbspace > >(on the same drive - this is a single-drive server) make a difference ? > > Single drive servers are on a hiding to nothing! I tried to put all the indeces on a different dbspace (still on the same drive) and this made thing just a little worse. I found out though that 80% of I/O activity was spent in this index dbspace. No, we can't drop the indeces as reporting is not done at midnight but also at any time. The indeces were created mainly to support the reporting. > > However check to see if you *can* drop any indexes - after all if the > report runs overnight does it matter if it takes a bit longer? > > Also check to see that the distribution of keys in each index is even > - you don't have a single-column index with a preponderance of keys > having one value. This has caused problems for us in the past. Our > example was where the possible values were something like REC, ORD, > AUT, PRT and COM. We needed an index on this column as we needed to > be able to go straight to the ones at AUT (for example), but they all > ended up as COM. Adding an extra column (which happened to be a > serial column) resolved the problem. I have seen this as a problem in > Ingres as well. > > I am also worried about indexing the VARCHARS - these kind of columns > are IMHO not normally good candidates. The problem apparently was in the indexing scheme (and possibly in the design of the table). Your comments above are particular helpful and I will try them out. BTW, this table is an insert and query only (no delete or update) and is not a unique table (kind of customers' usage history). Does it make a difference in insertion performance for being not unique ? Thanks, Thanh Sent via Deja.com http://www.deja.com/ Before you buy.

On Tue, 3 Oct 2000 15:14:55 +0100 , Thanh <tqma@my-deja.com> wrote: >In article <39d9da19.681764315@comino-leeds>, > sally.woolrich@comino.com (Sally Woolrich) wrote: >> On Wed, 27 Sep 2000 15:17:30 +0100, Thanh <tqma@my-deja.com> wrote: >> >> >Oh thanks. >> > >> >Taking some advice from Frank, I played around and got some >> >improvement but not much, I am not sure why the write cache >> >is %99.xx when I first bounce the server, but then it gradually >> >dropped out. Below is the latest onconfig and onstat's output. >> >Note that this is a non-logging database. We have UnixWare 7.1.1 and >> >AIX-4.3.3. NOAGE and KAIO is not supported in UnixWare 7. Turn them >> >on in AIX did not make a difference though. >> > >> >Would puting all the indexes for this table on a different dbspace >> >(on the same drive - this is a single-drive server) make a >difference ? >> >> Single drive servers are on a hiding to nothing! > >I tried to put all the indeces on a different dbspace (still on >the same drive) and this made thing just a little worse. I found >out though that 80% of I/O activity was spent in this index dbspace. > >No, we can't drop the indeces as reporting is not done at midnight >but also at any time. The indeces were created mainly to support >the reporting. > >> >> However check to see if you *can* drop any indexes - after all if the >> report runs overnight does it matter if it takes a bit longer? >> >> Also check to see that the distribution of keys in each index is even >> - you don't have a single-column index with a preponderance of keys >> having one value. This has caused problems for us in the past. Our >> example was where the possible values were something like REC, ORD, >> AUT, PRT and COM. We needed an index on this column as we needed to >> be able to go straight to the ones at AUT (for example), but they all >> ended up as COM. Adding an extra column (which happened to be a >> serial column) resolved the problem. I have seen this as a problem in >> Ingres as well. >> >> I am also worried about indexing the VARCHARS - these kind of columns >> are IMHO not normally good candidates. > >The problem apparently was in the indexing scheme (and possibly >in the design of the table). Your comments above are particular >helpful and I will try them out. > >BTW, this table is an insert and query only (no delete or update) >and is not a unique table (kind of customers' usage history). >Does it make a difference in insertion performance for being not >unique ? > See my comments above. If you have very skewed distributions it may. The symptoms when we had the problem was that we could repair the table (back in the days of SE), repair it and repair it again yet never fix the corrupt indexes. Even unloading, dropping & recreating the table didn't work. however the best columns to index are serials, integers, smallintegers, dates and chars where they are small - say CHAR( 3 ). VARCHARS are awful as are long CHARS as the size of the index becomes totally out of proportion to the data, and it's also very slow for the system to work with as so much data has to be checked when inserting or updating. Remember that if you update a key value the effect is similar to deleteing & reinserting it. >Thanks, >Thanh > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

Thanh wrote in message <8rcpkm$omj$1@nnrp1.deja.com>... > >BTW, this table is an insert and query only (no delete or update) >and is not a unique table (kind of customers' usage history). >Does it make a difference in insertion performance for being not >unique ? > Yes, duplicate indexes and stored as a tree down the in the individual key and then a list of pointers to rows which have that index key values. When inserting - Informix has to read the whole list of row pointers to find the end to add onto! deleteing - informix has to rewrite the whole list - updating = delete+insert Make all indexes unique by appending a small (integer/serial) unique key to them. >Thanks, >Thanh > > >Sent via Deja.com http://www.deja.com/ >Before you buy.