SELECT query hangs (or takes far too long)


#1

First, I am brand new to MAPD and in a bit over my head.

I have a single table:
mapdql> \d sst
CREATE TABLE sst (
t TIMESTAMP,
lat FLOAT,
lon FLOAT,
sst FLOAT)

This looks good:
mapdql> select sum(sst), count(1) from sst ;
EXPR$0|EXPR$1
3107618560.000000|483840000
1 rows returned.
Execution time: 64 ms, Total time: 64 ms

500 Million rows in 64ms. Awesome!

Now I want to run this Query. What I really want is the results of the inner query but I don’t want to dump them to the terminal so I just count(1) them for a test.

t,lat,lon are the “Key” (175,480,576 distinct values) so there would be about 48,000,000 rows in the output set before doing the counting

mapdql> select count(1) from (select sum(sst), count(1) from sst group by t, lat, lon);

With 40 million rows it finishes in about 2 seconds. After expanding to 500,0000,000 rows. I left this query going fro an 2 hours and never got a result.

I can see the is CPU is pegged. All 2x8 cores at 100%. The GPUs are near zero… the odd blip at 30% or so.

Can anyone suggest the right way to deal with this query? Or how to debug it?

For Completeness I included the output from EXPLAIN and EXPLAIN CALCITE, I am still studying them to see if I can deduce the cause.

My config. is
Intel® Xeon® CPU E5-2670 0 @ 2.60GHz
4 x NVidia K5000 (4GB per GPU)
256GB RAM
Very Fast SSD Storage


mapdql> explain select count(1) from (select sum(sst), count(1) from sst group by t, lat, lon);
Explanation
IR for the GPU:

; Function Attrs: uwtable
define void @query_group_by_template(i8** nocapture readnone %byte_stream, i8* nocapture readonly %literals, i64* nocapture readnone %row_count_ptr, i64* nocapture readonly %frag_row_off_ptr, i32* %max_matched_ptr, i64* %agg_init_val, i64** %group_by_buffers, i64** %small_groups_buffer, i32 %frag_idx, i64* %join_hash_tables, i32* %total_matched, i32* %error_code) #21 {
.entry:
  %0 = getelementptr i8*, i8** %byte_stream, i32 0
  %1 = load i8*, i8** %0
  %2 = getelementptr i8*, i8** %byte_stream, i32 1
  %3 = load i8*, i8** %2
  %4 = getelementptr i8*, i8** %byte_stream, i32 2
  %5 = load i8*, i8** %4
  %6 = load i64, i64* %row_count_ptr, align 8
  %7 = load i32, i32* %max_matched_ptr, align 4
  %crt_matched = alloca i32
  %old_total_matched = alloca i32
  %8 = call i32 @pos_start_impl(i32* %error_code)
  %9 = call i32 @pos_step_impl()
  %10 = call i32 @group_buff_idx_impl()
  %11 = sext i32 %8 to i64
  %12 = getelementptr i64*, i64** %group_by_buffers, i32 %10
  %13 = load i64*, i64** %12, align 8
  %14 = call i64* @init_shared_mem_nop(i64* %13, i32 0)
  %15 = icmp slt i64 %11, %6
  br i1 %15, label %.loop.preheader, label %.exit

.loop.preheader:                                  ; preds = %.entry
  %16 = sext i32 %9 to i64
  br label %.forbody

.forbody:                                         ; preds = %20, %.loop.preheader
  %pos = phi i64 [ %11, %.loop.preheader ], [ %21, %20 ]
  %17 = call i32 @row_func(i64* %14, i64* null, i32* %crt_matched, i32* %total_matched, i32* %old_total_matched, i64* %agg_init_val, i64 %pos, i64* %frag_row_off_ptr, i64* %row_count_ptr, i8* %literals, i8* %1, i8* %3, i8* %5, i64* %join_hash_tables)
  %18 = call i32 @record_error_code(i32 %17, i32* %error_code)
  %19 = icmp ne i32 %18, 0
  br i1 %19, label %.error_exit, label %20

.error_exit:                                      ; preds = %.forbody
  ret void

; <label>:20:                                     ; preds = %.forbody
  %21 = add i64 %pos, %16
  %22 = icmp slt i64 %21, %6
  br i1 %22, label %.forbody, label %._crit_edge

._crit_edge:                                      ; preds = %20
  br label %.exit

.exit:                                            ; preds = %._crit_edge, %.entry
  call void @write_back_nop(i64* %13, i64* %14, i32 0)
  ret void
}

; Function Attrs: alwaysinline
define i32 @row_func(i64* %group_by_buff, i64* %small_group_by_buff, i32* %crt_match, i32* %total_matched, i32* %old_total_matched, i64* %agg_init_val, i64 %pos, i64* %frag_row_off, i64* %num_rows_per_scan, i8* %literals, i8* %col_buf0, i8* %col_buf1, i8* %col_buf2, i64* %join_hash_tables) #22 {
entry:
  %0 = load i64, i64* %frag_row_off
  br i1 true, label %filter_true, label %filter_false

filter_true:                                      ; preds = %entry
  %1 = alloca i64, i32 3
  %2 = call i64 @fixed_width_int_decode(i8* %col_buf0, i32 8, i64 %pos)
  %3 = getelementptr i64, i64* %1, i32 0
  store i64 %2, i64* %3
  %4 = call float @fixed_width_float_decode(i8* %col_buf1, i64 %pos)
  %5 = fpext float %4 to double
  %6 = bitcast double %5 to i64
  %7 = getelementptr i64, i64* %1, i32 1
  store i64 %6, i64* %7
  %8 = call float @fixed_width_float_decode(i8* %col_buf2, i64 %pos)
  %9 = fpext float %8 to double
  %10 = bitcast double %9 to i64
  %11 = getelementptr i64, i64* %1, i32 2
  store i64 %10, i64* %11
  %12 = call i64* @get_group_value(i64* %group_by_buff, i32 16384, i64* %1, i32 3, i32 8, i32 3, i64* %agg_init_val)
  %13 = icmp ne i64* %12, null
  br i1 %13, label %groupby_nullcheck_true, label %groupby_nullcheck_false

filter_false:                                     ; preds = %groupby_nullcheck_true, %entry
  ret i32 0

groupby_nullcheck_true:                           ; preds = %filter_true
  br label %filter_false

groupby_nullcheck_false:                          ; preds = %filter_true
  %14 = trunc i64 %pos to i32
  %15 = sub i32 0, %14
  ret i32 %15
}

mapdql>


mapdql> explain calcite select count(1) from (select sum(sst), count(1) from sst group by t, lat, lon);
Explanation
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalProject($f0=[1])
LogicalProject(EXPR$0=[$3], EXPR$1=[$4])
LogicalAggregate(group=[{0, 1, 2}], EXPR$0=[SUM($3)], EXPR$1=[COUNT()])
LogicalProject(t=[$0], lat=[$1], lon=[$2], sst=[$3], $f4=[1])
EnumerableTableScan(table=[[mapd, SST]])


#2

take a look to mapd_server.INFO, orposts the log.

i guess this query is going to take an huge quantity of memory and you have disabled the watchdog.

if i try a query with lower cardinality on a table wioth 460m records and using just 2 floats in the group by i get this message in the log

147483648B) created in 6 ms GPU_MGR:0
I1102 17:27:20.538422 28632 BufferMgr.cpp:266] ALLOCATION slab of 4194304 pages (2147483648B) created in 1 ms GPU_MGR:0
I1102 17:27:21.176165 26356 RelAlgExecutor.cpp:1659] Query ran out of GPU memory, punt to CPU

mapdql> select count(1) from ( select count(1),sum(total_amount) from yellow_tripdata group by dropoff_latitude,dropoff_longitude );
104235462
1 rows returned.
Execution time: 59958 ms, Total time: 60119 ms
mapdql>

and the query takes a lot…adding another fields i guess the system would run completly of of memory ( i estimated my query is taking 24GB of RAM, to run)

performance wise i can suggest to launch several queries (e.g. a query for every month/quarter) so that the group by dataset could fit on gpu memory and making the final reduction on client side tool and, if possible, to use a dictionary encoded timestamp to save some memory