Обсуждение: BUG #14106: Large memory client and server consumption in the insert of big values.
BUG #14106: Large memory client and server consumption in the insert of big values.
От
Nikolay.Nikitin@infowatch.com
Дата:
The following bug has been logged on the website: Bug reference: 14106 Logged by: Nikolay Nikitin Email address: Nikolay.Nikitin@infowatch.com PostgreSQL version: 9.5.2 Operating system: Red Hat server 6.7 Description: Hi, If I insert big value with X size by using C and libpq then client takes 2X memory and server process takes 370M (empty session process memory size) + 4X. The 1G value insert demands 6G + 370M if the client works on same server as postgres. It is very big. I think there are two ways to solve this problem: 1. The best way is modify client libpq interface to support server prepared statements and its parameter binding before execution. Also in this parameter binding a partial loading is needed of text and bytea parameters same as large object partial loading. We can not use large objects because our data is partitioned in many tablespaces. 2. Second way is the reducing memory consumption in the existing code. I do not understand why client takes one size of parameter in addition. And 4X server process memory consumption is very surprisingly. This bug intersects with BUG #14100: Large memory consumption in a partitioning insert of great values. But that bug is about internal server process memory taking. This bug is about client-server memory consumption. sample of code: Table: create table test_insert ( b bytea ); C code: int main(int argc, char **argv) { PGconn *conn; PGresult *res; conn = PQconnectdb("user='postgres' password='...' host='192.168.198.20' port='5432' dbname='postgres'"); int buffer_size = 500 * 1024 * 1024; char *buffer = malloc(buffer_size); FILE* fd = fopen("some_file_path", "rb"); fread(buffer, buffer_size, 1, fd); fclose(fd); const char *paramValues[1]; int paramLengths[1]; int paramFormats[1]; paramValues[0] = buffer; paramLengths[0] = buffer_size; paramFormats[0] = 1; res = PQexecParams(conn, "insert into test_insert(b) values ($1)", 1, NULL, paramValues, paramLengths, paramFormats, 1); PQclear(res); free(buffer); PQfinish(conn); printf("Ok"); return 0; } Best regards, Nikitin Nikolay.