[YSQL] Memory bloat with large number of writes in a single transaction

[sushantrmishra]

Jira Link: DB-12638

Description

Memory bloat is observed when multiple inserts are performed in single transaction. Once the transaction is committed or aborted then memory is released.

Query Pattern : Begin; insert <1> ; insert <2> , insert <3> ......, Insert <N>; commit;

reproduction script:

import psycopg2
import subprocess

connection = psycopg2.connect(database="yugabyte", user="yugabyte", password="xxx", host="localhost", port=5433)

query = "INSERT INTO test VALUES (1);"

cursor = connection.cursor()
connection.autocommit = False

cursor.execute("CREATE TABLE IF NOT EXISTS test (id int);")

cursor.execute("SELECT pg_backend_pid();")
pid = cursor.fetchall()[0][0]
print("pid="+str(pid))

for i in range(0, 1000000):
        cursor.execute(query)

        if (i % 10000 == 0):
                print("i=" + str(i) + " rss=")
                subprocess.run(["ps", "-p", str(pid), "-o", "rss", "--no-headers"])

Sample output:

pid=3645
i=0 rss=46700
i=10000 rss=138232
i=20000 rss=234104
i=30000 rss=330248
i=40000 rss=430212
i=50000 rss=528388
i=60000 rss=621944
i=70000 rss=725728
i=80000 rss=821604
i=90000 rss=919780
i=100000 rss=1015916
...

The issue is resolved with the following patch applied (key difference is the change in yb_exec_query_wrapper_one_attempt) invalidate-foreign-key-cache.patch

with the patch:

pid=118377
i=0 rss=45900
i=10000 rss=47644
i=20000 rss=47644
i=30000 rss=47496
i=40000 rss=47496
i=50000 rss=47664
i=60000 rss=47664
i=70000 rss=47552
i=80000 rss=47552
i=90000 rss=47544
i=100000 rss=47544

(This is probably not the right fix. But it proves that the growing allocations exist in the FK reference caches)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[timothy-e]

How I came up with the patch:

1. a simple SELECT * FROM yb_heap_stats(); confirmed that most memory was allocated in PGGate:

   SELECT * FROM yb_heap_stats() -- converted to kb, commas added for clarity
   total_heap_requested             | 1,503,657
   total_heap_usage                 | 1,752,451
   total_heap_allocation            | 1,499,350
   cached_free_memory               | 1,335
   total_heap_released              | 0
   PostgreSQL_memory_usage          | 1,473
   PostgreSQL_storage_gateway_usage | 1,497,876

2. Apply the diff https://phorge.dev.yugabyte.com/D33735 and rebuild

3. Modify the test script: a. Add cursor.execute("set yb_tcmalloc_sample_freq = 16;") to the beginning b. To the main loop, add

       if (i % 100000 == 0):
               cursor.execute("select * from yb_heap_snapshot(false);")
               print(cursor.fetchall())

4. Run the test script and observe the following profiled allocations after a few hundred thousand iterations: sample.txt

e.g.

[(23615616, 368994, 64, 23615616, 368994, 'tcmalloc::tcmalloc_internal::SampleifyAllocation<>()
slow_alloc<>()
TCMallocInternalNewArray
std::__1::__hash_table<>::__emplace_unique_impl<>()
yb::pggate::PgSession::AddForeignKeyReference()
yb::pggate::PgApiImpl::AddForeignKeyReference()
YBCPgAddIntoForeignKeyReferenceCache
...

(the first few numbers correspond to estimated_bytes, estimated_count, avg_bytes_per_allocation, sampled_bytes, sample_count)

The top three entries each reference the ForeignKeyReferenceCache. There might be more, but starting with that will give an idea of what percent of the bloat is due to the cache.