Обсуждение: connection dropped from the backend server
Hi all I have a process of some data manipulation and ultimate transfer to a postgres database A DML statement gest concoted with the transformed data and pusshed into a named pipe The named pipe is tailed -f in the background like this nohup $SHELL <<EOF & tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost EOF All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activitythere is really nothing the host I run the nohuped tail Any suggestions how to approach this/make it better/monitor ? Thanks -- Armand
On 03/27/2018 03:36 PM, armand pirvu wrote: > Hi all > > > I have a process of some data manipulation and ultimate transfer to a postgres database > A DML statement gest concoted with the transformed data and pusshed into a named pipe > The named pipe is tailed -f in the background like this > > nohup $SHELL <<EOF & > tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost > EOF > > All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activitythere is really nothing the host I run the nohuped tail Could it be that pg_stat_activity shows nothing because the DML has completed when you look? Does the data find its way into the database? > > > Any suggestions how to approach this/make it better/monitor ? > > Thanks > -- Armand > > > -- Adrian Klaver adrian.klaver@aklaver.com
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 Sent from my iPhone > On Mar 27, 2018, at 6:03 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >> On 03/27/2018 03:36 PM, armand pirvu wrote: >> Hi all >> I have a process of some data manipulation and ultimate transfer to a postgres database >> A DML statement gest concoted with the transformed data and pusshed into a named pipe >> The named pipe is tailed -f in the background like this >> nohup $SHELL <<EOF & >> tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost >> EOF >> All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activitythere is really nothing the host I run the nohuped tail > > Could it be that pg_stat_activity shows nothing because the DML has completed when you look? > > Does the data find its way into the database? > >> Any suggestions how to approach this/make it better/monitor ? >> Thanks >> -- Armand > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
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: state query backend_start and any others you might think are important from here: https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW > > > > Sent from my iPhone > >> On Mar 27, 2018, at 6:03 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >>> On 03/27/2018 03:36 PM, armand pirvu wrote: >>> Hi all >>> I have a process of some data manipulation and ultimate transfer to a postgres database >>> A DML statement gest concoted with the transformed data and pusshed into a named pipe >>> The named pipe is tailed -f in the background like this >>> nohup $SHELL <<EOF & >>> tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost >>> EOF >>> All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activitythere is really nothing the host I run the nohuped tail >> >> Could it be that pg_stat_activity shows nothing because the DML has completed when you look? >> >> Does the data find its way into the database? >> >>> Any suggestions how to approach this/make it better/monitor ? >>> Thanks >>> -- Armand >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.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
On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Adrian Klaver <adrian.klaver@aklaver.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 happensSo 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 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
Sent from my iPhone
On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Adrian Klaver <adrian.klaver@aklaver.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 happensSo 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 laneWell there is no flow pattern, The flow can be inexistent for days , even weeks and then it can get super busyThe 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 | activebirstdb | 10082 | 192.168.1.187 | 55859 | 2018-03-27 20:43:55.301216-05 | 2018-03-27 20:46:07.190064-05 | idleps -fu armandpUID PID PPID C STIME TTY TIME CMDarmandp 3264 3263 0 20:39:13 pts/2 0:00 tail -f /u1/sys_admin/dba/ingres2birst.fifoarmandp 3265 3263 0 20:39:13 pts/2 0:00 psql -U csidba -d birstdb -h 172.16.10.93Give it about 10 min at most and bam out it goesbirstdb=# 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 | activeAlthough the above two processes are still outI 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 linesAny ideas/suggestions you might have to improve this ? I am not saying 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 definedThank you both— Armand
Sorry for the double post but as a possible solution. Why not move the named pipe to the postgres host and simply whatever i was dumping into said pipe instead of doing locally just doing over ssh
What do you think ?
Thank you
-- Armand
On 03/27/2018 07:21 PM, armand pirvu wrote: > >> On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us >> <mailto:tgl@sss.pgh.pa.us>> wrote: >> >> Adrian Klaver <adrian.klaver@aklaver.com >> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com