Обсуждение: Hungry postmaster
Hi! At the moment on my Linux box I have process 'postmaster' eats all CPU. It corresponds with connection closed day before - not sure if it was normal disconnect. strace shows the process constantly makes 'send' syscall with EPIPE result. Any ideas how to trace this issue and/or prevent such thing in the future? Pg - 8.0.0 Kernel - 2.4.20 -- Best regards Ilja Golshtein
"Ilja Golshtein" <ilejn@yandex.ru> writes:
> At the moment on my Linux box I have process
> 'postmaster' eats all CPU. It corresponds
> with connection closed day before -
> not sure if it was normal disconnect.
> strace shows the process constantly
> makes 'send' syscall with EPIPE result.
> Any ideas how to trace this issue
> and/or prevent such thing in the future?
Send it a SIGINT and see if it goes away.
If so, I would bet that someone did an unconstrained join (ie SELECT
the cross product of some large tables) and killed their client instead
of waiting for the result. It's not hard to write a SQL query that will
produce terabytes of output :-(
If SIGINT doesn't kill it pretty quickly, try attaching to it with gdb
so you can get a stack trace.
regards, tom lane
>Tom Lane <tgl@sss.pgh.pa.us> writes: >If so, I would bet that someone did an unconstrained join (ie SELECT >the cross product of some large tables) and killed their client instead >of waiting for the result. Quite probably if you recall my Yesterday's question about low speed of queries with multitude joins ;) BTW, many thanks to you for for positive answer. It is not very good news PG is not bullet-proof here and it is so simple to hang PG session (and what is the worst such process effectively utilizes all resources). Are PG admins experienced in determining and killing such zombi-sessions? I mean, may be script or something to do this job exist in nature? Thanks. -- Best regards Ilja Golshtein
> Are PG admins experienced in determining and killing such zombi-sessions? > I mean, may be script or something to do this job exist in nature? > > Thanks. > #statement_timeout = 0 # 0 is disabled, in milliseconds I think if you give this item in postgresql.conf a value, it will kill long running queries for you. > -- > Best regards > Ilja Golshtein > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
>> Are PG admins experienced in determining and killing such zombi-sessions? >> I mean, may be script or something to do this job exist in nature? >> > Thanks. >> > >#statement_timeout = 0 # 0 is disabled, in milliseconds > >I think if you give this item in postgresql.conf a value, it will kill >long running queries for you. Thanks. Not perfect solution, but much better then nothing. To be honest, I don't quite understand why PG does 'send' again and again, while EPIPE seems to be unrecoverable. Isn't it better to terminate server process? -- Best regards Ilja Golshtein
"Ilja Golshtein" <ilejn@yandex.ru> writes:
> To be honest, I don't quite understand why PG does 'send' again and again, while
> EPIPE seems to be unrecoverable.
> Isn't it better to terminate server process?
No. See previous discussions in the archives.
(One point is that a long-running query isn't necessarily generating any
output, anyway, so this wouldn't be a magic cure for anything.)
regards, tom lane
On Thu, Jun 16, 2005 at 10:32:46AM -0400, Tom Lane wrote: > "Ilja Golshtein" <ilejn@yandex.ru> writes: > > To be honest, I don't quite understand why PG does 'send' again and again, while > > EPIPE seems to be unrecoverable. > > Isn't it better to terminate server process? > > No. See previous discussions in the archives. > > (One point is that a long-running query isn't necessarily generating any > output, anyway, so this wouldn't be a magic cure for anything.) Ok, so I did look through the archives [1] and came up with your following example: > As an example: whether an UPDATE command completes might depend on > whether any invoked triggers try to issue NOTICEs. So you are saying if I start a really long update on a remote server and my computer crashes, the UPDATE should complete even though it cannot communicate that result. (-EPIPE is non recoverable_ Not quite what I expected, but that means that you should *always* explicitly use BEGIN and COMMIT if you want to know whether or not a query has actually successfully completed. Otherwise it can complete without you being aware of it. Actually, this explains a crash we had recently. Somoeone had typed in a more-tuples-than-atoms-in-the-universe query and then closed the terminal without realising it. A few days later the machine ran out of memory and died. Normally the client runs out of memory first but without a client it just keeps on going. [1] http://archives.postgresql.org/pgsql-hackers/2005-05/msg01386.php Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.