Introduction
In the OpenShift 3.9 GPU blog, we leveraged machine learning frameworks on OpenShift for image recognition. And in the How To Use GPUs with DevicePlugin in OpenShift 3.10 blog, we installed and configured an OpenShift cluster with GPU support. In this installment, we will create a more sophisticated workload on the cluster - accelerating databases using GPUs.
One of the key parts of any machine learning algorithm is the data (often referred to as the data lake/warehouse, stored as structured, semi-structured or unstructured data).
A major part of machine learning pipelines is the preparation, cleaning, and exploration of this data. Specifically removing NAs (missing values), transformations, normalization, subsetting, sorting, and a lot of plotting.
This blog will focus on the data preparation phase, showing how we can use a GPU to accelerate queries in PostgreSQL (the data warehouse). It then uses R for plotting the data.
Environment Overview
- Red Hat Enterprise Linux 7.5, CentOS PostgreSQL 10 image
- OpenShift Container Platform 3.10 Cluster running on AWS
- Container Runtime: crio-1.10.5
- Container Tools: podman-0.6.1, buildah-1.1, skopeo-0.1.30
- Master node: m4.xlarge
- Infra node: m4.xlarge
- Compute node: p3.2xlarge (One NVIDIA Tesla V100 GPU, 8vCPUs and 61GB RAM)
The yaml and configuration files used for this blog can be found at https://github.com/redhat-performance/openshift-psap/tree/master/blog/gpu/pg-strom
We have created a git repository that includes artifacts to be used throughout the blog. We will refer to them as pg-strom/<file>
from now on.
# git clone https://github.com/redhat-performance/openshift-psap
We will use centos/postgresql-10-centos7
as a base image and enable GPU accelerated queries with PG-Strom. PG-Strom is an extension module of PostgreSQL that enables accelerated SQL workloads for data analytics or batch processing.
Building a custom image with buildah
The building of the new image will be done with buildah, which facilitates building OCI container images and is an alternative to “docker build”. Skopeo is a tool that facilitates inspection, pulling and pushing container images to a registry. Install buildah and skopeo now.
# yum -y install buildah skopeo
Buildah uses simple bash scripts for building and sample demos can be accessed here: Buildah Demos. The buildah script used in this blog can be found at pg-strom/buildah-pg.sh
. The script mounts a container, installs required software and commits the container for later use. Simply invoking the script will generate a GPU-enabled PostgreSQL container.
# <dir>/pg-strom/buildah-pg.sh
Let’s check if we have a complete image committed locally:
# podman images | grep pgstrom
localhost/pgstrom latest 7ca1854e4176 5 days ago 517MB
The container can optionally be pushed to a registry. Skopeo can inspect the image without pulling it from a registry:
# skopeo inspect <registry>/<repo>/pgstrom{
"Name": "<registry>/<repo>/pgstrom",
"Tag": "latest",
"Digest": "sha256:b71e9bca91e23a21e7579c01d38522b76dc05f83c59",
"RepoTags": [
"latest"
],
"Created": "2018-07-13T10:26:49.604378011Z",
...
Prepare the PostgreSQL server
Another new feature in OpenShift 3.10 is support for HugePages. HugePages are a memory access performance optimization technique commonly used with databases (like PostgreSQL, C and java applications. We will use HugePages for the database memory. Let’s install some tools for easier management and create the needed mounts.
# yum -y install libhugetlbfs-utils
# hugeadm --create-global-mounts
Lets allocate a fair amount of hugepages on our nodes and check the nodes if the changes
were accepted.
For this we will use tuned, which is a daemon for monitoring and adaptive tuning of system devices. We can create a profile that is in its simplest form a configuration file with several sections to tune system controls and parameters. Besides that one can supply bash scripts to implement more complicated tunings to a system.
The following configuration file pg-strom/pgstrom.conf
can be placed in /etc/tuned/pgstrom
to be recognizable as a profile to tuned. Tuned has the concept of profile inheritance. Here we set “include=openshift-node” as the parent profile for our custom pgstrom (child) profile.
# tuned
[main]
summary=Configuration for pgstrom
include=openshift-node
[vm]
transparent_hugepages=never
[sysctl]
vm.nr_hugepages=16384
Now load the new tuned profile based on the openshift-node profile. Per default each OpenShift node (compute, control-plane, … ) has its own tuning profile based on the responsibilities it has.
# tuned-adm profile pgstrom
Check the node for HugePages.
# oc describe node | grep Capacity -A13
Capacity:
cpu: 8
hugepages-1Gi: 0
hugepages-2Mi: 32Gi
memory: 62710868Ki
nvidia.com/gpu: 1
pods: 250
Allocatable:
cpu: 8
hugepages-1Gi: 0
hugepages-2Mi: 32Gi
memory: 29054036Ki
nvidia.com/gpu: 1
pods: 250
Persistent Volume with Local Storage
We want our data to be saved persistent (as opposed to stored ephemerally, in the container, which would not persist). For this we will use a new feature in OpenShift 3.10 called Local Volumes. The essential part here is the new local-storage-provisioner, which monitors a specific path on the host and creates a PV (persistent volume) for each mount point found in this path.
These PVs can be grouped into named group PVs like SSD or HDD and can be claimed in a namespace. This claim can then be used by a pod as a volume.
How to setup a PV with local volume can be read here: Configuring Local Volumes and how a pod can consume this PV with a PersistentVolumeClaim can be read here: Using Persistent Volume.
For this blog I have created a HDD PV group with a single disk that holds the PostgreSQL data.
# ls /mnt/local-storage/hdd/
disk1
OpenShift will create a PV for disk1, we will claim this PV and mount it into the pod. Have a look at the pg-strom/pgstrom.yml
to see how to mount a claim.
After setup of the data store, we can deploy the pgstrom pod with the following pg-strom/pgstrom.yml
. Start the pod and check the logs, the container will initializes the PostgreSQL data store (initdb).
# oc create -f pgstrom.yml
# oc logs pgstrom
---------------------------- snip ---------------------------------------
Starting server...
UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
UTC [1] LOG: listening on IPv6 address "::", port 5432
UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
UTC [1] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
UTC [1] LOG: redirecting log output to logging collector process
UTC [1] HINT: Future log output will appear in directory "log".
To enable the extension we have to alter the postgresql.conf
(which is stored in the PV that was mounted in to the pod) and add some tweaks for GPU acceleration. Edit /var/lib/pgsql/data/userdata/postgresql.conf
inside the pod and set:
# oc rsh pgstrom /bin/bash
$ vi /var/lib/pgsql/data/userdata/postgresql.conf
------------------------- snip -----------------------------------------
## postgresql.conf
huge_pages = on
# Initial buffers and mem too small, increase it to work in mem
# and not in storage
shared_buffers = 30GB
work_mem = 30GB
# PG-Strom internally uses several background workers,
# Default of 8 is too small, increase it
max_worker_processes = 100
max_parallel_workers = 100
# PG-Strom module must be loaded on startup
shared_preload_libraries = '/usr/pgsql-10/lib/pg_strom.so,pg_prewarm'
------------------------- snip -----------------------------------------
After editing the configuration file, restart the pod to enable the new feature.
# oc replace --force -f pgstrom.yml
Inside the pod we can check if the server is listening
# oc exec pgstrom pg_isready
/var/run/postgresql:5432 - accepting connections
The buildah script has also installed postgresql-odbc
that is used to connect from our workstation to the database server. But first let’s create the test database on which we will do some initial test queries.
# oc exec -it pgstrom /bin/bash
$ cd /var/lib/pgsql/pg-strom/test
$ make init_regression_testdb
# Grab some coffee, this takes some time
Accessing the DB Server from R
For the next part we will use R and the RStudio to create a notebook similar to a Jupyter notebook to access the database and plot some numbers from our queries.
Install R, RStudio and the R package RPostgreSQL according to your distribution on your workstation.
I have added a simple notebook pg-strom/pgstrom.Rmd
for reference. Here is a running notebook in RStudio running SQL queries against the PostgreSQL server.
The first step is to connect to the database via the db driver package.
require(RPostgreSQL)drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
user="postgres",
password="postgres",
host=".com",
# This is the database we created in the step before
dbname="contrib_regression_pg_strom")
Now we can issue SQL queries against the database. As a first step lets enable PG-Strom extension.
dbGetQuery(con, "SET pg_strom.enabled=on")
We can now run accelerated queries against the database, let’s start with a simple NATURAL JOIN
over two tables and let the database EXPLAIN
and ANALYZE
what it is doing in this specific query.
dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 GROUP BY CAT")
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=18097.45..18130.73 rows=1024 width=24) (actual time=299.065..300.648 rows=1025 loops=1)
Group Key: t0.cat
-> Sort (cost=18097.45..18100.01 rows=1024 width=48) (actual time=299.053..299.480 rows=1025 loops=1)
Sort Key: t0.cat
Sort Method: quicksort Memory: 193kB
-> Custom Scan (GpuPreAgg) (cost=18025.77..18046.25 rows=1024 width=48) (actual time=295.002..295.546 rows=1025 loops=1)
Reduction: Local
Combined GpuJoin: enabled
-> Custom Scan (GpuJoin) on t0 (cost=19089.39..26894.61 rows=995000 width=16) (never executed)
Outer Scan: t0 (cost=0.00..20310.00 rows=1000000 width=12) (actual time=62.944..106.492 rows=1000000 loops=1)
Depth 1: GpuHashJoin (plan nrows: 1000000...995000, actual nrows: 1000000...994991)
HashKeys: t0.aid
JoinQuals: (t0.aid = t1.aid)
KDS-Hash (size plan: 11.54MB, exec: 7125.12KB)
-> Seq Scan on t1 (cost=0.00..2031.00 rows=100000 width=12) (actual time=0.012..49.225 rows=100000 loops=1)
Planning time: 0.424 ms
Execution time: 476.807 ms
PostgreSQL builds a tree structure of plan nodes representing the different actions taken. We can see that parts of the execution tree is being accelerated by the GPU (GpuPreAgg, GpuJon, GpuHashJoin) and we get as a result the planning and execution time.
Let’s turn off the extension an rerun the SQL statement. Again from our notebook we can issue the following.
dbGetQuery(con, "SET pg_strom.enabled=off")
dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 GROUP BY CAT")
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=27455.10..27488.38 rows=1024 width=24) (actual time=2819.862..2823.624 rows=1025 loops=1)
Group Key: t0.cat
-> Sort (cost=27455.10..27460.22 rows=2048 width=48) (actual time=2819.847..2821.153 rows=3075 loops=1)
Sort Key: t0.cat
Sort Method: quicksort Memory: 529kB
-> Gather (cost=27127.42..27342.46 rows=2048 width=48) (actual time=2806.990..2809.907 rows=3075 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial HashAggregate (cost=26127.42..26137.66 rows=1024 width=48) (actual time=2804.084..2804.723 rows=1025 loops=3)
Group Key: t0.cat
-> Hash Join (cost=3281.00..23018.05 rows=414583 width=16) (actual time=306.307..2281.754 rows=331664 loops=3)
Hash Cond: (t0.aid = t1.aid)
-> Parallel Seq Scan on t0 (cost=0.00..14476.67 rows=416667 width=12) (actual time=0.027..622.406 rows=333333 loops=3)
-> Hash (cost=2031.00..2031.00 rows=100000 width=12) (actual time=305.785. -> Seq Scan on t1 (cost=0.00..2031.00 rows=100000 width=12) (actual time=0.012..182.335 rows=100000 loops=3)
Planning time: 0.201 ms
Execution time: 2824.216 ms
We can see that no GPU methods appear and the query is run on a CPU. The execution time increased from ~477 ms to 2824 ms - almost a 6x speedup.
Let’s take this as a starting point and create a benchmark for natural joins for ten tables (t0-t9) adding one table at a time run on a CPU and on a GPU. I have implemented a small function to extract the execution time from the output and saving the results in a R data frame for easier handling and plotting (see the notebook for more details).
Now run the queries against the CPU and GPU.
dbGetQuery(con, "SET pg_strom.enabled=on")ton <- list()
ton <- c(ton, get_exec_time(dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 GROUP BY CAT")))
ton <- c(ton, get_exec_time(dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 NATURAL JOIN t2 GROUP BY CAT")))
ton <- c(ton, get_exec_time(dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 NATURAL JOIN t2 NATURAL JOIN t3 GROUP BY CAT")))
ton <- c(ton, get_exec_time(dbGetQuery(con, "EXPLAIN ANALYZE SELECT cat, count(*), avg(ax) FROM t0 NATURAL JOIN t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 GROUP BY CAT")))
...
After extracting the execution times we are now able to plot the data in R, we are using here ggplot2
, the most used graphing package in R.
The GPU accelerated queries level off at ~2.5 sec where the CPU queries increase by each new table added. We could go further and add more tables to show when the GPU execution time will increase but that is out of scope for this blog entry.
Running DBT-3 on PostgreSQL with PG-Strom
The DBT-3 benchmarks is an open source implementation of a decision support benchmark, where the data and workload are based on a specific database specification. The benchmark will be run with PG-Strom enabled and disabled similar to the test above.
Most the queries are accelerated without any SQL query changes (Some of the queries did not run either on CPU or GPU so they were reported as 0).
Conclusion
PG-Strom is a nice drop-in for immediate acceleration at the SQL database level. The more number crunching operations one has in a query (avg, cnt, sqrt, …) the more benefit one will have from the GPU.
Furthermore there are several efforts going on to accelerate R with GPUs. In the near future one might have a complete GPU accelerated pipeline from database to analytics, plotting and statistical computing in R.
Über den Autor
Mehr davon
Nach Thema durchsuchen
Automatisierung
Das Neueste zum Thema IT-Automatisierung für Technologien, Teams und Umgebungen
Künstliche Intelligenz
Erfahren Sie das Neueste von den Plattformen, die es Kunden ermöglichen, KI-Workloads beliebig auszuführen
Open Hybrid Cloud
Erfahren Sie, wie wir eine flexiblere Zukunft mit Hybrid Clouds schaffen.
Sicherheit
Erfahren Sie, wie wir Risiken in verschiedenen Umgebungen und Technologien reduzieren
Edge Computing
Erfahren Sie das Neueste von den Plattformen, die die Operations am Edge vereinfachen
Infrastruktur
Erfahren Sie das Neueste von der weltweit führenden Linux-Plattform für Unternehmen
Anwendungen
Entdecken Sie unsere Lösungen für komplexe Herausforderungen bei Anwendungen
Original Shows
Interessantes von den Experten, die die Technologien in Unternehmen mitgestalten
Produkte
- Red Hat Enterprise Linux
- Red Hat OpenShift
- Red Hat Ansible Automation Platform
- Cloud-Services
- Alle Produkte anzeigen
Tools
- Training & Zertifizierung
- Eigenes Konto
- Kundensupport
- Für Entwickler
- Partner finden
- Red Hat Ecosystem Catalog
- Mehrwert von Red Hat berechnen
- Dokumentation
Testen, kaufen und verkaufen
Kommunizieren
Über Red Hat
Als weltweit größter Anbieter von Open-Source-Software-Lösungen für Unternehmen stellen wir Linux-, Cloud-, Container- und Kubernetes-Technologien bereit. Wir bieten robuste Lösungen, die es Unternehmen erleichtern, plattform- und umgebungsübergreifend zu arbeiten – vom Rechenzentrum bis zum Netzwerkrand.
Wählen Sie eine Sprache
Red Hat legal and privacy links
- Über Red Hat
- Jobs bei Red Hat
- Veranstaltungen
- Standorte
- Red Hat kontaktieren
- Red Hat Blog
- Diversität, Gleichberechtigung und Inklusion
- Cool Stuff Store
- Red Hat Summit