Re: Create Index Mysteriously Slow at the End - IDS 11.10.UC2W2 -RedHatEL5
I think I've found a work around to my index creation problems.
KAIO enabled
RTO_SERVER_RESTART 0, aka disabled
CKPTINTVL 28800, 8 hoursLRUs 1
CLEANERS 1LRU 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