Comment On The Certified DBA

How great it is to work in a company where your management is either unable or unwilling to comprehend and accept a logical presentation of facts, regardless of who it is that is presenting them.

it's just sad...

Let's be honest, this just confirms what we all know about DBAs.

So TRWTF is that Paul allowed this total nonsense article to go out in a trade magazine, read by other DBAs, perpetuating a myth about partitioning. So now every sys admin ordered to do something equally stupid has Paul to thank when the DBA pulls the article as "proof" that his method is right.
This highlights the real lack of scholarship in computer science circles.

This smells fishy, Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck? Even if the data was spanned and the DBA used some very old way of doing stuff, the fact is there are more drives available and should have had higher I/O throughput.

Also, Paul should be fired on the spot for doing this, he is messing around with something he clearly doesn't understand (not that the DBA was any wiser, it is however the DBAs responsability), having tools intentionally report faulty information will make debugging extremely problematic.

Aha! Argument from Authority strikes again.

If there's a database administration course anywhere that babbles about where to put stuff on disk, it's a rip-off.

Here in the 21st century you put it all on a SAN and assume the guys who designed the SAN knew what they were doing. Which I suppose is TRWTF here: the presumption that no one else knows what they're doing.

/CAPTCHA: Odio, part of the flying monkeys' marching song

- The haughty parasite spend trucks of company's money for nothing and fails.
- The real computer guy takes all the risks, saves the day and shuts up.
- The parasite is acclaimed

Maybe one day people will understand that parasites can only survive if they find a resigned host to suck...

Anon:

So TRWTF is that Paul allowed this total nonsense article to go out in a trade magazine, read by other DBAs, perpetuating a myth about partitioning. So now every sys admin ordered to do something equally stupid has Paul to thank when the DBA pulls the article as "proof" that his method is right.
This highlights the real lack of scholarship in computer science circles.

I think you're being unfair to Paul, it is incredibly difficult to work with these "guru" types (and DBAs in general) so why should Paul have felt the need to put himself in the firing line to help this guy? Besides which, it's not going to perpetuate any myths - this was a trade magazine for other DBAs and we all know perfectly well that those glazed-eye morons just don't have the capacity to take on new information like normal human beings. I think Paul did the right thing - he kept his head down and out the firing line and the DBA is going to look like a moron to his peers, assuming any of his peers are actually intelligent people (unlikely for DBAs but you never know).

Mikkel:

Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck?

Hey, come on. 10^0 is still an order of magnitude

What was the Trade Magazine called? Have you got a scanned copy of it?

DBA here.

+ hardware background, from building white boxes to load balancing servers.

DBA was a moron. Stay out of SysAdmin's business, and expect him to stay out of yours. Paul wasn't much brighter. Create partitions and then create the RAID array? Was this software RAID? If so, that's TRWTF (and the real bottleneck.)

UK Guy:

Mikkel:

Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck?

Hey, come on. 10^0 is still an order of magnitude

10^0 = 1.

1 bottlneck is all you need.

Johnny Awkward:

What was the Trade Magazine called? Have you got a scanned copy of it?

Yes, I second that - I'd put that on my wall too...

Mike:

UK Guy:

Mikkel:

Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck?

Hey, come on. 10^0 is still an order of magnitude

10^0 = 1.

1 bottlneck is all you need.

Substituting into the sentence, 1 more performance

More like the certifiable DBA.

3rd Ferguson:

If there's a database administration course anywhere that babbles about where to put stuff on disk, it's a rip-off.

Here in the 21st century you put it all on a SAN and assume the guys who designed the SAN knew what they were doing.

You seem to be suggesting that a SAN would optimise disk I/O though intelligent data placement. Are you sure about that?

Who is the guy/gal approving these spending items, and how do I sucker my company into hiring that wasteful spending chump?

Really, how the hell would all those drives get approval?

I'm rooting for Paul in this story but I have to ask - why didn't he just show his greatly improved two-drive solution to the boss?

"Hey boss, you know all these problems we've been having? Turns out the DBA doesn't know how to set up an efficient drive array, I've done it myself with just two drives and the performance is spot on. Also, you can get a refund for all these unused 10,000rpm drives he made you buy. So anyway, about that raise...".

1. Write to Trade Magazine™ about how that article came about.
2. Write to their competitors, telling them the same.
3. Get popcorn.
4. ????
5. PROFIT!

anon:

1. Write to Trade Magazine™ about how that article came about.
2. Write to their competitors, telling them the same.
3. Get popcorn.
4. ????
5. PROFIT!

I guess the Real WFT™ is the hidden Southpark reference here...

Mikkel:

This smells fishy, Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck? Even if the data was spanned and the DBA used some very old way of doing stuff, the fact is there are more drives available and should have had higher I/O throughput.

Also, Paul should be fired on the spot for doing this, he is messing around with something he clearly doesn't understand (not that the DBA was any wiser, it is however the DBAs responsability), having tools intentionally report faulty information will make debugging extremely problematic.

Nice troll.

Anonymous:

I'm rooting for Paul in this story but I have to ask - why didn't he just show his greatly improved two-drive solution to the boss?

"Hey boss, you know all these problems we've been having? Turns out the DBA doesn't know how to set up an efficient drive array, I've done it myself with just two drives and the performance is spot on. Also, you can get a refund for all these unused 10,000rpm drives he made you buy. So anyway, about that raise...".

i agree, i think this should've been the real WTF

Anonymous:

I'm rooting for Paul in this story but I have to ask - why didn't he just show his greatly improved two-drive solution to the boss?

"Hey boss, you know all these problems we've been having? Turns out the DBA doesn't know how to set up an efficient drive array, I've done it myself with just two drives and the performance is spot on. Also, you can get a refund for all these unused 10,000rpm drives he made you buy. So anyway, about that raise...".

Then the DBA accuses Paul of sabotaging the previous system while beating his chest about his extensive experience. The DBA had to have some clout if he managed to:
1. convince management to buy a crapload of 7200 RPM disks
2. convince management to buy a 10000 RPM disks to replace those
3. convince management to buy even yet still more 10000 RPM disks when all of his previous purchases failed, and
4. then skipped off on vacation while the system he was solely responsible for was bogged down

The funny thing is each of these steps is reasonable for performance tuning but hardware RAID and the disk IO performance changes in recent years essentially invalidated these procedures.

Uh... ok, the 10 partitions per drive bit was a little screwy, but there's an obvious throughput advantage to staggering frequently-used areas of the database across different physical disks. A single RAID 1 array doesn't cut it for a 200 gig DB.

A SAN isn't some magic panacea that will fully-optimize the performance of any database. For very large databases you should still be spreading the data/indexes across different LUNs which correspond to entirely different disks.

Where did the tech even find a 7200 RPM SCSI disk? Was this 10 years ago? Or was the server using SATA disks (the real WTF)?

Ok, the 'certified' DBA was going way overboard, I'll give it that. 60 partitions? Unless we're dealing with a multi-terabyte database, that would seriously scare me.

*However*, there is a LOT of research into disk layout for database servers. Please look into it before you criticize either the DBA or Paul, too much here. There are valid reasons for placing logfiles on separate partitions (which I do by default), and breaking out large tables or indexes into separate files and disks.

If you really want to know the details, google "Paul Randal SQL Server" and read up on it. (He's the guy who wrote DBCC CHECKDB for Microsoft, and I think he knows a thing or two about on-disk structures for SQL Server...)

HTH

One more thing. SAN only works well if you know what you're doing, and still take some of these measures. I have standalone boxes that respond in < 3ms (Good) to disk requests, while the san responds in > 30 ms (Poor). Of course, try telling that to the SAN admins...

Corresponding partitions on disks 1, 3, and 5 were to be concatenated (not striped) to each other via RAID, while the partitions on disks 2, 4, and 6 served as mirrors of their corresponding partitions on disks 1, 2, and 5. All of these partitions were then to be mounted as directories (/p1, /p2, etc) in the file system.

So, let's see:

- Writes will be no faster than writing to a single disk (and will actually be slightly slower because there's now a layer of overhead in deciding which disk a particular block should be written to) because any given write is only ever going to one disk + its' mirror.
- Reads should be faster.

So why can't Paul show us the magazine page?

Aaron:

Uh... ok, the 10 partitions per drive bit was a little screwy, but there's an obvious throughput advantage to staggering frequently-used areas of the database across different physical disks. A single RAID 1 array doesn't cut it for a 200 gig DB.

A SAN isn't some magic panacea that will fully-optimize the performance of any database. For very large databases you should still be spreading the data/indexes across different LUNs which correspond to entirely different disks.

Where did the tech even find a 7200 RPM SCSI disk? Was this 10 years ago? Or was the server using SATA disks (the real WTF)?

I'm no sys admin but I'd say 10 partitions per disk is more than a little screwy - it's bloody stupid and a bottleneck waiting to happen. The story backs me up.

Anonymous:

3rd Ferguson:

If there's a database administration course anywhere that babbles about where to put stuff on disk, it's a rip-off.

Here in the 21st century you put it all on a SAN and assume the guys who designed the SAN knew what they were doing.

You seem to be suggesting that a SAN would optimise disk I/O though intelligent data placement. Are you sure about that?

I'm saying optimizing disk I/O through intelligent data placement is almost always premature optimization. If you're picking data location on disk then you'd better be 100% confident that there is nothing more to be done in about 5 other areas of performance tuning that always matter much more.

And if you are talking about physical data placement within a SAN you are about 10 physical layers away from the bits anyway and you'd almost always be better served by adding hardware that's closer to the database engine, meaning network, RAM and CPU cores.

Stop ragging on the DBAs. There are many "DBAs" out there who really know what they are doing. Then there are others who try to emulate the "black magic" of optimization.

I agree that this story sounds like the usual exaggeration and simplification, but the bit about the article in the trade magazine sounds awfully verifiable to me if it is true.

3rd Ferguson:

Anonymous:

3rd Ferguson:

If there's a database administration course anywhere that babbles about where to put stuff on disk, it's a rip-off.

Here in the 21st century you put it all on a SAN and assume the guys who designed the SAN knew what they were doing.

You seem to be suggesting that a SAN would optimise disk I/O though intelligent data placement. Are you sure about that?

I'm saying optimizing disk I/O through intelligent data placement is almost always premature optimization. If you're picking data location on disk then you'd better be 100% confident that there is nothing more to be done in about 5 other areas of performance tuning that always matter much more.

And if you are talking about physical data placement within a SAN you are about 10 physical layers away from the bits anyway and you'd almost always be better served by adding hardware that's closer to the database engine, meaning network, RAM and CPU cores.

A simple "no" would have sufficed.

It's not completely clear from the article but it was probably set up as RAID 5 originally. Oracle (for one) runs poorly on it. Raid 10, which is close to what the admin did, is the right way.

I had a customer who set this up for all their systems, whether they had Oracle or not, and once I pulled the DBs over to Raid 10 it improved performance and throughput considerably. (Raid 1 is much better than it was years back but I recommend RAID 10).

BAARF on.

Anonymously Yours:

Anonymous:

I'm rooting for Paul in this story but I have to ask - why didn't he just show his greatly improved two-drive solution to the boss?

"Hey boss, you know all these problems we've been having? Turns out the DBA doesn't know how to set up an efficient drive array, I've done it myself with just two drives and the performance is spot on. Also, you can get a refund for all these unused 10,000rpm drives he made you buy. So anyway, about that raise...".

Then the DBA accuses Paul of sabotaging the previous system while beating his chest about his extensive experience. The DBA had to have some clout if he managed to:
1. convince management to buy a crapload of 7200 RPM disks
2. convince management to buy a 10000 RPM disks to replace those
3. convince management to buy even yet still more 10000 RPM disks when all of his previous purchases failed, and
4. then skipped off on vacation while the system he was solely responsible for was bogged down

I disagree. As a lowly developer when I say, "we need these server specs to run this new app" management doesn't question it. How many sysadmins do you know that have a server sitting at home because they "needed a new one for x project".

BradC:

RAID-10 on dedicated LUNs = happy users, happy DBAs
RAID-5 on shared LUNs = unhappy users, unhappy DBAs

I thought that DBAs were only happy when they made users and developers miserable?

Cujo:

It's not completely clear from the article but it was probably set up as RAID 5 originally. Oracle (for one) runs poorly on it. Raid 10, which is close to what the admin did, is the right way.

I had a customer who set this up for all their systems, whether they had Oracle or not, and once I pulled the DBs over to Raid 10 it improved performance and throughput considerably. (Raid 1 is much better than it was years back but I recommend RAID 10).

BAARF on.

Raid 1 is better than it used to be??? It hasn't changed. RAID 1 is mirrored.

http://en.wikipedia.org/wiki/Nested_RAID_levels

http://en.wikipedia.org/wiki/Standard_RAID_levels

Read some before you post. There ARE purposes to each RAID configuration.

Good one :D

Let me guess, it was an ORACLE DBA, right?

> I’m not questioning your expertise

That's TRWTF right there. Even monkeys fall out of trees.

highphilosopher:

As a lowly developer when I say, "we need these server specs to run this new app" management doesn't question it.

Sure, but in these straitened times they still say “no” to the request for funds to get the hardware you say you need. There's what the app needs and there's what the app has got to be squeezed into because your capital budget was suddenly cut by 80%…

operagost:

Mikkel:

This smells fishy, Paul claims he could achieve orders of magnitude more performance by putting up a mirrored harddrive compared to many disks with dedicated partitions, and it was an I/O bottleneck? Even if the data was spanned and the DBA used some very old way of doing stuff, the fact is there are more drives available and should have had higher I/O throughput.

Also, Paul should be fired on the spot for doing this, he is messing around with something he clearly doesn't understand (not that the DBA was any wiser, it is however the DBAs responsability), having tools intentionally report faulty information will make debugging extremely problematic.

Nice troll.

Are you sure it's a nice troll? It could be a crotchety, old, ugly troll, who just finished having a billy-goat for lunch.

Another DBA comment.:

Ok, the 'certified' DBA was going way overboard, I'll give it that. 60 partitions? Unless we're dealing with a multi-terabyte database, that would seriously scare me.

*However*, there is a LOT of research into disk layout for database servers. Please look into it before you criticize either the DBA or Paul, too much here. There are valid reasons for placing logfiles on separate partitions (which I do by default), and breaking out large tables or indexes into separate files and disks.

If you really want to know the details, google "Paul Randal SQL Server" and read up on it. (He's the guy who wrote DBCC CHECKDB for Microsoft, and I think he knows a thing or two about on-disk structures for SQL Server...)

HTH

Paul Randal certainly knows what he is talking about, but you apparently don't. Log files go onto separate RAID Arrays, not separate partitions. The purpose there is to segregate the sequential IO for logging from the random IO of the data files. Logical partitions don't separate IO, its still the same disk array physically. Disk subsystem misconfiguration is one of the biggest causes of performance problems for database servers, and no a SAN is not the solution to your database server IO needs unless you have someone that understands the IO patterns associated with the database platform being implemented and configures the SAN arrays properly, and the underlying disks are dedicated.

The entire configuration recommended by the DBA here is wrong, nonsensical, and would cause significant IO bottlenecks from competing IO workloads in any RDBMS that uses the Write Ahead Logging protocol.

OK, someone please find the original article in the magazine :) Must read.

highphilosopher:

I disagree. As a lowly developer when I say, "we need these server specs to run this new app" management doesn't question it. How many sysadmins do you know that have a server sitting at home because they "needed a new one for x project".

No sysadmins that I know would lie, cheat, and steal like that. I'm serious -- I hate dishonesty. I don't have any servers at home because I claimed that we "needed a new one for x project". If you have done that, shame on you.

Mikkel:

Also, Paul should be fired on the spot for doing this, he is messing around with something he clearly doesn't understand (not that the DBA was any wiser, it is however the DBAs responsability), having tools intentionally report faulty information will make debugging extremely problematic.

He's the Systems Admin. So..

You seem to be suggesting that a SAN would optimise disk I/O though intelligent data placement. Are you sure about that?

I'm not the one who said that, but yes that's a valid expectation for an enterprise SAN solution - it should do that for you, at block level.

I assume this story goes back a few years, as worring about disk partitioning for DB optimisation is nonsense today (at most it should comes down to deciding on striped v mirrored v striped+mirrored RAID if the storage is even on the local system).

I'm sure most of us here work with database that are under half a terabyte anyway, and so can fit comfortably in RAM on a single x64 box.