## Storage Size 1 million objects => 540mb ( based on ~= 4.2 million objects => 2.3GB not gzipped) 1 million objects => 127mb gzipped 4x reduction in space ## Local storage takeaways: SQLite optimisations make a difference in insertion speed. Insertion speed does slow down on large tables (+1m rows). Partioned tables (by, for example, the first two decimals of the hash) have slower but predictable insertion speed. Not sure if compromise is worth it? ## Even More Optimised single object table Optimisations are: - `PRAGMA journal_mode = MEMORY;` - `PRAGMA synchronous = OFF;` - `PRAGMA count_changes=OFF;` - `PRAGMA temp_store=MEMORY;` ### Test 1 ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 2286 ms -> 50000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 1426 ms -> 100000 objects per second ------------------------------------------------- ### Test 2 ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 3052 ms -> 33333.333333333336 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 2244 ms -> 50000 objects per second ------------------------------------------------- ### Test 3 ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 4941 ms -> 25000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 2555 ms -> 50000 objects per second ------------------------------------------------- ### Test 4 ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 8022 ms -> 12500 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 3350 ms -> 33333.333333333336 objects per second ------------------------------------------------- ### Test 5 ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 6602 ms -> 16666.666666666668 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 3445 ms -> 33333.333333333336 objects per second ------------------------------------------------- ### Test 5+: A couple of more rounds, pushing objs to 2.000k ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 7332 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7625 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 7539 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 4249 ms -> 25000 objects per second ------------------------------------------------- ------------------------------------------------- ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 8300 ms -> 12500 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7289 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 8668 ms -> 12500 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 8060 ms -> 12500 objects per second ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 10228 ms -> 10000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 11475 ms -> 9090.90909090909 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 12540 ms -> 8333.333333333334 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7113 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 17153 ms -> 5882.35294117647 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 5997 ms -> 20000 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 20841 ms -> 5000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 9195 ms -> 11111.111111111111 objects per second ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 13404 ms -> 7692.307692307692 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7529 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 19806 ms -> 5263.1578947368425 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7318 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 24612 ms -> 4166.666666666667 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7410 ms -> 14285.714285714286 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 22257 ms -> 4545.454545454545 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 18699 ms -> 5555.555555555556 objects per second ------------------------------------------------- ------------------------------------------------- Starting to save 100000 of objects ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 20947 ms -> 5000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 14089 ms -> 7142.857142857143 objects per second ------------------------------------------------- ## Optimised single object table Optimisations are: `PRAGMA journal_mode = MEMORY;` and `PRAGMA synchronous = OFF;` ### Test 1: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 2267 ms -> 50000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 1327 ms -> 100000 objects per second ------------------------------------------------- ### Test 2: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 4532 ms -> 25000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 2243 ms -> 50000 objects per second ------------------------------------------------- ### Test 3: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 3768 ms -> 33333.333333333336 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 5295 ms -> 20000 objects per second ------------------------------------------------- ### Test 4: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 4033 ms -> 25000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 3126 ms -> 33333.333333333336 objects per second ------------------------------------------------- ### Test 5: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 4432 ms -> 25000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 3527 ms -> 33333.333333333336 objects per second ------------------------------------------------- ## Single object table ### Test 1: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 11964 ms -> 9090.90909090909 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 6875 ms -> 16666.666666666668 objects per second ------------------------------------------------- 200k total in db ### Test 2: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 21956 ms -> 4761.9047619047615 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 8904 ms -> 12500 objects per second ------------------------------------------------- 400k total in db ### Test 3: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 25532 ms -> 4000 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 10124 ms -> 10000 objects per second ------------------------------------------------- 600k total in db ### Test 4: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 26629 ms -> 3846.153846153846 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 10610 ms -> 10000 objects per second ------------------------------------------------- 800k total in db ### Test 5: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 26956 ms -> 3846.153846153846 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 11007 ms -> 9090.90909090909 objects per second ------------------------------------------------- 1000k total in db ## Bucketed Object Table (256 individual tables for objects): Pre-generate 256 tables, of form `objs${prefix}`, where prefix is the cartesian product of all the valid hex decimals (`0-9, a-f`). ### Test 1: Forgot to copy paste. 200k total in db ### Test 2: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 19096 ms -> 5263.1578947368425 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 7401 ms -> 14285.714285714286 objects per second ------------------------------------------------- 400k total in db ### Test 3: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 22477 ms -> 4545.454545454545 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 8668 ms -> 12500 objects per second ------------------------------------------------- 600k total in db ### Test 4: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 23438 ms -> 4347.826086956522 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 9288 ms -> 11111.111111111111 objects per second ------------------------------------------------- 800k total in db ### Test 5: ------------------------------------------------- BufferedWriteTest: Wrote 100000 in 23735 ms -> 4347.826086956522 objects per second ------------------------------------------------- ------------------------------------------------- BulkWriteMany: Wrote 100000 in 9944 ms -> 11111.111111111111 objects per second ------------------------------------------------- 1mil total in db