“I’m not questioning your expertise,” Paul cautiously said to the Certified DBA, “it’s just that I’m just not used to requests with… this level of detail.”

Paul should have done what he was asked, exactly how he was asked to do it. After all, he was not an expert but just a lowly systems administrator. Fortunately, the Certified DBA made sure to keep him in his place.

“I would expect not,” smirked the Certified DBA, “system administrators generally don’t know how to tune for database performance, so I have to be very specific on the server configuration.”

The “configuration” that the Certified DBA was referring to was a rather complex set-up of disk partitions and RAID. Per his request, the server Paul was to set up should have six disks, each with ten partitions set-up. 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.

“Fair enough,” Paul acquiesced, “but I’d like to understand why this helps with database tuning . I mean, wouldn’t this just create slower reads and writes? I just don’t see how it helps perfor—”

“I’ve been doing this for fifteen years and it’s not like you can understand database tuning overnight.” The Certified DBA was getting aggravated. “The long and the short of it is: the partitions allow me to place datafiles on different physical parts of the disk. The outer edge of the disk is the fastest, so I put indexes in partition 1. Large binary data goes on partition 10. And so on.”

Paul wasn’t convinced, and still couldn’t imagine how this would improve performance, especially since a query that touched several tables would require reading several partitions from disk. But as the lowly system administrator, he did as he was told and set-up the sixty partitions and then configured them with the RAID tool.

The application launched a couple months later and, shortly thereafter, ran into performance problems. The Certified DBA was not surprised, especially after learning that the drives Paul ordered were only 7,200 RPM. “It’s just common sense,” he scolded Paul, “go with 10,000 RPM! Why would you even bother with 7,200 in anything you do? I’m not a system admin, and I know that.”

Doing exactly as he was told, Paul ordered another batch of drives – this time 10,000 RPM –and configured them in an “even more performant configuration.” Instead of utilizing the entire disk, only the top 25% of disk space was to be used. According to the Certified DBA, the bottom 75% – located beyond the “outer rings” – was far too slow, and needed to be abandoned.

It took an exorbitant amount of time to configure, and it still didn’t work. The application ran awfully slow, and disk I/O was through the roof. Worse, as a result of the smaller drives and 25% usage requirement, the available disk space was quickly filling up.

The Certified DBA offered yet another idea: stagger the partitions such that partition 1 starts on disk 1, partition 2 starts on disk 3, and so on. And, of course, buy more 10,000 RPM drives. In theory, it should drastically help alleviate the high I/O, since it would “randomize” where data were being stored or retrieved.

Paul, being the lowly system administrator, was not in a position to argue with the Certified DBA, and had little choice but to do what he was told. But instead of doing just that, Paul decided to try something different.

While the Certified DBA was on vacation, Paul took a pair of the 10,000 RPM drives, mirrored them, and then set-up a series of directories (/p1, /p2) on the file system. When the system was brought back online – using a single drive and a single partition – the I/O reads went down by a couple orders of magnitude. The overall application performance improved dramatically, and the users were appreciative.

But there was one thing, however, that was missing: approval by the Certified DBA. Undoubtedly, he’d be upset that his instructions weren’t followed, and accuse the lowly sysadmin of subterfuge and insubordination.

With the Certified DBA returning the following day, Paul was desperate and changed the “df” command such that it executed a shell script that looked identical to the real df command: it listed ten partitions, each mounted in /p1, /p2, and so on.

And the shell script worked. In fact, it worked so well that the Certified DBA took a screenshot of it and used it in an article he wrote about database “tuning” for a trade magazine. And, being the nice guy that he was, the Certified DBA gave Paul a signed copy of that issue.

Though the Certified DBA is long gone, Paul still keeps the signed magazine hanging in his office. It’s taught him more about database tuning than he ever could have hoped for.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!