dba-support

2.ProxySQL – Query Routing

ProxySQL is a powerful tool for managing MySQL and MariaDB database traffic. One of its key features is query routing, which allows you to direct queries to different backend servers based on specific rules. This can help with load balancing, read/write splitting, and optimizing database performance. Here’s a detailed explanation of how ProxySQL handles query routing:

How Query Routing Works in ProxySQL

ProxySQL uses rules defined in its mysql_query_rules table to determine how queries are routed to backend servers. These rules can be based on various criteria, such as the query pattern, user, schema, or other attributes. Here’s a step-by-step process of how query routing works:

  1. Defining Backend Servers:
  • Servers are categorized into different hostgroups, each representing a set of backend servers.
  • Example:
    INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('master-db', 3306, 0); INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('replica-db1', 3306, 1); INSERT INTO mysql_servers (hostname, port, hostgroup_id) VALUES ('replica-db2', 3306, 1);
  1. Creating Query Rules:
  • Rules are created in the mysql_query_rules table to match specific query patterns and route them to the appropriate hostgroup.
  • Example:
    INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 1, 1);
  • This rule routes all SELECT queries to hostgroup 1 (which contains the replicas).
  1. Setting User Defaults:
  • Users are associated with a default hostgroup, which determines where their queries are routed if no specific rules are matched.
  • Example:
    INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('user', 'password', 0);
  1. Loading Configuration:
  • After defining the servers, users, and rules, the configuration needs to be loaded into runtime and saved to disk.
  • Example:
    LOAD MYSQL SERVERS TO RUNTIME;
    SAVE MYSQL SERVERS TO DISK;
    LOAD MYSQL USERS TO RUNTIME;
    SAVE MYSQL USERS TO DISK;
    LOAD MYSQL QUERY RULES TO RUNTIME;
    SAVE MYSQL QUERY RULES TO DISK;

Example of Query Routing Rules

Here are some more detailed examples of query routing rules you can create in ProxySQL:

  1. Read/Write Splitting:
  • Route all INSERT, UPDATE, DELETE, and other write queries to the master and all SELECT queries to replicas.
    INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 1, 1); INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply) VALUES (2, '^(INSERT|UPDATE|DELETE|REPLACE).*', 0, 1);
  1. Routing Based on User:
  • Route queries from a specific user to a different hostgroup.
    INSERT INTO mysql_query_rules (rule_id, username, destination_hostgroup, apply) VALUES (3, 'readonly_user', 1, 1);
  1. Schema-Based Routing:
  • Route queries for a specific schema to a particular hostgroup.
    INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply, schema_name) VALUES (4, '.*', 2, 1, 'analytics_db');

Advanced Routing with Query Attributes

ProxySQL can route queries based on various attributes beyond just the query pattern. Some of these attributes include:

  • Client Address: Route queries based on the client’s IP address.
  INSERT INTO mysql_query_rules (rule_id, client_addr, destination_hostgroup, apply) VALUES (5, '192.168.1.%', 1, 1);
  • Client Port: Route queries based on the client’s port.
  INSERT INTO mysql_query_rules (rule_id, client_port, destination_hostgroup, apply) VALUES (6, 3306, 0, 1);
  • Transaction Routing: Route all queries within a transaction to the same hostgroup.
  INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply, flagIN) VALUES (7, '^(BEGIN|START TRANSACTION)', 0, 1, 0);
  INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, apply, flagOUT) VALUES (8, '^(COMMIT|ROLLBACK)', 0, 1, 0);

Monitoring and Adjusting Rules

After setting up query routing rules, it’s essential to monitor their performance and adjust them as needed. ProxySQL provides extensive logging and statistics to help you analyze the effectiveness of your routing rules.

  • Query Statistics: View statistics on queries to understand the load on each hostgroup.
  SELECT hostgroup, schemaname, digest_text, count_star, sum_time FROM stats_mysql_query_digest;
  • Connection Statistics: Monitor connections to backend servers.
  SELECT hostgroup, srv_host, srv_port, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool;

By leveraging ProxySQL’s query routing capabilities, you can significantly enhance the performance, scalability, and reliability of your MySQL or MariaDB database infrastructure.

amit