I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!
I’m dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.
Still hush hush, the development of that feature touched at the very same issues described in Michael’s post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a few insights of mine:
Internal array implementation
Like Michael, I started by implementing arrays through tables. That is, create a (temporary, in my case) table, wrap it up with a lot of stored routine code, and simulate an array. This array is not yet provided to the user, but is used internally for QueryScript’s own code.
Well, disappointment here: during load tests on intense structures, such as a foreach loop, where each iteration of the loop requires the creation of an array, I found that the current solution does not hold well on busy servers.
Seemingly, there’s nothing wrong with the creation of a new table every once in a while — and in particular a temporary table. However, I quickly found out that a busy server thrashes the table cache with such intense rate of creation/dropping of tables. The competition over the table cache mutex becomes intolerable and hogs not only the script’s execution but the entire server’s.
There’s also the issue of the type of array values — no going around using textual columns, of course, but — how long? A VARCHAR(32767) should be enough for any reasonable implementation, but — how much memory would that consume? Both MEMORY and standard temporary tables (Percona Server has that partially resolved) use a fixed row format, which means a 32K text is actually allocated in memory even when your value is ‘x’. Continue reading » “On stored routines and dynamic statements”