Re: Parallel Seq Scan

On Thu, Mar 12, 2015 at 9:50 PM, Thom Brown <thom(at)linux(dot)com> wrote:
>
> On 12 March 2015 at 15:29, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > Please note that parallel_setup_cost and parallel_startup_cost are
> > still set to zero by default, so you need to set it to higher values
> > if you don't want the parallel plans once parallel_seqscan_degree
> > is set. I have yet to comeup with default values for them, needs
> > some tests.
>
> Thanks. Getting a problem:
>

Thanks for looking into patch.

So as per this report, I am seeing 3 different problems in it.

Problem-1:
---------------------
>
> # SELECT name, setting FROM pg_settings WHERE name IN
> ('parallel_seqscan_degree','max_worker_processes','seq_page_cost');
> name | setting
> -------------------------+---------
> max_worker_processes | 20
> parallel_seqscan_degree | 8
> seq_page_cost | 1000
> (3 rows)
>
> # EXPLAIN SELECT DISTINCT bid FROM pgbench_accounts;
> ERROR: too many dynamic shared memory segments
>
>

This happens because we have maximum limit on the number of
dynamic shared memory segments in the system.

In function dsm_postmaster_startup(), it is defined as follows:

maxitems = PG_DYNSHMEM_FIXED_SLOTS
+ PG_DYNSHMEM_SLOTS_PER_BACKEND * MaxBackends;

In the above case, it is choosing parallel plan for each of the
AppendRelation,
(because of seq_page_cost = 1000) and that causes the test to
cross max limit of dsm segments.

One way to fix could be that we increase the number of dsm segments
that can be created in a system/backend, but it seems to me that in
reality there might not be many such plans which would need so many
dsm segments, unless user tinkers too much with costing and even if
he does, he can increase max_connections to avoid such problem.

I would like to see opinion of other people on this matter.

Problem-2:
--------------------
2015-03-12 16:09:30 GMT [7880]: [36-1] user=,db=,client= LOG: server
process (PID 7889) was terminated by signal 11: Segmentation fault
2015-03-12 16:09:30 GMT [7880]: [37-1] user=,db=,client= DETAIL:
Failed process was running: SELECT pg_catalog.quote_ident(c.relname)
FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm',
'f') AND substring(pg_catalog.quote_ident(c.relname),1,10)='pgbench_br'
AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <>
(SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM
pg_catalog.pg_namespace n WHERE
substring(pg_catalog.quote_ident(n.nspname) || '.',1,10)='pgbench_br'
AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE
substring(pg_catalog.quote_ident(nspname) || '.',1,10) =
substring('pgbench_br',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1))
> 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' ||
pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c,
pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind
IN ('r', 'S', 'v', 'm', 'f') AND
substring(pg_catalog.quote_ident(n.nspname) || '.' ||
pg_catalog.quote_ident(c.relname),1,10)='pgbench_br' AND substri

This seems to be unrelated to first issue (as the statement in log has
nothing to do with Problem-1) and this could be same issue what
Amit Langote has reported, so we can test this once with the fix for that
issue, but I think it is important if we can isolate the test due to which
this problem has occurred.

Problem-3
----------------
I am seeing as Assertion failure (in ExitParallelMode()) with this test,
but that seems to be an issue due to the lack of integration with
access-parallel-safety patch. I will test this after integrating with
access-parallel-safety patch.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com