exceeds the configured watchdog none-encoded string translation limit of 1000000 rows.

[jieguolove]

heavysql> select count() from heavyai_us_states a,flights_2008_7M b where a.abbr=b.dest_city and a.abbr='New York'; Query requires one or more casts between none-encoded and dictionary-encoded strings, and the estimated table size (14019456 rows) exceeds the configured watchdog none-encoded string translation limit of 1000000 rows. heavysql> select count() from heavyai_us_states a,flights_2008_7M b where a.name=b.dest_city and a.name='New York'; Query requires one or more casts between none-encoded and dictionary-encoded strings, and the estimated table size (14019456 rows) exceeds the configured watchdog none-encoded string translation limit of 1000000 rows. heavysql> select a.* from heavyai_us_states a,flights_2008_7M b where a.name=b.dest_city and a.name='New York' limit 3; Query requires one or more casts between none-encoded and dictionary-encoded strings, and the estimated table size (14019456 rows) exceeds the configured watchdog none-encoded string translation limit of 1000000 rows.

just test ,it's be limited? why? thanks!

[cdessanti]

Hi @jieguolove,

I guess you hit a BUG. We are working on it

We are sorry, Candido

[cdessanti]

Hi @jieguolove ,

A fast workaround to this issue can be set one of those database parameters watchdog-none-encoded-string-translation-limit=100000000000 or enable-watchdog=false It can be set in the heavy.conf file or from command line.

Hope this helps Candido

[jieguolove]

There is another problem: A single large table query is very fast in milliseconds, but if two large tables are associated with the query, the speed is very slow.Is this gpu database not suitable for multi-table association queries? Is it possible to support index creation in the future? thanks!

[cdessanti]

HI,

what do you mean by slow?

using the TPC-100 tables and joining the lineitem with orders is quite fast

heavysql> \timing
heavysql> select count(*) from orders;
EXPR$0
149999990
1 rows returned.
Execution time: 14 ms, Total time: 32 ms
heavysql> select count(*) from lineitem;
EXPR$0
480025129
1 rows returned.
Execution time: 159 ms, Total time: 160 ms
heavysql> select count(*) from lineitem join orders on L_ORDERKEY=O_ORDERKEY;

EXPR$0
480025097
1 rows returned.
Execution time: 1270 ms, Total time: 1271 ms
heavysql> 
heavysql> select date_trunc(month, O_ORDERDATE), sum(L_EXTENDEDPRICE) from lineitem join orders on L_ORDERKEY=O_ORDERKEY group by 1 order by 2 desc limit 10;

EXPR$0|EXPR$1
1996-12-01 00:00:00|236921255725.16
1995-10-01 00:00:00|236839511866.18
1996-10-01 00:00:00|236826744388.51
1997-08-01 00:00:00|236798976129.21
1996-01-01 00:00:00|236790425463.58
1998-05-01 00:00:00|236786420810.85
1997-01-01 00:00:00|236755131191.32
1996-03-01 00:00:00|236739623427.68
1996-08-01 00:00:00|236718915792.09
1993-03-01 00:00:00|236706276270.02
10 rows returned.
Execution time: 1276 ms, Total time: 1276 ms
heavysql> 
heavysql> select date_trunc(month, O_ORDERDATE), sum(L_EXTENDEDPRICE) from lineitem join orders on L_ORDERKEY=O_ORDERKEY group by 1 order by 2 asc limit 10;
EXPR$0|EXPR$1
1998-08-01 00:00:00|15297146099.20
1994-02-01 00:00:00|213382813502.62
1995-02-01 00:00:00|213592777401.46
1998-02-01 00:00:00|213732597803.70
1993-02-01 00:00:00|213823673316.60
1997-02-01 00:00:00|213835897333.70
1996-02-01 00:00:00|221029173087.41
1992-02-01 00:00:00|221321233025.08
1996-09-01 00:00:00|228411170644.40
1995-11-01 00:00:00|228456058972.39
10 rows returned.
Execution time: 175 ms, Total time: 176 ms
heavysql> select date_trunc(year, O_ORDERDATE), sum(L_EXTENDEDPRICE) from lineitem join orders on L_ORDERKEY=O_ORDERKEY group by 1 order by 2 asc limit 10;
EXPR$0|EXPR$1
1998-01-01 00:00:00|1632686808061.99
1993-01-01 00:00:00|2783498814961.83
1995-01-01 00:00:00|2784006097518.12
1997-01-01 00:00:00|2784797404259.98
1994-01-01 00:00:00|2785079242969.93
1992-01-01 00:00:00|2791791115050.66
1996-01-01 00:00:00|2792923575250.97
7 rows returned.
Execution time: 356 ms, Total time: 357 ms

The latest two join are faster because there is no need to read anything from disk. I run these queries using 2 RTX 2080ti tables, and the 6.0 release (the 50.10 is really slower in the first join) I also used shared table to better distribute the data between the two GPUs.

Candido

[jieguolove]

The query was inconclusive for several minutes, so I Ctrl+c. `omnisky@omnisky-Super-Server:~$ $HEAVYAI_PATH/bin/heavysql -p HyperInteractive User admin connected to database heavyai heavysql> \timing heavysql> select count() from j1; EXPR$0 1778311840 1 rows returned. Execution time: 77 ms, Total time: 79 ms heavysql> \t heavyai_us_states heavyai_us_counties heavyai_countries test flights_2008_7M us_states flights_2008_10k j1 heavysql> select count() from flights_2008_10k; EXPR$0 105155920 1 rows returned. Execution time: 38 ms, Total time: 42 ms heavysql> select count() from j1 a,flights_2008_10k b where a.flight_year=b.flight_year and b.flight_year='2008'; ^C Interrupt signal (2) received. Asking server to interrupt query. ERR_INTERRUPTED: Query execution has been interrupted heavysql> select count() from j1 a join flights_2008_10k b on a.flight_year=b.flight_year and b.flight_year='2008'; ^C Interrupt signal (2) received. Asking server to interrupt query. ERR_INTERRUPTED: Query execution has been interrupted heavysql> select count(*) from j1 a join flights_2008_10k b on a.flight_year=b.flight_year; ^C Interrupt signal (2) received. Asking server to interrupt query. ERR_INTERRUPTED: Query execution has been interrupted heavysql> \memory_summary HeavyDB Server CPU Memory Summary: MAX USE ALLOCATED FREE 98304.00 MB 98223.84 MB 98304.00 MB 80.15 MB

HeavyDB Server GPU Memory Summary: [GPU] MAX USE ALLOCATED FREE [0] 32768.00 MB 3592.44 MB 10240.00 MB 6647.56 MB The allocation is capped!

heavysql> `

[cdessanti]

Hi, You are running something like a cross join of a table that contains 1.7 billion rows with another one containing 100m, so you're asking to check the system about 1 Zetta (10E21) equalities...something like a sextillion or a 1,700,000,000,000,000,000,000.

To continue your evaluation I suggest you try something moresignificant.

Regards, Candido