I know why it’s always the databaseJune 29th, 2007 — ddelmoli
Jeff sums it up best in wondering why, when an application is a complex combination of system components, is the database always blamed for poor performance?
I’ll tell you why.
People ask for data. And they don’t like the first set of data they get, so they ask again, and then they browse back and forth through the data.
And where is the data? In the database.
The database is responsible for serving data quickly. And since you’re the DBA, when the data doesn’t come back quickly enough, you’re asked to “look at the database”.
BTW, this is one of the reasons I’m a huge fan of constant monitoring — I like to see if the database is acting differently from when “everything was ok”. This requires some kind of historical view or “baseline”. Some way to look back in time to yesterday, or last week at this time, or last month during the monthly close, etc. If the profile during the “slow response” is the same as it was during the “fast response”, generally there’s nothing wrong with the database overall — there may be something going on with that particular request, but you know how to fix those.
And, unfortunately, the database is ridiculously well instrumented, and you’ve convinced everybody that you can quickly see everything going on in the database. Oracle’s event model has really no counterpart on the other parts of the application stack.
Check out Jeremy’s post again — look at how “easy” it was to see that AIX was hopelessly mis-configured and was using a ton of swap space.
I’ve never really liked the idea of swap space — Why would you ever want anything at all swapped out of memory on a database server? Do you monitor memory usage on your PC? What happens when you start using swap space? You go to the Kingston web site and purchase more memory, that’s what. I used to get annoyed at H/W O/S vendors when I’d ask why we needed so much swap. My favorite answers:
- Just in case
- We’ll crash / panic otherwise
- We “pre-swap” code text images to speed up swapping
- The system becomes more efficient during non-peak usage as it can handle more processes then as long as they’re idle
This is nuts.
Another fun excuse I’ve heard is when there is significant data caching at the middle tier and web tier — “it takes a long time for the database to give us data to fully populate the cache, so queries are slow”.
This is why I advocate SIMPLE architectures for data-intensive applications. Fewer non-database places where things can go wrong. Fewer times that the DBA gets asked about “why isn’t my data coming back fast enough” and has to spend time finding out which part of the application stack isn’t pulling its weight.