topic Re: Problem with ACL related Tables Size in Nuxeo Forum

Problem with ACL related Tables Size

pibou_Bouvret — Fri, 30 Jan 2015 09:26:46 GMT

I have a problem with the growing size of some tables :

          relation           |  size
-----------------------------+---------
 aclr_user_map_acl_id_idx    | 15 GB
 aclr_user_map               | 12 GB
 aclr_user_map_user_id_idx   | 8482 MB

The total size of the /var/lib/psql is around 40Gb

Once dumped, the archive is around 3 Gb

If I restart the server, the db falls back to its "real size" : less than 10 Gb

What's happening ? That's our third or fourth nuxeo installation, and it is the first time this kind of problem occurs.

Re: Problem with ACL related Tables Size

Florent_Guillau — Fri, 30 Jan 2015 14:19:59 GMT

The table aclr_user_map, as described in the doc contains the necessary info to be able to do efficient searches. It's a relationship table between the identity of the user (all his groups) and the synthetic ACLs applying to sets of documents. It will grow with the number of users that have made a search since the last truncation, and with the variety of ACLs set on documents.

At Nuxeo restart this table is truncated (when not in cluster mode).

Re: Problem with ACL related Tables Size

pibou_Bouvret — Fri, 30 Jan 2015 16:06:45 GMT

Quite disapointed by your answer

Re: Problem with ACL related Tables Size

Florent_Guillau — Fri, 30 Jan 2015 16:20:00 GMT

Not necessarily, you can have a nightly job that calls nx_vacuum_read_acls while holding an exclusive lock (ACCESS EXCLUSIVE) on at least aclr, aclr_user, aclr_user_map, aclr_modified, acls, hierarchy and hierarchy_read_acl to make sure that concurrent processes don't mess up the tables.