Skip to content

2.27.0.0-b538

2.27.0.0-b538: [#28175] YSQL: Reduce PgFKReferenceCache memory usage
@d-uspenskiy d-uspenskiy tagged this 07 Sep 06:48 
Summary:
The `PgFKReferenceCache` collects intents for FK checks and then perform batched read. The size of the batch is controlled by the `ysql_session_max_batch_size` flag and GUC.
In spite of the fact batch size is limited YSQL may collect much more intents than this size prior to perform the read. This leads to extra memory consumption.

```
CREATE TABLE p1 (k INT PRIMARY KEY);
CREATE TABLE p2 (k INT PRIMARY KEY);
CREATE TABLE p3 (k INT PRIMARY KEY);
CREATE TABLE c (k INT PRIMARY KEY,  v1 INT REFERENCES p1(k), v2 INT REFERENCES p2(k), v3 INT REFERENCES p3(k));
INSERT INTO p1 SELECT i FROM generate_series (1, 1000000) AS i;
INSERT INTO p2 SELECT i FROM generate_series (1, 1000000) AS i;
INSERT INTO p2 SELECT i FROM generate_series (1, 1000000) AS i;

INSERT INTO c SELECT i, i , i, i FROM generate_series (1, 1000000) AS i;
```

In the example above the `PgFKReferenceCache` on processing INSERT will create 3 * 1000000 = 3000000 elements in intents cache.

Total peak memory usage on this statement is quite huge (`255- 285Mb`)
Memory usage can be decreased to `165-190Mb` in case `PgFKReferenceCache` will not collect more that max batch size elements and will perform the read once the limit will be reached.

This diff performs this optimization.
Jira: DB-17824

Test Plan: Jenkins

Reviewers: amartsinchyk, jason, myang, kramanathan

Reviewed By: myang

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D46385
Assets 2
Loading