Обсуждение: BUG #2576: tcp_keepalive doesn't work
The following bug has been logged online: Bug reference: 2576 Logged by: Fujii Masao Email address: fujii.masao@oss.ntt.co.jp PostgreSQL version: 8.1.4 Operating system: Fedora Core 5 Description: tcp_keepalive doesn't work Details: Hi. I found an error that tcp_keepalive doesn't work. The sequence of steps to reproduce the problem is as follow. ---------- [terminal 1] $ postmaster -p xxxx NOTE: - postgresql.conf that I changed from the default is as follow. listen_addresses = '*' tcp_keepalives_idle = 10 tcp_keepalives_interval = 5 tcp_keepalives_count = 2 - I add the Client Authentication Record to pg_hba.conf. host all all xx.xx.xx.xx/xx trust [terminal 2] $ pgbench testdb -c 50 -t 100 -h xx.xx.xx.xx -p xxxx [terminal 3] # /sbin/ifdown eth0 #ifdown while running pgbench Then, 50 postgres processes keep alive though tcp_keepalive should kill them within 30 seconds at latest. [terminal 4] $ sleep 30 $ ps -ef | grep postgres ... postgres 16815 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38738) idle in transaction postgres 16816 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38739) UPDATE waiting postgres 16817 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38740) UPDATE waiting postgres 16818 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38741) UPDATE waiting postgres 16819 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38742) UPDATE waiting postgres 16820 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38743) idle in transaction postgres 16821 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38744) UPDATE waiting postgres 16822 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38745) UPDATE waiting postgres 16823 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb xx.xx.xx.xx(38746) idle ... ---------- I'll be pleased if you cope with the problem. Thanks in advance.
"Fujii Masao" <fujii.masao@oss.ntt.co.jp> writes: > I found an error that tcp_keepalive doesn't work. You seem to have a misunderstanding of what tcp_keepalive is for. It does not kill a backend that is in the midst of a query. A backend will terminate when it is waiting for a client command and it sees that the connection has been lost --- which is what a TCP timeout will cause to happen. But your example backends are not waiting for client commands. If you want backends to abort live queries before they finish, use statement_timeout. regards, tom lane
Hi. > You seem to have a misunderstanding of what tcp_keepalive is for. It > does not kill a backend that is in the midst of a query. A backend will > terminate when it is waiting for a client command and it sees that the > connection has been lost --- which is what a TCP timeout will cause to > happen. But your example backends are not waiting for client commands. No. In my example, there are backends that are waiting for client commands. Please pay attention to the result of 'ps -ef | grep postgres'. >> [terminal 4] >> $ sleep 30 >> $ ps -ef | grep postgres >> ... >> postgres 16815 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38738) idle in transaction >> postgres 16816 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38739) UPDATE waiting >> postgres 16817 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38740) UPDATE waiting >> postgres 16818 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38741) UPDATE waiting >> postgres 16819 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38742) UPDATE waiting >> postgres 16820 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38743) idle in transaction >> postgres 16821 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38744) UPDATE waiting >> postgres 16822 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38745) UPDATE waiting >> postgres 16823 16782 0 17:06 pts/1 00:00:00 postgres: postgres sampledb >> xx.xx.xx.xx(38746) idle >> ... 'idle in transaction' backends don't terminate though they are waiting for a client command and connection has been lost. By the way, I ran strace for 'idle in transaction' backend. ----- $ strace -p xxx Process xxx attached - interrupt to quit recv(7, ----- best regards;
Hi. I found the cause of the error that tcp_keepalive doesn't work. The cause is a specification of linux kernel. In the specification of linux kernel, tcp_keepalive doesn't work if the network outage occurs before receiving ACK for send() system-call. This behavior of tcp_keepalive is reported even by LKML. Linux-Kernel Archive: Re: 2.6.12.5 bug? per-socket TCP keepalive settings http://www.ussg.iu.edu/hypermail/linux/kernel/0508.2/0757.html I'm confused whether tcp_keepalive problem should be solved at the DB level. regards;
Fujii Masao <fujii.masao@oss.ntt.co.jp> writes: > Linux-Kernel Archive: Re: 2.6.12.5 bug? per-socket TCP keepalive settings > http://www.ussg.iu.edu/hypermail/linux/kernel/0508.2/0757.html > I'm confused whether tcp_keepalive problem should be solved > at the DB level. According to that, Linux keepalive starts working once you have either sent or received at least one byte over the connection. Therefore it's not possible to get past the authentication stage without keepalive being ready to go. And we do have a pretty short timeout on the auth stage (1 minute if memory serves). So I'm not seeing what problem we need to solve. In any case, if you don't like that behavior methinks you need to be lobbying some kernel hackers, not database weenies. Postgres is not in the business of second-guessing the TCP stack. regards, tom lane
Tom Lane wrote: > According to that, Linux keepalive starts working once you have either > sent or received at least one byte over the connection. Therefore it's > not possible to get past the authentication stage without keepalive > being ready to go. And we do have a pretty short timeout on the auth > stage (1 minute if memory serves). Do you mean authentication_timeout? It's useless in my example because 'idle in transaction' backends have already gotten past the auth stage. > So I'm not seeing what problem we > need to solve. Please consider the system that puts the load from two AP servers to one DB server. At this time, the problem is that the processing of overall DB server might stop when the LAN cable of only one AP server comes off. Because 'idle in transaction' backends that don't terminate by tcp_keepalive keep holding the lock. > > In any case, if you don't like that behavior methinks you need to be > lobbying some kernel hackers, not database weenies. Postgres is not > in the business of second-guessing the TCP stack. ok. I also think that solving at the Kernel level is the best in this problem. best regards;
Fujii Masao <fujii.masao@oss.ntt.co.jp> writes: > Because 'idle in transaction' backends that don't terminate by tcp_keepalive > keep holding the lock. If a backend has gotten to the point of being 'idle in transaction', then it's certainly exchanged data with the client, so TCP keepalive should work. I suggest you need to file a kernel bug report if it doesn't. regards, tom lane