Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: phranque
The other 2 drives contain images and a site done in .net Currently IIS is running around 30-40% CPU when i exceed 200 page requests/second, and sql server takes up about 40-50% CPU.
I want to get a Quad Opteron 2.2ghz with 4 15,000 RPM drives in a raid 5 configeration with 8GB 400 DDR ram.
My question is can i do better? I'm currently not using raid, will raid 5 kill my performance because i have a lot of inserts/updates ~20/second?
Right now my main bottleneck seems to be that my harddrives just can't keep up,and in the next 6 months i expect to be doing 75 to 100 million selects/inserts a day.
RAID 5 will help you in that the INSERT / SELECT requests will now be spread over multiple hard drives. You pay a slight penalty in RAID 5 for the parity information that protects you in case of a disk failure. You can avoid this penalty by going to RAID 10 (RAID 1 + RAID 0). This is accomplished by mirroring two striped sets. Another good feature to get for RAID is hot swappable hard drives.
SQL Server will benefit from multiple processors and all the memory that you can throw at it. The ideal situation (if possible) is to have enough memory so that the entire database is memory resident.
all writes go to this 'big' box and all/most reads to 3 'slaves' which have ordinary scsi drives, some selects, especially if they are search related can lock up a DB so these should always go to a dedicated (cheap) slave server.
The best setup would probably be raid-10 which lets you survive 2 crashed disks at once - sometimes a second disk crashes exactly after a disk just crashed and while the raid setup is making a complete rebuild (->heavy disk-IO).
The other thing you can do is to setup multiple DBs for different tasks and to store/cache certain things directly in (shared) memory.
With 25-30 million transactions per day you must be averaging something like 350 database transactions per second, which is pretty considerable.
It depends on what you are doing with the database, but unless your queries are pulling large datasets, you are probably more disk limited than you are processor limited.
You'd probably see more of a performance boost with a better disk infrastructure than you would with 4 processors, and you'd also avoid having to get a SQL Enterprise license, which would cost more than the hardware.
High end SQL setups should typically have at least 3 distinct physical volumes -- OS, transaction log, and data. Depending on the data, it may be further segmented to more physical volumes. In our environment, we typically have the OS on a mirror, the log files on a RAID1 or 10, and the data on a RAID5. The more drives the merrier as the more drives the better your performance. If budget allows, go for RAID10 on both transaction log and data arrays.
I wouldn't suggest putting the transaction logs on IDE Drives as they will become a bottleneck -- the log needs to be written before the data is written. Initially you may get a performance boost just because you'll be eliminating some contention all around, but it isn't a good long term strategy.
At the levels your talking about you need many more smaller drives, not fewer larger drives.
From a memory perspective it also comes back to query patterns -- large querysets will benefit from extra ram as SQL will use it for more data caching. The ram won't help your write performance.
You should also be moving to a platform that you can readily expand -- any solid raid platform should let you expand the RAID10 and RAID5 arrays 'live', which lets you add drives as you need them for performance or capacity reasons.
Don't forget about future expansion as well -- clustering is a logical requirement as your site grows and the downtime becomes expensive (or more expensive than it is already, as the case may be) -- make sure your infrastructure will support it.
you are probably more disk limited than you are processor limited.I can second that... modern CPUs are incredible efficient, main DB serving some 400-500 requests per second here shows CPUs 70-80% idle. It's all about disks, disks, disks and RAM. And of course DB should be on its own server without any other processes running.
Think of selecting all webmaster world members within a 100 mile radius of town abc and then sorting them by the time they last visited the site. Add to that allowing filtering by any criteria plus paging of results. This is done around 40 times a second and few hundred not so intensive requets a second. Many of the tables contain 2 to 4 million records so the selects/indexs are fairly large. I figure as the site grows larger i can break off parts of the database onto seperate machines, but for the core database i need something super fast.
As for Raid 5 i was reading that if you put in enough drives the write limitation is over come.