I'm running procedures on a MySQL server version 5.0.51a. According to
the MySQL docs, the max_sp_recursion_depth server variable can be set
between 0 and 255. However, the real maximum value depends also on the
value of the thread_stack server variable which can only be set at the
MySQL daemon startup time.

For some reason, our DBA has the value of thread_stack set for the
minimum allowed for this version of MySQL which is running on our
school's Linux Debian server (131,072 instead of the default 192KB). The
procedures I plan to implement would need a recursion depth of about 40,
max. 50.

Is there any way of calculating in advance what the minimum value of
thread_stack should be? I haven't seen anything yet that would point me
in the right direction, and the MySQL documentation only states that
increasing the value of thread_stack might be necessary here -- but not
by how much.

And are there any negative side effects to setting this variable to,
say, 256KB instead of the default 192KB?

Robert Hairgrove <nobody (AT) hogwash (DOT) com> wrote:
This is a bad idea already. Under unlucky circumstances this can lead
to a MySQL crash due to stack overflow.

Why? Can't you rewrite the code to *not* use recursion? Recursive
code often looks more elegant, but is quite as often less efficient
than a nonrecursive solution.

No. But you can try. Just write a recursive procedure and try up to
which recursion depth it works (prepare to be killed by the DBA)

The reason why this cannot be predicted is that it depends on the
libraries linked in and the configuration. One example when this
went wrong (and the reason why the default was increased from 128K
to 192K) is the DNS resolver. If host name lookups are turned on,
then the DNS resolver from glibc will be called. And this uses
recursion too. Some time ago the libc code was changed in a way that
required more stack space and then there was all kinds of reports
about crashes. The result was the increased stack space default.

I should also mention that it is good practise to turn *off* host
name lookups for a production system.

On 32-bit systems this will result in fewer possible max_connections
because there will be more address space allocated. Note that this
is *not* running out of memory, but running out of address space.
On 64-bit systems this is a non-issue. The lazy memory allocation
strategy of almost all operating systems will "conceal" the extra
stack space allocation when it is not used.


XL

Thanks for your feedback, Axel...

On 09/11/2011 11:48 PM, Axel Schwenke wrote:
I think they used an old my.cnf file after updating the server OS ...
the system is actually 64 bit, not 32 bit as I had initially assumed
(version_compile_machine = x86_64). Of course, the default value for
thread_stack on 64 bit systems is 256K anyway, so I convinced the DBA to
change it to this value. Neither of us knew exactly why the minimum
value was implemented.

I'm aware that many times, recursive procedures and functions can be
re-written to use iteration (always so in the case of tail recursion). I
am still looking into this as a possibility. However, I had already
implemented this scheduling algorithm once back in 1997, which is a kind
of back-tracking algorithm, in 80x86 assembler using recursion. It has
served me well since then, so I am a bit reluctant to change it.

Since recursion is offered as one of many programming paradigms here by
MySQL, I don't see any reason *not* to use it -- aside from the fact
that many people don't know how to use it properly, but I don't count
myself as belonging to that camp.

I think I shall "try this at home" first... If my code works on my
32-bit system with thread_stack set to the default value, it should work
OK on the 64 bit server with the default value set for a 64 bit system.
As a secondary school network, we don't get too many hits anyway, so I
think I can risk it.

Good to know ... thanks!