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

[Nikolai Zhubr]

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 delay, or maybe
it is a bug. The effect somewhat varies depending on windows version,
CPU cores, selected system timer frequency, and Wait* call pattern
(frequency).

I can not currently see how it can be fixed in a generic and reliable
way in postgres without sacrificing some performance. On the other hand,
leaving it as-is is exposing the server to a substantial CPU abuse (even
unintentional, as it initially happened in my case).


Ok, I've finally found what happened. False alarm, in short. The server 
is perfectly fine.


My mistake was that I was using most basic ways of monitoring CPU load, 
i.e. provided by GetProcessTimes() and taskmgr.exe and such. And because 
they only operate at the scheduler granularity (usually 16ms approx), 
some rounding has to happen in cpu consumption calculation. With this 
approach, this rounding can apparently distort the measurement 
dramatically (in some corner cases).


Now, xperf employs a more fair approach to cpu consumption measurement, 
based on all the exact times of process switching-in and -out, which 
apparently gives a much better result.


So, in my case the results of taskmgr and xperf are very different, and 
considering the above, taskmgr's report should be dismissed as bogus. 
And xperf indicates that my stress-tests actually do not cause postgres 
backend process to consume any noticable cpu percentage al all. That's good.



Regards,
Nikolai




Regards,
Nikolai




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Nikolai Zhubr]

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 thing simply
does not exist in the protocol, at least as of 9.5)


Ok, I've made some additions to the protocol to be able to do pings to a 
real server, some standalone tests mimicing postgresql communication and 
employing RDTSC, and run rediculously extensive experiments. And my 
findings to the moment is that the reason for CPU load issue is a wierd 
behaviour of windows' core functions, specifically 
WaitForMultipleObjects (and apparently the whole family of Wait 
functions) which is used in postgresql backend for waiting.


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 delay, or maybe 
it is a bug. The effect somewhat varies depending on windows version, 
CPU cores, selected system timer frequency, and Wait* call pattern 
(frequency).


I can not currently see how it can be fixed in a generic and reliable 
way in postgres without sacrificing some performance. On the other hand, 
leaving it as-is is exposing the server to a substantial CPU abuse (even 
unintentional, as it initially happened in my case).



Regards,
Nikolai




Regards,
Nikolai



from mirror:
https://github.com/postgres/postgres/blob/master/src/bin/scripts/pg_isready.c


relevant call is PQping:

rv = PQpingParams(keywords, values, 1);











--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Nikolai Zhubr]

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 sql.


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 thing simply 
does not exist in the protocol, at least as of 9.5)



Regards,
Nikolai



from mirror:
https://github.com/postgres/postgres/blob/master/src/bin/scripts/pg_isready.c

relevant call is PQping:

rv = PQpingParams(keywords, values, 1);







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rader, David]

--
David Rader
dav...@openscg.com

On Tue, Feb 14, 2017 at 5:28 AM, Nikolai Zhubr  wrote:

> 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 postgres in
>> the loop?
>>
>
> No, the network performance in general is fine. However, some specific
> continuous communication pattern causes something to go very wrong on
> windows periodically. (And there is no such problem on linux btw) While the
> issue now seems rather related to windows itself and not postgres, but the
> pattern typical for some of my postgres uses is apparently a reliable
> trigger. Therefore, I'm thinking to first isolate the problem inside
> postgres, and if it still triggers then, second step, create a test
> "server" consisting of communication code of postgres to run without any
> other parts of postgres.
>
>
> Regards,
> Nikolai
>
>
>
>>
>>
>
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 sql.

from mirror:
https://github.com/postgres/postgres/blob/master/src/bin/scripts/pg_isready.c

relevant call is PQping:

rv = PQpingParams(keywords, values, 1);

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

[Nikolai Zhubr]

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 postgres in
the loop?


No, the network performance in general is fine. However, some specific 
continuous communication pattern causes something to go very wrong on 
windows periodically. (And there is no such problem on linux btw) While 
the issue now seems rather related to windows itself and not postgres, 
but the pattern typical for some of my postgres uses is apparently a 
reliable trigger. Therefore, I'm thinking to first isolate the problem 
inside postgres, and if it still triggers then, second step, create a 
test "server" consisting of communication code of postgres to run 
without any other parts of postgres.



Regards,
Nikolai








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[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 postgres in 
the loop?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Nikolai Zhubr]

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 some continued (valid) session,
but so that the server is not actually doing anything above that (no
sql, no locking, no synchronizing, zero usefull activity, just
pumping network I/O)



If you are willing to drop the "no sql" requirement you could use
something like

select rpad('*', 1, '*');

this will send a lot of data over the wire, the SQL overhead should be
fairly small.


Well yes, but I've been there already.
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.


Anyway, I've now got pretty sure the standard protocol out of the box 
does not provide such short-circuite capability so I'll have to hack it 
a bit.



Regards,
Nikolai



You can send more data if you combine that with e.g. generate_series()




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Mead]

On Mon, Feb 13, 2017 at 5:10 PM, Thomas Kellerer  wrote:

> 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 some continued (valid) session,
>> but so that the server is not actually doing anything above that (no
>> sql, no locking, no synchronizing, zero usefull activity, just
>> pumping network I/O)
>>
>
My personal favorite is 'select 1'

   I know you're not looking for SQL, but, you gotta start somewhere...


>
>>
> If you are willing to drop the "no sql" requirement you could use
> something like
>
> select rpad('*', 1, '*');
>
> this will send a lot of data over the wire, the SQL overhead should be
> fairly small.
>
> You can send more data if you combine that with e.g. generate_series()
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

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

[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 some continued (valid) session,
but so that the server is not actually doing anything above that (no
sql, no locking, no synchronizing, zero usefull activity, just
pumping network I/O)



If you are willing to drop the "no sql" requirement you could use something like

select rpad('*', 1, '*');

this will send a lot of data over the wire, the SQL overhead should be fairly 
small.

You can send more data if you combine that with e.g. generate_series()








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general