SEO

From: "TBP" <thebigpotato@nothere.co.uk>

> How many CPU VPs?

4

Andrew

----- Original Message -----
From: "TBP" <thebigpotato@nothere.co.uk>
Newsgroups: comp.databases.informix
To: <informix-list@iiug.org>
Sent: Wednesday, May 14, 2008 6:57 AM
Subject: Re: Create Index Mysteriously Slow at the End - IDS
11.10.UC2W2 -RedHatEL5


> Andrew Ford wrote:
>> From: <jprenaut@yahoo.com>
>>
>>>If you are always catching the flush_sub threads with this stack, then
>>>they have submitted an I/O request to the KAIO threads
>>
>>
>> I thought it would be interesting if I disabled KAIO and went with
>> AIOVPs.
>>
>> Totally different behaviour with the AIOVPs.
>>
>> Scanning the data goes well enough, 20K pages per second. When that is
>> done
>> I see simultaneous reads from the index dbspace and writes to the
>> physical
>> log at a rate of 200 to 1000 pages per second each with almost exactly 1
>> read for every 1 write, the physical log and index dbspace are on
>> different
>> physical disks. I don't see this physical logging with KAIO enabled.
>> LRU
>> flushing kicks in from time to time and some pages are written to the
>> index
>> dbspace.
>>
>> This goes on for a while until I force a checkpoint. At this point the
>> index reads and physical log writes stop, the LRU queues become very
>> dirty
>> and LRU flushing starts writing pages to the index dbspace at a rate of
>> 2000
>> to 5000 pages per second.
>>
>> When index pages stop being written to the index dbspace we start reading
>> from the index dbspace again, very slowly at times. Sometimes as slow as
>> 20
>> pages per second (no additional I/O going on.) Something strange also
>> happens, LRU flushing stops completely even though there are multiple LRU
>> queues that are 35% dirty (lru_min is 10, lru_max is 20.)
>>
>> The index dbspace reads continue until all of the index pages are read
>> and
>> the create index statement completes.
>>
>> Does using AIOVPs cause different behaviour when creating indexes than
>> KAIO?
>> It looks like it does, but should it?
>>
>> Everything else with the engine I have tested works great and this isn't
>> really a show stopper for us since we don't rebuild these large indexes
>> unless we're migrating to new hardware, but it is strange.
>>
>> Andrew
>>
>>
> How many CPU VPs?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

From: <jprenaut@yahoo.com>

> Have you dropped the index to rebuild it, or just building a new
> similiar index?

Dropping the index and rebuilding the same index.

> If you dropped the index, the new physical logging
> could be because the engine is now seeing that the pages it's using
> for the new index used to be valid previously existing pages so it has
> to now physically log them.

Oh my gosh. During my KAIO tests I had been dropping the index, bouncing
the engine and then building the index. For the AIOVP test I bounced the
engine, dropped the index and the rebuilt the index. The important thing
here is the checkpoint that gets executed between the dropping of the index
and the recreation of the index on the tests with no physical logging and no
checkpoint in between the dropping and creating of the index for the tests
with physical logging.

Executing a checkpoint in between dropping and creating the index eliminates
the physical logging and the AIOVPs config starts behaving like the KAIO
config. Except it looks like things start acting like I would expect. Scan
the data pages then write new index pages and finally scan the newly created
index pages. No long checkpoints.

LRU flushing does still mysteriously stop like before, I notice that IDS has
done what I asked and auto tuned my LRU flushing from 10.0/20.0 to 9.9/19.8
and this is about when the LRU flushing stops (some dirty queues are flushed
down to 9.9% dirty and some are at 35% dirty)

Andrew

I think I've found a work around to my index creation problems.

KAIO enabled
RTO_SERVER_RESTART 0, aka disabled
CKPTINTVL 28800, 8 hours
LRUs 1
CLEANERS 1
LRU min dirty 0.00000
LRU max dirty 0.00001
Data rows - 212 million
2K Data pages - 16 million
2K Index pages created - 6 million

So, what I've done here is eliminate the Chunk writes. This seemed to be
causing some problems, there would be a Cleaner with a status of C during
the long checkpoints but very little chunk writing would happen (mostly LRU
writing.) I'm agressively flushing dirty pages to disk through a single
cleaner and it keeps up for the most part (few foreground writes here and
there, increasing LRUs and Cleaners to 11 and 13 respectively seemed to give
worse performance)

We scan the 16 million data pages in 5 minutes (over 50K pages per sec)
We write 6 million index pages in 10 minutes (10K pages per sec)
We read 6 million index pages in 20 minutes (5K pages per sec)

This is the best performance I've seen. The configuration is a little weird
and wouldn't be doable if this system was in production at the time of the
index build. Lucky for me, it won't be.

I would like to eliminate that 20 minute scan of the 6 million index pages.
I think that is the new automatic update statistics, it would be nice if
this functionality was optional.

Andrew

Hello Andrew,

what happens when you try onmode -B
(used to be undoced for flushing the buffer cache the checkpoint way
without actually doing a checkpoint....)

Superboer.


On 15 mei, 11:18, "Andrew Ford" <af...@networkip.net> wrote:
> I think I've found a work around to my index creation problems.
>
> KAIO enabled
> RTO_SERVER_RESTART 0, aka disabled
> CKPTINTVL 28800, 8 hours
> LRUs 1
> CLEANERS 1
> LRU min dirty 0.00000
> LRU max dirty 0.00001
> Data rows - 212 million
> 2K Data pages - 16 million
> 2K Index pages created - 6 million
>
> So, what I've done here is eliminate the Chunk writes. This seemed to be
> causing some problems, there would be a Cleaner with a status of C during
> the long checkpoints but very little chunk writing would happen (mostly LRU
> writing.) I'm agressively flushing dirty pages to disk through a single
> cleaner and it keeps up for the most part (few foreground writes here and
> there, increasing LRUs and Cleaners to 11 and 13 respectively seemed to give
> worse performance)
>
> We scan the 16 million data pages in 5 minutes (over 50K pages per sec)
> We write 6 million index pages in 10 minutes (10K pages per sec)
> We read 6 million index pages in 20 minutes (5K pages per sec)
>
> This is the best performance I've seen. The configuration is a little weird
> and wouldn't be doable if this system was in production at the time of the
> index build. Lucky for me, it won't be.
>
> I would like to eliminate that 20 minute scan of the 6 million index pages.
> I think that is the new automatic update statistics, it would be nice if
> this functionality was optional.
>
> Andrew

"Superboer" <superboer7@t-online.de> wrote in message
news:475f7a43-274a-4512-ab0f-6d872018f293@59g2000hsb.googlegroups.com...

> what happens when you try onmode -B


With LRUs set to 127, Cleaners set to 127, LRU min/max set to 70/80,
RTO_SERVER_RESTART disabled and CKPTINTVL set to 8 hours I execute onmode -B
and continuously execute onmode -B each time I see the cleaner stop writing
to a chunk (state = C).

Doing this I get about the same performance as my previous test of

RTO_SERVER_RESTART 0, aka disabled
CKPTINTVL 28800, 8 hours
LRUs 1
CLEANERS 1
LRU min dirty 0.00000
LRU max dirty 0.00001

The only difference seems to be most of the writes are chunk writes and not
LRU writes, no change in performance.

Still 5 minutes to scan the data pages, 10 minutes to write the index pages
and 20 minutes to read the index pages.

Thanks for the suggestion,

Andrew

Hello Andrew,

hmmm sound like there are issues with checkpointing....
i suggest to file a case @ibm/informix; this may bite all of us
someday...


anyway you could
try and generate your own stuff:
create procedure generatechkpt()
define dirty decimal (4,3);


while (1=1)

select ( sum(lru_nmod) / sum ( lru_nfree + lru_nmod ))
into dirty
from syslrus ;

if (dirty < 0.75 ) then
system "sleep 1";
else
system "onmode -B";
end if
end while ;
end procedure;

execute the above as informix and set lrumax and mindirty to 99.
when the index build is done then do manually an onmode -B followed by
a onmode -c

see you

Superboer.




On 16 mei, 00:09, "Andrew Ford" <af...@networkip.net> wrote:
> "Superboer" <superbo...@t-online.de> wrote in message
>
> news:475f7a43-274a-4512-ab0f-6d872018f293@59g2000hsb.googlegroups.com...
>
> > what happens when you try onmode -B
>
> With LRUs set to 127, Cleaners set to 127, LRU min/max set to 70/80,
> RTO_SERVER_RESTART disabled and CKPTINTVL set to 8 hours I execute onmode -B
> and continuously execute onmode -B each time I see the cleaner stop writing
> to a chunk (state = C).
>
> Doing this I get about the same performance as my previous test of
>
> RTO_SERVER_RESTART 0, aka disabled
> CKPTINTVL 28800, 8 hours
> LRUs 1
> CLEANERS 1
> LRU min dirty 0.00000
> LRU max dirty 0.00001
>
> The only difference seems to be most of the writes are chunk writes and not
> LRU writes, no change in performance.
>
> Still 5 minutes to scan the data pages, 10 minutes to write the index pages
> and 20 minutes to read the index pages.
>
> Thanks for the suggestion,
>
> Andrew