Using Lua for Stored Procedures in a High

Using Lua for Stored Procedures in a High Availability Database Veri. Sign I 2 S Team Name of Presenter: John Rodriguez Date: July 14, 2008

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

Introduction + Veri. Sign’s mission is making the Internet and telecommunications networks more reliable, more intelligent, and more secure. + Veri. Sign 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, memorybased 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? ▪ ▪ 3 Carefully, aggressively simplify the design and architecture. Target only the critical subset of system functionality.

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 (~ 50 K/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. 4

Running Continuously + What kinds of things don’t stop but just keep going, going…? ▪ The moon keeps orbiting. ▪ Simple tools just work. ▪ What makes them different from things that break, or must 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. 5

Continuous Availability + Commercial Availability - routine outages + High Availability - occasional outages + Continuous Availability - no scheduled outages ▪ 90% ▪ 99. 9% ▪ 99. 95% ▪ 99. 99% 2 hours down/day 1 hour down/week 1 hour down/month 1 hour down/quarter 1 hour down/year ▪ 99. 999% 5 minutes down/year 99. 9995% 2 minutes down/year 99. 9999% 30 seconds down/year + Perfect Availability - nightly batch jobs - weekly backups - monthly reorganization - quarterly upgrade - reboot after failure, - automated switch over - Project Goal - Wow - no visible failures ▪ 99. 99999% failure is never visible to work in progress 6

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 massive read-only access. ▪ ▪ 7 Replicas don’t vote, which simplifies recovery analysis. Replicas can be promoted to Mirror when needed. Prime processes changes and manages data consistency Mirrors confirm persistence Guard defends against poison data Prime Guard Mirror Replicas support unlimited read access Replica Mirror Replica As many levels as desired Replica

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

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

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

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 lua. L_reg cursor_metatable[] = { {“__newindex”, set. Column}, {“__index”, get. Column}, {“__gc”, cleanup}, {NULL, NULL} } ▪ lua. L_newmetatable(lstate, “Session. Cursor”) ▪ lua. L_register(lstate, NULL, cursor_metatable) + Associating metamethods to userdata: ▪ lua. L_getmetatable(lstate, “Session. Cursor”) ▪ Lua_setmetatable(lstate, -2) 11

Cursor Example - Lua Stored Procedure + A sample metamethod in C: int set. Column (lua_state *lstate) { lua. L_checkudata(lstate, 1, “Session. Cursor”) col. Name = lua_tostring(lstate, 2) col. Data = 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 12

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

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! 14

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 15

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 16

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 17

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

Questions + Answers 19

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