$30 off During Our Annual Pro Sale. View Details »

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

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

Tech-Verse2022
PRO

November 17, 2022
Tweet

More Decks by Tech-Verse2022

Other Decks in Technology

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")
  10. None
  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)