MySQL ProxySQL 测试环境搭建与性能优化实践

1. MySQL Proxy 测试环境搭建与工具选型在开始正式的测试之前我们需要先搭建一个完整的测试环境。我选择了三台配置相同的服务器4核8G内存SSD存储分别部署MySQL主库、从库和Proxy节点。操作系统统一使用Ubuntu 20.04 LTSMySQL版本为8.0.28社区版。注意生产环境中Proxy节点建议部署在应用服务器和数据库服务器之间网络延迟应控制在1ms以内测试工具选型方面我主要使用了以下工具组合sysbench 1.0.20用于基准性能测试mysqlslap内置的负载模拟工具pt-query-digest慢查询分析Prometheus Grafana监控指标可视化1.1 Proxy方案对比目前主流的MySQL Proxy方案有以下几种Proxy类型代表产品协议支持主要功能特点传统代理MySQL RouterMySQL协议官方出品路由功能完善中间件型ProxySQLMySQL协议查询缓存、读写分离、连接池全功能型MaxScaleMySQL协议数据分片、故障转移、防火墙云原生方案VitessgRPC水平扩展、分布式事务支持经过综合评估我选择ProxySQL 2.4.4作为本次测试的主体主要基于以下考虑社区活跃度高企业应用案例丰富配置灵活支持动态加载配置性能损耗较小功能覆盖全面兼容MySQL 8.0的新认证方式2. 基础功能测试与验证2.1 连接路由测试首先验证ProxySQL的基础路由功能。配置了3个后端服务器组写组master10.0.0.1:3306读组slave110.0.0.2:3306备读组slave210.0.0.3:3306通过以下命令测试路由规则-- 写操作路由验证 INSERT INTO test.t1 VALUES (1); -- 确认只在master执行 -- 读操作路由验证 SELECT * FROM test.t1; -- 确认在slave1/slave2轮询测试发现一个关键问题当使用MySQL 8.0默认的caching_sha2_password认证时ProxySQL需要额外配置[mysql_servers] 10.0.0.1:3306WRITER 10.0.0.2:3306READER 10.0.0.3:3306READER [mysql_users] usernametestuser passwordTest123 default_hostgroup10 active1 use_ssl0 default_schematest transaction_persistent1 fast_forward0 backend12.2 查询缓存测试ProxySQL的查询缓存是其核心功能之一。我设计了以下测试场景准备10万行测试数据执行相同SELECT查询100次带WHERE条件对比开启/关闭缓存时的响应时间测试结果模式平均响应时间(ms)QPSCPU使用率无缓存12.34810.545%有缓存0.87114948%缓存配置的关键参数UPDATE mysql_query_rules SET cache_ttl30000 WHERE rule_id1; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;3. 性能基准测试3.1 只读场景测试使用sysbench进行只读测试sysbench oltp_read_only \ --db-drivermysql \ --mysql-host10.0.0.100 \ --mysql-port6033 \ --mysql-usertestuser \ --mysql-passwordTest123 \ --mysql-dbtest \ --tables10 \ --table-size100000 \ --threads64 \ --time300 \ --report-interval10 \ run测试结果对比ProxySQL vs 直连MySQL指标直连MySQLProxySQL差异平均延迟(ms)5.26.830%95%延迟(ms)9.112.436%吞吐量(QPS)125009800-21%连接建立时间(ms)3.25.778%3.2 读写混合场景测试调整sysbench参数模拟30%写操作sysbench oltp_read_write \ --db-drivermysql \ --mysql-host10.0.0.100 \ --mysql-port6033 \ --mysql-usertestuser \ --mysql-passwordTest123 \ --mysql-dbtest \ --tables10 \ --table-size100000 \ --threads64 \ --time300 \ --report-interval10 \ --percentile95 \ run性能对比数据指标直连MySQLProxySQL差异平均延迟(ms)8.711.228%95%延迟(ms)15.320.131%吞吐量(QPS)89007200-19%错误率(%)0.010.15140%4. 高可用与故障转移测试4.1 主库故障切换模拟主库宕机场景停止master节点MySQL服务观察ProxySQL监控日志验证写操作是否自动切换到新master关键配置参数UPDATE mysql_servers SET max_replication_lag300; INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type) VALUES (10,20,read_only);实测切换时间分布检测到故障3-5秒标记节点下线立即生效新主库选举依赖MGR约10秒完全恢复总耗时15-20秒4.2 网络分区测试使用tc命令模拟网络延迟和丢包# 添加100ms延迟5%丢包 tc qdisc add dev eth0 root netem delay 100ms loss 5%观察到的现象连接池中的空闲连接会持续超时新建连接成功率降至约80%自动重试机制导致部分查询耗时激增优化建议[mysql_connection] connect_timeout3000 connection_max_age_ms60000 ping_interval100005. 生产环境调优建议5.1 关键参数配置根据测试结果总结的最佳实践配置-- 连接池设置 SET mysql-connection_max_age_ms3600000; SET mysql-ping_interval60000; SET mysql-connect_timeout2000; -- 查询规则优化 UPDATE mysql_query_rules SET apply1,active1,retries2; -- 监控调整 UPDATE global_variables SET variable_valuetrue WHERE variable_namemysql-monitor_enabled;5.2 监控指标重点必须监控的核心指标清单指标名称报警阈值采集频率ConnPool_conn_used_pct80%10sQuery_ResponseTime_95th500ms30sBackend_latency_avg100ms30sMySQL_Aborted_connects5/min60sMemory_used_pct70%10s5.3 常见问题解决方案在实际部署中遇到的典型问题及解决方法认证失败问题现象客户端报Access denied 解决UPDATE global_variables SET variable_valuefalse WHERE variable_namemysql-use_ldap_auth;内存泄漏问题现象内存持续增长不释放 解决限制查询缓存大小SET mysql-query_cache_size_mb1024;路由失效问题现象写操作被路由到只读实例 解决检查事务持久性设置UPDATE mysql_users SET transaction_persistent1;经过全面测试ProxySQL在MySQL架构中确实能发挥重要作用但需要根据实际业务特点进行针对性调优。建议在测试环境充分验证后再逐步上线生产流量