Using Lua for Stored Procedures in a High Availability Database

1. Using Lua for Stored Procedures in a High Availability Database VeriSign I2S Team Name of Presenter: John Rodriguez Date: July 14, 2008

2. Agenda 1 VeriSign High Availability Database Background (VHAD) – Simplicity 2 Why Choose Lua for Stored Procedures? 3 VHAD Integrated Development Environment – IDE

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.

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.

5. Running Continuously • What kinds of things don’t stop but just keep going, going, going…? • The moon keeps orbiting. • Simple tools just work. • What makes them differentfrom 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.

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 over99.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

7. Prime Guard Replica Replica Mirror Mirror Mirror Replica Replica Topology • 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 processes changesand manages data consistency Guard defends against poison data Mirrorsconfirmpersistence Replicassupport unlimitedread access As many levels as desired

8. Agenda 1 VeriSign High Availability Database Background (VHAD) – Simplicity 2 Why Choose Lua for Stored Procedures? 3 VHAD Integrated Development Environment – IDE

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.

10. Cursor Example – The Schema in SQL • Assume there is a database table like the following: • CREATE TABLE DOMAIN_T (DOMAINNAME VARCHAR(256), REGISTRARVARCHAR(256), DATE TIMESTAMP);

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)

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.DOMAINNAME domain.RESISTRAR = args.REGISTRAR domain.DATE = args.DATE Cursor.insert(domain) commit() end

13. Agenda VeriSign High Availability Database (VHAD) Background – Simplicity Why Choose Lua for Stored Procedures? VHAD Integrated Development Environment – IDE

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!

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

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

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

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.

19. Questions + Answers

20. Thank You "Make everything as simple as possible, but not simpler." Albert Einstein