disclaimer - actian · 2018-11-16 · agenda oltp data growing pains the traditional ‘cures’...
TRANSCRIPT
This document is for informational purposes only and is subject to change at any time without notice. The information in this document is proprietary to Actian and no part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the express prior written permission of Actian.
This document is not intended to be binding upon Actian to any particular course of business, pricing, product strategy, and/or development. Actian assumes no responsibility for errors or omissions in this document. Actian shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. Actian does not warrant the accuracy or completeness of the information, text, graphics, links, or other items contained within this material. This document is provided without a warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, or non-infringement.
Disclaimer
Actian Hybrid DataConference2018 London
ActianHybrid DataConference2018 London
Alex Hanshaw
Data Growing Pains, What Now?
Director of Engineering
Agenda
OLTP Data Growing Pains The Traditional ‘Cures’▪Archiving▪ETL & DW
Actian X▪Hybrid database solution▪Avoiding the Pain Points
Demo▪Overview▪Creating and loading X100 tables▪Synchronising your OLTP and X100 tables▪Using Qlik to prove a point
OLTP Data Growing PainsWhether it’s slowly or quickly your data is growing!
OLTP Data Growth Pains
▪Data is growing. Fact!
▪Existing OLTP system slows down as data grows– More rows to process during query
execution– Overflow chains– Not designed for big data
▪Existing OLTP systems has hits data size limitations– Row limits– Column limits– Partition limits– File size limits
6 © 2018 Actian Corporation
The Traditional “Cures”Sometimes the cure is worse than the problem!
The Traditional ‘Cures’: Archiving Pain Points
▪Archival process creation and maintenance
▪Security concerns– GDPR
▪Slow retrieval▪Loss of insight
– Long term trends & patterns
8 © 2018 Actian Corporation
The Traditional ‘Cures’: ETL & DW
9 © 2018 Actian Corporation
Transactional
Enterprise Apps
ISVs
CustomApps
Vector
Data Loading
Data WareHouse
High Speed Analytics
Ingres
The Traditional ‘Cures’: ETL & DW Pain Points
▪Significant Investment– Software– Hardware– Training– Development & Testing
▪Risky– Data concerns - GDPR.– Incompatibility Issues– Restricted Functionality
▪No feedback loop▪Data Latency
– Reporting excludes live data
10 © 2018 Actian Corporation
Actian XA solution without more problems!
Actian X Hybrid Database
▪ A Hybrid OLTP & Analytics solution that is not strictly HTAP – but better!– No need to change existing OLTP
applications or migrate data– Allows incremental investment in analytics
▪ Actian X will handle both OLTP and Analytics Applications:– The Actian X solution is populated and
refreshed via an automated Load process.– The columnar data is compressed to
minimize the impact on OLTP operations.
▪ New Columnar X100 Table Type– Available on Linux 64-bit & Windows 64-bit– 12 times faster than HEAP on 6.7M rows– 22 times faster than HEAP on 33M rows– 23 times faster than HEAP on 200M rows
Query Processing
Act
ian
X
SQL Queries
Analytics Application
OLTP Application
Ingres Query Execution
OLTP
Query Plan
I/
O
X100 Query Execution
Analytics
Query Plan
I/
O
LOAD
Hybrid Application
Row storage for
OLTPColumnar storage
for Analytics
Actian X Hybrid Database
13
▪Responding up to 10X faster than your competition can be game-changing
▪Best-in-class analytics performance– Single node & Scale-out Hadoop– Designed for the cloud
▪Use Cases:–Fraud detection that identifies
transactions in real time.–Product promotions that respond to
real-time market opportunities.–Clickstream analytics to improve
online shopping experience–Real-time IoT analytics to make
adjustments to operational manufacturing systems
© 2018 Actian Corporation
500,000 1,000,000 1,500,000 2,000,000QphH 0
Actian Vector 5.0 2,140,307
SQL Server 2016 1,071,018
SQL Server 2016 969,504
SQL Server 2014
Sybase IQ
Jun ‘16
May ‘16
Mar ‘16
Jul ‘15
May ‘15
Apr ‘14
Oct ‘13
725,686
461,837
SQL Server 2014
SQL Server 2014
700,329
230,119
Twice as fast as the nearest competitor
TPC-H Scale Factor 3000GB
The Hybrid ‘Cure’: Avoiding the Pain Points with Actian X
▪No additional Investment– Software – Upgrade directly from
Ingres– Hardware – Can run on commodity
hardware– Training – Use existing interfaces and
syntax
▪Low Risk– Existing OLTP mechanisms protect
the new X100 data– No need to transmit data outside of
the database
▪Easy, Incremental Growth– Simple, easy table creation– Simple, automated synchronisation
▪ Insight into all of your data– No need to archive– Live data in your X100 applications
14 © 2018 Actian Corporation
Demo
Actian X – Demo Overview
▪Demonstration uses a 17.7M row data set provided by United States Department of Transportation’s Bureau of Transportation
▪Statistics of historical airline on-time flight data from January 2014 to January 2017. https://www.transtats.bts.gov/ONTIME/
▪Qlik Sense Desktop Dashboard
▪Growing Pains– 4 years of data could not be loaded into the
OLTP tables. TID limits.– Dashboard load time is more than 1 hour
against an OLTP heap table.
▪Actian X– More than 28 years of data can be loaded
into an X100 table.– X100 dashboard load time less than 15
seconds
Actian X – Demo Table Creation/Load
▪ create table carriers_x100 as select * from carriers_oltpwith structure = x100; \g
▪ create table ontime_x100 as select * from ontime_oltpwith structure = x100;
\g– 17.7M rows in 639.303834 seconds, that’s 10.6550639 minutes.– Processor: Intel(R) Core(TM) i7-6500U CPU @ 2.50GHz, 2592
Mhz, 2 Core(s), 4 Logical Processor(s)
▪ insert into table ontime_x100 select * from ontime_oltpwhere flightdate > (date('now') - date('3
months')) \gcarriers_oltp
Actian X – Demo Synchronisation
▪ INSERTS– create procedure proc_ins_carriers_x100 ( ccode char(2) with
null, carrier char(25) with null ) as
begin insert into carriers_x100 ( ccode, carrier ) values ( :ccode,
:carrier );end \g
– create rule trg_ins_carriers_x100 after insert into carriers_oltpexecute procedure proc_ins_carriers_x100 (ccode = new.ccode,
carrier = new.carrier ); \g carriers_oltp
Actian X – Demo Synchronisation
▪UPDATES– create procedure proc_upd_carriers_x100 ( old_ccode char(2)
with null,old_carrier char(25) with null, new_ccode char(2)
with null,new_carrier char(25) with null )
as beginupdate carriers_x100 set ccode = :new_ccode,
carrier = :_new_carrierwhere ccode = :old_ccode and carrier =
:old_carrier;end \g
– create rule trg_upd_carriers_x100 after update of carriers_oltpexecute procedure proc_upd_carriers_x100 ( old_ccode =
old.ccode,old_carrier = old.carrier, new_ccode = new.ccode, new_carrier = new.carrier ); \g
carriers_oltp
Actian X – Demo Synchronisation
▪DELETES– create procedure proc_del_carriers_x100 ( ccode char(2) with
null )as begin
delete from carriers_x100 where ccode = :ccode;end \g
– create rule trg_del_carriers_x100 after delete from carriers_oltpexecute procedure proc_del_carriers_x100 ( ccode =
old.ccode ); \g
▪WARNING: If multiple rules fire at the same time the order of execution is not guaranteed.
carriers_oltp
Actian X – Demo
Actian X – Demo Proves a Point
▪Establishes a high speed analytics dashboard running against 17.7M rows of “live” data in under 20 minutes.
▪Minimal changes to Qlik dashboards
▪No additional financial investment
▪No learning curve
▪Provides a future where your business insight is based on all of your data
22 © 2018 Actian Corporation
Actian X – Hybrid Joins
▪New in Actian X 11.1 (Q4 2018)– Use standard join syntax to join OLTP and X100 tables– Allows ‘Live’ results without needing to update X100 tables after every transaction– No need to replicate small tables to satisfy join requirements– Reduced update activity against X100 tables– Reduce duplication of data– Simplifies DBP & Rule based synchronisation– Seamlessly add feedback loop to OLTP
▪ Further reduce the pain of data growth!
Thank you!Any Questions?