nla/amberdb — pull request #244 by ato (2014-11-18 23:26:18+00:00)
Title: Use the MySQL memory engine for temporary tables

By default MySQL uses the regular storage engine (MyISAM or InnoDB) for temporary tables (presumably so they can be larger than RAM). However there's a considerable amount of latency to create or delete a disk backed table, frequently up to 50ms just for one operation.

This commit switches all our CREATE TEMPORARY TABLE statements to use the memory engine when running on MySQL.

Testing against the production database I found a massive speedup for for basic traversals, dropping query times down to the submilliseconds, in some particular cases as much as 400x.

Here's an example of `copy.getFile()`:

Before:

```
Query_ID Duration Query
2 0.07938475 CREATE TEMPORARY TABLE v0 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT)
3 0.00043100 DROP TABLE IF EXISTS v1
4 0.00581275 CREATE TEMPORARY TABLE v1 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT)
5 0.00058400 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 0, id, 0, 'root', 0 \nFROM vertex \nWHERE id IN (761808) \nAND txn_end = 0
6 0.00035350 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0
7 0.00065950 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 1, v.id, e.id, e.label, e.edge_order \nFROM vertex v, edge e, v1 \nWHERE e.txn_end = 0 \n AND v.txn_end = 0 \n AND e.label IN ('isFileOf') \n AND (e.v_out = v.id AND e.v_in = v1.vid)\n AND v1.step = 0
8 0.00035425 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0 \nWHERE v0.step = 1
9 0.04884925 DROP TABLE IF EXISTS v0
10 0.00592250 CREATE TEMPORARY TABLE v0 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT)
11 0.04911875 DROP TABLE IF EXISTS v1
12 0.00522050 CREATE TEMPORARY TABLE v1 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT)
13 0.00047600 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 0, id, 0, 'root', 0 \nFROM vertex \nWHERE id IN (761808) \nAND txn_end = 0
14 0.00021400 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0
15 0.00062675 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 1, v.id, e.id, e.label, e.edge_order \nFROM vertex v, edge e, v1 \nWHERE e.txn_end = 0 \n AND v.txn_end = 0 \n AND e.label IN ('isFileOf') \n AND (e.v_out = v.id AND e.v_in = v1.vid)\n AND v1.step = 0
16 0.00030575 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0 \nWHERE v0.step = 1

Total: 198 ms
```

After:

```
Query_ID Duration Query
2 0.00068725 CREATE TEMPORARY TABLE v0 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT) ENGINE=memory
3 0.00014625 DROP TABLE IF EXISTS v1
4 0.00069725 CREATE TEMPORARY TABLE v1 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT) ENGINE=memory
5 0.00038325 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 0, id, 0, 'root', 0 \nFROM vertex \nWHERE id IN (761808) \nAND txn_end = 0
6 0.00024450 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0
7 0.00051950 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 1, v.id, e.id, e.label, e.edge_order \nFROM vertex v, edge e, v1 \nWHERE e.txn_end = 0 \n AND v.txn_end = 0 \n AND e.label IN ('isFileOf') \n AND (e.v_out = v.id AND e.v_in = v1.vid)\n AND v1.step = 0
8 0.00025325 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0 \nWHERE v0.step = 1
9 0.00016725 DROP TABLE IF EXISTS v0
10 0.00071475 CREATE TEMPORARY TABLE v0 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT) ENGINE=memory
11 0.00023275 DROP TABLE IF EXISTS v1
12 0.00066600 CREATE TEMPORARY TABLE v1 (step INT, vid BIGINT, eid BIGINT, label VARCHAR(100), edge_order BIGINT) ENGINE=memory
13 0.00036725 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 0, id, 0, 'root', 0 \nFROM vertex \nWHERE id IN (761808) \nAND txn_end = 0
14 0.00023700 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0
15 0.00052450 INSERT INTO v0 (step, vid, eid, label, edge_order) \nSELECT 1, v.id, e.id, e.label, e.edge_order \nFROM vertex v, edge e, v1 \nWHERE e.txn_end = 0 \n AND v.txn_end = 0 \n AND e.label IN ('isFileOf') \n AND (e.v_out = v.id AND e.v_in = v1.vid)\n AND v1.step = 0
16 0.00026575 INSERT INTO v1 (step, vid, eid, label, edge_order) \nSELECT step, vid, eid, label, edge_order \nFROM v0 \nWHERE v0.step = 1

Total: 6 ms
```