To add to the real-world performance info, I recently upgraded the
backup program I'm developing, HashBackup, from SQLite 3.6.18 to
3.8.8, mostly to see if it fixed some occasional "Database is locked"
errors (multithreaded). For these tests, nothing was changed except
the SQLite library.
Hardware:
--------------
The tests were on a quad-core processor Mac Mini i7 with 16GB of
memory, though HB only uses maybe 100-150MB for these tests. In most
cases, can make good use of up to 3 processors before other limiting
factors kick in.
The Mini has 2 1TB 5400rpm drives that max out at about 112MB/s on the
outer tracks with dd on the raw device:
sh-3.2# dd if=/dev/rdisk0 of=/dev/null bs=128k
^C5661+0 records in
5661+0 records out
741998592 bytes transferred in 6.578497 secs (112791508 bytes/sec)
An application can read from the file system at about 82MB/sec. This
disk has full-disk-encryption enabled, so maybe that is a factor:
sh-3.2# dd if=test-hb-home.tar of=/dev/null bs=128k
^C5361+0 records in
5361+0 records out
702676992 bytes transferred in 8.552060 secs (82164648 bytes/sec)
Environment:
------------------
These tests backup data from drive 0 and write the backup data and
SQLite database to drive 1. Before testing different SQLite versions,
drive 1 (the output drive) is emptied. Drive 0, the system drive, is
mounted and of course changing slightly between tests, but nothing is
running except the usual system processes.
Test 1: Full system backup followed by incremental backup
------------------------------------------------------------------------------
For a full backup, HB is mainly doing inserts of file system and HB
metadata using prepared insert statements. The dedup table and arc
files (compressed user data) do not use SQLite. Around 700K files are
backed up on the system drive, totalling 49GB. There are only 3000
files 1MB or larger, so this test is seek-bound on the filesystem.
The largest file is 8GB.
SQLite 3.6.18 full backup:
Time: 2745.6s, 45m 45s
Checked: 698470 paths, 49500931464 bytes, 49 GB
Saved: 698418 paths, 49500931464 bytes, 49 GB
Excluded: 45
Dupbytes: 15300578096, 15 GB, 30%
Compression: 51%, 2.1:1
Space: 23 GB, 23 GB total
No errors
real 45m52.149s
user 26m43.971s
sys 4m4.460s
SQLite 3.6.18 incremental backup:
Time: 425.8s, 7m 5s
Checked: 698471 paths, 49501306705 bytes, 49 GB
Saved: 108 paths, 1235685931 bytes, 1.2 GB
Excluded: 45
Dupbytes: 1230321290, 1.2 GB, 99%
Compression: 99%, 1493.3:1
Space: 827 KB, 23 GB total
No errors
real 7m17.200s
user 1m51.300s
sys 0m43.691s
SQLite 3.8.8 full backup:
Time: 3068.1s, 51m 8s
Checked: 698472 paths, 49501873829 bytes, 49 GB
Saved: 698420 paths, 49501873829 bytes, 49 GB
Excluded: 45
Dupbytes: 15296606115, 15 GB, 30%
Compression: 51%, 2.1:1
Space: 23 GB, 23 GB total
No errors
real 51m21.242s
user 26m42.716s
sys 4m12.255s
SQLite 3.8.8 incremental backup:
NOTE: a large Postgres log file was backed up with 3.6.18 that was not
backed up this time. This backup saved 18MB instead of 1.2GB.
Time: 362.6s, 6m 2s
Checked: 698472 paths, 49501964307 bytes, 49 GB
Saved: 64 paths, 18663074 bytes, 18 MB
Excluded: 45
Dupbytes: 17388470, 17 MB, 93%
Compression: 99%, 116.6:1
Space: 160 KB, 23 GB total
No errors
real 6m9.153s
user 1m29.346s
sys 0m42.033s
My conclusion, based on the full system backup test, is that SQLite
3.8.8 is 11-12% slower than 3.6.18 for this insert workload:
(3068-2745)/2745
.1176
I'm not particularly happy about that, but if it fixes my Database is
locked problem, I can live with it, and I might be able to use a
partial index on one index to speed this up.
Test 2: backup 2 versions of a large SQL dump
--------------------------------------------------------------
In this test, there are 2 versions of an SQL database dump, to SQL
commands. The 2 files are around 1.8GB. This test eliminates a lot
of the filesystem seeking aspect of a system backup. The purge
command was used before each backup to force actual disk I/O on the
data file and SQLite database:
3.6.18 initial backup:
Around 33K SQL inserts are performed as the file is split into blocks.
Time: 23.1s
Checked: 4 paths, 1812149567 bytes, 1.8 GB
Saved: 4 paths, 1812149567 bytes, 1.8 GB
Excluded: 0
Dupbytes: 0
Compression: 78%, 4.7:1
Space: 382 MB, 382 MB total
No errors
real 0m23.223s
user 0m53.623s
sys 0m1.709s
3.6.18 incremental backup:
Around 9K inserts are performed for new blocks.
Around 24K queries are performed to verify block hashes match
Around 24K updates are performed to bump reference counts
Time: 21.5s
Checked: 4 paths, 1771063478 bytes, 1.8 GB
Saved: 4 paths, 1771063478 bytes, 1.8 GB
Excluded: 0
Dupbytes: 1306412633, 1.3 GB, 73%
Compression: 93%, 14.6:1
Space: 121 MB, 503 MB total
No errors
real 0m24.181s
user 0m31.974s
sys 0m1.266s
3.8.8 initial backup:
Time: 22.4s
Checked: 4 paths, 1812149567 bytes, 1.8 GB
Saved: 4 paths, 1812149567 bytes, 1.8 GB
Excluded: 0
Dupbytes: 0
Compression: 78%, 4.7:1
Space: 382 MB, 382 MB total
No errors
real 0m22.917s
user 0m53.039s
sys 0m1.657s
3.8.8 incremental backup:
Time: 21.3s
Checked: 4 paths, 1771063478 bytes, 1.8 GB
Saved: 4 paths, 1771063478 bytes, 1.8 GB
Excluded: 0
Dupbytes: 1306412633, 1.3 GB, 73%
Compression: 93%, 14.6:1
Space: 121 MB, 503 MB total
No errors
real 0m23.868s
user 0m32.040s
sys 0m1.303s
Here are the stats for this backup:
HashBackup build #1199 Copyright 2009-2015 HashBackup, LLC
Backup directory: /hb
2 completed backups
3.5 GB file bytes checked since initial backup
3.5 GB file bytes saved since initial backup
43s total backup time
1.8 GB average file bytes checked per backup in last 2 backups
1.8 GB average file bytes saved per backup in last 2 backups
100.00% average changed data percentage per backup in
last 2 backups
21s average backup time for last 2 backups
3.5 GB file bytes currently stored
2 archives
501 MB archive space
500 MB active archive bytes - 100.00%
7:1 industry standard dedup ratio
250 MB average archive space per backup for last 2 backups
7:1 reduction ratio of backed up files for last 2 backups
6.2 MB dedup table current size
41,267 dedup table entries
18% dedup table utilization at current size
8 files stored
8 files active
4 paths stored
5 paths active
41,267 blocks stored
65,881 block references
41,267 unique blocks
24,614 deduped blocks
1.6:1 block dedup ratio
12 KB average stored block size
299 MB backup space saved by dedup
55,172 average variable-block length (bytes)
For this test, the two SQLite versions seem equivalent.
Test 3: HB selftest
------------------------
HB has a selftest feature like fsck for a filesystem. I ran this on a
large customer backup with 25M files in an 8GB SQLite database. It
takes around 30 minutes to run, and was about 2 minutes faster with
3.8.8 vs 3.6.18. This is an all-query benchmark, with no inserts or
updates. It's mostly a bunch of sequential table scans.
Overall, I'm okay with these performance changes. I would have liked
a 10% speed increase across the board, like anyone else, but I might
still be able to use some new features in 3.8.8 to maintain
performance parity with 3.6.18 for my particular workload, and I'd
rather be on a recent release of SQLite.
Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
