Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RAID5 problem

Posted on 2009-04-02
5
Medium Priority
?
588 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
[X]
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
5 Comments
 
LVL 3

Accepted Solution

by:
rmmustafa earned 1000 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 56

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Are you an Exchange administrator employed with an organization? And, have you encountered a corrupt Exchange database due to which you are not able to open its EDB file. This article will explain all the steps to repair corrupt Exchange database.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

730 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