|
Hi,
I have a setup with 3 Galera nodes on MariaDB 10.4.18.
I have blocking issue with MariaDB stability during concurrent updates on same row (this usually happens for me when multiple client applications tries to modify a record in the database in the same time).
The issues is the following:
1. Multiple customers starts the application and tries to perform an update on the same record.
2. Galera handles a few concurrencies but eventually hangs.
Now, I understand that we need to retry the transaction in the case of a deadlock, the issue is that Galera hangs before the application retries that.
In order to replicate this we perform the following test:
1. Start Galera on 3 nodes
2. Have a ProxySQL with 3 nodes as writers
3. We have a tool that connects to Galera start a tool that sends queries continuously to the database
I'm attaching the script that we use testing and also I'm putting here he Galera configuration:
- MariaDB validation diagram.png - This is the network topology for testing
- MariaDB-server.cnf
- galera-lock-replication.sql - SQLs to create the database and table for testing
- galera-lock-replication.php - the tool that connects to database and send sql update
- threads-tool.php - the tool that starts "galera-lock-replication.php" in multithreading mode to simulate concurrency
Issue replication
1. Create the database using the galera-lock-replication.sql script provided in attach
2. Copy the 2 php files (galera-lock-replication.php and threads-tool.php) provided in attach in /galera-test/ folder under apache root
3. Start a first terminal with:
# php /path/to/galera-test/threads-tool.php
|
- this will start bulks of 100 paralel calls to the frontend webservice in a loop:
http://127.0.0.1/galera-test/galera-lock-replication.php
|
4. Wait few seconds (between 5 and 20)
5. Galera cluster should start refuse connections
|
MariaDB validation diagram.png
MariaDB-server.cnf