Sep 5 2008

DataBase Performance - Critical Hardware Configuration Tips

Posted by Mike Brunt at 3:25 PM
53 comments
- Categories: ColdFusion | JRun-J2EE

In my opinion there are four critical and separate areas that govern the performance characteristics of a Database server, also known as RDBMs. In this article I will focus on hardware considerations and hard-drive selection, in particular. However it is good to mention all four areas and I see these as follows:

  1. Code: This covers the efficiency of in-line SQL. For instance, avoid Select * at all times, I see it as never justifiable and the difference in performance between explicitly naming every column in a table and using Select * is dramatic; Select * can levy a 10-12 times performance penalty causing full table scans every time the query runs. In addition carefully examine SQL containing joins and sub queries. Also, take great care when looping over code that will cause queries, make sure you know the maximum number of loops and the performance impact of that. Always use CFQUERYPARAM and where possible use Stored Procedures. This last two points have both performance and security benefits. There are many more coding related items and I am only listing the major ones here. 
  2. Data modeling - Database Design: One of the biggest problems I come across is applications performing badly because the initial data model did not consider application usage growth and/or reporting needs. This latter item was covered in a blog posting I did for Alagad which can be found here. The discovery phase of application design is critical particularly in relation to the data modeling. Try to make sure there is plenty of time to get this right, think of the database as a heart, if it falters or fails your application will suffer the same fate. 
  3. Ongoing Maintenance: As applications change so should the components needed to ensure ongoing efficiency. In a database (RDBMs) effective table indexing can bring about immense gains in performance. Start right, by establishing efficient indexes and keep right by constantly reviewing them, this not a one time job. Also look at things such as views and temporary tables, in case that is a more efficient way to process data. 
  4. Database Hardware – Hard Drives: One area which is often overlooked when selecting hardware to house our database(s) is the hard-drive configuration. When creating specification requirements we tend to think of CPU clock speed and RAM as the main points of consideration for a server. In my experience, however; how the hard-drives are configured is just as critical if not more so.

Before getting into the details, here is why good Database Administrators will home in on millisecond improvements. Let’s consider an operation that takes 10 extra milliseconds to complete and which needs to perform 100 times an hour, for a year. That seemingly insignificant 10 milliseconds turns into 2.4 hours. Let’s also assume that operation runs 1,000 times an hour and I can tell you from my experience that queries running 1,000 times per hour is very common, I have seen many clients where this could be tens of thousand times per hour. Even at a lowly 1,000 times per hour that 10 milliseconds becomes 24 hours of wasted time. Hopefully this example demonstrates why good DBA’s think in milliseconds and why selecting the correct hard drive configuration is very important.

In understanding the subject of optimal hard drive selection we should consider the different parts which make up an enterprise level RDBMs. These parts or operations fall into three areas of functionality; reading, writing and reading and writing. So the transaction logs (critical if ever up to the minute restoration is necessary) and the tempdb mainly run write operations. So for these operations where, writing to the hard drives in ongoing and frenetic we need the least number of physical hard drives that will give us adequate redundancy. Getting basic, if we only have one hard-drive and it fails we have a big problem. So we can turn to something called RAID (Redundant Array of Inexpensive Disks). There is a good article here which describes the different kinds of RAID arrays. In the case of heavy write operations such as those the transaction logs and tempdb need , we should ideally select RAID Level 10. In RAID Level 10 we have a minimum of two drives which are striped and mirrored and contain the same data. As we only need to write to two drives at once, operations that are heavily using writes work efficiently on RAID Level 10 and If one drive fails we still have service.

For the data itself we should use RAID Level 5 or 6. In a typical RDBMs the amount of data will continue to grow and the advent of Sarbanes Oxley means that deleting data is no longer a safe option. In addition, the data itself will be read from and written to frequently. So we need something that gives us plenty of storage capacity. RAID 5 or 6 arrays require a minimum of 3 hard-drives and one drive is always dedicated to maintaining parity for the array.  So if we have 5 x 200GB hard drives which total 1,000GB we can only actually use 800GM of that space. Again, data is mirrored across all drives so if one fails we still have service. This means that with each write operation we are actually writing to 5 drives in an array with 5 drives, obviously the more drives the more concurrent writing.  This multiple disk writing is why RAID Level 10 is a better option for heavy writing processes

There is one last part of the database (RDBMs) we have not yet covered, the engine itself, the binaries. I would recommend that these be on a separate RAID
Level 1 array to give us the basic redundancy that we need.

Finally, we should consider the item which allows data to flow to and from the hard drives within the server-hardware itself. These items are typically called “controllers”. At minimum, each drive array should have its own dedicated controller. So in the example above we actually have three RAID arrays.

  • RAID 10 for the transaction logs and tempdb
  • RAID 5 or 6 for the actual data itself
  • RAID 1 for the database engine

In this case we need a minimum of three hard-drive controllers. Ideally though a dedicated controller per hard drive is even better and that is what I would recommend. It is important to consider that a slow or failing database will cost far more than any number of controllers either in direct loss of revenue (eCommerce for instance) or a loss of clients and your good name which can be even costlier.

In my next article on this subject I will delve into some ways of quantifying hard drive requirements for an RDBMs.

 

Comments

Mike Brunt

Mike Brunt wrote on 09/07/08 6:45 AM

@Michael thanks for your comment and the points you make. In my experience even if you explicitly name each column in a query and run that under load doing the same thing by using Select * is inordinately slower.

Your point about more data than needed being sent is of course correct and efficient indexing is another very important factor in all of this.
cosplay

cosplay wrote on 09/16/10 12:25 AM

Prom Dresses: Find Online fashionable prom dresses, 2011 Prom Dresses, prom gown, cheap prom dresses bridesmaid dresses, bridesmaid gowns, cheap bridesmaid
smart card

smart card wrote on 10/20/10 9:23 PM

A very informative article about understanding DataBase performance where some critical hardware configuration tips are given which will be taken advantage of by many! The four areas that govern the characteristics of a DataBase server are also explained in detail so that we understand the article better!! I am sure this give really help the DBAs to understand how choosing the selecting the correct hard drive configuration is so important!!
optimizare web seo

optimizare web seo wrote on 05/18/11 7:49 AM

The performance of the database can be improved by a caching system. In this way the website will respond faster at the queries.
Custom flag

Custom flag wrote on 07/12/11 3:07 AM

ohh man this is awesome post.i think they should usebanner to get more flow in look.
you know this is great working by the writer.The writer has done a great job in speniding his time in
research about this article. can i subscribe all his posts ?
I am really happy to read this. i was searching this from last two months and atlast i got it. hurrah..!!
Ios On Android Phone

Ios On Android Phone wrote on 01/18/12 9:00 PM

I am sure this give really help the DBAs to understand how choosing the selecting the correct hard drive configuration is so important!!
human services masters degrees

human services masters degrees wrote on 03/12/12 3:22 AM

My house has a room which is a state of the art server headquarters. I tend to update critical hardware from time to time but I have some problems with finding the best prices on the market so I'm constantly searching for tips so I can improve my business (cost wise).
iehomeloans

iehomeloans wrote on 04/10/13 5:39 AM

Untill i read this post, I was not confident to configure the critical components. However, now I use these tips while configuring the critical components and I find it very easy. Thank you very much for posting this useful information and keep posting such articles.
hublot

hublot wrote on 06/27/13 9:07 PM

Things are very open and intensely clear explanation of issues.
essayontime

essayontime wrote on 08/10/14 4:36 AM

After thoroughly reading complete article of that blog, i will recommend to all world because i found top class results related to different topics. We have to work hard to establish our online educational process. Hope some great response from all over the world.
writingessayz

writingessayz wrote on 09/25/14 4:39 AM

Wonderful blog post. I really appreciate the quality of your work here. Thanks so much for sharing such valuable information with the rest of the world.
dissertation writing services

dissertation writing services wrote on 09/29/14 8:31 AM

This is an excellent post i seen.I have to thanks to you to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post..
Sonya

Sonya wrote on 09/29/14 1:10 PM

Thanks a lot. Order immaculate papers custom written especially for you! http://marvelous-essay.net
sparxx rx

sparxx rx wrote on 10/16/14 8:47 AM

Our enticing garments are made from top-quality fabric that is guaranteed to last. Add spark to the bedroom with our bra and skirt sets available in a variety of colors.
ultimate pets

ultimate pets wrote on 11/02/14 12:44 AM

Believe it or not the overall physical appearance of our pets can also shape the way that they feel. A clean and fresh pet is definitely a happy pet.
good dog magazine

good dog magazine wrote on 11/02/14 12:44 AM

Choosing the dog house design can be challenging for some dog owners. A dog owner must carefully weigh the options available for the dog to feel comfortable and at home.
para watch dogs

para watch dogs wrote on 11/02/14 12:44 AM

Most people have a beloved pet in their home, which in most cases is a dog. While we are seeking companionship without canine, we also want them to protect our households from any potential harm.
superior home services 1

superior home services 1 wrote on 11/19/14 6:49 AM

Outstanding House for you to Home are generally excellent constructors in Chicago for... conclude is very good and provokes the house owners. Superior Household Builders will certainly design to perform the operate.
eye on home

eye on home wrote on 11/19/14 6:50 AM

Underneath eye tinting or darker circles are generally caused on account of vitiation inside vata plus the pitta doshas. There are generally some effective do-it-yourself solutions to remove under eyesight discoloration.
homes moving

homes moving wrote on 11/19/14 6:50 AM

Getting a new household is precisely what most individuals' think of. However, most ones have no idea the thoughts and multi-step technique of purchasing a whole new home.
public business law

public business law wrote on 11/19/14 6:50 AM

Mothers and fathers are a necessary partner inside education with their children. While constitutional law won't necessarily format parental protection under the law regarding education and learning, Texas statutory legislations does.
click

click wrote on 12/26/14 10:18 PM

Just like commenting for the sake of it It makes whoever posted the article be aware that someone cares and we do
Essaypaper

Essaypaper wrote on 01/17/15 5:12 PM

These hardware tips are worth following and I am one of hundreds of people and visitors who usually gain these coding and tips here. Thanks a bunch.
www.scorespro.com

www.scorespro.com wrote on 01/26/15 11:38 PM

Analyze cricket is now dull and also prosaic. Some concrete floor proposals to be able to revamp the particular test cricket are already suggested simply by former Native indian cricket person Ravi Sasthri.
brazilian straight hair

brazilian straight hair wrote on 01/27/15 4:31 AM

nice post,it's very useful for me, thanks for sharing
www.smartgrideducation.org

www.smartgrideducation.org wrote on 02/13/15 4:37 AM

Any time people point out that someone makes sense, what carry out they suggest? When we reference an personal as "well-educated, " exactly what are we discussing? When an individual call an individual "highly-intelligent, inches what details does in which communicate concerning them?
charlesdevon

charlesdevon wrote on 04/04/15 2:47 AM

I am running a little school http://www.chiefpapers.com/write-a-good-research-paper of writing and i got problem with my laptop, my computer started restarting at odd times and then it stopped loading windows, but would load BIOS.... now it won't even load BIOS anyone know why it happen
www.abc.com

www.abc.com wrote on 04/04/15 2:51 AM

nice post
writer

writer wrote on 04/28/15 4:32 PM

That's nice analysis and I really like the conclusion.
hijra

hijra wrote on 04/28/15 4:34 PM

I think these database issues occure more in asp application rather than php
clothing manufacturer

clothing manufacturer wrote on 07/04/15 4:12 AM

A very informative article about understanding DataBase performance where some critical hardware configuration tips are given which will be taken advantage of by many! The four areas that govern the characteristics of a DataBase server are also explained in detail so that we understand the article better!! I am sure this give really help the DBAs to understand how choosing the selecting the correct hard drive configuration is so important!!
CAR FOR SELL

CAR FOR SELL wrote on 07/15/15 1:29 AM

Selling a vehicle is usually a daunting process. We have assembled some important tips that you need to take under consideration if it is time to offer your automobile.
Medical Spa

Medical Spa wrote on 07/15/15 1:30 AM

There's tiny doubt you've been aware of medical and also day schools before, but you don't know the particular difference among them? This write-up will show you the key difference among medical schools and evening spas.
shopping

shopping wrote on 07/15/15 1:31 AM

Mystery shopping is a wonderful way to produce extra funds. In reality, some people produce a full moment living carrying it out. There are usually many puzzle shopping companies that may pay one to shop, eat with restaurants and indulge in focus teams.
http://www.fcherstal.be

http://www.fcherstal.be wrote on 08/29/15 4:18 AM

Information Technology has never been more needed and jobs in IT are not suffering from the downturns elsewhere. There may be short-term effects such as if we are working in the Automobile Industry in IT yet even there I feel salvation lies in intelligently applying IT to
terry

terry wrote on 09/16/15 4:45 AM

Thaks for these tips ;) By the way, do not hesitate to order papers at <a href="http://custom-essays-online.com">Custom-Essays-Online.com</a>;.
Security Guard Equipment

Security Guard Equipment wrote on 10/03/15 5:13 AM

Once you’ve selected a security guard service based on your needs, you may wonder what resources each guard will have while on the job.</p>
http://www.nettoyage-toulouse-31.fr

http://www.nettoyage-toulouse-31.fr wrote on 10/09/15 11:52 PM

We used it too, and we were excited about the restults we obtained.
urban decay makeup

urban decay makeup wrote on 12/20/15 1:13 AM

The best things for women are found here.
Newton Moses

Newton Moses wrote on 03/18/16 9:22 AM

Thanks a lot for giving us a detailed explanation of four critical and separate areas that govern the performance characteristics of a Database server. I would highly recommend this post to anyone who is interested in information technology (IT).
Feel free to check out the link below whenerver you are in need of creative essay writing services:
https://www.literaturereviewhelp.com/9-custom-writing/964-creative-writing-topics
John

John wrote on 03/26/16 4:14 AM

Both are the best but we have to chose one , when we talk about bottes air max 1 pas chere http://www.chaussureeshop.com/nike-air-max-1-bottes-c-1_224.html the power of decision making we should be analysis first then we do any thing farther.
csr racing hile apk

csr racing hile apk wrote on 05/27/16 3:52 PM

I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information.
www.link2sdapk.net

www.link2sdapk.net wrote on 06/28/16 4:34 AM

hi was just seeing if you minded a comment. i like your website and the thme you picked is super. I will be back.
http://www.clashofclansapkmod.net/
fuentes

fuentes wrote on 08/13/16 11:18 AM

this is really great,unique and very informative post, i like sport shoes http://www.runningfanshop.com/ . thanks!
mransom

mransom wrote on 08/22/16 5:50 AM

I've looked information on this very subject and I'm happy that I've found it http://www.negoziorun.com/adidas-c-95.html
abc

abc wrote on 08/26/16 2:39 AM

nice one
Damaris Montesinos

Damaris Montesinos wrote on 08/31/16 3:35 PM

Hi there I just thought I would tell you that I enjoyed reading your website and to wish you all the best with it in the future - Damaris Montesinos
hidup sehat

hidup sehat wrote on 12/12/16 11:55 AM

thank for your tips, very inpirations
https://playgmodfreeblog.wordpress.com/

https://playgmodfreeblog.wordpress.com/ wrote on 01/04/17 8:05 AM

For shirtng. t wint to reid recent iffitrs out of thts blog. Keep posttng. You’ve ltfted in essenttil tssue. Thinks
https://playgmodfreeblog.wordpress.com/

https://playgmodfreeblog.wordpress.com/ wrote on 01/04/17 8:05 AM

For shirtng. t wint to reid recent iffitrs out of thts blog. Keep posttng. You’ve ltfted in essenttil tssue. Thinks
./././
see more

see more wrote on 01/06/17 2:53 AM

Whoi thts greit but,t love greit photos ind your irttcle mtght be iny tndtvtduils nothtng ltke beciuse mtnd
t ibsolutely enjoytng every btt of tt. tt's good to shire ind in excellent stte. t do wint to thinks. Greit work! You folks ilso hive some good ttems ind perform i fintisttc webstte. Keep the greit work up.

Write your comment



(it will not be displayed)



Leave this field empty: