How thread_stack variable relates to amount of UNION in a query?
To speed up updates, I use the UNION trick:
UPDATE shop_items t INNER JOIN ( SELECT 0 id, 0 title UNION ALL SELECT 1, 'text1' UNION ALL SELECT 2, 'text2' ... ) data ON data.id = t.id SET t.id = data.id, t.title = data.title It works fine for queries about 1 MB length, but after the size increase it starts to fail with error:
ERROR 1436 (HY000) at line 1: Thread stack overrun: 266736 bytes used of a 286720 byte stack, and 20000 bytes needed. Use ‘mysqld –thread_stack=#’ to specify a bigger stack.
I surely can "specify a bigger stack" and find appropriate value for each specific query length by pure guessing, but I find this approach inappropriate: in development, if you do something, but don’t know how it exactly works, you may end up poorly, with the code failing randomly in the future.
If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.
Which is not very helpful.
So, is there any direct and computable relation of thread_stack to complexity of queries with unions? Perhaps, query length or UNION’s amount? If I know the rules, I can limit queries complexity appropriately.
It’s very likely that each SELECT in your UNION is spawning a thread. That said, this isn’t a detail MySQL exposes in a legible way, and I wouldn’t recommend designing software based on trying to map an internal implementation detail that may change without warning in future versions. If you’re determined to use this "trick", I’d say limit how complex a query you generate with it to well below where you start seeing problems.
Answer provided by chaos was correct: queries are limited by amount of SELECTs/UNIONs, not total characters count. I tried to delete all texts from my query, reducing it from 1 MB to 100 KB, but MySQL still refused to accept it. But as soon as I reduced the lines count to the limit we are trying to find – the query started working.
And here is complete answer I deduced from observations, experiments and a bit of mathematical shamanism.
Default MySQL value for thread_stack is 286 720 bytes, which stands for 262 KiB.
With this value I can create query with 4076 SELECTs: one base SELECT + 4075 UNIONs.
4076 is almost 4096, lacking 20. That means someone stole a number equaling 20 SELECTs.
286 720 (262 KiB) is almost 262 144 (256 KiB). Looks like MySQL wants that 24 576 (6 KiB) for its shady purposes.
Let’s divide one almost for another almost: 262 144 / 4096 = 64. Seems like one SELECT requires 64 bytes of memory.
Then we can invent a formula:
(thread_stack - shady MySQL hoard - stolen 20 SELECTs) / bytes per SELECT = SELECTs available to us
In case with default 286 720 bytes:
(286 720 - 24 576 - 1 280) / 64 = 4076
Then I checked it with setting thread_stack=131072 in the config file. That is 128 KiB.
Here is the math:
(131 072 - 24 576 - 1 280) / 64 = 1644
I edited my query to contain 1644 SELECTs. It ran without any problems.
Then added one more little UNION + SELECT and immediately got:
ERROR 1436 (HY000) at line 1: Thread stack overrun: 111088 bytes used of a 131072 byte stack, and 20000 bytes needed. Use ‘mysqld –thread_stack=#’ to specify a bigger stack.
Seems like, for now, the formula is working right. But will it work in the future – probably, but it’s no more than a speculation.
