8

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.

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your response. I could show my code, but its nothing more than a generic PDO query execution with a fetchAll. I'm sure being associative jacks the memory up a bit, but I cant imagine it would account for 11MB of data. I have used unbuffered queries in the past and that does solve the issue, it just does not answer the question as to where the extra 11M of bloat comes from.
PHP is open source. You can look at how it handles arrays if you're curious. I suspect it's using hash tables to maintain your large arrays, and it's probably got them allocated sparsely enough to avoid too many hash collisions. Even numeric arrays in PHP are associative. Keep in mind PHP is designed for web page rendering, so the large-array cases aren't at the center of its target uses. Java and C# have more efficient collection classes. github.com/php/php-src
3

Ok, so thanks to Ollie Jones's answer, I realized I've been looking in the wrong place for my information. This isn't a PDO memory usage issue but an issue with the way PHP stores its arrays. (Maybe issue isnt the right word, it is what it is)

After a bit of digging I found this incredibly helpful article which gives a great breakdown of how PHP allocates memory for array elements:

https://nikic.github.io/2011/12/12/How-big-are-PHP-arrays-really-Hint-BIG.html

Spoiler alert, it uses a TON of memory for each element. Apparently it has gotten a lot better in PHP7. The article states that for a simple integer array (in PHP5) element it will use about 18 times the more memory than the size of the integer itself. Since i'm seeing about a 12* increase on associative string data, id say that is indeed a vast improvement over PHP5.

According to the article, memory is being allocated for the following:

  • zvalue_value union, which relates to the weak typecasting that PHP allows
  • Type storage and garbage collection data
  • The Zend Memory Manager allocation
  • Hash table buckets

If you had some interest in this as well, I highly recommend reading that article, its a quick read and has a lot of great information.

Thanks again to Ollie Jones for pointing me in the right direction.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.