4

I use Laravel 8 to perform a query on a MySQL 8 table using the query builder directly to avoid Eloquent overhead but I'm getting a lot of memory consumption anyway.
To show you an example, I perform the following query to select exactly 300 000 elements.

My code looks like this:

$before = memory_get_usage();
$q_coords = DB::table('coords')->selectRaw('alt, lat, lng, id')
    ->where('active', 1)->take(300000)->get();

$after = memory_get_usage();
echo ($after - $before);

It displays 169760384 which means something like 169MB if I'm not mistaking..
Looks like a lot to me because in my query I only asked for 2 float and 2 bigInt, which represents something like 4 x 8 bytes (32 bytes).
And.. 32 x 300 000 records ~= 9600000 (almost 10MB).

How is that even possible that it uses so much memory? I am very surprised.

EDIT

I also tried using PDO directly, same result.

$query = DB::connection()->getPdo()->query("select alt, lat, lng, id from coords WHERE active = 1 LIMIT 300000");
$q_coords = $query->fetchAll();
Marc
  • 1,350
  • 2
  • 11
  • 29
  • What is your question exactly ? cause 300 000 entries is not a normal behavior for any code. + in php each variable is contructed as a zend object + each result from Eloquent is converted to a PHP object... – N69S Oct 12 '20 at 14:25
  • @N69S I'm not using `Eloquent` to avoid the overhead due to `Models`, it's using `stdClass`. I get 300k entries to perform some calculation later, but here I'm just trying to understand why it does use that much memory for personal comprehension when I was expecting it to use less (because I know I can solve it using `chunks` or `cursors` anyway). It just seems a lot of stuff "around" to handle records of 32 bytes.. – Marc Oct 12 '20 at 14:36
  • If you really need to access 300.000 records in a single query, so try using pdo directly and using `PDO::FETCH_NUM`. – Elias Soares Oct 12 '20 at 15:00
  • How about `get()->toArray()`? – nice_dev Oct 12 '20 at 15:06
  • @Marc so this `->where('active', 1)->take(300000)->get()` is not eloquent ? – N69S Oct 12 '20 at 17:32
  • @N69S It could, but it's also part of the Query Builder which is my case. – Marc Oct 12 '20 at 18:56

3 Answers3

2

Because thet are represented as PHP objects in memory and not just as their raw data usage. However there is a solution to limit the memory usage: chunk

https://blackdeerdev.com/laravel-chunk-vs-cursor/

Chunk: It will “paginate” your query, this way you use less memory.

online Thomas
  • 8,864
  • 6
  • 44
  • 85
1

Since Laravel Query Builder uses stdObj to represent it results, you will have a lot of overhead:

Each object will store the value of the row itself, and the names of each column. So your 32 bytes turns into a lot of bytes.

Elias Soares
  • 9,884
  • 4
  • 29
  • 59
1

In PHP each variable is handled with a specific data structure to allow dynamic typing, garbage collection and more..
You can see here a (pretty old but still ok) article: link

You can also see that arrays have a more specific processing, because it need a bucket, for example to store array keys which are considered as Strings.

All of that means there is (according to the article) approximately 144 bytes of data used to store an element of an array.
Well, while I can't explain EXACTLY your result, I can still tell you that in your case have something like this:

300 000 * 144 * 4 = 172 800 000

Which means 300000 rows of 4 variables with 144 bytes by variable.

As you can see it's not that far away from what you got even if my maths are not taking into account the improvement done in PHP 7 and other factors...

David
  • 131
  • 4