Uploaded image for project: 'Alfresco One Platform'
  1. Alfresco One Platform
  2. ACE-3942

BM-0004: MySQL node cleanup SQL is inefficient

    Details

      Description

      During nightly deleted node cleanup, we observe the following SQL running via the NodeDAOImpl.deleteNodesByCommitTime:

      explain extended
              delete from alf_node_properties
              where 
                   node_id IN
                   (
                  select node.id
                  from
                      alf_node node
                      join alf_transaction txn on (txn.id = node.transaction_id)
                  where
                      node.type_qname_id = 143 and
                      txn.commit_time_ms < 1429439513401
                      )
      ;
      

      The plan for this (with over 200M entries in alf_node_properties):

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY alf_node_properties ALL PRIMARY,fk_alf_nprop_n,fk_alf_nprop_qn,fk_alf_nprop_loc,idx_alf_nprop_s,idx_alf_nprop_l       202698759 100.00 Using where
      2 DEPENDENT SUBQUERY node eq_ref PRIMARY,idx_alf_node_txn_type,idx_alf_node_tqn PRIMARY 8 func 1 100.00 Using where
      2 DEPENDENT SUBQUERY txn eq_ref PRIMARY,idx_alf_txn_ctms PRIMARY 8 bm4gHrd.node.transaction_id 1 100.00 Using where

      This causes massive load on the database with the ALL select.

      Changing the statement to:

      explain extended
              delete np from alf_node_properties np
              join alf_node n on (np.node_id = n.id)
              join alf_transaction txn on (txn.id = n.transaction_id)
              where
                  n.type_qname_id = 143 and
                  txn.commit_time_ms < 1429439513401
      ;
      

      Gives the plan:

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE n ref PRIMARY,idx_alf_node_txn_type,idx_alf_node_tqn idx_alf_node_tqn 8 const 219476 100.00  
      1 SIMPLE txn eq_ref PRIMARY,idx_alf_txn_ctms PRIMARY 8 bm4gHrd.n.transaction_id 1 100.00 Using where
      1 SIMPLE np ref PRIMARY,fk_alf_nprop_n PRIMARY 8 bm4gHrd.n.id 1 100.00  

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                closedissues Closed Issues
                Reporter:
                dhulley Derek Hulley [X] (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: