 |
Column-oriented GPU-accelerated Database Management System
CoGaDB
|
|
Column-oriented GPU-accelerated Database Management System
CoGaDB
|
In this section, we provide a short getting started guide for using CoGaDB. Furthermore, we list the available commands of CoGaDB's command line interface. Finally, we present a short demo of the SQL Interface to show its current capabilities.
At first, we have to create a directory, where CoGaDB can store its database:
set path_to_database=/home/DATA/coga_databases/ssb_sf1
Then, we have to create a database and import data. This can be done in two ways: using the sql interface (create table, insert into), or using a utility command. CoGaDB supports utility commands for importing databases from two common OLAP benchmarks: the TPC-H and the Star Schema Benchmark. Note that you have to generate the *.tbl files using the dbgen tool. Assuming we have generated a database for the star schema benchmark of scale factor one and stored the resulting *.tbl files in /home/DATA/benchmarks/star_schema_benchmark/SF1/, we can import the data with the following command:
create_ssb_database /home/DATA/benchmarks/star_schema_benchmark/SF1/
For the TPC-H benchmark, the command is create_tpch_database.
Now CoGaDB imports the data and stores them in the database. Depending on the scale factor, this can take a while. After the import finishes, we can start working with the database. Since CoGaDB is an in-memory database, we first have to load the database in the main memory:
loaddatabase
Then, we can start issuing queries. We can either use SQL or build in aliases for stored queries. We provide stored queries for all queries of the star schema benchmark. The template command is ssbXY, which executes SSB-Query X.Y (X has to be a number between 1 and 4; Y has to be a number between 1 and 3 except when X is 3, in this case 4 is valid for Y as well).
Sometimes, when no NVIDIA GPU is available, we need to restrict CoGaDB to use only the CPU. We can configure this by issuing the following command:
setdevice cpu
If we want to allow CoGaDB to use both processing devices, we can replace cpu with any. It is also possible to force the usage of the GPU for all processing tasks by specifying gpu. However, this is NOT recommended, because for most complex queries, CoGaDB will not be able to perform all processing tasks on GPU only.
CoGaDB offers a set of commands not included in SQL to ease development and debugging:
| Command | Description |
|---|---|
| loaddatabase | loads complete database in main memory |
| unittests | performs a self check of CoGaDB |
| printschema | prints the schema of the active database |
| showgpucache | prints status information of the GPU column cache |
| simple_ssb_queries | simple demonstrator for queries on SSB Benchmark data set |
| set <variablename>=<variablevalue> | assign the value <variablevalue> to the variable <variablename> |
| print <variable> | print value of variable |
| create_tpch_database <path to *.tbl files> | import tables of TPC-H benchmark in CoGaDB |
| create_ssb_database <path to *.tbl files> | import tables of star schema benchmark in CoGaDB |
| exec <SQL statement>=""> | Execute SQL statements |
| toggleQC | Toggle the state of Query Chopping activation. Per default QC is off. |
| ssbXY | Execute SSB-Query X.Y (X has to be a number between 1 and 4; Y has to be a number between 1 and 3 except when X is 3, in this case 4 is valid for Y as well) |
| setdevice <DEVICE> | Sets the default device, which is used for execution. Possible values are 'cpu', 'gpu' or 'any' to use either the CPU or the GPU or both concurrently. |
| setparallelizationmode <PARALLELIZATION mode>=""> | Sets the default parallelization mode for sub-plans generated during Two Phase Physical Optimization (TOPPO) in the second phase (currently only for complex selections). Valid values are 'serial' and 'parallel' |
| quit | exits CoGaDB |
CoGaDB has the following build in variables:
| Variable | Description |
|---|---|
| path_to_database | absolute or relative path to directory where the database is stored |
CoGaDB supports a subset of the SQL-92 standard. We provide a short demo in the following to show the current capabilities of the SQL Interface. Note that we shortened the output of the following listings to the relevant information: query, result and execution time.
Lets first create a table:
CoGaDB>exec create table Test ( id int, val varchar); TEST: | ID | VAL | ------------------------------- 0 rows Execution Time: 0.27820 ms
Now we can insert data:
CoGaDB>exec insert into Test values (0,'Car'); TEST: | ID | VAL | ------------------------------- | 0 | Car | 1 rows Execution Time: 0.21268 ms CoGaDB>exec insert into Test values (1,'Truck'); TEST: | ID | VAL | ------------------------------- | 0 | Car | | 1 | Truck | 2 rows Execution Time: 0.27443 ms CoGaDB>exec insert into Test values (2,'Boat'); TEST: | ID | VAL | ------------------------------- | 0 | Car | | 1 | Truck | | 2 | Boat | 3 rows Execution Time: 0.14750 ms CoGaDB>exec insert into Test values (3,'Street'); TEST: | ID | VAL | ------------------------------- | 0 | Car | | 1 | Truck | | 2 | Boat | | 3 | Street | 4 rows Execution Time: 0.16489 ms
Finally, we can query our table:
CoGaDB>select * from Test; TEST: | ID | VAL | ------------------------------- | 0 | Car | | 1 | Truck | | 1 | Boat | | 3 | Street | 4 rows Execution Time: 0.85918 ms CoGaDB>exec select id from Test where val='Boat'; printing Lookup Table: projection( Lookup_Table_selection(TEST ) ) LookupTable 'projection( Lookup_Table_selection(TEST ) )': Consist of 1 Tables: TEST | ID | ------------------------------- | 2 | 1 rows Execution Time: 1.11014 ms
Now we show a more complex query typical for OLAP workloads. We execute query 2.1 from the Star Schema Benchmark:
CoGaDB>exec select sum(lo_revenue), d_year, p_brand from lineorder, dates, part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand order by d_year, p_brand; groupby(sort( sort( concat( AGGREGATED( concat( AGGREGATED( concat( AGGREGATED( LINEORDER ),AGGREGATED( DATES ) ) ),AGGREGATED( Lookup_Table_selection(PART ) ) ) ),AGGREGATED( Lookup_Table_selection(SUPPLIER ) ) ) ) )): | D_YEAR | P_BRAND | LO_REVENUE | ------------------------------- | 1992 | MFGR#121 | 667692864.000000 | | 1992 | MFGR#1210 | 568029888.000000 | | 1992 | MFGR#1211 | 552505536.000000 | | 1992 | MFGR#1212 | 683486784.000000 | | 1992 | MFGR#1213 | 615323840.000000 | | 1992 | MFGR#1214 | 616843072.000000 | | 1992 | MFGR#1215 | 648939264.000000 | | 1992 | MFGR#1216 | 643585600.000000 | | 1992 | MFGR#1217 | 835229632.000000 | | 1992 | MFGR#1218 | 606804160.000000 | | 1992 | MFGR#1219 | 563125312.000000 | | 1992 | MFGR#122 | 726185728.000000 | | 1992 | MFGR#1220 | 532641280.000000 | | 1992 | MFGR#1221 | 580700032.000000 | | 1992 | MFGR#1222 | 708154432.000000 | | 1992 | MFGR#1223 | 550780864.000000 | | 1992 | MFGR#1224 | 666115840.000000 | | 1992 | MFGR#1225 | 563126400.000000 | | 1992 | MFGR#1226 | 635218688.000000 | | 1992 | MFGR#1227 | 572245376.000000 | | 1992 | MFGR#1228 | 550885824.000000 | | 1992 | MFGR#1229 | 587150016.000000 | | 1992 | MFGR#123 | 755098496.000000 | | 1992 | MFGR#1230 | 570770304.000000 | | 1992 | MFGR#1231 | 505686272.000000 | | 1992 | MFGR#1232 | 659162112.000000 | | 1992 | MFGR#1233 | 683613376.000000 | | 1992 | MFGR#1234 | 596447104.000000 | | 1992 | MFGR#1235 | 584148416.000000 | | 1992 | MFGR#1236 | 608850240.000000 | | 1992 | MFGR#1237 | 528499264.000000 | | 1992 | MFGR#1238 | 562479232.000000 | | 1992 | MFGR#1239 | 577189952.000000 | | 1992 | MFGR#124 | 649797184.000000 | | 1992 | MFGR#1240 | 746865280.000000 | | 1992 | MFGR#125 | 645919104.000000 | | 1992 | MFGR#126 | 584842496.000000 | | 1992 | MFGR#127 | 597628800.000000 | | 1992 | MFGR#128 | 605195072.000000 | | 1992 | MFGR#129 | 614833024.000000 | | 1993 | MFGR#121 | 665506048.000000 | | 1993 | MFGR#1210 | 665441408.000000 | | 1993 | MFGR#1211 | 574899776.000000 | | 1993 | MFGR#1212 | 622842688.000000 | | 1993 | MFGR#1213 | 537628480.000000 | | 1993 | MFGR#1214 | 599670464.000000 | | 1993 | MFGR#1215 | 620609024.000000 | | 1993 | MFGR#1216 | 582264448.000000 | | 1993 | MFGR#1217 | 676795200.000000 | | 1993 | MFGR#1218 | 589771328.000000 | | 1993 | MFGR#1219 | 585700032.000000 | | 1993 | MFGR#122 | 777985856.000000 | | 1993 | MFGR#1220 | 466499840.000000 | | 1993 | MFGR#1221 | 521363104.000000 | | 1993 | MFGR#1222 | 659926656.000000 | | 1993 | MFGR#1223 | 594230976.000000 | | 1993 | MFGR#1224 | 793732096.000000 | | 1993 | MFGR#1225 | 549938432.000000 | | 1993 | MFGR#1226 | 642279680.000000 | | 1993 | MFGR#1227 | 602400640.000000 | | 1993 | MFGR#1228 | 719167744.000000 | | 1993 | MFGR#1229 | 592683968.000000 | | 1993 | MFGR#123 | 669865856.000000 | | 1993 | MFGR#1230 | 611418880.000000 | | 1993 | MFGR#1231 | 529316096.000000 | | 1993 | MFGR#1232 | 690396992.000000 | | 1993 | MFGR#1233 | 594584960.000000 | | 1993 | MFGR#1234 | 721612864.000000 | | 1993 | MFGR#1235 | 638832704.000000 | | 1993 | MFGR#1236 | 506928448.000000 | | 1993 | MFGR#1237 | 557937984.000000 | | 1993 | MFGR#1238 | 489449408.000000 | | 1993 | MFGR#1239 | 626674944.000000 | | 1993 | MFGR#124 | 622534656.000000 | | 1993 | MFGR#1240 | 730419904.000000 | | 1993 | MFGR#125 | 610593024.000000 | | 1993 | MFGR#126 | 646904576.000000 | | 1993 | MFGR#127 | 665004928.000000 | | 1993 | MFGR#128 | 745828992.000000 | | 1993 | MFGR#129 | 562984064.000000 | | 1994 | MFGR#121 | 656155520.000000 | | 1994 | MFGR#1210 | 685700736.000000 | | 1994 | MFGR#1211 | 630536064.000000 | | 1994 | MFGR#1212 | 645472704.000000 | | 1994 | MFGR#1213 | 496881376.000000 | | 1994 | MFGR#1214 | 612419520.000000 | | 1994 | MFGR#1215 | 613483840.000000 | | 1994 | MFGR#1216 | 617308480.000000 | | 1994 | MFGR#1217 | 667483904.000000 | | 1994 | MFGR#1218 | 552667776.000000 | | 1994 | MFGR#1219 | 538198912.000000 | | 1994 | MFGR#122 | 615424832.000000 | | 1994 | MFGR#1220 | 511932736.000000 | | 1994 | MFGR#1221 | 547157760.000000 | | 1994 | MFGR#1222 | 550635712.000000 | | 1994 | MFGR#1223 | 501827168.000000 | | 1994 | MFGR#1224 | 723810368.000000 | | 1994 | MFGR#1225 | 501363200.000000 | | 1994 | MFGR#1226 | 568169472.000000 | | 1994 | MFGR#1227 | 553443840.000000 | | 1994 | MFGR#1228 | 475284224.000000 | | 1994 | MFGR#1229 | 641371264.000000 | | 1994 | MFGR#123 | 586263040.000000 | | 1994 | MFGR#1230 | 563001856.000000 | | 1994 | MFGR#1231 | 558960896.000000 | | 1994 | MFGR#1232 | 609120320.000000 | | 1994 | MFGR#1233 | 639189440.000000 | | 1994 | MFGR#1234 | 635021120.000000 | | 1994 | MFGR#1235 | 611832640.000000 | | 1994 | MFGR#1236 | 642980608.000000 | | 1994 | MFGR#1237 | 605569984.000000 | | 1994 | MFGR#1238 | 520323264.000000 | | 1994 | MFGR#1239 | 604578176.000000 | | 1994 | MFGR#124 | 614092352.000000 | | 1994 | MFGR#1240 | 826205376.000000 | | 1994 | MFGR#125 | 561077120.000000 | | 1994 | MFGR#126 | 641538880.000000 | | 1994 | MFGR#127 | 765116352.000000 | | 1994 | MFGR#128 | 589369344.000000 | | 1994 | MFGR#129 | 535648736.000000 | | 1995 | MFGR#121 | 564932672.000000 | | 1995 | MFGR#1210 | 514656608.000000 | | 1995 | MFGR#1211 | 602015232.000000 | | 1995 | MFGR#1212 | 603074304.000000 | | 1995 | MFGR#1213 | 536000608.000000 | | 1995 | MFGR#1214 | 562296576.000000 | | 1995 | MFGR#1215 | 560291520.000000 | | 1995 | MFGR#1216 | 733201536.000000 | | 1995 | MFGR#1217 | 766560320.000000 | | 1995 | MFGR#1218 | 642708032.000000 | | 1995 | MFGR#1219 | 526933600.000000 | | 1995 | MFGR#122 | 536402560.000000 | | 1995 | MFGR#1220 | 585930560.000000 | | 1995 | MFGR#1221 | 530120032.000000 | | 1995 | MFGR#1222 | 666863360.000000 | | 1995 | MFGR#1223 | 641169408.000000 | | 1995 | MFGR#1224 | 703416896.000000 | | 1995 | MFGR#1225 | 555536256.000000 | | 1995 | MFGR#1226 | 664045760.000000 | | 1995 | MFGR#1227 | 581628928.000000 | | 1995 | MFGR#1228 | 687814208.000000 | | 1995 | MFGR#1229 | 683773888.000000 | | 1995 | MFGR#123 | 665712000.000000 | | 1995 | MFGR#1230 | 579595456.000000 | | 1995 | MFGR#1231 | 580677312.000000 | | 1995 | MFGR#1232 | 754888320.000000 | | 1995 | MFGR#1233 | 529519200.000000 | | 1995 | MFGR#1234 | 651091008.000000 | | 1995 | MFGR#1235 | 666334336.000000 | | 1995 | MFGR#1236 | 647244672.000000 | | 1995 | MFGR#1237 | 589898944.000000 | | 1995 | MFGR#1238 | 513659008.000000 | | 1995 | MFGR#1239 | 630727744.000000 | | 1995 | MFGR#124 | 549471168.000000 | | 1995 | MFGR#1240 | 779785152.000000 | | 1995 | MFGR#125 | 410181248.000000 | | 1995 | MFGR#126 | 540122112.000000 | | 1995 | MFGR#127 | 673853632.000000 | | 1995 | MFGR#128 | 601863680.000000 | | 1995 | MFGR#129 | 536371200.000000 | | 1996 | MFGR#121 | 647097920.000000 | | 1996 | MFGR#1210 | 595542848.000000 | | 1996 | MFGR#1211 | 731875584.000000 | | 1996 | MFGR#1212 | 606629632.000000 | | 1996 | MFGR#1213 | 622169472.000000 | | 1996 | MFGR#1214 | 540875200.000000 | | 1996 | MFGR#1215 | 634117760.000000 | | 1996 | MFGR#1216 | 660828416.000000 | | 1996 | MFGR#1217 | 685095680.000000 | | 1996 | MFGR#1218 | 518528192.000000 | | 1996 | MFGR#1219 | 555762624.000000 | | 1996 | MFGR#122 | 627830272.000000 | | 1996 | MFGR#1220 | 606140480.000000 | | 1996 | MFGR#1221 | 585609984.000000 | | 1996 | MFGR#1222 | 504108128.000000 | | 1996 | MFGR#1223 | 618574336.000000 | | 1996 | MFGR#1224 | 781443840.000000 | | 1996 | MFGR#1225 | 631089984.000000 | | 1996 | MFGR#1226 | 726889472.000000 | | 1996 | MFGR#1227 | 518373568.000000 | | 1996 | MFGR#1228 | 603695936.000000 | | 1996 | MFGR#1229 | 615060416.000000 | | 1996 | MFGR#123 | 587953408.000000 | | 1996 | MFGR#1230 | 573470016.000000 | | 1996 | MFGR#1231 | 562430208.000000 | | 1996 | MFGR#1232 | 714332480.000000 | | 1996 | MFGR#1233 | 538853632.000000 | | 1996 | MFGR#1234 | 724594560.000000 | | 1996 | MFGR#1235 | 623234624.000000 | | 1996 | MFGR#1236 | 630860096.000000 | | 1996 | MFGR#1237 | 532629440.000000 | | 1996 | MFGR#1238 | 487116224.000000 | | 1996 | MFGR#1239 | 583815680.000000 | | 1996 | MFGR#124 | 658011648.000000 | | 1996 | MFGR#1240 | 835392960.000000 | | 1996 | MFGR#125 | 535273024.000000 | | 1996 | MFGR#126 | 583509568.000000 | | 1996 | MFGR#127 | 546939456.000000 | | 1996 | MFGR#128 | 564236928.000000 | | 1996 | MFGR#129 | 569420864.000000 | | 1997 | MFGR#121 | 631830528.000000 | | 1997 | MFGR#1210 | 668802624.000000 | | 1997 | MFGR#1211 | 590431680.000000 | | 1997 | MFGR#1212 | 657789760.000000 | | 1997 | MFGR#1213 | 607511872.000000 | | 1997 | MFGR#1214 | 546360192.000000 | | 1997 | MFGR#1215 | 654248384.000000 | | 1997 | MFGR#1216 | 643872768.000000 | | 1997 | MFGR#1217 | 655017408.000000 | | 1997 | MFGR#1218 | 613229760.000000 | | 1997 | MFGR#1219 | 563752384.000000 | | 1997 | MFGR#122 | 629108544.000000 | | 1997 | MFGR#1220 | 565845504.000000 | | 1997 | MFGR#1221 | 619004160.000000 | | 1997 | MFGR#1222 | 645620608.000000 | | 1997 | MFGR#1223 | 493459648.000000 | | 1997 | MFGR#1224 | 721915456.000000 | | 1997 | MFGR#1225 | 588092032.000000 | | 1997 | MFGR#1226 | 660816128.000000 | | 1997 | MFGR#1227 | 593503488.000000 | | 1997 | MFGR#1228 | 568070464.000000 | | 1997 | MFGR#1229 | 603174656.000000 | | 1997 | MFGR#123 | 548241920.000000 | | 1997 | MFGR#1230 | 630192384.000000 | | 1997 | MFGR#1231 | 608082944.000000 | | 1997 | MFGR#1232 | 743199360.000000 | | 1997 | MFGR#1233 | 630175680.000000 | | 1997 | MFGR#1234 | 725502016.000000 | | 1997 | MFGR#1235 | 573174464.000000 | | 1997 | MFGR#1236 | 559478528.000000 | | 1997 | MFGR#1237 | 712715840.000000 | | 1997 | MFGR#1238 | 482196768.000000 | | 1997 | MFGR#1239 | 688093376.000000 | | 1997 | MFGR#124 | 530165792.000000 | | 1997 | MFGR#1240 | 641815936.000000 | | 1997 | MFGR#125 | 534222816.000000 | | 1997 | MFGR#126 | 638908672.000000 | | 1997 | MFGR#127 | 618720128.000000 | | 1997 | MFGR#128 | 571249280.000000 | | 1997 | MFGR#129 | 584183936.000000 | | 1998 | MFGR#121 | 381464640.000000 | | 1998 | MFGR#1210 | 335711264.000000 | | 1998 | MFGR#1211 | 381413504.000000 | | 1998 | MFGR#1212 | 416853216.000000 | | 1998 | MFGR#1213 | 274528544.000000 | | 1998 | MFGR#1214 | 321318624.000000 | | 1998 | MFGR#1215 | 358404448.000000 | | 1998 | MFGR#1216 | 393639104.000000 | | 1998 | MFGR#1217 | 363930912.000000 | | 1998 | MFGR#1218 | 364725792.000000 | | 1998 | MFGR#1219 | 364337984.000000 | | 1998 | MFGR#122 | 392272992.000000 | | 1998 | MFGR#1220 | 316099488.000000 | | 1998 | MFGR#1221 | 318731680.000000 | | 1998 | MFGR#1222 | 326961312.000000 | | 1998 | MFGR#1223 | 368606624.000000 | | 1998 | MFGR#1224 | 395545792.000000 | | 1998 | MFGR#1225 | 385398528.000000 | | 1998 | MFGR#1226 | 409641280.000000 | | 1998 | MFGR#1227 | 303716000.000000 | | 1998 | MFGR#1228 | 332407776.000000 | | 1998 | MFGR#1229 | 368823104.000000 | | 1998 | MFGR#123 | 410870944.000000 | | 1998 | MFGR#1230 | 353565312.000000 | | 1998 | MFGR#1231 | 346221184.000000 | | 1998 | MFGR#1232 | 372917312.000000 | | 1998 | MFGR#1233 | 332307264.000000 | | 1998 | MFGR#1234 | 444282624.000000 | | 1998 | MFGR#1235 | 361972416.000000 | | 1998 | MFGR#1236 | 320125344.000000 | | 1998 | MFGR#1237 | 309277088.000000 | | 1998 | MFGR#1238 | 252431488.000000 | | 1998 | MFGR#1239 | 400175072.000000 | | 1998 | MFGR#124 | 309643584.000000 | | 1998 | MFGR#1240 | 344323552.000000 | | 1998 | MFGR#125 | 302823584.000000 | | 1998 | MFGR#126 | 272220288.000000 | | 1998 | MFGR#127 | 358767872.000000 | | 1998 | MFGR#128 | 338572960.000000 | | 1998 | MFGR#129 | 319373824.000000 | 280 rows Execution Time: 5336.66898 ms
Note that this execution is not representative for the performance of CoGaDB.
1.7.6.1