search

status-im / infra-shards

Infrastructure for Status fleets
https://github.com/status-im/nim-waku
0 stars 2 forks source link

set max_locks_per_transaction to 2160 #34

Closed Ivansete-status closed 1 week ago

Ivansete-status commented 1 week ago

Description

We are using partitions in our postgres DBs. And we have one partition per hour (24 partitions per day.)

The default max_locks_per_transaction value (64) can cause "our of memory" and block issues in the DB because we use to have more than 64 partitions.

With 2160 we aim to avoid that issue for 90 days (2160 == 90*24.) if we consider a time retention policy of 90 days. Nevertheless, we usually have time retention policy of 30 days in our Status fleets, but we are just adding some extra margin.

Issue

PostgreSQL doesn't respond properly during E2E tests - closes https://github.com/waku-org/nwaku/issues/2838

apentori commented 1 week ago

On which fleet should we start deploying it ? test ?

Ivansete-status commented 1 week ago

On which fleet should we start deploying it ? test ?

The first one should be shards.staging

jakubgs commented 1 week ago

Keep in mind the max_locks_per_transaction setting requires a restart:

The shared lock table has space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction; hence, no more than this many distinct objects can be locked at any one time. This parameter limits the average number of object locks used by each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent table with many children. This parameter can only be set at server start.

https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION

apentori commented 1 week ago

Applied to sharding.staging