Brian "Krow" Aker's Idle Thoughts - Prepared Statements, Musings

In the back of my mind for the last couple of weeks have been some musings on prepared statements.

So I walk into this customers office and they don't speak english.

I only speak a few words of their language.

It goes like this:

"Blah, blah, blah, Prepared Statements, blah, blah..."

I say "Turn them off, they are what is crashing your application."

The end customer is confused because the translator had not finished before I gave the answer.

So I explain the problem... the customer has a lot of connections to the database... a lot... and the database is running out of memory and crashing. Was this the customer's problem? Yes it was.

This is a practiced conversation for me. I could do a song and dance on "well you should be building your application like..." but the customer wants to build their application their way. They want a lot of SQL to exist that they are going to generate on the fly. The shortest answer is "turn them off".

So why are prepared statements a problem?

Because users do not clean up/close unused prepared statements. Multiply the number of prepared statements times the number of open connections and you begin to see the problem.

What do you do about this?

Turn them off. The Java driver and a few other drivers turn them off automatically.

Why?

Because unless you really happen to know what you are doing with them, you are going to crash the database (somewhere in the back of my mind is "this is why we moved to garbage collected languages").

I'm not going to defend the design.

Are they faster?

Well "yes" if you have blobs, otherwise... still "yes" for most cases.

Each connection thread in MySQL keeps its own copy. There is no global pool of prepared statements on the server.

And if there was a global pool?

We would need a bunch of read/write locks. Locks are a real problem right now across the industry. I do not believe that any application being built today which has to share data/state has this solved.

Locks harm scaleability. Sure... you could keep a cache of them local to the connection that reads from a central cache, but this would just be pushing out the problem with caching (which is the curren trend in scaling work arounds). The problem is that we see MySQL set ups with thousands of connections, so even this solution is going to chew up a lot of memory.

So what am I thinking?

Ditch the current design completely. Push the parser out to the client, and let it send out a stripped down bytecode language which can be de-serialized back to something we can execute on.

Just skip the entire "lets put this in the server" approach for prepared statements. No cache needed, and you push processing off to the client where it belongs. Will it require a new extension to the protocol? Yes.

The client library would also get a bit bigger, but that does not concern me as much. You can pull sql_yacc.yy and a few functions into a library and get away from the problem of supporting two parsers.

To someone writing against the database there would be no change, dynamic SQL would continue to work as is. The difference is all in what the client is doing internally, and what it is shipping over the wire.

Heck, its even modular at this point :)

Just thinking about a solution...