postgresql потребляет больше памяти на сервере БД для продолжительного соединения

Question

У нас есть приложение на сервере C++, которое подключается к базе данных postgresql с помощью библиотеки libpq. Приложение создает сотни соединений с базой данных, и большую часть времени жизни соединения составляет область применения.

Первоначально приложение работало нормально, но в течение некоторого времени сервер postgres занимал больше памяти для длительных соединений. Написав приведенный ниже пример программы, я узнал, что создание подготовленных операторов с использованием PQsendPrepare и PQsendQueryPrepared вызывает проблему потребления памяти на сервере базы данных.

Как мы можем исправить эту проблему с памятью сервера? есть ли функция libpq для освобождения памяти на сервере?

#include <iostream>
#include <fstream>
#include <string>
#include <sstream>
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main(int argc, char *argv[]) {

    const int LEN = 10;
    const char *paramValues[1];

    int paramFormats[1];
    int rowId = 7369;
    Oid paramTypes[1];
    char str[LEN];
    snprintf(str, LEN, "%d", rowId);
    paramValues[0] = str;
    paramTypes[0]=20;
    paramFormats[0]=0;
    long int c=1;

    PGresult* result;
    //PGconn *conn = PQconnectdb("user=scott dbname=dame");
    PGconn *conn = PQsetdbLogin ("", "", NULL, NULL, "dame", "scott", "tiger") ;

    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
    do_exit(conn);
    }
    char *stm = "SELECT coalesce(ename,'test') from emp where empno=$1";
    for(;;)
    {
        std::stringstream strStream ; 
        strStream << c++ ;
        std::string strStatementName = "s_" + strStream.str() ;
        if(PQsendPrepare(conn,strStatementName.c_str(), stm,1,paramTypes) )
        {
            result = PQgetResult(conn); 
            if (PQresultStatus(result) != PGRES_COMMAND_OK)
            {
                PQclear(result) ;
                result = NULL ;
                do
                {
                    result = PQgetResult(conn);
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                } while (result != NULL) ;
                std::cout<<"error prepare"<<PQerrorMessage (conn)<<std::endl;
                break;
            }
            PQclear(result) ;
            result = NULL ;
            do
            {
                result = PQgetResult(conn);
                if(result != NULL)
                {
                    PQclear (result) ;
                }
            } while (result != NULL) ;
        }
        else
        {
            std::cout<<"error:"<<PQerrorMessage (conn)<<std::endl;
            break;
        }

        if(!PQsendQueryPrepared(conn,
                strStatementName.c_str(),1,(const char* const *)paramValues,paramFormats,paramFormats,0))
        {
            std::cout<<"error:prepared "<<PQerrorMessage (conn)<<std::endl;
        }
        if (!PQsetSingleRowMode(conn))
        {
            std::cout<<"error singrow mode "<<PQerrorMessage (conn)<<std::endl;
        }
    result = PQgetResult(conn);
        if (result != NULL)
        {
            if((PGRES_FATAL_ERROR == PQresultStatus(result)) || (PGRES_BAD_RESPONSE == PQresultStatus(result)))
            {
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn);
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                } while (result != NULL) ;
                break;
            }

            if (PQresultStatus(result) == PGRES_SINGLE_TUPLE)
            {
                std::ofstream myfile;
            myfile.open ("native.txt",std::ofstream::out |     std::ofstream::app);
                myfile << PQgetvalue(result, 0, 0)<<"\n";
                myfile.close();
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn) ;
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                }
                while(result != NULL) ;
                sleep(10);
            }
            else if(PQresultStatus(result) == PGRES_TUPLES_OK || PQresultStatus(result) ==  PGRES_COMMAND_OK)
            {
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn) ;
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                }
                while(result != NULL) ;
            }
       }

    }

    PQfinish(conn);
    return 0;

}

Answer 1

Первоначально приложение работало нормально, но в течение некоторого времени сервер postgres занимал больше памяти для длительных соединений. Написав приведенный ниже пример программы, я узнал, что создание подготовленных операторов с использованием PQsendPrepare и PQsendQueryPrepared вызывает проблему потребления памяти на сервере базы данных.

Ну, это не удивительно. Вы генерируете новое подготовленное имя оператора на каждой итерации вашего внешнего цикла, а затем создаете и выполняете подготовленный оператор с этим именем. Все полученные подготовленные операторы с разными именами действительно будут оставаться в памяти сервера, пока соединение открыто. Это намеренно.

Как мы можем исправить эту проблему с памятью сервера?

Я бы охарактеризовал это как проблему логики программы, а не проблему памяти сервера, по крайней мере, в том, что касается тестовой программы. Вы получаете ресурсы (подготовленные заявления), а затем позволяете им зависать, когда вы больше не используете их. Сами по себе операторы не просочились, так как вы можете воссоздать сгенерированные алгоритмом имена операторов, но проблема похожа на утечку ресурсов. В вашей программе, а не в Postgres.

Если вы хотите использовать одноразовые подготовленные операторы, то дайте им пустую строку, "", как их зовут. Постгрес называет эти "неназванные" заявления. Каждое безымянное утверждение, которое вы подготовите, заменит любое предыдущее, принадлежащее тому же соединению.

Но даже это взломать. Наиболее важной особенностью подготовленных заявлений, в первую очередь, является то, что они могут быть использованы повторно. Каждое утверждение, подготовленное вашей тестовой программой, идентично, поэтому вы не только тратите память, вы также тратите циклы процессора. Вы должны подготовить его только один раз - через PQsendPrepare() или, может быть, просто PQprepare() - и когда он будет успешно подготовлен, выполняйте его столько раз, сколько хотите PQsendQueryPrepared() или же PQqueryPrepared(), передавая одно и то же имя оператора каждый раз (но, возможно, разные параметры).

есть ли функция libpq для освобождения памяти на сервере?

Документация для синхронных версий функций запроса гласит:

Подготовленные заявления для использования с PQexecPrepared также может быть создан путем выполнения операторов SQL PREPARE. Кроме того, хотя нет функции libpq для удаления подготовленного оператора, оператор SQL DEALLOCATE может использоваться для этой цели.

Насколько я понимаю, в Postgres есть только один вид подготовленного оператора, который используется как синхронными, так и асинхронными функциями. Так что нет, libpq не предоставляет функции специально для отбрасывания подготовленных операторов, связанных с соединением, но вы можете написать оператор на SQL, чтобы выполнить эту работу. Конечно, было бы бессмысленно создавать новый подготовленный оператор с уникальным именем для выполнения такого оператора.

Большинству программ не нужно располагать таким большим количеством четко подготовленных утверждений, чтобы создать проблему, о которой вы сообщаете.