Calcite Server Time


#1

For one of my query, I see following in log file -
I0724 21:24:46.084880 30687 Calcite.cpp:329] Time in Thrift 4 (ms), Time in Java Calcite server 875471 (ms)

Does it mean about 14 min were spent in query parsing alone?
Is there a guideline to minimize this time?

Thanks.


#2

Hi @mbaxi,

Can you please provide any more information about:

  • Your hardware and environment / deployment architecture (example: Community Edition installed locally on a single GPU)
  • What version of MapD you are using (example: 4.0)
  • Information about your data (such as size, number of rows / columns, schema, sample data if possible)
  • Information about your query (such as the query itself, obfuscated if you prefer)

Regards,
Veda


#3

H/w - Single node cpu with 32 cores and 128 GB Ram
MapD version - 4.0.2
Data size - 1 TB on disk in flat schema, # rows 1 Billion, Num columns = 30, columns used in query = 9
Query -
SELECT r, m, p, sum(fc) AS fc, (CASE WHEN sum(fc) > 0 Then 1 Else 0 End) AS fwc, (CASE WHEN scs=0 THEN ‘U’ WHEN sc/scs>=0.74 Then ‘R’ Else ‘U’ End) AS reliable from mytable where (( t = ‘a’ AND m = ‘a_1’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_2’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_3’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_4’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_5’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_6’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’))) AND end_date = ‘2018-06-06’ AND ft =‘W’ GROUP BY r, m, p, reliable;

Datatypes
Columns r, m, p, t are of type TEXT ENCODING DICT(16)
fc SMALLINT ENCODING FIXED(8)
sc INTEGER ENCODING FIXED(16)
scs SMALLINT ENCODING FIXED(8)
ft TEXT ENCODING DICT(8)
end_date DATE ENCODING FIXED(32)

I reran the query again just to check if issue was reproducible or not, here is log from second run -

I0725 16:23:53.149588 16678 FileMgr.cpp:174] Completed Reading table’s file metadata, Elapsed time : 830332ms Epoch: 6 files read: 2264 table location: ‘/data/mapd/data/mapd_data/table_3_1/’
I0725 16:23:55.797873 16678 Catalog.cpp:2322] Instantiating Fragmenter for table mytable took 832982ms
I0725 16:23:56.267416 15559 Calcite.cpp:329] Time in Thrift 5 (ms), Time in Java Calcite server 833648 (ms)
I0725 16:23:56.424736 15559 Catalog.cpp:2375] Time to load Dictionary 3_1 was 111ms
I0725 16:23:56.768132 15559 Catalog.cpp:2375] Time to load Dictionary 3_8 was 343ms
I0725 16:23:57.112011 15559 Catalog.cpp:2375] Time to load Dictionary 3_7 was 343ms
I0725 16:23:57.366585 15559 Catalog.cpp:2375] Time to load Dictionary 3_9 was 254ms
I0725 16:23:57.602394 15559 Catalog.cpp:2375] Time to load Dictionary 3_2 was 235ms
I0725 16:23:57.916276 23925 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:24:39.549785 24575 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:25:17.444663 24759 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:25:56.707496 24233 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:26:37.668710 24328 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:27:22.207702 24748 BufferMgr.cpp:283] ALLOCATION slab of 8388608 pages (4294967296B) created in 0 ms CPU_MGR:0
I0725 16:28:08.287231 15559 MapDHandler.cpp:663] sql_execute-COMPLETED Total: 1085680 (ms), Execution: 1085678 (ms)


#4

have you loaded your table on very small batches?


#5

@aznable , @veda,

I had part files generated on hdfs about 2.2 gb in size, I loaded them in 6 incremental batches using COPY. Are you concerned about - Instantiating Fragmenter for table mytable took 832982ms, but not all queries are taking time in instantiating fragmenter.

Is there anything I should take care while data ingestion when loading via multiple small files? or can I run some utility later to achieve same impact.


#6

Those arnt smaller batches bit quote large ones

I recently loaded 200m or record with local copy in hundreds batches and i havent foto problems


#7

Hi @mbaxi,

We ran your query on an empty table using the same DDL and got a response right away on an engineering desktop.
mapdql> SELECT r, m, p, sum(fc) AS fc, (CASE WHEN sum(fc) > 0 Then 1 Else 0 End) AS fwc, (CASE WHEN scs=0 THEN ‘U’ WHEN sc/scs>=0.74 Then ‘R’ El
se ‘U’ End) AS reliable from mytable where (( t = ‘a’ AND m = ‘a_1’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR
( t = ‘a’ AND m = ‘a_2’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_3’ AND p IN (‘p0’,‘p1
‘,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_4’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,’
p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_5’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_6’ AND
p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’))) AND end_date = ‘2018-06-06’ AND ft =‘W’ GROUP BY r, m, p, reliable;
No rows returned.
Execution time: 6031 ms, Total time: 6038 ms
mapdql> SELECT r, m, p, sum(fc) AS fc, (CASE WHEN sum(fc) > 0 Then 1 Else 0 End) AS fwc, (CASE WHEN scs=0 THEN ‘U’ WHEN sc/scs>=0.74 Then ‘R’ Else ‘U’ End) AS reliable from mytable where (( t = ‘a’ AND m = ‘a_1’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_2’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_3’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_4’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_5’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’)) OR ( t = ‘a’ AND m = ‘a_6’ AND p IN (‘p0’,‘p1’,‘p2’,‘p3’,‘p4’,‘p5’,‘p6’,‘p7’,‘p8’,‘p9’,‘p10’,‘p11’))) AND end_date = ‘2018-06-06’ AND ft =‘W’ GROUP BY r, m, p, reliable;
No rows returned.
Execution time: 98 ms, Total time: 106 ms

From the log:
I0725 12:57:47.421281 19389 Catalog.cpp:2643] Instantiating Fragmenter for table mytable took 0ms
I0725 12:57:47.604835 18501 Calcite.cpp:346] Time in Thrift 1 (ms), Time in Java Calcite server 253 (ms)

What about other queries, do you see this much delay on the Calcite server?
Regards,
Veda


#8

Thanks @veda, @aznable for taking a look.
Not all of my queries have high calcite server time, about 80% have it under 50 ms and a 10 % under 100 ms.

However if I lookup for ‘Instantiating Fragmenter for table’ about 60% queries had response time less than 5 sec while remaining ones had it somewhere from 10-16 min.

I changed the schema model from flat to star, and time spent in both Fragmenter and Calcite was reduced to 200ms for the above query, however query execution time became static 38 sec.
Earlier query execution time used to reduce on rerunning the same query, but now it is pretty constant is this expected behavior for join queries?


#9

my experience with joining a star schema, so with n-1 relations, on map is mostly positive.

using the ASA’s flights dataset you can download here http://stat-computing.org/dataexpo/2009/the-data.html
so a fact table witn 120M+ records and two small dimension table, one of them used twice

select c1_year,description,state,avg(arrdelay),avg(depdelay) from flights f JOIN airports a ON f.origin = a.iata JOIN carriers c ON f.uniquecarrier = c.code group by 1,2,3 order by 1,3;

9707 rows returned.
Execution time: 238 ms, Total time: 253 ms

without joins

select c1_year,c2_month,uniquecarrier,avg(arrdelay),avg(depdelay) from flights f group by 1,2,3 order by 1,3;
3360 rows returned.

Execution time: 167 ms, Total time: 171 ms

Not a so big difference, but maybe is because of datatypes i used in the columns involved in join (text econded dict(16) that produce a compact bitmap halping a good response time.

I suspect that using other datatypes like dates also with low cardinalities, you could have a bad performance (this is valid for group by/distinct too).

So if you have an date/ts or integer/bigint with low cardinality but a large maximum value and you are using for joins/gb you can try to load as text dictionary encoded to get a good performance.


#10

Hi Aznable,

Thanks for the information and now i am clear about this topic

Regards