BlobTempSpace: faster BLOB operations in HQbird
HQbird can cache BLOBs in temp space, in order to speed up BLOB operations (+15%–200% faster than in vanilla Firebird), and to prevent growth of the database file in case of mistaken BLOB operations.
HQbird uses an extra firebird.conf parameter BlobTempSpace to control this
feature. The caching option can be:
0— disabled,1— enabled for PSQL (default),2— enabled for all BLOB operations.
Result in short
In the test below, the same workload ran about 12 times faster with
BlobTempSpace enabled:
| Temp storage | BlobTempSpace off | BlobTempSpace on | Speed-up |
|---|---|---|---|
| RAM drive (48 GB) | 108.2 s | 9.0 s | 12.0× |
| SSD (Samsung 870 QVO, 1 TB) | 106.0 s | 9.1 s | 11.7× |
Total time = EXECUTE BLOCK + COMMIT, average of two runs.
Where the time goes
The gain comes from two places:
- EXECUTE BLOCK is 2.3–2.4× faster. The BLOB concatenation runs in temp space, not in database pages.
- COMMIT is almost instant. Without the cache, the commit writes about 1.1 million pages to the database (81–88 s). With the cache, it writes 3 021 pages (15–16 ms).
| Temp storage | BlobTempSpace | EXECUTE BLOCK | COMMIT | Page writes (block + commit) |
|---|---|---|---|---|
| RAM drive | off | 20.9 s | 87.4 s | 1 117 485 |
| RAM drive | on | 9.0 s | 0.015 s | 3 057 |
| SSD | off | 21.8 s | 84.2 s | 1 117 485 |
| SSD | on | 9.1 s | 0.016 s | 3 057 |
With BlobTempSpace enabled, the database receives about 365 times fewer
page writes. Fetches and marks stay almost the same (5.15 million fetches,
3.45 million marks), so the database does the same logical work; only the
physical writes go away.
Test setup
- Workload: one
EXECUTE BLOCKthat builds BLOBs by concatenation (750 rows), thenCOMMIT. Each case ran twice (run-1, run-2). - Temp storage (
FIREBIRD_TMP):- RAM drive: WinRamTech x64 (ex. Qsoft), 48 GB.
- SSD: Samsung 870 QVO, 1 TB, with
TempCacheLimit = 48G(RAM drive disabled).
- Measurement: Firebird trace statistics (elapsed time, reads, writes, fetches, marks).
- With
BlobTempSpaceoff, the final database size was 8 839 MB.
Raw results
| Temp storage | BlobTempSpace | Run | Action | Elapsed, ms | Writes | Fetches | Marks |
|---|---|---|---|---|---|---|---|
| RAM drive | off | 1 | EXECUTE BLOCK | 20 104 | 1 127 | 5 154 541 | 3 450 817 |
| RAM drive | off | 1 | COMMIT | 87 531 | 1 116 358 | 1 | 1 |
| RAM drive | off | 2 | EXECUTE BLOCK | 21 621 | 1 127 | 5 154 541 | 3 450 817 |
| RAM drive | off | 2 | COMMIT | 87 229 | 1 116 358 | 1 | 1 |
| RAM drive | on | 1 | EXECUTE BLOCK | 9 039 | 36 | 5 153 997 | 3 450 273 |
| RAM drive | on | 1 | COMMIT | 15 | 3 021 | 1 | 1 |
| RAM drive | on | 2 | EXECUTE BLOCK | 8 976 | 36 | 5 153 997 | 3 450 273 |
| RAM drive | on | 2 | COMMIT | 15 | 3 021 | 1 | 1 |
| SSD | off | 1 | EXECUTE BLOCK | 21 831 | 1 127 | 5 154 541 | 3 450 817 |
| SSD | off | 1 | COMMIT | 86 726 | 1 116 358 | 1 | 1 |
| SSD | off | 2 | EXECUTE BLOCK | 21 834 | 1 127 | 5 154 541 | 3 450 817 |
| SSD | off | 2 | COMMIT | 81 614 | 1 116 358 | 1 | 1 |
| SSD | on | 1 | EXECUTE BLOCK | 9 084 | 36 | 5 153 997 | 3 450 273 |
| SSD | on | 1 | COMMIT | 16 | 3 021 | 1 | 1 |
| SSD | on | 2 | EXECUTE BLOCK | 9 074 | 36 | 5 153 997 | 3 450 273 |
| SSD | on | 2 | COMMIT | 16 | 3 021 | 1 | 1 |
Reads were 0 in all runs.
Notes
- The RAM drive and the SSD give almost the same result. The temp storage type is not the bottleneck for this workload: the cache itself gives the gain.
- The speed-up depends on the workload. Heavy BLOB concatenation in PSQL, as in this test, gains the most. Typical BLOB operations gain 15%–200%.