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
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’; ".
[url]Redirecting
Hi Kenn Takara
My nproc shows 16 and wsrep_slave_threads have set to 8 before.
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"
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.