Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.15
-
None
-
None
Description
We have 3 nodes, located in EU-AS-US hosted by Gooogle Cloud, with the "Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu" version.
The nodes are synchronized with Galera Cluster and the databases are encrypted with "File Key Management Encryption" MariaDb plugin.
When we insert/update a relevant quantity of data (about 20k rows insertion) with a single SQL transaction, the database server is "frozen/locked".
Therefore all the other databases and tables, not related to data insertion, are "frozen/locked" on write (update, insert sql queries)
We have monitored all the queries executed after commit with the relevant data and they are all on hold waiting for the end of the transaction.
Is this the standard behaviour of galera/mariadb?
Are you aware about any configuration to solve this issue with galera/mariadb?
Following we report the servers configuration, the same for all servers, and the ping log between 3 servers:
--galera
|
[mysqld]
|
wsrep_on=ON
|
innodb_buffer_pool_size=12G
|
wsrep_cluster_address="gcomm://x.x.x.x,x.x.x.x,x.x.x.x" |
bind-address=0.0.0.0 |
default_storage_engine=InnoDB
|
binlog_format=row
|
wsrep_provider=/usr/lib/galera/libgalera_smm.so
|
wsrep_cluster_name="mariadb-galera-cluster" |
wsrep_sst_method=rsync
|
wsrep_node_address=x.x.x.x
|
wsrep_node_name=db-xx-yy
|
wsrep_gtid_mode=ON
|
wsrep_gtid_domain_id=9999 |
log_slave_updates=ON
|
log_bin=mariadb-bin
|
wsrep_slave_threads= 40 |
innodb_autoinc_lock_mode=2 |
innodb_flush_log_at_trx_commit=0 |
max_allowed_packet=256M
|
innodb_log_file_size=256M
|
max_connections = 2000 |
long_query_time = 1 |
|
|
-- mysqld_compatibility.cnf
|
[mysqld]
|
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" |
[mysql_install_db]
|
auth_root_authentication_method=normal
|
[mariadb]
|
optimizer_search_depth=1 |
|
|
|
|
|
|
-- US
|
root@db-eu-10:/etc/mysql/mariadb.conf.d# ping db-us-10 |
PING db-us-10 (y.y.y.y) 56(84) bytes of data. |
64 bytes from db-us-10 (y.y.y.y): icmp_seq=1 ttl=64 time=99.5 ms |
64 bytes from db-us-10 (y.y.y.y): icmp_seq=2 ttl=64 time=99.1 ms |
64 bytes from db-us-10 (y.y.y.y): icmp_seq=3 ttl=64 time=99.0 ms |
64 bytes from db-us-10 (y.y.y.y): icmp_seq=4 ttl=64 time=98.9 ms |
64 bytes from db-us-10 (y.y.y.y): icmp_seq=5 ttl=64 time=99.3 ms |
--- db-us-10 ping statistics --- |
5 packets transmitted, 5 received, 0% packet loss, time 4005ms |
rtt min/avg/max/mdev = 98.927/99.171/99.522/0.205 ms |
|
|
-- AS
|
root@db-eu-10:/etc/mysql/mariadb.conf.d# ping db-as-10 |
PING db-as-10 (z.z.z.z) 56(84) bytes of data. |
64 bytes from db-as-10 (z.z.z.z): icmp_seq=1 ttl=64 time=250 ms |
64 bytes from db-as-10 (z.z.z.z): icmp_seq=2 ttl=64 time=249 ms |
64 bytes from db-as-10 (z.z.z.z): icmp_seq=3 ttl=64 time=249 ms |
64 bytes from db-as-10 (z.z.z.z): icmp_seq=4 ttl=64 time=249 ms |
64 bytes from db-as-10 (z.z.z.z): icmp_seq=5 ttl=64 time=249 ms |
--- db-as-10 ping statistics --- |
6 packets transmitted, 5 received, 16.6667% packet loss, time 5007ms |
rtt min/avg/max/mdev = 248.565/248.989/250.301/0.660 ms |