Node hang after INSERT IGNORE INTO or DELETE FROM .. WHERE 1=1

[tmdmmtk4]

Hi

I built a percona xtradb cluster with three node (5.7.19). Cluster work properly except after i use INSERT IGNORE INTO or DELETE FROM … WHERE 1=1

Here is my query

[I]INSERT IGNORE INTO catalog_product_entity_group_price_location_level[/I]
[I] ([/I]
[I] SELECT pr.*[/I]
[I] FROM catalog_product_entity_group_price_level pr[/I]
[I] INNER JOIN[/I]
[I] ([/I]
[I] SELECT entity_id, level, event, max(location_level) as max_location_level, customer_group_id, warehouse_id, is_promotion, value[/I]
[I] FROM catalog_product_entity_group_price_level[/I]
[I] WHERE 1 = 1[/I]

[I] GROUP BY entity_id, customer_group_id, warehouse_id, is_promotion ORDER BY NULL[/I]
[I] ) tmp_table[/I]
[I] ON[/I]
[I] pr.entity_id = tmp_table.entity_id[/I]
[I] AND pr.customer_group_id = tmp_table.customer_group_id[/I]
[I] AND pr.warehouse_id = tmp_table.warehouse_id[/I]
[I] AND pr.location_level = tmp_table.max_location_level[/I]
[I] AND pr.is_promotion = tmp_table.is_promotion[/I]
[I] GROUP BY pr.entity_id, pr.warehouse_id, pr.customer_group_id, pr.is_promotion, pr.event, pr.level, pr.location_level[/I]
[I] ORDER BY NULL[/I]
[I] )[/I]

• When a node runs this query it still works normally. But after query runs completely, i can read but cannot write any table on this node.
• The result of query is about 10 milion rows
• Node just cannot write for about 1 hour. After that it work normally. I think node sync to other nodes in this time
• Why does it block writing on databases. Please help me

Thanks

[Kenn_Takara]

What is your value for wsrep_slave_threads? If it hasn’t been changed, the default is 1, which means that only a single thread is handling writes.
You can check the value by executing "show variables like ‘wsrep_slave_threads’; ".

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/wsrep-system-index.html

[tmdmmtk4]

Hi Kenn Takara
My nproc shows 16 and wsrep_slave_threads have set to 8 before.

[tmdmmtk4]

Hi Kenn Takara

My value for wsrep_slave_threads is 8 and nproc show 16.
Here is my config for wsrep :

[mysqld]

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.20,192.168.1.21,192.168.1.23

binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=192.168.1.23
# Cluster name
wsrep_cluster_name='OmniCluster'

#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=percona-node4

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="xxx"

[krunalbauskar]

Just to re-understand the problem:

Say you have 2 node cluster

• Node-1 is where you run the said long-running insert.
• Node-2 is replicating this insert through galera replication.

Now when the insert/delete is completed on node-1 if you execute another insert on NODE-1 … DOES IT HANGS?
OR
Do you mean when the insert/delete is completed on node-1 if you execute another insert on NODE-2 … IT HANGS

Latter is not an issue. Node-2 is busy applying the existing replicated write-set. It can’t allow followup write-set to commit till the replicate write-set is committed.

Former looks to be a problem.
If you are hitting a former case then can please share the reproducible test-case.