Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

27.02.2017 10:08, I wrote: [...] So, what I've observed is that Wait* functions _usually_ go to sleep nicely when the state is not signalled, but _sometimes_, depending on unknown criteria, it can choose to instead do a busy-loop wait or something CPU-expensive. Maybe it tries to optimize the

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

15.02.2017 0:06, I wrote: [...] Indeed, such function is available. But essentially, this function is a (kind of) combined login+logout, therefore it would not work for my purpose. (Despite its name, it can not be used to perform some communication "ping" within an established session, such

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 18:15, Rader, David: [...] Try the libpq call from pg_isready. It does actually make a round trip to the postgres server and asks the server if it is ready to accept connections. So you are running the socket communication code of postgres and a small bit of "status" check but not any

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 12:47, John R Pierce: On 2/13/2017 11:03 PM, Nikolai Zhubr wrote: Now I'd like to locate a CPU eater more precisely - supposedly there is some issue with communication, that is why I don't want to mix in anything else. use iperf to test the network transport layer, without any

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 1:10, Thomas Kellerer: Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

, zero usefull activity, just pumping network I/O) Regards, Nikolai -- David Rader dav...@openscg.com <mailto:dav...@openscg.com> On Sun, Feb 12, 2017 at 12:23 PM, Nikolai Zhubr <n-a-zh...@yandex.ru <mailto:n-a-zh...@yandex.ru>> wrote: Hello all, In order to locat

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Hello all, In order to locate the problem more precisely, I'd like to prepare a test, involving some ping-like communication between the server and a test client. That is, I'd like to repeatedly send something valid to the server and get some valid replies from it, but without any kind of

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

05.02.2017 22:05, I wrote: [...] And yes, running Process Explorer gave some new and unexpected input. During the period of this strange high load it claims 40% CPU is used by interrupts (normally 0.01%) and 3% used by backend postgres.exe (normally approx 0%). I'd guess this means some problem

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

05.02.2017 17:10, I wrote: [...] And yes, running Process Explorer gave some new and unexpected input. During the period of this strange high load it claims 40% CPU is used by interrupts (normally 0.01%) and 3% used by backend postgres.exe (normally approx 0%). I'd guess this means some problem

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

03.02.2017 22:16, Andres Freund: [...] Well, profiling postgres.exe is still beyond my capability at this time anyway. Instead, I'll try to prepare a most simple client application example for testing the behaviour in question so that anyone could run it easily. (And while working on such an

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

03.02.2017 20:29, Andres Freund: [...] Could you use process monitor or such to see what the process is doing while using a lot of CPU? I'm not sure how to do this, especially considering that the process in question is running as a service? I don't think that stops you, if you have admin

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

03.02.2017 13:52, I wrote: [...] Adding this "Sleep(15)" before "goto retry" into secure_read() has apparently eliminated the effect at our production server too. That is, my load-bug-detector has been quiet for > 24hr or more. Now by adding more debigging stuff into secure_read() and

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

02.02.2017 2:14, I wrote: 01.02.2017 1:02, I wrote: [...] Could you use process monitor or such to see what the process is doing while using a lot of CPU? I'm not sure how to do this, especially considering that the process in question is running as a service? Now, some more input: * 9.5.2

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

01.02.2017 1:02, I wrote: [...] Could you use process monitor or such to see what the process is doing while using a lot of CPU? I'm not sure how to do this, especially considering that the process in question is running as a service? Now, some more input: * 9.5.2 server running on linux

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

31.01.2017 19:51, Andres Freund: [...] Exactly same trouble with 9.4 (Specifically EDB 9.4.10-1 win32) That's good to know, less because of 519b0757, more because of the latch changes - but they also went in in 9.5... Would it make sense to check some even older ones? Could you use process

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

31.01.2017 10:37, I wrote: [...] 1. "select localtimestamp" 40 times (As separate requests, one by one, but no delay inserted in between) 2. wait 1/2 second. 3. goto 1 Craig, could this be a side-effect of 519b0757? That's new in 9.5, and that's directly related to the code paths discussed

Re: [GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

31.01.2017 6:43, Michael Paquier wrote: On Mon, Jan 30, 2017 at 6:16 PM, Nikolai Zhubr<n-a-zh...@yandex.ru> wrote: I'm observing some strange inexplicable effect in 9.5.5 server running on x86 windows (32-bit windows xp sp3). Oh, well... You are aware that this is out of support by Mic

[GENERAL] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Hello all, (Hopefully this is right place to post on the subject, otherwise please let me know.) I'm observing some strange inexplicable effect in 9.5.5 server running on x86 windows (32-bit windows xp sp3). That is, CPU usage in backend process for the session in question starts to grow,

Re: [GENERAL] Nonblocking libpq + openssl = ?

17.09.2016 3:27, Andres Freund: [...] The relevant user-level API of libpq (PQisBusy) doesn't have a way to return "waiting for write". So we'd have to break API compatibility. I think this is actually not entirely correct. PQisBusy is fine, I do not see a need to touch it at all. Rather, the

Re: [GENERAL] Nonblocking libpq + openssl = ?

17.09.2016 11:15, I wrote: [...] The relevant user-level API of libpq (PQisBusy) doesn't have a way to return "waiting for write". So we'd have to break API compatibility. Ah, I see. But then, this is a very common sort of problem (Existing API spec getting inadequate for some new features

Re: [GENERAL] Nonblocking libpq + openssl = ?

17.09.2016 3:27, Andres Freund: [...] Looks like it _usually_ happens so that such interdependent reads and writes are unnecessary in the absence of renegotiations. But still [1] instructs to always check for both SSL_ERROR_WANT_READ and SSL_ERROR_WANT_WRITE in all cases. Supposedly it is for a

Re: [GENERAL] Nonblocking libpq + openssl = ?

17.09.2016 2:05, Andres Freund: [...] Well, it's not pretty. I quite dislike this bit, and I've complained about it before. But it is noteworthy that it's nearly impossible to hit these days, due to ssl-renegotiation support having been ripped out. That's what could trigger openssl to require

[GENERAL] Nonblocking libpq + openssl = ?

Hello all, Not sure this is exactly right list, so feel free to point me to some other as appropriate. While working on a higher-level binding to the libpq library, I've (likely) discovered a problem with non-blocking operation in case of using openssl. And, it looks so striking I'd like to

Re: [GENERAL] Actual row order in UPDATE and SELECT FOR UPDATE

Hello all, 16.02.2016 2:41, Vitaly Burovoy: [...] UPDATE deals with rows in order they are fetched from a heap. In a common case it can be considered as unordered. [...] However SELECT can fetch rows in a specific order and locking by FOR UPDATE clause is applied _after_ sorting. Then you can

[GENERAL] Actual row order in UPDATE and SELECT FOR UPDATE

Hello all, I can't find any clear description of how to reliably figure and/or enforce specific row (locking) order within UPDATE and SELECT FOR UPDATE statements dealing with multiple rows. I'd like to get rid of some deadlocks (caused by share locks). While the manual explains locks and

Re: [GENERAL] Server-side hooks for user session start and session end

Hi, 27.09.2015 23:22, Pavel Stehule wrote: [...] updated patch - fixed error reporting Wow! I'll definitely borrow it :) Thank you, Nikolai Regards Pavel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org ) To make

Re: [GENERAL] Server-side hooks for user session start and session end

Hi, 27.09.2015 22:59, Pavel Stehule: 2015-09-27 21:40 GMT+02:00 Jim Nasby >: _PG_fini It should not work - see a doc I'd rather consider registering with on_shmem_exit -- as per the insightfull note placed in

Re: [GENERAL] Server-side hooks for user session start and session end

Hi, 27.09.2015 8:29, Pavel Stehule: I'll check it. It is working. Patch attached Oh, brilliant! This is a _huge_ help actually! If I understand it correctly, any unhandled SQL-level exceptions will essentially be ignored there, so that the session will continue regardless? And maybe

[GENERAL] Server-side hooks for user session start and session end

Hi all, I'm trying to find a soultion to automatically execute something (preferrably a function or at least some pure sql statements) at the beginning and at the end of a user session. As an example, imagine just storing of all login and logout timestamps (though the real task is a bit more

Re: [GENERAL] Server-side hooks for user session start and session end

Hi Pavel, 26.09.2015 19:26, Pavel Stehule wrote: [...] This cannot be solved without patching PostgreSQL source code :( . There are not good hooks for custom extension. Patch is relative simple, but I cannot to publish it. Ok, I see. Creating such a patch might be not very hard actually. But

Re: [GENERAL] Server-side hooks for user session start and session end

26.09.2015 20:59, Tom Lane wrote: [...] A session-end hook seems pretty problematic to me: you simply cannot guarantee that it will run at all. (Consider process crash or server abort cases.) So anything built on the assumption that it gets to run at session end is going to be inherently