TMQ, indexes and performances

[vincent-kali]

Hi all,

I'm working with TMQ on a large repo (about 5 millions of docs) on a community 5.1.g.

A read in doc that optional indexes could be added by the patch (http://dev.alfresco.com/resource/AlfrescoOne/5.0/configuration/alfresco/dbscripts/upgrade/4.2/org.hi...)

But I checked that these indexes are already added in my DB. Does it means that this patch applies to previous versions only ?

In some situations, eventual queries are incomparably more performant that TMQ.

(about 1000x faster in some situations). Does it make sense ? Or it means that my postgres has to be tuned ?

Thanks,

Vincent

[afaust]

I gave a presentation about transactional metadata queries at last BeeCon which included considerations for performance (video recording). In Alfresco 5.1.g there are actually two sets of indices for TMQ - one originates from Alfresco 4.2 (the one you referenced) and another was introduced with 5.1. For adequate performance both must be enabled and their indices must be added to the database schema. For very specific use cases additional, business-oriented indices can be useful. I cannot see anything close to a 1000x worse performance in transactional metadata queries when they are used correctly. It is true that there are some scaling issues (due to the design) when you attempt to retrieve results for a very unselective query or attempt to retrieve page 20 or later.

Your PostgreSQL server may need to be tuned if you are (still) using the default installation done by Alfresco because that will have the configuration for memory-related parameters (e.g. shared_buffers) set to the factory default which is not suitable for scaling production use.

[vincent-kali]

Thanks Axel for your response. I've already watched your (great) presentation about TMQ.

When you say "both must be enabled and their indices must be added to the database schema" does it means that I've some action to perform on a 5.1.g default setup ? (As indexes are already defined in DB)

Vincent

[afaust]

As I mentioned in my presentation, by default, none of the indices are added in an Alfresco installation and action must be taken. That applies to 5.1.g as well. I don't know why you have one set of indices added - I would assume because at some point you had actually enabled that patch for at least one start of the system.

[vincent-kali]

I see that following patches are applied by default when running alfresco 5.1x installation:

- patch.db-V4.2-metadata-query-indexes

- patch.db-V5.1-metadata-query-indexes

After every restart, you'll see message: Ignoring script patch (post-Hibernate): patch.db-VX.X-metadata-query-indexes, meaning that patch is ignored as already applied (to my understanding).

Then indexes below are set on the alfresco DB on 5.1.g by default:

table alf_node:

idx_alf_node_cor 
idx_alf_node_crd 
idx_alf_node_mdq 
idx_alf_node_mod 
idx_alf_node_mor 
idx_alf_node_tqn 
idx_alf_node_txn_type

table alf_node_properties:

idx_alf_nprop_b
idx_alf_nprop_d
idx_alf_nprop_f
idx_alf_nprop_l
idx_alf_nprop_s

Then to action has to be taken....?

[afaust]

Yes, it looks like the recent version now include these patches by default in the first installation and the runtime check has also been adapted. That is good to know and I had not noticed that before.