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 – READ s go to the slaves – WRITE s 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 ● parser – SET GLOBAL ... – extract tablenames ● balance ● tokenizer – load balancers – normalize() ● commands – cleanup queries – parse MySQL Command Packets

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)