combinandoen un mismodbms: transacciones+ análisisde ... · retos de la carga de trabajo de tipo...
TRANSCRIPT
© 2011 IBM Corporation
Informix Ultimate Warehouse Edition
Combinando en un mismo DBMS:Transacciones + Análisis de Rendimiento Extremo
Jun 9, 1011
Veronica Gomes ([email protected]) – IBM Informix Development
© 2011 IBM Corporation2
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation3
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation4
Software Group – Information Management
Transacciones vs Análisis – Uso de la Base de Datos
Data Warehouse para Análisis (OLAP)Base de Datos para Transacciones (OLTP)
�Métrica de rendimiento: Query Throughput & Respuesta�Métrica de rendimiento: Transaction Throughput
�Uso ad-hoc, queries no conocidos ni optimizados a priori�Uso repetitivo, SQL pre-definidos y pre-optimizados
�Acceso secuencial a gran volumen de datos, operaciones sobre columnas�Acceso aleatorio a poco volumen de datos, operaciones sobre registros
�Queries complejos, joins, filtros, agregaciones; Delta de cargas (batch)�Transacciones cortas y simples, lecturas y cambios aleatorios en línea
�Pocos usuarios concurrentes (de niveles gerencial y ejecutivo)�Muchos usuarios concurrentes (de niveles operativo e IT)
�Data histórica, integrada, detallada/sumarizada a cierto nivel, duplicada�Data actual, aislada a un sistema, detallada, normalizada
�Diseño multidimensional: métricas y perspectivas del negocio (Estrella)�Diseño de BD a partir de la aplicación (Modelo Entidad-Relación)
�Acceso a través de herramientas de inteligencia de negocios�Acceso a través de aplicaciones específicas
�Soporta la estrategia y toma de decisiones del negocio�Soporta las operaciones de misión crítica y día-a-día del negocio
© 2011 IBM Corporation5
Software Group – Information Management
5
Almacenamiento por Registro – Ventajas y Retos para OLAP
� Bases de datos con almacenamiento basado en registro (row-based / row-wise database)
� Características de este diseño:– Usada en disco y memoria por los Sistemas para Manejo de Bases de Datos Relacionales (RDBMS)– Se almacena un registro completo (tupla/fila) dentro de una página, seguido por el próximo registro– Optimiza el almacenamiento de nuevos registros y posteriores cambios en ellos (loads, inserts, merge, update, delete)– Es eficiente en I/O al obtener registros si el query necesita la mayoría de sus columnas– Si la data es comprimida en el almacenamiento, el registro es descomprimido cuando se obtiene
� Ventajas:– La tecnología de RDBMS ha evolucionado por décadas, a plataformas que satisfacen altas demandas empresariales– Alto rendimiento en cargas de trabajo y transacciones en base a registros, como las de tipo OLTP– Alto rendimiento en operaciones masivas de carga, inserción, merge y actualización, importantes en Data Warehouse
Informix aprovecha esta tecnología para cargas OLTP y OLAP
© 2011 IBM Corporation6
Software Group – Information Management
6
Almacenamiento por Registro – Ventajas y Retos para OLAP
� Retos de la carga de trabajo de tipo Warehousing/OLAP producida por consultas analíticas:– Es inherentemente compleja para los RDBMS, ya que estos queries típicamente:
• Necesitan procesar sólo pocas columnas en millones/billones de registros a la vez• Producen múltiples joins, scans, funciones de agregación, sorts y predicados• Son queries ad-hoc, generados al momento desde herramientas de BI/OLAP• El acceso es costoso: Los valores de la misma columna para cualesquiera dos registros distintos se encuentran separados entre sí
– Todo esto en una base de datos que crece incrementalmente– Los usuarios demandan cada vez por tiempos de respuesta rápidos y predecibles
Select col1 c, sum(col3) total
From …
Group by col1
Order by total desc;
© 2011 IBM Corporation7
Software Group – Information Management
7
Almacenamiento por Registro – Ventajas y Retos para OLAP
� Algunas formas de superar estos retos en un RDBMS incluyen:– Construcción de índices– Particionamiento de datos (Fragmentación)– Compresión de datos– Construcción de Cubos (MOLAP/ROLAP/HOLAP)– Creación y mantenimiento de Tablas de Agregados o Vistas Materializadas– Entonación de la consulta SQL reescribiendo el query– Actualización de las Estadísticas del Optimizador– Ajuste de parámetros para optimizar carga de trabajo OLAP/DSS en el motor– Appliances que agregan nueva capa de hardware y software optimizar el I/O al DBMS
� Tener un sistema con carga de trabajo mixta (OLTP/OLAP) es siempre un reto– Ajustar y mantener el sistema para rendimiento tanto para OLTP como para OLAP– La carga de trabajo OLAP afecta el rendimiento de la carga de trabajo OLTP y viceversa
© 2011 IBM Corporation8
Software Group – Information Management
Almacenamiento por Columna – Ventajas y Retos para OLAP
� Bases de datos columnares (column-based / column-oriented / columnar databases)
� Características:– La tecnología existe desde comienzos la década de los 1970’s y ha resurgido recientemente– Se almacenan juntos los valores de cada columna, para todas las instancias (registros)
� Ventajas:– Alto rendimiento en consultas analíticas y cualquier carga de trabajo basada en columna, donde típicamente se necesitan todos los valores de unas pocas columnas
– Al guardar juntos todos valores de cada columna, se pueden obtener mejores niveles de compresión– Combinada con tecnología de in-memory database, se alcanzan tiempos extremadamente bajos
Select col1 c, sum(col3) total
From …
Group by col1
Order by total desc;
© 2011 IBM Corporation9
Software Group – Information Management
Almacenamiento por Columna – Ventajas y Retos para OLAP
� Retos:– Lentitud en la carga completa e incremental del warehouse: Cada registro a insertar es separado en sus columnas, cada una se almacena en forma comprimida junto con las otras instancias de valores de dicha columna
– Lentitud en operaciones basadas en registro u obtención de múltiples columnas: Cada vez que se requiere un registrose obtienen y descomprimen separadamente los valores de sus columnas, y se agrupan para formar el registro
– Tienen necesidad de producir mecanismos internos para “imitar” un sistema relacional en varios aspectos– Puede necesitarse: disponer de mucha memoria para escalar, crear índices apropiados, y reorganizar toda la data– No apropiados para ambientes mixtos OLTP/OLAP– Los datos pueden no estar disponibles durante las cargas/actualizaciones– Dependiendo de la implementación, pueden ofrecer limitaciones adicionales en: el esquema y jerarquía de datos, acceso a los datos, uso de estándarles y SQL, alta-disponibilidad, escalabilidad, concurrencia de usuarios, etc
Informix aprovecha esta tecnología para queries OLAP
© 2011 IBM Corporation10
Software Group – Information Management
10
Data Warehousing en 2011
DBMS Market in 2011:
� DBMS market at the close of 2009 was approximately $21.2 billion (2010 data not yet available)
� Data Warehouse DBMS market was approximately 35% of the DBMS market or $7.42 billion
Key Findings:
� Data warehouse DBMSs have evolved to a broader analytics infrastructure supporting operational analytics, corporate performance management and other new applications and uses.
� Cost is driving interest in alternative architectures but performance optimization is driving multi-tiered data architectures and a variety of deployment options - notably a strong interest in in-memory data mart deployments.
© 2011 IBM Corporation11
Software Group – Information Management
11
Data Warehousing en 2011
Market Dynamics for 2011:
� Today, smaller data warehouses, those less than 5 TB's of source system extracted data (SSED) are the only "data warehouse" for the entire organization and are commonly solving organizations' analytic needs. Gartner estimates that between 70% and 75% of all systems referred to as EDW are actually single business departments in nature.
Analysis:
� Optimization techniques such as summaries, aggregates and indexes are simply the result of performance restrictions inherent to normalized data and the way the RDBMS manages rows and columns.
© 2011 IBM Corporation12
Software Group – Information Management
12
Data Warehousing en 2011
A Glimpse Into the Future:
� Vendor solutions began to focus even more on the ability to isolate and prioritize workload types including strategies for dual warehouse deployments and mixing OLTP and OLAP on the same platform.
� In-memory DBMS solutions provide a technology which enables OLTP/OLAP combined solutions. Organizations should increase their emphasis on financial viability during 2011 and even into 2012 as well as aligning their analytics strategies with vendor road maps when choosing a solution.
© 2011 IBM Corporation13
Software Group – Information Management
13
Data Warehouse Appliances:
� DW appliances are not a new concept. Most vendors have developed an appliance offering or promote
certified configurations. Main reason for consideration is simplicity.
The Resurgence of Data Marts:
� Data marts can be used to optimize DW by offloading part of the workload, returning greater performance
to the warehousing environment
Column-Store DBMSs:
� CIOs should be aware that their current DBMS vendor may offer a column-store solution. Don’t just buy a
column-store-only DBMS because a column store was recommended by your team.
In-Memory DBMSs:
� IMDBMS technology also introduces a higher probability that analytics and transactional systems can
share the same database.
Tendencias de Data Warehouse para CIOs, 2011-2012
© 2011 IBM Corporation14
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation15
Software Group – Information Management
15
Historia de Informix en Data Warehousing
� IBM Informix tiene 3 productos de base de datos:– XPS para Base de Datos en sistemas MPP– Red Brick para data marts/data warehousing con esquemas Estrella (Star Schema)– Informix Dynamic Server (IDS/Informix) para OLTP y (ahora) Data Warehousing
© 2011 IBM Corporation16
Software Group – Information Management
16
Informix Warehouse Feature- SQW- Data Modeling- ELT/ETL
Informix Warehouse with Storage Optimization/Compression
Cognos integration- Native Content Store on IDSSQL Merge
External Tables
Star Join OptimizationMulti-index Scan(Internal Bitmap technology)New FragmentationFragment Level StatsStorage Provisioning
Roadmap de Data Warehousing en Informix
Informix Warehouse Accelerator
IWA Phase 2Con el Informix Ultimate Warehouse Edition
© 2011 IBM Corporation17
Software Group – Information Management
17
Source: Forrester
Query
Tools
Analytics
BPS
Apps
BI
Apps
LOB
apps
Databases
Other
transactional
data sources
I/O & data loading
Query processing
DBMS & Storage mgmt
Data Loading
HPL
DB utilities
ON utilities
DataStage
External Tables
Online attach/detach
Data & Storage Management
Deep Compression (option Ultimate)
Interval and List Fragmentation
Online attach/detach
Fragment level stats
Storage provisioning
Table defragmenter
Query Processing
Light Scans
Merge
Hierarchical Queries
Multi-Index Scan
Skip Scan
Bitmap Technology
Star and Snowflake join optimization
Implicit PDQ
Access performance
Características de Data Warehousing disponibles en Informix 11.70
Desde 11.70.xC2: Informix Warehouse Accelerator(Informix Ultimate Warehouse Edition)
© 2011 IBM Corporation18
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation19
Software Group – Information Management
Ediciones del servidor de base de datos IBM Informix 11.7
� (DevWorks Article) Compare the Informix Version 11 editions (by Carlton Doe)
Learn the differences in features, functions, and pricing models
http://www.ibm.com/developerworks/data/library/techarticle/dm-0801doe/index.html
� (Brochure) Informix product family V11.7 (1.39MB)
ftp://public.dhe.ibm.com/common/ssi/ecm/en/imb14078usen/IMB14078USEN.PDF
� (Overview Page) IBM Informix Product family
http://www.ibm.com/software/data/informix/
© 2011 IBM Corporation20
Software Group – Information Management
Informix Ultimate Warehouse Edition 11.70
� IBM Informix Ultimate Edition (64-bit)
� IBM Informix Storage Optimization
Feature (Deep Compression)
� IBM Informix Warehouse Accelerator
(Intel Linux 64-bit hardware + software)
El IBM Informix Ultimate Warehouse Edition consiste de los siguientes
componentes de software y hardware:
IBM System x3850 X5
+
(Nuevo) Add-on de tipo Appliance con tecnología innovadora
para performance extremo de cargas analíticas en Informix
© 2011 IBM Corporation21
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation22
Software Group – Information Management
22
La Tercera Generación de Bases de Datos ya está aquí
Source: IDC’s Article (Carl Olofson) – Feb. 2010
1st Generation
2nd Generation
3rd Generation
�Vendor proprietary databases of IMS, IDMS, Datacom
�RDBMS for Open Systems, dependent on disk layout, limitations in scalability
and disk I/O
�Database tuning by adding updating stats, creating/dropping indexes, data
partitioning, summary tables & cubes, force query plans, resource governing
�Most data warehouses will be stored in a columnar fashion
�Most OLTP database will either be augmented by an in-memory database
(IMDB) or reside entirely in memory
�Most large-scale servers will achieve horizontal scalability through clustering
IDC predicts that, 4 years from now…
OLTP and Data Warehousing can co-exist on the same system in an economic way
© 2011 IBM Corporation23
Software Group – Information Management
23
Qué lo hace diferente?
• Performance: Tiempos de respuesta sin
precedentes y predecibles, para permitir
cargas analíticas que no bloquean ni afectan
otras cargas analíticas ni operacionales
• Integración: Se conecta con Informix/IDS a
un nivel profundo y provee transparencia
para las aplicaciones
• Cargas de trabajo auto-suficientes: Los
queries son ejecutados de la forma más
efeciente posible
• Transparencia: Las aplicaciones
conectadas a Informix no advierten del IWA
• Simplified administration: Optimización
manejada por el appliance, eliminando
muchas tareas de performance tuning
Qué es?
El Informix Warehouse Accelerator (IWA) es un add-on
de tipo Appliance para Informix, optimizado para
procesamiento de workload de warehouse, que
permite la combinación en un mismo sistema Informix
de análisis del negocio y procesos operacionales.
El IWA acelera en una plataforma de BD columnar en
memoria queries de warehouse selectivos para obtener
tiempos de respuesta sin precedentes.
Permite combinar en un mismo sistema Informix 11.70lo mejor de OLTP con lo mejor de Data Warehousing
El Informix Warehouse Accelerator
© 2011 IBM Corporation24
Software Group – Information Management
El Informix Warehouse Accelerator
� Add-on de tipo appliance para Informix
� Provee tecnología de BD de 3ra generación para warehouse y ambientes mixtos con Informix
� Informix lo usa para ejecutar con extrema rapidez queries analíticos sobre data marts específicos
� Hardware IBM de bajo costo:
� Intel 64-bit (IBM System ex5)
� Multi-core y SIMD (8 cores)
� Memoria (512GB-1.5TB, pronto:3TB)
� Arreglo de Discos (6 x 300 GB SAS)
� Software con múltiples tecnologías avanzadas
para performance de queries sobre datamarts:
� 64-bit In-Memory Database
� Columnar storage and access
� Extreme Compression
� Frequency Partitioning -column groups
� Massive Parallelism –load/query
� Predicate eval. on compressed data
� Multi-core & Vector Optimization
� Rendimiento de queries analíticos aceleradosmejora en órdenes de magnitud (ex 10-500+x)
� Efecto indirecto positivo en otras cargas
� Aceleración automática y transparente
� Soporte de las herramientas de BI
� Funciona sobre la infraestructura de warehouse existente en Informix
� Rendimiento y Simplicidad:
� No Query Tuning and Optimizer Hints
� No Database tuning for warehousing
� No Index creation, reorganization
� No Update Statistics
� No Partioning/Fragementation
� No Storage Mgmt/Page size configuration
� No Database/schema changes
� No Application changes
� No Summary Tables/Materialized Views
� No Buying more expensive Hardware
� No Change of expectations
Qué es? Qué ofrece?
© 2011 IBM Corporation25
Software Group – Information Management
25
1
2
34
5
6
7 1
2
34
5
6
7
Row & Columnar DatabaseRow format within IDS for transactional workloads and columnar data access via accelerator for
OLAP queries.
Extreme Compression
Required because RAM is the limiting factor.
Massive ParallelismAll cores are used within cpus for queries
Predicate evaluation on
compressed dataOften scans w/o decompression
during evaluation
Frequency Partitioning
Enabler for the effective parallel access of the compressed data for scanning. Horizontal and Vertical Partition
Elimination.
In Memory Database
3rd generation database technology avoids I/O. Compression allows huge databases
to be completely memory resident
Multi-core and Vector
Optimized AlgorithmsAvoiding locking or synchronization
Tecnologías en el Informix Warehouse Accelerator para optimización de warehousing
© 2011 IBM Corporation26
Software Group – Information Management
Dónde funciona el IWA en Business Intelligence (BI)
� Supported queries: Analytics complex, ah-doc, with:– Sequential scans and much I/O, multiple joins (including the fact table), multiple predicates/filters – aggregate functions, scalar functions– sorts, group by
� A query can be sent to the accelerator for processing only if:– The query refers to the fact table– The query refers to a subset of the tables in the data mart definition– The table joins that are in the query match the table joins that are specified in the data mart definition.
� In some specific cases, the IWA will not accelerate a query, and IDS will process it instead– Consult Users’ Guide for more information
STAR/SNOWFLAKE schama
© 2011 IBM Corporation27
Software Group – Information Management
27
Definiendo el Data Mart que se va a acelerar
� A MART is a logical collection of tables which are related to each other. For example, all tables of a single star schema would belong to the same MART.
� The administrator uses a rich client interface to define the tables which belong to a MART together with the information about their relationships.
� IDS creates definitions for these MARTs in the own catalog. The related data is read from the IDS tables and transferred to IWA.
� The IWA transforms the data into a highly compressed, scan optimized format which is kept locally (in memory) on the Accelerator
Define
Worker Processes
Coordinator Process
IDS + IWA
© 2011 IBM Corporation28
Software Group – Information Management
Pasos para Poner a funcionar el IWA de Informix
© 2011 IBM Corporation29
Software Group – Information Management
29
• Alternative 1: Install IWA on a separate Linux box
• Alternative 2: Install Informix and IWA in the same symmetric multiprocessing system
• Note: IWA requires Linux on Intel x64 (64-bit EM64T) Xenon
Informix Database Server Informix Warehouse Accelerator
64-bit RHEL 5/SUSE 11
Solaris 10/AIX 6.1/HP-UX 11.3164-bit RHEL 5/SUSE 11
Informix Warehouse Accelerator
64-bit RHEL 5/SUSE 11
Informix Database Server
Arquitectura - Escenarios de Configuración
© 2011 IBM Corporation30
Software Group – Information Management
30
Informix Database Servercoordinator node
worker node
Query Router
Informix Warehouse Accelerator
worker node worker node worker node
highly compressed
data in memory:
25% of fact table +
copy of all
dimension tables
highly compressed
data in memory:
25% of fact table +
copy of all
dimension tables
highly compressed
data in memory:
25% of fact table +
copy of all
dimension tables
highly compressed
data in memory:
25% of fact table +
copy of all
dimension tables
accelerator storage directory: compressed data on disk
OLTP Client BI Client
Manages the distributiontasks such as loadingdata and and query
processing.Performs data
compression and query processing with extreme parallelism
DWADIR=$IWA_INSTALL_DIR/dwa/demo
START_PORT=21020
NUM_NODES=5 (1 coord, 4 worker)
WORKER_SHM=0.7 (70% of mem)
COORDINATOR_SHM=0.05 (5% of mem)
DRDA_INTERFACE="eth0"
accelerated queries
1 coordinator node per 4 worker nodes
TCP/IP &
DRDA
Arquitectura de Procesos del Informix Warehouse Accelerator
© 2011 IBM Corporation31
Software Group – Information Management
31
TCP/IP
Informix (IDS):• Routes SQL queries to accelerator
• User need not change SQL or apps.
• Can always run query in IDS, e.g., if
– too short an est. execution time
Bulk Loader
SQL Queries (from apps)
Informix Warehouse Accelerator
Compressed
DB partition
Query
Processor
Data Warehouse
IDS SQL
(via DRDA)
Query Router
Informix Warehouse Accelerator:
� Connects to IDS via TCP/IP & DRDA
� Analyzes, compresses, and loads
� Copy of (portion of) warehouse
� Processes routed SQL query and
returns answer to IDS
Results
Configuración del Informix Warehouse Accelerator con Informix
GUI tool
(ISAO)
© 2011 IBM Corporation32
Software Group – Information Management
IBM Smart Analytics Optimizer (ISAO) Studio
Easy to use graphical tool for data mart configuration
© 2011 IBM Corporation33
Software Group – Information Management
Consideraciones para usar el Accelerator
� Data mart is required to be designed in a star or snowflake schema– Indexes required to identify relationships between fact table and dimensions
� At least the one considered the fact table is to be loaded into the Accelerator’s memory
� You can upload vertical partitions of a datamart (some columns of some tables in a star/snowflake schema) into the Accelerator
� You can upload several data marts into the Accelerator, as memory permits
� To turn OLAP query acceleration on, and accelerate subsequent queries submitted within the session whenever possible:
� Turn acceleration on: set environment use_dwa ‘1’;
� To debug (information is logged in online.log): set environment use_dwa ‘3’;
� Transparent to BI tools if using sysdbopen() to invoke the statement
© 2011 IBM Corporation34
Software Group – Information Management
Query Diagnostics (1)
� The data marts or subset of data marts loaded into the IWA are kept by Informix as Accelerated Query Tables (AQT)– AQT = A view definition that will allow the Informix Optimizer to recognize which queries could be accelerated via IWA
� Turning Acceleration ON:– SQL statement “set environment use_dwa ‘1’”
� Turning Acceleration ON with debugging in Online.log:– SQL statement “set environment use_dwa ‘3’”
� Turn Acceleration OFF:– SQL statement “set environment use_dwa ‘0’” (DEFAULT)
� The SQL explain output : New information – (IWA-Executed) if the query was successfully matched against an AQT and therefore offloaded to the accelerator
– (IDS-Executed) if the query did not match an AQT or could not be accelerated through IWA and was processed by the Informix Dynamic Server (IDS) alone
– (IWA-Avoid_Execute) if the query was invoked under “SET EXPLAIN ON AVOID_EXECUTE” for the plan to be generated by the Informix Optimizer without actually executing the query; and if it was executed, it would be offloaded to IWA. If (IDS-Avoid_Execute) is produced instead, the query was to be resolved by IDS only.
© 2011 IBM Corporation35
Software Group – Information Management
Query Diagnostics (2)
� SQL Tracing will generate additional information regarding query acceleration in the “onstat-g his” output. Its corresponding catalog table sysmaster:syssqltrace has these new columns:– sql_aqtinfo: a value > 0 is the time (in ms) spent in query matching; a value of 0 means the SQL statement did not qualify for offloading;
– and a value < 0 is the acceleration error code obtained– sql_aqt: contains the name of the AQT matching the query
© 2011 IBM Corporation36
Software Group – Information Management
Hardware de Referencia del Informix Warehouse
300 GB SAS hard disk drives each
6 disks
512 GBMemory
X7560 @ 2.27GH 4 X 8
This ex5 could be x3850
Intel(R) Xeon(R) CPU
- 16x 1.8" SAS SSDs with eXFlash or 8x 2.5" SAS HDDs
- Optional MAX5 32-DIMM memory expansion (max up 3TB –soon more)
- Scalable from 4 sockets and 64 DIMMs to 8 sockets and 128 DIMMs
- 8-core, 6-core and 4-core processor options with up to 2.26 GHz (8-core), 2.66 GHz
(six-core) and 1.86 GHz (four-core) speeds with up to 16 MB L3 cache
- 4-processor, 4U rack-optimized enterprise server with Intel® Xeon® processors
Opciones:
Informix Warehouse Accelerator System Requirements
https://www-304.ibm.com/support/docview.wss?rs=630&uid=swg27021332
© 2011 IBM Corporation37
Software Group – Information Management
Ambientes ideales para el Acelerador
Informix Ultimate Warehouse
< 5 TB data mart
HighConcurrency
Star schemaMixed Workloads
Informix IDS, XPS, Red Brick
MixedWorkloads
© 2011 IBM Corporation38
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation39
Software Group – Information Management
What our customers are saying….
"Before using Informix Warehouse Accelerator, complex inventory and sales analysis queries on the enterprise warehouse with more than a billion rows took anywhere from a few minutes to 45 minutes to run. When we ran those same queries using Informix Warehouse Accelerator, they finished in 2 to 4 seconds! That means they ran from 60 to 1400 times as quickly, with an average acceleration factor of more than 450— all without any index or cube building, query tuning or application changes!” – Large Shoe Retailer
"Informix Warehouse Accelerator is a game changer in the database market! It offers highly impressive performance with queries running 30 times faster than previously. The columnar technology saves a lot of processing time; it reduced our workload time from 9.5 hours to 15 minutes, all without any database tuning or need to manage the physical storage.“ – Lester Knutsen, President, Advanced DataTools Corporation
© 2011 IBM Corporation40
Software Group – Information Management
Customer Warehouse Scenarios and Performance Results
Federal Agency - GermanyFederal Agency - Germany Federal Agency - USA
Large Shoe Retailer
• 537 Queries executed over a 30 GB data mart•432 Queries accelerated via IWA•Performance with IDS+IWA was 90 times faster than XPS
• Sample data set from 2 TB warehouse•8 representative queries ran 127 times faster
• BI Queries on 150 GB data set•Fact table – 1 billion rows• Queries against Informix – 22 minutes•Queries against Informix + IWA : 4 secs, 330 times faster
• 10 GB data mart sample• Fact table – 25 million rows•Queries against Informix – over 20 mins•Queries against Informix + IWA: 4 secs, 300 times faster
Large Global Retailer
© 2011 IBM Corporation41
Software Group – Information Management
Case Study : Major U.S. Shoe Retailer
� Top 7 time-consuming queries in Retail BI and Warehouse:(Against 1 Billion rows Fact Table)
2 secs45 mins & up7
2 secs30 mins6
2 secs2 mins5
4 secs30 mins & up4
2 secs3 mins 40 secs3
2 secs1 min 3 secs2
4 secs22 mins1
IDS 11.7 IWAIDS 11.5Query
Our Retail users will be really happy to see such a huge improvement in the
queries processing timings.
This IWA extension to IDS will really bring value to the Retail BI environment.
© 2011 IBM Corporation42
Software Group – Information Management
Agenda
� Retos y Tendencias en la Industria de Data Warehouse
� Data Warehousing con Informix
� Informix Ultimate Warehouse Edition
� Informix Warehouse Accelerator
� Resultados en Rendimiento
� Información Adicional
� Q & A
© 2011 IBM Corporation43
Software Group – Information Management
Recursos Adicionales (1)
� Publications for IBM Informix Warehouse Accelerator
https://www-304.ibm.com/support/docview.wss?uid=swg27021031– Includes links to the manuals: (set up and admin guide - step by step)
• Quick Start Guide for Informix Warehouse Accelerator • Warehouse Accelerator Administration Guide
� IBM Informix Warehouse Accelerator - White Paper
http://www-01.ibm.com/software/sw-library/en_US/detail/K367396Y51842Y16.html
� Informix Warehouse Accelerator - Speed is everything
http://planetids.com/content/informix-warehouse-accelerator-speed-everything
� Informix Ultimate Warehouse Edition - Official page for this edition
� http://www-01.ibm.com/software/data/informix/ultimate-warehouse-edition/
� (German) Landing page for Informix at IBM Germany. With useful IWA information
� http://www-01.ibm.com/software/de/informix/
� Presentations at the International Informix Users Group (IIUG) Conference 2011 in Kansas (being posted under the Member area --it is free to be a member at iiug.org site):
� http://www.iiug.org
© 2011 IBM Corporation44
Software Group – Information Management
Recursos Adicionales (2)
Videos about Informix Warehouse Accelerator (based on Fred Ho's webcast / presentation):
� Informix Warehouse Accelerator
http://www.youtube.com/watch?v=ohyanfPucE4
http://www.youtube.com/watch?v=ohyanfPucE4&feature=related
� Part 1: Informix warehouse Overview.
http://www.youtube.com/watch?v=MILUTXHez-M&feature=related
� Part 2: Informix Warehouse - SQW tool
http://www.youtube.com/watch?v=R_BajAaxR2A&feature=related
� Part 3: Informix v11.70 warehouse features overview.
http://www.youtube.com/watch?v=Tqtn3Rq6G0U&feature=related
� Part 4: Informix warehouse accelerator. Overview and integration
http://www.youtube.com/watch?v=O6DzOfd7uC4&feature=related
� Part 5/5: Informix Warehouse Accelerator technology.
http://www.youtube.com/watch?v=7PXRKIkKkRU&feature=related
© 2011 IBM Corporation45
Software Group – Information Management
Recursos Adicionales (3)
� Fred Ho’s Webcast Replay available:
� An InformationWeek Webcast: Combining OLTP and Extreme Data Warehouse Performance, Duration: 60 Minutes
https://www.techwebonlineevents.com/ars/eventregistration.do?mode=eventreg&F=1002943&K=CAA1EC
� Links (at PlanetIDS site) to Set up tips for Informix Warehouse Accelerator:
http://planetids.com/– Informix Warehouse Accelerator - Using the Eclipse based Administration Tool– Informix Warehouse Accelerator - Configuration Tips - Part 5 - Size of /dev/shm– Informix Warehouse Accelerator - Configuration Tips - Part 4 - Understanding Linux Out of Memory– Informix Warehouse Accelerator - Configuration Tips - Part 3 - Linux Kernel Tuning– Informix Warehouse Accelerator - Configuration Tips - Part 2 - Memory Resources– nformix Warehouse Accelerator - Configuration Tips - Part 1 - CPU Resources
� Blogs:– Fred Ho's blog at IBM DeveloperWorkshttps://www.ibm.com/developerworks/mydeveloperworks/blogs/fredho66/?lang=en_us
– Keshav's blog at IBM DeveloperWorkshttps://www.ibm.com/developerworks/mydeveloperworks/blogs/Keshav/?lang=en