MySQL Proxy Making MySQL more flexible Jan Kneschke jan@mysql.com - - PowerPoint PPT Presentation

MySQL Proxy

Making MySQL more flexible Jan Kneschke jan@mysql.com

MySQL Proxy

• proxy-servers forward requests to backends

and can

– transform, handle or block them

• released under the GPL

– see http://forge.mysql.com/wiki/MySQL_Proxy

• developed as part of the Enterprise Tools since

February 2007

Design Decisions

• goal is to be transparent to the application layer
• supports all platforms and languages
• designed to handle thousands of parallel

connections (c10k)

• uses a embedded scripting language for

customizations

Transparency

• SHOW WARNINGS can be worked around with

Query Injection

• SELECT USER() shows the connected user

(the proxy, not the client) which can be corrected with result-set rewriting

• host auth against the MySQL server

Latency

• early tests via localhost
• same script run directly and through the proxy
• latency per mysql-packet: 0.4ms
• ping RTT on 1Gbit: 0.1ms

Load Balancing

• load balancing distributes the load across

several slaves

• Shortest Queue First is default

– send new connections to the server with the least

number of open connections

Fail Over

• dead host are detected
• taking out of load balancing for 2min
• uses custom load balancers to decide how to

handle a dead host

– hot + standby – uses load balancing

Removing SPoF

• one Proxy == Single Point of Failure
• use external Heartbeat (linuxha.org) or
• 2 LB proxies + 1 Host Selecting Proxy per

application server

Failsafe Load Balancing

Flexibility

• proxy embeds LUA
• allows analyzing and manipulating packets

– Inspection – Rewriting – Blocking – Injection

LUA

• PiL http://lua.org/manual/5.1/
• embedded, simple, efficient
• can do OO-like programming
• has scalars, tables, metatables and anonymous

functions

Query Rewriting

• Macro Packages (ls, cd, who, ...)
• tagging queries with SQL_CACHE
• migrating table-names and SQL dialects
• turn EXPLAIN UPDATE|DELETE into

equivalent EXPLAIN SELECT

Query Profiling

• SHOW SESSION STATUS around a Query

Exec_time: 6749 us .. Handler_read_rnd_next = 252 .. Handler_write = 252 .. Select_scan = 1

Query Statistics

• Normalize Queries to track query usage
• Count Table and Index usage
• Optimize Query Cache Usage by injecting

SQL_CACHE in cachable queries

• see lib/analyze-queries.lua

Auditing

• Diagnostic Auditing
• track which user+ip run which query or

accessed which objects when

• assign query-costs
• log gathered information in a central place
• see lib/auditing.lua

Global Transaction ID

• Inject a counter in all transactions
• Answers questions like

– which slave is most current – can I read from this slave, or do I have to read from

master

– you name it

Connection Pooling

• reusing open connections between proxy and

server

• reduces concurrency on the MySQL Server
• external connection pool for PHP

Statement Routing

• split the query stream into reading and writing

– READs go to the slaves – WRITEs and transactions to the master

• automatic scale-out
• sharding

Tokenizer

• turns a SQL query into a token stream
• not a full parser, just a tokenizer for speed

reasons

• understands KEYWORDS, /*comments*/,

“strings”, 123 and `literals`

• later we'll add support for SQL modes

normalizing Queries

1: { TK_SQL_SELECT, select } 2: { TK_STAR, * } 3: { TK_SQL_FROM, from } 4: { TK_LITERAL, t1 } 5: { TK_SQL_WHERE, where } 6: { TK_LITERAL, id } 7: { TK_EQ, = } 8: { TK_INTEGER, 1 } normalized query: SELECT * FROM `t1` WHERE `id` = ?

Libraries

• auto-config

– SET GLOBAL ...

• balance

– load balancers

• commands

– parse MySQL

Command Packets

• parser

– extract tablenames

• tokenizer

– normalize() – cleanup queries

Internals – LUA scripting

• proxy.* is the namespace
• proxy.connection.* is the current

connection

• proxy.backends[...] are the backends
• proxy.global.* is the global table
• proxy.global.config.* is used for the

config

Internals - Scope

• Each connection has its own script scope
• proxy.global.* to share data between

connections

• use local to make variables local to the

function

• use package.seeall() to export functions

from modules

Internals - Threading

• the global scope and threading don't play nice

by default

• http://www.cs.princeton.edu/~diego/professional/l
• patches lua to apply mutexes around variable

access

Internals – Script Cache

• 0.6.0 we reload the script on each connection

start

• adding a script cache with mtime check
• lua_pushvalue(L, -1) does the trick

Roadmap

• to be released 0.6.0

– tokenizer – read-write splitting – Query Statistics

• later

– parallel Queries – proxy initiates connections

LUA - Gotchas

• only false and nil are !true, 0 is true
• to say “not equal” you use ~=
• there are no shortcuts

– no a++, no a *= 4, ... – no a > b ? a : b (there is “(a > b) and a or b)