Análise de bancos de dados analíticos open source

6 de Outubro de 2016

Matheus Espanhol

Agenda● Cenário Movile

● Técnicas para otimização de bancos de dados analíticos

● Paralelismo do PostgreSQL 9.6

● Citusdb e Postgres-XL

● Arquitetura do Citusdb

● Citusdb na Movile

● Conclusões e expectativas

A Movile● A Movile é player global no Mercado de conteúdo e comércio via smartphones● Deixamos a vida das pessoas melhor por meio dos nossos aplicativos ● Buscamos incansavelmente fazer as coisas melhor, mais rápidas e mais baratas

○ 50 milhões de pessoas utilizam os serviços da Movile todo mês○ Estamos conectados a mais de 70 operadoras em toda América○ Crescimento anual médio de 80%, nos últimos 7 anos e espera crescer 50% ao ano, até 2020

Messaging Payments

Cenário - Data Intelligence● Cenário enfrentado:

○ Típico 1 sistema, 1 tabela = ~200GB/mês

○ Sistema orientado a eventos gerando 140MM/dia de registros proveniente de Smartphones

○ Controle de SMS de plataformas gerando 200MM/dia de registros

○ Tratando histórico de mais de 400MM de clientes que passaram por nossas plataformas

○ Tamanho de registros variando de 2Kb a 8Kb

○ Integrador de dados de todas as plataformas Movile e das empresas do grupo

● Banco de dados na nuvem○ Apenas últimos 3 meses

○ Muitos usuários concorrentes

○ Unidades de negócio, níveis de uso e níveis de criticidade distintas

Cenário - Data Intelligence● Necessidade de realocação de espaço constante

● Custos elevados para aumento de storage na nuvem

● Máquinas locais de histórico não comportam mais que 2 anos de dados

● Índices ocupam bastante espaço○ Consultas nem sempre utilizam campos indexados

● Falta de espaço em disco para restaurar dados históricos

● Gargalo de I/O

● CPU subutilizado

➢ Compressão de dados

➢ Armazenamento por coluna

➢ Bancos de dados de processamento

paralelo (MPP)

Cenário - Data Intelligence

AlternativasOpen source

Citusdb(pg_shard + cstore_fdw)

Postgres-XL

Appliance

NetezzaGreenplum

Teradata

Cloud

Google BigQuery

Amazon Redshift

Processamento Paralelo● Múltiplos CPUs em uma única máquina

○ Conjuntos de dados distintos

○ Possível gargalo de memória e disco

○ PostgreSQL 9.6

● MPP (Massive Parallel Processing)○ Cluster de banco de dados

○ Dados distribuídos entre os nós do cluster

○ Cluster shared nothing

■ Cada nó possui os próprios recursos de memória e disco

Processamento Paralelo - PostgreSQL 9.6COPY mte_201607 FROM PROGRAM 'zcat /data/csv/mte/2016/07/mte_*_part_00.gz' CSV DELIMITER '|';

● Quantidade de registros: 184 milhões● Quantidade de colunas: 53● Tamanho da tabela: 176 GB● Tempo de carga: 10 horas 12min

SET max_parallel_workers_per_gather TO 6;

SELECT inserted_time,sms_text FROM mte_201607 WHERE uuid = '3CA81380-B06D-11E5-8C95-7610042D67C6';

Time: 433053.770 ms = 7min

Armazenamento colunar● Análise de dados geralmente é aplicada a algumas colunas

● Redução do tamanho das leituras em disco

● Menor uso de buffer cache

● Self-indexing

○ Redução do uso de espaço em disco com índices

● Maior vantagem para compressão de dados

Compressão de dados● Maior capacidade de armazenamento

● Redução do tamanho das leituras em disco

● Agregações em dados compactados

● Impacto no processamento

○ Bancos de dados analíticos geralmente possuem gargalo de disco

○ Poucos usuários

○ Baixa concorrência

Postgres-XLPrós

● Tempo de resposta de consultas● Modo de replicação e distribuição

Contras

● ACID (GTM)● Alta disponibilidade● Escalabilidade● Instalação complexa

CitusdbPrós

● Extensão do PostgreSQL ● MPP● Colunar● Compressão de dados● Sharding e replicação lógica

Contras

● Comunidade de usuários○ Código aberto recentemente

Citusdb - Arquitetura

Citusdb - Arquitetura

Citusdb - Performance● Real-time executor

○ Consulta roda com o máximo de recursos○ 1 conexão por shard○ Ex: consulta simples sem JOINs entre shards ou com poucos shards

● Task Tracker executor○ Ideal para consultas grandes e complexas○ 1 conexão por worker○ Maior controle do paralelismo○ Ex: consulta complexa que cruza dados entre os shards e com muitos shards

Citusdb - Métodos de distribuição● Hash

○ Ideal para INSERTs em tempo real○ Dados não ordenados e que sofrem alterações○ Mais eficiente para consultas com operações de igualdade

● Append○ Ideal para dados históricos inseridos através de rotinas de ETL○ Ordenados por tempo e não sofrem alterações○ Mais eficiente para consultas com filtro por intervalo de tempo

Instalação - 1 master e 2 workersyum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpmyum install citus_95

service postgresql-9.5 initdbecho "shared_preload_libraries = 'citus'" >> /citusdb/master/postgresql.conf

initdb -D /data/citusdb/worker1sed -i 's/#port = 5432/port = 5433/g' /data/citusdb/worker1/postgresql.confecho "shared_preload_libraries = 'citus'" >> /data/citusdb/worker1/postgresql.conf

initdb -D /data/citusdb/worker2sed -i 's/#port = 5432/port = 5434/g' /data/citusdb/worker2/postgresql.confecho "shared_preload_libraries = 'citus'" >> /data/citusdb/worker2/postgresql.conf

echo "localhost 5433localhost 5434" > /citusdb/master/pg_worker_list.conf

pg_ctl -D /citusdb/master startpg_ctl -D /data/citusdb/worker1 startpg_ctl -D /data/citusdb/worker2 start

Instalação - 1 master e 2 workersfor port in $(seq 5432 5434)do

createdb -p $port atlaspsql atlas -p $port -c "CREATE EXTENSION citus"

done

psql atlas -c "SELECT * FROM master_get_active_worker_nodes();"

node_name | node_port -----------+----------- localhost | 5433 localhost | 5434

Carga de dadosCREATE TABLE IF NOT EXISTS mte_201607 (id bigint NOT NULL,inserted_time timestamp NOT NULL,phone bigint NOT NULL,sms_text text,….);

SET citus.shard_replication_factor TO 1;SET citus.shard_max_size TO '1GB';

SELECT master_create_distributed_table('mte_201607', 'inserted_time', 'append');

COPY mte_201607 FROM PROGRAM 'zcat /data/csv/mte/2016/07/mte_*_part_00.gz' CSV DELIMITER '|';

MetadadosSELECT logicalrelid::regclass,

CASE partmethod WHEN 'a' THEN 'APPEND' ELSE 'HASH' END AS partmethod FROM pg_dist_partition;

logicalrelid | partmethod ---------------------+------------ mte_201601 | APPEND mte_201602 | APPEND mte_201603 | APPEND mte_201604 | APPEND mte_201605 | APPEND mte_201606 | APPEND mte_201607 | APPEND mte_201608 | APPEND mte_201609 | APPEND mte_201610 | APPEND

MetadadosSELECTlogicalrelid::regclass, shardid,CASE shardstorage

WHEN 't' THEN 'TABLE'WHEN 'c' THEN 'COLUNAR'WHEN 'f' THEN 'FOREIGN'

END AS shardstorage,shardminvalue,shardmaxvalueFROM pg_dist_shardWHERE logicalrelid::regclass::text = 'mte_201607'ORDER BY shardid;

logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue --------------+---------+--------------+---------------------+------------------------- mte_201607 | 102350 | TABLE | 2016-07-01 00:00:00 | 2016-07-01 23:59:59 mte_201607 | 102352 | TABLE | 2016-07-02 00:00:00 | 2016-07-02 23:59:59 mte_201607 | 102354 | TABLE | 2016-07-03 00:00:00 | 2016-07-03 23:59:59 mte_201607 | 102356 | TABLE | 2016-07-04 00:00:00 | 2016-07-04 23:59:59 mte_201607 | 102358 | TABLE | 2016-07-05 00:00:00 | 2016-07-05 23:59:59 mte_201607 | 102360 | TABLE | 2016-07-06 00:00:00 | 2016-07-06 23:59:59

MetadadosSELECT pg_dist_shard.logicalrelid::regclass,

pg_dist_shard.shardid, CASE shardstate

WHEN '1' THEN 'FINALIZED' WHEN '3' THEN 'INACTIVE' WHEN '4' THEN 'TO_DELETE'

END AS shardstate, pg_size_pretty(shardlength), nodename,nodeport

FROM pg_dist_shard_placement JOIN pg_dist_shard USING (shardid) WHERE logicalrelid::regclass::text = 'mte_201607'ORDER BY pg_dist_shard.shardid;

logicalrelid | shardid | shardstate | pg_size_pretty | nodename | nodeport --------------+---------+------------+----------------+-----------+---------- mte_201607 | 102350 | FINALIZED | 288 MB | localhost | 5434 mte_201607 | 102352 | FINALIZED | 281 MB | localhost | 5434 mte_201607 | 102354 | FINALIZED | 284 MB | localhost | 5434 mte_201607 | 102356 | FINALIZED | 289 MB | localhost | 5434

cstore_fdw● Foreign Data Wrapper para armazenamento colunar no PostgreSQL

○ Mais poderosa ainda com Citus

● Permite compressão de dados○ Compressão LZ disponível○ Extensível para outros algoritmos de compressão○ Suporta mais de 40 tipos de dados do PostgreSQL

● Arquivos criados no sistema operacional ○ Formato baseado no ORC (Optimized Row Columnar)

● Skip Indexes○ Estatísticas por blocos (MIN/MAX de cada bloco)○ Seleciona apenas blocos relevantes

Instalação e configuraçãoecho "shared_preload_libraries = 'cstore_fdw'" >> /citusdb/master/postgresql.confecho "shared_preload_libraries = 'cstore_fdw'" >> /data/citusdb/worker1/postgresql.confecho "shared_preload_libraries = 'cstore_fdw'" >> /data/citusdb/worker2/postgresql.conf

for port in $(seq 5432 5434)do

psql atlas -p $port -c "CREATE EXTENSION cstore_fdw"done

-- Master node

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

CREATE FOREIGN TABLE IF NOT EXISTS mte_201607 (id bigint NOT NULL,inserted_time timestamp NOT NULL,phone bigint NOT NULL,sms_text text,...) SERVER cstore_server;

Carga de dadosSET citus.shard_replication_factor TO 1;SET citus.shard_max_size TO '1GB';

SELECT master_create_distributed_table('mte_201607', 'inserted_time', 'append');

COPY mte_201607 FROM PROGRAM 'zcat /data/csv/mte/2016/07/mte_*_part_00.gz' CSV DELIMITER '|';

ls -lhtr /data/citusdb/worker1/cstore_fdw/16384/ | headtotal 291G-rw------- 1 postgres postgres 191M Sep 24 02:37 16675-rw------- 1 postgres postgres 474 Sep 24 02:37 16675.footer-rw------- 1 postgres postgres 191M Sep 24 02:38 16679-rw------- 1 postgres postgres 474 Sep 24 02:38 16679.footer-rw------- 1 postgres postgres 193M Sep 24 02:40 16683

Carga de dados - com compressãoCREATE FOREIGN TABLE IF NOT EXISTS mte_201607 (id bigint NOT NULL,inserted_time timestamp NOT NULL,phone bigint NOT NULL,sms_text text,...) SERVER cstore_server OPTIONS(compression 'pglz');

SET citus.shard_replication_factor TO 1;SET citus.shard_max_size TO '1GB';

SELECT master_create_distributed_table('mte_201607', 'inserted_time', 'append');

COPY mte_201607 FROM PROGRAM 'zcat /data/csv/mte/2016/07/mte_*_part_00.gz' CSV DELIMITER '|';

Comparativo

Postgres 9.6 Citusdb Citusdb + Cstore Citusdb + Cstore Compactado

Quantidade de registros 184 milhões 184 milhões 184 milhões 184 milhões

Quantidade de colunas 53 53 53 53

Tamanho da tabela 176 GB 176 GB 176 GB 119 GB

Tempo de carga 10 horas 5 hrs 10min 5hrs 15min 4hrs 40min

Tempo de consulta simples 7min 8min 3min 1.7min

Tuning Citusdb - Alguns parâmetros ● SET citus.max_running_tasks_per_node TO 6;

○ Número máximo de processos em paralelo em cada worker

● SET citus.shard_replication_factor TO 1;

○ Número de worker nodes que armazenará cada shard

● SET citus.shard_max_size TO '1GB';

○ Tamanho máximo de um shard

● SET citus.task_executor_type TO 'task-tracker';

○ Muda a sessão para task-tracker ou real-time

● SET citus.enable_ddl_propagation TO true;

○ Propagação automática de DDL

○ Habilitado por padrão

● SET citus.explain_all_tasks = 1;

○ Habilita EXPLAIN de todas as tasks paralelas

○ Útil para identificar nodes com planos de execução diferentes

Tuning cstore_fdw ● SERVER cstore_server OPTIONS(compression 'pglz',block_row_count '1000000');

○ Número de linhas por bloco

○ Padrão: 10000

○ Valor maior reduz leituras no disco

○ Mas reduz também o número de registros eliminados pela funcionalidade “Skip Indexes”

Conclusão● Postgres-XL é (até o momento) uma excelente alternativa para escalabilidade de

escrita para ambientes OLTP

● Citusdb é a alternativa open source que possui os principais recursos necessários

para ambientes analíticos

○ Concorre com soluções proprietárias de custo elevado

○ Permite a utilização de funcionalidades do Postgres

● cstore_fdw possibilitou a análise de dados históricos em infraestrutura própria

● Redução de custos mesmo se contratado suporte Citusdata

Futuro● Realizar estudo para comparar custos entre cluster Citusdb em datacenters e Cloud

● Carregar dados real-time

● Escalar horizontalmente a camada de persistência de grandes sistemas

● Reduzir custos com Redshift e Bigquery

Contato

twitter.com/matheusespanhol

linkedin.com/in/matheusespanhol

slideshare.net/matheus.espanhol

matheusespanhol@gmail.com