1

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?

user38311
  • 101
  • 1

4 Answers4

1

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.

Ricardo Pardini
  • 766
  • 7
  • 9
1

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.

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
0

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
0

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.

duffbeer703
  • 20,077
  • 4
  • 30
  • 39