rda submitsql: performing

Showing posts with label performing. Show all posts

Friday, March 30, 2012

RAID1 vs RAID5 for transaction log

I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advanceRaid 5 performs very poorly on writes as does hp/compaq's ADG (Advanced data
guarding).
If your database is mainly reads then raid 5 is ok for the datafiles.
Also consider that if a disk fails and the array is having to construct a
phantom disk on the fly then the performance of your system will probably
make it unusable.
It's worth pulling a disk on a system before it goes live and practice the
recovery.
Personally I use a raid 10 array of 4 disks for my log (and raid 10 arrays
for my data too). A mirror pair may do for your log.
Paul
"TJT" <TJT@.nospam.com> wrote in message
news:uDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I was hoping that someone could point me to some good documentation on
> selecting an optimum RAID configuration. While performing heavy updates
> during upgrades - I've noticed dramatic performance difference between
> RAID
> 1 and RAID 5 for the transaction log.
> It seems that it is less important for the data files themselves (RAID 5
> doesn't seem to hurt that much).
> Does this make sense where the RAID Configuration for the T-Log is more
> important than the Data?
> If someone could shed some light on this or point me to some good
> documantation - I would greatly appreciate it.
> Thanks in advance
>|||This is a multi-part message in MIME format.
--060704050306020201010502
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've always found this website to be a good RAID level overview (even
though it's a vendor website):
http://www.acnc.com/raid.html
It's not very detailed, just brief pros & cons and how the RAID level is
constructed, but it's good info nonetheless.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
TJT wrote:
>I was hoping that someone could point me to some good documentation on
>selecting an optimum RAID configuration. While performing heavy updates
>during upgrades - I've noticed dramatic performance difference between RAID
>1 and RAID 5 for the transaction log.
>It seems that it is less important for the data files themselves (RAID 5
>doesn't seem to hurt that much).
>Does this make sense where the RAID Configuration for the T-Log is more
>important than the Data?
>If someone could shed some light on this or point me to some good
>documantation - I would greatly appreciate it.
>Thanks in advance
>
>
--060704050306020201010502
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've always found this website to be a good RAID level overview
(even though it's a vendor website): 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a> ">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a> 
 
It's not very detailed, just brief pros & cons and how the RAID
level is constructed, but it's good info nonetheless. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
TJT wrote:
<blockquote cite="miduDWYpug5FHA.1248@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">I was hoping that someone could point me to some good documentation on
selecting an optimum RAID configuration. While performing heavy updates
during upgrades - I've noticed dramatic performance difference between RAID
1 and RAID 5 for the transaction log.
It seems that it is less important for the data files themselves (RAID 5
doesn't seem to hurt that much).
Does this make sense where the RAID Configuration for the T-Log is more
important than the Data?
If someone could shed some light on this or point me to some good
documantation - I would greatly appreciate it.
Thanks in advance
</pre>
</blockquote>
</body>
</html>
--060704050306020201010502--|||RAID5 is terrible for heavily updated data, and the Transaction Log is the
prototypical worst case. Go for RAID 1. For database files/filegroups it
really depends on the update load. In general the feeling has been that
RAID 10 (aka 1+0) is better for databases since you get maximum performance
and availability. But if you have lightly updated tables then RAID 5 is
going to be OK.
If I remember correctly Kalen Delaney's "Inside SQL Server 2000" book has a
good discussion about this.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e6mn%23ll5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I've always found this website to be a good RAID level overview (even
> though it's a vendor website):
> http://www.acnc.com/raid.html
> It's not very detailed, just brief pros & cons and how the RAID level is
> constructed, but it's good info nonetheless.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> TJT wrote:
>>I was hoping that someone could point me to some good documentation on
>>selecting an optimum RAID configuration. While performing heavy updates
>>during upgrades - I've noticed dramatic performance difference between
>>RAID
>>1 and RAID 5 for the transaction log.
>>It seems that it is less important for the data files themselves (RAID 5
>>doesn't seem to hurt that much).
>>Does this make sense where the RAID Configuration for the T-Log is more
>>important than the Data?
>>If someone could shed some light on this or point me to some good
>>documantation - I would greatly appreciate it.
>>Thanks in advance
>>
>>
>

Friday, March 23, 2012

R

Hi Experts,

After performing some tests on my setup I recorded very large performance degradation between simple setup and mirrored setup (High Availability – with witness). In some inserts tests (100K in a loop) it took up to 400% longer to complete for mirror setup compared to the simple one (40 seconds compared to 170 seconds).Is this performance hit normal? Is there something that can be done to make it perform better?

The PCs are connected using a 100MB switch and I'm using SQL Server Developer Edition.

Thanks,

Avi

Hi Avi,

The tests that you are running are bad scenario for high-availability mirroring. Each insert statement in the loop practically commits a single-statement transaction. This causes log to be sent to disk and mirror and waits for the mirror to harden that log. That way you are directly adding to the per-statement latency.

Here are a few things you can try:

- Don't run all inserts sequentially on a single connection. Rather, distribute them among multiple connections. That way you are increasing the chance that while one transaction is committing and thus waiting for the mirror to harden the log, others may run in parallel, thus amortising the wait time.

- If at all possible, make your transactions bigger (do several inserts in a single transaction). That way each log flush will carry several transactions.

- If at all possible, use high-performance mirroring mode (here you have the risk of having your log growing, though).

Hope this helps.

Thanks,

Kaloian.

|||

For better estimation of performance impact of database mirroring, your tests should be as close to the actual application as possible. For discussion on database mirroring performance, take a look at the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

PCs?

Would you please give more details on the hardware of the mirrored servers?

|||

Thank you all for the replys.

I use two dual Xeon PCs with 2GB memory. The disks are fast too. The performance without the mirroring is great - over 2000 inserts per second. However with mirroring it goes down sharply.

When I run the inserts in a single transaction it flies. However I cannot always do that. My workaround for now is to suspend the mirroring for a few seconds do my inserts and run the resume command. This brings me back to the performance I used to have, but I hope to find a more "clean solution". It is not so good to suspend and resume the mirroring so many times…

Any suggestions (hardware, configuration or application implementation)

Thanks,

Avi

|||

Avi,

What you are doing (suspend/insert/resume) is essentially similar to high-performance mirroring mode with possibly more overhead, because of the resynchronisation.

If you can tolerate possible data loss on failure, then SAFETY = OFF is the better choice. Does that work for you? If not can you give some more details on what you want to accomplish.

Thanks,

Kaloian.

Friday, March 30, 2012

RAID1 vs RAID5 for transaction log

RAID1 vs RAID5 for transaction log

RAID1 vs RAID5 for transaction log

Friday, March 23, 2012

R

rda submitsql

Blog Archive

About Me