Understanding memory usage of MySQL result in PHP (PDO)

Question

I am trying to understand why the memory usage for a single PDO result is so high. Here are a few things to know about the query/result:

• I am pulling a single VARCHAR(6) column from a single table
• The result is less than 30K rows
• This fetching this result uses ~12MB of memory in PHP (source: memory_get_usage)
• If I json_encode the result and dump it to a file, the actual data (in text form) is only ~1MB
• Using PHP7, MySQL 5.7, deployed on Ubuntu 14.04.

My question is, where exactly does the 11MB of bloat come in? If the actual data in text form is only about 1MB, then 11MB seems like a lot of overhead just to parse the data in PHP. Is there a reason for this? Am I missing something?

Edit:

Just to clarify, I am looking for a technical explanation as to why the bloat exists, not a workaround for the issue.

Answer 1

It's hard to give a specific answer without seeing your specific code. That being said, PHP data structures like arrays are associative. The PHP designers intentionally made a tradeoff to use extra RAM to save time on array access.

You can save memory in a couple of ways. For one thing, you can fetch each row of your result set as a numeric, rather than an associative array. Read this. http://php.net/manual/en/mysqli-result.fetch-array.php

For another thing, PHP slurps all the rows in your result set at once unless you tell it not to. This slurp operation consumes a lot of RAM. You don't need that if you're planning to process your large result set one row at a time. You need an unbuffered query to do that. Read this: http://php.net/manual/en/mysqlinfo.concepts.buffering.php