Seeing those actual numbers makes it quite clear why the device runs out of memory...
... maybe I should just restrain myself from executing huge queries.
You are if it's true
I think number one is sufficient, number two might be not worth the effort just for error handling.
I thought about an (additional) third possibility: warning the user if a huge number of queries has to be processed. This could be done fast by counting the number of queries between the selected timestamps. As the database has an index on timestamp you could easily subtract the corresponding ROWIDs. After selecting the time range this calculation could be done and a dialog presented to the user ("Your selection will retrieve >100.000 queries. There is a risk that your device runs out of memory. Do you really want to proceed?")
This would need a ping-pong approach where we do extra work to send something to the user. Also the limit is fairly arbitrary. 100,000 may already be too much for a 128 MB SoC.
Oh I absolutely agree
I was trying to think from a normal-user perspective though: seeing their browser (or at least tab) completely stuck for 30+ seconds is somewhat unpleasant.
As you said, though, it's unavoidable.
FWIW I've been running 5.x for a couple months now (due to an unrelated problem) and I'm very happy with it. If push comes to shove I can definitely either use SQL or push everything in my Elasticsearch instance and do analysis there.
That's great to hear! I didn't think this was going to end up in 5.x, but the fact this is already implemented in 6.x is awesome. Keep up the great work! When 6.x is stable-ish, even if alpha, I'll definitely help testing!
I guess I figured out one thing my constantly-overheating RPi 4 is good for..?
I understand this is not an issue that would have been resolved with V5.0 (non Beta) - as I did encounter the same "An unknown error occurred while loading the data" message as well when trying to fetch a query log even just for the current month (So from May 1st till 13th with ~ 25k of queries per day).
The /var/log/lighttpd/error.log also shows
2020-05-13 22:31:03: (mod_fastcgi.c.421) FastCGI-stderr: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in /var/www/html/admin/api_db.php on line 112
or
2020-05-13 22:58:16: (mod_fastcgi.c.421) FastCGI-stderr: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 14684160 bytes) in /var/www/html/admin/api_db.php on line 422
Pi-Hole V5.0 is running here on a Raspi 4 with 2 GB Ram - nothing else on it.
Are there any recommendations for this memory_limit value in /etc/php/7.3/cgi/php.ini for this particular case or would you have any other advice on how to proceed with this?
This issue cannot be solved easily. It will be solved with Pi-hole v6.0 which is targeting server-side pagination to reduce the workload on the PHP-engine. Currently, you can set the memory limit to whatever seems appropriate for your individual machine.
For instance:
You have 2 GB of memory
Typically, only 200 MB are used by the system and running processes
2 GB - 200 MB * 75% safety margin = 1350 MB as upper limit for PHP
