|
Home > Archive > MS SQL Server > October 2006 > Increasing writes by lazy writer and threads
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Increasing writes by lazy writer and threads
|
|
|
| My SQL Server 2000 database does transaction processing. I have
observed transactions taking longer than expected on a periodic basis,
say every 10-30 minutes depending on load. It appears that the
CHECKPOINT process is writing out a large amount of data on those
intervals, and its bursty writes delay my application server operations
up to 3 seconds (they normally take 100ms or less). I'm using Dell
dual-CPU hardware with 4GB memory, PERC 4e/DI and 10K RPM 146GB drives,
with RAID 5 data and RAID 1 log files.
When I created a scheduled job to run the CHECKPOINT every minute, the
3 second delay disappeared. Now the checkpoint seems to introduce no
more than a 100 ms delay once a minute (depending on the writes it must
do). Of course as my transaction volume increases this delay will
increase as well.
I thought the LAZY WRITER and other threads were supposed to do writes
so that the checkpointer did not have to. But in my DB that has very
low CPU usage, these writes seem to build up for the checkpointer to
do.
Now for the questions.
a) Why don't the lazy writer and other threads do more writing, forcing
checkpoint to do it?
b) Is there a way to tune or configure the lazy writer and/or threads
to write more dirty pages to disk at all times, so that I spread the
I/O load out rather than bursting it periodically?
c) Is there anything I can do in hardware to make this run smoother?
d) I have heard that SQL Server 2005 has more control over the resource
usage of the checkpointer, so you can tell it to use a smaller
percentage of the I/O resources of the system. Are there any other
configs in 2005 that reduce the reliance on the checkpointer?
Thanks.
Dave
| |
| Paul Cahill 2006-10-24, 6:28 pm |
| What build are you on? There was a bug fix to SQL 2000 SP4 where
autocheckpointing was not happening correctly.
http://www.aspfaq.com/sql2000builds.asp
Paul
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158326683.211285.82900@p79g2000cwp.googlegroups.com...
> My SQL Server 2000 database does transaction processing. I have
> observed transactions taking longer than expected on a periodic basis,
> say every 10-30 minutes depending on load. It appears that the
> CHECKPOINT process is writing out a large amount of data on those
> intervals, and its bursty writes delay my application server operations
> up to 3 seconds (they normally take 100ms or less). I'm using Dell
> dual-CPU hardware with 4GB memory, PERC 4e/DI and 10K RPM 146GB drives,
> with RAID 5 data and RAID 1 log files.
>
> When I created a scheduled job to run the CHECKPOINT every minute, the
> 3 second delay disappeared. Now the checkpoint seems to introduce no
> more than a 100 ms delay once a minute (depending on the writes it must
> do). Of course as my transaction volume increases this delay will
> increase as well.
>
> I thought the LAZY WRITER and other threads were supposed to do writes
> so that the checkpointer did not have to. But in my DB that has very
> low CPU usage, these writes seem to build up for the checkpointer to
> do.
>
> Now for the questions.
>
> a) Why don't the lazy writer and other threads do more writing, forcing
> checkpoint to do it?
>
> b) Is there a way to tune or configure the lazy writer and/or threads
> to write more dirty pages to disk at all times, so that I spread the
> I/O load out rather than bursting it periodically?
>
> c) Is there anything I can do in hardware to make this run smoother?
>
> d) I have heard that SQL Server 2005 has more control over the resource
> usage of the checkpointer, so you can tell it to use a smaller
> percentage of the I/O resources of the system. Are there any other
> configs in 2005 that reduce the reliance on the checkpointer?
>
> Thanks.
>
> Dave
>
| |
|
|
Paul Cahill wrote:
> What build are you on? There was a bug fix to SQL 2000 SP4 where
> autocheckpointing was not happening correctly.
> http://www.aspfaq.com/sql2000builds.asp
>
I am on SP3. We tried SP4 but had other issues with it.
And the auto-checkpointing seems to be happening just fine, every 10-30
minutes depending on transaction volume. My real question is how to
get the worker threads and lazy writer to do more of the work, because
the checkpoint is saturating my write-I/O path once every 10-30
minutes.
Thanks.
Dave
| |
| Paul Cahill 2006-10-24, 6:28 pm |
| Sorry if this is not answering your question either but our auto checkpoints
happen far more regularly.
As your are doing transaction processing I'd expect yours to be
checkpointing rapidly.
What's your recovery interval set to?
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158335473.528074.315090@d34g2000cwd.googlegroups.com...
>
> Paul Cahill wrote:
>
> I am on SP3. We tried SP4 but had other issues with it.
>
> And the auto-checkpointing seems to be happening just fine, every 10-30
> minutes depending on transaction volume. My real question is how to
> get the worker threads and lazy writer to do more of the work, because
> the checkpoint is saturating my write-I/O path once every 10-30
> minutes.
>
> Thanks.
>
> Dave
>
| |
|
| Hi, Paul. It is set to 0 minutes, so aiming for rapid recovery. I
thought this would give the shortest possible checkpointing interval,
which seems to be relatively long (eg 10-30 minutes). Even in that
period of time the lazy writer and worker threads seem to leave up to
100MB of dirty pages for the checkpointer to deal with. OTOH we have
2GB memory allocated to SQL Server, so 100MB is only 5% and perhaps is
not enough to tickle those threads into trying very hard.
Is it possible I should reduce mem available to SQL Server and would
get higher duty rates from lazy writer and worker threads?
| |
| Roger Wolter[MSFT] 2006-10-24, 6:28 pm |
| A recovery interval of 0 doesn't necessarily mean frequent checkpoints. It
means SQL Server will decide when it's a good time to do a checkpoint -
although this generally means a checkpoint about once a minute. Doing the
checkpoint commands periodically is the only way to really control the
checkpoint frequency. Reducing memory would cause the lazy writer to kick
in more often but it would also cause more memory thrashing so all your
transactions would be slow - not just the ones during the checkpoint. .
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158349284.247868.163700@m73g2000cwd.googlegroups.com...
> Hi, Paul. It is set to 0 minutes, so aiming for rapid recovery. I
> thought this would give the shortest possible checkpointing interval,
> which seems to be relatively long (eg 10-30 minutes). Even in that
> period of time the lazy writer and worker threads seem to leave up to
> 100MB of dirty pages for the checkpointer to deal with. OTOH we have
> 2GB memory allocated to SQL Server, so 100MB is only 5% and perhaps is
> not enough to tickle those threads into trying very hard.
>
> Is it possible I should reduce mem available to SQL Server and would
> get higher duty rates from lazy writer and worker threads?
>
| |
| Tibor Karaszi 2006-10-24, 6:28 pm |
| Also, RAID5 is very slow for writes, so then the checkpoint kicks in, it saturates your write cache
(if any) and then you pay the write penalty for RAID5. http://www.baarf.com/
As for the lazywriter, it tries to maintain a certain number of pages on the free list (not used).
If you have lots of memory, it can have a good free list, but still a lot of dirty pages (for the
checkpoint process to take care of).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:Ovnd8PR2GHA.4264@TK2MSFTNGP05.phx.gbl...
>A recovery interval of 0 doesn't necessarily mean frequent checkpoints. It means SQL Server will
>decide when it's a good time to do a checkpoint - although this generally means a checkpoint about
>once a minute. Doing the checkpoint commands periodically is the only way to really control the
>checkpoint frequency. Reducing memory would cause the lazy writer to kick in more often but it
>would also cause more memory thrashing so all your transactions would be slow - not just the ones
>during the checkpoint. .
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "DaveF" <dfoulser@paytronix.com> wrote in message
> news:1158349284.247868.163700@m73g2000cwd.googlegroups.com...
>
>
| |
|
| RAID 5 - Yes, slow. We are moving to RAID 10 to increase write
performance.
Lazy writer free list - is there a way to tune it to work harder, even
if the free list is big enough? Or ask for a bigger free list?
Likewise, any way I can get the worker threads to do more writing while
waiting for async I/O reads to complete?
Ideally I would spread my writes out to a more uniform distribution
over time, so as not to saturate cache or disks. Lazy writer and
worker threads are two resources I have not yet learned how to tune, so
would appreciate expert pointers on these two.
DF
| |
| Roger Wolter[MSFT] 2006-10-24, 6:28 pm |
| It's interesting that you have a different approach to this issue than what
we normally see. You want all transactions to be uniformly slower rather
than having one slow transaction every 10 minutes or so. The question we
usually see here is "how do I prevent checkpoints?" Their approach is to
make checkpoints so far apart that in practice they never happen and rather
force a checkpoint during off hours. It sounds like your update rate is so
low that this might be something to consider It sounds like what you would
be most happy with would be synchronous disk writes and there's no way to
make SQL Server do this. I don't see a lot of difference between adjusting
parameters to force checkpoints more often and doing scheduled checkpoints.
If you don't want to use agent to schedule checkpoints you might want to
look into using Serviced Broker for the scheduling. Perhaps adapting this
approach to checkpoints instead of backups:
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158532850.272547.202660@i3g2000cwc.googlegroups.com...
> RAID 5 - Yes, slow. We are moving to RAID 10 to increase write
> performance.
>
> Lazy writer free list - is there a way to tune it to work harder, even
> if the free list is big enough? Or ask for a bigger free list?
>
> Likewise, any way I can get the worker threads to do more writing while
> waiting for async I/O reads to complete?
>
> Ideally I would spread my writes out to a more uniform distribution
> over time, so as not to saturate cache or disks. Lazy writer and
> worker threads are two resources I have not yet learned how to tune, so
> would appreciate expert pointers on these two.
>
> DF
>
| |
| Tibor Karaszi 2006-10-24, 6:28 pm |
| I don't know of a way to tune the lazy writer for a larger free list. I'm sure Roger would have
picked up on that if such possibilities exists.
(Also, I probably simplified the protocol described in my earlier post, so it might not
(theoretically) be so easy so you just change the free list. One downside I could see is that you
would utilize less of your memory in the machine, since you'd say "I want this amount of memory free
all the time". )
I agree with Roger to spread out some checkpoints. Perhaps someone from the engine team picks up on
this and
a) sort out the possible simplifications/misconceptions I had in my earlier post and
b) confirm or reject the assumption that we don't have such knobs to tweak.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158532850.272547.202660@i3g2000cwc.googlegroups.com...
> RAID 5 - Yes, slow. We are moving to RAID 10 to increase write
> performance.
>
> Lazy writer free list - is there a way to tune it to work harder, even
> if the free list is big enough? Or ask for a bigger free list?
>
> Likewise, any way I can get the worker threads to do more writing while
> waiting for async I/O reads to complete?
>
> Ideally I would spread my writes out to a more uniform distribution
> over time, so as not to saturate cache or disks. Lazy writer and
> worker threads are two resources I have not yet learned how to tune, so
> would appreciate expert pointers on these two.
>
> DF
>
| |
|
| Roger,
Thanks, very useful. It is good to challenge my thinking on this.
We're doing real-time transactions for retail and want to maintain 2
second SLA at the point of sale, including all network traffic. Taking
3 sec at app server makes this hard to do. At our rates we could have
dozens or hundreds of retail customers affected by slowness, and the
checkpoints just get more frequent during busy times. Hard to image
how to push checkpoints outside a 10-hour busy window when they would
normally happen every 10 minutes or less (at peak times) and seem to
write about 100MB - that would scale to about 6GB, triple my available
memory.
Another thing to consider is my disk array. We have internal RAID on
dell equipment (perc 4). The 100MB of checkpoint write seems to delay
my app server work by 3+ seconds. Another disk management system that
allowed my app servers to do their read/write activity while flushing
cache could eliminate the perceived problem. Not a SQL Server issue,
but probably something seen by many you know. Any suggestions on
direct attached SCSI, SAS vs Fibre channel SAN in this regard?
Dave
| |
|
| One more thing on running checkpoints more frequently. The scheduler
limits to run them once a minute, but at some transaction rate even
that will take a "long" time. Looking ahead to that is one reason why
I ask about getting the lazy writer and worker threads to do some of
the writes opportunistically.
Dave
| |
| Roger Wolter[MSFT] 2006-10-24, 6:29 pm |
| Have you tried the recovery interval at 1 instead of 0?
The lazy writer will write more often if it needs to get rid of dirty pages
because the instance is out of buffer memory.
The Service Broker scheduler example allows you the schedule checkpoints
down to 1 second intervals - although I wouldn't recommend that.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"DaveF" <dfoulser@paytronix.com> wrote in message
news:1158591696.988523.105800@e3g2000cwe.googlegroups.com...
> One more thing on running checkpoints more frequently. The scheduler
> limits to run them once a minute, but at some transaction rate even
> that will take a "long" time. Looking ahead to that is one reason why
> I ask about getting the lazy writer and worker threads to do some of
> the writes opportunistically.
>
> Dave
>
|
|
|
|
|