The Marginal DBA

You Have A Performance Problem; What Do You Do?

Brent Ozar had a blog post about what hardware you can purchase with the price of two cores of Enterprise Edition and argues that you should probably spend some more money on hardware.  Gianluca Sartori has a blog post along similar lines.  By contrast, Andre Kamman had an entire SQLBits talk about not throwing hardware at the problem.  And finally, Jen McCown argues that you should spend the money on Enterprise Edition.  When you have a performance problem, who’s right?

The answer is, all of them.  And to head off any comments, my first paragraph is mostly a setup; ask any of these authors and I’m sure they’ll tell you that “it depends” is the right answer.  The purpose of this post is to dig a bit deeper and discuss when each of these points is the correct answer, looking at things from an economist’s perspective.

Detour Into Basic Economics

Before I get too deep into application of economic principles to the world of database administration, let’s cover these principles really quick so we’re all on the same page.

Scarcity

Your data center is only so big.  Your rack can only hold so many servers.  Your credit card has a limit, and so does your bank account.  At some point, your budget runs out.  And even if your budget is phenomenal, there are technological limits to how big a server can be, or even how big a cluster of servers can be.  No matter how much cash, how many top-tier experts, and how much time you have available, there is always a limit.

I’ve personally never had to worry about hitting the 128 TB RAM limit in Windows Server 2012 R2.  Even if you had a server capable of using 128 TB of RAM, I’m sure it’d be so expensive that there’s no way I’d ever be allowed near it.

This, in short, is scarcity:  you will always run out of resources before you run out of problems.

Opportunity Cost

In a world of scarcity, we can figure out the true cost of something:  what is the next-best alternative to this?  For example, let’s say we have the option to buy $100K worth of hardware.  What other alternatives exist?  We could bring in a consultant and pay that person $100K to tune queries.  We could send the current staff out for $100K worth of training.  We could hire a new person at $100K (although this is a stream of income rather than a one-time payment, so it’s a bit trickier of an answer).  Or we could buy $100K worth of software to help solve our problem.  Dollar values here simply help focus the mind; even without currencies, we can still understand the basic concept:  the actual cost of a good or service is what you forego when you decide to make the trade to obtain that good or service.

Understanding opportunity cost is critical to making good decisions.  It also helps lead into the third major topic today.

Marginal Utility

One of the toughest problems of pre-Marginal Revolution economics was the diamond paradox.  The short version of the diamond paradox is as follows:  water is more valuable than diamonds, in the sense that a person can live with water but no diamonds, but not vice versa.  So why is water so inexpensive, yet diamonds are so expensive?

The answer is in marginal utility.  The idea behind marginal utility is that your valuation of a thing changes as you obtain more of that thing.  The first glass of water for a thirsty man in the desert is worth quite a bit; the 900th glass of water, not so much.  In hardware terms, going from 8 GB of RAM to 16 GB of RAM is worth more to you than going from 192 GB to 200 GB.  The 8-16 jump allows you to do quite a bit that you might not have been able to do before; the jump from 192 to 200, even though it is the same total RAM difference, opens many fewer doors.

Detour’s Over; Back To The Main Point

Armed with these principles of economics, let’s dive into the problem.  To keep things simple, I’m going to think about three competing areas for our IT budgets:

  1. Purchasing newer, better, or more hardware
  2. Paying people to write better code or tune application performance
  3. Purchasing Enterprise Edition licenses rather than Standard Edition (or APS appliances rather than Enterprise Edition)

Applying the scarcity principle first, we know we can’t afford all of these things.  If your company can afford all of the items on the list, then this whole topic’s moot.  But honestly, I’ve never seen a company whose budget was so unlimited that they could keep hiring more and more people to fix code while buying more and more hardware and getting more and more expensive software.  At some point, you hit a limit and need to make the hard decision.

Here’s a simplistic interpretation of those limits:

LinearConstraints1.png

In this example, I have a set of hardware, queries, and SQL Server versions that I allocate to OLTP processing and OLAP processing.  I have three constraints (which I’ve made linear for the sake of simplicity):  hardware, application code, and SQL Server versions.  The idea behind this two-dimensional linear constraint picture is that you are able to pick any point on the X-Y plane which is less than or equal to ALL constraints.  In other words, you can pick any spot in the purple-lined section of the image.

Taking this scenario, the application is tuned reasonably well and the version of SQL Server isn’t going to help us much; we simply don’t have powerful enough hardware.  This might be something like trying to run a production SQL Server instance on 8 GB of RAM, or running on 5400 RPM hard drives.  Even if we hire people to tune queries like mad and push the red boundary out further, it doesn’t matter:  we will still have the same bottlenecks.

By contrast, in this case, if we purchase new hardware, we can shift the purple curve out.  Let’s say we get some fancy new hardware which solves the bottleneck.  Now our constraint problem might look something like the following:

LinearConstraints2.png

We still need to pick a spot somewhere in the purple-lined section, but notice that our constraint is no longer hardware.  In fact, we have two potential constraints:  version limitations and application limitations.  The answer to “what do we need to do?” just got a bit more difficult.  If we are in a situation in which we lean heavily toward OLTP activity, our next bottleneck is the application:  now it’s time to rewrite queries to perform a bit better.  By contrast, if we’re in a big warehouse environment, we will want to upgrade our servers to take advantage of OLAP features we can’t get in Standard Edition (e.g., clustered columnstore indexes or partitioning).

Can We Make This Practical?

In the real world, you aren’t going to create a linear programming problem with defined constraints…probably.  Nevertheless, the important part of the above section is the principle:  solve your tightest constraint.

The Easy Stuff

Here’s a bit of low-hanging fruit:

  1. If you’re running production SQL Server instances with less than 32 GB of RAM, you almost certainly have a hardware constraint.  Buy that RAM.  Going back to Brent’s post, you can buy a brand new server for $7K, but if you have a server that can hold at least 128 GB of RAM, it looks like you can buy that RAM for about a grand (and I’m guessing you can probably get it for less; that’s retail price).
  2. If you haven’t done any query tuning, you can probably find two or three costly queries and tune them easily.  They may not be the biggest, nastiest queries, but they help you reduce server load.  Similarly, find two or three missing indexes and add them.
  3. If you query relatively small sets of static data, put up a Redis server and cache those results.  A client of mine did that and went from about 85 million SQL Server queries per day to 60,000.  Even if your queries are perfectly optimized, that’s an incredible difference.

The nice thing about low-hanging fruit is that the opportunity cost is extremely low.  Spend a grand for RAM, 3-5 hours tuning queries, and a few hundred dollars a month on Premium Redis caching.  For a company of any reasonable size, those are rounding errors.

The Hard Stuff

Once you get beyond the low-hanging fruit, you start to see scarcity creeping back in.  You’ve got reasonable hardware specs, but maybe that Pure Storage array will help out…or maybe you need to bring in top-notch consultants to optimize queries in a way you could never do…or maybe it’s time to buy Enterprise Edition and take advantage of those sweet, sweet features like In-Memory OLTP.

Finding the right answer here is a lot harder of a problem.  One place to start looking is wait stats.  SQL Server keeps track of reasons why queries are waiting, be they hardware-related (disk, IO, CPU), scheduling related, network bandwidth related, or even because your application can’t handle how fast SQL Server’s pushing data across to it.  There’s a bit of interpretation involved with understanding wait stats, but Paul Randal is putting together the definitive resource for understanding wait stats.

The rest of my advice comes down to experience and testing.  If you’re in an environment in which you can run tests on hardware before purchase, that’s an easy way to tell if hardware is your bottleneck.  One scenario I had a few years back involved a case in which we got to play with a solid state disk array back when those were brand new.  We ran our production SQL Server instances on them for a week or so and saw almost no performance gain.  The reason was that our servers had 8 GB of RAM and 1-2 VCPUs, so faster disk simply exacerbated CPU and memory issues.  By contrast, at my current company, we had a chance to play with some newer servers with more powerful CPUs than what we have in production and saw a nice performance boost because we’re CPU-limited.

The nice thing is that my graphics above aren’t quite accurate in one big sense:  application tweaks and hardware work in parallel, meaning that buying new hardware can also push out the application tweak curve a bit and vice versa.  This means that, in many cases, it’s better to find the cheaper answer, be that hardware or sinking hours into application development and tuning.

Emphasizing Sunk Costs

There’s one last thing to discuss here:  the concept of sunk costs.  If you want a long-winded, rambling discussion of sunk costs, here’s one I wrote while in grad school.  For the short version, “sunk costs are sunk.”

Why is it that a company is willing to pay you $X a year to tune queries but isn’t willing to pay 0.1 * $X to get hardware that would end up solving the constraint issue above so much more easily?  The answer is that, from an accounting standpoint, the new hardware is a new cost, whereas your salary is already factored into their budget.  From the standpoint of the in-house accountant, your salary is a fixed cost (which they have to pay regardless of whether you’re spending 80 hours a week tuning queries like a boss or 5 hours a week of work & 35 hours of Facebook + Pokemon Go…at least until they fire you…) and from the accountant’s perspective, your effort doesn’t get shifted around; you’re still a DBA or database developer or whatever it is you do.  So your salary is a sunk cost.

I should mention a huge, huge point here:  salaries are sunk costs in the short term.  In the long term, salaries are neither fixed nor sunk costs.  In the long term, your manager can re-assign you to a different department (meaning that your salary is not sunk), and your salary can change or even go away if it makes sense from the company’s perspective (meaning that salary is a variable cost rather than a fixed cost).  But for our purposes, we’re interested in the short-term ramifications here, as “long-term” could mean a year or even several years.

Also, there is one big exception to the rule that your salary is a sunk cost:  if you get paid hourly, your wages become a variable cost, and it’s a lot easier to sell the company on trading variable costs for fixed costs:  “If you buy this hardware for $X, you don’t have to pay me 3 * $X to tune queries.”  That’s an actual cost reduction, whereas in the salary case, you’re still arguing for a net increase in company cost incurred.

So what can you do in that case?  Selling the company on “reduced cost” doesn’t cut it so much for salaried employees because you generally aren’t really reducing costs from the company’s standpoint.  Instead, you have to sell on opportunity cost:  if you weren’t spending 15 hours a week trying to tune queries to get this ETL process to work adequately, you could focus on Project X, which could net the company $Y in revenue (where $Y is an expected revenue return conditional upon the probability of success of Project X).  If $Y is substantially higher than the cost of hardware, you now have a solid business case you can take to higher-ups to get newer, better hardware.

Similarly, if you’re spending all of your time on application development and the company’s throwing barrels of cash at vendors for new hardware, you could make the case that getting some time to tune queries might allow the company to save money on net by deferring the cost of hardware purchases.  This is an easier case to make because, again, your salary is a sunk cost (unless you’re a wage-earning employee), so the opportunity cost comes down to what the next-best alternative is with your time.

Conclusion

For the most part, I can’t tell you whether you’re better off buying more hardware, tuning those queries, upgrading to Enterprise Edition, or doing something entirely different (at least unless you’re willing to shovel cash in my direction and have me take a look…).  What I hoped to do in this blog post was to give you some basic economics tools, letting you apply them to your own situation.  With these concepts in place, they’ll give you ammunition when going to management to ease your biggest constraint.  Managers and accountants are going to be a bit more amenable to arguments around opportunity cost, and your pleas are less likely to fall on deaf ears when you realize that sunk costs shouldn’t affect future behavior.

100% of the people reading this post are reading this post (and other lies about statistics)

A friend recently shared an article about the Ice Bucket Challenge that claimed only 27% of the money raised is going towards research. Here’s the article. 

Here’s the headline: 

ICE BUCKET CHALLENGE: ALS FOUNDATION ADMITS LESS THAN 27% OF DONATIONS FUND RESEARCH & CURES

$95 Million Later: Only 27% Of Donations Actually Help ‘Research The Cure’

I was pretty angry. The tone of the article is really awful too, slamming the ALS foundation for these heinous crimes. Yet, there’s some additional facts tucked away in a pie chart that give the lie to the headline. 19% of the funds raised go to patient and community outreach; a viable use of funding, don’t you think? 32%, the largest chunk of the funding, goes to public education. How dare they spend the money trying to make people aware of the disease and its effects! That’s what Wikipedia and webMD are for! Oh, and the $95 million figure they quote isn’t what they actually break down in the chart either — it’s only the expenses for the year ending January 31, 2014.

Given that pie chart, in fact, 79% of the donations go directly to aiding sufferers of the disease or increasing awareness; that’s pretty good. The foundation is rated very highly by Charity Navigator too. 

The salary for the CEO is pretty insane — $300k+ is nuts for a non-profit. However, it’s only a tiny slice of the total pie, and not nearly as bad as scaremongers would have you believe. If we, in the United States, don’t want to use tax dollars to contribute to health care, funding of organizations like this one is a great way to contribute. 

Elon Musk: World’s greatest pro wrestler? Or greatest human being?

Sadly, he is not a pro wrestler. But he is the CEO of Tesla Motors, and he just did something so awesome that he may qualify for greatest human being in the world. Let’s set the brilliant title of his post aside for a moment. (Okay, giggle for a moment or two at the title.)

Tesla renouncing all of their patents is more than just whipping it out and laying it on the table. Well, okay, whipping out their patents and laying them on the the table is exactly what they did. It is, however, a gauntlet thrown down at the feet of every major motor vehicle manufacturing in the world. This is Tesla saying, to be bluntly, “our product is so awesome, that we will tell you how to make it, and you still won’t succeed.” It is also Tesla saying, “Yeah, we’re already super rich, but we think that better, safer, more reliable cars is good for everybody and good for the planet.”

Maybe you believe human caused global warming isn’t a thing. Maybe you think that fossil fuel dependency isn’t a big deal. I’m not judging you. I’m not sure either. However, let me share a story for a moment.

As long time readers know, I have been in St. Petersburg, Russia, for the past ten months. Russia has been in the news quite a bit in those ten months for violence both inside the country and outside. I’ve even had my wallet stolen. Yet the one time I felt most in danger was when I was in the car going from the airport to the hostel. Why? Because the air quality was so absolutely putrid (and I’m an asthmatic) that for a few moments here and there, I literally could not breathe.

That’s not good. While I’m generally a misanthrope, I would prefer that human beings not die for stupid reasons. I share DNA with them. So, auto companies: pick up the gauntlet. Mass produce the ever loving shit out of these fuckers. Bring the cost down to a level where the average citizen can afford one. You will make shit tons of money — humans like moving around. I’m pretty sure you like money. There is no downside.

Sorry, Mike Trout: should have gotten an economics degree

Regression is quickly becoming the most interesting site in the Deadspin network (if the least funny). A recent article actually quantifies a superstar General Manager as more valuable than the best player in the game.  You can get his actual paper here.

I’ve skimmed the paper — the writing itself could use some improvements, I would argue, but that’s a stylistic question — and it seems solid to me. Undervaluing things that should be overvalued and vice versa is a skill, perhaps a tradition, in MLB. (Hi RBI and Saves! We love you! Don’t change!)

Yet, while I agree with his conclusions, I raise a further point. Consider that an MLB roster consists of 25 players. Ideally, if all else is equal and nobody gets hurt, you will have 8 or 9 regular players, and five starting pitchers. So, the “meat” of an MLB roster should be 13 to 14 players (depending on league). The rest are injury replacements, situational players, and relievers. Okay. Ignoring the minor leagues for the sake of simplicity (although one could argue that a GM’s influence on the minors is as important, or more so, than the majors), that means he has 13 or 14 critical decisions to make where he can make an immediate, direct impact.

Now let’s look at the NFL. You have 53 players on a roster, and no minor leagues, so this comparatively easier to make sense of. You, nominally, have 22 starters in the NFL. However, unlike MLB, the depth chart doesn’t stay static because, especially on defense, players move around and formations shift. So, out of that 22 starters, I’d offer the following players as “true starters”, in the sense that they’d play every snap in a perfect world where injuries aren’t an issue.

— The quarterback

— The offensive line

— The #1 and #2 WR

— The #1 and #2 CB

— The safeties

— The middle linebacker (or two)

— The nose tackle

That means that an NFL team should reasonably expect these 14 or 15 players to play every snap. The other positions are situational (your defensive front seven will shift positions quite frequently and take plays off) or based purely on formation (absence or presence of a TE or FB or nickelback). That’s… about the same as MLB. You can even argue it’s fewer, because there are nose tackles who don’t play every snap (although few of them). According to Bleacher Report, the average NFL GM makes about $2 million a year. We could argue NFL GMs are undervalued, and I think that’s accurate, but given the number of impact decisions they have to make, but with a much larger roster (and therefore with more margin for error), they should be undervalued relative to MLB GMs.

Consider, finally, the missing element: player development. When you hire a GM in any sport, but particularly MLB and the NFL, the public thinks their job is to make the team better now. It’s actually to make the team better tomorrow. To do this, MLB has, at minimum, six teams (considering one AAA, one AA, High A, A, and two rookie league teams) are devoted to each major league team for this very purpose. In the NFL, you have the 53 man roster. That’s it. If you want to argue that the NCAA is the minors for football, I can’t argue on the whole, but recall that there’s no former affiliation and that talent is much more randomized in this sense.

When we add the player development aspect back into the equation, I would actually consider the NFL executive to have the more difficult job, strictly based on having fewer options to work with. Therefore, I think that if MLB executives are undervalued (and they are), NFL executives are more so, and also deserve some more cash for their trouble.

Contra Penguatroll: The Spike In Tuition

This started out as a comment on yesterday’s blog post, but I’ve been terrible lately about posting, so I’m turning this into a full-blown post.

Reasons for tuition going up:
1) Greater demand. Tony is spot-on here; when American culture has gotten to the point where you _must_ go to a four-year university to “find yourself” (i.e., drink until you vomit) and the mere concept of physical labor is sub-human, universities are in a position to charge more.
1a) Corollary: smart people sucker not-so-smart people into giving the smart people money. There are a number of people (your average elementary education major, for example) who should never step foot on a legitimate university campus and who basically get taken (or have their parents get taken) to the tune of $150-200K for four years of partying. Think Wall Street but with more ivy and cheaper booze.
2) Restricted supply. Universities must be state accredited and must maintain this accreditation. Like other industries with state-enforced (or State-enforced, if you prefer) regulations, these barriers to entry protect the existing firms by preventing potentially-innovative firms from entering the market.
3) Subsidies. If a bill passed Congress tomorrow stating that every undergraduate student would receive $10,000 a year in “free” federal funding, what’s the first thing that happens? Universities raise tuition by $10,000. The marginal university student was willing to pay $X to get into a school. When everybody gets a level boost, the marginal student is now willing to pay $X+10,000. Look at how state and federal student funding has gone up over the years and see the natural result.

I do not consider loans as such a bad thing, nor are they a huge reason for tuition increases. A loan is nothing more than an agreement in which a person is allowed to re-distribute expected earnings from a later time period to an earlier time period. This means that there is some effect from the existence of loans, allowing certain people an opportunity to pay who otherwise would never have been able to afford an undergraduate education…but I don’t think it’s really that much. Instead:
3a) Subsidized loans. Here’s the part where Tony’s argument becomes a lot more valid. Those low interest rate loans tend to be federally subsidized.

I don’t like any of our resident Penguatroll’s proposed solutions. My solution would be to eliminate the problems as they exist:
1) Re-introduce a culture in which blue collar work is honorable instead of something you want to avoid. This is not something a government could do directly, but instead must happen through the efforts of people interested in this cause.  There is something that educators could do (with government support, in our current system):
1a) Make high school much, much harder. Not everybody should graduate from high school. I absolutely hated this idea ten years ago, but something like the German system of early tracks, good vocational schools, and expectations that relatively few people will actually make it to a university would be better than No Child Left Behind, Common Core, and cradle-to-school ridiculousness.  It might even be better than the pre-Head Start American system.
2) Remove accreditation altogether from state or federal purview. Let schools compete on the merits and private accreditation sources will emerge (e.g., something like an expanded US News and World Report). This would also allow new institutions to enter the market. I know the cry against this would be that people could just buy diplomas, but I have a two-fold response: first, those diplomas would quickly become meaningless as people obtain information about the diploma mills; second, college football and elementary education. Tell me finger painting and basket-weaving courses reach the rigour of higher education and that those students most assuredly are the intelligentsia of tomorrow. Actually, don’t: I’ve already seen Idiocracy.
3) Eliminate government subsidies of universities. I’m tempted to say all subsidies altogether, turning “public” universities private. But because I’m feeling moderate this evening, I’ll just stick with eliminating the demand-side subsidies: federal and state grants, loans, and other payments to students. This would force universities to drop tuition costs significantly, in an amount which just about matches the drop in subsidies.