玉面飞龙的BLOG

原文解释

What is MySQL Proxy?

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.

利用proxy可以实现多种对mysql来说还未完美实现的高级功能。如load balance, query分析,高级日志(包括详细的show status等统计信息),read write customer split等。

其中这个query statistics十分有用,类似DBA在mysql上作类似Oracle v$sessstat的查询,从而自定义query slow log。

安装包也自带很多实用的lua脚本。通过收集到的统计信息(虽然没有wait event)可以自定义要监控的slow query.

C:\>mysql-proxy –proxy-lua-script=share\analyze-query.lua

# 2009-01-12 16:09:24 [1] user: root, db: world
Query: “show tables”
Norm_Query: “SHOW `tables` ”
Exec_time: 10014 us
.. Bytes_received = 5
.. Bytes_sent = 19
.. Com_show_tables = 1
.. Created_tmp_tables = 1
.. Handler_read_rnd_next = 13
.. Handler_write = 12
.. Last_query_cost = 10.499
.. Qcache_not_cached = 2
.. Questions = 1
.. Select_scan = 1
.. Slow_queries = 2

# 2009-01-12 16:09:28 [1] user: root, db: world
Query: “select *from t3″
Norm_Query: “SELECT * FROM `t3` ”
Exec_time: 120173 us
.. Bytes_received = 9
.. Com_select = 1
.. Handler_read_first = 1
.. Handler_read_key = 2
.. Innodb_buffer_pool_pages_data = 2
.. Innodb_buffer_pool_read_requests = 60
.. Innodb_buffer_pool_reads = 2
.. Innodb_data_read = 32768
.. Innodb_data_reads = 2
.. Innodb_pages_read = 2
.. Last_query_cost = 1.199
.. Open_tables = 1
.. Opened_tables = 1
.. Qcache_not_cached = 2
.. Questions = 1
.. Select_scan = 1
.. Slow_queries = 2
.. Table_locks_immediate = 1
.. Uptime = 1

这年头Proxy功能越来越多，家族越来越强大，在互联网应用设计中使用越来越广泛。