Friday, June 19, 2009

Dynamic Memory Allocation in MySQL

Did you know that MySQL can dynamically allocate memory for queries? I didn't till I found this web page...

The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.

What's the big deal about this? mysqld needs enough memory in order to transmit the entire result of a query. If a query or result requires more than max_allowed_packet bytes, mysqld is unable to serve up the results. While the table structure is built to support very large amounts of data, it's possible to design a table in such a way that mysqld won't be able to respond by asking it to send back too much data in a single row.

I was surprised to learn that even if you can concat data into a text field, if the size of a retrieved data set exceeds max_allowed_packet bytes, the query will fail! That sucks. I was certain I had filed a bug on this years ago, but wasn't able to find it. At any rate, has been filed recently. :-)