SLIDE 1
Scripting libdrizzle with Lua inside Nginx
章亦春 (agentzh)
agentzh@gmail.com
2012.4
Scripting libdrizzle with Lua inside Nginx Scripting libdrizzle with - - PowerPoint PPT Presentation
Scripting libdrizzle with Lua inside Nginx Scripting libdrizzle with - - PowerPoint PPT Presentation
Scripting libdrizzle with Lua inside Nginx Scripting libdrizzle with Lua inside Nginx (agentzh) agentzh@gmail.com 2012.4 "MySQL is always the bottleneck !" "Really?!" Some benchmarks on Amazon EC2 Small
SLIDE 2
SLIDE 3
"MySQL is always the bottleneck!" "Really?!"
SLIDE 4
SLIDE 5
SLIDE 6
Some benchmarks on Amazon EC2 Small instances
SLIDE 7
SLIDE 8
A Slow MySQL Query select sleep(1)
SLIDE 9
♡ Amazon Linux AMI 2011.09 ♡ nginx 1.0.14 ♡ php-fpm 5.3.10
SLIDE 10
SLIDE 11
SLIDE 12
SLIDE 13
SLIDE 14
A Fast MySQL Query with a Small Resultset select * from world.City
- rder by ID
limit 1
SLIDE 15
SLIDE 16
SLIDE 17
A Fast MySQL Query with a Big Resultset (100 KBytes) select * from world.City
- rder by ID
limit 1000
SLIDE 18
SLIDE 19
SLIDE 20
SLIDE 21
We integrated libdrizzle directly into Nginx! http://wiki.nginx.org/HttpDrizzleModule
SLIDE 22
SLIDE 23
SLIDE 24
SLIDE 25
SLIDE 26
SLIDE 27
Let's just mud with nginx.conf, the Nginx configuration file
SLIDE 28
upstream my_mysql_backend { drizzle_server 127.0.0.1:3306 dbname=test password=some_pass user=monty protocol=mysql; # a connection pool that can cache up to # 200 mysql TCP connections drizzle_keepalive max=200 overflow=reject; }
SLIDE 29
location ~ '^/cat/(.*)' { set $name $1; set_quote_sql_str $quoted_name $name; drizzle_query "select * from cats where name=$quoted_name"; drizzle_pass my_mysql_backend; rds_json on; }
SLIDE 30
$ curl 'http://localhost/cat/Jerry' [{"name":"Jerry","age":1}]
SLIDE 31
The dynamic SQL Query for This Request select * from cats where name='Jerry'
SLIDE 32
SLIDE 33
The Slow MySQL Query again! select sleep(1)
SLIDE 34
SLIDE 35
SLIDE 36
SLIDE 37
The Fast MySQL Query with a Small Resultset Again! select * from world.City
- rder by ID
limit 1
SLIDE 38
SLIDE 39
SLIDE 40
The Fast MySQL Query with a Big Resultset (100 KBytes) Again! select * from world.City
- rder by ID
limit 1000
SLIDE 41
SLIDE 42
SLIDE 43
We also embedded Lua and LuaJIT directly into Nginx! http://wiki.nginx.org/HttpLuaModule
SLIDE 44
SLIDE 45
Use the Lua language to access the ngx_drizzle module!
SLIDE 46
SLIDE 47
location = /api { content_by_lua ' local rds_parser = require "rds.parser" local cjson = require "cjson" local resp = ngx.location.capture("/cat/Jerry") local data, err = rds_parser.parse(res.body) ngx.print(cjson.encode(data.resultset)) '; }
SLIDE 48
$ curl 'http://localhost/api' [{"name":"Jerry","age":1}]
SLIDE 49
The Fast MySQL Query with a Small Resultset Revisited! select * from world.City
- rder by ID
limit 1
SLIDE 50
SLIDE 51
SLIDE 52
The Fast MySQL Query with a Big Resultset (100 KBytes) Again! select * from world.City
- rder by ID
limit 1000
SLIDE 53
SLIDE 54
SLIDE 55
I just implemented the Lua cosocket API! http://wiki.nginx.org/HttpLuaModule#ngx.socket.tcp
SLIDE 56
! ! a socket API based on Lua coroutines ! ! a socket API that is synchronous ! ! a socket API that is nonblocking
SLIDE 57
SLIDE 58
I wrote the lua-resty-mysql library based on the cosocket API. http://github.com/agentzh/lua-resty-mysql
SLIDE 59
It is a pure Lua MySQL driver written from scratch!
SLIDE 60
local resty_mysql = require "resty.mysql" local mysql = resty_mysql:new() local ok, err = mysql:connect{ host = "127.0.0.1", port = 3306, database = "world", user = "monty", password = "some_pass" }
SLIDE 61
local query = "select * from cats" local rows, err, errno, sqlstate = mysql:query(query) for i, row in ipairs(rows) do
- - process the row table
end
SLIDE 62
- - add the current MySQL connection
- - into the per-worker connection pool,
- - with total capacity of 1024 connections and
- - 60 seconds maximal connection idle time
local ok, err = mysql:set_keepalive(60000, 1024)
SLIDE 63
The Slow MySQL Query Revisited! select sleep(1)
SLIDE 64
SLIDE 65
SLIDE 66
SLIDE 67
The Fast MySQL Query with a Small Resultset Revisited! select * from world.City
- rder by ID
limit 1
SLIDE 68
SLIDE 69
SLIDE 70
The Fast MySQL Query with a Big Resultset (100 KBytes) Revisited! select * from world.City
- rder by ID
limit 1000
SLIDE 71
SLIDE 72
SLIDE 73
How about comparing with the NodeJS world?
SLIDE 74
♡ node 0.6.14 ♡ node mysql 0.9.5 ♡ node generic pool 1.0.9
SLIDE 75
SLIDE 76
SLIDE 77
SLIDE 78
SLIDE 79
Caching responses with ngx_srcache + ngx_memc http://wiki.nginx.org/HttpSRCacheModule http://wiki.nginx.org/HttpMemcModule
SLIDE 80
SLIDE 81
# configure the cache storage location location /memc { internal; set $memc_key $query_string; set $memc_exptime 300; memc_pass 127.0.0.1:11211; }
SLIDE 82
location = /api { set $key "$uri?$args"; srcache_fetch GET /memc $key; srcache_store PUT /memc $key; # drizzle_pass/fastcgi_pass/content_by_lua/... }
SLIDE 83
SLIDE 84
SLIDE 85
SLIDE 86
Find the source for all the benchmarks given here: http://github.com/agentzh/mysql-driver-benchmark
SLIDE 87
Any questions? http://openresty.org https://groups.google.com/group/openresty
SLIDE 88 S c r
SLIDE 89
SLIDE 90
SLIDE 91
SLIDE 92
SLIDE 93
SLIDE 94
SLIDE 95
SLIDE 96
SLIDE 97
SLIDE 98
SLIDE 99
SLIDE 100
SLIDE 101
SLIDE 102
SLIDE 103
SLIDE 104
SLIDE 105
SLIDE 106
SLIDE 107
SLIDE 108
SLIDE 109
SLIDE 110
SLIDE 111
SLIDE 112
SLIDE 113
SLIDE 114
SLIDE 115
SLIDE 116
SLIDE 117
SLIDE 118
SLIDE 119
SLIDE 120
SLIDE 121
SLIDE 122
SLIDE 123
SLIDE 124
SLIDE 125
SLIDE 126
SLIDE 127
SLIDE 128
SLIDE 129
SLIDE 130
SLIDE 131
SLIDE 132
SLIDE 133
SLIDE 134
SLIDE 135
SLIDE 136
SLIDE 137
SLIDE 138
SLIDE 139
SLIDE 140