Migration Journey To TiDB - Kabilesh PR - Mydbops MyWebinar 38

Migration Journey To TiDB - Kabilesh PR - Mydbops MyWebinar 38

Youtube video link: https://youtu.be/_WgXm1Ykj8c

What You Will Learn
* Data Migration Strategies – Understand the best approaches for transferring data to TiDB with minimal disruption.
* Seamless Replication – Learn how to maintain data consistency and minimize downtime during the migration process.
* Schema Design Adjustments – Explore the key schema design adjustments necessary for optimal TiDB performance.
* Challenges & Solutions – Gain practical insights into tackling common migration challenges to ensure a smooth transition.

This webinar is ideal for database administrators, data engineers, system architects, and anyone involved in database management and migrations. Whether you are considering TiDB as a new solution or already exploring it, this session will equip you with valuable knowledge to streamline your migration journey.

#Webinar #TiDB #MyWebinar #Mydbops #DatabaseManagement #migration #DevOps #TechWebinar #database #dbms #dba #distributedsql #sql #HTAP

Mydbops Managed Services specializes in taking the pain out of database management while optimizing performance. Since 2015, we have been providing top-notch support and assistance for the top three open-source databases: MySQL, MongoDB, PostgreSQL and TiDB.

Our team offers a wide range of services, including assistance, support, consulting, 24/7 operations, and expertise in all relevant technologies. We help organizations improve their database's performance, scalability, efficiency, and availability.

Contact us: [email protected]

Visit: https://www.mydbops.com/

1. Migration Journey To TiDB

Empowering Scalability, Performance & Simplicity
Kabilesh PR
Founding Partner, Mydbops LLP
38
th
MyWebinar - Mydbops

2. About Me
Kabilesh PR
❏Interested in Open Source DB technologies
❏Keen Interest in MySQL, TiDB & Distributed SQL’s
❏Active Tech Speaker/Blogger
❏Pingcap Certified TiDB Professional
❏AWS Database Speciality
❏Founding Partner, Mydbops

3. Focus on MySQL, MongoDB, PostgreSQL, TiDB, Cassandra
Consulting
Services
Consulting
Services
Managed
Services
24*7
DBA Team
Targeted
Engagement
Mydbops Services

4. ❏Introduction

❏Why Migration ?

❏Migration Methods

❏Challenges & Solutions

Agenda

5. Introduction

6. TiDB is an Open Source , Distributed HTAP database compatible with MySQL Protocol.
Introduction
2

7. Understanding TiDB Architecture

9. MySQL compatible, the TiDB SQL Layer
separates compute from storage to make
scaling simpler,

10. The Placement Driver functions as a
orchestrator. Responsible for TSO,
scheduling, shard maintenance,
metadata and much more

11. Tikv is ROW based, Transactional
storage, Offers high-availability, strong
consistency that can auto-scale to
hundreds of node with petabyte data
scale

12. Why Migration ?

13. MySQL limitations

are usually experienced with growth in business!!

14. Scalability High Availability
Real-Time Analytics

15. Advantages of TiDB
Open Source
No Vendor lock-in with a
database that’s 100% open source.
Horizontal Scaling
Grants total transparency into data workloads without
manual sharding.

Horizontal Scaling
Grants total transparency into
data workloads with automatic sharding.
High Availability
Guarantees auto-failover and self-healing for
continuous data access.
MySQL Compatibility
Enjoy the most MySQL compatible
distributed SQL database on the planet.

Multi-Cloud
Deploy database clusters
anywhere in the world.

Mixed Workloads
Streamlined tech stack makes it
easier to produce real-time analytics.
Robust Security
Protect data with enterprise-grade
encryption both in-flight and at-rest.

16. Global client-Base TiDB

17. Journey of Migration

19. Assessment

20. ❏Primary key for all tables
❏Foreign-keys (experimental)
❏Serializable isolation
❏Triggers
❏Auto-increment
❏Full-text index
❏Triggers
❏Stored procs
❏User Defined functions

21. Migration Process

22. ❏Migration tool provided by TiDB which directly integrates with MySQL.
❏Its highly Available, can handle data from multiple sources.

DM - Data Migration

24. Aspect Physical Import Mode Logical Import Mode
Backend Local TiBD
Import Speed
Fast (approximately 100–500
GiB/hour)
Slower (approximately 10–50
GiB/hour)
Resource usage High Low
Network Usage High Low
ACID Compliance No Yes
Target Tables Must be Empty Can contain existing data
TiDB Cluster VersionRequires version 4.0.0 or later Compatible with all versions
Service Availability Limited service availability Full service availability

25. Task mode Description Use Case
Full Migrates the entire existing data from
the source database to TiDB.
Ideal for initial data migration when
setting up TiDB with existing datasets.
Incremental

Continuously replicates new changes
(binlog events) from the source to TiDB.
Suitable for keeping TiDB synchronized
with the source database in real-time.
All Combines both full data migration and
incremental replication.
Best for scenarios requiring a complete
data transfer followed by ongoing
synchronization

26. Validation

27. Data Validation

❏Dm supports validating data between source and
destination via checksum
❏Sync-diff-inspector compares dataset for inconsistencies
❏Manual validation.
❏Data validation time varies based on dataset size.

28. ❏Functional: Confirm that queries return identical result in both DB
❏Performance: compare query execution time to capture any
regressions
❏Read traffic: Transfer some live read traffic with proxysql or dual
reads from application
Query Validation

29. Switch over

31. Rollback

33. Challenges & Solutions

34. ❏Auto-increment: ID is batched by default per TiDB.
Solution : Enable auto_id_cache per table while Table creation to
have MySQL compatibility.
Bug : https://github.com/pingcap/tidb/issues/56814

35. Work around:
Alter table messages FORCE AUTO_INCREMENT=1304776036;

Wrote python script to fix the discrepant records
Thanks !! Max Liu, CEO and Founder at Pingcap. The bug was located and fixed in 3
days and patch was released

36. ❏Query Regression: Some of the queries had regression in terms of
exec time.
Solution : Queries we rewritten, ie., changing the joining
condition, where clause
❏Descending Index: Not supported.
Solution : Queries needs to be rewritten with range and sorted.
❏Full text index : Not Supported.
Solution : Integrate with external services like sphinx, elastic
search, Apache solr etc.

37. Case Study

38. Mydbops Empowers
Yulu's Urban Mobility with Scalable TiDB

39. Yulu's Challenges
Yulu's data size exceeded 18
TB, causing storage issues and
high costs.
Unmanageable
Data Growth
MySQL writes were
bottlenecked, impacting
application performance.
Performance
Bottlenecks
Large dataset size led
to slow DDL
operations.
Slow DDL
Operations

40. Single MySQL master resulted in
downtime during maintenance.
Limited Availability
Vertical scaling with cloud instances
increased database costs
Costly
Scaling
Yulu's Challenges

41. •Horizontal scaling for growing datasets
without performance impact.
•Automatic data sharding for efficient
workload distribution.
Scalability
How TiDB Benefits Yulu
•Distributed architecture for high
performance and low latency.
•Optimized storage engine for efficient
write handling.
Performance
•Resource efficiency reduces the need for
expensive hardware upgrades.
•Data compression from 18TB to 3.4TB for
significant storage cost savings.
Cost-Effectiveness
•High availability with automatic failover
ensures continuous operation.
•Strong data consistency guarantees data
accuracy and integrity.
Reliability

42. Percentage Reduction in
Storage Size - 72%
Benefits

43. Deployment and
configuration of TiDB
based on workload
requirements.
TiDB Services Offered By Mydbops
Data migration
assessments to ensure
seamless transition to
TiDB.
Live data
synchronization from
MySQL to TiDB for
continuous operations.
Best practices guidance
to optimize TiDB
performance and
efficiency.

44. Any Questions?

45. Consulting
Services
Consulting
Services
Connect with us !
[email protected] [phone] www.mydbops.com

46. Thank You