RAID5 problem

Posted on 2009-04-02
Last Modified: 2013-11-15
We are a company using Cache for Windows (x86-64) 2007.1.3 (Build 607) and have been experiencing slow database performance, especially when reading, due to the large database size of 19Gb.  We're using a custom made application build with the .NET framework 2 via an ODBC, and noticed that when making queries through particularly large tables, even though having proper indices where applicable,  the performance is quite lacking.

For example to make a select count query on a table having 23,267,429 records it took 237 seconds, 152 sec, 172 sec on three different occasions (e.g. SELECT COUNT(ID) FROM MY_TABLE). Is that "normal" or can we make it in 10 seconds or less?
On the same table if we had to run another query using an index built on two fields, and if we tried to look for a non-existing record, it would take 1.3 sec.
If using the same table if we had to run another query using this same index, and if we tried to look for an existing record, it would take 1.1 sec.
On a relatively smaller table it would take 0.35 seconds

The first question is whether there is a way to make the database reading possibly faster . We have tried playing around with the memory by allocating more RAM but without any positive results.  Using the server Microsoft's performance montior, it is evident that during peak load there are very high percentage reads on the disks going on whilst the CPU is relaxed and the writing is minimum.  There are times when the server reading is fixed at 100% usage for over half an hour.

Secondly, as part of the solution, we are considering changing the RAID5 to RAID10 with four disks of 300G SAS running 15000rpm. Will that help to improve the performance? If yes, what cluster size of disks and what other technical setup do you recommend please to improve, hopefully in a drastic manner the performance?

Further details about our scenario
x3400, 2 CPUs Xeon Quad Core @ 1.60GHz
8 Gigs RAM
RAID 5 setup with five 146.8GB 3.5in 15K HS U320 SAS HDD
1 Gigabit Ethernet
Windows 2003 server, having hardware dedicate to this database only.

Question by:borgb002
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

rmmustafa earned 500 total points
ID: 24047954
It is obvious that your application and search quires depends on the HD activity , so moving to RAID 0 will surely solve some of the issues on this part , I tested RAID 5 and 0 disk performance myself and was astonished with the results. On RAID 5 array (5 SCSI Hard drives with 132GB ) doing normal partition backup for around 10GB of data , the transfer rate was 8 MB/s ,,,, I tested same array on same physical server using RAID 0 and the transfer rate was 30 MB/s ,,, and this small test can show the difference in throughout between the two RAIDs implementation .
Back to your case , as long as the quires are run directly on the disks and there is no indexing mechanism to populate the dbase  index into RAM , then adding more  RAM , or upgrading the CPU  wont help a lot.
LVL 28

Expert Comment

ID: 24049394
Depending on where the bottleneck is, moving to RAID 10 could help.  We have a SQL server implementation here that is quite large and as it grew larger accessing it became slower and slower.  We hired a SQl consultant and he made several recommendations.  Upgrading the firmware of our servers was one thing, but the one that really made the difference was moving from RAID 5 to RAID 10.  My supposition is that you would get a speedup from doing the same, but only if that's the bottleneck and the only way to tell that is get someone looking at the server who knows what to look at.
LVL 55

Expert Comment

ID: 24050336
Moving from RAID 5 to RAID 10 will only help if the controller does load balanced reads across both disks in a mirror. You don't say what it is. Generally the more disks you have the faster you can read (and write) so you can add more and short-stroke them by partitioning and "wasting" most of the space.

With only 19GB database you might be better off putting it on SSD. A fusion io drive might cost $3000 but it'll certainly give you much faster access time.

SQL people will probably tell you to split the table into several smaller ones.
LVL 28

Expert Comment

ID: 24275211
I don't know that anyone of us completely answered the question since the asker never responded to any possible solution, but I believe that we all contributed correct information.

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question