CODEWITHSUNDEEP
phpmysqlarraysmemorypdo
Kirk Logan
Kirk Logan

Reputation: 783

Understanding memory usage of MySQL result in PHP (PDO)

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:

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.

Upvotes: 8

Views: 2356

Answers (2)

Kirk Logan
Kirk Logan

Reputation: 783

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.

Upvotes: 3

O. Jones
O. Jones

Reputation: 108796

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

Upvotes: 3

Related Questions