Kohei KaiGai, profile picture
Uploaded byKohei KaiGai
459 views

20181116 Massive Log Processing using I/O optimized PostgreSQL

The document describes a technology called PG-Strom that uses GPU acceleration to optimize I/O performance for PostgreSQL. PG-Strom allows data to be transferred directly from NVMe SSDs to the GPU over the PCIe bus, bypassing the CPU and RAM. This reduces data movement and allows PostgreSQL queries to be partially executed directly on the GPU. Benchmark results show the approach can achieve throughput close to the theoretical hardware limits for a single server configuration processing large datasets.

Download to read offline
Massivelogdataprocessing usingI/Ooptimized PostgreSQL 〜NVME and GPU breaks through the storage performance limitation〜 HeteroDB,Inc Chief Architect & CEO KaiGai Kohei <kaigai@heterodb.com>
about HeteroDB Massive log data processing using I/O optimized PostgreSQL Corporate overview  Name HeteroDB,Inc  Established 4th-Jul-2017  Location Shinagawa, Tokyo, Japan  Businesses Production of accelerated PostgreSQL database Technical consulting on GPU&DB region By the heterogeneous-computing technology on the database area, we provides a useful, fast and cost-effective data analytics platform for all the people who need the power of analytics. CEO Profile  KaiGai Kohei – He has contributed for PostgreSQL and Linux kernel development in the OSS community more than ten years, especially, for security and database federation features of PostgreSQL.  Award of “Genius Programmer” by IPA MITOH program (2007)  The posters finalist at GPU Technology Conference 2017.
Features of RDBMS  High-availability / Clustering  DB administration and backup  Transaction control  BI and visualization  We can use the products that support PostgreSQL as-is. Core technology – PG-Strom Massive log data processing using I/O optimized PostgreSQL PG-Strom: An extension module for PostgreSQL, to accelerate SQL workloads by the thousands cores and wide-band memory of GPU. GPU Big-data Processing PG-Strom Machine-learning & Statistics
GPU’s characteristics - mostly as a computing accelerator Massive log data processing using I/O optimized PostgreSQL Over 10years history in HPC, then massive popularization in Machine-Learning NVIDIA Tesla V100 Super Computer (TITEC; TSUBAME3.0) Computer Graphics Machine-Learning How I/O workloads are accelerated by GPU that is a computing accelerator? Simulation
Where is the best “location” to process massive data? Massive log data processing using I/O optimized PostgreSQL  Data transportation is fundamentally expensive.  SQL is a simple and flexible tool for data management.  Long-standing familiar tool for engineers. Data Visualization Data Processing Data Accumulation Data Generation Statistics & Machine- Learning PG-Strom for Big-Data PG-Strom for Analytics SSD-to-GPU Direct SQL Columnar Data (Parquet) CREATE FUNCTION my_logic( reggstore, text ) RETURNS matrix AS $$ $$ LANGUAGE ‘plcuda’; PL/CUDA INSERT Gstore_fdw Pystrom Database system is closest to the data. Simple & Flexible data management without data transportation. BI Tools
DataSize Of course, mankind has no “silver bullets” for all the usage scenarios Massive log data processing using I/O optimized PostgreSQL Small Tier • Small & Middle Businesses or Branch of Big Companies • Data size: Less than 100GB • Software: RDBMS Middle Tier • Global or domestic large company • Data size: 1TB〜100TB • Software: Small scale Hadoop, or DWH solution Top Tier • Global Mega-Company • Data size: 100TB, or often PB class • Software: Large scale Hadoop cluster
Data Management Technical Challenges for Log Processing (1/3) Massive log data processing using I/O optimized PostgreSQL I/O (Throughput) Computing
Technical Challenges for Log Processing (2/3) Massive log data processing using I/O optimized PostgreSQL Distributed System has less H/W load, but more expensive in administration Data Management with Reasonable Design
Data Management Technical Challenges for Log Processing (3/3) Massive log data processing using I/O optimized PostgreSQL Ultra Fast I/O Parallel Computing Hardware Evolution pulls up single-node potential to big-data processing GPU NVME- SSD
Massive log data processing using I/O optimized PostgreSQL How PostgreSQL utilizes GPU? 〜Architecture of PG-Strom〜
GPU code generation from SQL - Example of WHERE-clause Massive log data processing using I/O optimized PostgreSQL QUERY: SELECT cat, count(*), avg(x) FROM t0 WHERE x between y and y + 20.0 GROUP BY cat; : STATIC_FUNCTION(bool) gpupreagg_qual_eval(kern_context *kcxt, kern_data_store *kds, size_t kds_index) { pg_float8_t KPARAM_1 = pg_float8_param(kcxt,1); pg_float8_t KVAR_3 = pg_float8_vref(kds,kcxt,2,kds_index); pg_float8_t KVAR_4 = pg_float8_vref(kds,kcxt,3,kds_index); return EVAL((pgfn_float8ge(kcxt, KVAR_3, KVAR_4) && pgfn_float8le(kcxt, KVAR_3, pgfn_float8pl(kcxt, KVAR_4, KPARAM_1)))); } : E.g) Transformation of the numeric-formula in WHERE-clause to CUDA C code on demand Reference to input data SQL expression in CUDA source code Run-time compiler Parallel Execution
EXPLAIN shows Query Execution plan with Custom GPU-node Massive log data processing using I/O optimized PostgreSQL postgres=# EXPLAIN ANALYZE SELECT cat,count(*),sum(ax) FROM tbl NATURAL JOIN t1 WHERE cid % 100 < 50 GROUP BY cat; QUERY PLAN --------------------------------------------------------------------------------------------------- GroupAggregate (cost=203498.81..203501.80 rows=26 width=20) (actual time=1511.622..1511.632 rows=26 loops=1) Group Key: tbl.cat -> Sort (cost=203498.81..203499.26 rows=182 width=20) (actual time=1511.612..1511.613 rows=26 loops=1) Sort Key: tbl.cat Sort Method: quicksort Memory: 27kB -> Custom Scan (GpuPreAgg) (cost=203489.25..203491.98 rows=182 width=20) (actual time=1511.554..1511.562 rows=26 loops=1) Reduction: Local Combined GpuJoin: enabled -> Custom Scan (GpuJoin) on tbl (cost=13455.86..220069.26 rows=1797115 width=12) (never executed) Outer Scan: tbl (cost=12729.55..264113.41 rows=6665208 width=8) (actual time=50.726..1101.414 rows=19995540 loops=1) Outer Scan Filter: ((cid % 100) < 50) Rows Removed by Outer Scan Filter: 10047462 Depth 1: GpuHashJoin (plan nrows: 6665208...1797115, actual nrows: 9948078...2473997) HashKeys: tbl.aid JoinQuals: (tbl.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.016..15.407 rows=100000 loops=1) Planning Time: 0.721 ms Execution Time: 1595.815 ms (19 rows)
A usual composition of x86_64 server Massive log data processing using I/O optimized PostgreSQL GPUSSD CPU RAM HDD N/W
Data flow to process a massive amount of data Massive log data processing using I/O optimized PostgreSQL CPU RAM SSD GPU PCIe PostgreSQL Data Blocks Normal Data Flow All the records, including junks, must be loaded onto RAM once, because software cannot check necessity of the rows prior to the data loading. So, amount of the I/O traffic over PCIe bus tends to be large. Unless records are not loaded to CPU/RAM once, over the PCIe bus, software cannot check its necessity even if it is “junk”.
Core Feature: SSD-to-GPU Direct SQL Massive log data processing using I/O optimized PostgreSQL CPU RAM SSD GPU PCIe PostgreSQL Data Blocks NVIDIA GPUDirect RDMA It allows to load the data blocks on NVME-SSD to GPU using peer-to-peer DMA over PCIe-bus; bypassing CPU/RAM. WHERE-clause JOIN GROUP BY Run SQL by GPU to reduce the data size Data Size: Small v2.0
Benchmark Results – single-node version Massive log data processing using I/O optimized PostgreSQL 2172.3 2159.6 2158.9 2086.0 2127.2 2104.3 1920.3 2023.4 2101.1 2126.9 1900.0 1960.3 2072.1 6149.4 6279.3 6282.5 5985.6 6055.3 6152.5 5479.3 6051.2 6061.5 6074.2 5813.7 5871.8 5800.1 0 1000 2000 3000 4000 5000 6000 7000 Q1_1 Q1_2 Q1_3 Q2_1 Q2_2 Q2_3 Q3_1 Q3_2 Q3_3 Q3_4 Q4_1 Q4_2 Q4_3 QueryProcessingThroughput[MB/sec] Star Schema Benchmark on NVMe-SSD + md-raid0 PgSQL9.6(SSDx3) PGStrom2.0(SSDx3) H/W Spec (3xSSD) SSD-to-GPU Direct SQL pulls out an awesome performance close to the H/W spec  Measurement by the Star Schema Benchmark; which is a set of typical batch / reporting workloads.  CPU: Intel Xeon E5-2650v4, RAM: 128GB, GPU: NVIDIA Tesla P40, SSD: Intel 750 (400GB; SeqRead 2.2GB/s)x3  Size of dataset is 353GB (sf: 401), to ensure I/O bounds workload
Technology Basis - GPUDirect RDMA Massive log data processing using I/O optimized PostgreSQL ▌P2P data transfer technology between GPU and other PCIe devices, bypass CPU  Originally designed for multi-nodes MPI over Infiniband  Infrastructure of Linux kernel driver for other PCIe devices, including NVME-SSDs. Copyright (c) NVIDIA corporation, 2015
SSD-to-GPU Direct SQL - Software Stack Massive log data processing using I/O optimized PostgreSQL Filesystem (ext4, xfs) nvme_strom kernel module NVMe SSD NVIDIA Tesla GPU PostgreSQL pg_strom extension read(2) ioctl(2) Hardware Layer Operating System Software Layer Database Software Layer blk-mq nvme pcie nvme rdma Network HBA File-offset to NVME-SSD sector number translation NVMEoF Target (JBOF) NVMe Request ■ Other software component ■ Our developed software ■ Hardware NVME over Fabric nvme_strom v2.0 supports NVME-over-Fabric (RDMA).
40min queries in the former version, now 30sec Case Study: Log management and analytics solution Massive log data processing using I/O optimized PostgreSQL Database server with GPU+NVME-SSD Search by SQL Security incident • Fast try & error • Familiar interface • Search on row-data Aug 12 18:01:01 saba systemd: Started Session 21060 of user root. Understanding the situation of damage Identifying the impact Reporting to the manager
Run faster, beyond the limitation
Next bottleneck – Flood of the data over CPU’s capacity NVME and GPU accelerates PostgreSQL beyond the limitation -PGconf.EU 2018-21 Skylake-SP allows P2P DMA routing up to 8.5GB/s over PCIe root complex. GPU SSD-1 SSD-2 SSD-3 md-raid0 Xeon Gold 6126T routing by CPU
Consideration for the hardware configuration Massive log data processing using I/O optimized PostgreSQL PCIe-switch (PLX) can make CPU more relaxed. CPU CPU PLX SSD GPU PLX SSD GPU PLX SSD GPU PLX SSD GPU SCAN SCAN SCAN SCAN JOIN JOIN JOIN JOIN GROUP BY GROUP BY GROUP BY GROUP BY Very small amount of data GATHER GATHER
Hardware with PCIe switch (1/2) - HPC Server Massive log data processing using I/O optimized PostgreSQL Supermicro SYS-4029TRT2 x96 lane PCIe switch x96 lane PCIe switch CPU2 CPU1 QPI Gen3 x16 Gen3 x16 for each slot Gen3 x16 for each slot Gen3 x16
Hardware with PCIe switch (2/2) - I/O expansion box Massive log data processing using I/O optimized PostgreSQL NEC ExpEther 40G (4slots) 4 slots of PCIe Gen3 x8 PCIe Swich 40Gb Ethernet Network switch CPU NIC extra I/O boxes
System configuration with I/O expansion boxes Massive log data processing using I/O optimized PostgreSQL PCIe I/O Expansion Box Host System (x86_64 server) NVMe SSD PostgreSQL Tables PostgreSQL Data Blocks Internal PCIe Switch SSD-to-GPU P2P DMA (Large data size) GPU WHERE-clause JOIN GROUP BY PCIe over Ethernet Pre-processed small data A few GB/s SQL execution performance per box A few GB/s SQL execution performance per box A few GB/s SQL execution performance per box NIC / HBA Simplified DB operations and APP development by the simple single-node PostgreSQL configuration Enhancement of capacity & performance Visible as leafs of partitioned child-tables on PostgreSQL v2.1
Benchmark (1/2) - System configuration Massive log data processing using I/O optimized PostgreSQL x 1 x 3 x 6 x 3 NEC Express5800/R120h-2m CPU: Intel Xeon E5-2603 v4 (6C, 1.7GHz) RAM: 64GB OS: Red Hat Enterprise Linux 7 (kernel: 3.10.0-862.9.1.el7.x86_64) CUDA-9.2.148 + driver 396.44 DB: PostgreSQL 11beta3 + PG-Strom v2.1devel NEC ExpEther 40G (4slots) I/F: PCIe 3.0 x8 (x16 physical) ... 4slots with internal PCIe switch N/W: 40Gb-ethernet Intel DC P4600 (2.0TB; HHHL) SeqRead: 3200MB/s, SeqWrite: 1575MB/s RandRead: 610k IOPS, RandWrite: 196k IOPS I/F: PCIe 3.0 x4 NVIDIA Tesla P40 # of cores: 3840 (1.3GHz) Device RAM: 24GB (347GB/s, GDDR5) CC: 6.1 (Pascal, GP104) I/F: PCIe 3.0 x16 SPECIAL THANKS FOR v2.1
Benchmark (2/2) - Result of query execution performance Massive log data processing using I/O optimized PostgreSQL  13 SSBM queries to 1055GB database in total (a.k.a 351GB per I/O expansion box)  Raw I/O data transfer without SQL execution was up to 9GB/s. In other words, SQL execution was faster than simple storage read with raw-I/O. 2,388 2,477 2,493 2,502 2,739 2,831 1,865 2,268 2,442 2,418 1,789 1,848 2,202 13,401 13,534 13,536 13,330 12,696 12,965 12,533 11,498 12,312 12,419 12,414 12,622 12,594 0 2,000 4,000 6,000 8,000 10,000 12,000 14,000 Q1_1 Q1_2 Q1_3 Q2_1 Q2_2 Q2_3 Q3_1 Q3_2 Q3_3 Q3_4 Q4_1 Q4_2 Q4_3 QueryExecutionThroughput[MB/s] Star Schema Benchmark for PgSQL v11beta3 / PG-Strom v2.1devel on NEC ExpEther x3 PostgreSQL v11beta3 PG-Strom v2.1devel Raw I/O Limitation max 13.5GB/s for query execution performance with 3x GPU/SSD units!! v2.1
PostgreSQL is a successor of XXXX? Massive log data processing using I/O optimized PostgreSQL 13.5GB/s with 3x I/O boxes 30GB/s with 8x I/O boxes is EXPECTED Single-node PostgreSQL now offers DWH-appliance grade performance
Massive log data processing using I/O optimized PostgreSQL The Future Direction 〜How latest hardware makes Big-Data easy〜
NVME-over-Fabric and GPU RDMA support Massive log data processing using I/O optimized PostgreSQL ready Host System SSD SSD SSD HBA CPU 100Gb- Network JBOF  More NVME-SSDs than what we can install on single-node with GPUs  Flexibility of storage capacity enhancement on demand GPU/DB-node Storage-node SSD-to-GPU Direct SQL over 100Gb Ethernet with RDMA protocol SSD SSD SSD SSD SSD SSD SSD SSD SSD HBA JBOF SSD SSD SSD SSD SSD SSD HBA
Direct Load of External Columnar Data File (Parquet) Massive log data processing using I/O optimized PostgreSQL  Large Data import to PostgreSQL takes long time before analytics.  Reference to external data files can skip the data importing.  Columnar format minimizes amount of I/O to be loaded from storage.  SSD-to-GPU Direct SQL on the columnar-file optimizes I/O efficiency. Foreign Table allows reference to various kind of external data source. It can be an infrastructure for SSD-to-GPU Direct SQL on Columnar Data Files. Table Foreign Table (postgres_fdw) Foreign Table (file_fdw) Foreign Table (Gstore_fdw) Foreign Table (Parquet_fdw) External RDMS server (Oracle, MySQL, ...) CSV File GPU Device Memory Parquet File External Data Source Data loading takes long time  WIP
Expected usage – IoT grade log data processing and analysis Massive log data processing using I/O optimized PostgreSQL As a data management, analytics and machine-learning platform for log data daily growing up Manufacturing Logistics Mobile Home electronics Why PG-Strom?  It covers around 30-50TB data with single node by addition of I/O expansion box.  It allows to process the raw log data as is, more than max performance of H/W.  Users can continue to use the familiar SQL statement and applications. BI Tools massive log collection and management JBoF: Just Bunch of Flash NVME over Fabric (RDMA) PG-Strom
Summary Massive log data processing using I/O optimized PostgreSQL  PG-Strom An extension of PostgreSQL, to accelerate SQL execution by GPU. It pulls out max capability of the latest hardware for big-data processing workloads.  Core feature: SSD-to-GPU Direct SQL It directly loads data blocks on NVME-SSD to GPU using P2P DMA, then runs SQL workloads on GPU on the middle of I/O path. By reduction of the data to be processed, it improves the performance of I/O bound jobs.  Major Usage?  Large Log Processing - M2M/IoT grows the data size to be processed rapidly, however, proper usage of the latest hardware can cover most of the workloads, before the big architecture change to distributed cluster.  Simple single-node system, Familiar SQL interface, 10GB/s〜 performance  What I didn’t talk in this session  Advanced Analytics, Machine-Learning & Similarity Search on GPU  Let us make another chance 
Backup Slides
uninitialized File BLK-100: unknown BLK-101: unknown BLK-102: unknown BLK-103: unknown BLK-104: unknown BLK-105: unknown BLK-106: unknown BLK-107: unknown BLK-108: unknown BLK-109: unknown Consistency with disk buffers of PostgreSQL / Linux kernel Massive log data processing using I/O optimized PostgreSQL ① PG-Strom checks PG’s shared buffer of the blocks to read  If block is already cached on the shared buffer of PostgreSQL.  If block may not be all-visible using visibility-map. ② NVME-Strom checks OS’s page cache of the blocks to read  If block is already cached on the page cache of Linux kernel.  But, on fast-SSD mode, page cache shall not be copied unless it is not diry. ③ Then, kicks SSD-to-GPU P2P DMA on the uncached blocks BLK-100: uncached BLK-101: cached by PG BLK-102: uncached BLK-103: uncached BLK-104: cached by OS BLK-105: cached by PG BLK-106: uncached BLK-107: uncached BLK-108: cached by OS BLK-109: uncached BLCKSZ (=8KB) Transfer Size Per Request BLCKSZ * NChunks BLK-108: cached by OS BLK-104: cached by OS BLK-105: cached by PG BLK-101: cached by PG BLK-100: uncached BLK-102: uncached BLK-103: uncached BLK-106: uncached BLK-107: uncached BLK-109: uncached unused SSD-to-GPU P2P DMA Userspace DMA Buffer (RAM) Device Memory (GPU) CUDA API (userspace) cuMemcpyHtoDAsync BLK-108: cached by OS BLK-104: cached by OS BLK-105: cached by PG BLK-101: cached by PG
(Extra Info) about Star Schema Benchmark Massive log data processing using I/O optimized PostgreSQL Q1-1) select sum(lo_extendedprice*lo_discount) as revenue from lineorder,date1 where lo_orderdate = d_datekey and d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25; Q4-1) select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit from date1, customer, supplier, part, lineorder where lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_partkey = p_partkey and lo_orderdate = d_datekey and c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, c_nation order by d_year, c_nation ; 1.6GB customer 528MB supplier 416KB date 206MB parts lineorder 1051GB

More Related Content

PDF
20181212 - PGconfASIA - LT - English
byKohei KaiGai
 
PDF
20181016_pgconfeu_ssd2gpu_multi
byKohei KaiGai
 
PDF
20181025_pgconfeu_lt_gstorefdw
byKohei KaiGai
 
PDF
20180920_DBTS_PGStrom_EN
byKohei KaiGai
 
PDF
20210301_PGconf_Online_GPU_PostGIS_GiST_Index
byKohei KaiGai
 
PDF
Technology Updates of PG-Strom at Aug-2014 (PGUnconf@Tokyo)
byKohei KaiGai
 
PDF
20210928_pgunconf_hll_count
byKohei KaiGai
 
PPTX
Parallel K means clustering using CUDA
byprithan
 
20181212 - PGconfASIA - LT - English
byKohei KaiGai
 
20181016_pgconfeu_ssd2gpu_multi
byKohei KaiGai
 
20181025_pgconfeu_lt_gstorefdw
byKohei KaiGai
 
20180920_DBTS_PGStrom_EN
byKohei KaiGai
 
20210301_PGconf_Online_GPU_PostGIS_GiST_Index
byKohei KaiGai
 
Technology Updates of PG-Strom at Aug-2014 (PGUnconf@Tokyo)
byKohei KaiGai
 
20210928_pgunconf_hll_count
byKohei KaiGai
 
Parallel K means clustering using CUDA
byprithan
 

What's hot

PDF
Parallel Implementation of K Means Clustering on CUDA
byprithan
 
PDF
Apache Nemo
byNAVER Engineering
 
PDF
A Deeper Dive into EXPLAIN
byEDB
 
PDF
Prashant de-ny-project-s1
byPrashant Ratnaparkhi
 
PDF
Clickhouse Capacity Planning for OLAP Workloads, Mik Kocikowski of CloudFlare
byAltinity Ltd
 
PDF
ClickHouse Features for Advanced Users, by Aleksei Milovidov
byAltinity Ltd
 
PDF
Tricks every ClickHouse designer should know, by Robert Hodges, Altinity CEO
byAltinity Ltd
 
PDF
20190909_PGconf.ASIA_KaiGai
byKohei KaiGai
 
PDF
Gruter_TECHDAY_2014_04_TajoCloudHandsOn (in Korean)
byGruter
 
PDF
Webinar: Secrets of ClickHouse Query Performance, by Robert Hodges
byAltinity Ltd
 
PPT
Upgrading To The New Map Reduce API
byTom Croucher
 
PPTX
scalable machine learning
bySamir Bessalah
 
PDF
Accelerate Reed-Solomon coding for Fault-Tolerance in RAID-like system
byShuai Yuan
 
PDF
Demystifying DataFrame and Dataset
byKazuaki Ishizaki
 
PDF
ABCI: An Open Innovation Platform for Advancing AI Research and Deployment
byRyousei Takano
 
PPTX
MapReduce: A useful parallel tool that still has room for improvement
byKyong-Ha Lee
 
PDF
Opportunities of ML-based data analytics in ABCI
byRyousei Takano
 
PDF
Distributed Multi-GPU Computing with Dask, CuPy and RAPIDS
byPeterAndreasEntschev
 
PDF
Supermicro High Performance Enterprise Hadoop Infrastructure
bytempledf
 
PDF
Supermicro cloudera hadoop
bySupermicro_SMCI
 
Parallel Implementation of K Means Clustering on CUDA
byprithan
 
Apache Nemo
byNAVER Engineering
 
A Deeper Dive into EXPLAIN
byEDB
 
Prashant de-ny-project-s1
byPrashant Ratnaparkhi
 
Clickhouse Capacity Planning for OLAP Workloads, Mik Kocikowski of CloudFlare
byAltinity Ltd
 
ClickHouse Features for Advanced Users, by Aleksei Milovidov
byAltinity Ltd
 
Tricks every ClickHouse designer should know, by Robert Hodges, Altinity CEO
byAltinity Ltd
 
20190909_PGconf.ASIA_KaiGai
byKohei KaiGai
 
Gruter_TECHDAY_2014_04_TajoCloudHandsOn (in Korean)
byGruter
 
Webinar: Secrets of ClickHouse Query Performance, by Robert Hodges
byAltinity Ltd
 
Upgrading To The New Map Reduce API
byTom Croucher
 
scalable machine learning
bySamir Bessalah
 
Accelerate Reed-Solomon coding for Fault-Tolerance in RAID-like system
byShuai Yuan
 
Demystifying DataFrame and Dataset
byKazuaki Ishizaki
 
ABCI: An Open Innovation Platform for Advancing AI Research and Deployment
byRyousei Takano
 
MapReduce: A useful parallel tool that still has room for improvement
byKyong-Ha Lee
 
Opportunities of ML-based data analytics in ABCI
byRyousei Takano
 
Distributed Multi-GPU Computing with Dask, CuPy and RAPIDS
byPeterAndreasEntschev
 
Supermicro High Performance Enterprise Hadoop Infrastructure
bytempledf
 
Supermicro cloudera hadoop
bySupermicro_SMCI
 

Similar to 20181116 Massive Log Processing using I/O optimized PostgreSQL

PDF
PGConf.ASIA 2019 Bali - Full-throttle Running on Terabytes Log-data - Kohei K...
byEqunix Business Solutions
 
PDF
20181210 - PGconf.ASIA Unconference
byKohei KaiGai
 
PDF
20201006_PGconf_Online_Large_Data_Processing
byKohei KaiGai
 
PDF
GPU/SSD Accelerates PostgreSQL - challenge towards query processing throughpu...
byKohei KaiGai
 
PDF
20170602_OSSummit_an_intelligent_storage
byKohei KaiGai
 
PDF
PG-Strom v2.0 Technical Brief (17-Apr-2018)
byKohei KaiGai
 
PDF
SQL+GPU+SSD=∞ (English)
byKohei KaiGai
 
PDF
20150318-SFPUG-Meetup-PGStrom
byKohei KaiGai
 
PDF
SQream DB - Bigger Data On GPUs: Approaches, Challenges, Successes
byArnon Shimoni
 
PDF
GPGPU Accelerates PostgreSQL (English)
byKohei KaiGai
 
PDF
SQL CUDA
byMuhaza Liebenlito
 
PDF
PG-Strom - GPGPU meets PostgreSQL, PGcon2015
byKohei KaiGai
 
PDF
GPGPU Accelerates PostgreSQL ~Unlock the power of multi-thousand cores~
byKohei KaiGai
 
PDF
20171206 PGconf.ASIA LT gstore_fdw
byKohei KaiGai
 
PDF
20160407_GTC2016_PgSQL_In_Place
byKohei KaiGai
 
PDF
Fast data in times of crisis with GPU accelerated database QikkDB | Business ...
byMatej Misik
 
PDF
Sqream DB on OpenPOWER performance
byGanesan Narayanasamy
 
PDF
pgconfasia2016 plcuda en
byKohei KaiGai
 
PDF
PL/CUDA - Fusion of HPC Grade Power with In-Database Analytics
byKohei KaiGai
 
PPTX
SQREAM DB on IBM Power9
byGanesan Narayanasamy
 
PGConf.ASIA 2019 Bali - Full-throttle Running on Terabytes Log-data - Kohei K...
byEqunix Business Solutions
 
20181210 - PGconf.ASIA Unconference
byKohei KaiGai
 
20201006_PGconf_Online_Large_Data_Processing
byKohei KaiGai
 
GPU/SSD Accelerates PostgreSQL - challenge towards query processing throughpu...
byKohei KaiGai
 
20170602_OSSummit_an_intelligent_storage
byKohei KaiGai
 
PG-Strom v2.0 Technical Brief (17-Apr-2018)
byKohei KaiGai
 
SQL+GPU+SSD=∞ (English)
byKohei KaiGai
 
20150318-SFPUG-Meetup-PGStrom
byKohei KaiGai
 
SQream DB - Bigger Data On GPUs: Approaches, Challenges, Successes
byArnon Shimoni
 
GPGPU Accelerates PostgreSQL (English)
byKohei KaiGai
 
SQL CUDA
byMuhaza Liebenlito
 
PG-Strom - GPGPU meets PostgreSQL, PGcon2015
byKohei KaiGai
 
GPGPU Accelerates PostgreSQL ~Unlock the power of multi-thousand cores~
byKohei KaiGai
 
20171206 PGconf.ASIA LT gstore_fdw
byKohei KaiGai
 
20160407_GTC2016_PgSQL_In_Place
byKohei KaiGai
 
Fast data in times of crisis with GPU accelerated database QikkDB | Business ...
byMatej Misik
 
Sqream DB on OpenPOWER performance
byGanesan Narayanasamy
 
pgconfasia2016 plcuda en
byKohei KaiGai
 
PL/CUDA - Fusion of HPC Grade Power with In-Database Analytics
byKohei KaiGai
 
SQREAM DB on IBM Power9
byGanesan Narayanasamy
 

More from Kohei KaiGai

PDF
20221116_DBTS_PGStrom_History
byKohei KaiGai
 
PDF
20221111_JPUG_CustomScan_API
byKohei KaiGai
 
PDF
20211112_jpugcon_gpu_and_arrow
byKohei KaiGai
 
PDF
20210731_OSC_Kyoto_PGStrom3.0
byKohei KaiGai
 
PDF
20210511_PGStrom_GpuCache
byKohei KaiGai
 
PDF
20201128_OSC_Fukuoka_Online_GPUPostGIS
byKohei KaiGai
 
PDF
20201113_PGconf_Japan_GPU_PostGIS
byKohei KaiGai
 
PDF
20200828_OSCKyoto_Online
byKohei KaiGai
 
PDF
20200806_PGStrom_PostGIS_GstoreFdw
byKohei KaiGai
 
PDF
20200424_Writable_Arrow_Fdw
byKohei KaiGai
 
PDF
20191211_Apache_Arrow_Meetup_Tokyo
byKohei KaiGai
 
PDF
20191115-PGconf.Japan
byKohei KaiGai
 
PDF
20190926_Try_RHEL8_NVMEoF_Beta
byKohei KaiGai
 
PDF
20190925_DBTS_PGStrom
byKohei KaiGai
 
PDF
20190516_DLC10_PGStrom
byKohei KaiGai
 
PDF
20190418_PGStrom_on_ArrowFdw
byKohei KaiGai
 
PDF
20190314 PGStrom Arrow_Fdw
byKohei KaiGai
 
PDF
20181212 - PGconf.ASIA - LT
byKohei KaiGai
 
PDF
20181211 - PGconf.ASIA - NVMESSD&GPU for BigData
byKohei KaiGai
 
PDF
20180920_DBTS_PGStrom_JP
byKohei KaiGai
 
20221116_DBTS_PGStrom_History
byKohei KaiGai
 
20221111_JPUG_CustomScan_API
byKohei KaiGai
 
20211112_jpugcon_gpu_and_arrow
byKohei KaiGai
 
20210731_OSC_Kyoto_PGStrom3.0
byKohei KaiGai
 
20210511_PGStrom_GpuCache
byKohei KaiGai
 
20201128_OSC_Fukuoka_Online_GPUPostGIS
byKohei KaiGai
 
20201113_PGconf_Japan_GPU_PostGIS
byKohei KaiGai
 
20200828_OSCKyoto_Online
byKohei KaiGai
 
20200806_PGStrom_PostGIS_GstoreFdw
byKohei KaiGai
 
20200424_Writable_Arrow_Fdw
byKohei KaiGai
 
20191211_Apache_Arrow_Meetup_Tokyo
byKohei KaiGai
 
20191115-PGconf.Japan
byKohei KaiGai
 
20190926_Try_RHEL8_NVMEoF_Beta
byKohei KaiGai
 
20190925_DBTS_PGStrom
byKohei KaiGai
 
20190516_DLC10_PGStrom
byKohei KaiGai
 
20190418_PGStrom_on_ArrowFdw
byKohei KaiGai
 
20190314 PGStrom Arrow_Fdw
byKohei KaiGai
 
20181212 - PGconf.ASIA - LT
byKohei KaiGai
 
20181211 - PGconf.ASIA - NVMESSD&GPU for BigData
byKohei KaiGai
 
20180920_DBTS_PGStrom_JP
byKohei KaiGai
 

Recently uploaded

PDF
Application Monitoring and Observability: Elastic Stack Solution for Producti...
byPattabhi Amperayani
 
PDF
Cassandra vs. ScyllaDB: Evolutionary Differences
byScyllaDB
 
PDF
Airtable Building Semantic Search with Milvus
byZilliz
 
PDF
Expert Python App Development Company for Modern Enterprises
bySynapseIndia
 
PDF
The invisible key: Securing the new attack vector of OAuth tokens
byGianluca Varisco
 
PDF
The Gory Details of a Full-Featured Userspace CPU Scheduler by Avi Kivity
byScyllaDB
 
PPTX
Autonomous Convoy Routing via Drone Swarms and Multi-Modal Threat Detection
byIvan Ruchkin
 
PPTX
xCapture v3: Efficient, Always-On Thread Level Observability with eBPF by Tan...
byScyllaDB
 
PPTX
"The Art of Web Scraping: Tree Algorithms and JS Magic", Dmytro Tarasenko
byFwdays
 
PPTX
"Daily workflows with Cursor IDE", Maksym Anisimov
byFwdays
 
PDF
Dragino商品カタログ 2025.7 LoRaWAN・NB-IoT・LTE-M(LTE Cat.M1)対応センサーリスト
byCRI Japan, Inc.
 
PDF
Getting the Best of TrueDEM – October News & Updates
bypanagenda
 
PPTX
Failure Modes in Platinum Crucibles Creep, Contamination, and Oxidation Mecha...
byM-Kube Enterprise
 
PDF
The Tale of Taming TigerBeetle's Tail Latency by Tobias Ziegler
byScyllaDB
 
PDF
From Data Chaos to Copilot Confidence: A Step-by-Step Microsoft 365 Copilot R...
byNikki Chapple
 
PDF
Combining AI with Red Teaming and Bug Bounty
byRamin Farajpour Cami
 
PDF
TrustArc Webinar - Migration to TrustArc: What Your Journey Will Look Like
byTrustArc
 
PPTX
CBD Belgium 2025 - The adventures of a Microsoft 365 Platform Owner.pptx
byJasper Oosterveld
 
PDF
APDCA Energy sustainability white paper.pdf
byflintglobalapac
 
PDF
Unlock This Brilliant App Freezur That Builds Brainrot Videos That Captivate ...
bySOFTTECHHUB
 
Application Monitoring and Observability: Elastic Stack Solution for Producti...
byPattabhi Amperayani
 
Cassandra vs. ScyllaDB: Evolutionary Differences
byScyllaDB
 
Airtable Building Semantic Search with Milvus
byZilliz
 
Expert Python App Development Company for Modern Enterprises
bySynapseIndia
 
The invisible key: Securing the new attack vector of OAuth tokens
byGianluca Varisco
 
The Gory Details of a Full-Featured Userspace CPU Scheduler by Avi Kivity
byScyllaDB
 
Autonomous Convoy Routing via Drone Swarms and Multi-Modal Threat Detection
byIvan Ruchkin
 
xCapture v3: Efficient, Always-On Thread Level Observability with eBPF by Tan...
byScyllaDB
 
"The Art of Web Scraping: Tree Algorithms and JS Magic", Dmytro Tarasenko
byFwdays
 
"Daily workflows with Cursor IDE", Maksym Anisimov
byFwdays
 
Dragino商品カタログ 2025.7 LoRaWAN・NB-IoT・LTE-M(LTE Cat.M1)対応センサーリスト
byCRI Japan, Inc.
 
Getting the Best of TrueDEM – October News & Updates
bypanagenda
 
Failure Modes in Platinum Crucibles Creep, Contamination, and Oxidation Mecha...
byM-Kube Enterprise
 
The Tale of Taming TigerBeetle's Tail Latency by Tobias Ziegler
byScyllaDB
 
From Data Chaos to Copilot Confidence: A Step-by-Step Microsoft 365 Copilot R...
byNikki Chapple
 
Combining AI with Red Teaming and Bug Bounty
byRamin Farajpour Cami
 
TrustArc Webinar - Migration to TrustArc: What Your Journey Will Look Like
byTrustArc
 
CBD Belgium 2025 - The adventures of a Microsoft 365 Platform Owner.pptx
byJasper Oosterveld
 
APDCA Energy sustainability white paper.pdf
byflintglobalapac
 
Unlock This Brilliant App Freezur That Builds Brainrot Videos That Captivate ...
bySOFTTECHHUB
 

20181116 Massive Log Processing using I/O optimized PostgreSQL

  • 1.
    Massivelogdataprocessing usingI/Ooptimized PostgreSQL 〜NVMEand GPU breaks through the storage performance limitation〜 HeteroDB,Inc Chief Architect & CEO KaiGai Kohei <kaigai@heterodb.com>
  • 2.
    about HeteroDB Massive logdata processing using I/O optimized PostgreSQL Corporate overview  Name HeteroDB,Inc  Established 4th-Jul-2017  Location Shinagawa, Tokyo, Japan  Businesses Production of accelerated PostgreSQL database Technical consulting on GPU&DB region By the heterogeneous-computing technology on the database area, we provides a useful, fast and cost-effective data analytics platform for all the people who need the power of analytics. CEO Profile  KaiGai Kohei – He has contributed for PostgreSQL and Linux kernel development in the OSS community more than ten years, especially, for security and database federation features of PostgreSQL.  Award of “Genius Programmer” by IPA MITOH program (2007)  The posters finalist at GPU Technology Conference 2017.
  • 3.
    Features of RDBMS High-availability / Clustering  DB administration and backup  Transaction control  BI and visualization  We can use the products that support PostgreSQL as-is. Core technology – PG-Strom Massive log data processing using I/O optimized PostgreSQL PG-Strom: An extension module for PostgreSQL, to accelerate SQL workloads by the thousands cores and wide-band memory of GPU. GPU Big-data Processing PG-Strom Machine-learning & Statistics
  • 4.
    GPU’s characteristics -mostly as a computing accelerator Massive log data processing using I/O optimized PostgreSQL Over 10years history in HPC, then massive popularization in Machine-Learning NVIDIA Tesla V100 Super Computer (TITEC; TSUBAME3.0) Computer Graphics Machine-Learning How I/O workloads are accelerated by GPU that is a computing accelerator? Simulation
  • 5.
    Where is thebest “location” to process massive data? Massive log data processing using I/O optimized PostgreSQL  Data transportation is fundamentally expensive.  SQL is a simple and flexible tool for data management.  Long-standing familiar tool for engineers. Data Visualization Data Processing Data Accumulation Data Generation Statistics & Machine- Learning PG-Strom for Big-Data PG-Strom for Analytics SSD-to-GPU Direct SQL Columnar Data (Parquet) CREATE FUNCTION my_logic( reggstore, text ) RETURNS matrix AS $$ $$ LANGUAGE ‘plcuda’; PL/CUDA INSERT Gstore_fdw Pystrom Database system is closest to the data. Simple & Flexible data management without data transportation. BI Tools
  • 6.
    DataSize Of course, mankindhas no “silver bullets” for all the usage scenarios Massive log data processing using I/O optimized PostgreSQL Small Tier • Small & Middle Businesses or Branch of Big Companies • Data size: Less than 100GB • Software: RDBMS Middle Tier • Global or domestic large company • Data size: 1TB〜100TB • Software: Small scale Hadoop, or DWH solution Top Tier • Global Mega-Company • Data size: 100TB, or often PB class • Software: Large scale Hadoop cluster
  • 7.
    Data Management Technical Challenges forLog Processing (1/3) Massive log data processing using I/O optimized PostgreSQL I/O (Throughput) Computing
  • 8.
    Technical Challenges forLog Processing (2/3) Massive log data processing using I/O optimized PostgreSQL Distributed System has less H/W load, but more expensive in administration Data Management with Reasonable Design
  • 9.
    Data Management Technical Challenges forLog Processing (3/3) Massive log data processing using I/O optimized PostgreSQL Ultra Fast I/O Parallel Computing Hardware Evolution pulls up single-node potential to big-data processing GPU NVME- SSD
  • 10.
    Massive log dataprocessing using I/O optimized PostgreSQL How PostgreSQL utilizes GPU? 〜Architecture of PG-Strom〜
  • 11.
    GPU code generationfrom SQL - Example of WHERE-clause Massive log data processing using I/O optimized PostgreSQL QUERY: SELECT cat, count(*), avg(x) FROM t0 WHERE x between y and y + 20.0 GROUP BY cat; : STATIC_FUNCTION(bool) gpupreagg_qual_eval(kern_context *kcxt, kern_data_store *kds, size_t kds_index) { pg_float8_t KPARAM_1 = pg_float8_param(kcxt,1); pg_float8_t KVAR_3 = pg_float8_vref(kds,kcxt,2,kds_index); pg_float8_t KVAR_4 = pg_float8_vref(kds,kcxt,3,kds_index); return EVAL((pgfn_float8ge(kcxt, KVAR_3, KVAR_4) && pgfn_float8le(kcxt, KVAR_3, pgfn_float8pl(kcxt, KVAR_4, KPARAM_1)))); } : E.g) Transformation of the numeric-formula in WHERE-clause to CUDA C code on demand Reference to input data SQL expression in CUDA source code Run-time compiler Parallel Execution
  • 12.
    EXPLAIN shows QueryExecution plan with Custom GPU-node Massive log data processing using I/O optimized PostgreSQL postgres=# EXPLAIN ANALYZE SELECT cat,count(*),sum(ax) FROM tbl NATURAL JOIN t1 WHERE cid % 100 < 50 GROUP BY cat; QUERY PLAN --------------------------------------------------------------------------------------------------- GroupAggregate (cost=203498.81..203501.80 rows=26 width=20) (actual time=1511.622..1511.632 rows=26 loops=1) Group Key: tbl.cat -> Sort (cost=203498.81..203499.26 rows=182 width=20) (actual time=1511.612..1511.613 rows=26 loops=1) Sort Key: tbl.cat Sort Method: quicksort Memory: 27kB -> Custom Scan (GpuPreAgg) (cost=203489.25..203491.98 rows=182 width=20) (actual time=1511.554..1511.562 rows=26 loops=1) Reduction: Local Combined GpuJoin: enabled -> Custom Scan (GpuJoin) on tbl (cost=13455.86..220069.26 rows=1797115 width=12) (never executed) Outer Scan: tbl (cost=12729.55..264113.41 rows=6665208 width=8) (actual time=50.726..1101.414 rows=19995540 loops=1) Outer Scan Filter: ((cid % 100) < 50) Rows Removed by Outer Scan Filter: 10047462 Depth 1: GpuHashJoin (plan nrows: 6665208...1797115, actual nrows: 9948078...2473997) HashKeys: tbl.aid JoinQuals: (tbl.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.016..15.407 rows=100000 loops=1) Planning Time: 0.721 ms Execution Time: 1595.815 ms (19 rows)
  • 13.
    A usual compositionof x86_64 server Massive log data processing using I/O optimized PostgreSQL GPUSSD CPU RAM HDD N/W
  • 14.
    Data flow toprocess a massive amount of data Massive log data processing using I/O optimized PostgreSQL CPU RAM SSD GPU PCIe PostgreSQL Data Blocks Normal Data Flow All the records, including junks, must be loaded onto RAM once, because software cannot check necessity of the rows prior to the data loading. So, amount of the I/O traffic over PCIe bus tends to be large. Unless records are not loaded to CPU/RAM once, over the PCIe bus, software cannot check its necessity even if it is “junk”.
  • 15.
    Core Feature: SSD-to-GPUDirect SQL Massive log data processing using I/O optimized PostgreSQL CPU RAM SSD GPU PCIe PostgreSQL Data Blocks NVIDIA GPUDirect RDMA It allows to load the data blocks on NVME-SSD to GPU using peer-to-peer DMA over PCIe-bus; bypassing CPU/RAM. WHERE-clause JOIN GROUP BY Run SQL by GPU to reduce the data size Data Size: Small v2.0
  • 16.
    Benchmark Results –single-node version Massive log data processing using I/O optimized PostgreSQL 2172.3 2159.6 2158.9 2086.0 2127.2 2104.3 1920.3 2023.4 2101.1 2126.9 1900.0 1960.3 2072.1 6149.4 6279.3 6282.5 5985.6 6055.3 6152.5 5479.3 6051.2 6061.5 6074.2 5813.7 5871.8 5800.1 0 1000 2000 3000 4000 5000 6000 7000 Q1_1 Q1_2 Q1_3 Q2_1 Q2_2 Q2_3 Q3_1 Q3_2 Q3_3 Q3_4 Q4_1 Q4_2 Q4_3 QueryProcessingThroughput[MB/sec] Star Schema Benchmark on NVMe-SSD + md-raid0 PgSQL9.6(SSDx3) PGStrom2.0(SSDx3) H/W Spec (3xSSD) SSD-to-GPU Direct SQL pulls out an awesome performance close to the H/W spec  Measurement by the Star Schema Benchmark; which is a set of typical batch / reporting workloads.  CPU: Intel Xeon E5-2650v4, RAM: 128GB, GPU: NVIDIA Tesla P40, SSD: Intel 750 (400GB; SeqRead 2.2GB/s)x3  Size of dataset is 353GB (sf: 401), to ensure I/O bounds workload
  • 17.
    Technology Basis -GPUDirect RDMA Massive log data processing using I/O optimized PostgreSQL ▌P2P data transfer technology between GPU and other PCIe devices, bypass CPU  Originally designed for multi-nodes MPI over Infiniband  Infrastructure of Linux kernel driver for other PCIe devices, including NVME-SSDs. Copyright (c) NVIDIA corporation, 2015
  • 18.
    SSD-to-GPU Direct SQL- Software Stack Massive log data processing using I/O optimized PostgreSQL Filesystem (ext4, xfs) nvme_strom kernel module NVMe SSD NVIDIA Tesla GPU PostgreSQL pg_strom extension read(2) ioctl(2) Hardware Layer Operating System Software Layer Database Software Layer blk-mq nvme pcie nvme rdma Network HBA File-offset to NVME-SSD sector number translation NVMEoF Target (JBOF) NVMe Request ■ Other software component ■ Our developed software ■ Hardware NVME over Fabric nvme_strom v2.0 supports NVME-over-Fabric (RDMA).
  • 19.
    40min queries in theformer version, now 30sec Case Study: Log management and analytics solution Massive log data processing using I/O optimized PostgreSQL Database server with GPU+NVME-SSD Search by SQL Security incident • Fast try & error • Familiar interface • Search on row-data Aug 12 18:01:01 saba systemd: Started Session 21060 of user root. Understanding the situation of damage Identifying the impact Reporting to the manager
  • 20.
    Run faster, beyondthe limitation
  • 21.
    Next bottleneck –Flood of the data over CPU’s capacity NVME and GPU accelerates PostgreSQL beyond the limitation -PGconf.EU 2018-21 Skylake-SP allows P2P DMA routing up to 8.5GB/s over PCIe root complex. GPU SSD-1 SSD-2 SSD-3 md-raid0 Xeon Gold 6126T routing by CPU
  • 22.
    Consideration for thehardware configuration Massive log data processing using I/O optimized PostgreSQL PCIe-switch (PLX) can make CPU more relaxed. CPU CPU PLX SSD GPU PLX SSD GPU PLX SSD GPU PLX SSD GPU SCAN SCAN SCAN SCAN JOIN JOIN JOIN JOIN GROUP BY GROUP BY GROUP BY GROUP BY Very small amount of data GATHER GATHER
  • 23.
    Hardware with PCIeswitch (1/2) - HPC Server Massive log data processing using I/O optimized PostgreSQL Supermicro SYS-4029TRT2 x96 lane PCIe switch x96 lane PCIe switch CPU2 CPU1 QPI Gen3 x16 Gen3 x16 for each slot Gen3 x16 for each slot Gen3 x16
  • 24.
    Hardware with PCIeswitch (2/2) - I/O expansion box Massive log data processing using I/O optimized PostgreSQL NEC ExpEther 40G (4slots) 4 slots of PCIe Gen3 x8 PCIe Swich 40Gb Ethernet Network switch CPU NIC extra I/O boxes
  • 25.
    System configuration withI/O expansion boxes Massive log data processing using I/O optimized PostgreSQL PCIe I/O Expansion Box Host System (x86_64 server) NVMe SSD PostgreSQL Tables PostgreSQL Data Blocks Internal PCIe Switch SSD-to-GPU P2P DMA (Large data size) GPU WHERE-clause JOIN GROUP BY PCIe over Ethernet Pre-processed small data A few GB/s SQL execution performance per box A few GB/s SQL execution performance per box A few GB/s SQL execution performance per box NIC / HBA Simplified DB operations and APP development by the simple single-node PostgreSQL configuration Enhancement of capacity & performance Visible as leafs of partitioned child-tables on PostgreSQL v2.1
  • 26.
    Benchmark (1/2) -System configuration Massive log data processing using I/O optimized PostgreSQL x 1 x 3 x 6 x 3 NEC Express5800/R120h-2m CPU: Intel Xeon E5-2603 v4 (6C, 1.7GHz) RAM: 64GB OS: Red Hat Enterprise Linux 7 (kernel: 3.10.0-862.9.1.el7.x86_64) CUDA-9.2.148 + driver 396.44 DB: PostgreSQL 11beta3 + PG-Strom v2.1devel NEC ExpEther 40G (4slots) I/F: PCIe 3.0 x8 (x16 physical) ... 4slots with internal PCIe switch N/W: 40Gb-ethernet Intel DC P4600 (2.0TB; HHHL) SeqRead: 3200MB/s, SeqWrite: 1575MB/s RandRead: 610k IOPS, RandWrite: 196k IOPS I/F: PCIe 3.0 x4 NVIDIA Tesla P40 # of cores: 3840 (1.3GHz) Device RAM: 24GB (347GB/s, GDDR5) CC: 6.1 (Pascal, GP104) I/F: PCIe 3.0 x16 SPECIAL THANKS FOR v2.1
  • 27.
    Benchmark (2/2) -Result of query execution performance Massive log data processing using I/O optimized PostgreSQL  13 SSBM queries to 1055GB database in total (a.k.a 351GB per I/O expansion box)  Raw I/O data transfer without SQL execution was up to 9GB/s. In other words, SQL execution was faster than simple storage read with raw-I/O. 2,388 2,477 2,493 2,502 2,739 2,831 1,865 2,268 2,442 2,418 1,789 1,848 2,202 13,401 13,534 13,536 13,330 12,696 12,965 12,533 11,498 12,312 12,419 12,414 12,622 12,594 0 2,000 4,000 6,000 8,000 10,000 12,000 14,000 Q1_1 Q1_2 Q1_3 Q2_1 Q2_2 Q2_3 Q3_1 Q3_2 Q3_3 Q3_4 Q4_1 Q4_2 Q4_3 QueryExecutionThroughput[MB/s] Star Schema Benchmark for PgSQL v11beta3 / PG-Strom v2.1devel on NEC ExpEther x3 PostgreSQL v11beta3 PG-Strom v2.1devel Raw I/O Limitation max 13.5GB/s for query execution performance with 3x GPU/SSD units!! v2.1
  • 28.
    PostgreSQL is asuccessor of XXXX? Massive log data processing using I/O optimized PostgreSQL 13.5GB/s with 3x I/O boxes 30GB/s with 8x I/O boxes is EXPECTED Single-node PostgreSQL now offers DWH-appliance grade performance
  • 29.
    Massive log dataprocessing using I/O optimized PostgreSQL The Future Direction 〜How latest hardware makes Big-Data easy〜
  • 30.
    NVME-over-Fabric and GPURDMA support Massive log data processing using I/O optimized PostgreSQL ready Host System SSD SSD SSD HBA CPU 100Gb- Network JBOF  More NVME-SSDs than what we can install on single-node with GPUs  Flexibility of storage capacity enhancement on demand GPU/DB-node Storage-node SSD-to-GPU Direct SQL over 100Gb Ethernet with RDMA protocol SSD SSD SSD SSD SSD SSD SSD SSD SSD HBA JBOF SSD SSD SSD SSD SSD SSD HBA
  • 31.
    Direct Load ofExternal Columnar Data File (Parquet) Massive log data processing using I/O optimized PostgreSQL  Large Data import to PostgreSQL takes long time before analytics.  Reference to external data files can skip the data importing.  Columnar format minimizes amount of I/O to be loaded from storage.  SSD-to-GPU Direct SQL on the columnar-file optimizes I/O efficiency. Foreign Table allows reference to various kind of external data source. It can be an infrastructure for SSD-to-GPU Direct SQL on Columnar Data Files. Table Foreign Table (postgres_fdw) Foreign Table (file_fdw) Foreign Table (Gstore_fdw) Foreign Table (Parquet_fdw) External RDMS server (Oracle, MySQL, ...) CSV File GPU Device Memory Parquet File External Data Source Data loading takes long time  WIP
  • 32.
    Expected usage –IoT grade log data processing and analysis Massive log data processing using I/O optimized PostgreSQL As a data management, analytics and machine-learning platform for log data daily growing up Manufacturing Logistics Mobile Home electronics Why PG-Strom?  It covers around 30-50TB data with single node by addition of I/O expansion box.  It allows to process the raw log data as is, more than max performance of H/W.  Users can continue to use the familiar SQL statement and applications. BI Tools massive log collection and management JBoF: Just Bunch of Flash NVME over Fabric (RDMA) PG-Strom
  • 33.
    Summary Massive log dataprocessing using I/O optimized PostgreSQL  PG-Strom An extension of PostgreSQL, to accelerate SQL execution by GPU. It pulls out max capability of the latest hardware for big-data processing workloads.  Core feature: SSD-to-GPU Direct SQL It directly loads data blocks on NVME-SSD to GPU using P2P DMA, then runs SQL workloads on GPU on the middle of I/O path. By reduction of the data to be processed, it improves the performance of I/O bound jobs.  Major Usage?  Large Log Processing - M2M/IoT grows the data size to be processed rapidly, however, proper usage of the latest hardware can cover most of the workloads, before the big architecture change to distributed cluster.  Simple single-node system, Familiar SQL interface, 10GB/s〜 performance  What I didn’t talk in this session  Advanced Analytics, Machine-Learning & Similarity Search on GPU  Let us make another chance 
  • 35.
  • 36.
    uninitialized File BLK-100: unknown BLK-101: unknown BLK-102:unknown BLK-103: unknown BLK-104: unknown BLK-105: unknown BLK-106: unknown BLK-107: unknown BLK-108: unknown BLK-109: unknown Consistency with disk buffers of PostgreSQL / Linux kernel Massive log data processing using I/O optimized PostgreSQL ① PG-Strom checks PG’s shared buffer of the blocks to read  If block is already cached on the shared buffer of PostgreSQL.  If block may not be all-visible using visibility-map. ② NVME-Strom checks OS’s page cache of the blocks to read  If block is already cached on the page cache of Linux kernel.  But, on fast-SSD mode, page cache shall not be copied unless it is not diry. ③ Then, kicks SSD-to-GPU P2P DMA on the uncached blocks BLK-100: uncached BLK-101: cached by PG BLK-102: uncached BLK-103: uncached BLK-104: cached by OS BLK-105: cached by PG BLK-106: uncached BLK-107: uncached BLK-108: cached by OS BLK-109: uncached BLCKSZ (=8KB) Transfer Size Per Request BLCKSZ * NChunks BLK-108: cached by OS BLK-104: cached by OS BLK-105: cached by PG BLK-101: cached by PG BLK-100: uncached BLK-102: uncached BLK-103: uncached BLK-106: uncached BLK-107: uncached BLK-109: uncached unused SSD-to-GPU P2P DMA Userspace DMA Buffer (RAM) Device Memory (GPU) CUDA API (userspace) cuMemcpyHtoDAsync BLK-108: cached by OS BLK-104: cached by OS BLK-105: cached by PG BLK-101: cached by PG
  • 37.
    (Extra Info) aboutStar Schema Benchmark Massive log data processing using I/O optimized PostgreSQL Q1-1) select sum(lo_extendedprice*lo_discount) as revenue from lineorder,date1 where lo_orderdate = d_datekey and d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25; Q4-1) select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit from date1, customer, supplier, part, lineorder where lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_partkey = p_partkey and lo_orderdate = d_datekey and c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, c_nation order by d_year, c_nation ; 1.6GB customer 528MB supplier 416KB date 206MB parts lineorder 1051GB