From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | armand pirvu <armand(dot)pirvu(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: connection dropped from the backend server |
Date: | 2018-03-28 04:11:04 |
Message-ID: | 1e86e8f7-7e7a-1ae4-e3f0-d703eb13be0f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 03/27/2018 07:21 PM, armand pirvu wrote:
>
>> On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
>> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>>
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> writes:
>>> On 03/27/2018 04:07 PM, armand pirvu wrote:
>>>> As long as the connection stays up yes data gets fine across
>>>> In pg_stat_activity I see the node ip address where tail -f piped
>>>> into psql happens
>>
>>> So what does the rest of that record show? In particular for:
>>
>> I wonder how often data gets put into the pipe. If it's "not very often",
>> maybe the connection from psql to the server is timing out due to
>> inactivity? This would be the fault of a firewall or something in
>> between. You could probably fix it by enabling (more aggressive) TCP
>> keepalive settings.
>>
>> regards, tom lane
>
> Well there is no flow pattern, The flow can be inexistent for days ,
> even weeks and then it can get super busy
>
> The data flows as expected well untill the connection gets dropped.
> Bolded from pg_stat_activity (a test I just did)
>
> birstdb=# select datname, pid, client_addr, client_port, backend_start,
> query_start, state from pg_stat_Activity;
> datname | pid | client_addr | client_port | backend_start
> | query_start | state
> ---------+-------+---------------+-------------+-------------------------------+-------------------------------+--------
> birstdb | 10046 | | -1 | 2018-03-27
> 20:40:11.721804-05 | 2018-03-27 20:47:27.118434-05 | active
> * birstdb | 10082 | 192.168.1.187 | 55859 | 2018-03-27
> 20:43:55.301216-05 | 2018-03-27 20:46:07.190064-05 | idle*
>
>
> ps -fu armandp
> UID PID PPID C STIME TTY TIME CMD
> armandp 3264 3263 0 20:39:13 pts/2 0:00 tail -f
> /u1/sys_admin/dba/ingres2birst.fifo
> armandp 3265 3263 0 20:39:13 pts/2 0:00 psql -U csidba -d
> birstdb -h 172.16.10.93
>
>
>
> Give it about 10 min at most and bam out it goes
>
> birstdb=# select datname, pid, client_addr, client_port, backend_start,
> query_start, state from pg_stat_Activity;
> datname | pid | client_addr | client_port | backend_start
> | query_start | state
> ---------+-------+-------------+-------------+-------------------------------+-------------------------------+--------
> birstdb | 10208 | | -1 | 2018-03-27
> 20:51:25.835382-05 | 2018-03-27 21:08:47.164249-05 | active
>
> Although the above two processes are still out
>
> I think the tcp keep alives might help but I am also thinking like a
> each min check maybe and if things got in the pipe well dump ‘em to
> Postgres. Something along these lines
>
> Any ideas/suggestions you might have to improve this ? I am not saying
Yeah, dump the named pipe idea and just create the connection for the
duration of the DML event.
> it is perfect far from it, but I kinda took the model/idea from the
> Nagios named pipe only that one too runs at x seconds/minutes interval
> defined
>
>
>
> Thank you both
> — Armand
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Petrea | 2018-03-28 05:02:30 | pg_dump -Fd -j2 on standby in 9.6.6 |
Previous Message | armand pirvu | 2018-03-28 03:07:12 | Re: connection dropped from the backend server |