2

连接数据库报too many clients already疑惑

 2 years ago
source link: https://www.oschina.net/question/4584830_2323427
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

连接数据库报too many clients already疑惑

数据库在连接时失败并报出too many clients already,原因是连接过多,超过PG库设置的最大连接数。但是我在这里存在一个疑惑,它这个报错触发的条件,我查看了PG12.1的源码,部分如下

按照这个逻辑,too many clients already应该是regular backends, autovac workers, walsenders
和background workers的子进程数量和大于或者等于 2 * (MaxConnections + autovacuum_max_workers + 1 + max_wal_senders + max_worker_processes)的值才会输出的。所以我的疑惑是,究竟是clients的连接超过了max_connections,还是按这个逻辑来的,抑或是我少看了哪一部分或者理解有误。

    /*
     * If we're going to reject the connection due to database state, say so
     * now instead of wasting cycles on an authentication exchange. (This also
     * allows a pg_ping utility to be written.)
     */
    switch (port->canAcceptConnections)
    {
        case CAC_STARTUP:
            ereport(FATAL,
                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),
                     errmsg("the database system is starting up")));
            break;
        case CAC_SHUTDOWN:
            ereport(FATAL,
                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),
                     errmsg("the database system is shutting down")));
            break;
        case CAC_RECOVERY:
            ereport(FATAL,
                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),
                     errmsg("the database system is in recovery mode")));
            break;
        case CAC_TOOMANY:
            ereport(FATAL,
                    (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
                     errmsg("sorry, too many clients already")));
            break;
        case CAC_WAITBACKUP:
            /* OK for now, will check in InitPostgres */
            break;
        case CAC_OK:
            break;
    }
    return STATUS_OK;
}
这里匹配到CAC_TOOMANY所以输出了sorry, too many clients already

    /*
     * Don't start too many children.
     *
     * We allow more connections here than we can have backends because some
     * might still be authenticating; they might fail auth, or some existing
     * backend might exit before the auth cycle is completed.  The exact
     * MaxBackends limit is enforced when a new backend tries to join the
     * shared-inval backend array.
     *
     * The limit here must match the sizes of the per-child-process arrays;
     * see comments for MaxLivePostmasterChildren().
     */
    if (CountChildren(BACKEND_TYPE_ALL) >= MaxLivePostmasterChildren())
        result = CAC_TOOMANY;
    return result;
}
这里在满足(CountChildren(BACKEND_TYPE_ALL) >= MaxLivePostmasterChildren()),才返回了这个结果。所以这个条件,应该是触发打印出sorry, too many clients already的条件

/*
 * Count up number of child processes of specified types (dead_end children
 * are always excluded).
 */
static int
CountChildren(int target)
{
    dlist_iter    iter;
    int            cnt = 0;
    dlist_foreach(iter, &BackendList)
    {
        Backend    *bp = dlist_container(Backend, elem, iter.cur);
        if (bp->dead_end)
            continue;
        /*
         * Since target == BACKEND_TYPE_ALL is the most common case, we test
         * it first and avoid touching shared memory for every child.
         */
        if (target != BACKEND_TYPE_ALL)
        {
            /*
             * Assign bkend_type for any recently announced WAL Sender
             * processes.
             */
            if (bp->bkend_type == BACKEND_TYPE_NORMAL &&
                IsPostmasterChildWalSender(bp->child_slot))
                bp->bkend_type = BACKEND_TYPE_WALSND;
            if (!(target & bp->bkend_type))
                continue;
        }
        cnt++;
    }
    return cnt;
}
/*
 * MaxLivePostmasterChildren
 *
 * This reports the number of entries needed in per-child-process arrays
 * (the PMChildFlags array, and if EXEC_BACKEND the ShmemBackendArray).
 * These arrays include regular backends, autovac workers, walsenders
 * and background workers, but not special children nor dead_end children.
 * This allows the arrays to have a fixed maximum size, to wit the same
 * too-many-children limit enforced by canAcceptConnections().  The exact value
 * isn't too critical as long as it's more than MaxBackends.
 */
int
MaxLivePostmasterChildren(void)
{
    return 2 * (MaxConnections + autovacuum_max_workers + 1 +
                max_wal_senders + max_worker_processes);
}

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK