AnsweredAssumed Answered

Slow SQL query slowing down Alfresco - missing index?

Question asked by oleh on Apr 4, 2013
Latest reply on Apr 5, 2013 by oleh
Hello :)

First of all, apologies if this isn't the right forum to ask this question. Mods, please feel free to correct me and move the post to the correct forum.

I've noticed we have a few "hickups" in our Alfresco. Currently we have 80GB data, roughly 400000 nodes.

Most of the time I see MySQL running "wild" for a few minutes.  I'll see quite a few queries like this one:

SELECT assoc.id AS id,
       parentNode.id AS parentNodeId,
       parentNode.version AS parentNodeVersion,
       parentStore.protocol AS parentNodeProtocol,
       parentStore.identifier AS parentNodeIdentifier,
       parentNode.uuid AS parentNodeUuid,
       childNode.id AS childNodeId,
       childNode.version AS childNodeVersion,
       childStore.protocol AS childNodeProtocol,
       childStore.identifier AS childNodeIdentifier,
       childNode.uuid AS childNodeUuid,
       assoc.type_qname_id AS type_qname_id,
       assoc.child_node_name_crc AS child_node_name_crc,
       assoc.child_node_name AS child_node_name,
       assoc.qname_ns_id AS qname_ns_id,
       assoc.qname_localname AS qname_localname,
       assoc.is_primary AS is_primary,
       assoc.assoc_index AS assoc_index
FROM alf_child_assoc assoc
JOIN alf_node parentNode ON (parentNode.id = assoc.parent_node_id)
JOIN alf_store parentStore ON (parentStore.id = parentNode.store_id)
JOIN alf_node childNode ON (childNode.id = assoc.child_node_id)
JOIN alf_store childStore ON (childStore.id = childNode.store_id)
WHERE parentNode.id = 10
  AND assoc.child_node_name = '2ba4f767-3892-44e4-9503-5f739a350f05'
  AND assoc.child_node_name_crc = 908029460


Each query will take roughly 13-15s to run.

Running an EXPLAIN on the query above gives me this

+—-+————-+————-+——–+———————————————————————+——————+———+——————————+——–+————-+
| id | select_type | table       | type   | possible_keys                                                       | key              | key_len | ref                          | rows   | Extra       |
+—-+————-+————-+——–+———————————————————————+——————+———+——————————+——–+————-+
|  1 | SIMPLE      | parentNode  | const  | PRIMARY,store_id,fk_alf_node_store                                  | PRIMARY          | 8       | const                        |      1 |             |
|  1 | SIMPLE      | parentStore | const  | PRIMARY                                                             | PRIMARY          | 8       | const                        |      1 |             |
|  1 | SIMPLE      | assoc       | ref    | parent_node_id,fk_alf_cass_pnode,fk_alf_cass_cnode,idx_alf_cass_pri | idx_alf_cass_pri | 8       | const                        | 105552 | Using where |
|  1 | SIMPLE      | childNode   | eq_ref | PRIMARY,store_id,fk_alf_node_store                                  | PRIMARY          | 8       | alf_test.assoc.child_node_id |      1 |             |
|  1 | SIMPLE      | childStore  | eq_ref | PRIMARY                                                             | PRIMARY          | 8       | alf_test.childNode.store_id  |      1 |             |
+—-+————-+————-+——–+———————————————————————+——————+———+——————————+——–+————-+


Notice the 105552 rows it has to scan through.. Are we missing an index?

A bit info about my setup:
Community 4.0.d (with Solr)
MySQL 5.5.29

The server is a virtual server, Ubuntu 12.04, 8GB ram.

Outcomes