Monday, May 18, 2009

Make sure write cache is enabled on your RAID controller

Using a write cache protected by battery (BBWC) is well known and one of the best practices in RDBMS world. But I have frequently seen situations that people do not set write cache properly. Sometimes they just forget to enable write cache. Sometimes write cache is disabled even though they say they set properly.

Make sure that BBWC is enabled. If not enabled, you will be able to easily get better performance by just enabling it. The following is a DBT-2 example.

Write cache is disabled:

# iostat -xm 10
avg-cpu: %user %nice %system %iowait %steal %idle
21.16 0.00 6.14 29.77 0.00 42.93

Device: rrqm/s wrqm/s r/s w/s rMB/s
sdb 2.60 389.01 283.12 47.35 4.86
wMB/s avgrq-sz avgqu-sz await svctm %util
2.19 43.67 4.89 14.76 3.02 99.83


Write cache is enabled:

# iostat -xm 10
avg-cpu: %user %nice %system %iowait %steal %idle
40.03 0.00 16.51 16.52 0.00 26.94

Device: rrqm/s wrqm/s r/s w/s rMB/s
sdb 6.39 368.53 543.06 490.41 6.71
wMB/s avgrq-sz avgqu-sz await svctm %util
3.90 21.02 3.29 3.20 0.90 92.66

Both run same applications(DBT-2), but the server activity was significantly different each other. I got 85% better result when write cache is enabled.


Checking write cache and battery status

BBWC is mostly equipped with H/W raid controller so operational command depends on products. Here is an example of "arcconf" command result.

# /usr/StorMan/arcconf GETCONFIG 1 AL
...
--------------------------------------------------------
Controller Battery Information
--------------------------------------------------------
Status : Optimal
Over temperature : No
Capacity remaining : 99 percent
Time remaining (at current draw): 3 days, 1 hours, 11 minutes

--------------------------------------------------------
Logical device information
--------------------------------------------------------
Logical device number 0
...
Read-cache mode : Disabled
Write-cache mode : Enabled (write-back)
Write-cache setting: Enabled (write-back) when
protected by battery
...

--------------------------------------------------------
Physical Device information
--------------------------------------------------------
Device #0
Device is a Hard drive
...
Size : 140009 MB
Write Cache : Disabled (write-through)
...


Write cache should be enabled only when battery backup is working. In other words:
- Write cache on logical device (H/W raid controller) is enabled when protected by battery
- Write cache on physical device is disabled
- The battery has enough capacity and long enough remaining time

I recommend DBAs to monitor write cache status regularly (adding this to your monitoring scripts), including battery status checking. Long time ago I was asked for urgent help to fix a problem that application performance suddenly went down. I looked into problems then found that write cache unexpectedly turned off because a battery was expired. If you successfully detected that battery capacity was decreased before write cache was disabled, you would be able to take an action before server performance suddenly goes down (i.e. allocating scheduled down time in order to replace the battery).


Quick health check with mysql commands

If you are not familiar with H/W raid controller specific command but want to check write cache status quickly, using mysqlslap or stored procedure is easy.

mysqlslap:
$ mysql -e "set global innodb_flush_log_at_trx_commit=1"
$ mysqlslap --concurrency=1 --iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-load-type=write \
--number-of-queries=100000


stored procedure:
create table t (c1 int) engine=innodb;
delimiter //
create procedure sp1(IN i INTEGER)
BEGIN
DECLARE done INTEGER DEFAULT 0;
WHILE i > 0 DO
insert into t values (1);
SET i = i - 1;
END WHILE;
END;
//
delimiter ;

set global innodb_flush_log_at_trx_commit=1;
call sp1(100000);


You will be able to insert thousands of records per second if write cache is enabled. If disabled, only hundreds of inserts per second is possible, so you can easily check.

44 comments:

Sildenafil Citrate said...

My best friend told this that the best practices in RDBMS world was to use a write cache protected by battery. I tried it myself and I was impressed with the results, I recommend it to everybody now!

philip said...

hi yoshinori,

when linux server has hardward issue, for example:harddisk is broken, how to dispose the data which save in the BBWC?

head lice home remedies said...

In all probability the most useful as well as up-to-date information I got here across on this topic. I'm sure fortunate that I noticed your article by chance. I’ll be subscribing to your own rss feed so that I can have the most recent posts. Take pleasure in every little thing here.

Sagging skin said...

Your post really grabbed my attention and interest for the reason that the content is not just informative but also simple yet meaningful.

Natural laxative foods Stomach gas Hard stool treatment Health benefits of almonds White spots on skin
Cure ear infection Under eye wrinkles Baby ear infection Prevent prostate cancer Prevent prostate cancer Lemon tea healh benefits Lemon tea healh benefits Prevent candida yeast infection Prevent candida yeast infection Health benefits of papaya Health benefits of papaya Benefits of peppermint oil Benefits of peppermint oil Stop hiccups Stop hiccups Water and weight loss Water and weight loss Wart Removal Wart Removal Health benefits of oatmeal

android tablet said...

Good post. Very impressive. Thanks for sharing.

hermes birkin said...

Thank you for another essential article. Where else could anyone get that kind of information in such a complete way of writing? I have a presentation incoming week, and I am on the lookout for such information.
Hermes replica
hermes birkin replica

Marissarachelle said...

Here is the world-leading web dedicated to providing best services for MMORPG gamers to buy wow gold around the World. Let us help you play the game on YOUR style without worry about where to get cheapest wow gold!Your account is guaranteed 100% safety.Your best choice to us is wise because we aim to exceed your wow gold expectations and guarantee a risk-free and worry-free deal to buy world of warcraft gold.

Runescape Gold said...

Which queue duration is significantly too much. In the second test out after 15 a long time you may have the normal IO delay time of 128 moments. Perhaps it will accelerate bulk including however visits will put up with, any kind of entertaining procedure (pc vm, world-wide-web host, db back up a web site web server, and many others...) depending on that will commute will be unusable. To hold an active technique sensation responsive it is advisable to keep disk latency underneath 20ms maximum, if at all possible beneath 10ms.

If you've got any disk assortment that could deal with the item, significant queues are fantastic. I've got arrays by using countless disks, in addition to 4 or perhaps 7 4gbps fiberchannel jacks in order to connect them; they're able to cope with lists associated with 2000 a interface whilst keeping responsiveness. And you have got to measurement the idea correctly or else you trigger other concerns.


http://buyteraonline.com/
http://rsgoldfsbest.com/

http://fashoionstyles.com/
http://warcraftever.com/

Anonymous said...

Add to that, we offer hermes hello kitty bags many illustrious ladies replica watches

to our valued customers cheaply involving ladies idolReplicas. Breitling BT-216 watches, ladies

omega watches, and ladies tag Patek Philippe replica Watches heuer watches. Further we

offer Swiss fake Breitling watches to our clients inexpensively i.e. Swiss Daytona fake Breitling watches, Swiss Day Date fake Breitling

watches, Swiss Date Just fake Breitling watches, etc.

Runescape Gold said...

Thank you for sharing. Glad to see you.It is really a good post.http://www.buypdjewelry.com/

how to get a girl to give you head said...

I want to thank you for this informative read. Loved the whole article! Thanks for sharing.
jacks blowjob persuasion
how to get a girl to suck your dick
get girlfriend to deepthroat
convince girlfriend facial
how to get more blow jobs
i need a blowjob
wife wont suck my cock
make swallow cum
jacks blowjob persuasion review

Emily said...

Les sites d'or d3 gold ne promouvraient pas évidemment leurs affaires en disant - 'le coffre-fort de presque 60 %', 'la plupart des personnes ne deviennent pas attrapées 'ou' vous serez sans doute bien en achetant Hou la de l'or d'us'. Vous êtes si stupides, le Blizzard peut facilement pister, signaler ou contrôler n'importe laquelle de ces transactions. Ils pourraient facilement accumuler une liste des diablo iii items caractères et des comptes utilisés par les vendeurs - au lieu de les fermer définitivement ils pourraient juste surveiller qu'ils ont négocié avec - le bingo vous avez attrapé un acheteur d'or. Vous pouvez bien que cela le Monde de Warcraft est si grand qu'ils ne pourraient pas la prise possible chacun qui en échangeant hou la de l'or par l'argent réel. Et vous pouvez croire que le Blizzard n'interdira pas votre compte, parce que vous irez diablo 3 cd key faire a perdu leur économique. Non, non, non!

buyGuildwars2gold said...

He time of life is short ; to spend that shortness basely, it would be too long rs2 gold, It's great to be great, but it's greater to be human Runescape Gold, One meets its destiny on the road he takes to avoid it rs gold.

buyGuildwars2gold said...

Life is not measured by the number of breaths we take scarlet blade gold, but by the moments that take our breath away scarlet blade gold. I have a simple philosophy: Fill what's empty scarlet blade gold.

FRANCISCO Dake said...

Some great thoughts, and I'll certainly take it onboard with my writing. I can't help but think you should take a look at a film called 'Freedom Deep' when talking about 'Book of Eli'. The Denzel film shares a staggering amount of similarities to this independent Australian film, particularly the main characters and themes. Hell, even a lot of the shots and scenes are familiar! Keep up the good work, this blog is worth its weight in gold!
buy-college-essay
online-writing-services
Accounts Software For Small Business

Cahaya Mandiri said...

Great Post. I have not been visiting the site recently. Took a visit again and there were some great comments on the site. Excellent post. Keep up the good work.
tips cara agar cepat hamil l CARA BELAJAR BAHASA INGGRIS l the best acne treatment l how to lose weight fast easy
margahayuland l BISNIS ONLINE l tips cepat hamil l how to get rid of acne home remedies l
home remedies for acne l how to cure acne fast l
baju batik modern l toko sepatu online l grosir jam tangan online l
jual jam tangan l toko jam tangan murah peluang usaha online l is acne no more for you l how to get rid of acne naturally
how to clear acne l cure acne naturally
best natural remedies for acne l acne no more l tempat belajar bisnis online
peluang usaha rumahan l cara mendapatkan uang dari internet
makanan sehat agar cepat hamil l penyebab tidak bisa hamil lcara agar cepat hamil


muhammad yusuf syaifullah said...

thanks for sharing,this is very good post
Rumput Kebar Penyubur Kandungan

romesh seo said...

What a fantastic post ! it was absolutely actual informative.I attending advanced in account added of your assignment post, And accomplish you abiding that i will bookmark your post how ever i can appear a back latter.
Plastic valves

mike Sales said...

I am really excited about the trailer which is going to get released in the coming month. I am sure that the visual effects works are going to get you a lot of fans. Thanks for sharing the updated, links and information. Keep posting.
Truflo flow meters

jenny maria said...

I really enjoyed the quality information you offer to your visitors for this blog. I will bookmark your blog and have my friends check up here often.
ipad covers

sadia sulaman said...

I have been waiting for someone to share this post. This has actually made me think and I hope to read more. Thanks a lot for sharing with us.SEO Manchester

sadia sulaman said...

I will be interested in more similar topics. i see you got really very useful topics , i will be always checking your blog thanks. Web Design Manchester

sadia sulaman said...

Thanks for the unmatchable diary.it was rale useful for me.navigator sharing specified ideas in the instant as symptomless.www.mattressnextday.co.uk

sadia sulaman said...

Great post today. An interesting read for any person who would agree with your views. Long may these posts continue. SEO Liverpool

sadia sulaman said...

This article was written by a real thinking writer. I agree many of the with the solid points made by the writer. I’ll be back.cool wallpapers

sadia sulaman said...

Hello, i am glad to read the whole content of this blog and am very excited and happy to say that the webmaster has done a very good job here to put all the information content and information at one place.Buy Facebook Fans

shahbaz said...

A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post.Apple passbook store cards

mike Sales said...

Great post I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article.
Flow switch

mike Sales said...

I am glad to land on your page. I have learned a lot about creativity and art. I am looking forward for more updates
Truflo paddle wheel flow meters

sadia sulaman said...

I visited your site & after visiting i found that it is very informational for everyone you have done really a great job thank you.voip phones

sadia sulaman said...

I just wanted to say that I found your site via Goolge and I am glad I did. Keep up the good work and I will make sure to bookmark you for when I have more free time away from the books. Thanks again!www.flevoservice.co.uk |

www.ascotuw.co.uk |

www.stagandhendays.co.uk |

www.carvinworld.co.uk |

www.ashun.co.uk |

www.signvinylonline.co.uk |

www.kickstartup.co.uk |

www.radioalternativa.co.uk |

www.mob-apps.co.uk |

www.kellysofnarberth.co.uk |

Sadia Sulaman said...

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging.BlogHuts

jack sha said...

At Approved sum, we have a tendency to perceive that hard-working folks such as you generally want extra funds before their cash advances

next day. therefore we have a tendency to create it fast, straightforward and hassle-free to induce a day

Sadia Sulaman said...

I believe that this information will be beneficial to travel marketers to plan their strategies. Benchmarking will also help a lot.Yealink T41P

sadia sulaman said...

Very Useful information, this is both good reading for, have quite a few good key points and I learn some new stuff from it too. Social Media Marketing Manchester

mike Sales said...

I am full of emotions after reading this post. I really like it and it is good, that author decided to write on this topic. I would like to read his another posts.
levelpro displays

paul sam said...

Coming together is a beginning; keeping together is progress; working together is success.
Timber Merchants Manchester

sadia sulaman said...

Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!White Label SEO

mike Sales said...

I would like to appreciate your hard work you did write this post, Thanks for sharing this valuable post.
Truflo

sadia sulaman said...

Thank you for this blog. That's all i can say..useful information shared... i am very happy to read this article.. thanks for giving us nice info. fantastic walk-through.theweddingpaperdivas |

bigeasyweddings |

indieweddings |

phillywedding |

tylerscottwedding |

tylerscottweddings |

liveweddingband |

weddingsamui |

wedding-bliss |

weddingfresno |

sadia sulaman said...

Thanks for ones marvelous posting! I genuinely enjoyed reading it,you are a great author. I will be sure to bookmark your blog and may come back very soon.Door Casings

mike Sales said...

Thank you for such a fantastic blog. Where else could anyone get that kind of info written in such a perfect way? I have a presentation that I am presently working on, and I have been on the lookout for such information.
Flowline ultrasonic sensors

mike Sales said...

You are so cool! I can’t think I have read anything like this before. It’s really good to find somebody with some original thoughts on this subject. Thanks for sharing with us.
Flow meter

mike Sales said...

I would like to appreciate your hard work you did write this post, Thanks for sharing this valuable post.
Truflo

Post a Comment