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

Original link. http://thedailywtf.com/Articles/The-Certified-DBA.aspx.

Article summary: The DBA suggests an approach involving rigorous partitioning, 10 partitions per disk (3 actual disks and 3 raid). The stats show that the performance is non-optimal. Then the DBA suggests an alternative of 1 partition per disk (with more added disks). This also fails. The sys-admin then sets up a single disk, single partition and saves the day.

The size of disks was not mentioned but given today,s typical disk sizes (of the order of 100 GB), the partitions ; would be huge, it surprises me that a single disk with all partitions outperformed.

Initially I suspect that the data was segregated and hence faster reads. But how come the performance didn't degrade as time went by with all the inserts and updates happening? Saw this on reddit, but the explanation was by far spindle/platter centered. There was no mention in the article about this. Is there any other reason? I can only guess that the tables were using a incorrect hash distribution causing non-uniform allocation across disks (wrong partitioning); this would increase fetch times. Any thoughts?

share|improve this question

4 Answers 4

The whole thing is moronic, sorry.

If you have 6 disks, you could try

  • 3 pairs of RAID-1. Each with one partition each. First partition for system/tempdb, second for data, third for transaction logs.

  • 1 pair of RAID-1 and one 4-disk RAID-10. One partition each.

  • If the data is mostly read-only, one big RAID-5 volume, with one partition.

In any case, it does not make any sense to use the same platter for different volumes.

share|improve this answer

To understand why a single partition will always outperform multiple partitions on the same drive (all else being equal) you only have to think about what the head assembly needs to do. There will always be a lot of moving back and forth but with multiple partitions the requirement to move the assembly will be very much greater. As this is the slowest part of disk access the effect on performance is great.

share|improve this answer

The whole partitioning thing sounds stupid. Sorry. partitioning data on the same platter (i.e. multiple files in multiple partitions) is not reducing IO in any means compared to having all the files in one partition. I also find it funny the DBA did not bother making IO tests with a testing tool to check the real performance of the disc setup.

Theoretically, regarless how bocked up thigns are, the IO capacity of MULTIPLE discs (and they were buying them more and more) is always a LOT higher than that of a single one - so something is really fishy here. Not saying the article is wrong - just I miss the pieces to put some reasoning into the bad performance.

share|improve this answer

There is lots of missing information in the article, but it sounds to me like the DBA thought that he was trying present the database with a block device, (vs. a filesystem) but was doing so on some sort of SAN or other shared storage device. Unfortunately, he was also an idiot.

Maybe the "Certified DBA" was an AS/400 or AIX refugee... IBM sysadmin tools used to let you assign storage based on physical location on disk.

I used to do this (correctly) a decade ago with Informix databases on Sun boxes when my employer was too cheap to buy Veritas Filesystem. When the system went down, having the database manage the disk would avoid 4+ hour UFS fsck runs.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.