Comment by hinkley | Hacker News Reader

Comment by hinkley | Hacker News Reader

Parent Full thread

Long ago, we brought in a consultant to tell us why we couldn't max out our very fancy Oracle 9i database hardware. Nothing we did could get us much above 50% saturation on the disk array, CPU, or networking. We were stumped.

I don't know where Oracle is on this issue these days, but in that version there was a query cache for solved queries. And for a query to execute, it had to be in that cache. That dictated how many queries could be in-flight at once. Or rather, how many unique queries. If you used Prepared Statements, it would be happy to run as many configurations in parallel as you wanted. We were using Prepared Statements, so that shouldn't have been a problem. Right? ... Right?

And then he showed me the log of queries.

Less than 2 years earlier (oh, you can already tell this is going somewhere, can't you), a newer team mate was trying to scrub all of the query string arithmetic from our code. Someone had settled on some sort of Builder pattern, but this guy was getting stuck. The guy who had instigated this had to tag out for reasons that I cannot recall, so he asked me to sub in. I got a little briefing, then we went through the problems and I sorted him out, and then I very carefully explained to him what we expected of this code.

If you're gonna bind variables, bind all of them. Solving part of a combinatorics problem isn't solving it, solving part of a SQL injection problem isn't solving it. Bind. Every. Variable. Got it? Got it. You good? I'm good.

And then, dear friends, I made a fateful mistake: I went back to my other tasks and fires, and the other engineer oversaw the rest of the work and the code review.

18+ months later, I'm staring at a bunch of nearly identical queries in the Oracle query cache that have half a dozen bind variables and then a couple of distinct AND clause near the end. Plot twist: he was now my boss. The answer to 'why is our fancy hardware slow?' was, "because of you, Mike, because of you." Fuck a duck.

I guess maybe the reason he was so awful at giving orders is that he never learned how to follow them.

I'm not sure i follow the punchline. If the queries need different where clauses then they aren't interchangeable. Or are you saying they were semanticly the same and only syntacticly different?

My read of GP's story is that the prepared queries varied by supplying different hard-coded parameters, in addition to some bound variables. For example, the WHERE clause might be "WHERE first=? AND last='Smith'". The 'first' argument is a bound variable, while the 'last' argument is part of the prepared query string.

dantillberg has the right of it.

We were doing some complicated fitness tests on our data to return appropriate rows to the user.

Actual:

SELECT * FROM things WHERE ... AND columnA>? AND columnB<5;

Expected:

SELECT * FROM things WHERE ... AND columnA>? AND columnB<?;

These sorts of mistakes tend to sneak back into projects that previously had this handled, so you do have to watch for regressions. (But that's not what happened in this case, if anyone is wondering.)

I don't know if there are any good ways to audit for these sorts of problems these days. At one point I joked that I was going to write my own database library that only accepted Prepared Statements and nothing else, but barring a full SQL query parser, I'm not entirely sure how I thought I was going to prevent someone from doing that.

You really want this to be an SQL mode that the server enforces. Give me a query where anything that could be a parameter wasn't and I'll fail the query, write a nasty log, and maybe disconnect your client.

I think as an opinionated library, you could build a minimal parser, and fail queries that had values or didn't meet your parsing. I'm not super familiar with SQL these days, but I'd guess you could make a reasonable subset and have confidence that you would have false positives, but no false negatives.

Oracle, at least, has/had the problem that it can't use the values of parameters to affect the execution plan. So sometimes it's good to write in the values in the SQL statement.

If you write "select .. where account_id=?" then the query plan will probably be the same for any account_id, e.g. use the index, so that's fine, working as intended.

However, if you write "... where active=?" and active can only be true or false and there are a few thousand active entries but tens of millions of inactive ones, the query plan might be quite different for active=true and active=false. (Active=true would use an index, but active=false would make more sense to just sequentially read the whole table, as most rows will be used anyway.)

So if you have very few distinct values, the selection of the value influences the execution plan, and the value doesn't come from an untrusted source, it can be better to not use query parameters.

That is to say, 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately.

> 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately

This is one of the problems that I have with most SQL databases: they try to do the magic for you and make it hard to see what's going on under the hood. Databases aren't magic, they are just indexes, and there are only so many ways of organizing them. Providing a bit more transparency into how the data is stored and retrieved allow you to write efficient queries without the guesswork.

That's not entirely correct. A hard parse causes the optimizer to do bind variable peeking, i.e. it chooses the execution plan based on the bind variable value.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/optim...

From the same source:

When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.

In that case, would you not use a partial index, and kill two birds with one stone?

On that same project we got into putting hints into the queries. I’m sure if push came to shove you could do that for situations like this one and solve the problem. Less portable that way, of course.

The other way to fight things like this that however you want your users to behave should be the most straightforward way to use the code. If you library provides shortcuts to bad behavior, people will take them.

I was just rereading you comments and started searching for SQL linters and thinking how hard that would be in practice due to all the ways the queries get muddled in imperative code and a thought came to mind:

One of the ways we reduce people’s reliance on string concatenation is by providing interpolation. And I think that at least in some languages you can substitute your own logic for interpolation. You might be able to encourage bind variables that way.

    SELECT * FROM users 
        WHERE last_name=${surname} AND age>${legalDrinkingAge(state)} 
        ORDER BY birthdate ASC

This is mostly how entity framework behaves. Last I checked they had some sort of nonsense checking whether a variable was in _any_ closure before deciding whether to bind it or substitute it, but give or take the rough edges it will bind variables provided in parameterized strings.

> Nothing we did could get us much above 50% saturation on the disk array, CPU, or networking.

What you wrote basically makes no sense unless you are doing continuous table scans (ask any DBA and see how puzzled they look.)

Production MySQL and Postgres master databases that I administer typically have uptime loads of 0.1 to 0.2 on Linux, processing 5,000 qps.

Regarding placeholders and bind parameters (those are different things), how they work depends mostly on your client library. For example, some libraries do rewrite placeholders with string pasting, so verify that. YMMV.

Source: I am a DBA.

> Production MySQL and Postgres master databases that I administer typically have uptime loads of 0.1 to 0.2 on Linux, processing 5,000 qps.

I have worked with plenty production postgres instances that have much much higher loads. As in > 100 (on larger machines obviously). It's pretty easy to have workloads where the hot data set fits mostly into RAM - and if that's the case the read portion of that workload is usually bound by context switches and CPU processing time of the queries.

Far from every workload has enough queries to utilize a machine with plenty cores, but there's also a lot where that's trivial.

If you are getting a lower qps out of a machine than you expect and none of the hardware subsystems are saturated then something is wrong, and it’s not table scans. A table scan would be maxing iops for disk or memory or both, right?

When was 9i released? Are you qualified to compare 15 year old hardware running a 15 year old database on a 15 year old OS to what you have going on now?

(Hell, I’m not even sure it was Intel hardware. Odds are reasonably good that was on an Ultrasparc, since that’s what our customers were running)

Reply on news.ycombinator.com