Not the answer you need?
Register and ask your own question!

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

tmdmmtk4tmdmmtk4 EntrantCurrent User Role Beginner
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

Comments

  • Kenn TakaraKenn Takara Percona Percona Staff Role
    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
  • tmdmmtk4tmdmmtk4 Entrant Current User Role Beginner
    Hi Kenn Takara
    My nproc shows 16 and wsrep_slave_threads have set to 8 before.
  • tmdmmtk4tmdmmtk4 Entrant Current User Role Beginner
    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"
    
  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.