Normally, the database isn't the bottleneck. Tue, Oct 18. 2011
A lot of people like to point out that code efficiency for webapps isn't relevant nowadays, code can be slow, even glacially slow given the fact that the webapp mostly waits for the database anyway.
This, quite frankly, is wrong almost all of the time.
Let's take a look at writes (since the reading problem can be solved by some kind of caching strategy - once you have almost everything cached you are measuring Dictionary/Map performance).
I invite you to turn on statement logging on your database and capture the SQL DML that's being emitted by your favourite webapp framework for a specific write use case (including the transaction boundaries). Turn off your logging again for max. performance. Normally your web framework will be capable of handling 5-20 non-cacheable user requests/sec that result in direct write requests to the database (measure with httperf or whatever). Now shut down your webapp, and run that queries directly against your database, using the correct transaction boundary. Then run queries in parallel. Get some solid numbers.
You may discover that the transaction and its queries complete in a few msecs, and you get a lot more requests/s to the DB right away. From a plain DB perspective on a developer machine. Now who's bottlenecking? Certainly not your DB.
If your queries really run slow, the first step is to ensure correct DB design and DB configuration. Your db design must be sane (2NF/3NF or BCNF) and indices must have been set correctly. Technical issues like full table locks with MyISAM matter. String operators and functions in general, LIKE queries etc. Yes, of course you will have to be able to optimize or circumvent really "hard" queries.. At the end of the day, with sane database design, you can get really decent performance.. on a totally normal, average box. And the database will prevail. A whole lot more is going to die before that CUSTOMERS table won't exist any more.
Of course, Disc IO is the upper limit for DB IO (if the db really fsyncs on every transaction), but even with std 7200RPM Discs on developer machines you can get fast performance with correct db design.
But don't tell me your webapp is only capable of handling just a few req/s per second for your average webapp because of your database.
It's highly probable that everything else is preventing you from achieving higher throughput...
...Your ORM that thinks its an object runtime
...Your favourite programming language
...Your beloved ultimate framework,
...Your own application code
...Missing caching strategy
...And a whole lot more
PS
I am not talking about ultra-high-traffic sites here, like facebook. I am talking about normal webapps with moderate traffic.. It makes me wonder why everybody tries to solve the problems facebook has to solve (millions of users and gazillions of internet traffic) ...
PPS
If in doubt, measure.
This, quite frankly, is wrong almost all of the time.
Let's take a look at writes (since the reading problem can be solved by some kind of caching strategy - once you have almost everything cached you are measuring Dictionary/Map performance).
I invite you to turn on statement logging on your database and capture the SQL DML that's being emitted by your favourite webapp framework for a specific write use case (including the transaction boundaries). Turn off your logging again for max. performance. Normally your web framework will be capable of handling 5-20 non-cacheable user requests/sec that result in direct write requests to the database (measure with httperf or whatever). Now shut down your webapp, and run that queries directly against your database, using the correct transaction boundary. Then run queries in parallel. Get some solid numbers.
You may discover that the transaction and its queries complete in a few msecs, and you get a lot more requests/s to the DB right away. From a plain DB perspective on a developer machine. Now who's bottlenecking? Certainly not your DB.
If your queries really run slow, the first step is to ensure correct DB design and DB configuration. Your db design must be sane (2NF/3NF or BCNF) and indices must have been set correctly. Technical issues like full table locks with MyISAM matter. String operators and functions in general, LIKE queries etc. Yes, of course you will have to be able to optimize or circumvent really "hard" queries.. At the end of the day, with sane database design, you can get really decent performance.. on a totally normal, average box. And the database will prevail. A whole lot more is going to die before that CUSTOMERS table won't exist any more.
Of course, Disc IO is the upper limit for DB IO (if the db really fsyncs on every transaction), but even with std 7200RPM Discs on developer machines you can get fast performance with correct db design.
But don't tell me your webapp is only capable of handling just a few req/s per second for your average webapp because of your database.
It's highly probable that everything else is preventing you from achieving higher throughput...
...Your ORM that thinks its an object runtime
...Your favourite programming language
...Your beloved ultimate framework,
...Your own application code
...Missing caching strategy
...And a whole lot more
PS
I am not talking about ultra-high-traffic sites here, like facebook. I am talking about normal webapps with moderate traffic.. It makes me wonder why everybody tries to solve the problems facebook has to solve (millions of users and gazillions of internet traffic) ...
PPS
If in doubt, measure.
Trackbacks
Trackback specific URI for this entry
No Trackbacks
