The database isn't your performance bottleneck

Quite often as a data professional you will hear that the database is the bottleneck although there isn't really much backing up why other than people's experience. I suspect that this is most likely that their experience has been a poor one and that it's a general thought without really understanding why they say this.

For my part, I'd normally quite happily say that the opposite can be true, but lets give it a little more substance if I can.

Databases are generally misunderstood and even more dangerously, people 'think' they understand them. If you look in terms of performance you find there are some areas that time is spent/lost and these are :

• Logging (a transaction log)
• Latching (for the most part this happens when loading data into memory)
• Locking (locking the data so it can be altered)
• B-Tree maintenance (indexes)
• Querying the data (yes, this is where joins come into it if using an RDBMS)

When you add in high concurrency you effectively complicate this as the database has a much smaller window (that decreases as you add users) to operate in. Plus all 5 are mixed in together.

The more data you need to work with, the more thought you need to put in. My current systems have approx 2 billion rows across all tables. My previous employers had individual tables with 17 billion rows in (a 1.5TB table). 

The easiest ways of giving yourself a bigger window is to add memory and speed up your IO responsiveness. NAND flash negates the latency found with mechanical storage so some previous worries can be pushed further away. This is easy stuff for a DBA to do as it doesn't involve the development teams and gives quick and easy wins with minimum effort (there is a hell of a lot more that goes on in case you were wondering). Once you have done this you take care of most of the issues mentioned above. However, this leaves querying.....

As a general rule, people write bad queries (and this includes seasoned data professionals). It's not that they mean to, it's just that you can write the same query in dozens of ways and the speed will vary a lot. You don't get told what's good or what's bad and can generally get your data back regardless. Until you see this under load you won't be aware of how this manifests in live. This is the problem. You likely haven't actually seen that the problem exists already and it won't rear its head until it's under load. What makes this problem a hundred times worse is that when identified, there is little willingness to make a simple change and improve it. Rinse and repeat and you have a mess.

For this reason, DBA's will want to be able to get to the offending query (hence preferring stored procs as they won't get to the code). If they can tweak the query, they can improve the system. If they can't, they have to find a willing product owner, dev lead or whatever you want to call them. Time has to be found and testing re-done and so on. If it was right early on before shipping it saves time and money.

A DB developer should be able to help tune the queries but sadly this is often looked at as 'micro-optimisation'. It's not really fair that they are held back from applying their experience but are then beaten with the stick that has 'bottleneck' written on it.

An ORM can help save a lot of development time, but you really should think about how many times that query is run and how much cost there is to support that for several years. Personally I'd suggest the ORM should be used to speed up development but then write the query properly when it needs to be done. Don't just stick to one rule or the other, find the balance.

A lot of teams do TDD (Test Driven Development) and if they don't, they should. Maybe I should push people towards Load Test Driven Development ?

If you have a large number of people using your system you really do need to go beyond functional testing, but also put a lot of effort into load testing. This isn't something that necessarily refers only to the data tier, but throughout the stack. You may perceive that a query functions fine as it passes your tests. Well, being blunt, that's a bit boring in the high concurrency game. How does it function with 100 users, 1,000 users ? 10,000 ? And so on.....

Those problems that you didn't know you have will rise to the surface and then you have a bottleneck. You fix this by solving the IO, memory, code, and scale out your hardware to suit. It won't address the underlying issues though.

Don't start thinking you are Facebook or Twitter and use what they use to solve much bigger concerns. Just get the little bits right. It will make the world of difference. When you find a problem, fix it and move on. Don't find it, flag it and then do fuck all about it.

Your end game is to ultimately solve latency throughout the stack. The less things that get in the way, the faster you can go. The better you can do this, the less kit you need (you do need some, but likely much less than you think).

To leave with a thought, just because it passes a functional test and the syntax parses does not mean it is fit for a live estate. The detail is important most of the time.