Split digiKam database queries between localhost (thumbnails) and network (metadata)

ProxySQL allows you to redirect a user's SQL queries to different SQL servers, by defining rules that match query patterns. In my digiKam usecase the rules are fairly simple, since all commands involving the thumbnails database digikam_thumbs should be redirected to a local MySQL server, and all calls to the digikam database redirected to the network server. Still, the setup is time-consuming, and the complexity of at least two databases in two MySQL instances, in addition to the ProxySQL instance, can lead to confusion. I'm using MariaDB from the respective Linux distribution both an the network server (Debian), and on the clients. A user for ProxySQL has to be created on all these databases, and given the correct access rights.

On the local server:

sudo su 
mysql CREATE USER 'dkproxy'@'localhost' IDENTIFIED BY 'yourPassword'; select User,Password from user;
GRANT ALL ON digikam_thumbs.* TO 'dkproxy'@'localhost'; # mysql -p -u root digikam_thumbs < digikam_thumbs.sql;

And on the network server :

# CREATE DATABASE digikam; (same for face and similarity)
# mysql -p -u root digikam < digikam.sql; (same for face and similarity)
CREATE USER 'dkproxy'@'%' IDENTIFIED BY 'yourPassword'; 
GRANT ALL ON digikam.* TO 'dkproxy'@'%';

On the local ProxySQL server:

sudo dpkg -i proxysql_2.0.10-ubuntu18_amd64.deb
mysql -u admin -p -h 127.0.0.1 -P6032 --prompt='Admin> '

Now create the user dkproxy, and the rules for redirecting queries :

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('dkproxy','yourPassword', 1);
SAVE MYSQL USERS TO DISK; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME;; LOAD MYSQL USERS TO RUNTIME;

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'127.0.0.1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,' ',3306);
SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME;

Digikam needs a socket for working with proxySQL


update global_variables set variable_value='0.0.0.0:6033;/run/proxysql/proxysql.sock' where variable_name='mysql-inte
rfaces';
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (10,1,'dkproxy','digikam_thumbs',0,1); INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (20,1,'dkproxy','digikam',1,1); INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (21,1,'dkproxy','digikam_face',1,1); INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (22,1,'dkproxy','digikam_similarity',1,1); SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME;

That should be all, if I did not forget anything. Again connecting to the ProxySQL admin interface you can easily see how well the rules are working:

Admin> 
SELECT hostgroup,srv_host,status,ConnFree,ConnOK,Queries,Bytes_data_sent,Bytes_data_recv,Latency_us FROM stats_mysql_connection_pool;
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host        | srv_port | status | ConnFree | ConnOK | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
| 0         | 127.0.0.1       | 3306     | ONLINE | 1        | 1      | 34767   | 2128098         | 595568549       | 0          |
| 1         | 192.168.1.180   | 3306     | ONLINE | 2        | 2      | 121759  | 3399218         | 5250558         | 380        |
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)

Digikam must be configured now. Hostport must be 6033. As DB connection options, addition

UNIX_SOCKET=/run/proxysql/proxysql.sock

From our local thumbnail host we got about 100 times as much data as from the general digiKam database, which is on the network. But most importantly: digiKam feels much faster again! Next if I have time I might look into why digiKam sent so many queries to the remote server after all, when I just clicked around and filtered for a few minutes! ;-) In my previous job I could reduce the number of SQL queries in a database application drastically by using more JOIN statements...