A school of thought exists in many database and system administrators that performance tuning is the highest of arts, the greatest of skills, and the loftiest of aims. Buy any system and tune it well - so goes the theory - and it will perform to a level unanticipated by your peers. You will receive praise and adulation.
I fully understand that it is a rewarding and enjoyable occupation, for the task itself is beautifully self-contained. No messy “user requirements” or “business processes” to consider, just pure unadulterated tuning. Gather your metrics, dig out your obscure, imported, reference book (preferably with a cool red Porsche on the front), stick on the headphones (Radiohead preferred), sip your coffee, and start tunin’ them there systems!
You can probably tell that this is something I’ve done before. You certainly wouldn’t be wrong, and I do - I’ll confess - slightly yearn to find myself in that situation again where I can geek out and optimise to my hearts content, sure in the knowledge I’m taking the greatest test a geek system administrator can take, with a proof that is irrefutable: The system now runs five percent faster because I’ve tweaked insert_complicated_term
But there’s a problem with this. It’s not actually the best thing to be doing in most situations.
This might sound a bit controversial at first - especially to any sysadmins or DBA’s reading this - but do allow me to explain over the course of the next few paragraphs. I’m certainly all for doing it when the situation necessitates it. But as a means in itself, as a reason-d’etre, it’s really isn’t the best way of expending effort.
It’s my experience, gained over years of supporting systems that are often quite closely tied to the users - rather than abstracted away through layers of support and departmental obligations - that almost all of the performance problems I’ve encountered have been down to simple things, often simple programmatic problems or errors brought about by a misunderstanding (from the developer or administrator), an architectural choice, or simple resource contention.
Ok, so maybe in some places the developers are all top of the class, fully jargon compliant, aspect, object, extreme, agile, pure-jargon oriented folk with the lastest iPod and geek-chic t-shirt. But I’m yet to experience such a place, and heck, even the best developers (and certainly system administrators!) should admit they’re prepared to learn a thing or two from each other. Eagerness to learn is, in my mind, more admirable and beneficial than complacency and over confidence.
It’s true that it’s the administrators role is to ensure that the best performance is achieved from the systems in use, but all too often I see administrators and DBA’s zooming in on the minutiae, rather than looking at the bigger picture. Not seeing the wood for the trees, if you like (although that’s perhaps a bit unfair).
The rub is us system administrators are often quite an antisocial lot, no laughing matter the moniker “BOFH“. We find it easier to deal with machines, rather than engage with the developers. Yet it’s precisely the engaging with the developers where most of the big performance problems can be solved. Work together to find and correct those approaches - drawing on the best knowledge from the team - so that the application and the database work well together, and things will be much easier/faster - both now - in the future.
Give a man a fish; you have fed him for today. Teach a man to fish; and you have fed him for a lifetime
What I’m saying here then is, rather than focusing on tuning out the best performance as a means in itself, it’s better to sit up, take stock of the entire application infrastructure, and engage with the people working on it elsewhere. Take a holistic view. Don’t focus purely on the performance tuning detail, but engage with the users, the developers, the managers and the other administrators, and try and build a picture. Is the approach right? Challenge assumptions, and question decisions. But build a picture. With that picture, I’d bet that better performance can be built over time, certainly that can be built purely by focusing on the detail.
July 27th, 2005 at 10:58 am
+1
That big picture thing is something David Gelernter calls “topsight” and it’s important in all sorts of things - it helps one spot trends, train wrecks (of the project variety), opportunities etc. Unfortunately, getting stuck in the detail and ignoring the big picture is something humans do very well.
Watching the debate re: the recent shooting of an innocent on the underground is very instructive. Most are attempting to reduce it to a discussion about right/wrong or good/bad policy when there are all sorts of other things to worry about like practicality of application, available time and data etc.
October 4th, 2005 at 8:24 pm
[...] So here’s the rub, to me at least. I think the DBA’s function as a guru of configuration options and optimiser tricks is not what’s needed by enterprises. Sure, they want people who understand the products, but they want DBA’s further up the stack, working with developers to build better applications, fixing business problems, and not fine-tuning obscure parts of their infrastructure. Most of the problems can be fixed with good, proper, sensible data modelling techniques, and using products that (as they rightly should) take care of the maintenance tasks like index rebuilds, statistics updates or de-fragmentation processes automatically. [...]
February 21st, 2006 at 2:52 am
I agree that performance tuning is not the highest of arts, but to do it correctly does require a unique and extensive set of skills. To give you an example, I needed to scale a system that couldn’t handle the current load to a load of over 20 times the current load. This had to all be done with little or no additional hardware investment.
I think the myth is that you can always scale linearly by adding hardware. The system I’m speaking of had no way to grow through hardware because the architecture and code was so poor, you could throw 100 times more hardware at the solution and achieve only 10% additional throughput in the same amount of time. This database forced single threading of transaction batches and had queries that would grow exponentially each day due to all of the table scans.
In order for the prior solution to be effective, it took a deep understanding of user requirements and business processes. The problem included manual processes that would many times fail that would in turn cause system failures. The users would perceive these process failures as performance problems since it would add to the overall run time of the batch applications.
I agree with you on the necessity to stay away from the obscure improvements that shave a milli-second here and a milli-second there. We implemented a repeating process that identified and isolated the 10 worst performance problems and addressed those. After significant unit, integration and system testing we would repeat the process with the 10 new worst areas until we reached our objectives. It is occasionally hard to steer the bits-and-bytes people away from semantics and focused on the top 10.
I assume when you say “It’s not actually the best thing to be doing in most situations” you mean that performance tuning of code should not be done if there isn’t a positive ROI, or Return On Investment. This is true. If it is cheaper to add hardware to meet your scaling needs than to tune software and configurations, you should add hardware. If it is cheaper to tune software than add hardware, you need to tune software.
How would you spend your own money? Would you spend $500 to fix your boat motor to go your needed 35mph for skiing or spend $40,000 on a brand new boat. This may sound like a ridiculous comparison but you would be surprised how many people will argue for the new boat when they are spending their company’s money.
April 5th, 2006 at 12:15 pm
Yes - I agree with your analysis. Performance depends upon several factors including Application programs, users perception of data, users requirements, processing criteria etc etc. You need to get a better and a bigger picture of various processes to see what is going on from end to end. Only then can you point out the flaws in the process. Politics and ego has to be set aside for resolving issues and problems.