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
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Map software access 4 36
Need help with software deployments 3 63
Pay to 支付宝 12 57
Server Recommendations 30 18
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
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…
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

821 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