PG 數(shù)據(jù)庫庫監(jiān)聽隊列的長度問題

發(fā)布時間：2020-08-09 18:01:59 來源：ITPUB博客閱讀：221 作者：babyyellow 欄目：建站服務(wù)器

不論mysql 還是pg 數(shù)據(jù)庫都通過監(jiān)聽某個ip/端口, 或者某個socket 來實(shí)現(xiàn)通訊.
這里涉及到一個問題,就是這個監(jiān)聽隊列的長度問題.

mysql  是自己實(shí)現(xiàn)的,  在my.cnf 里有個配置選項  back_log 這就是設(shè)置監(jiān)聽隊列的長度的.

PG 數(shù)據(jù)庫的監(jiān)聽隊列的長度, 似乎沒有地方可以設(shè)置.

在做一個pgbench 的高并發(fā)壓力測試的時候,似乎出現(xiàn)這個問題.

命令:
pgbench -n -r -c 250 -j 250 -T 2 -f update_smallrange.sql

錯誤消息：
Connection to database "" failed:
could not connect to server: Resource temporarily unavailable
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

但是從上面的“Resource temporarily unavailable”看不出是哪個資源出問題了。
經(jīng)過調(diào)查，找到了下面一個鏈接
http://www.postgresql.org/message-id/20130617141622.GH5875@alap2.anarazel.de

[code]
From:Andres Freund To:pgsql-hackers(at)postgresql(dot)orgSubject:PQConnectPoll, connect(2), EWOULDBLOCK and somaxconnDate:2013-06-17 14:16:22Message-ID:20130617141622.GH5875@alap2.anarazel.de (view raw, whole thread or download thread mbox)Thread: 2013-06-17 14:16:22 from Andres Freund   2013-06-26 11:22:58 from Andres Freund 2013-06-26 16:07:54 from Tom Lane 2013-06-26 18:12:00 from Andres Freund    2013-06-27 00:07:40 from Tom Lane    2013-06-27 06:17:57 from Andres Freund    2013-06-27 13:48:25 from Tom Lane    2013-06-27 16:42:47 from Tom Lane    Lists:pgsql-hackersHi,

When postgres on linux receives connection on a high rate client
connections sometimes error out with:
could not send data to server: Transport endpoint is not connected
could not send startup packet: Transport endpoint is not connected

To reproduce start something like on a server with sufficiently high
max_connections:
pgbench -h /tmp -p 5440 -T 10 -c 400 -j 400 -n -f /tmp/simplequery.sql

Now that's strange since that error should happen at connect(2) time,
not when sending the startup packet. Some investigation led me to
fe-secure.c's PQConnectPoll:

if (connect(conn->sock, addr_cur->ai_addr,
                     addr_cur->ai_addrlen) < 0)
{
if (SOCK_ERRNO == EINPROGRESS ||
      SOCK_ERRNO == EWOULDBLOCK ||
      SOCK_ERRNO == EINTR ||
      SOCK_ERRNO == 0)
{
      /*
      * This is fine - we're in non-blocking mode, and
      * the connection is in progress.  Tell caller to
      * wait for write-ready on socket.
      */
      conn->status = CONNECTION_STARTED;
      return PGRES_POLLING_WRITING;
}
/* otherwise, trouble */
}

So, we're accepting EWOULDBLOCK as a valid return value for
connect(2). Which it isn't. EAGAIN in contrast is on some BSDs and on
linux. Unfortunately POSIX allows those two to share the same value...

My manpage tells me:
EAGAIN No more free local ports or insufficient entries in the routing cache.  For
   AF_INET see the description of
   /proc/sys/net/ipv4/ip_local_port_range ip(7)
   for information on how to increase the number of local
   ports.

So, the problem is that we took a failed connection as having been
initially successfull but in progress.

Not accepting EWOULDBLOCK in the above if() results in:
could not connect to server: Resource temporarily unavailable
   Is the server running locally and accepting
   connections on Unix domain socket "/tmp/.s.PGSQL.5440"?

which makes more sense.

Trivial patch attached.

Now, the question is why we cannot complete connections on unix sockets?
Some code reading reading shows net/unix/af_unix.c:unix_stream_connect()
shows:
if (unix_recvq_full(other)) {
err = -EAGAIN;
if (!timeo)
goto out_unlock;
So, if we're in nonblocking mode - which we are - and the receive queue
is full we return EAGAIN. The receive queue for unix sockets is defined
as
static inline int unix_recvq_full(struct sock const *sk)
{
return skb_queue_len(&sk->sk_receive_queue) > sk->sk_max_ack_backlog;
}
Where sk_max_ack_backlog is whatever has been passed to the
listen(backlog) on the listening side.

Question: But postgres does listen(fd, MaxBackends * 2), how can that be
a problem?
Answer:
   If the backlog argument is greater than the value in /proc/sys/net/core/somaxconn,
   then  it  is  silently  truncated to that value; the default value in this file is
   128.  In kernels before 2.4.25, this limit was a hard coded value, SOMAXCONN, with
   the value 128.

Setting somaxconn to something higher indeed makes the problem go away.

I'd guess that pretty much the same holds true for tcp connections,
although I didn't verify that which would explain some previous reports
on the lists.

TLDR: Increase /proc/sys/net/core/somaxconn

Greetings,

Andres Freund

--
Andres Freund                      http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[/code]

原來是PG服務(wù)端的listen backlog(受內(nèi)核參數(shù)somaxconn限制)不夠用了，somaxconn的默認(rèn)值是128，調(diào)大后，重啟PG再測就OK了。

   /proc/sys/net/core/somaxconn
            This  file  defines a ceiling value for the backlog argument of listen(2); see the listen(2) manual page
            for details.

到這里解決方案就很明了了,

echo 256 > /proc/sys/net/core/somaxconn

然后重新啟動pg 繼續(xù)進(jìn)行就ok 了.

向AI問一下細(xì)節(jié)

PG 數(shù)據(jù)庫庫監(jiān)聽隊列的長度問題

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽