Review 'max_locks_per_transaction' setting in FlowDB

[jc-harrison]

Postgres stores table locks in memory, with pre-allocated space for max_locks_per_transaction * max_connections locks. Attempting to take more locks than this at any one time will result in an out-of-memory error. In the FlowDB defaults we set max_connections=200, and we're not explicitly setting max_locks_per_transaction so that defaults to 64.

Queries that select from partitioned tables require an AccessShareLock on each partition, as well as on every index on each of those partitions (this is true even if the query only requires data from one partition - postgres needs to obtain a lock before checking constraints to assess whether a partition is required). The events tables have a partition per day, and typically a number of indexes on each partition, so this can result in a large number of locks required for each query.

E.g. if the events.calls table contains 2 years of data, with 3 indexes (on datetime, msisdn and location_id), then an EventTableSubset query such as:

SELECT datetime, location_id, msisdn
FROM events.calls 
WHERE events.calls.datetime >= '2022-02-10 00:00:00' AND events.calls.datetime < '2022-02-11 00:00:00'

would require 365*2*(3+1)=2920 'AccessShareLock' table locks. With the current default settings, just 5 such transactions (of a possible 200) would be sufficient to exhaust the memory allocated for 200*64=12800 locks.

From this I think it's clear that the default max_locks_per_transaction=64 is insufficient for our heavy use of partitions, and we need to increase it. A suitable value would depend on the maximum span of data we expect to keep in the DB at any given time, along with the typical number of indexes we create on the events tables.

At the same time it's worth considering whether this is the right solution. As far as I can tell there's no major reason it would be a bad idea to allow significantly more table locks per transaction, but it's possible this issue is a sign we should be approaching something a bit differently.

[greenape]

Does this change at all with new style partitions?

[jc-harrison]

Does this change at all with new style partitions?

I haven't found the definitive answer to that yet, but I think not.

[greenape]

After a little thinking, it may decrease the number of locks required because of the different approach to partition pruning, but won't I think mitigate entirely.

This suggests that the shared memory assignment is going to be 168 bytes per possible lock, so increasing this to quite a large number (e.g. 5 years, all event types) doesn't seem untenable to me?

[jc-harrison]

OK, so 5 years of 4 event types with indexes on msisdn, location_id, datetime and tac would mean max_locks_per_transaction = 365*5*4*(1+4) = 36500. Then, assuming we keep max_connections=200, the memory assignment would be 36500*200*168 bytes, which is 1.14GB - that's certainly not negligible, but still small compared to the typical memory available on a FlowKit server.

Of course, to actually use all of those locks would require 200 transactions simultaneously accessing the events tables, which is perhaps unlikely in practice.

[greenape]

Can we set this using the configurator script?