Skip to content

Instantly share code, notes, and snippets.

@a2ndrade
Last active August 5, 2016 15:44
Show Gist options
  • Save a2ndrade/d95eb35b6bd419dfd6ab to your computer and use it in GitHub Desktop.
Save a2ndrade/d95eb35b6bd419dfd6ab to your computer and use it in GitHub Desktop.
Atomic
\c 15 300i
/ c: count | th: table handle | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
/ source: "abcdefghijklmnopqrstuvwxyz" (size=26)
/ input: (# of groups) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
/ output (# of handles): 676 169 81 49 36 25 16 16 9 9 9 9 4 4 4 4 4 4 4 4 4 4 4 4 4 1
getHdbTableHandles:{[crossProductAlphabetSize]`$(":hdb/",/:raze g,\:/:g:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"]),'"/"}
getAllHdbTableHandles:{
/:raze getHdbTableHandles each 1+til 26 / all tables ~1200
/:raze getHdbTableHandles each 4 5 / 74 tables
/:raze getHdbTableHandles each 25 26 / 5 tables
`:hdb/aa/`:hdb/bb/}
resetHdb:{resetHdbTables each getAllHdbTableHandles[]}
resetHdbTables:{[th].[;();:;([] e:`long$(); v:`char$(); `s#t:`long$())] each th}
appendToHdbTableFrom:{[th;c;f].[th;();,;([]e:c?100000*count f;v:c?f;t:(1+$[0~count tx:select [-1] t from th;0;first tx`t]))]}
appendToHdbTable:appendToHdbTableFrom[;;"abcdefghijklmnopqrstuvwxyz"]
appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}
generateRandomHdbTableFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]
/////////////////////////////////////////////////////////////////////////////
/ Historical DB (sync or async write?)
/ RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
/ ~1157 HDB Tables;
resetHdb[]
/q)\t appendToAllHdbTables[1]
/323
/q)\t appendToAllHdbTables[1]
/308
/q)\t appendToAllHdbTables[1]
/287
/q)\t appendToAllHdbTables[1]
/288
/q)\t appendToAllHdbTables[1]
/292
/q)\t appendToAllHdbTables[10]
/414
/q)\t appendToAllHdbTables[10]
/414
/q)\t appendToAllHdbTables[10]
/419
/q)\t appendToAllHdbTables[100]
/421
/q)\t appendToAllHdbTables[100]
/424
/q)\t appendToAllHdbTables[100]
/438
/q)\t appendToAllHdbTables[1000]
/696
/q)\t appendToAllHdbTables[1000]
/800
/q)\t appendToAllHdbTables[1000]
/739
/q)\t appendToAllHdbTables[1000]
/805
\t appendToAllHdbTables[1000000] / 1 million EVT rows each; total space 19.8GB
/214871 3.5min
/q)\t appendToAllHdbTables[1]
/471
/q)\t appendToAllHdbTables[1]
/479
/q)\t appendToAllHdbTables[1]
/468
/q)\t appendToAllHdbTables[1]
/481
/q)\t appendToAllHdbTables[10]
/508
/q)\t appendToAllHdbTables[10]
/481
/q)\t appendToAllHdbTables[10]
/479
/q)\t appendToAllHdbTables[10]
/479
/q)\t appendToAllHdbTables[10]
/477
/q)\t appendToAllHdbTables[100]
/715
/q)\t appendToAllHdbTables[100]
/829
/q)\t appendToAllHdbTables[100]
/728
/q)\t appendToAllHdbTables[100]
/733
/q)\t appendToAllHdbTables[1000]
/883
/q)\t appendToAllHdbTables[1000]
/858
/q)\t appendToAllHdbTables[1000]
/1062
/q)\t appendToAllHdbTables[1000]
/918
/q)\t appendToAllHdbTables[1000]
/918
/////////////////////////////////////////////////////////////////////////////
/ Background Engine
generateSnapshotTable:{[th;tx]tx:$[-1~tx;0Wj;tx];() xkey update `u#e from `v xasc select last v by e from th where t <= tx}
persistSnapshotTable:{[th;tx].[0N!`$ssr[string th;"hdb";"rtdb"];();:;generateSnapshotTable[th;tx]];}
// tables are written one at a time b/c we cannot fit all of them in memory
persistAllSnapshotTables:{[tx]do[c:count ths:getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;persistSnapshotTable[th;tx]]}
\t persistAllSnapshotTables[-1]
/756940 12mins
/q)\w
/125152 939524096 939524096 0 0 17179869184
/q).Q.gc[]
/872415232
/q)\w
/125152 67108864 939524096 0 0 17179869184
/////////////////////////////////////////////////////////////////////////////
/ Real-time DB
/ source: "abcdefghijklmnopqrstuvwxyz" (size=26)
/ input: (# of groups) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
/ output (# of handles): 676 169 81 49 36 25 16 16 9 9 9 9 4 4 4 4 4 4 4 4 4 4 4 4 4 1
getRtdbTableHandles:{[crossProductAlphabetSize]`$(":rtdb/",/:raze g,\:/:g:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"])}
getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}
/ range queries / paging
/q)a:get `:rtdb/aa
/q)\t:100 select from a where v="f"
/3
/q)b:`e xasc a
/q)\t:100 select from b where v="f"
/255
/q)\t:1000 select [25] from a where v="l"
/3
/[A\t:1000 select [25] from b where v="l"
/2108
/ 6-way inner join; >700,000 records each table
/ a:get `:rtdb/aa
/ b:([e:`long$((count a)?100000*26)] v:`a$exec e from a)
/ c:([e:`long$((count b)?100000*26)] v:`b$exec e from b)
/ d:([e:`long$((count c)?100000*26)] v:`c$exec e from c)
/ e:([e:`long$((count d)?100000*26)] v:`d$exec e from d)
/ f:([e:`long$((count e)?100000*26)] v:`e$exec e from e)
/ g:([e:`long$((count f)?100000*26)] v:`f$exec e from f)
/q)select e, v, v.e, v.v, v.v.e, v.v.v, v.v.v.e, v.v.v.v, v.v.v.v.e, v.v.v.v.v, v.v.v.v.v.e, v.v.v.v.v.v, v.v.v.v.v.v.e, v.v.v.v.v.v.v from g where v.v.v.v.v.v.v="a"
/e v e1 v1 e2 v2 e3 v3 e4 v4 e5 v5 e6 v6
/----------------------------------------------------------------------------------------------------
/2551962 1212660 1212660 562791 562791 302528 302528 1944305 1944305 226886 226886 59 59 a
/330811 2425946 2425946 682145 682145 2557747 2557747 2130724 2130724 1984860 1984860 167 167 a
/604934 138123 138123 844191 844191 1274836 1274836 1442355 1442355 75099 75099 173 173 a
/537687 237218 237218 2496620 2496620 1584186 1584186 116213 116213 1680022 1680022 226 226 a
/2241894 1304112 1304112 2211893 2211893 1443621 1443621 405374 405374 1498402 1498402 246 246 a
/2597440 604817 604817 2206476 2206476 954912 954912 1965228 1965228 2141420 2141420 620 620 a
/2048682 1200358 1200358 2496026 2496026 2321398 2321398 1250701 1250701 282990 282990 691 691 a
/2260400 1829468 1829468 1918513 1918513 665858 665858 1711819 1711819 2107293 2107293 760 760 a
/2062671 958318 958318 1137506 1137506 1255301 1255301 1676709 1676709 623108 623108 763 763 a
/1484085 1068714 1068714 432969 432969 255655 255655 1150057 1150057 771105 771105 773 773 a
/802785 923265 923265 1809151 1809151 837898 837898 1219314 1219314 1733637 1733637 795 795 a
/342155 1008619 1008619 395634 395634 185095 185095 1831358 1831358 1631805 1631805 900 900 a
/842763 1278366 1278366 1914570 1914570 1728390 1728390 2265205 2265205 1694638 1694638 997 997 a
/1955063 2085073 2085073 68989 68989 1868701 1868701 1987359 1987359 1107222 1107222 1014 1014 a
/2184095 743466 743466 1413979 1413979 2479078 2479078 2530141 2530141 769094 769094 1036 1036 a
/2348590 339783 339783 1682360 1682360 1386295 1386295 1380836 1380836 2493437 2493437 1039 1039 a
/2551811 2443367 2443367 603514 603514 1231797 1231797 2401962 2401962 1398119 1398119 1093 1093 a
/1703181 2382242 2382242 664449 664449 1002290 1002290 2405670 2405670 305844 305844 1180 1180 a
/1958907 1489006 1489006 2584627 2584627 1860499 1860499 2304432 2304432 1498434 1498434 1257 1257 a
/1589195 324759 324759 789424 789424 1047664 1047664 1084867 1084867 1206856 1206856 1339 1339 a
/..
/q).Q.w[]
/used| 135396288 / 135MB
/heap| 201326592 / 200MB
/peak| 268435456
/wmax| 0
/mmap| 0
/mphy| 17179869184
/syms| 1768
/symw| 68991
/q)\t select e, v, v.e, v.v, v.v.e, v.v.v, v.v.v.e, v.v.v.v, v.v.v.v.e, v.v.v.v.v, v.v.v.v.v.e, v.v.v.v.v.v, v.v.v.v.v.v.e, v.v.v.v.v.v.v from g where v.v.v.v.v.v.v="a"
/71
/////////////////////////////////////////////////////////////////////////////
/ Earlier TESTS
/ 169 Tables
/ 169 million rows appended in 22 seg. total ~2.87 GB (169 tables, 1 million each)
/ after that, 10 rows are appended to all 169 tables in 50-200 ms
/ 25 Tables
/ 25 million rows appended in 3.7 seg. total ~425MB (25 tables, 1 million each)
/ after that, 10 rows are appended to all 25 tables in ~25ms
/ 9 Tables
/ 9 million rows appended in 1.5 seg. total ~153MB (9 tables, 1 million each)
/ after that, 10 rows are appended to all 9 tables in ~8ms
/ 4 Tables
/ 4 million rows appended in 0.6 seg. total ~68MB (4 tables, 1 million each)
/ after that, 10 rows are appended to all 4 tables in ~4ms
/ Throughput : 10-250 writes per second ****w/o transaction log****
////////////////////////////////////////////////////////////////////////////////////
/ Experimental
/ ([] e:`long$(); v:`a$`e!`long$(); t:`long$())
/ value flip select e from a
/ {[th;c;f]([e:`long$()] v:`long$())}[`;10;"abcdefghijklmnopqrstuvwxyz"]
/getHdbTableTypeFrom:{[th;types]n:ssr[-1_string th;":hdb/";""];m:((*) over `int$n) mod count types;types@m}
/getHdbTableType:getHdbTableTypeFrom[;`boolean`int`long`float`datetime]
/findForeignKeyTableHandle:{[th;prefix;suffix]$[(not "aa"~s)&((~)over s)&2~count s:ssr[(-1*count suffix)_string th;prefix;""];`$prefix,2#`char$-1+`int$s@0,suffix;`]}
/findSnapshotTableForeignKey:findForeignKeyTableHandle[;":rtdb/";""]
/getSnapshotTableTypeFrom:{[th;types]n:ssr[string th;":rtdb/";""];m:((*) over `int$n) mod count types;$[`fk~t:types@m;`fk;t]}
/getSnapshotTableType:getSnapshotTableTypeFrom[;`int`float`datetime]
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/ all operations with 1 million rows
/q)\t ka:`e xkey select from `:rtdb/aa/
/2
/q)\t kb:`e xkey select from `:rtdb/bb/
/4
/.Q.w[]`mmap > 0, i.e. ka & kb are memory-mapped, not loaded into memory!
/\t ij[ka;kb]
/~254 for hdb; ~167 for rtdb
/ids:100?exec e from ka
/q)\t:100 select from ka where e in ids
/23 /cold + `u#e
/q)\t:100 select from ka where e in ids
/4 /hot + `u#e
/q)\t:100 select from `:rtdb/aa/ where e in ids
/49 / with `u#e
/q)\t:100 select from `:rtdb/aa/ where e in ids
/1862 / without `u#e
/q)\t:100 select count v from `:rtdb/aa/ where v in ("aeiou")
/44
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment