Re: CLEANERS
A user raised CLEANERS from 64 to 128 and began seeing a warning in the online log, asking whether to revert. The replies never address the specific message; instead they debate page-cleaner tuning. Points made: the manual's one-cleaner-per-disk rule, that at checkpoint cleaners are assigned to chunks (sorted writes) but to LRU queues for LRU writes, and that LRU writes beat foreground writes because SQL threads aren't stalled. Suggested settings: LRUS = max(NUMCPUVPS+2, 4) with CLEANERS = LRUS+2 (SAP experience), or CLEANERS = greater of disk count and LRUS (Kagel). No resolution of the original warning is recorded, and a tangential question about update statistics speeding an insert went unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hanna_shaw@my-deja.com wrote in message <90gti5$gso$1@nnrp1.deja.com>... >Hi, > >I only increased 'CLEANERS' from 64 to 128 and restarted the informix >server. I got the following warning message in log file: > >Is it serious message? What should I do? Change CLEANERS back to 64? >Before I changed CLEANERS, I never had such message. 64 or 128 page cleaners? How many disks are you running? Are most of those cleaners asleep all their life? The recommendation is one cleaner per disk, so do you have a large farm of disks to justify that number of cleaners? Have the stats shown that all these cleaners are actually doing something?
In article <3a2c1dd0@news.iprimus.com.au>, "Andrew Hamm" <ahamm@sanderson.net.au> wrote: > hanna_shaw@my-deja.com wrote in message <90gti5$gso$1@nnrp1.deja.com>... > >Hi, > > > >I only increased 'CLEANERS' from 64 to 128 and restarted the informix > >server. I got the following warning message in log file: > > > >Is it serious message? What should I do? Change CLEANERS back to 64? > >Before I changed CLEANERS, I never had such message. > > 64 or 128 page cleaners? How many disks are you running? Are most of those > cleaners asleep all their life? The recommendation is one cleaner per disk, > so do you have a large farm of disks to justify that number of cleaners? > Have the stats shown that all these cleaners are actually doing something? > > I imagine the general theory is how much does it hurt to have extra cleaners, vs how much does it hurt not too. Some people feel the same way with LRU queues, because LRU contention can really suck.(I would have said LRU queue contention, but that is a mouthful) Alot of times people suggest LRU's=CLEANERS, so that at checkpoint time each LRU gets assigned it's own CLEANER. So, if you have 128 LRU's to reduce LRU contention, and you want a CLEANER assigned to each LRU at checkpoint time, you would set CLEANERS at 128. Will Sent via Deja.com http://www.deja.com/ Before you buy.
"William Rice" <ricew@operamail.com> wrote in message
news:90isbi$1j4$1@nnrp1.deja.com...
> In article <3a2c1dd0@news.iprimus.com.au>,
> "Andrew Hamm" <ahamm@sanderson.net.au> wrote:
> > hanna_shaw@my-deja.com wrote in message
> <90gti5$gso$1@nnrp1.deja.com>...
> > >Hi,
> > >
> > >I only increased 'CLEANERS' from 64 to 128 and restarted the
informix
> > >server. I got the following warning message in log file:
> > >
> > >Is it serious message? What should I do? Change CLEANERS back to
64?
> > >Before I changed CLEANERS, I never had such message.
> >
> > 64 or 128 page cleaners? How many disks are you running? Are most of
> those
> > cleaners asleep all their life? The recommendation is one cleaner
per
> disk,
> > so do you have a large farm of disks to justify that number of
> cleaners?
> > Have the stats shown that all these cleaners are actually doing
> something?
> >
> >
> I imagine the general theory is how much does it hurt to have extra
> cleaners, vs how much does it hurt not too.
>
> Some people feel the same way with LRU queues, because LRU contention
> can really suck.(I would have said LRU queue contention, but that is a
> mouthful)
>
> Alot of times people suggest LRU's=CLEANERS, so that at checkpoint
time
> each LRU gets assigned it's own CLEANER.
During checkpoints the cleaner threads are assigned to chunks not LRUs.
They sort the modified buffers to flush them in ascending order. If you
have many chunks on the same disk(s) the you may run into disk
contention
mainly if you have consecutively numbered chunks on the same disk.
The rule of thumb, one disk -> one cleaner -> one LRU applies IMO only
if you are not using KAIO *and* have at least one AOI vp per disk *and*
doing most I/O during checkpoints (LRU_MAX_DIRTY and LRU_MIN_DIRTY
high).
If most I/O is done between checkpoints (LRU writes, LRU_*_DIRTY low)
then
the cleaner threads are assigned to a LRU queue and then work on the
MLRU.
No sorting after chunk no. is done here.
With KAIO and LRU_*_DIRTY low (10/5; 5/2; 2/1) I'd suggest
LRUS = max((NUMCPUVPS + 2) 4)
CLEANERS = LRUS + 2
as a start. At SAP we found performance gain with this configuration.
>
> So, if you have 128 LRU's to reduce LRU contention, and you want a
> CLEANER assigned to each LRU at checkpoint time, you would set
CLEANERS
> at 128.>
> Will
>
my 2 c
Christian
--
The opinions stated above are my own
and not necessarily those of my employer.
In article <90j07j$39s$1@news1.wdf.sap-ag.de>,
"Christian Knappke" <ch.knappke@gmx.net> wrote:
> "William Rice" <ricew@operamail.com> wrote in message
> news:90isbi$1j4$1@nnrp1.deja.com...
> > In article <3a2c1dd0@news.iprimus.com.au>,
> > "Andrew Hamm" <ahamm@sanderson.net.au> wrote:
> > > hanna_shaw@my-deja.com wrote in message
> > <90gti5$gso$1@nnrp1.deja.com>...
> > > >Hi,
> > > >
> > > >I only increased 'CLEANERS' from 64 to 128 and restarted the
> informix
> > > >server. I got the following warning message in log file:
> > > >
> > > >Is it serious message? What should I do? Change CLEANERS back to
> 64?
> > > >Before I changed CLEANERS, I never had such message.
> > >
> > > 64 or 128 page cleaners? How many disks are you running? Are most
of
> > those
> > > cleaners asleep all their life? The recommendation is one cleaner
> per
> > disk,
> > > so do you have a large farm of disks to justify that number of
> > cleaners?
> > > Have the stats shown that all these cleaners are actually doing
> > something?
> > >
> > >
> > I imagine the general theory is how much does it hurt to have extra
> > cleaners, vs how much does it hurt not too.
> >
> > Some people feel the same way with LRU queues, because LRU
contention
> > can really suck.(I would have said LRU queue contention, but that is
a
> > mouthful)
> >
> > Alot of times people suggest LRU's=CLEANERS, so that at checkpoint
> time
> > each LRU gets assigned it's own CLEANER.
>
> During checkpoints the cleaner threads are assigned to chunks not
LRUs.
> They sort the modified buffers to flush them in ascending order. If
you
> have many chunks on the same disk(s) the you may run into disk
> contention
> mainly if you have consecutively numbered chunks on the same disk.
My mistake. Thank you for the correction.
> The rule of thumb, one disk -> one cleaner -> one LRU applies IMO only
> if you are not using KAIO *and* have at least one AOI vp per disk
*and*
> doing most I/O during checkpoints (LRU_MAX_DIRTY and LRU_MIN_DIRTY
> high).
>
> If most I/O is done between checkpoints (LRU writes, LRU_*_DIRTY low)
> then
> the cleaner threads are assigned to a LRU queue and then work on the
> MLRU.
> No sorting after chunk no. is done here.
>
> With KAIO and LRU_*_DIRTY low (10/5; 5/2; 2/1) I'd suggest
>
> LRUS = max((NUMCPUVPS + 2) 4)
> CLEANERS = LRUS + 2
I know for a 4 CPU system, going from 8 LRU's to 42 LRU's we experienced
a fairly dramatic drop in bufwaits, and an increase in query response
time.
Once again, I would ask about the cost of having a lot of LRU's. As
long as your queues are not extremely short, what is the cost vs.
possible gain.(I would be happy to be corrected again, because I have
yet to find any significant costs to adding LRU's)
>
> as a start. At SAP we found performance gain with this configuration.
>
> >
> > So, if you have 128 LRU's to reduce LRU contention, and you want a
> > CLEANER assigned to each LRU at checkpoint time, you would set
> CLEANERS
> > at 128.> >
> > Will
> >
>
> my 2 c
> Christian
> --
>
> The opinions stated above are my own
> and not necessarily those of my employer.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
I am a little confused but some of you might be able to clear this up for me. I have a table with only 2 columns and I was loading only 2000 rows from a temp table. It was loading 5 rows per second and then I ran update stats high on the table and it completed the load within seconds. Could someone tell me how does update stats help on a insert? Thanks in advance. Sent via Deja.com http://www.deja.com/ Before you buy.
I am a little confused but some of you might be able to clear this up for me. I have a table with only 2 columns and I was loading only 2000 rows from a temp table. It was loading 5 rows per second and then I ran update stats high on the table and it completed the load within seconds. Could someone tell me how does update stats help on a insert? Thanks in advance. Sent via Deja.com http://www.deja.com/ Before you buy.
Andrew Hamm wrote: > > hanna_shaw@my-deja.com wrote in message <90gti5$gso$1@nnrp1.deja.com>... > >Hi, > > > >I only increased 'CLEANERS' from 64 to 128 and restarted the informix > >server. I got the following warning message in log file: > > > >Is it serious message? What should I do? Change CLEANERS back to 64? > >Before I changed CLEANERS, I never had such message. > > 64 or 128 page cleaners? How many disks are you running? Are most of those > cleaners asleep all their life? The recommendation is one cleaner per disk, > so do you have a large farm of disks to justify that number of cleaners? > Have the stats shown that all these cleaners are actually doing something? The manual recommends one per spindle, yes, but out here in the field we have determined that performance of checkpoints and LRU flushing under extreme load is smoothest if CLEANERS is the larger of #disks and the value of the LRUS parameter or higher. Art S. Kagel
William Rice wrote in message <90isbi$1j4$1@nnrp1.deja.com>... >In article <3a2c1dd0@news.iprimus.com.au>, > "Andrew Hamm" <ahamm@sanderson.net.au> wrote: >> hanna_shaw@my-deja.com wrote in message><90gti5$gso$1@nnrp1.deja.com>... >I imagine the general theory is how much does it hurt to have extra >cleaners, vs how much does it hurt not too. > >Some people feel the same way with LRU queues, because LRU contention >can really suck.(I would have said LRU queue contention, but that is a >mouthful) > >Alot of times people suggest LRU's=CLEANERS, so that at checkpoint time >each LRU gets assigned it's own CLEANER. > >So, if you have 128 LRU's to reduce LRU contention, and you want a >CLEANER assigned to each LRU at checkpoint time, you would set CLEANERS >at 128. It scares me to think how much load each spindle will be suffering under an onslaught of 128 cleaners during LRU writes. But the numbers look potentially interesting, when I consider what might actually be a decent spread of threads across the spindles when real write activity is actually considered. Could you reply with details of: - number of spindle, number of dbspaces on each and the counts of chunks attached to each space - a "quality" measure that attempts to state the load balance of writes attained thru thoughtful allocation of tables to said dbspaces. Words about statistical measures (sysmaster info etc) which indicate decent load-spreading will be sufficient. - settings of LRUs including max's and mins - stats about frequency and throughput of foreground, LRU and ckhpoint writes. - any other relevant facts and observations. I'm interested in what appears to be a hint of a certain style of space allocations and write behaviours. On a related subject, I note that page 11-52 of the 7.3 admin guide states in the bottom paragraph that "LRU writes are preferred over foreground writes because page cleaner threads perform buffer writes much more efficiently than sqlthreads do". But it's quite clear from the next section that only chunk writes do sorted writes, so I'm intrigued to know exactly what mechanism LRU writes use to be "much more efficient". Does anyone have the hard facts to fill in the glossed-over stuff? Are the hard facts actually detailed in a later version of the manual?
In article <3a2d750a$1@news.iprimus.com.au>,
"Andrew Hamm" <ahamm@sanderson.net.au> wrote:
> William Rice wrote in message <90isbi$1j4$1@nnrp1.deja.com>...
> >In article <3a2c1dd0@news.iprimus.com.au>,
> > "Andrew Hamm" <ahamm@sanderson.net.au> wrote:
> >> hanna_shaw@my-deja.com wrote in
message><90gti5$gso$1@nnrp1.deja.com>...
>
> >I imagine the general theory is how much does it hurt to have extra
> >cleaners, vs how much does it hurt not too.
> >
> >Some people feel the same way with LRU queues, because LRU contention
> >can really suck.(I would have said LRU queue contention, but that is
a
> >mouthful)
> >
> >Alot of times people suggest LRU's=CLEANERS, so that at checkpoint
time
> >each LRU gets assigned it's own CLEANER.
> >
> >So, if you have 128 LRU's to reduce LRU contention, and you want a
> >CLEANER assigned to each LRU at checkpoint time, you would set
CLEANERS
> >at 128.>
> It scares me to think how much load each spindle will be suffering
under an
> onslaught of 128 cleaners during LRU writes. But the numbers look
> potentially interesting, when I consider what might actually be a
decent
> spread of threads across the spindles when real write activity is
actually
> considered. Could you reply with details of:
>
Some of my opinion on this is due to pre-fuzzy checkpoints, when you
wanted your LRU_MIN/MAX enforced so that you wouldnt end up with a 20
second checkpoint.
> - number of spindle, number of dbspaces on each and the counts of
chunks
> attached to each space
> - a "quality" measure that attempts to state the load balance of
writes
> attained thru thoughtful allocation of tables to said dbspaces. Words
about
> statistical measures (sysmaster info etc) which indicate decent
> load-spreading will be sufficient.
> - settings of LRUs including max's and mins
> - stats about frequency and throughput of foreground, LRU and ckhpoint
> writes.
> - any other relevant facts and observations.
At my current workplace 95% of the data is inserted via light
appends, and most of the queries that matter do light scans. Not much
LRU/CLEANER tuning.
If you severely over configure CLEANERS, you might hit some issues. But
as you start getting more disks, and I/O subsystems with lots of
cache, it becomes harder and harder to over configure CLEANERS with a
max of 128.
Maybe someone's benchmarking will prove me wrong. I did some basic ones
on 7.30UC2 on a single CPU box with two sets of mirrored disks, and was
not able to get significant decreases in performance by bumping these
numbers really high, but I spent less than a day on it.
>
> I'm interested in what appears to be a hint of a certain style of
space
> allocations and write behaviours.
>
> On a related subject, I note that page 11-52 of the 7.3 admin guide
states
> in the bottom paragraph that "LRU writes are preferred over foreground
> writes because page cleaner threads perform buffer writes much more
> efficiently than sqlthreads do". But it's quite clear from the next
section
> that only chunk writes do sorted writes, so I'm intrigued to know
exactly
> what mechanism LRU writes use to be "much more efficient". Does anyone
have
The following is my understanding.
With a foreground write the SQL thread being executed gets put on hold
until that write is flushed, and the data can be retrieved into the
buffer. As opposed to waiting on a read, you are now waiting for a
write, and then a read. If a cleaner is cleaning an LRU, no sql thread
is put on hold.
> the hard facts to fill in the glossed-over stuff? Are the hard facts
> actually detailed in a later version of the manual?
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.