HowTo tune MariaDB for Raspberry PI 3, pi-hole project and a little of dnsmasq

April 12, 2017

Hi y’all!

Days ago I was playing with the pi-hole project to avoid ads without using adblock or other plugins, this way you can read your favorites websites without the annoying popup about how they are supported by ads (thanks SecurityWeekly!.

I have a very extensive dnsmasq DHCP configuration including tags for different MAC addresses, this tags are use to push different DNS servers to certain devices:

dhcp-option=tag:iptv,option:dns-server,172.19.24.3,172.19.23.3
dhcp-host=10:10:b1:09:78:4c,iptv,set:iptv,10.0.0.8

The pi.hole project worked like a charm and was a matter of copy my custom dnsmasq configuration files to the /etc/dnsmasq.d folder.

PiHole

Encoruraged by that success, and because the Pi was the one I was using for the ownCloud Pi Drive I tought that will be cool to take advantage of that cool WD Labs Green Caviar (2TB 2.5” internal HDD with USB 3.0 interface) and put it to the work again.

The setup should need to be light due to the RAM limitations on the PI so I started with lighttpd and sqlite3. Lighttpd was being used by the pi.hole project so, why no reuse it?

Adding the following snippet to the lighttpd.conf was sufficient to setup a new vhost on the current lighttpd installation:

$SERVER["socket"] == "nextcloud.gnuardo.com:443" {
    server.document-root = "/var/www/nextcloud/htdocs"
    server.errorlog = "/var/log/lighttpd/nextcloud_error.log"
    accesslog.filename = "/var/log/lighttpd/nextcloud_access.log"
    ssl.engine = "enable"
    ssl.pemfile = "/etc/lighttpd/nextcloud.gnuardo.com.pem"
    ssl.ca-file = "/etc/lighttpd/letsencrypt_ca.crt"
    server.name = "nextcloud.gnuardo.com"
    setenv.add-response-header = (
        "Strict-Transport-Security" => "max-age=15552000; includeSubdomains"
    )
}

However the performance using sqlite3 was terrible, the service was unusable so I was forced to switch to MariaDB. The MariaDB default setup in raspbian was to much for my Pi3 that already has the pi.hole project, stunnel, fail2ban and an asterisk server. Memory was quickly consumed as so was the swap file.

Time to tuneup the /etc/mysql/my.cnf file:

# The MySQL server
[mysqld]
# Network and sockets
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysql.pid

# Logging
log_error                      = /var/log/mysql/error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/slow.log
general_log_file               = /var/log/mysql/general.log

# Tunning
wait_timeout                   = 20
max_connections                = 64
key_buffer_size                = 32M
query_cache_size               = 32M
tmp_table_size                 = 32M
innodb_buffer_pool_size        = 8M

server-id       = 1
myisam_recover  = BACKUP

The MySQL memory calculator website was handy to get a setup that worked for me. The crucial variables were:

wait_timeout: Because is important to recover the memory after we served the request.
max_connections: Only a few clients will use this server. Why waste memory in open and unused file descriptors and structures?
innodb_buffer_pool_size: We’re using only one app with several tables, another possibility is to reduce the pool_chunk_size instead.
key_buffer_size: If you make the value too large, your system might start to page and become extremely slow (that’s exactly what was happening).

After those changed were committed the RAM consumption of all the services in this RPi were stable at less than 512MB, giving me room for future projects in this box, we didn’t incur in any big performance penalty, still I need to make some serious benchmarks.

Your mileage can vary radically from mine.

Profit!