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
>
>