AutoParallel feature in SQL not working

Question

Question

Hello all,

We have our system with AutoParallel enabled:

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
1

But whenever I try to run any sql the autoparallel does not work. For example, this simple query:

When I force it with %PARALLEL we can see it will effectively run in parallel:

The total records is bigger than the threshold. So, what can go wrong? Anyone in the same situation?

Product version: IRIS 2023.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2023.1.3 (Build 517U) Wed Jan 10 2024 13:30:33 EST [Health:5.1.0-2.m3]

Discussion (9)3

Log in or sign up to continue

score 0 · Answer 1 · 2024-11-13T15:29:24-05:00

Eduard Lebedyuk · Nov 13

I don't think you'll get much from parallelization with * in SELECT. Calling @Benjamin De Boe

0 0

score 0 · Answer 2 · 2024-11-14T01:08:43-05:00

Hello Eduard,

I guess you mean * could prevent it? I've tried also using only some properties, and it's not auto-parallelizing it neither :'(

I tried the same via ODBC and result keep being the same: no autoparallel :'(

score 0 · Answer 3 · 2024-11-15T06:08:23-05:00

This is indeed expected behaviour. It's not the SELECT * itself, but the fact that this query is not applying any filtering or doing any other calculations that are worth parallelizing. So the query is asking to return all rows as-is, passing them back through a single connection/process. Therefore the optimizer argues there's no benefit in parallelizing that work, as the work of collating the per-process results back into a single resultset is pure overhead.

The actual formula being applied is a little more subtle (a WHERE clause that is expected to only filter out a small fraction of the rows would not be enough to parallelize either) and as has been suggested you still need to hit the AutoParallel threshold for the process mgmt code not to outweigh the benefits (e.g. if there's only a few hundred rows).

Thanks,
benjamin

score 0 · Answer 4 · 2024-11-15T09:19:42-05:00

hi!

Thx for your reply. As explained to Ali Naser I've done some other testing... I've created this class and populated with 1M records. Then I've run some queries and still no parallel :'( I can't find any situation where the autoparallel works on its own.

Example queries:

select Address, City, Name from MCCH.ClassTest where age>10
select Address, City, Name from MCCH.ClassTest where age>20
select Address, City, Name from MCCH.ClassTest where age>30

Class:

Class SQLUser.ClassTest Extends (%Persistent, %Populate){

Property Name As %String(MAXLEN = 255);
Property Age As %Integer(MAXVAL = 100, MINVAL = 1);
Property Address As %String(MAXLEN = 255);
Property City As %String(MAXLEN = 255);
Property State As %String(MAXLEN = 255);
Property Zip As %String(MAXLEN = 255);
Property Country As %String(MAXLEN = 255);
Property Comment As %String(MAXLEN = 255);
Property Hobby As %String(MAXLEN = 255);
Property JobTitle As %String(MAXLEN = 255);
Property Company As %String(MAXLEN = 255);
Property PhoneNumber As %String(MAXLEN = 255);
Property Email As %String(MAXLEN = 255);
Property Gender As %String(MAXLEN = 1);
Property Ethnicity As %String(MAXLEN = 255);
Property Race As %String(MAXLEN = 255);
Property Religion As %String(MAXLEN = 255);
Property MaritalStatus As %String(MAXLEN = 255);
Property Children As %Integer(MAXVAL = 10, MINVAL = 0);
Property Income As %Integer(MAXVAL = 100000, MINVAL = 0);
Property Occupation As %String(MAXLEN = 255);
Property Education As %String(MAXLEN = 255);
Property HomePhone As %String(MAXLEN = 255);
Property MobilePhone As %String(MAXLEN = 255);
Property WorkPhone As %String(MAXLEN = 255);
Property WorkEmail As %String(MAXLEN = 255);
Property HomeEmail As %String(MAXLEN = 255);
Property HomeAddress As %String(MAXLEN = 255);
Property HomeCity As %String(MAXLEN = 255);
Property HomeState As %String(MAXLEN = 255);
Property HomeZip As %String(MAXLEN = 255);
Property HomeCountry As %String(MAXLEN = 255);
Property WorkAddress As %String(MAXLEN = 255);
Property WorkCity As %String(MAXLEN = 255);
Property WorkState As %String(MAXLEN = 255);
Property WorkZip As %String(MAXLEN = 255);
Property WorkCountry As %String(MAXLEN = 255);
Property WorkPhoneNumber As %String(MAXLEN = 255);
Property WorkMobilePhone As %String(MAXLEN = 255);
Property WorkFax As %String(MAXLEN = 255);
Property WorkWebsite As %String(MAXLEN = 255);
Property WorkComments As %String(MAXLEN = 255);
Property FootballTeam As %String(MAXLEN = 255);
Property BaseballTeam As %String(MAXLEN = 255);
Property BasketballTeam As %String(MAXLEN = 255);
Property HockeyTeam As %String(MAXLEN = 255);
Property GolfTeam As %String(MAXLEN = 255);
Property SportsComments As %String(MAXLEN = 255);
Property SportsComments2 As %String(MAXLEN = 255);
Property SportsComments3 As %String(MAXLEN = 255);

Index IdxAge On Age;
}

Rows:
select count(*) from MCCH.ClassTest -> 1.000.000

So, I've decided to run some tests to see if autoparallel is ignored because it's not worth it. I've run the same query after clearing all the buffers (d ClearBuffers^|"%SYS"|GLOBUFF()):

select * from %PARALLEL SQLUser.ClassTest where age>40  : 0.0614 seconds  318 global references 34801 commands executed

select * from SQLUser.ClassTest where age>40 : 0.2377 seconds  600975 global references 2437292 commands executed

So, it seems that it's faster with %PARALLEL, but I can't find out how to write the query so the autoparallel goes on :'(

I have even tried queries without * to check, with no luck. For example:
select Address, City, Name from SQLUser.ClassTest where age>30

score 0 · Answer 5 · 2024-11-18T03:50:37-05:00

Hi David,

AutoParallel is based on a comprehensive formula weighing the cost (setup cost for the coordination work, writing and then recombining per-process results) against the benefits (independent work that can run in parallel).

For queries not doing any aggregation, iow when the result rows correspond directly to rows in the table being queried (and especially if there are no JOINs), having to write and then read the per-process results, even when done in parallel, does not add value if there's no significant compute cost associated with for example validating a filter predicate.

For the SELECT COUNT(*), the optimizer is satisfying that from the index (you don't seem to have a bitmap extent index, but that index on age is still plenty small), which is very cheap to read so 1M rows still don't weigh up against the setup costs.

Anyway, AutoParallel works very well for most of our customers. It's based on a cost formula that incorporates a few constants representing the cost of IO and computations that generalize across infrastructure, so for some environments there may be cases where it over-estimates or under-estimates the exact cost, leading to edge cases where the other option might have yielded a slightly faster query, but generally the formula holds well and every now and then we review whether the constants need to be tuned (wrt newer standards for hardware).

As for the particular example with 0.06 vs 0.24s, I think there may be something different at play there. The (presumed!) non-parallel case does 600k grefs whereas the parallel one only needs 318. Even if your result should only have 300 rows, I would expect it to need at least twice as many grefs (index lookup + master map), so I'd recommend giving that another try after ensuring table stats are up to date and comparing the query plans (for the %PARALLEL, %NOPARALLEL, and query without either hint). A possible explanation might be that your query tool (the SMP?) at the UI level was only retrieving the first 100 rows for the first query, and all of them for the second test.

score 0 · Answer 6 · 2024-11-20T03:40:58-05:00

Thanks for your answer. I don't doubt AutoParallel works at all, I simply can't have it working in any of the queries I tried, even with AVG, SUM, etc., using 1M or 10M rows. I gave you all the info, you can run yourself if interested.

So, I presume the best approach (for me) to be in control of what is happening into the system is to add %PARALLEL whenever I want it to effectively do it. Old good habits never die :-D

score 0 · Answer 7 · 2024-11-14T11:39:30-05:00

Maybe the query optimizer doesn't see any performance improvements from running the query in parallael? You can use the "Alternate Show Plans" tool to see what other query plans were considered.

score 0 · Answer 8 · 2024-11-15T02:59:20-05:00

Hello Ali,

I didn't think about it :) I've checked and it provides some other plans but none with parallel in it. I've tried creating a new class, populating it with 1M rows and created an index. Then run several queries, and none of them (even alternate plans) are using parallel. Only when %PARALLEL is forced.

Weird....

score 1 · Answer 9 · 2024-11-15T06:00:06-05:00

And don't forget about AutoParallelThreshold