Re: Create Index Mysteriously Slow at the End - IDS 11.10.UC2W2 -RedHatEL5

I think you are on to something there with the LRU's. Possibly your LRU values need to be fractionalized so that you don't have too many to flush come checkpoint time. ----- Original Message ----- Subject: Re: Create Index Mysteriously Slow at the End - IDS 11.10.UC2W2 -RedHatEL5 Date: Tue, May 13, 2008 18:31 From: "Andrew Ford" <aford@networkip.net> From: "Neil Truby" <neil.truby@ardenta.com> > > > is the dbspace to which the indexes being written very fragmented: ie > > could IDS be spending time scanning for free space? > > I can guarantee the dbspace is not fragmented. The dbspace is empty before > the index is created, the table extent sizes are 1GB, so my index extents > should be large enough to make this not a allocating many small index > extents problem either. > > I looks to me, and I could be way way off here, that the non blocking > checkpoint is attempting to flush the dirty pages to disk and the LRU > cleaners are still trying to do their business and they are involved in a > battle royale for resources because we're flooding the buffer cache with > these 3 million new index pages we just created and nobody wins. > > I am using large (to me anyhow) chunks, 100 GB for the data dbspace and 100 > GB for the index dbspace and I do have a decent number of buffers (over > 500,000 2K buffers = 1 GB) Maybe something is not optimized and IDS is > spinning its wheels trying to sort something before it dumps it to disk. > > I also see the main_loop() thread relatively often in the onstat -g act > output during these silly long checkpoints. This is the checkpoint trying > to do its thing, correct? > > The search continues... > > Thanks, > > Andrew > > ----- Original Message ----- > From: "Neil Truby" <neil.truby@ardenta.com> > Newsgroups: comp.databases.informix > To: <informix-list@iiug.org> > Sent: Tuesday, May 13, 2008 5:06 PM > Subject: Re: Create Index Mysteriously Slow at the End - IDS 11.10.UC2W2 - > RedHatEL5 > > > > > > "Andrew Ford" <aford@networkip.net> wrote in message > > news:mailman.1093.1210707144.20610.informix-list@iiug.org... > >> I'm running into some very strange high checkpoints and really poor > >> performance towards the end of an index build. Can anyone shed some > >> light on what could be going on? > >> > >> Things start off really great. Scan threads, xchng threads and psortproc > >> threads hum along just fine writing out my temporary files to > >> PSORT_DBTEMP. But when it comes time to actually put my new index pages > >> on disk things start getting weird. I start experiencing very long > >> checkpoints (700 seconds followed by 350 seconds). I realize that these > >> are non blocking checkpoints and I shouldn't be concerned but read/write > >> performance goes right into the terlet. Before the checkpoints I'm > >> reading about 30K pages a second and writing 10K pages a second, during > >> and after the checkpoints I'm reading about 2K pages a second and writing > >> 200 pages a second. > >> > >> So the really complicated stuff that I would expect to take the longest > >> time (scanning the data pages and doing all of the sorting) completes in > >> about 10 minutes while the easier stuff that I would expect to go really > >> quickly (writing the index pages to disk) is taking 40 minutes. > >> > >> IDS 11.10.UC2W2 - Red Hat EL5 > >> > >> 115 million rows - Unique index key size = 40 bytes > >> > >> 524288 2K buffers, 127 LRUs, 127 Cleaners > >> > >> Getting the same results with RTO_SERVER_RESTART enabled and disabled > >> with CKPTINVL set as low as 60 seconds and as high as 10 minutes. > >> > >> Getting the same results with lru_min and lru_max set as low as 5/10 and > >> as high as 70/80. > >> > >> Andrew > > > > I've no particualr experience on this version, but just one thing I've > > seen before in others: is the dbspace to which the indexes being written > > very fragmented: ie could IDS be spending time scanning for free space? > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >