The web panel slowness is from PHP. sqlite3 is very performance oriented.
Thank you for the Feature Request, we'll leave it open for user comment.
The web panel slowness is from PHP. sqlite3 is very performance oriented.
Thank you for the Feature Request, we'll leave it open for user comment.
Thank you for your help.
I have the same request. We have around 5k user and using SQL as database may help to have the better perf.
It will come one day
But also this, mysqld
/mariadb
stores databases in files and on top of that runs a network daemon that can speak SQL.
sqlite3
doesnt run any network parts and interacts with the database files directly so less overhead and complexity.
Would your organization be interested in sponsoring the Feature Request?
SQLite still seems to be the best option IMO.
Concerning performance, see
among other (similar) links found through Google. I'd yet have to convinced that there can at all be a noticeable difference in performance, otherwise, even attempting to switch wouldn't make much sense.
This is partially also because we wrote our own extensions for SQLite giving us maximum performance with minimal need for data post-processing. For instance, we invented the subnet_match()
subroutine which can be used to compute subnet-aware matches, e.g.,
SELECT subnet_match('192.168.0.0/16','192.168.1.5');
returning
16
signaling that the addresses match in (at least) 16 bits.
We use this in queries like
SELECT count(id) matching_count,
max(id) chosen_match_id,
ip chosen_match_text,
group_concat(id) matching_ids,
subnet_match(ip,?) matching_bits
FROM client
WHERE matching_bits > 0
GROUP BY matching_bits
ORDER BY matching_bits DESC
LIMIT 1;
I doubt MySQL has this.
I am not db expert, but can understand why sqllite better than MySQL in speed.
I think the bottleneck is from PHP to SQLite not good.
The advantage I see in MySQL, PgSQL is not performance but but high availability.
In corporate settings this might be a required feature not otherwise needed for home installs.
I am using 2 pihole with about >1500 client. The statistics portal is now useless with no data because admin page can not load. I increase php memory limit but no help. Do you think that MySQL can resolve the issue?
No, when Pi-hole works as you expect it to, then this is not a database issue.
No page is loading at all? Most pages are directly talking to the resolver and are not using the database at all, so an issue here will not be resolved by switching to a different database backend.
Not really at all.
The dashboard is working after take long time to response but the page related with queries were failed
for example: queries.php?forwarddest=blocklist
What are you running Pi-hole on? How much RAM? How much memory is allocated to PHP? What is the webserver?
Those are all things to fix before even getting to the database.
Hello
Just installed few minutes ago and i'm looking if it possible to use a deported database
I am interested too. 2 reason
I have a NAS with a mariadb, and it make sense to use it (for me)
And on the top of the interest, is to save my SD card.
The gravity database is only written to once a week (or on manual interactivity, e.g., when you edit domains, of course). I don't think there will be much to save here.
Does mariadb offer subnet matching capabilities like described here?
Hi Dan,
I am using CentOS Linux release 7.8.2003 (Core), with 8GB of RAM, PHP 7.2.31 (memory_limit = 512M). Any advise.
Thx.
Memory limit is way too low.
Would below be a good indicator ?
pi@ph5:~ $ php-cgi -q <<< $'<?php echo memory_get_peak_usage(1) . "\n" ?>'
2097152
Bit low but its home use
pi@ph5:~ $ php-cgi -i | grep memory_limit
<tr><td class="e">memory_limit</td><td class="v">128M</td><td class="v">128M</td></tr>
EDIT: Thinking, above only shows mem usage for that one command line session probably.
Need to do the echo memory_get_peak_usage(1)
on one of your web pages.
And there seems to be a couple of them running:
pi@ph5:~ $ ps -o pid,vsize,cmd -C php-cgi
PID VSZ CMD
10273 191004 /usr/bin/php-cgi
10290 191488 /usr/bin/php-cgi
10291 191336 /usr/bin/php-cgi
10292 191492 /usr/bin/php-cgi
10293 191336 /usr/bin/php-cgi
EDIT2:
Is same it appears:
pi@ph5:~ $ curl -s http://localhost/admin/ | head -1
2097152
EDIT3: Sorry for straying off topic a bit
Well,
tell me what I am missing but the dashboard does not do any heavy lifting? Only the Query Log does. So why is the dashboard slow? It should always be very fast. AFAIK the databse is never involved on the dashboard guys?
May be a language issue. When someone says dashboard I take that to mean the entire admin interface, not just the index page.