Hi Heikki!
I made some modifications as you requested:
I have modified the program, now DISCARD ALL is issued within the program, at the end.
I have added all headers to ps aux output so anyone can see the memory growth I am refering to.
I now connect directly to postgrres,
I run now 500000 prepared statements.
I hope it's clearer now.
Thank you very much for your tips.
Subject: Memory Growth Issue in "Backend" after Creating and Executing Multiple "Named Prepared Statements" with Different Names and Executing DISCARD ALL Finally.
Product: PostgreSQL 14
Dear Technical Support Team,
We reach out to you to report an issue related to memory growth in PostgreSQL backend processes when running many Prepared Statements with different names, even though the "DISCARD ALL" command is executed at the end of the program execution.
We understand that while Prepared Statements are executed and maintained in the session, memory may grow since various objects need to be stored in the session, such as the parsed query, execution plans, etc.
However, what we don't understand is why, when the DISCARD ALL command is eventually executed, memory is not freed at all.
Could you please provide us with a more detailed explanation of this behavior? Additionally, we would like to know if there is any other specific action or configuration that we can perform to address this issue and ensure that backend memory is reduced after executing many "Named Prepared Statements".
We appreciate your attention and look forward to your guidance and suggestions for resolving this problem.
We have attached a small C program with libpq that demonstrates this issue, along with the program's output and the execution of the "ps aux" program.
Best regards,
Daniel Blanch Bataller
Hoplasoftware DBA
prepared_statement.c program
============================
/*
* prepared_statement.c
* This program demonstrates the backend memory growth using a large number
* of prepared statements, as expected.
* But surprisingly, after executing DISCARD ALL; memory is not recovered at all.
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <unistd.h>
#define ITERATIONS 500000
#define PRINT_TIMES 5000
#define SLEEP_AFTER_DISCARD_ALL 60
#define HOST "localhost"
#define PORT "5432"
#define DB "test"
#define USER "test"
#define PASS "test"
int main() {
// Connect to the database
const char *conninfo = "host=" HOST " port=" PORT " dbname=" DB " user=" USER " password=" PASS "";
printf("Connecting to %s\n", conninfo);
PGconn *conn = PQconnectdb(conninfo);
// Check connection result
if (PQstatus(conn) != CONNECTION_OK) {
fprintf(stderr, "Connection error: %s\n", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
// Get backend PID
printf("Getting backend PID \n");
PGresult *result = PQexec(conn, "SELECT pg_backend_pid();");
// Check result status
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "Error executing query: %s\n", PQerrorMessage(conn));
PQclear(result);
PQfinish(conn);
exit(EXIT_FAILURE);
}
// Get result
char *pid = PQgetvalue(result, 0, 0);
printf("Backend PID: %s\n", pid);
// Main loop
printf("Excecuting %d PreparedStatements\n", ITERATIONS);
for (int i = 0; i <= ITERATIONS; i++) {
// Prepare "Prepared Statement"
char stmt_name[50];
sprintf(stmt_name, "ps_%d", i);
const char *query = "SELECT 1 WHERE 1 = $1";
if (i % PRINT_TIMES == 0) printf("Executing PreparedStatement '%s'\n", stmt_name);
PGresult *prepare_result = PQprepare(conn, stmt_name, query, 1, NULL);
if (PQresultStatus(prepare_result) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error preparing the PreparedStatement: %s\n", PQresultErrorMessage(prepare_result));
PQclear(prepare_result);
PQfinish(conn);
exit(1);
}
// Preprared Statement parameters
const char *paramValues[] = {"1"};
// Execute Prepared Statement
PGresult *res = PQexecPrepared(conn, stmt_name, 1, paramValues, NULL, NULL, 0);
// Check Prepared Statement execution result
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr, "Error executing query: %s\n", PQresultErrorMessage(res));
PQclear(res);
PQfinish(conn);
exit(1);
}
// Get results
int numRows = PQntuples(res);
int numCols = PQnfields(res);
for (int i = 0; i < numRows; i++) {
for (int j = 0; j < numCols; j++) {
PQgetvalue(res, i, j); // Do nothing
}
}
// Free Result
PQclear(res);
}
// Execute discard all
printf("Executing DISCARD ALL;\n");
PGresult *discard_result = PQexec(conn, "DISCARD ALL;");
// Check result status
if (PQresultStatus(discard_result) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error executing command: %s\n", PQerrorMessage(conn));
PQclear(discard_result);
PQfinish(conn);
exit(EXIT_FAILURE);
}
// Wait to check backend growth
printf("Waiting %d seconds, now its time to check backend growth!\n", SLEEP_AFTER_DISCARD_ALL);
sleep(SLEEP_AFTER_DISCARD_ALL);
// Close Connection
PQfinish(conn);
return 0;
}
program output:
===============
Connecting to host=localhost port=5432 dbname=test user=test password=test
Getting backend PID
Backend PID: 6423
Excecuting 500000 PreparedStatements
Executing PreparedStatement 'ps_0'
Executing PreparedStatement 'ps_5000'
...
Executing PreparedStatement 'ps_495000'
Executing PreparedStatement 'ps_500000'
Executing DISCARD ALL;
Waiting 60 seconds, now its time to check backend growth!
Postgres log:
=============
2024-02-02 08:29:22.554 CET [6423] test@test LOG: ejecutar ps_499999: SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test@test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test@test LOG: ejecutar ps_500000: SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test@test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test@test LOG: sentencia: DISCARD ALL;
Ps aux output (memory growth):
==============================
$ date; ps aux | head -n 1; ps aux | grep 6423
vie 02 feb 2024 08:29:24 CET
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 6423 59.1 13.1 2358692 2105708 ? Ss 08:28 0:27 postgres: 14/main: test test 127.0.0.1(46084) idle
Product: PostgreSQL 14
Dear Technical Support Team,
We reach out to you to report an issue related to memory growth in PostgreSQL backend processes when running many Prepared Statements with different names, even though the "DISCARD ALL" command is executed at the end of the program execution.
We understand that while Prepared Statements are executed and maintained in the session, memory may grow since various objects need to be stored in the session, such as the parsed query, execution plans, etc.
However, what we don't understand is why, when the DISCARD ALL command is eventually executed, memory is not freed at all.
Could you please provide us with a more detailed explanation of this behavior? Additionally, we would like to know if there is any other specific action or configuration that we can perform to address this issue and ensure that backend memory is reduced after executing many "Named Prepared Statements".
We appreciate your attention and look forward to your guidance and suggestions for resolving this problem.
We have attached a small C program with libpq that demonstrates this issue, along with the program's output and the execution of the "ps aux" program.
Best regards,
Daniel Blanch Bataller
Hoplasoftware DBA
prepared_statement.c program
============================
/*
* prepared_statement.c
* This program demonstrates the backend memory growth using a large number
* of prepared statements, as expected.
* But surprisingly, after executing DISCARD ALL; memory is not recovered at all.
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <unistd.h>
#define ITERATIONS 500000
#define PRINT_TIMES 5000
#define SLEEP_AFTER_DISCARD_ALL 60
#define HOST "localhost"
#define PORT "5432"
#define DB "test"
#define USER "test"
#define PASS "test"
int main() {
// Connect to the database
const char *conninfo = "host=" HOST " port=" PORT " dbname=" DB " user=" USER " password=" PASS "";
printf("Connecting to %s\n", conninfo);
PGconn *conn = PQconnectdb(conninfo);
// Check connection result
if (PQstatus(conn) != CONNECTION_OK) {
fprintf(stderr, "Connection error: %s\n", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
// Get backend PID
printf("Getting backend PID \n");
PGresult *result = PQexec(conn, "SELECT pg_backend_pid();");
// Check result status
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "Error executing query: %s\n", PQerrorMessage(conn));
PQclear(result);
PQfinish(conn);
exit(EXIT_FAILURE);
}
// Get result
char *pid = PQgetvalue(result, 0, 0);
printf("Backend PID: %s\n", pid);
// Main loop
printf("Excecuting %d PreparedStatements\n", ITERATIONS);
for (int i = 0; i <= ITERATIONS; i++) {
// Prepare "Prepared Statement"
char stmt_name[50];
sprintf(stmt_name, "ps_%d", i);
const char *query = "SELECT 1 WHERE 1 = $1";
if (i % PRINT_TIMES == 0) printf("Executing PreparedStatement '%s'\n", stmt_name);
PGresult *prepare_result = PQprepare(conn, stmt_name, query, 1, NULL);
if (PQresultStatus(prepare_result) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error preparing the PreparedStatement: %s\n", PQresultErrorMessage(prepare_result));
PQclear(prepare_result);
PQfinish(conn);
exit(1);
}
// Preprared Statement parameters
const char *paramValues[] = {"1"};
// Execute Prepared Statement
PGresult *res = PQexecPrepared(conn, stmt_name, 1, paramValues, NULL, NULL, 0);
// Check Prepared Statement execution result
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr, "Error executing query: %s\n", PQresultErrorMessage(res));
PQclear(res);
PQfinish(conn);
exit(1);
}
// Get results
int numRows = PQntuples(res);
int numCols = PQnfields(res);
for (int i = 0; i < numRows; i++) {
for (int j = 0; j < numCols; j++) {
PQgetvalue(res, i, j); // Do nothing
}
}
// Free Result
PQclear(res);
}
// Execute discard all
printf("Executing DISCARD ALL;\n");
PGresult *discard_result = PQexec(conn, "DISCARD ALL;");
// Check result status
if (PQresultStatus(discard_result) != PGRES_COMMAND_OK) {
fprintf(stderr, "Error executing command: %s\n", PQerrorMessage(conn));
PQclear(discard_result);
PQfinish(conn);
exit(EXIT_FAILURE);
}
// Wait to check backend growth
printf("Waiting %d seconds, now its time to check backend growth!\n", SLEEP_AFTER_DISCARD_ALL);
sleep(SLEEP_AFTER_DISCARD_ALL);
// Close Connection
PQfinish(conn);
return 0;
}
program output:
===============
Connecting to host=localhost port=5432 dbname=test user=test password=test
Getting backend PID
Backend PID: 6423
Excecuting 500000 PreparedStatements
Executing PreparedStatement 'ps_0'
Executing PreparedStatement 'ps_5000'
...
Executing PreparedStatement 'ps_495000'
Executing PreparedStatement 'ps_500000'
Executing DISCARD ALL;
Waiting 60 seconds, now its time to check backend growth!
Postgres log:
=============
2024-02-02 08:29:22.554 CET [6423] test@test LOG: ejecutar ps_499999: SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test@test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test@test LOG: ejecutar ps_500000: SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test@test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test@test LOG: sentencia: DISCARD ALL;
Ps aux output (memory growth):
==============================
$ date; ps aux | head -n 1; ps aux | grep 6423
vie 02 feb 2024 08:29:24 CET
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 6423 59.1 13.1 2358692 2105708 ? Ss 08:28 0:27 postgres: 14/main: test test 127.0.0.1(46084) idle
El jue, 1 feb 2024 a las 14:51, Heikki Linnakangas (<hlinnaka@xxxxxx>) escribió:
On 01/02/2024 15:40, Daniel Blanch Bataller wrote:
> We have attached a small C program with libpq that demonstrates this
> issue, along with the program's output and the execution of the "ps aux"
> program.
There is no DISCARD ALL command in the test program you included. I can
see the DISCARD ALL in the log output, however. Perhaps you included a
wrong version of the test program?
In any case, it's notoriously hard to measure memory usage of backend
processes correctly. The resident size displayed by tools like 'ps' and
'top' includes shared memory, too, for example.
I'd recommend that you run the test much longer, and observe the memory
usage for a much longer period of time. I would expect it to eventually
stabilize at some reasonable level.
--
Heikki Linnakangas
Neon (https://neon.tech)
