topic Re: TMQ, indexes and performances in Alfresco Forum

TMQ, indexes and performances

vincent-kali — Wed, 21 Jun 2017 11:48:08 GMT

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.hibernate.dialect.Dialect/metadata-query-inde

Re: TMQ, indexes and performances

afaust — Thu, 22 Jun 2017 17:27:21 GMT

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.

Re: TMQ, indexes and performances

vincent-kali — Thu, 22 Jun 2017 17:43:36 GMT

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

Re: TMQ, indexes and performances

afaust — Thu, 22 Jun 2017 18:13:29 GMT

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.

Re: TMQ, indexes and performances

vincent-kali — Mon, 26 Jun 2017 14:15:30 GMT

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

Re: TMQ, indexes and performances

afaust — Mon, 26 Jun 2017 14:17:32 GMT

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.