mysql no paypal tesla e uber
TRANSCRIPT
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
5 de Agosto de 2017, UNINOVE, São Paulo - SP
MySQL no Uber, Tesla e Paypal: arquiteturas de referênciaAirton Lastori
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
DBA ou SysAdmin
Dev ou
ArquitetoGerencial
Quem?
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
“The demand for database administrators and developers is much higher than the current supply, as well, leading to lots of open positions.”
https://stackoverflow.blog/2017/03/09/developer-hiring-trends-2017/
Oracle Confidential – Internal/Restricted/Highly Restricted 4
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Agenda
1. Paypal e MySQL NDB Cluster
2. Tesla e MySQL InnoDB Cluster
3. Uber e uso do MySQL como Document Store
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Visão GeralPayPal é a principal referência para pagamentos online. Oracle DB é usado no sistema core e MySQL para alguns serviços secundários.
AplicaçãoO PayPal desenvolveu uma arquitetura baseada em nuvemglobalmente distribuída utilizando MySQL Cluster para apoiar o sistema de detecção de fraudes. O sitema entrega 99,999% de disponibilidade, transacional, com consistência forte distribuídaem escala global < 1 Seg.
Por quê MySQL Cluster?“Você pode alcançar alta performance e disponibilidade semdesistir do modelo relacional e consistência em leitura.” --Daniel Austin, Chief Architect, PayPal
Caso de Sucesso: Paypal
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 7
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 8
Arquitetura MySQL (NDB) Cluster
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
HOST BHOST A
HOST DHOST C
HOST BHOST A
Oracle Confidential – Internal/Restricted/Highly Restricted 9
Deploys típicos
SQL
No
de
Dat
a N
od
eManagement 1
SQL
No
de
Dat
a N
od
eManagement 2
HOST COMPART. C
HOST COMPART. D
SQL
No
de
Dat
a N
od
e
Management 1
SQL
No
de
Dat
a N
od
e
Management 2
EscalabilidadeO número de SQL Nodes não precisa ser igual ao de Data NodesÉ comum encontrar Clusters com mais de 2 Data Nodes
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Escritas no MySQL Cluster – 2 réplicas
Management
1. INSERT...
2. Parse
3. NDB API CALL
4. WRITE (RAM)
5. NDB REPLICATE
6. WRITE (RAM)7. ACK
8. COMMIT OK
9. COMMIT OK
App
(~1s) FLUSH(~1s) FLUSH EscalabilidadeCom mais de 2 Data Nodes há auto-sharding
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Leituras no MySQL Cluster – melhor caso
Management
1. SELECT...
2. Parse
3. NDB API CALL
4. READ (RAM+DISK)
5. RECORD SET
6. SELECT output
App
EscalabilidadeNo pior caso, o Data Node delega a leitura para o nó que contém o dado
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 12
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 13
mysql.com/why-mysql/white-papers/guide-to-optimizing-performance-of-the-mysql-cluster
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 14
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 15
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Visão GeralPayPal é a principal referência para pagamentos online. Oracle é usado no sistema core e MySQL para alguns serviços secundários, inclusive detecção de fraudes.
AplicaçãoO PayPal desenvolveu uma arquitetura baseada em nuvemglobalmente distribuída utilizando MySQL Cluster para apoiar o sistema de detecção de fraudes. O sitema entrega 99,999% de disponibilidade, transacional, com consistência forte distribuídaem escala global < 1 Seg.
Por quê MySQL Cluster?“Você pode alcançar alta performance e disponibilidade semdesistir do modelo relacional e consistência em leitura.” --Daniel Austin, Chief Architect, PayPal
Caso de Sucesso: Paypal
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Visão GeralTesla é reconhecidamente a mais inovadora fabricante de automóveis no mundo.
AplicaçãoMySQL é usado no processo de manufatura dos carros, portanto é uma aplicação extremamentecrítica.
Por que MySQL InnoDB Cluster?O MySQL InnoDB Cluster é uma solução de HA simples de implementar e manter, construídasobre a sólida arquitetura de plugins do MySQL.
Caso de Sucesso: Tesla
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 18
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
MySQL InnoDB Cluster
Group Replication
Router Router
App App• Banco de dados replicado altamente
disponível e tolerante a falhas
• Permite topologias Multi-Primary (ativo-ativo) ou Single-Primary
• Arquitetura cloud-friendly, shared-nothing, sem storage compartilhado
• Automatiza failover e failback, reconfiguração e reconexão após falhas
• GA, pronto para produção desde a versão 5.7.17 (dez/16)
MySQL Shell
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Escritas no MySQL InnoDB Cluster
Router
App
MySQL Shell
1. INSERT...
2. Conexão Roteada
3.1 Certificação 3.2 Certificação 3.3 Certificação
5. COMMIT
6. BINLOG
4. Certificação OK = Consenso
~7 Replicação
7. COMMIT OK
Group Replication
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Leituras no MySQL InnoDB Cluster
Router
App
MySQL Shell
1. SELECT...
2. Conexão Roteada
3. SELECT(InnoDB)
4. SELECT output
Router
App
Group Replication
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
DemoAlta Disponibilidade com MySQL InnoDB Cluster
https://youtu.be/7xfAoejmxnI
Oracle Confidential – Internal/Restricted/Highly Restricted 23
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Visão GeralUber é a empresa pioneira da nova economia do compartilhamento, líder no mercado de mobilidade e transportes.
AplicaçãoMySQL é usado como principal backend paraarmazenamento de dados transacionais de viagens.
Por que MySQL?“No início de 2017, estávamos ficando sem espaço no banco de dados (...) não conseguíamos armazenar dados suficientes de viagem com o Postgres.” –Jakob Thomsem, Uber
Caso de Sucesso: Uber
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
“We had an unexpected loss of data on nearly every technology we used at one time or another, except MySQL.”
– Pinterest Engineering
Oracle Confidential – Internal/Restricted/Highly Restricted 25
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Usa MySQL como NoSQL
eng.uber.com/schemaless-part-one
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Usa MySQL como NoSQL
eng.uber.com/schemaless-part-one
DisclaimerUber informa que usa Cassandra e Riak em outros serviços.
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Trip record lifecycle
eng.uber.com/schemaless-part-one
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Trip record lifecycle
eng.uber.com/schemaless-part-one
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Schemaless
30
mysql> CREATE DATABASE shard_0001;
mysql> USE shard_0001;
mysql> CREATE TABLE entity (
added_id int NOT NULL AUTO_INCREMENT,
row_key binary(16) NOT NULL,
column_name varchar(20) NOT NULL,
ref_key tinyint UNSIGNED NOT NULL,
body json NOT NULL,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (added_id) );
Adaptado de: eng.uber.com/schemaless-part-two
Estrutura flexível e denormalizadaque privilegia inserção (append-only).
row_key é um UUID e junto com column_name e ref_key identificam unicamente uma “célula” em todo dataset (vai permitir sharding).
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Indexação Simples
31
mysql> ALTER TABLE entity
ADD UNIQUE INDEX cell_index (
row_key,
column_name,
ref_key
);
Usar AUTO_INCREMENT permite que o MySQL escreva “células” (linhas) de forma linear no disco.
Torna-se também um ponteiro único para cada linha (dentro da B-Tree), usado para buscar o conteúdo de maneira eficiente na ordem que foram inseridas no tempo (no shard).
Este índice composto permite check constraint e uma busca eficiente de uma “célula” através dos seus identificadores únicos.
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
CREATEmysql> INSERT INTO entity
(added_id, row_key, column_name, ref_key, body) VALUES (
NULL,
unhex(replace(uuid(),'-','')),
'BASE',
1,
'{
"rider_uuid" : "3 618cb03b-4032-11e7-a6b9-0800277ff81b",
"driver_partner_uuid" : "3cdbb224-402e-11e7-a6b9-0800277ff81b",
"city_uuid" : "45c5714c-402e-11e7-a6b9-0800277ff81b"
}');
Query OK, 1 row affected (0.01 sec)
32
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
READmysql> SELECT * FROM product_info_hybrid \G
*************************** 1. row ***************************
added_id: 1
row_key: ▒▒▒▒@2
column_name: BASE
ref_key: 1
body: {"city_uuid": "45c5714c-402e-11e7-a6b9-0800277ff81b",
"rider_uuid": "3 618cb03b-4032-11e7-a6b9-0800277ff81b",
"driver_partner_uuid": "3cdbb224-402e-11e7-a6b9-0800277ff81b"}
created_at: 2017-05-24 4:14:19
1 row in set (0.00 sec)
33
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Generated Columnsmysql> ALTER TABLE entity ADD
row_key_uuid varchar(36) GENERATED ALWAYS AS
(insert(
insert(
insert(
insert(
hex(concat(substr(id_bin,5,4),substr(id_bin,3,2),
substr(id_bin,1,2),substr(id_bin,9,8))),
9,0,'-'),
14,0,'-'),
19,0,'-'),
24,0,'-‘)) VIRTUAL;
34
Mais info: mysqlserverteam.com/storing-uuid-values-in-mysql-tables
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Generated Columnsmysql> SELECT added_id, row_key_uuid, column_name, ref_key, body,
created_at FROM entity \G
*************************** 1. row ***************************
added_id: 1
row_key_uuid: 403211E7-F1CF-DDEC-A6B9-0800277FF81B
column_name: BASE
ref_key: 1
body: {"city_uuid": "45c5714c-402e-11e7-a6b9-0800277ff81b",
"rider_uuid": "3 618cb03b-4032-11e7-a6b9-0800277ff81b",
"driver_partner_uuid": "3cdbb224-402e-11e7-a6b9-0800277ff81b"}
created_at: 2017-05-24 4:14:19
1 row in set (0.00 sec)
35
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Indexação do JSON
36
Encontrar todas viagens de um motorista em uma cidade num intervalo específico.
mysql> ALTER TABLE entity ADD
driver_partner varchar(36) GENERATED ALWAYS AS
(body->>"$.driver_partner_uuid") VIRTUAL;
mysql> ALTER TABLE entity ADD
city varchar(36) GENERATED ALWAYS AS (body->>"$.city_uuid") VIRTUAL;
mysql> ALTER TABLE entity
ADD INDEX driver_city_index (
driver_partner (18),
city (18),
created_at );Adaptado de: eng.uber.com/schemaless-part-one
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Scale out via Sharding
eng.uber.com/schemaless-part-two
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Write scalability for HA
eng.uber.com/schemaless-part-two
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 39
Read Scale-Out
Async Replication + Auto Failover
Write Scale-Out
Sharding
S1
S2
S3
S4
MySQL Roadmap
Timeline
MySQL Document Store
Relational & Document Model
MySQL HA
Out-Of-Box HA
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 40
Confiabilidade e Flexibilidade
MySQL 5.7
Document Store
Suporte JSON
Nova API
BDs RelacionaisTecnologia madura,
comprovadamente segura.Transações, queries, JOINs
complexos e conjunto extenso de ferrametnas
operacionais
NoSQLFlexibilidade. Escalabilidade
horizontal e facilidade de uso, schemaless, document
store (JSON)
Aplicações ModernasAgile + DevOps com proteção robusta dos dados
Banco de Dados HíbridoMelhor dos mundos, semtrade-offs. Propriedades ACID & confiabilidade de SGBDR + flexibilidade de document store
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
JSON improvements MySQL 8.0
• MySQL Document Store
– Aggretation Functions
– Pretty print Functions
– Partial document operations
– Performance improvements
• Administer MySQL via the MySQL Shell
–One stop DevOps tool for MySQL (incl. HA)
– Use your preferred language: SQL, JavaScript, Python, …
– Relational or Document
42
Feature Requestfrom Developers
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
UUID and Bit-wise Improvements MySQL 8.0
• Functions to convert UUID to and from binary:
– UUID_TO_BIN()
– BIN_TO_UUID()
– plus IS_UUID()
• Bit-wise operations on binary data types
• Bit-wise operations on binary data types
– Designed with IPv6 in mind:
– INET6_ATON(address) & INET6_ATON(network)
43
Feature Requestfrom Developers
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
UUID_TO_BIN Optimization MySQL 8.0
44
24,75 25,5 26,25 27, 27,75 28,5 29,25
Insert Performance OptimizedOriginal
• Binary format is now smaller and insert-order efficient:
11e678fe53303f87a4778c89a52c4f3b
53303f87-78fe-11e6-a477-8c89a52c4f3bFrom VARCHAR(36)
To VARBINARY(16)
Feature Requestfrom Developers
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Conclusão
Oracle Confidential – Internal/Restricted/Highly Restricted 45
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Resumo das opções para MySQL
Replication Group ReplicationCluster Carrier Grade
Edition
Velocidade de failover Média Alta Alta
Consistência de Leitura Fraca Fraca Forte
Escalabilidade (nativa) Leitura Leitura Leitura+Escrita
Sem perda de dados durante falha
Config. Sim Sim
Facilidade de migração a partir de InnoDB stand-alone
Média Fácil Média
Limite de armazenamento 64TB+ 64TB+ ~3TB
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Próximo passos – conte conosco
• Teste o MySQL (NDB) Cluster– Imagem Docker: hub.docker.com/r/mysql/mysql-cluster
– Whitepaper: mysql.com/why-mysql/white-papers/mysql-cluster-evaluation-guide
• Teste o MySQL InnoDB Cluster– Imagem Docker: https://github.com/mattlord/Docker-InnoDB-Cluster
– Manual: dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-userguide.html
• MySQL Shell 1.0.9, MySQL Router 2.1.3, MySQL Server 5.7.17 (ou superiores)
• Teste o MySQL como Document Store– Imagem Docker: https://hub.docker.com/r/mysql/mysql-cluster
– Quick start guide: https://dev.mysql.com/doc/refman/5.7/en/document-store.html
– X-Dev API: https://dev.mysql.com/doc/x-devapi-userguide/en
– MEAN Stack (“M” de MySQL): insidemysql.com/develop-by-example-document-store-working-with-express-js-angularjs-and-node-js
Oracle Confidential – Internal/Restricted/Highly Restricted 47
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 49
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 51cloud.oracle.com/mysql
Oracle Confidential – Internal/Restricted/Highly Restricted53