Issues and Future Prospects for the MySQL Environment Managed by Yahoo! JAPAN and LINE

Tsubasa Tanaka (yoku0825) (LINE / MySQL1 Team / DBA for MySQL)
Makoto Sato (Yahoo! JAPAN / Service Platform, System Management Group,Technology Group / DataBase Engineer)

https://tech-verse.me/ja/sessions/292
https://tech-verse.me/en/sessions/292
https://tech-verse.me/ko/sessions/292

Transcript

1. Issues and Future Prospects for the MySQL Environment Managed by Yahoo! JAPAN and LINE Makoto Sato / Yahoo! JAPAN Tsubasa Tanaka (yoku0825) / LINE
2. 自己紹介 and 自社環境の紹介 Introduce us and our environment
3. • Makoto Sato • Yahoo! JAPAN Corp. Database1, Service Platform DBA ,DBRE
4. • Tsubasa Tanaka (a.k.a. yoku0825) • LINE Corp. Database Department MySQL1Team Senior DBA for MySQL
5. Yahoo! JAPAN • MySQL DBaaS • HA:Percona XtraDB Cluster(PXC) • Openstack / Trove Private Cloud
6. • DBS for MySQL (on our Private Cloud environment named "Verda") • HA based Semisync-Replication named "GlenHA" • Support only Verda VMs (Not support Physical Machines)
7. DBaaS以外の選択肢 When your DBaaS couldn't sutisfy the requirement, how to support them?
8. Redis Cache use? Yes No Relational? No Yes QPS within 1000 Capacity less than 1T No Cassandra MySQL DBaaS Yes QPS within 10000 Capacity less than 5T Yes MySQL V2 No Oracle Database Selection on Yahoo! JAPAN Yahoo! JAPAN
9. • We provides calculator flavor which you should choose from past(or estimated) resource usage •CPU usage •Disk usage •And so on.. • If required spec is over the VM flavors, we prepares Physical Machine and Ancient HA tool (called as "Legacy")
11. 我々のDBaaSはここがすごい Good point in our environment
12. Multi-master - You can write data anywhere Multi-location - Replication between 3DCs, high availability Availability - Non stop maintenance Read Write MySQL DBaaS BCP on Yahoo! JAPAN PXC Read Write Read Write DataCenter3 Node1 Node3 Node2 DataCenter1 DataCenter2 Yahoo! JAPAN
13. Primary Standby Read+Write from Apps Primary Standby Read Read Write from Apps Read from Apps Async Replication based HA topology (Minimum: 2nodes, At least 2nodes to Read endpoint
14. Primary Standby Read+Write from Apps Primary Standby Read Read Write from Apps Read from Apps - Standby node does not receive any Apps traffic. - When Primary downs, the server which has nearest replication position to Primary will be promoted(not only Standby, but also Read Replicas)
15. DBS for MySQL is dog foodning in Verda features. • VM Compute (to avoid split-brain and auto- healing) • LoadBarancer (to provide read-endpoint for App, draining Replica which should be removed) • VKS (to work GlenHA) • VSFS (to backup volume)
16. 我々のDBaaSの弱点 Pain point in our environment
17. Yahoo! JAPAN • Separate DB team and Cloud development team • Monitoring PFs are scattered, making it difficult to track failures • (ex. logging=Splunk metrics=Grafana observation=Sensu Go
18. Yahoo! JAPAN • DBaaS cannot be used on a large scale (Resolved. MySQL V2 is now an option) • DDL operation was very complicated, and many accidents occurred
19. Our maximum "operation unit" is "One Cluster of Replication" (Primary + Standby, Primary + Standby + N * Read Replicas) • Adding new User or editing hosts connected from • Create new schema • Making "Proof of changes" by Workflow... Please imagine, if your service has 32 or more shards(= Cluster of Replication) ..
20. Hard to customize each services • Account privileges are limited by GlenHA's restricts. •This means App-side developers can't use global privileges (ex. Process_priv, Select_priv on performance_schema) • All monitoring systems are shared. •mysqld_exporter + Grafana + Alertmanager + self-developed notification system •Once you need to write new monitoring something, you face the Accounts restriction...
21. 我々のDBaaSが次に目指す所 Future in our environment
22. Multi-master Multi-location Availability ＋Flexibility - Add MasterNode - Add Replica(semi-sync,Async) Read Write Next Generation MySQL DBaaS on Yahoo! JAPAN MySQL GR Read Write Read Write DataCenter3 Node1 Node3A Node2A DataCenter1 DataCenter2 Node2B Asyinc Replica Node3B Node2 Rep Node3 Rep Semi-syinc Replica Yahoo! JAPAN
23. New "operation unit" called "Cluster" ( N * "One-Replication-Cluster"s) Primary Standby Primary Standby Primary Standby You can request only once to make Workflow or edit user!!
24. Providing DBA-tools by Web Apps • myStatugo(Open Source + Custom) + gotty • safeddl (pt-online-schema-change + gh-ost + Native ALTER TABLE wrapper) • MUH (MyDumper + Replication Constructor + Data comparison + DNS Switchover)