Understanding replication and read lock contention problems
Hi, I'm a novice SQL admin and have set up replication according to this page to support a central location to store the bayes database for a spamassassin installation. https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/
Learning an mbox across the network from one of the mail relays is not only unimaginably slow due to some read lock contention, but I'm unsure whether learning the mbox on one of the mail relays is actually being synced back to the primary server or if it's only affecting the replica on which I'm doing the training.
How can I tell whether changes made to the local database are being written/synced to the primary server?
spamassassin is set up to use the local database, not the primary.
Slave_IO_State: Waiting for master to send event
Master_Host: havoc.mycompany.com
Master_User: repuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000711
Read_Master_Log_Pos: 183655846
Relay_Log_File: xavier-relay-bin.000004
Relay_Log_Pos: 183656145
Relay_Master_Log_File: mysql-bin.000711
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: bayes,txrepdb,sqlgrey,sbclient
When I connect to the primary database in order to do the bayes training, there's some kind of read lock contention.
2020-12-02 19:16:35 3179860 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2020-12-02 19:16:35 3179860 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 479741333, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 39 lock struct(s), heap size 8312, 38 row lock(s), undo log entries 37
MySQL thread id 3180255, OS thread handle 139741126801152, query id 195205475 209-216-11-109.static.tampabayfiber.com 209.216.11.109 amavis Updating
UPDATE bayes_token SET atime = '1606958190' WHERE id = '4' AND token IN ('...')
2020-12-02 19:16:35 3179860 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13828 page no 7851 n bits 360 index PRIMARY of table `bayes`.`bayes_token` trx id 479741333 lock_mode X locks rec but not gap waiting
Record lock, heap no 48 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 5; hex 46f92bed66; asc F + f;;
2: len 6; hex 00001c984485; asc D ;;
3: len 7; hex 4e00000a45335f; asc N E3_;;
4: len 4; hex 80000132; asc 2;;
5: len 4; hex 80003839; asc 89;;
6: len 4; hex dfc83c4b; asc <K;;
2020-12-02 19:16:35 3179860 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 479741061, ACTIVE 35 sec inserting
mysql tables in use 1, locked 1487 lock struct(s), heap size 73848, 500 row lock(s), undo log entries 1050
MySQL thread id 3179860, OS thread handle 139740899604224, query id 195206025 xavier.mycompany.com 209.216.11.114 amavis Update
INSERT INTO bayes_token
(id, token, spam_count, ham_count, atime)
VALUES ('4','h/','0','1','1606826878')
ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + '0', 0),
ham_count = GREATEST(ham_count + '1', 0),
atime = GREATEST(atime, '1606826878')
2020-12-02 19:16:35 3179860 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
I'm unsure what to do next and would appreciate any insight.
Answer Answered by Daniel Black in this comment.
First there is a need to clarify one thing, there is never any read lock contentions, all read locks are shared.
Replication is one way, master -> replica (slave). To identify the difference you'll need to look at the data in the columns.
The error you have posted is a dead-lock error. This occurs when a transaction takes locks (update/insert) in a different order to another transaction. So transaction X takes lock A and tries to obtain lock B. And transaction Y holds lock B and tries to aquire lock A. This is detected by the server and a deadlock thrown as there is no obvious server action to take. The locks would be output that you have posted, its just truncated slightly.
From your scenario it seems you'd like bi-direction replication between your end nodes and other nodes. For this scenario it seems Galera would be a better replication technology to use.
Bayes_token has a primary key of `(id,token)` so even with galera deadlocks may not be resolved (but at least it will be in sync). On this table small difference in the values may not make a difference to the application (https://github.com/apache/spamassassin/blob/trunk/sql/bayes_mysql.sql#L23) as I understand it.
