3

The Question

My question is are there any limitations on the amount of rows that are returned via query using either Mysql or Laravel?

Firstly Im using Laravel 5.2 and i am trying to retrieve my data from my logs table which has recently hit over 10k rows, Now since then (or around that time) the usual laravel syntax to retrieve the data isnt working

Syntax

$logs = Log::all()

return $logs; //This is then handled by Vue.js on the client side

It used to work fine now it doesnt, nothing is returned and no errors are displayed in the chrome devtools, just an empty string.

Ive only just recently been in the database and notice that information is still being added to the logs daily so my code is working still, But when i try to retrieve the information to show it on the client side it doesnt work.

What i think the problem is

Which has lead me to believe that Laravel or MySQL has some sort of limitations on the amount of rows that are retrieved? Im not sure how to check my query limitations and what not.

What i've done

I have already looked over stackoverflow for the answer but I've not found anything useful, I've come across someone saying that 9 million rows is alright as long as the table is correctly indexed etc.

I asked a question before but all the answers and suggestions were incorrect so hopefully this new found information can shed some light on the problem.

Kenziiee Flavius
  • 1,918
  • 4
  • 25
  • 57
  • No errors are displayed in the chrome devtool but how about elsewhere, like on the webserver logs? – apokryfos Jan 10 '17 at 14:58
  • 1
    Can you try to fetch the last 100 rows with `Log::orderBy('id', 'desc')->take(100)->get();` and see if that works? – Roberto Geuke Jan 10 '17 at 14:58
  • @apokryfos Ive checked laravel logs but im not sure where the webserver logs are – Kenziiee Flavius Jan 10 '17 at 15:00
  • @RobertoGeuke Bingo it works, so now how do i go about finding the max amount of information i can retrieve? It never occurred to me until now that there might be too much information being retrieved – Kenziiee Flavius Jan 10 '17 at 15:03
  • 1
    You're probably just hitting a memory limit: https://stackoverflow.com/questions/18593852/laravel-eloquent-orm-maximum-rows-it-could-retrieve – Serg Chernata Jan 10 '17 at 15:09
  • @KenziieeFlavius the limit can be on a lot of places. The client side, laravel can have a limit, your server can have a limit. We will need more details like an error or something before we can help you find out where the limit is coming from. You can increase your `take()` method with 1000 in steps and see when it fails. It will give the limit a number, maybe it will help. – Roberto Geuke Jan 10 '17 at 15:09
  • Ill do a few incremental tests and come back, no errors are being displayed on the client side or on laravels logs, which leaves the server logs which ill try find in a bit and get back to you guys – Kenziiee Flavius Jan 10 '17 at 15:11
  • @SergChernata It seems 1.1 mb is my limit anything above this number causes an error 500 with no information but now i know its a memory limitation. Thank you all for the help just got to find out how to increase said limit now – Kenziiee Flavius Jan 10 '17 at 15:20
  • 1
    there's a number of ways: https://stackoverflow.com/questions/11885191/how-to-increase-memory-limit-for-php-over-2gb – Serg Chernata Jan 10 '17 at 15:21
  • You probably need to check your server configuration, for apache2x in Linux the default location is `/var/log/apache2/error.log` but this can be overriden in the vhost configuration. – apokryfos Jan 10 '17 at 15:52

1 Answers1

1

The problem ended up being that my query Log::all(); was pulling back over 1.1mb of information while my server limit on queries was bang on 1.1mb. Two people helped me to identify and solve the problem.

@RobertoGeuke:

"Can you try to fetch the last 100 rows with Log::orderBy('id', 'desc')->take(100)->get(); and see if that works?

You can increase your take() method with 1000 in steps and see when it fails. It will give the limit a number, maybe it will help."

This helped me clarify that my query was requesting too much information from the database but i still wasn't sure if it was a row limitation or a memory limitation.

@SergChernata:

"You're probably just hitting a memory limit: Laravel Eloquent ORM maximum rows it could retrieve

[Increase Memory Limits?] there's a number of ways: How to increase memory limit for PHP over 2GB?"

This helped me pin point the problem and also gave me options to increase the memory limitation.

Kenziiee Flavius
  • 1,918
  • 4
  • 25
  • 57