Help using Redis or MariaDB for sa-learn

[robertpopa22]

I need help to setup Redis or MariaDB on my MiaB server.

At this moment the server is slow when receiving or moving emails from inbox to spam and back.

I imagine that a db engine would speed things up, since it allows parallel executions.

Did anyone tried this? Can anyone offer some experience?

[stylnchris]

I mean no disrespect whatsoever, but this is out of the scope for this project. I’m not sure you will get any help from anyone here with regards to this topic.

Honestly I don’t have any issues with server speed, I’m hosting 26 domains with quite a few users in each domain without any real issues. Have you looked at CPU and Memory on your server?

Also if you find that you really just “have to have a DB backend for mail” consider switching to https://www.iredmail.org/ or another project.

[KiekerJan]

My advice is first to think about why a database would be faster. You could of course install a database, integrate it somehow with Mail in a Box (I don’t know how, sorry) and then discover the performance is not helped at all.
When you move a mail to another folder in dovecot there are also network, webserver and file system components involved. Those also influence performance. So ideally, you would first determine where any slowdown occurs.

[robertpopa22]

Solved it

Main reason to switch to this setup is to have a multi threaded approach to spam processing. sa-learn only uses one thread in a file db setup (the default of MaiB). Therefore, when 10000+ emails are processed it becomes impractical hence the move to a relational db engine. The complete change takes about 15-30 minutes.

I used the guide from Migrating spamassassin from db files to MySQL - Untimely meditations

Please find the steps adjusted for MaiB

1. Backup rules
sudo sa-learn --sync
sudo sa-learn --backup > /home/root_user/sa-learn.backup

2. Install mysql
(from guide How to Install MySQL on Ubuntu 22.04 | phoenixNAP KB)

sudo apt update
sudo apt upgrade
sudo apt install mysql-server
mysqld --version
sudo mysql_secure_installation
(please check guide for details here)
sudo systemctl status mysql

3. Configure database

sudo mysql -u root

mysql> create schema spamassassin;
Query OK, 1 row affected (0.01 sec)

mysql> create user ‘spamassassin’@‘localhost’ identified by ‘Password’;
Query OK, 0 rows affected (0.03 sec)

mysql> grant select,insert,update,delete on spamassassin.* to ‘spamassassin’@‘localhost’;
Query OK, 0 rows affected (0.01 sec)

mysql>exit

4. Configure tables in database
sudo mysql -u root -p spamassassin < /usr/share/doc/spamassassin/sql/bayes_mysql.sql

4b. Verify the tables (just as a precaution)
sudo mysql -u root spamassassin
mysql> show tables;

+------------------------+
| Tables_in_spamassassin |
+------------------------+
| bayes_expire           |
| bayes_global_vars      |
| bayes_seen             |
| bayes_token            |
| bayes_vars             |
+------------------------+
5 rows in set (0.01 sec)

mysql> exit

5. Activate new setup in /etc/spamassassin/local.cf
Comment these
#bayes_path /home/user-data/mail/spamassassin/bayes
#bayes_file_mode 0666

Enter these lines
bayes_auto_expire 0
bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn DBI:mysql:spamassassin:localhost
bayes_sql_username spamassassin
bayes_sql_password password

Restart service
sudo service spamassassin restart

6. Restore backup
sudo sa-learn --restore < /home/dit2022/sa-learn.backup

[KiekerJan]

Nice work.
How’s the performance now?

[robertpopa22]

Performace seems similar but we have multiple access simultaneous so, in this scenario, it is a big plus. Multiple threads and all processors are used for both data storage and email parsing.

I will observe it for some time and come back with some factual details.

So, for spam is about 21.084 mess/sec

So, for ham is about 29.308 mess/sec

Please note that both spam and ham learning were running in paralel. So they should be added… Aprox 50mess/sec (for a HP 380 G9, with a Xeon E5-2620 v3