Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Appendix for "Consider SQLite"

kvtest

Patch to apply to ktest.c @ d9f814a6:

927,928c927,929
<     sqlite3_exec(db, "PRAGMA synchronous=normal", 0, 0, 0);
<     sqlite3_exec(db, "PRAGMA temp_store=memory", 0, 0, 0);
---
>     if( noSync ){
>       sqlite3_exec(db, "PRAGMA synchronous=OFF", 0, 0, 0);
>     }
1029c1030
<             "UPDATE kv SET v=randomblob(remember(512,?2))"
---
>             "UPDATE kv SET v=randomblob(remember(length(v),?2))"

(Change 512 to 32000 for the 32kb test)

GCC info:

$ gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c -o kvtest -ldl -lpthread
$ gcc --version
gcc (Debian 8.3.0-6) 8.3.0

On the Equinix m3.large.x86 instance, on ext4 (mount flags: rw,realtime, the default on Debian) on the NVMe drive, I get:

Writing 512 bytes:

SQLite version: 3.37.0
--count 100000 --max-id 1000 --asc
--cache-size 1000 --jmode wal
--mmap -64771072
Database page size: 4096
Total elapsed time: 1.378
Microseconds per BLOB write: 13.780
Content write rate: 37.2 MB/s

Writing 32kb:

SQLite version: 3.37.0
--count 100000 --max-id 1000 --asc
--cache-size 1000 --jmode wal
--mmap -64771072
Database page size: 4096
Total elapsed time: 1.378
Microseconds per BLOB write: 13.780
Content write rate: 37.2 MB/s

Reading 512 bytes:

SQLite version: 3.37.0
--count 1000000 --max-id 1000 --asc
--cache-size 1000 --jmode wal
--mmap 1316134912
Database page size: 4096
Total elapsed time: 2.092
Microseconds per BLOB read: 2.092
Content read rate: 244.7 MB/s

On the DigitalOcean machine, I got:

Writing 512 bytes:

SQLite version: 3.37.0
--count 1000000 --max-id 1000 --asc
--cache-size 1000 --jmode wal
--mmap 1316134912
Database page size: 4096
Total elapsed time: 35.217
Microseconds per BLOB write: 35.217
Content write rate: 14.5 MB/s

Reading 512 bytes:

SQLite version: 3.37.0
--count 1000000 --max-id 1000 --asc
--cache-size 1000 --jmode wal
--mmap 1316134912
Database page size: 4096
Total elapsed time: 3.340
Microseconds per BLOB read: 3.340
Content read rate: 153.3 MB/s

speedtest1

I applied the following patch to speedtest1 @ d9f814a6 to allow it to compile on both old and new SQLite:

1817c1817,1818
<           sqlite3_bind_text(g.pStmt, j+2, zNum, len, SQLITE_STATIC);
---
>           sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
>                               SQLITE_STATIC, SQLITE_UTF8);
2383a2385,2387
>   if( g.nReserve>0 ){
>     sqlite3_file_control(g.db, 0, SQLITE_FCNTL_RESERVE_BYTES, &g.nReserve);
>   }

And got the following results:

AlphaVPS:

root@alpha-776390bfd2:~# gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ speedtest1.c sqlite3.c -o speedtest1 -ldl -lpthread
root@alpha-776390bfd2:~# gcc --version
gcc (Debian 8.3.0-6) 8.3.0
Copyright (C) 2018 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

root@alpha-776390bfd2:~# ./speedtest1 
-- Speedtest1 for SQLite 3.6.23.1 2010-03-26 22:28:06 b078b588d617e07886ad156e9f54
 100 - 50000 INSERTs into table with no index......................    0.291s
 110 - 50000 ordered INSERTS with one index/PK.....................    0.462s
 120 - 50000 unordered INSERTS with one index/PK...................    0.588s
 130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.616s
 140 - 10 SELECTS, LIKE, unindexed.................................   27.065s
 142 - 10 SELECTS w/ORDER BY, unindexed............................    1.974s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    1.036s
 150 - CREATE INDEX five times.....................................    1.655s
 160 - 10000 SELECTS, numeric BETWEEN, indexed.....................    0.534s
 161 - 10000 SELECTS, numeric BETWEEN, PK..........................    0.556s
 170 - 10000 SELECTS, text BETWEEN, indexed........................    1.006s
 180 - 50000 INSERTS with three indexes............................    1.089s
 190 - DELETE and REFILL one table.................................    1.210s
 200 - VACUUM......................................................    3.046s
 210 - ALTER TABLE ADD COLUMN, and query...........................    0.026s
 230 - 10000 UPDATES, numeric BETWEEN, indexed.....................    0.689s
 240 - 50000 UPDATES of individual rows............................    0.853s
 250 - One big UPDATE of the whole 50000-row table.................    0.255s
 260 - Query added column after filling............................    0.029s
 270 - 10000 DELETEs, numeric BETWEEN, indexed.....................    1.061s
 280 - 50000 DELETEs of individual rows............................    0.934s
 290 - Refill two 50000-row tables using REPLACE...................    2.246s
 300 - Refill a 50000-row table using (b&1)==(a&1).................    1.143s
 310 - 10000 four-ways joins.......................................    1.932s
 320 - subquery in result set......................................    2.679s
 400 - 70000 REPLACE ops on an IPK.................................    0.568s
 410 - 70000 SELECTS on an IPK.....................................    0.300s
 500 - 70000 REPLACE on TEXT PK....................................    1.184s
 510 - 70000 SELECTS on a TEXT PK..................................    0.620s
 520 - 70000 SELECT DISTINCT.......................................    0.403s
 980 - PRAGMA integrity_check......................................    2.238s
 990 - ANALYZE.....................................................    0.303s
       TOTAL.......................................................   58.591s
root@alpha-776390bfd2:~# cat /proc/cpuinfo 
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 6
model		: 45
model name	: Intel(R) Xeon(R) CPU E5-2450L 0 @ 1.80GHz
stepping	: 7
microcode	: 0x1
cpu MHz		: 1795.641
cache size	: 16384 KB
physical id	: 0
siblings	: 1
core id		: 0
cpu cores	: 1
apicid		: 0
initial apicid	: 0
fpu		: yes
fpu_exception	: yes
cpuid level	: 13
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology cpuid tsc_known_freq pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx hypervisor lahf_lm pti ssbd ibrs ibpb stibp tsc_adjust xsaveopt arat md_clear
bugs		: cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs
bogomips	: 3591.28
clflush size	: 64
cache_alignment	: 64
address sizes	: 46 bits physical, 48 bits virtual
power management:

DigitalOcean:

root@debian-s-1vcpu-1gb-sgp1-01:~# gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ speedtest1.c sqlite3.c -o speedtest1 -ldl -lpthread
root@debian-s-1vcpu-1gb-sgp1-01:~# ./speedtest1 
-- Speedtest1 for SQLite 3.37.0 2021-11-27 14:13:22 bd41822c7424d393a30e92ff6cb2
 100 - 50000 INSERTs into table with no index......................    0.050s
 110 - 50000 ordered INSERTS with one index/PK.....................    0.074s
 120 - 50000 unordered INSERTS with one index/PK...................    0.102s
 130 - 25 SELECTS, numeric BETWEEN, unindexed......................    0.086s
 140 - 10 SELECTS, LIKE, unindexed.................................    0.126s
 142 - 10 SELECTS w/ORDER BY, unindexed............................    0.208s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..................    0.102s
 150 - CREATE INDEX five times.....................................    0.153s
 160 - 10000 SELECTS, numeric BETWEEN, indexed.....................    0.077s
 161 - 10000 SELECTS, numeric BETWEEN, PK..........................    0.078s
 170 - 10000 SELECTS, text BETWEEN, indexed........................    0.239s
 180 - 50000 INSERTS with three indexes............................    0.191s
 190 - DELETE and REFILL one table.................................    0.197s
 200 - VACUUM......................................................    0.232s
 210 - ALTER TABLE ADD COLUMN, and query...........................    0.004s
 230 - 10000 UPDATES, numeric BETWEEN, indexed.....................    0.090s
 240 - 50000 UPDATES of individual rows............................    0.190s
 250 - One big UPDATE of the whole 50000-row table.................    0.023s
 260 - Query added column after filling............................    0.005s
 270 - 10000 DELETEs, numeric BETWEEN, indexed.....................    0.383s
 280 - 50000 DELETEs of individual rows............................    0.281s
 290 - Refill two 50000-row tables using REPLACE...................    0.427s
 300 - Refill a 50000-row table using (b&1)==(a&1).................    0.162s
 310 - 10000 four-ways joins.......................................    0.610s
 320 - subquery in result set......................................    0.406s
 400 - 70000 REPLACE ops on an IPK.................................    0.102s
 410 - 70000 SELECTS on an IPK.....................................    0.061s
 500 - 70000 REPLACE on TEXT PK....................................    0.124s
 510 - 70000 SELECTS on a TEXT PK..................................    0.112s
 520 - 70000 SELECT DISTINCT.......................................    0.066s
 980 - PRAGMA integrity_check......................................    0.404s
 990 - ANALYZE.....................................................    0.039s
       TOTAL.......................................................    5.404s

recurse.social queries

SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mastodon_production'; -- = 518126 @ 15:41 12/22 TPE time
                                                                                              --   590029 @ 3:03 12/23 TPE time
                                                                                              --   704344 @ 19:29 12/23 TPE time

lobste.rs queries

MariaDB [lobsters]> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES where table_schema = 'lobsters';
+----------+----------------------+------------------+
| DB_NAME  | TABLE_NAME           | TABLE_SIZE_in_MB |
+----------+----------------------+------------------+
| lobsters | ar_internal_metadata |       0.01562500 |
| lobsters | hat_requests         |       0.03125000 |
| lobsters | taggings             |      17.54687500 |
| lobsters | read_ribbons         |     147.90625000 |
| lobsters | story_texts          |     842.23437500 |
| lobsters | users                |      21.67187500 |
| lobsters | hidden_stories       |      19.54687500 |
| lobsters | tags                 |       0.04687500 |
| lobsters | messages             |       6.29687500 |
| lobsters | domains              |       1.51562500 |
| lobsters | categories           |       0.03125000 |
| lobsters | invitation_requests  |       0.01562500 |
| lobsters | comments             |     487.65625000 |
| lobsters | tag_filters          |       3.50000000 |
| lobsters | moderations          |       2.67187500 |
| lobsters | saved_stories        |      12.54687500 |
| lobsters | votes                |     516.64062500 |
| lobsters | suggested_titles     |       0.53125000 |
| lobsters | stories              |      70.10937500 |
| lobsters | replying_comments    |             NULL |
| lobsters | mod_notes            |       4.89062500 |
| lobsters | keystores            |      12.03125000 |
| lobsters | schema_migrations    |       0.03125000 |
| lobsters | suggested_taggings   |       6.06250000 |
| lobsters | hats                 |       0.07812500 |
| lobsters | invitations          |       1.87500000 |
+----------+----------------------+------------------+
26 rows in set (0.002 sec)
MariaDB [lobsters]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-12-21 13:53:38 |
+---------------------+
1 row in set (0.001 sec)

MariaDB [lobsters]> show global status like "Com_select";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 4553942 |
+---------------+---------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_update";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 13261 |
+---------------+-------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_insert";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 14381 |
+---------------+-------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_delete";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 613   |
+---------------+-------+
1 row in set (0.002 sec)
MariaDB [lobsters]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-12-22 15:39:38 |
+---------------------+
1 row in set (0.001 sec)

MariaDB [lobsters]> show global status like "Com_select";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 7131719 |
+---------------+---------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_update";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 20937 |
+---------------+-------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_insert";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 22464 |
+---------------+-------+
1 row in set (0.002 sec)

MariaDB [lobsters]> show global status like "Com_delete";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 955   |
+---------------+-------+
1 row in set (0.002 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment