Re: Postgresql 10.3 , query never completes if LIMIT clause isspecified and paralle processing is on - Mailing list pgsql-general

From Alessandro Aste
Subject Re: Postgresql 10.3 , query never completes if LIMIT clause isspecified and paralle processing is on
Date
Msg-id CAM9F+O2MgGMsdjJ34xhz9_8raaD=w6but79f0KQ4DTEYrRJ0FA@mail.gmail.com
Whole thread Raw
In response to Re: Postgresql 10.3 , query never completes if LIMIT clause isspecified and paralle processing is on  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
List pgsql-general
Thanks Tomas. We're currently building postgres from source. In order to enable symbols, you want me to re-configure postres with  --enable-debug then run perf?

Regards,

On Thu, Mar 22, 2018 at 5:00 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:


On 03/22/2018 11:33 AM, Alessandro Aste wrote:
> Tomas, thank you. This machine  is abare metal server running only a
> staging postgresql 10.3  instance. Nobody is using it beside me. 
>
> I'm attaching 4 files.  
>
> every_30_seconds_top_stats_during_query.txt    - this is a caputure of
> the top command every 30 seconds(more or less) for 10+ minutes while I'm
> running the query. Let me know if this helps to answere your question.
> EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt   - 
> query plan  with full query and  max_parallel_workers_per_gather  force
> to 0. Full output.
> EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt  - 
>  query plan with full query and  default parellel processing settings. 
> Full output.
> EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx  -
> query plan of the query omitting the LIMIT clause and default parellel
> processing settings. Full output.
>

OK. Looking at the top output, I see this:

       PID USER       VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    104880 postgres  30.8g 1.9g 1.9g R 92.0  1.5  15:15.60 postmaster
    111732 postgres  30.8g 476m 473m R 88.2  0.4   0:00.47 postmaster
    111730 postgres  30.8g 473m 470m R 86.4  0.4   0:00.46 postmaster
    111731 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
    111733 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
    111734 postgres  30.8g 476m 473m R 86.4  0.4   0:00.46 postmaster
    111728 root      15824 1912  828 R  3.8  0.0   0:00.04 top

That means it certainly is not stuck, it's simply doing a lot of work on
CPU. The question is why and what it's doing ...

Can you collect some CPU profiles using perf? There's a howto here:

    https://wiki.postgresql.org/wiki/Profiling_with_perf

But in short - install perf, install debuginfo packages for postgres,
and then do

    perf record -a -g -s sleep 60

while running the query. Once the profile data is collected, do

    perf report > report.txt

and share the report.txt with us (well, if it's very large you may need
to only cut the first 1000 lines or so).

That should tell us in which functions most of the time is spent. That
will give us some hints, hopefully.

>
> For what concerns the  self-contained test case - I'll do my best to
> prepare it.
>

Yeah, that would be helpful.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-general by date:

Previous
From: Jeremy Finzel
Date:
Subject: Re: found xmin from before relfrozenxid on pg_catalog.pg_authid
Next
From: Tomas Vondra
Date:
Subject: Re: Postgresql 10.3 , query never completes if LIMIT clause isspecified and paralle processing is on