Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What are some rough performance limits (read/s, write/s) for a single database server (no master-slave architecture), assuming storage on disk? How many read/s, write/s, depending on the kind of disk? (SSD vs non-SSD) , assuming simple operations (select one row by primary key, update one row, correctly indexed). I assume this limit is dependent on disk seek/write.

EDIT: My question is more about getting rough metrics of the number of operations a database supports: to be able to know for example, if a new feature triggering 300 inserts/s can be supported without scaling out with additional servers.

share|improve this question

migrated from stackoverflow.com Dec 24 '10 at 19:09

This question came from our site for professional and enthusiast programmers.

    
Probably only benchmarks can tell you this not documentation. –  Jani Dec 24 '10 at 13:18
    
Impossible to anwser, it all depends on your needs. No benchmark did a test using your database nor using your application and hardware. –  Frank Heikens Dec 24 '10 at 13:26

4 Answers 4

On business I always use non-SSD storage. Raid 5 with 10 x spindles + 2 Hotspare.

It works fine.

As I know SSD have a poor Raidperformance. You must also take a look an max write operations. I use SSDs only for my developer PC.

If you need more performance you can use a seperate Raid

  • Raid 1 for the OS
  • and also a seperate Raid 5 for the transactionlog
  • Raid 10 for the datafiles
share|improve this answer
    
just wondering: why raid5 for the transaction log and raid10 for the data files? –  araqnid Dec 24 '10 at 11:42

You can read many benchmarks results here as if the website is a reliable source.

share|improve this answer

May I be so bold as to suggest you test it to get the answer for your own query. If you have windows run SSIO.exe on your development box to performance test the disk to understand how it performs doing nothing.

Then running performance monitor (assuming you are in MS-SQL TKProf maybe on Oracle - dont use mysql or others sorry there will be a similar tool.) Run your query and look at how many reads/ writes and how much CPU it took to do it. You can then compare the SSIO and Performance Monitor numbers and Scale the hardware requirements to handle the number of users / frequency that this update will need based on your current equipment.

Having said that 300 rows is a very small number of rows to be changing for any reasonable SQL Server even on fairly slow disks. Unless there are massive compounded indices or blobs etc. I have live in processes as the user watches a query that create 1.5M rows that run in fractions of a second on relatively modest hardware.

share|improve this answer

You can't judge without benchmarking your app and load.

It comes down to RAID levels, spindle speeds, size of transactions (not just "inserts"), triggers, foreign keys, hyperthreading, other apps on the server, RAM in the server, how disk are arranged (separate volumes for tempdb, one per DB t-log, MDFs etc), service pack level, RAID controller cache configuration, CPU Ls + L3 cache, number of cores, schema design, code...

Scaling up is easier then scaling out: you add overhead if you federate servers or partition tables. Cheaper to add RAM and more spindles.

A good article is Paul Nielson's 35k TPS. At least 100 times higher load than your 300 or so.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.