Slow SQL query slowing down Alfresco - missing index?

[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.

[afaust]

Hello,

it appears you have a node that has about 105552 child nodes in your database. This can be the root of the version or archive store, if you have no such constellation in your business data. When you check the indexes on the alf_child_assoc table you find only a composite index parent_node_id involving the child_node_name column and child_node_name_crc column in 3rd and 4th order. MySQL can't use that index to optimize the query if the query does not include the necessary columns of the 1st and 2nd order, parent_node_id and type_qname_id. Unfortunately, type_qname_id is not specified in this query (as it may not be restricted in the specific query use case), so MySQL has to do a (partial) table scan on child_node_name column.

If you add an additional index on the child_node_name column alone, you should be able to improve that querys performance. I've used the following in a quick test:
<blockcode>
CREATE INDEX idx_aca_cnn on alf_child_assoc(child_node_name);
</blockcode>

It would be interesting to know what kind of node the parent is. This could potentially be something for an enhancement ticket in JIRA, especially if it affects a core Alfresco service.

Regards
Axel

[oleh]

Hi Axel

Thank you for the quick response!

In the past we previously had cm:versionable as a mandatory aspect on cm:content, which means that everything got versioned while from the beginning. We now realize that this is a mistake (we see a huge difference in performance, specially via CIFS if we turn it off). We have now implemented a rule to only apply cm:versionable to specific areas of the repository.

All of our data is located in one space in Company Home - but not directly under this space, it's divided up into subspaces (lots of them). I don't think any space has 105552 direct child nodes..

I'll start to dig into the DB to see what's going on, I'll keep you posted.

[oleh]

Hi Axel

It seems the culprit is the store root for version2Store (node id 10). I tried looking it up in the node browser - took for ever to load, it has a ton of children.

Any info on the version2Store? It doesn't seem very effective if Alfresco stores everything related to versions in one level?

I'm gonna try the index you suggested, but something seems a bit off. It might be our own fault because we had cm:versionable as a mandatory aspect, but any info you have on it would be much appreciated

[afaust]

Hello,

well, keeping every root of a version history in one level and enforcing a naming convention based on the UUID of a node allows for a pretty inexpensive / simple lookup algorithm (if the DB query is appropriately optimized). Of course, for administrators this is a real pain as they can't use the node browser. I too would love it if this could be partitioned more efficiently. Providing a proof-of-concept alternative version service is one open checkbox on my personal when-there-is-time list, as I would also love to have some kind of branching ability (e.g. copy a node and have all the history up to the point of copying available on the duplicate as the start of a new version history) in Alfresco. And I have already implemented a custom database query in one project that could be used to retrieve the version history of a specific node in one DB query without requiring a specific logical structure.

Regards
Axel

[oleh]

Hi Axel

I see, it makes sense, however it's a shame like you say that we can't use the nodebrowser to view the version store. Your project sounds great

I applied the fix from https://issues.alfresco.com/jira/browse/ALF-13260 this morning and it solved my issues because the query now correctly uses the index.

Notice that the real fix is below in the comments here:
https://issues.alfresco.com/jira/browse/ALF-13260?focusedCommentId=170521&page=com.atlassian.jira.pl...

The initial fix in the description of the issue doesn't fix my problem, but adding the and assoc.type_qname_id to the query did indeed solve the issue.
It seems it has been fixed in 4.1 so I guess we'll have to bump up upgrading to the latest version soon

Once again, thank you for pointing me in the right direction.

/Ole

[oleh]

Hi again

I did some research and I found this one: https://issues.alfresco.com/jira/browse/ALF-13260

It seems there is a bug because the indexes would require assoc.type_qname_id to be included in the query. The JIRA ticket describes the process to fix it, so the query does indeed include the missing column and my tests (running the query manually) verifies that this effectively solves the problem. Can't try it out on the servers before tomorrow morning, but I'm pretty sure this is fixes our problems

I'll update the thread how it goes, but I want to thank you, Axel your input about indexes pointed me in the right direction