I use Pihole for DNS servers throughout the company to prevent employees from seeing ads and some websites. However, due to the large number of visits, SQLite performance used by FTL is seriously insufficient. So how to use MySQL as a database?
Can you provide some more detail on why you find it seriously insufficient? What is slow in the process, where are you seeing a need for improvement?
Thank you for your help.When I open the web panel of Pihole, his response is a little slow, and the statistics are always displayed after the page is loaded first and after a few seconds.I think this is SQLite's function is simple and its performance is low. He can be used in my home. But in the company, I think Pihole should support other types of databases so as to reduce the reading and writing of local disks and improve performance in case of large access.
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?
2 Likes
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.
1 Like
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.
1 Like
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.
1 Like
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