Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

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(dot)vondra(at)2ndquadrant(dot)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
>