using lua for stored procedures in a high availability database verisign i2s team name of presenter:...
TRANSCRIPT
![Page 1: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/1.jpg)
Using Lua for Stored Procedures in a High Availability Database
VeriSign I2S Team
Name of Presenter: John Rodriguez
Date: July 14, 2008
![Page 2: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/2.jpg)
2
Agenda
1 VeriSign High Availability Database Background (VHAD) –
Simplicity
2 Why Choose Lua for Stored Procedures?
3 VHAD Integrated Development Environment – IDE
![Page 3: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/3.jpg)
3
Introduction
+ VeriSign’s mission is making the Internet and telecommunications networks
more reliable, more intelligent, and more secure.
+ VeriSign runs the registry (database) for the .com, .net, .cc, and .tv TLDs.
+ The motivation for VHAD was driven by historic and projected transactional
growth and system availability requirements
+ VHAD is a very high availability (99.9995%), very fast, replicated, memory-
based database, optimized for Online Transaction Processing (OLTP).
+ Are you crazy? Why write your own database? ▪ Big, replicated commercial database systems just have too many moving parts.▪ Cost rises exponentially when we push well beyond normal availability levels.
+ I still think you’re crazy. What makes you think you can actually do this?▪ Carefully, aggressively simplify the design and architecture.▪ Target only the critical subset of system functionality.
![Page 4: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/4.jpg)
4
Mission
+ Continuous Availability▪ Availability is achieved by minimal administration, narrowly focused
scope, simple and clear design, in-memory architecture ▪ Approach is NOT added redundancy over a lower-availability database.
+ High Performance▪ Designed for 1+ billion update transactions per day (~ 50K/second).▪ Designed for an unlimited volume of read-only transactions.
+ Online Transaction Processing▪ Assumes Insertion/Update of approximately 10 rows per transaction.▪ Not designed for general purpose set processing – no huge transactions.
+ Storage Engine is fully ACID compliant▪ Atomicity – Transactional commit/abort – all or nothing. ▪ Consistency – Foreign key / uniqueness relationships are enforced. ▪ Isolation – A transaction never sees someone else’s partial change.▪ Durability – Acknowledgement of a commit guarantees data
persistence.
![Page 5: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/5.jpg)
5
+ What kinds of things don’t stop but just keep going, going, going…?▪ The moon keeps orbiting.▪ Simple tools just work.▪ What makes them different
from things that break, ormust be stopped occasionally?
+ Continuous Availability:▪ No “maintenance windows”, no fine print.▪ No excuses, it doesn’t matter why.▪ Not an affirmation that we will fix it,
but a guarantee that we won’t need to.
+ How targeting really high availability effects design choices.
Running Continuously
![Page 6: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/6.jpg)
6
Continuous Availability
+ Commercial Availability - routine outages▪ 90% 2 hours down/day - nightly batch jobs ▪ 99% 1 hour down/week - weekly backups▪ 99.9% 1 hour down/month - monthly reorganization
+ High Availability - occasional outages▪ 99.95% 1 hour down/quarter - quarterly upgrade▪ 99.99% 1 hour down/year - reboot after failure,
+ Continuous Availability - no scheduled outages▪ 99.999%5 minutes down/year - automated switch over
99.9995% 2 minutes down/year - Project Goal99.9999% 30 seconds down/year - Wow
+ Perfect Availability - no visible failures▪ 99.99999% failure is never visible to work in progress
![Page 7: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/7.jpg)
7
Topology
Prime processes changesand manages data consistency+ Prime performs all changes.
▪ If the Prime fails, a Mirror is automatically selected to be promoted to Prime .
+ Mirrors confirm persistence.▪ Provide persistence in memory.▪ Must include a separate site.
+ Guard only writes log to disk.▪ Protects against poison data.▪ Not subject to errors in the
database logic.
+ Replicas support massiveread-only access.▪ Replicas don’t vote, which
simplifies recovery analysis.▪ Replicas can be promoted to
Mirror when needed.
Prime
Guard
ReplicaReplica
Mirror MirrorMirror
ReplicaReplica
Replicassupport
unlimitedread access
Mirrorsconfirm
persistence
Guard defends against poison data
As many levels as desired
![Page 8: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/8.jpg)
8
Agenda
1 VeriSign High Availability Database Background (VHAD) – Simplicity
2 Why Choose Lua for Stored Procedures?
3 VHAD Integrated Development Environment – IDE
![Page 9: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/9.jpg)
9
Rationale for Choosing Lua for Stored Procedures
+ Lua shares major VHAD design principle – keep it small and simple.
+ Lua is mature and very stable – critical core code is not expanding.
+ Most databases realistically require a service outage when a stored procedure is added or modified, and that change isn’t transactional.
+ VHAD requires more dynamic behavior there, because anything that compromises even a single transaction is considered an outage.
+ Lua gives us the dynamic behavior we need – different interpreters can have different code in use simultaneously for a procedure name.
+ Highly parallel operation is needed: thousands of threads at once – Lua interpreter is fully thread safe and reentrant, unlike most others.
+ Lua Garbage Collection via interpreter heap – it’s a huge advantage not to have to explicitly deallocate or use reference counts.
+ Efficient direct access to C++ objects gives a big speed advantage.
![Page 10: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/10.jpg)
10
Cursor Example – The Schema in SQL
+ Assume there is a database table like the following:▪ CREATE TABLE DOMAIN_T (
DOMAINNAME VARCHAR(256), REGISTRAR VARCHAR(256), DATE TIMESTAMP);
![Page 11: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/11.jpg)
11
Cursor Example – Userdata and Metatable
+ One of the basic types that Lua supports is userdata.
+ Lua provides APIs to create userdata types and associate a
metatable of metamethods to a userdata type:▪ Static const struct luaL_reg cursor_metatable[] = {
{“__newindex”, setColumn},
{“__index”, getColumn},
{“__gc”, cleanup},
{NULL, NULL}
}▪ luaL_newmetatable(lstate, “Session.Cursor”)▪ luaL_register(lstate, NULL, cursor_metatable)
+ Associating metamethods to userdata:▪ luaL_getmetatable(lstate, “Session.Cursor”)▪ Lua_setmetatable(lstate, -2)
![Page 12: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/12.jpg)
12
Cursor Example - Lua Stored Procedure
+ A sample metamethod in C:int setColumn (lua_state *lstate) {
luaL_checkudata(lstate, 1, “Session.Cursor”)colName = lua_tostring(lstate, 2)colData = lua_tonumber(lstate, 3)
}
+ A sample stored procedure that uses the Cursor type:function INSERT_DOMAIN (args)
domain = Cursor.new (“REGY”, “DOMAIN”)
domain.DOMAINNAME = args.DOMAINNAMEdomain.RESISTRAR = args.REGISTRARdomain.DATE = args.DATECursor.insert(domain)commit()
end
![Page 13: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/13.jpg)
13
Agenda
VeriSign High Availability Database (VHAD) Background – Simplicity
Why Choose Lua for Stored Procedures?
VHAD Integrated Development Environment – IDE
![Page 14: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/14.jpg)
14
VHAD Integrated Development Environment - IDE
+ Need an environment that lets a user:▪ Write a stored procedure▪ Load a stored procedure dynamically▪ Debug a stored procedure▪ List schemas, tables, column values, etc.▪ Rapid prototyping of stored procedures
+ Developing a database tool like sqlplus – but better▪ Display schemas, stored procedures, etc.▪ Display columnar data▪ Execute a stored procedure▪ Sqlplus does not have a stored procedure debugger!
![Page 15: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/15.jpg)
15
ldb
+ Developed a command line Lua debugger - ldb
+ The ldb language▪ list firstline [lastline]▪ break line ▪ clear line ▪ display [varname] ▪ step ▪ continue ▪ print stack|trace|breakpoints ▪ quit ▪ help
![Page 16: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/16.jpg)
16
ldb Example
ldb> break 11
setting breakpoint at ./sort.lua line 11
11 local i=l+1
ldb> continue
original
Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
stopping at breakpoint in ./sort.lua line 11
11 local i=l+1
ldb> display m
Find local name m:
name m, type number, value 1.000000
Find global m:
Did not find global m
![Page 17: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/17.jpg)
17
ldb Example - continued
ldb> print stack
7: 2
6:`Jan'
5: 1
4: function
3: 12
2: 1
1: table
ldb> print trace
level# name,type,language,source,currentline,linedefinedat
0# qsort,global,Lua,./sort.lua,12,5
1# testsorts,global,Lua,./sort.lua,55,51
2# ,,main,./sort.lua,66,0
![Page 18: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/18.jpg)
18
Conclusion – Difficulties Faced
+ Execution of stored procedures from memory.
+ Thread pools of Lua_states are reused.
+ Decimal adaptation and other numeric types.
+ Debugging stored procedures.
![Page 19: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/19.jpg)
19
Questions + Answers
![Page 20: Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008](https://reader035.vdocuments.us/reader035/viewer/2022062618/551488c2550346f06e8b4da0/html5/thumbnails/20.jpg)
Thank You
"Make everything as simple as possible, but not simpler." Albert Einstein