SQL Server Performance

How many databases can I sensibly support?

Discussion in 'Performance Tuning for Hardware Configurations' started by tholder, May 7, 2003.

  1. tholder New Member

    Hi,

    I'm setting up my own dedicated server to offer my clients hosting and want to get the pricing right. Make sure I make enough to cover the hardware and license costs and ongoing running cost (and possibly a little profit - there's a thought) whilst providing a good deal for my clients. My problem is, apart from having built some large databases I'm not really sure of the performance of my hardware.

    The server I'm putting it on to is a:

    Dual Athlon MP 2200+ (ok, I know call me crazy but I allways back the underdog)
    2gig RAM
    128Meg RAID controller with 6x IBM Ultrastar drives
    Windows 2003 server

    The question is, how many databases can this server sensibly support, given that it will also be serving up web pages, dns and mail?

    I know this is a bit of an unknown quantity given that some bad code could lock it up, but I intend to review as much as the code as possible that goes on to it.

    Also, what's the best way to configure the disks?

    I've been told by someone the best option is:

    2 disks mirrored for system (OS/etc) with a separate partition for swap space.
    4 disks RAID 5 for data

    What's the benefit of mirroring the 2 disks? Why not just have one big RAID 5 array?

    Thanks for your help.
  2. tholder New Member

    I know this is a bit like 'how long's a piece of string' so I'll just add, all the databases will be for web sites, nothing huge, just shopping systems, forums etc and most will be smaller than 100meg in size.
  3. Bic New Member

    quote:Originally posted by tholder
    Also, what's the best way to configure the disks?

    I've been told by someone the best option is:

    2 disks mirrored for system (OS/etc) with a separate partition for swap space.
    4 disks RAID 5 for data


    I don't know how flexible your budget/time is, but maybe you can stress test (based on estimated usage) this sort of setup and see how it fairs. My DEV box has 110 databases. On the other hand, one of my extremely high utilization production boxes has 1 database on it. Other prod boxes have 10 databases on them.

    Compaq has a SQL Sizer application, though this is primarily for data warehouses. Will this be a custom built machine? You might want to check out www.microsoft.com/hcl for hardware compatibility. What sort of backup or standby mechanism do you plan on?

    It sounds like your going to have a fair amount of writes, so logs and tempdb should be on own raid 5 or mirror set.
  4. Luis Martin Moderator

    I don't agree with RAID 5 with data files on it.
    I suggest RAID 5 for OS and SQL software, but RAID 1 or RAID 10 for DataFiles and Tempdb.

    Luis Martin
  5. tholder New Member

    So databases and tempdb on to the system partition?

    Yes, it is a custom built server, but one that I've had previously running SQL Server with no problems.

    backup will be nightly to another server.

    Is there any way to track bandwidth through SQL Server from an external source? Obviously the bandwidth is costing me, so I'd like to know which users are running large queries on their development machines etc.
  6. satya Moderator

    You can using PROFILER and PERFMON by collecting counters and trace.
    Also third party tools like ENTEGRA will help to some extent for Audit purposes.

    _________
    Satya SKJ
  7. bradmcgehee New Member

    Because you won't know how each database will be used, or how busy it will be, I would recommend a RAID 10 system for everything, or RAID 10 for everything except the OS and swap file, which could be RAID 1. This is more expensive, but will provide better throughput over the long haul.

    As I assume that you are already aware, you will need to license your server using the "Per CPU" option (because the server is accessable through the Internet), which is quite expensive. Because of this, you may want two servers. One as a dedicated SQL Server (1 CPU, the fasted you can get) and another for everything else (with 1 or more CPUs). The odds are that the databases won't be CPU intensive, and 1 CPU and lots of RAM will be more than adequate.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  8. Bic New Member


    What is the relative cost of a RAID 10 versus RAID 5? To have RAID 10, you'd have to get another box just to hold the additional drives right? This configuration starts to take more server real estate, no?


  9. tholder New Member

    Presumably there is nothing to stop me using RAID 10 with the 6 disks in the system made up of 3 RAID 1 sets. Is that correct? Presumably 6x18gb disks would leave me with 54 gig? How does RAID 10 compare to RAID 5 in terms of fault tollerence?

    What are the advantages of this over RAID 5? Being able to read/write to more than one disk at once?
  10. Luis Martin Moderator

    Tha's ok, Tholder with RAID 10 6x18 you have 54 Gb. free.
    In terms of tollerence with RAID 10 your are in problems if broke 1 disk and his mirror at same time.

    The advantages of RAID 5 is u$s. Is faster to read but to slow to write.

    Luis Martin
  11. pendragon New Member

    Hi tHolder<br /><br />Don't know if this thread is still going, but thought I'd drop a note.<br /><br />For general Databases (balanced read and write) the ideal would be to use RAID 1 for OS and Binnaries; Page file on separate disk - not on separate partition; RAID 0+1 for Data; RAID 1 for Logs - may have to split logs onto their own separate RAID 1 devices; tempdb on RAID 0 (unless you require fault-tolerance, but remember, SQL Server rebuilds tempdb every time it restarts).<br /><br />RAID 0+1 - Striped Mirroring (which virtually all vendors implement in their RAID Controllers when allowing a mix of RAID 0 and RAID 1) has the most fault tolerance. Striped mirroring writes logical blocks of data across two or more mirrored sets of disks, creating a single volume with no redundant information between the mirrored sets of disks. Each mirrored set of disks consists of two or more disks. Striped mirroring provides the same high level of availability as mirroring. The available capacity of a striped mirror is equal to that of each disk in the mirror set (in a two-disk mirror set, 50 percent of the disk capacity is used for data protection). Unlike mirroring, however, striped mirroring provides symmetrical performance. Read performance is very fast because data is available from each disk in the mirror. In striped mirror implementation, RAID array controllers support simultaneous reads from each member of the mirror, which maximizes read performance. Write performance is also fast because writes occur simultaneously across each mirrored set. With striped mirroring, if a drive fails, read and write performance is slightly reduced, but only for data stored on the mirror set containing the failed disks. Read and write performance to and from the other mirrored sets is not affected. Striped mirroring can survive the failure of multiple drives, provided at least one drive in each mirrored set survives.Striped mirroring is the best solution for a database server requiring high performance and high availability. Capacity can be a problem, however, because of the number of disks required and their cost. <br /><br />With RAID 5, if you loose more than one disk (which are next to each other) then thats your data gone. Also, with RAID 5, for every logical write to the database, 2 physical reads and 2 physical writes are carried out which means more I/Os. I'm sure you know RAID 5 is good for reads though. I have seen a 40GB DSS Database with everything on DELL PowerEdge twin Xeon processor (1.4MHz) and 4 x 36GB HDDs RAID 5 with about 10 developers using it quite heavily (including lots of writes) running not too bad.<br /><br />The above all said, for your situation, if space is a must then RAID 5 all of it until you can get some more drives attached, or if space is not too much of a problem, you could do 3 x RAID 1 with OS, Binnaries and Pagefile on one (yes I know thats contrary to what I said earlier [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] but trying to make the best of what you've got), Data files and tempdb on another, and finally Logs on the last. Where the Logs sit you may find that you have plenty of space so, if you can find a quiet period you could place database backup here.<br /><br />Good luck and hope it works ok.<br /><br />

Share This Page