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
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:
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
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
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)