Downloaded 10 times
![Matching Expressions • Classify: Identify servers, sessions, or routers with logical conditions • Compose: Use variables, operators, and values to create matches • Chain: Link conditions with AND, OR, or NOT for flexibility (<function> | <variable>) [<operator> <value>] [<logical-operator> <expression>]* 16](https://image.slidesharecdn.com/mysqlroutingguidelinesfosdem25-250204160547-45fa96dd/75/Routing-Guidelines-Unlocking-Smarter-Query-Routing-in-MySQL-Architectures-16-2048.jpg)
![Predefined Variables $.session.* VA R I A B L E T Y P E E X A M P L E $.session.targetIp String "192.168.1.235" $.session.targetPort Integer 6446 $.session.sourceIp String "10.1.103.12" $.session.user String "admin" $.session.connectAttrs String (key-value) ._os = 'Linux' $.session.schema String "world" $.session.randomValue Double [0, 1] 0.3 18](https://image.slidesharecdn.com/mysqlroutingguidelinesfosdem25-250204160547-45fa96dd/75/Routing-Guidelines-Unlocking-Smarter-Query-Routing-in-MySQL-Architectures-18-2048.jpg)
![AdminAPI C O M M A N D P U R P O S E .create_routing_guideline(name[, json[, options]]) Obvious .set_routing_option("guideline", name) New option "guideline" to activate a guideline in the target topology. .get_routing_guideline([name]) Evident .remove_routing_guideline(name) Clear .routing_guidelines() Lists all Routing Guidelines of the topology with some info .import(filePath) Imports a Routing Guideline stored in a .json file into the topology 23 <Cluster>, <ReplicaSet>, <ClusterSet>](https://image.slidesharecdn.com/mysqlroutingguidelinesfosdem25-250204160547-45fa96dd/75/Routing-Guidelines-Unlocking-Smarter-Query-Routing-in-MySQL-Architectures-23-2048.jpg)
![AdminAPI C O M M A N D P U R P O S E .show([options]) Displays a comprehensive summary of the Routing Guideline .as_json() Unambiguous .destinations() Explicit .routes() Unmistakable .add_route(name, match, destinations [, options]) Clear .add_destination(name, match, [, options]) Self-evident .remove_route(name) Lucid .remove_destination(name) Straightforward .set_destination_option(destinationName, option, value) Decipherable .set_route_option(routeName, option, value) Perceptible .copy(name) Visible .export(filePath) Exports the target Routing Guideline to a .json file .rename(name) Logical <RoutingGuideline>](https://image.slidesharecdn.com/mysqlroutingguidelinesfosdem25-250204160547-45fa96dd/75/Routing-Guidelines-Unlocking-Smarter-Query-Routing-in-MySQL-Architectures-24-2048.jpg)
Uploaded byMiguel Araújo
Routing Guidelines: Unlocking Smarter Query Routing in MySQL Architectures
The document discusses advanced query routing guidelines for MySQL architectures, highlighting the need for improved routing mechanisms amidst evolving database setups. It identifies challenges with current routing methods, such as latency, resource prioritization, and schema-specific routing, and presents new strategies for dynamic and efficient query management. The guidelines aim to enhance high availability, disaster recovery, and compliance through smarter, adaptable routing techniques incorporating local and schema-based approaches.
Routing Guidelines: Unlocking Smarter Query Routing in MySQL Architectures
- 1. Routing Guidelines Unlocking SmarterQuery Routing in MySQL Architectures Miguel Araújo Senior Principal Software Engineer MySQL, Oracle February 02, 2025 FOSDEM'25
- 2.
- 3. Setting the Stage TheCase for Smarter Query Routing
- 4. 4 Current Routing Mechanism TheMySQL Router serves as a lightweight middleware that efficiently routes client requests to the appropriate MySQL Servers. • Full integration into MySQL Architectures • Transparent client connection routing • Load Balancing • Application connection failover • Automatically does what is expected from it with almost no setup (most of the time)
- 5. 5 A Shared Experience Afriend running an e-commerce struggled and asked me for help... • BI traffic is intensive • Frontend traffic must avoid stale data • Do manual Routing configuration? • Use "read_only_targets" and multiple Routers? "Write me a patch to support this, please?" Where Current Routing Falls Short
- 6. As MySQL evolveswith setups like ClusterSet, traditional routing faces significant challenges, requiring a smarter approach. 6
- 7. 7 Challenges With EvolvingTopologies Geographically distributed instances (InnoDB ClusterSet) • Current challenge: Routing doesn't consider latency differences across datacenters • Need: Adaptable routing to optimize performance based on proximity
- 8. 8 Challenges With EvolvingTopologies Resource prioritization • Current challenge: Equal treatment of all sessions may cause inefficiencies during overload or partial outage • Need: Prioritize critical operations during high load
- 9. 9 Challenges With EvolvingTopologies Schema-specific routing (vertical partitioning) • Current challenge: Manual connection management based on schema location • Need: Automatic routing to the right Cluster based on schema or other criteria
- 10. 10 Complex Routing Factors Asthe complexity of MySQL setups grows, routing must adapt: • Support application-specific configurations • Adapt to dynamic changes in topologies such as failovers, overloads, or maintenance • Enable granular control, even down to individual statements within a session • Custom routing behavior for very narrow and specific use cases
- 11. MySQL Routing Guidelines CoreConcepts And Technical Foundations
- 12. 12 Routing Guideline Syntax •Destinations o Primary o Secondary • Routes o ro • Name o FOSDEM25 • Version o 1.0
- 13. Breaking It Down Destinations •Group MySQL instances in the topology using pattern-matching expressions • The expressions define which servers are included in a destination class • Each class forms a pool of candidate instances for routing 13
- 14. Breaking It Down Routes •Match incoming client sessions to appropriate destination candidates, using expressions • The expressions define how client sessions are classified and directed to MySQL Servers • Candidate destinations are organized into tiers, with each tier containing one or more destination classes 14
- 15. Matching Rules Predefined variables •$.server.* • Related to the MySQL Server • $.session.* • Related to the Client session • $.router.* • Related to the Router instance Functions / Operators • Logical operators • AND | OR | NOT • Inclusion checks • IN | NOT IN • LIKE operator • Pattern matching | _ | % • Arithmetic operations • + | - | * | % | / • Comparisons • > | >= | < | <= | = | <> • Functions • SQRT() | CONCAT() | IS_IPV6() | etc. 15
- 16. Matching Expressions • Classify:Identify servers, sessions, or routers with logical conditions • Compose: Use variables, operators, and values to create matches • Chain: Link conditions with AND, OR, or NOT for flexibility (<function> | <variable>) [<operator> <value>] [<logical-operator> <expression>]* 16
- 17. Predefined Variables $.server.* VA RI A B L E T Y P E E X A M P L E $.server.label String "myserver" $.server.address String "myhost123-portugal" $.server.port Integer 3306 $.server.uuid String "bae9454b-d8f3-11ef-8e0c- d08e7912e4ee" $.server.version Integer (MMmmpp) 90200 $.server.memberRole Enum SECONDARY $.server.tags String (key-value) .performance = 'high' $.server.clusterName String "FOSDEM25" $.server.clusterRole Enum REPLICA $.server.clusterSetName String "MyClusterSet" $.server.isClusterInvalidated Boolean False 17
- 18. Predefined Variables $.session.* VA RI A B L E T Y P E E X A M P L E $.session.targetIp String "192.168.1.235" $.session.targetPort Integer 6446 $.session.sourceIp String "10.1.103.12" $.session.user String "admin" $.session.connectAttrs String (key-value) ._os = 'Linux' $.session.schema String "world" $.session.randomValue Double [0, 1] 0.3 18
- 19. Predefined Variables $.router.* VA RI A B L E T Y P E E X A M P L E $.router.port.rw Integer 6446 $.router.port.ro Integer 6447 $.router.port.rw_split Integer 6448 $.router.localCluster String "FOSDEM25" $.router.hostname String "domus" $.router.bindAddress String "127.0.0.1" $.router.tags String (key-value) "tag:router_foo" $.router.routeName String "bootstrap_ro" $.router.name String "myrouter123" 19
- 20. Functions VA R IA B L E E X A M P L E CONCAT('str', 'str', ...) CONCAT('a', 'b', 'cde') abcde SQRT('number') SQRT(0.16) 0.4 NUMBER('str') NUMBER(CONCAT('1', '2')) 12 NETWORK('str', 'int') NETWORK('192.168.1.33', 24) 192.168.1.0/24 IS_IPV6('str') IS_IPV6('192.168.1.33) FALSE IS_IPV4('str') IS_IPV4('192.168.1.33) TRUE STARTSWITH('str1', 'str2') STARTSWITH('foo', 'foobar') TRUE ENDSWITH('str1', 'str2') CONCAT('a', 'b', 'cde') abcde CONTAINS('str1', 'str2') CONTAINS('foobar', 'foo') TRUE RESOLVE_V4('str') RESOLVE_V4('domus') 127.0.0.1 RESOLVE_V6('str'): RESOLVE_V6('domus') 2a02:26f0:d8:108e::a15 REGEXP_LIKE('str1', 'str2') REGEXP_LIKE('foobarbaz', 'foo.*baz') TRUE SUBSTRING_INDEX('str1', 'str2', 'int') SUBSTRING_INDEX("test", "s", 1) te
- 21. Workflow 2. Match RouteRules Classifies incoming client request to routes Route 1 : n candidate destination class 1. Classify Destinations Groups Servers into destination classes Server 1 : n destination class 3. Apply Routing Strategy first-available round-robin 4. Monitor Topology Reclassify servers Update Routes Disconnect invalid connections 21
- 22. AdminAPI • Extended tosupport Routing Guidelines • Define, manage, visualize • New <RoutingGuideline> class • Seamless support in all MySQL Architectures: • InnoDB Cluster • InnoDB ReplicaSet • InnoDB ClusterSet 22
- 23. AdminAPI C O MM A N D P U R P O S E .create_routing_guideline(name[, json[, options]]) Obvious .set_routing_option("guideline", name) New option "guideline" to activate a guideline in the target topology. .get_routing_guideline([name]) Evident .remove_routing_guideline(name) Clear .routing_guidelines() Lists all Routing Guidelines of the topology with some info .import(filePath) Imports a Routing Guideline stored in a .json file into the topology 23 <Cluster>, <ReplicaSet>, <ClusterSet>
- 24. AdminAPI C O MM A N D P U R P O S E .show([options]) Displays a comprehensive summary of the Routing Guideline .as_json() Unambiguous .destinations() Explicit .routes() Unmistakable .add_route(name, match, destinations [, options]) Clear .add_destination(name, match, [, options]) Self-evident .remove_route(name) Lucid .remove_destination(name) Straightforward .set_destination_option(destinationName, option, value) Decipherable .set_route_option(routeName, option, value) Perceptible .copy(name) Visible .export(filePath) Exports the target Routing Guideline to a .json file .rename(name) Logical <RoutingGuideline>
- 25. Impact And UseCases Unlocking The Potential Of Routing Guidelines
- 26. High Availability AndDisaster Recovery Goals 1. Seamless Failover: ❑ Redirect traffic to alternate nodes duringoutages for uninterrupted service 2. Local-First: ❑ Prefer local nodes for routing, using remote nodes asfallback options 3. Optimized Read-Write Routing: ❑ Routes write traffic to primary and distributes read traffic across secondaries and read-replicas for scale-out 4. Fallback levels: ❑ Implement fallback tiers to ensure maximumavailability 26
- 27. High Availability AndDisaster Recovery 27
- 28. Geolocation-Based Routing AndCompliance • Routes traffic based on IP to specific regional destinations • Directs traffic requiring a specific compliance to servers tagged with it 28
- 29. Schema-Based Routing • Applicationschema Traffic: Sessions using 'app_schema' are directed to AppCluster • Data schema traffic: Sessions using 'data_schema' are directed to the main data cluster • $.session.schema 29
- 30. Testing, Staging, AndSession Affinity • Testing traffic: Routes ~10% of requests to testing servers for isolated testing • Staging traffic: Routes ~20% of requests to staging servers for validation • Production traffic: Routes remaining requests to production servers for stability • Session affinity: Ensures 'persistent_user' sessions maintain continuity 30
- 31. Client Characteristics Routing •Backup Traffic: Routes sessions coming from 'mysqldump' to the Backup Servers • Linux traffic: Routes sessions coming from Linux clients to Servers running on Linux • Connection attributes • ._os = 'Linux' • .program_name = 'mysqldump' • Metadata tags • $.server.tags 31
- 32. From A Friend'sChallenge To Smarter Routing 32 • Primary and Secondaries: ▪ $.server.memberRole • Split Read-Replicas: By network ranges and member-role ▪ NETWORK() ▪ $.server.memberRole • Testing Servers: Match by MySQL Server version ▪ $.server.version
- 33. From A Friend'sChallenge To Smarter Routing 33 • BI traffic: Prioritize read-replicas dedicated for it ▪ $.session.connectAttrs ▪ $.session.user • Frontend traffic: Prioritize secondaries, fallback to read-replicas and primary ▪ $session.user
- 34. Closing and Q&A Takeaways,Resources, And Discussion
- 35. Takeaways • Smarter Routing:Routing Guidelines enable dynamic, flexible, and declarative query routing • Effortless Management: MySQL Shell / AdminAPI makes defining and managing Routing Guidelines straightforward • Future-Ready Architectures: Routing Guidelines empower scalable, resilient, and flexible MySQL setups, seamlessly handling complex topologies. 35
- 36. • Cookbook: https://github.com/mysql/mysql-shell/blob/master/ROUTING_GUIDELINES.md •Documentation: https://dev.mysql.com/doc/mysql-shell/en/admin-api-routing- guidelines.html • Community: Join our Slack workspace: bit.ly/mysql-slack ▪ #mysql_innodb_cluster ▪ #router ▪ #shell Resources 36
- 37.