Mailing List Archive

Hi Brent,

On 2/10/21 12:21 PM, Brent Clark wrote:
> Good day Guys
>
> I just want to check with the community, is there anybody using SA's
> bayes with the Redis backend?
>
> I work at a largish ISP, so we talking lots of mail.
>
> There is no real question, but what I would like to find out is (and to
> ask), does it scale and are any pitfalls?
> Naturally, we would look at doing HA, but am asking for that any
> comment, any tip, any opinion on using redis for bayes.

Been using it from day one (I'm party to blame we have this) and it
scales VERY well. Bayes processing bottleneck has become a thing of the
past.
Pifalls? none so far.

I wouldn't go back anymore.
Obviously, it's global only, no per user.

Axb

Hi,

> > There is no real question, but what I would like to find out is (and to
> > ask), does it scale and are any pitfalls?
> > Naturally, we would look at doing HA, but am asking for that any
> > comment, any tip, any opinion on using redis for bayes.
>
> Been using it from day one (I'm party to blame we have this) and it
> scales VERY well. Bayes processing bottleneck has become a thing of the
> past.
> Pifalls? none so far.
>
> I wouldn't go back anymore.
> Obviously, it's global only, no per user.

Is there an easy/efficient way of converting an existing mariadb bayes
database to redis?

Perhaps "sa-learn --backup", set up redis, then restore?

I know I've been less than successful in the past when migrating from
one version of mariadb to another, so just wondering how successful
this approach would be.

The problem I'm having with bayes in mariadb is being able to use a
central database server for the database, while reading and updating
it from remote systems. Will redis solve this problem?

# sa-learn --dump magic
0.000 0 3 0 non-token data: bayes db version
0.000 0 11083 0 non-token data: nspam
0.000 0 48363 0 non-token data: nham
0.000 0 3709015 0 non-token data: ntokens
0.000 0 1372117134 0 non-token data: oldest atime
0.000 0 1613055126 0 non-token data: newest atime
0.000 0 0 0 non-token data: last journal sync atime
0.000 0 1606461007 0 non-token data: last expiry atime
0.000 0 0 0 non-token data: last expire atime delta
0.000 0 0 0 non-token data: last expire
reduction count

On 2021-02-11 9:54 am, Alex wrote:

> Hi,
> There is no real question, but what I would like to find out is (and to ask), does it scale and are any pitfalls? Naturally, we would look at doing HA, but am asking for that any comment, any tip, any opinion on using redis for bayes. Been using it from day one (I'm party to blame we have this) and it scales VERY well. Bayes processing bottleneck has become a thing of the past. Pifalls? none so far. I wouldn't go back anymore. Obviously, it's global only, no per user.

Is there an easy/efficient way of converting an existing mariadb bayes
database to redis?

Perhaps "sa-learn --backup", set up redis, then restore?

I know I've been less than successful in the past when migrating from
one version of mariadb to another, so just wondering how successful
this approach would be.

The problem I'm having with bayes in mariadb is being able to use a
central database server for the database, while reading and updating
it from remote systems. Will redis solve this problem?

# sa-learn --dump magic
0.000 0 3 0 non-token data: bayes db version
0.000 0 11083 0 non-token data: nspam
0.000 0 48363 0 non-token data: nham
0.000 0 3709015 0 non-token data: ntokens
0.000 0 1372117134 0 non-token data: oldest atime
0.000 0 1613055126 0 non-token data: newest atime
0.000 0 0 0 non-token data: last journal sync atime
0.000 0 1606461007 0 non-token data: last expiry atime
0.000 0 0 0 non-token data: last expire atime delta
0.000 0 0 0 non-token data: last expire
reduction count

I've had good luck with using mariadb and galera to share the
spamassassin database across systems. I run a small 3-node setup for
email, 2x servers running dovecot replicating to each other, and a 3rd
galera quorum server. Mariadb is master-master across all 3 nodes, so
changes on any one are replicated to all the others via vpn.

Works well, and for the amount of data in the spamassassin database, it
replicates very quickly.

On Thursday 11 February 2021 at 17:21:41, deano-spamassassin@areyes.com wrote:

> Is there an easy/efficient way of converting an existing mariadb bayes
> database to redis?
>
> Perhaps "sa-learn --backup", set up redis, then restore?

https://www.mail-archive.com/users@spamassassin.apache.org/msg107512.html
answers this for you, I think :)


Antony.

--
There are two possible outcomes:

If the result confirms the hypothesis, then you've made a measurement.
If the result is contrary to the hypothesis, then you've made a discovery.

- Enrico Fermi

Please reply to the list;
please *don't* CC me.

On 2021-02-11 12:58 pm, Alex wrote:

> Hi,
>
>> I've had good luck with using mariadb and galera to share the spamassassin database across systems. I run a small 3-node setup for email, 2x servers running dovecot replicating to each other, and a 3rd galera quorum server. Mariadb is master-master across all 3 nodes, so changes on any one are replicated to all the others via vpn. Works well, and for the amount of data in the spamassassin database, it replicates very quickly.
>
> This sounds very interesting to me. Can you share more details about
> your configuration? I haven't worked with galera before, but have some
> experience with mariadb - it's currently set up as a single master
> with the actual mail relays being set up as slaves. I'd imagine the
> first thing is to convert them all to masters...
>
> Any help would be greatly appreciated.
> Thanks,
> Alex

Sure. I have a (fairly complex) ansible playbook that sets up the whole
3-node cluster, but here are the relevant details.

This is the galera portion of _/etc/mysql/my.cnf_

> #
> # * Galera-related settings
> #
> [galera]
> bind-address = 0.0.0.0
> binlog_format = row
> default_storage_engine = InnoDB
> innodb_autoinc_lock_mode = 2
> innodb_flush_log_at_trx_commit = 2
> wsrep_cluster_address = gcomm://master.vpn,dove1.vpn,dove2.vpn
> wsrep_node_name = master.vpn
>
> # Need to specify vpn address here, not public address
> wsrep_node_address = 192.168.100.50
>
> wsrep_cluster_name = my_cluster
> wsrep_on = 1
> wsrep_provider = /usr/lib/galera/libgalera_smm.so
> wsrep_sst_auth = "root:my_sekrit_password"

And this is the ansible role mariadb_restart/tasks/main.yml. This gets
called whenever another mariadb-affecting task sets the DO_RESTART
variable. This will cleanly restart the whole mariadb galera cluster.

> - become: yes
> block:
>
> - name: Check status of mysqld
> command: systemctl status mysql
> ignore_errors: yes
> changed_when: false
> register: mysql_status
>
> - name: Gracefully stop mysql on all nodes to start up cluster
> service:
> name: "mysql"
> state: "stopped"
> register: mysql_stopped
> when: mysql_status is succeeded
>
> - name: Force kill mysqld if stuck in starting state
> command: pkill -9 mysqld
> ignore_errors: yes
> changed_when: false
> when: mysql_stopped is failed
>
> - name: Clear RAM caches to free up space
> command: sysctl -w vm.drop_caches=3
> when: ansible_virtualization_type != "openvz"
> changed_when: false
>
> - name: Check if grastate.dat file exists for bootstrapping node0
> stat:
> path: /var/lib/mysql/grastate.dat
> register: grastate_exists
> when: inventory_hostname == play_hosts[0]
>
> - name: Force node0 to be a new bootstrap node
> lineinfile:
> dest: /var/lib/mysql/grastate.dat
> regexp: 'safe_to_bootstrap: 0'
> line: 'safe_to_bootstrap: 1'
> when:
> - inventory_hostname == play_hosts[0]
> - grastate_exists.stat.exists
>
> - name: bootstrap a new cluster with galera_new_cluster
> shell: /usr/bin/galera_new_cluster
> when: inventory_hostname == play_hosts[0]
>
> - name: add slave nodes to the cluster
> service:
> name: "mysql"
> state: "started"
> when: inventory_hostname != play_hosts[0]
>
> - name: Stop mysql on node0
> service:
> name: "mysql"
> state: "stopped"
> when: inventory_hostname == play_hosts[0]
>
> - name: re-add master node to the cluster
> service:
> name: "mysql"
> state: "started"
> when: inventory_hostname == play_hosts[0]
>
> #
> # block
> when: do_restart | bool

Argh - formatting got messed up. But you get the idea. It can also be
run via a small script that runs ansible-playbook like this

> ansible-playbook -K -i hosts mariadb.yml --extra-vars "{do_restart: true}"

In the list of hosts, node0 is the master or quorum node. The other two
are the dovecot replication nodes (dovecot, exim4, roundcube, etc)

I have a bash alias to check on cluster status ...

> alias cluster='mysql -B -s -N -e "show status like "%wsrep_cluster%";"'
>
> dc@master:~$ cluster
> wsrep_cluster_weight 3
> wsrep_cluster_capabilities
> wsrep_cluster_conf_id 10
> wsrep_cluster_size 3
> wsrep_cluster_state_uuid 480b440d-6643-11eb-94bc-5b47cf0676a8
> wsrep_cluster_status Primary