PHP massive memory usage for SQL query

Question

I've stumbled across an odd issue while optimising my Apache + PHP memory usage. Basically the code blows up with the error message "Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 50331646 bytes)" when trying to bind the results of a MySQLi query.

The relevant tables are:

CREATE TABLE `note` (
  `noteID` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` int(11) NOT NULL,
  `text` mediumtext NOT NULL,
  PRIMARY KEY (`noteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content` (
  `contentID` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(2048) NOT NULL,
  `datestamp` datetime NOT NULL,
  PRIMARY KEY (`contentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the query that blows up is:

select content.contentID, 
content.text, 
content.datestamp, 
note.noteID, 
note.contentID, note.text 
from basereality.content as content 
inner join basereality.note as note 
on content.contentID = note.contentID 
where content.contentID = 1028 ;

Running the query in MySQL on the server runs fine, and the size of 'note' returned is under a kilobyte.

One weird thing is that the size that it is trying to allocate 50331646 is 0x2FFFFFE in hex which is a suspiciously round number. It's almost like PHP is trying to allocate a buffer large enough to hold the largest mediumtext field possible, rather than actually allocating memory for the data retrieved.

Anyone know a workaround for this - other than to increase the max memory allowed in PHP?

btw Other people had the same issue but didn't seem to know how to resolve it. Out of memory (allocated 50855936) (tried to allocate 50331646 bytes)

The exact line that blows up is:

$statement->bind_result(
  $content_contentID,
  $content_text,
  $content_datestamp,
  $note_noteID,
  $note_contentID,
  $note_text);

Changing the query to select "'A test string' as note", rather than fetching the column no longer shows the massive memory usage.

btw I am sure that I'm not trying to retrieve large data. This shows the actual length of the mediumtext field in the note table:

 select length(text) from basereality.note;
+--------------+
| length(text) |
+--------------+
|          938 |
|          141 |
|         1116 |
|          431 |
|          334 |
+--------------+

Answer 1

I found the answer and commented in another question:

Allowed memory size of 67108864 bytes exhausted

Basically, in old MySQL connector library, rather than just allocating the actual size of the field 'text' to store the result, instead the maximum possible size of the type mediumtext is allocated.

i.e. 16 megabytes is always allocated even if the field for the result is only 100 kilobytes.

The easiest and probably best way to fix this is to switch to using the new MySQLND connector which doesn't show this behaviour.