Transcript Accelerating SQL Database Operations on a GPU

Accelerating SQL Database
Operations on a GPU with CUDA
Peter Bakkum & Kevin Skadron
The University of Virginia
GPGPU-3 Presentation
March 14, 2010
Overview
• Implementation of certain SQL SELECT queries to
run on an NVIDIA GPU with CUDA
• Built on top of the SQLite database
• Average query speedup of 35X
• Two goals:
– Accelerate existing database operations
– Give programmers easier access to GPU hardware
2
Outline
• SQL Review
• SQLite
• Implementation
– Scope
– Limitations
•
•
•
•
•
Testing, Results
Multicore
Hardware Limitations
Future Work
Conclusions
3
SQL Review
• Widely-used database query language
• Declarative, thus easy to parallelize
• Data mining operations have been accelerated on
GPUs
SELECT name FROM students
WHERE gpa > 3 AND age < 25
4
SQLite
•
•
•
•
•
Most widely installed SQL database
Simple design
Easy to understand code
Code placed in the public domain
Performance comparable to other open-source
databases
5
SQLite Architecture
• Runs as part of the client process
• SQL queries are parsed and transformed to a
program in an intermediate language of opcodes
– Process resembles compilation, output resembles
assembly
• Intermediate language program, or query plan,
executed by the SQLite virtual machine
6
Implementation
• A re-implementation of the virtual machine as a
CUDA kernel
• Uses SQLite query processor
• Many query plans execute opcodes over all rows
in a table, inherently parallelizable
• Data selected from SQLite and transferred to
GPU in initialization step
• Queries execute in a kernel launch
• Results transferred from the GPU
7
Implementation
• Each thread assigned a table row
• Threads execute opcodes
• Thread divergence occurs as threads jump to
different opcodes based on the data
• Reductions must be performed to coordinate
output of results and aggregations of values
8
Example Opcode Program
1: Integer
2: Integer
…
6: Column
7: Lt
8: Column
9: Gt
10: Column
11: ResultRow
12: Next
3 1 0
25 2 0
Alice
23
3.5
0
1
0
2
0
5
0
1
12
2
12
0
1
6
3
3
3
3
5
0
0
1: Integer
2: Integer
…
6: Column
7: Lt
8: Column
9: Gt
10: Column
11: ResultRow
12: Next
Bob
2.5
3 1 0
25 2 0
0
1
0
2
0
5
0
1
12
2
12
0
1
6
3
3
3
3
5
0
0
26
9
GPU Limitations
• GPU memory size
– 4 GB on Tesla C1060 GPUs
• Data must be transferred to GPU
• Results must be transferred back
• CUDA makes certain optimizations more difficult
10
Scope
• Subset of possible SELECT queries
• Numerical data types
– 32, 64 bit ints, 32, 64 bit IEEE 754 floats
• Mid-size data sets
• Applications that run multiple queries over the
same data
– Ignore the data transfer time to the GPU
11
Testing
• 13 queries benchmarked on CPU and GPU
• Random data set of 5 million rows, varying
distribution, data type
• Running times include the query execution time and
the results transfer time
• Tesla C1060 GPU, CUDA 2.2
– 4 GB memory, 240 streaming multiprocessors, 102 GB/s
bandwidth
• Intel Xeon X5550 with Linux 2.6.24
• CPU code compiled and optimized with the Intel C
Compiler 11.1
12
Running Time Comparison
4
Running Time (seconds)
3.5
3
2.5
2
SQLite
1.5
GPU
1
0.5
0
Int
Float
Aggregation
Query Type
Average
13
Speedup Comparison
50
45
40
Speedup (x)
35
30
25
Speedup
20
15
10
5
0
Int
Float
Aggregation
Query Type
Average
14
Results
• The average query took 2.27 seconds for CPU
execution, .063 seconds for GPU execution
– 36X speedup
• Speedup varied by query, range of 20-70X
• The size of the result set significantly affected
relative query speed
– Synchronization among threads
– Time to transfer results back from the GPU
15
Multicore
• SQLite does not take advantage of multiple cores,
the results shown are for a single core
• The maximum possible speedup is n times on an
n-core machine, less because of overhead
• The GPU is faster because of the number of SMs,
memory throughput, and very efficient thread
synchronization
16
Hardware Limitations
• No support for indirect jumps
• Dynamically accessed arrays are stored in local
(global memory latency) memory
• Certain 32 and 64 bit math operations are
emulated on current hardware
• These limitations are expected to be removed in
Fermi, the next generation of NVIDIA hardware
17
Future Work
• This is preliminary work, there are a number of
topics available for future research:
– More complete implementation of SQL, including
JOINs, INSERTs, indices etc.
– Multi-GPU, Distributed GPU implementation
– Direct comparison to multi-core
– Direct comparison to other databases
– GPU-targeted SQL query processor
18
Conclusions
• SQL is a good programming paradigm for
accessing GPU hardware
• Many database operations can be significantly
accelerated on GPUs
• Next-generation GPU hardware will likely
improve these results
• This is an open area for future research
19
Questions
20
SQL Queries Tested
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT id, uniformi, normali5 FROM test WHERE uniformi > 60 AND normali5 < 0
SELECT id, uniformf, normalf5 FROM test WHERE uniformf > 60 AND normalf5 < 0
SELECT id, uniformi, normali5 FROM test WHERE uniformi > -60 AND normali5 < 5
SELECT id, uniformf, normalf5 FROM test WHERE uni-formf > -60 AND normalf5 < 5
SELECT id, normali5, normali20 FROM test WHERE (normali20 + 40) > (uniformi - 10)
SELECT id, normalf5, normalf20 FROM test WHERE (normalf20 + 40) > (uniformf - 10)
SELECT id, normali5, normali20 FROM test WHERE normali5 * normali20 BETWEEN -5
AND 5
SELECT id, normalf5, normalf20 FROM test WHERE normalf5 * normalf20 BETWEEN -5
AND 5
SELECT id, uniformi, normali5, normali20 FROM test WHERE NOT uniformi OR NOT
normali5 OR NOT normali20
SELECT id, uniformf, normalf5, normalf20 FROM test WHERE NOT uniformf OR NOT
normalf5 OR NOT normalf20
SELECT SUM(normalf20) FROM test
SELECT AVG(uniformi) FROM test WHERE uniformi > 0
SELECT MAX(normali5), MIN(normali5) FROM test
21
Rows Returned vs. Speedup
22