Re: [exim] Using MySQL Stored Procedures in Exim

On 2/3/07, Mike Cardwell <exim-users@???> wrote:
>
> When stored procedures didn't work for me, I used stored functions
> instead. Of course, with stored functions, you can only return a single
> element, but you can return stuff like:
>
> "foo1=bar1\nfoo2=bar2"
>
> Then pull data out of it using ${extract{}{}}
>
> Presumably, even with your hack in place, exim barfs if you return
> multiple result sets?
>
>
Yeah, I've got a mix of both functions and procedures, though most of my
uses have been to grab a bunch of fields (e.g. maildir path, quota, etc),
Though that's a pretty interesting idea to CONCAT(..) the results and use
${extract, since I'm already using ${extract anyway in just about every
case.

You're correct on the single result set, I haven't tried doing anything with
multiple result sets since I'm only ever looking for at most one result set
with one row. I'd imagine that it'd just get the first result set and ignore
the rest (though that's completely unsubstantiated).

One downside that I can report though is that when I rolled this out across
all 30+ servers, the CPU load on the mysql server shot through the roof.
Doing basically the exact same set of queries as just plain queries coming
from Exim would typically use up somewhere around 25-50% of the CPU between
"user" and "system" depending on load (dual proc 3ghz Xeons); doing those
queries in stored procedures caused the CPU to hit 100%. I verified this a
number of times. I had expected that the fact that the query was already
prepared on the server when using stored procedures would've made up for the
extra processing power required to use stored procedures, but apparently
that's not the case. Maybe a future version of MySQL will do a bit better.