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.von...@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 >
