10-15 seconds of pre-compile

[zhou]

Btw, can you try to run the query in interpret mode? Option (interpreter_mode=interpret).
It would be helpful to see how long that takes.

[dtroyan]

@zhou

Btw, can you try to run the query in interpret mode? Option (interpreter_mode=interpret).
It would be helpful to see how long that takes.

Dear @zhou, I’ve already done it here 10-15 seconds of pre-compile - #11 by dtroyan - Help - SingleStore Forums

[dtroyan]

@zhou

@dtroyan Sorry for the late response.
I examined the query. Unfortunately, interpFirst is not helping too much in this case. Because this query is generating one single plan the leaves so we won’t get the usual benefit of parallel compilation. In addition since all tables are rowstore, the interpreter will be a lot less efficient so we won’t do meaningful work until compilation finishes.
as a result, interpFirst won’t perform better than compile and run.

(for particular case, it might perform better if the tables are not sharded the same way).

Thanks again for the meaningful feedback. We will improve this case in later version of memSQL.

Dear @zhou, could you please explain in more details, why exactly is this query taking the whole 3 seconds to execute on the first time, and ~150ms on all consecutive runs?

It’s taking even more than compile times on all leaves combined (600ms+600ms) + the actual execution time (150ms).

The MemSQL becomes very hard to sell to my internal customers with this behaviour, as the live dashboards update for more than 5 seconds sometimes on every filter change, as each time a filter changes the SQL query changes => new compilation cycle => 3-5 sec (sometimes over 7 secs!) execution.

[zhou]

I will try to repro the issue myself.
Meanwhile, could you run this query? This will skip optimizer.

SELECT STRAIGHT_JOIN STR_TO_DATE( DATE_FORMAT(servicedesk.RegisteredOn,’%Y-%m-%d %H’),’%Y-%m-%d %H’) AS RegisteredOn, COUNT() AS count, STR_TO_DATE( DATE_FORMAT(servicedesk.RegisteredOn,’%Y-%m-%d %H’),’%Y-%m-%d %H’) AS op, DATE_FORMAT(servicedesk.RegisteredOn,’%Y-%m-%d %H’) AS op_2 FROM ((((test.servicedesk as servicedesk LEFT JOIN ( SELECT WITH(NO_MERGE_THIS_SELECT=1) serviceitem__via__ServiceItemI_0.id AS id FROM test.serviceitem as serviceitem__via__ServiceItemI_0 ) AS serviceitem__via__ServiceItemI ON (servicedesk.ServiceItemId = serviceitem__via__ServiceItemI.id)) LEFT JOIN ( SELECT WITH(NO_MERGE_THIS_SELECT=1) casestatus__via__StatusId_0.id AS id FROM test.casestatus as casestatus__via__StatusId_0 ) AS casestatus__via__StatusId ON (servicedesk.StatusId = casestatus__via__StatusId.id)) LEFT JOIN ( SELECT WITH(NO_MERGE_THIS_SELECT=1) sysadminunit_0.id AS id, sysadminunit_0.name AS name FROM test.sysadminunit as sysadminunit_0 ) AS sysadminunit ON (sysadminunit.id = servicedesk.GroupId)) LEFT JOIN ( SELECT WITH(NO_MERGE_THIS_SELECT=1) sysadminunit_prev_0.id AS id, sysadminunit_prev_0.name AS name FROM test.sysadminunit as sysadminunit_prev_0 ) AS sysadminunit_prev ON (sysadminunit_prev.id = servicedesk.GroupId_prev)) WHERE ((servicedesk.ins_upd_ts > (‘2019-01-01’!:>datetime(6) NULL)) AND ( IFNULL(sysadminunit.name,’’) <> ‘’) AND (servicedesk.GroupModifiedOn > (‘2019-01-01’!:>datetime(6) NULL)) AND ISNULL(sysadminunit_prev.name) AND (NOT ISNULL(servicedesk.Number))) GROUP BY 3 ORDER BY 3 asc OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)

[dtroyan]

Thank you for the reply,

I have run your Query several times consecutively (“Query 2” on the screenshot),
and then the Query from the post #11 (“Query 1” on the screenshot) a couple of times as well - just to give you the reference.

[zhou]

It is not a good thing to do in general, but for these particular kind of query, you might try to run it with
Select Straight_join … Option(No_Query_rewrite=1);

[dtroyan]

@zhou, it actually does help somehow: first query run time has decreased to ~1.2secs with these hacks.

However, apparently these options do not work when used in a view DDL. I would not want to force all customers add these options in their queries, moreover - all BI tools just cannot be taught to use use them.

Am I right to understand that this behavior will be present in MemSQL for a while? It would be helpful to know if there is any kind of timeline to fix the issue?

[nikita]

@dtroyan. i assume you are from PA Networks. At this point, please file a support ticket. We provide “best effort” support to our community users but take customer support very seriously. Filing a support ticket will put the investigation on a faster track. We will update the community once we solve this.

[dtroyan]

Dear @nikita,
Could you clarify what is “PA Networks”? If that is supposed to be a company - I do not work there, nor do I know about its existence.

I cannot file a support ticket, as we do not have a paid subscription with MemSQL. This is the last issue with MemSQL that, from my perspective, should be settled before suggesting MemSQL to be our real-time analytics platform of choice in the future.

5-10 seconds for the initial query run time is unfortunately not acceptable:(

[nikita]

@dtroyan. I’m sorry I wrongly assumed u r on the same team with Ziv. We will keep digging and get to the bottom of it. Would you be able to give us access to the cluster? Over Webex or zoom?