antonmks/alenka

GPU database engine

alenka's Issues

What functions are used for sorting and external sorting of strings?

I have not found where are used the functions str_sort() and str_sort_host() from strings.cu. They are used somewhere?
And what functions are used for sorting and external sorting of strings?

missing function to_strings in bison.y

compile fail after git clone. function to_string is used in bison.y but never defined. the following should work and can be added into bison.y/bison.cu

include "sstream"

string to_string(long long int i) {
stringstream res;
res << i;
return res.str();
}

regards,
George

Problems with loading data

Dear Anton,

First i want to say this is remarkable project. Analyzing large dataset using GPUs is great idea.

I have a problem with correct select statement.
I load file (around 30M rows) into BINARY structure.
When load this binary structure and make basic SQL
D := SELECT sccode AS sccode1, product AS product1, SUM(sale) AS sale_sum
FROM A GROUP BY sccode, product;
I get less rows then same sql but on database.76K instead of 500k.Some products are missing which i for sure have in bigtable in DB.

More strange is when i user FILTER product <= 100000 (this number is not important max product code is 40000) i get around 160K

Can you tell whats wrong with my sql statement?

For loading data i use
A := LOAD 'bigtable.csv' USING (',') AS (uniqueid{1}:int, ccode{2}:varchar(10), acode{3}:varchar(10), sccode{4}:varchar(10), supplier{5}:int, product{6}:int, sale{15}:decimal);
STORE A INTO 'bigtable' BINARY;

Best Regards,

get some wrong results

using dbgen to generate 20G data and then running q1 and q6

and i check the results with mysql and monetdb.

and the result running q6 with alenka and mysql ,monetdb the result is the same

but running q1 i get different results

alenka results:
A|F|1132393551078.85|755169104|25.50|38239.09|0.05|1118803345410.88|1075769269507.28|0.04|29613505|
R|F|1132556540140.25|755284493|25.51|38246.80|0.05|1118968269619.38|1075930611640.11|0.04|29611797|
N|O|2229413402673.42|1486771871|25.50|38235.37|0.05|2202675535336.87|2117943983015.22|0.04|58307621|
N|F|29582901058.72|19739848|25.53|38262.38|0.05|29230208447.21|28103693343.31|0.04|773159|

mysql results:
N|O|2293949394287.65|1529813855|25.50|38234.94|0.05|2266438501095.17|2179254402765.45|0.04|53307656|
R|F|1132556540140.25|755284493|25.51|38246.80|0.05|1118968269619.38|1075930611640.11|0.04|29611797|
A|F|1132393551078.85|755169104|25.50|38239.09|0.05|1118803345410.88|1075769269507.28|0.04|29613505|
N|F|29582901058.72|19739848|25.53|38262.38|0.05|29230208447.21|28103693343.31|0.04|773159|

we get four rows and only one row(the first row of mysql resluts) is different with alenka and the result with monetdb is same as mysql

cannot compile on debian wheezy

Compilation of Alenka fails under Linux:

$ make
/usr/local/cuda/bin/nvcc --machine 64 -O3 -arch=sm_20 -c bison.cu
In file included from bison.y:20:0:
cm.h:50:18: fatal error: itoa.h: No such file or directory
compilation terminated.
make: *** [bison.o] Error 1```

I've commented out #include "itoa.h"line in cm.h, in this case compilation also fails:

[...]
bison.y(529): error: explicit type is missing ("int" assumed)

bison.y(529): error: no suitable conversion function from "std::_Rb_tree_const_iterator<std::string>" to "int" exists

bison.y(529): error: no operator "!=" matches these operands
        operand types are: int != std::_Rb_tree_const_iterator<std::string>

bison.y(530): error: operand of "*" must be a pointer

[...]
35 errors detected in the compilation of "/tmp/tmpxft_00002ae3_00000000-6_bison.cpp1.ii".
make: *** [bison.o] Error 2

Here is my environment:

$ uname -a
Linux crunchbang 3.2.0-4-amd64 #1 SMP Debian 3.2.46-1+deb7u1 x86_64 GNU/Linux

$ nvcc --version
nvcc: NVIDIA (R) Cuda compiler driver
Copyright (c) 2005-2013 NVIDIA Corporation
Built on Wed_Jul_17_18:36:13_PDT_2013
Cuda compilation tools, release 5.5, V5.5.0

$ g++ --version
g++ (Debian 4.7.2-5) 4.7.2

Invalid column in select terminates Alenka

select badcol from orders;
terminate called after throwing an instance of 'thrust::system::system_error'
what(): invalid argument

... called from CudaSet::addDeviceColumn

Out of Memory with large intermediate result set

Suppose i have the following query:
A := LOAD 'member_skill' BINARY AS (a_memberid{1}:int, skill{2}:varchar2(40));
B := LOAD 'mlt_k133B' BINARY AS (b_memberid{1}:int);
J := SELECT skill AS sk FROM A JOIN B on a_memberid = b_memberid;
G := SELECT sk AS skill_name, COUNT(sk) AS cnt FROM J GROUP BY sk;
R := ORDER G BY cnt DESC;
STORE R INTO 'member_skill_top10.txt' USING ('|') LIMIT 10;

A is 500 million and B is 12 million, J will be 12 million with varchar2(40), then when run group by on J, will get out of memory error.

If i store J as binary and load it again like the following, it looks like Alenka will not work?
STORE J INTO 'member_skill_temp' BINARY;
J2 := LOAD 'member_skill_temp' BINARY AS (skill_name{1}:varchar2(40));

Can the intermediate result be automatically partitioned if too large for device memory? What's the suggested workaround if not?

thanks,
George

Load error

• loading lineitem requires using ./alenka -l 5000000 even though I have 1GB of RAM in the 650 ti

I'm on Centos 6.4 with GCC 4.8.1, Cuda 5.5

./alenka load_lineitem.sql
GeForce GTX 650 Ti : 1032.500 Mhz (Ordinal 0)
4 SMs enabled. Compute Capability sm_30
FreeMem: 907MB TotalMem: 1023MB.
Mem Clock: 2700.000 Mhz x 128 bits (86.400 GB/s)
ECC Disabled

Process count = 6200000
LOAD: A sorted_lineitem.tbl 16 |
STORE: A lineitem
LOADING sorted_lineitem.tbl mem: 949477376
partition recs 6200000
Could not allocate 272800000 bytes of GPU memory for 6200000 records

Joins fail silently on 10 GB dataset

GeForce GTX 650 Ti : 1032.500 Mhz (Ordinal 0)
4 SMs enabled. Compute Capability sm_30
FreeMem: 916MB TotalMem: 1023MB.
Mem Clock: 2700.000 Mhz x 128 bits (86.400 GB/s)
ECC Disabled

Process count = 5000000
BINARY LOAD: L lineitem
Reading 600037902 records
segment sorted on 0
INLINE FILTER L
BINARY LOAD: S supplier
Reading 1000000 records
BINARY LOAD: C customer
Reading 15000000 records
BINARY LOAD: N nation
Reading 25 records
INLINE FILTER N
BINARY LOAD: N1 nation
Reading 25 records
INLINE FILTER N1
BINARY LOAD: O orders
Reading 150000000 records
JOIN SN s_nationkey n_nationkey1 900325376

join count 0
join time 0 880271360
JOIN LJ1 c_nationkey n_nationkey 900325376

join count 0
join time 0 880271360
JOIN LJ2 o_custkey c_custkey 900325376
Join result LJ : 0
JOIN LS l_suppkey s_suppkey 900325376
Join result LS : 0
SELECT T count : 0, Mem 900325376
SELECT G count : 0, Mem 900325376
STORE: GO mytest.txt |
SQL scan parse worked
tot disk time 0
cycle time 0.11

(Note: mytest.txt is null )
I'm on Centos 6.4 with GCC 4.8.1, Cuda 5.5

loading from file crash at thrust reduce_by_key

Hi Anton,
alenka crashed when loading, do you see what should be done to solve this? This is on Mac OS X.
Test Case:
Alenka]$ cat testtable.tbl
1|a
2|b
3|c
Alenka]$ cat load_testtable.sql
T := LOAD 'testtable.tbl' USING ('|') AS (id{1}:int, value{2}:varchar(20));
STORE T INTO 'testtable' BINARY;
Alenka]$./alenka -l 1 load_testtable.sql
Process count = 1
LOAD: T testtable.tbl 2 |
STORE: T testtable
LOADING testtable.tbl |
libc++abi.dylib: terminate called throwing an exception
Abort trap: 6

Analysis:
(gdb) s
pfor_delta_compress (source=0x500240000, source_len=8, file_name=0x7fff5fbfe9b8 "testtable.1.0", host=@0x102d22c00, tp=false, sz=0) at compress.cu:287

389 thrust::device_ptr fin_seq = thrust::device_malloc(cnt);
(gdb)
391 thrust::reduce_by_key(add_seq, add_seq+recCount,s_copy1,thrust::make_discard_iterator(),
(gdb)
libc++abi.dylib: terminate called throwing an exception

Program received signal SIGABRT, Aborted.
0x00007fff956b8212 in __pthread_kill ()

thanks,
George

Porting Alenka to 32bit OS

It would be good if users could run Alenka on 32bit platforms.

Why is STREAM load removed?

Why is STREAM load removed?
The current version can't parse load_lineitem.sql etc.

Can I use multiple predicates in the JOIN?

Hi Anton!
Can I use multiple predicates in the JOIN?
An example in q2m.sql if I add AND ps_partkey = s_suppkey:

J_PS := SELECT n_name AS n_name, ps_partkey AS ps_partkey, ps_supplycost AS ps_supplycost, s_name AS s_name, s_address AS s_address,  s_nationkey AS s_nationkey, s_phone AS s_phone, s_acctbal AS s_acctbal, s_comment AS s_comment
    FROM PS JOIN S ON ps_suppkey = s_suppkey AND ps_partkey = s_suppkey
    JOIN N ON s_nationkey = n_nationkey
            JOIN RF ON n_regionkey = r_regionkey;   

Then I get a message:

JOIN J_PS2 ps_partkey s_suppkey
Couldn't find column ps_partkey

Regards,
Alexey

How can I use LEFT in multi-table JOIN?

Hi Anton!
If I use LEFT in a single-table JOIN then it works. But if I use LEFT in multi-table JOIN (non-star join), then I get an error: syntax error
SQL scan parse failed

How can I use LEFT in multi-table JOIN?

Best regards,
Alexey

I tested Alenka on TPC-H on my GPU GTX 460SE, 1GB, 288 Cores, SM2.0.

Hi Anton!
I tested Alenka on TPC-H on my GPU GTX 460SE, 1GB, 288 Cores, SM2.0.

Can you show the results on your GPU and on the others GPU, on which you tested?

I have such results:

TPC-H	GPU SM2.0 GTX 460SE 1GB 288 Cores
Volume of DB:	10GB		100GB
Launch number:	1st launch	2nd launch	1st launch	2nd launch
q1.sql	5,3	3,8	90	90
q2.sql	1	0,36	9,3	2,3
q3.sql	1,8	1,7	71	67
q4.sql	4,25	1,3	45	13
q5.sql	-	-	-	-
q6.sql	1,77	0,9	39	8
q7.sql	8,7	2	-	-

Regards, Alexey

Query on known custkey results in "found Nothing" and then a segfault

The custkey '36901' is a valid existing custkey.
This query which should return at least 1 row fails:

A := FILTER orders BY o_custkey=36901 ;
A2 := SELECT o_orderkey AS o_orderkey,o_orderpriority AS o_orderpriority,o_shippriority AS o_shippriority,o_orderdate AS o_orderdate,o_custkey AS o_custkey FROM A;
DISPLAY A2 USING ('|');

returns:
found nothing
Segmentation fault (core dumped)

GDB reports:

(gdb) bt
#0 0x00000000006a2572 in zone_map_check(std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, std::queue<long long, std::deque<long long, std::allocator > >, std::queue<double, std::deque<double, std::allocator > >, CudaSet*, unsigned int) ()
#1 0x00000000006c553a in filter_op(char_, char_, unsigned int) ()
#2 0x00000000006c4e04 in copyColumns(CudaSet*, std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, unsigned int, unsigned long&, bool, bool) ()
#3 0x00000000004204a5 in emit_select(char_, char_, int) ()
#4 0x000000000042c45c in yyparse() ()

run on windows

hi,anton
have you ever tested it on windows by using nmake? and also i want to make it possible to run in vs2010 or some other IDE ,how can i achieve that?

Is there in AlenkaDB such a thing as NULL?

Hello Anton.
Is there in AlenkaDB such a thing as NULL? I get an exception, when I load the data from the pipe-delimited files:

1388582970|152470|2025211056|14.09.2012|0|0|0|0|0|0|0|0|0|0|196407|ABC||||||

This variant works fine:

1388582970|152470|2025211056|14.09.2012|0|0|0|0|0|0|0|0|0|0|196407|ABC|6|5|4|3|2|1

And if NULL is not implemented, then the quickest solution would be to use the maximum value possible for this type of field, when downloading from the pipe-delimited file.

error when using nmake

NMAKE : fatal error U1073: don't know how to make 'join.obj'

problem with Boost 1.53.0

Also when compiling Alenka with boost 1.53.0 on windows 32-bit & cuda 5.0 - I'm getting error:

C:\Program Files\NVIDIA GPU Computing Toolkit\CUDA\v5.0\bin/../include\boost/uno
rdered/detail/unique.hpp(342) : error C2248: 'boost::unordered::iterator_detail:
:iterator::node_' : cannot access private member declared in class 'boost:
:unordered::iterator_detail::iterator'
with
[
Node=boost::unordered::detail::ptr_node<std::pair<const _int64,unsi
gned int>>
]
C:\Program Files\NVIDIA GPU Computing Toolkit\CUDA\v5.0\bin/../include\b
oost/unordered/detail/buckets.hpp(199) : see declaration of 'boost::unordered::i
terator_detail::iterator::node'
with
[
Node=boost::unordered::detail::ptr_node<std::pair<const __int64,unsi
gned int>>
]
C:\Program Files\NVIDIA GPU Computing Toolkit\CUDA\v5.0\bin/../include\b
oost/unordered/detail/unique.hpp(336) : while compiling class template member fu
nction 'std::pair<_Ty1,_Ty2> &boost::unordered::detail::table_impl::opera
tor [](const __int64 &)'
with
[
_Ty1=const __int64,
_Ty2=unsigned int,
Types=boost::unordered::detail::map<std::allocator<std::pair<const _
_int64,unsigned int>>,__int64,unsigned int,boost::hash<int64>,std::equal_to<
int64>>
]
C:\Program Files\NVIDIA GPU Computing Toolkit\CUDA\v5.0\bin/../include\b
oost/unordered/unordered_map.hpp(73) : see reference to class template instantia
tion 'boost::unordered::detail::table_impl' being compiled
with
[
Types=boost::unordered::detail::map<std::allocator<std::pair<const _
_int64,unsigned int>>,int64,unsigned int,boost::hash<__int64>,std::equal_to<
int64>>
]
merge.cu(103) : see reference to class template instantiation 'boost::un
ordered::unordered_map<K,T,H,P,A>' being compiled
with
[
K=__int64,
T=unsigned int,
H=boost::hash<__int64>,
P=std::equal_to<__int64>,
A=std::allocator<std::pair<const __int64,unsigned int>>
]
NMAKE : fatal error U1077: '"C:\Program Files\NVIDIA GPU Computing Toolkit\CUDA
v5.0\bin\nvcc.EXE"' : return code '0x2'
Stop.

Can somebody help ?

compression problem

using pfor compression method is to store the array like(original-minimum)
but why still need to shift like this:
unsigned int shifted = vals[2] - vals[0] - (i%vals[1])*vals[0];
dest[i] = val << shifted;

Compile Error

I am hitting error while trying to make Alenka on my local machine, and I am not able to see exactly why. I cmake Cudpp 2.0 and copy the lib folder into Alenka with the moderngpu, then when I try to Make i hit the following. I have uninstalled and reinstalled cuda, nvidia drivers and others but keep hitting the same error. Any idea what I might be doing wrong?

/usr/local/cuda/bin/nvcc --machine 64 -O3 -arch=sm_20 -c bison.cu
fl.l(35): warning: statement is unreachable

fl.l(36): warning: statement is unreachable

fl.l(37): warning: statement is unreachable

fl.l(38): warning: statement is unreachable

fl.l(39): warning: statement is unreachable

fl.l(40): warning: statement is unreachable

fl.l(41): warning: statement is unreachable

fl.l(42): warning: statement is unreachable

fl.l(43): warning: statement is unreachable

fl.l(44): warning: statement is unreachable

fl.l(45): warning: statement is unreachable

fl.l(46): warning: statement is unreachable

fl.l(47): warning: statement is unreachable

fl.l(48): warning: statement is unreachable

fl.l(49): warning: statement is unreachable

fl.l(50): warning: statement is unreachable

fl.l(51): warning: statement is unreachable

fl.l(52): warning: statement is unreachable

fl.l(53): warning: statement is unreachable

fl.l(54): warning: statement is unreachable

fl.l(55): warning: statement is unreachable

fl.l(56): warning: statement is unreachable

fl.l(57): warning: statement is unreachable

fl.l(58): warning: statement is unreachable

fl.l(59): warning: statement is unreachable

fl.l(60): warning: statement is unreachable

fl.l(61): warning: statement is unreachable

fl.l(62): warning: statement is unreachable

fl.l(63): warning: statement is unreachable

fl.l(64): warning: statement is unreachable

fl.l(65): warning: statement is unreachable

fl.l(66): warning: statement is unreachable

fl.l(67): warning: statement is unreachable

fl.l(70): warning: statement is unreachable

fl.l(75): warning: statement is unreachable

fl.l(77): warning: statement is unreachable

fl.l(78): warning: statement is unreachable

fl.l(79): warning: statement is unreachable

fl.l(81): warning: statement is unreachable

fl.l(82): warning: statement is unreachable

fl.l(83): warning: statement is unreachable

fl.l(86): warning: statement is unreachable

fl.l(87): warning: statement is unreachable

fl.l(88): warning: statement is unreachable

fl.l(89): warning: statement is unreachable

fl.l(90): warning: statement is unreachable

fl.l(91): warning: statement is unreachable

fl.l(92): warning: statement is unreachable

fl.l(93): warning: statement is unreachable

fl.l(94): warning: statement is unreachable

fl.l(95): warning: statement is unreachable

fl.l(96): warning: statement is unreachable

fl.l(98): warning: statement is unreachable

fl.l(101): warning: statement is unreachable

fl.l(103): warning: statement is unreachable

fl.l(104): warning: statement is unreachable

fl.l(105): warning: statement is unreachable

lex.yy.c(828): warning: label "find_rule" was declared but never referenced

lex.yy.c(1607): warning: statement is unreachable

bison.y(1092): error: namespace "std" has no member "to_string"

bison.y(1108): error: namespace "std" has no member "to_string"

bison.y(2344): error: namespace "std" has no member "to_string"

bison.y(2351): warning: result of call is not used

bison.y(2352): warning: result of call is not used

bison.y(2353): warning: result of call is not used

3 errors detected in the compilation of "/tmp/tmpxft_0000159e_00000000-6_bison.cpp1.ii".
make: *** [bison.o] Error 2

Can I perform table join on varchar field?

As per title can I perform table join on varchar field for Alenka?
My attempt on it thus far failed with application crash anyway.

Can't compile strings_join.cu on linux

Anton, when I compile the new code line I get:

nvcc --machine 64 -O3 -arch=sm_20 -c strings_join.cu
strings_join.cu(48): error: argument list for class template "is_positive" is missing
detected during instantiation of "__nv_bool T_unroll_functor<unroll_count, T_functor>::operator()(T1 &, T2 &, T3 &, T4 &, unsigned int) [with unroll_count=40U, T_functor=T_str_gather, T1=thrust::device_ptr, T2=const size_t, T3=void *, T4=void *]"
(58): here

1 error detected in the compilation of "/tmp/tmpxft_0000475e_00000000-6_strings_join.cpp1.ii".

I don't get all the rows when try to fetch single table

Hi Anton ,
I'm try to run those commands :
R := LOAD 'nation' BINARY AS (nationkey{1}:int, regionkey{3}:int);
RS := SELECT nationkey as n_nationkey ,regionkey as n_regionkey FROM R ;
STORE RS INTO 'nation.sql.txt' USING ('|') LIMIT 10;

and I get output -
Process count = 6200000
BINARY LOAD: R nation
SELECT RS R
cycle 0 select mem 2751848448
final select 1
select time 0.102
STORE: RS nation.sql.txt |
SQL scan parse worked
cycle time 0.11
0|0|

While the table has more rows (it is the nation table from TPHC).

I'm using the lastest version from github.

Anyidea ? where to look ?

thanks
Amihay.

Bad filter column causes a segfault

Poorly written filter expressions cause segfaults rather than an error.
eg:
unnamed_A := FILTER orders BY o_orderkeyxxxxxxx <22 ;
unnamed_A2 := SELECT o_orderkey AS o_orderkey FROM unnamed_A;
DISPLAY unnamed_A2 USING ('|');

results in:
Segmentation fault (core dumped)

gdb backtrace shows:
(gdb) bt
#0 0x000000000069f860 in host_compare(long long*, long long, long long) ()
#1 0x00000000006ac030 in zone_map_check(std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, std::queue<long long, std::deque<long long, std::allocator > >, std::queue<double, std::deque<double, std::allocator > >, CudaSet*, unsigned int) ()
#2 0x00000000006c447d in filter_op(char_, char_, unsigned int) ()
#3 0x00000000006c3d74 in copyColumns(CudaSet*, std::queue<std::basic_string<char, std::char_traits, std::allocator >, std::deque<std::basic_string<char, std::char_traits, std::allocator >, std::allocator<std::basic_string<char, std::char_traits, std::allocator > > > >, unsigned int, unsigned long&, bool, bool) ()
#4 0x00000000004204a5 in emit_select(char_, char_, int) ()
#5 0x000000000042c45c in yyparse() ()

identifier "atoll" is undefined

I'm getting error "identifier "atoll" is undefined" in file cm.cu.
I have traced problem to this code:

`#ifdef _WIN64

define atoll(S) _atoi64(S)

endif`

and I'm building on Win32 - probably that is why such problem.
I've re-coded it as:

`#ifdef _WIN64
#define atoll(S) _atoi64(S)

else

#define atoll(S) atoi(S)

endif

`

and at least it builds on 32bit system. But i feel that it can break your code somewhere else, so i'm not sure is this hack really solves problem for 32bit machine.

Why I can not use a decimal number in the summation of numbers - sum aggregate function?

Hi Anton!
Why I can not use a decimal number in the summation of numbers - sum aggregate function?

For example take a table "partsupp" from TPC-H 10GB - table that used in q2m.sql.
If I calculate sum of the decimal numbers, grouped by integers:

PS := LOAD 'partsupp' BINARY AS 
    ( ps_partkey{1}:int, ps_suppkey{2}:int, ps_supplycost{4}:decimal);

RES := SELECT sum(ps_supplycost) as sum_ps_supplycost
 FROM PS
 GROUP BY ps_suppkey;

STORE RES INTO 'res_q2m.txt' USING ('|') LIMIT 100;

I got a message:

No group by on float/decimal columns

If I use it so:

PS := LOAD 'partsupp' BINARY AS ( ps_partkey{1}:int, ps_suppkey{2}:int, ps_supplycost{4}:decimal);

RES := SELECT ps_suppkey as ps_suppkey, sum(ps_supplycost) as sum_ps_supplycost
 FROM PS
 GROUP BY ps_suppkey;

STORE RES INTO 'res_q2m.txt' USING ('|') LIMIT 100;

then get an exception.

But if I calculate sum of the integer numbers, grouped by decimals, then all goes well:

PS := LOAD 'partsupp' BINARY AS 
    ( ps_partkey{1}:int, ps_suppkey{2}:int, ps_supplycost{4}:decimal);

RES := SELECT sum(ps_suppkey) as sum_ps_suppkey
 FROM PS
 GROUP BY ps_supplycost;

STORE RES INTO 'res_q2m.txt' USING ('|') LIMIT 100;

I have result:

3900654|
1981506|
2939347|
3569270|
2453065|
3689991|
...

It may should be vice versa? (Can calculate sum of the decimal numbers, and cann't opposite)

An additional.
As I know, Alenka-integer is a 64-bit signed value.
What precision of the Alenka-decimal number and what ranges of values it can have?

Alexey.

Q: some questions on db structure on alenka

Hi anton , I've looked in your project and really enjoyed on the idea and the concept.

I've being working with ORACLE RDBMS for about 15 year and have done several projects big projects.
I think that using of column db and gpgu is good concept and can give a real boost to data analsis.

I've some questions regarding how you've implement column db in alenka project.

if I understand correctly , every column is represented by a file which is compress , so for example for a table with 3 columns , we'll 3 files.

1.what happens on insert statement , does the new row go the end of the file (FIFO) , I assume that the file is zipped for every insert operation (or bulk ) ?
if so , what will happens if there will by serveral threads performing inserts ?

1. How do you corrdiante betweens values , for example if we got table of two columns test (a number,b number)
   and we run the query : select * from test where a=1 and b=2 ;
   How alenka retrive the correct row , is it based of row position in the file ?
   If so , what will happen if the insert doesn't contain data - like insert (5, null )

I hope you can help me on those questions .

Regards,
Amihay

q2 throws a 'thrust::system::system_error' on 10 GB

./alenka q2.sql
GeForce GTX 650 Ti : 1032.500 Mhz (Ordinal 0)
4 SMs enabled. Compute Capability sm_30
FreeMem: 916MB TotalMem: 1023MB.
Mem Clock: 2700.000 Mhz x 128 bits (86.400 GB/s)
ECC Disabled

Process count = 6200000
BINARY LOAD: N nation
Reading 25 records
BINARY LOAD: R region
Reading 5 records
INLINE FILTER R
BINARY LOAD: S supplier
Reading 10000 records
BINARY LOAD: PS partsupp
Reading 800000 records
BINARY LOAD: P part
Reading 200000 records
INLINE FILTER P
JOIN T ps_partkey p_partkey 958390272
terminate called after throwing an instance of 'thrust::system::system_error'
what(): unspecified launch fail and had some problems.
Aborted (core dumped)

• I'm on Centos 6.4 with GCC 4.8.1, Cuda 5.5

Whether to do short description of the architecture and implementation?

Hi Anton!
Could you make more comments in the code for the major functions?

Do you think, for the development of the project by other developers need to do a short description of the architecture?
Or to describe in the following topics until here:

1. What are the structure, characteristics and differences of the Alenka store from the classic columnar storage?
   References describe of data storage:
   http://dbmsmusings.blogspot.ru/2009/09/tour-through-hybrid-columnrow-oriented.html
   Presentation by Daniel Abadi:
   http://www.cs.yale.edu/homes/dna/talks/Column_Store_Tutorial_VLDB09.pdf
2. What libraries and algorithms uses for each of these operations:
   • SORT for numbers/strings: thrust::stable_sort_by_key() ...
   • JOIN for numbers: mgpu::RelationalJoin<MgpuJoinKindInner / MgpuJoinKindLeft / MgpuJoinKindRight / MgpuJoinKindOuter>() ...
   • JOIN for strings: thrust::gather_if() ...
   • FILTER for numbers/strings: thrust::copy_if() ..
   • GROUP BY for numbers/strings: ...
3. How does resolve conflicts when using MurmurHash2_64?
4. What are the features and differences of Alenka compression from the classic compression implementations PFOR, DELTA-PFOR, Dictionary?

Maybe in the future to create a wiki or describe it here now?

segment size？

why every segment size is 6200000, i think it would be ok if the size is larger if the device is 1G .and we can also we can design a strategy that based on the device memory

group by operation？

how did you achieve group by operation?since if you do the group by operation segment by segment, and the final result must merge the same record? or you used some another way to achieve group by ?

Faster joins

I added an option to sort database segments locally on a join key. The best performance can be reached by sorting the data file on a filter key and then sorting segments locally on a join key. This way the expensive sorting of the datasets can be avoided in join operations.
So, for example, the line
STORE A INTO 'lineitem' BINARY;
now becomes
STORE A INTO 'lineitem' BINARY SORT SEGMENTS BY orderkey;

Running time of Q3 changed from 15 to 7 seconds,
Q5 from 68 to 17 seconds.

DISTINCT on int

Hi.

I try to run Alenka with DISTINCT int, but failed.

Thank you.

File q2-distinct.sql

P := LOAD 'part' BINARY AS (p_partkey{1}:int, p_mfgr{3}:varchar(25), p_type{5}:varchar(25), p_size{6}:int);
Q := SELECT DISTINCT p_size AS mypsize FROM P; 
STORE Q INTO 'mytest6distinct.txt' USING ('|');

$ LD_LIBRARY_PATH=/workspace/alenka-devel/alenka-master/cudpp_src_2.0/lib /workspace/alenka-devel/alenka-master/alenka q2-distinct.sql 
Tesla C2075 : 1147.000 Mhz   (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem:   5305MB   TotalMem:   5375MB.
Mem Clock: 1566.000 Mhz x 384 bits   (150.336 GB/s)
ECC Enabled

Process count = 6200000 
BINARY LOAD: P part 
Reading 1000000 records
opening part.sort
SELECT Q P 5563211776
terminate called after throwing an instance of 'thrust::system::system_error'
  what():  invalid argument
Aborted 0 select mem 5546958848

Compile fail with Thrust 1.5

get the following error when compile on Mac OS X:

Alenka]$ make
/usr/local/cuda/bin/nvcc -O3 -arch=sm_20 -m64 -I/usr/local/boost_1_53_0 -c bison.cu
fl.l(34): warning: statement is unreachable

fl.l(35): warning: statement is unreachable
..
lex.yy.c(804): warning: label "find_rule" was declared but never referenced

lex.yy.c(1558): warning: statement is unreachable

bison.y(824): error: no instance of function template "thrust::stable_partition" matches the argument list
argument types are: (thrust::detail::normal_iterator<thrust::device_ptr>, thrust::detail::normal_iterator<thrust::device_ptr>, thrust::device_ptr<__nv_bool>, thrust::identity)

bison.y(825): error: no instance of function template "thrust::stable_partition" matches the argument list
argument types are: (thrust::detail::normal_iterator<thrust::device_ptr>, thrust::detail::normal_iterator<thrust::device_ptr>, thrust::device_ptr<__nv_bool>, thrust::identity)

2 errors detected in the compilation of "/tmp/tmpxft_000004f6_00000000-6_bison.cpp1.ii".
make: *** [bison.o] Error 2

I am using CUDA5.0 bundled thrust library:
$ grep 100503 /usr/local/cuda/include/thrust/version.h

define THRUST_VERSION 100503

regards,
George

Extra semicolon in cm.cu

I have successfully compiled Alenka and attempted to execute q1.sql.
However, it hits an error which indicating a CUDA error at line 830 in file cm.cu.
I wasn't sure if it was intended, I noticed an extra semicolon at the end of the if-statement at line 829 which precedes the line that giving me error.
An attempt to remove the semicolon, compile and run Alenka again seems to work now.

zone map question

when doing filter ，zone map is used to filter some block that is not between the number of [min,max] ,it has a great help in a sorted column,but seems has little affect on column that not sorted and the number is random?
and also when doing filter, it seems transfer the needed compressed column to device and also to decompress the data to do fiter operation, will it cost some time,i mean whether alenka filter operation has increase speed over same cpu filter operation ?

Alenka crashed with CUDA 5.5

So the CUDA 5.5 has finally hit production release and I've tried to recompile the whole thing with it.
It was able to compile without error but the program crashed almost immediately after launch.

can't compile

I'm compiling the code base on CentOS. But the compiler option --compiler-options "-std=c++0x" is not working. Any suggestions ?

Pubs?

Anton, have you published anything on this work yet? I'm looking forward to reading it. This is an "issue" for me. :)

Compile error in bison.y

Hi Alenka,

When trying to compile this error is show
"bison.y(1335): error: class "is_positive" may not have a template argument list"
As i see is_positive in cm.h

Regards,

Problems with group by

Hi Alenka,

Is it possible that with larger datasets Group BY with varchar field crashes application ?

I tried dataset around 30M rows and group by can be done with int fields , but when i use varchar field it crashes.

I tries with smaller dataset (couple of rows) and it worked.

This field cannot be NULL because its a primary key in database.

Testing enviroment:
GeForce GTX 760 2GB
Windows 8 Pro
Cuda toolkit 5.5
Change in MakeFile sm_35 to sm_30

Regards,

JDBC driver

Hi,

We have written an open source analytics tool called Saiku, and we like demoing its capabilities over different or more interesting data sources, do you have a JDBC driver for Alenka?

Thanks

Tom

Arises exceptions when JOIN or FILTER tables, with a tables size of 1000000 rows.

Hi Anton!
Very often arise exceptions when joining or filtering tables. This occurs for any value of the process count.
For example there is an uncaught exception and exit from the program, with a table size of 1000000 rows:

E := LOAD 'table3' BINARY AS (event_type{1}:varchar(50));

EF := FILTER E BY event_type = "Change1" OR event_type = "Change2" 
    OR event_type = "Change3";

And second example, for the size of 1000000 rows in each of two tables, at attempt to make JOIN I see this error:

D := LOAD 'table1' BINARY AS (f1{1}:varchar(100), d_id{10}:int );
B := LOAD 'table2' BINARY AS (b_id{6}:int);

EDCB := SELECT
         f1 AS f1
     FROM B JOIN D
        ON b_id = d_id;

F:\GPU_test_DWH>AlenkaDB.exe select2.sql
Process count = 6200000
BINARY LOAD: D table1
Reading 990980 records
BINARY LOAD: B table2
Reading 983510 records
JOIN EDCB b_id d_id
Cuda error in file 'C:/VSProject/AlenkaDB/cm.cu' in line 922 : unknown error.

Best regards,
Alexey

Load Append data or Union Data

Anton,
Generally we need append/insert new data to an existing table. For this to work, either we need a append the new data to an existing file when store, or, we can store it to new file and UNION them together when loading.
Do we have such feature in Alenka?
thanks,
George

Alenka crash when executing for q2.sql, q3.sql, q5.sql, and q7.sql

Hi.

I managed to build Alenka on Linux machine, Ubuntu 12.04 64-bit. However the application crash when executing for the above sql. I had pasted what I see on my my console below.

Please advice. I am at my wit end. Thanks.

EDITED:-
I created the table using
$ dbgen -n 10000 -o dbgen.tbl
and then create symbolic link to dbgen.tbl for every unfound .tbl file return by executing
$ ./alenka load_XXXXX.sql

ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q1.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: A lineitem
Reading 380033 records
opening lineitem.sort
FILTER B A 2736513024
MAP CHECK segment 0 A
filter is finished 380033 2734940160
filter time 0 2736513024
SELECT D B 2736513024
segment 0 select mem 2733367296
final select 2
select time 0.06
STORE: D mytest.txt |
SQL scan parse worked
cycle time 0.06
ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q2.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: N nation
Reading 380033 records
opening nation.sort
BINARY LOAD: R region
Reading 380033 records
opening region.sort
FILTER RF R 2736513024
MAP CHECK segment 0 R
filter is finished 0 2734940160
filter time 0.01 2736513024
BINARY LOAD: S supplier
Reading 380033 records
opening supplier.sort
BINARY LOAD: PS partsupp
Reading 380033 records
opening partsupp.sort
BINARY LOAD: P part
Reading 380033 records
opening part.sort
FILTER PF P 2736513024
terminate called after throwing an instance of 'thrust::system::detail::bad_alloc'
what(): std::bad_alloc: unspecified launch failure
Aborted (core dumped)
ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q3.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: O orders
Reading 380033 records
opening orders.sort
FILTER OFI O 2736513024
MAP CHECK segment 0 A
filter is finished 380033 2734940160
filter time 0.01 2736513024
BINARY LOAD: C customer
Reading 0 records
opening customer.sort
filter time 0 2736513024
BINARY LOAD: L lineitem
Reading 380033 records
opening lineitem.sort
FILTER LF L 2736513024
MAP CHECK segment 0 N
filter is finished 0 2734940160
filter time 0.01 2736513024
JOIN OLC1 o_custkey c_custkey 2736513024
Join result OLC : 0
Segmentation fault (core dumped)
ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q5.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: R region
Reading 380033 records
opening region.sort
FILTER RF R 2736513024
MAP CHECK segment 0 R
filter is finished 0 2734940160
filter time 0.01 2736513024
BINARY LOAD: N nation
Reading 380033 records
opening nation.sort
BINARY LOAD: O orders
Reading 380033 records
opening orders.sort
FILTER OFI O 2736513024
MAP CHECK segment 0 N
filter is finished 0 2734940160
filter time 0 2736513024
BINARY LOAD: S supplier
Reading 380033 records
opening supplier.sort
BINARY LOAD: C customer
Reading 0 records
opening customer.sort
BINARY LOAD: L lineitem
Reading 380033 records
opening lineitem.sort
JOIN J11 n_regionkey r_regionkey 2736513024
Join result J1 : 0
Segmentation fault (core dumped)
ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q6.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: A lineitem
Reading 380033 records
opening lineitem.sort
FILTER B A 2736513024
MAP CHECK segment 0 N
filter is finished 0 2734940160
filter time 0 2736513024
SELECT C count : 0, Mem 2736513024
STORE: C mytest.txt |
SQL scan parse worked
cycle time 0.01
ubuntu@ubuntuserv:~/workspace/Alenka-master$ ./alenka q7.sql
Tesla C2050 : 1147.000 Mhz (Ordinal 0)
14 SMs enabled. Compute Capability sm_20
FreeMem: 2610MB TotalMem: 2687MB.
Mem Clock: 1500.000 Mhz x 384 bits (144.000 GB/s)
ECC Enabled

Process count = 6200000
BINARY LOAD: L lineitem
Reading 380033 records
opening lineitem.sort
FILTER LF L 2736513024
MAP CHECK segment 0 N
filter is finished 0 2734940160
filter time 0 2736513024
BINARY LOAD: S supplier
Reading 380033 records
opening supplier.sort
BINARY LOAD: C customer
Reading 0 records
opening customer.sort
BINARY LOAD: N nation
Reading 380033 records
opening nation.sort
FILTER NF N 2736513024
MAP CHECK segment 0 R
filter is finished 0 2734940160
filter time 0.01 2736513024
BINARY LOAD: N1 nation
Reading 380033 records
opening nation.sort
FILTER NF1 N1 2736513024
MAP CHECK segment 0 R
filter is finished 0 2734940160
filter time 0.01 2736513024
BINARY LOAD: O orders
Reading 380033 records
opening orders.sort
JOIN SN s_nationkey n_nationkey1 2736513024
Join result SN : 0
Segmentation fault (core dumped)
ubuntu@ubuntuserv:/workspace/Alenka-master$
ubuntu@ubuntuserv:/workspace/Alenka-master$ uname -a
Linux ubuntuserv 3.5.0-36-generic #57precise1-Ubuntu SMP Thu Jun 20 18:21:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
ubuntu@ubuntuserv:/workspace/Alenka-master$ nvcc --version
nvcc: NVIDIA (R) Cuda compiler driver
Copyright (c) 2005-2012 NVIDIA Corporation
Built on Fri_Sep_21_17:28:58_PDT_2012
Cuda compilation tools, release 5.0, V0.2.1221
ubuntu@ubuntuserv:/workspace/Alenka-master$ gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/4.6/lto-wrapper
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu/Linaro 4.6.4-1ubuntu112.04' --with-bugurl=file:///usr/share/doc/gcc-4.6/README.Bugs --enable-languages=c,c++,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.6 --enable-shared --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.6 --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --enable-gnu-unique-object --enable-plugin --enable-objc-gc --disable-werror --with-arch-32=i686 --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu
Thread model: posix
gcc version 4.6.4 (Ubuntu/Linaro 4.6.4-1ubuntu112.04)
ubuntu@ubuntuserv:/workspace/Alenka-master$

Bad column in 'order by' crashes GPU

Specifying a bad column in the order by
ie:
unnamed_A1 := SELECT shipmode AS shipmode,linestatus AS linestatus,count(orderkey) AS col3,sum(price) AS paid FROM lineitem GROUP BY shipmode,linestatus;
unnamed_A2 := ORDER unnamed_A1 BY paidz;
DISPLAY unnamed_A2 USING ('|');

Results in:
tot disk time start0.06
terminate called after throwing an instance of 'thrust::system::detail::bad_alloc'
what(): std::bad_alloc: unspecified launch failure
Aborted (core dumped)

The GPU crashes and further invocation of any GPU program hangs at initialisation.

• requires a reboot.

cannot open input file 'cudpp64.lib'

nvcc --compiler-options /MD -O3 -arch=sm_20 -lcuda -lcudpp64 -lcudpp_has
h64 -o alenka merge.obj MurmurHash2_64.obj filter.obj strings.obj select.obj z
one_map.obj itoa.obj atof.obj cm.obj bison.obj
link.stub
LINK : fatal error LNK1181: cannot open input file 'cudpp64.lib'

i just download the cudpp and put it into the directory of the project alenka

hi

hi anton, i'm a freshman studying in gpu, and recently i'm looking into your project, it's impressed me a lot, but it makes me hard to understand your project because lack of documents except manual file. if you have some detail documents，please send me,thank you.

and also what kind of IDE you use when you coding this project?

does this project support large scale like 1G or 10G?

my email: [email protected]