Weird performance differences between cloud vendors
Hi,
I have run a test with pgbench against two cloud vendors (settings,
parameters almost the same).
Both Postgres (or whatever they do internally when they call it as
Postgres offering, NOT Aurora or so :-) )
I have got a strange result that cloud vendor 1 is performing almost
everywhere better in matter of
read and write but except in the init phase of pgbench it took almost
double the time.
/pgbench -i -IdtGvp -s 3000 "${PG_DATABASE}"/
/pgbench -c 50 -j 10 -P 60 -r -T 3600 "${PG_DATABASE}"/
| Metric | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud
vendor 2 (small) | cloud vendor 2 (large) |
|--|||---|---|
| **Initialization Time** | 60m52.932s | 3h0m8.97s | 32m7.154s |
5h14m16s | | **Benchmark Duration** | 3600s (1 hour) | 3600s (1 hour) |
3600s (1 hour) | 3600s (1 hour) | | **Transactions per Second** |
399.460720 | 9833.737455 | 326.551036 | 3314.363264 | | **Latency
Average (ms)** | 125.124 | 6.507 | 153.106 | 19.309 | | **Latency StdDev
(ms)** | 154.483 | 44.403 | 59.522 | 4.015 | | **Initial Connection Time
(ms)** | 557.696 | 174.318 | 1688.474 | 651.087 | | **Transactions
Processed** | 1,438,437 | 35,400,215 | 1,175,081 | 11,929,631 | |
Statement (ms) | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud
vendor 2 (small) | cloud vendor 2 (large) |
|-|||---|---|
| BEGIN | 8.599 | 0.545 | 9.008 | 1.398 | | UPDATE pgbench_accounts |
38.648 | 2.031 | 27.124 | 4.722 | | SELECT pgbench_accounts | 12.332 |
0.676 | 17.922 | 1.798 | | UPDATE pgbench_tellers | 17.275 | 0.853 |
20.843 | 1.831 | | UPDATE pgbench_branches | 18.478 | 0.862 | 21.941 |
1.743 | | INSERT INTO pgbench_history | 16.613 | 0.827 | 18.710 | 1.501
| | END | 13.177 | 0.708 | 37.553 | 6.317 |
Of course no one knows the magig underneath what some cloud vendors are
doing underneath but does anyone have some
ideas what the reason could be or how I could do better testing to find
this out?
Cheers
Dirk
Re: Weird performance differences between cloud vendors
On Thu, 2024-02-01 at 10:23 +0100, Dirk Krautschick wrote: > I have run a test with pgbench against two cloud vendors (settings, > parameters almost the same). > Both Postgres (or whatever they do internally when they call it as Postgres > offering, NOT Aurora or so :-) ) > > I have got a strange result that cloud vendor 1 is performing almost > everywhere better in matter of > read and write but except in the init phase of pgbench it took almost double > the time. Nobody except those vendors could tell you for certain, but perhaps on the one system the initial data load is fast, because you have not yet exceeded your I/O quota, and then I/O is throttled. Yours, Laurenz Albe
huge SubtransSLRU and SubtransBuffer wait_event
Hi, We have a Postgresqlv14.8 server, client use Postgresql JDBC connections, today, our server see a lot of "SubtransBuffer" and "SubtransSLRU" wait_event. Could you help direct me what's the possible cause and how to resolve this waits ? Thanks, James
Re: huge SubtransSLRU and SubtransBuffer wait_event
On Thu, 2024-02-01 at 11:50 +, James Pang (chaolpan) wrote: > We have a Postgresqlv14.8 server, client use Postgresql JDBC connections, > today, > our server see a lot of “SubtransBuffer” and “SubtransSLRU” wait_event. > Could you help direct me what’s the possible cause and how to resolve this > waits ? Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction. Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell. Yours, Laurenz Albe
Memory growth using many named prepared statements, in spite of using DISCARD ALL afterwards.
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
#include
#include
#include
#define ITERATIONS 5
#define PRINT_TIMES 5000
#define HOST "localhost"
#define PORT ""
#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);
}
// Close Connection
PQfinish(conn);
return 0;
}
./prepared_statement output:
Connecting to host=localhost port= dbname=test user=test password=test
Getting backend PID
Backend PID: 40690
Excecuting 5 PreparedStatements
Executing PreparedStatement 'ps_0'
Executing PreparedStatement 'ps_5000'
Executing PreparedStatement 'ps_1'
Executing PreparedStatement 'ps_15000'
Executing PreparedStatement 'ps_2'
Executing PreparedStatement 'ps_25000'
Executing PreparedStatement 'ps_3'
Executing PreparedStatement 'ps_35000'
Executing PreparedStatement 'ps_4'
Execu
Re: Memory growth using many named prepared statements, in spite of using DISCARD ALL afterwards.
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)
RE: huge SubtransSLRU and SubtransBuffer wait_event
Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction. Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell. You mean extensions to simulate a subtransaction like pg_background ? for JDBC driver option to simulate statement level rollback, could you share more details ? Thanks, James -Original Message- From: Laurenz Albe Sent: Thursday, February 1, 2024 8:42 PM To: James Pang (chaolpan) ; [email protected] Subject: Re: huge SubtransSLRU and SubtransBuffer wait_event On Thu, 2024-02-01 at 11:50 +, James Pang (chaolpan) wrote: > We have a Postgresqlv14.8 server, client use Postgresql JDBC > connections, today, our server see a lot of “SubtransBuffer” and > “SubtransSLRU” wait_event. > Could you help direct me what’s the possible cause and how to resolve this > waits ? Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction. Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell. Yours, Laurenz Albe
RE: huge SubtransSLRU and SubtransBuffer wait_event
Our case is 1) we use PL/PGSQL procedure1-->procedure2 (update table ;commit); 2) application JDBC client call procedure1 (it's a long running job, sometimes it could last > 1hours). During this time window, other Postgresql JDBC clients (100-200) coming in in same time , then quickly see MultiXactoffset and SubtransSLRU increased very quickly. Possible to increase Subtrans SLRU buffer size ?PL/PGSQL proc1--> procedure2(updates table) it use substransation in procedure2 ,right? Thanks, James -Original Message- From: James Pang (chaolpan) Sent: Thursday, February 1, 2024 11:34 PM To: Laurenz Albe ; [email protected] Subject: RE: huge SubtransSLRU and SubtransBuffer wait_event Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction. Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell. You mean extensions to simulate a subtransaction like pg_background ? for JDBC driver option to simulate statement level rollback, could you share more details ? Thanks, James -Original Message- From: Laurenz Albe Sent: Thursday, February 1, 2024 8:42 PM To: James Pang (chaolpan) ; [email protected] Subject: Re: huge SubtransSLRU and SubtransBuffer wait_event On Thu, 2024-02-01 at 11:50 +, James Pang (chaolpan) wrote: > We have a Postgresqlv14.8 server, client use Postgresql JDBC > connections, today, our server see a lot of “SubtransBuffer” and > “SubtransSLRU” wait_event. > Could you help direct me what’s the possible cause and how to resolve this > waits ? Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction. Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell. Yours, Laurenz Albe
Re: Memory growth using many named prepared statements, in spite of using DISCARD ALL afterwards.
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 50 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
#include
#include
#include
#define ITERATIONS 50
#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 (PQresultStat
