Solved

RAID5 problem

Posted on 2009-04-02
5
576 Views
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.

0
Comment
Question by:borgb002
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
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.
0
 
LVL 28

Expert Comment

by:jhyiesla
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.
0
 
LVL 55

Expert Comment

by:andyalder
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.
0
 
LVL 28

Expert Comment

by:jhyiesla
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Let’s list some of the technologies that enable smooth teleworking. 
This video shows how use content aware, what it’s used for, and when to use it over other tools.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now