HelloZeroNet / HelloZeroNet/ZeroNet

Move database connection to separate thread

Offen
#1,794 0 Kommentare 4 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
Vorherrschende Sprache
JavaScript
Sterne
18.8k
Forks
2.3k
PR-Merge-Kennzahlen
Keine gemergten PRs in 30 T.

Beschreibung

## Why?
To avoid blocking the application on heavy/large amount of queries.

## How?
Moving the database connection to different thread will not block the gevent eventloop, but as a downside it has some performance drawback because of more context switch and pushing queries/results using Queue objects.

## Threaded db benchmark results

### Inserting 100_000 * 0.6k data with separate queries
Py2 + nothread: 2.0s
Py3 + nothread: 1.3s
Py2 + thread: 4.2s
Py3 + thread: 2.8s

### Selecting 1000 * 1000 * 0.6k data
Py3 + nothread: 1.5s
Py3 + nothread: 2.2s
Py3 + thread: 1.7s
Py2 + thread: 4.0s

It's already optimized (eg. does not expect response from non-SELECT queries), but probably it could be improved by only executing SELECT queries from the other thread.

## Change DB mode to avoid data loss on power/hw failure

The current default DB mode could lead to db corruption on various failures. We should change it to more secure mode.

### Db mode benchmark

All tests done with Py3 on SSD by inserting 100 000 x 5k data. (creating 500MB db file)
Isolation None means: Autocommit after every INSERT / transaction.
Isolation "" means: Only commit when it's called explicitly.

```
Mode: speed, isolation: None journal_mode: 'memory' synchronous: 0 (init: 0.111s)
Commit done done 0.000s (site: 16.000KB)
Commit done done 0.000s (site: 133708.000KB)
Commit done done 0.000s (site: 267440.000KB)
Commit done done 0.000s (site: 401196.000KB)
Insert done in 10.925s
Delete done in 0.159s
Insert transaction done in 1.129s
Select done in 0.108s (found: 999)
Commit done in 0.000s (size: 534948.0KB)
Total: 12.594s
read_count: 0.14M, write_count: 0.55M, read_bytes: 190.33M, write_bytes: 2254.99M, other_count: 0.20M, other_bytes: 0.33M

Mode: security, isolation: None journal_mode: 'wal' synchronous: 1 (init: 0.128s)
Commit done done 0.000s (site: 4.000KB)
Commit done done 0.000s (site: 133344.000KB)
Commit done done 0.000s (site: 266264.000KB)
Commit done done 0.000s (site: 400252.000KB)
Insert done in 42.929s
Delete done in 0.203s
Insert transaction done in 13.298s
Select done in 0.180s (found: 999)
Commit done in 0.000s (size: 534948.0KB)
Total: 56.892s
read_count: 0.43M, write_count: 1.49M, read_bytes: 1764.87M, write_bytes: 3329.76M, other_count: 0.01M, other_bytes: 0.34M

Mode: security, isolation: '' journal_mode: 'wal' synchronous: 1 (init: 0.111s)
Commit done done 0.000s (site: 4.000KB)
Commit done done 1.530s (site: 133708.000KB)
Commit done done 1.649s (site: 267440.000KB)
Commit done done 1.760s (site: 401196.000KB)
Insert done in 7.191s
Delete done in 1.705s
Insert transaction done in 2.536s
Select done in 0.153s (found: 999)
Commit done in 6.314s (size: 534948.0KB)
Total: 18.202s
read_count: 0.55M, write_count: 1.02M, read_bytes: 2267.32M, write_bytes: 2104.22M, other_count: 0.01M, other_bytes: 0.33M

Mode: default, isolation: '' journal_mode: 'delete' synchronous: 2 (init: 0.117s)
Commit done done 0.005s (site: 16.000KB)
Commit done done 0.372s (site: 133708.000KB)
Commit done done 0.372s (site: 267440.000KB)
Commit done done 0.367s (site: 401196.000KB)
Insert done in 3.676s
Delete done in 0.467s
Insert transaction done in 2.732s
Select done in 0.104s (found: 999)
Commit done in 0.432s (size: 534948.0KB)
Total: 7.544s
read_count: 0.05M, write_count: 0.26M, read_bytes: 188.45M, write_bytes: 1046.84M, other_count: 0.01M, other_bytes: 0.33M
```

So if we want to avoid the insecure (currently default journal_mode: 'memory' synchronous: 0) mode we have to switch off the autocommit mode as autocommit + sync 1 mode is 5 times slower. In that manual commit mode journal: DELETE looks like results in less io operations and writes are faster, than WAL journal mode (3.6s vs 7.2s for INSERT, 0.4s vs 1.7s for DELETE)

### Plan

- [x] Benchmark threaded db overhead
- [x] Benchmark various db options
- [ ] Test if previous db files are compatible with new modes
- [ ] Test hw falure by unplugging slow pendrive during db write (#1782)
- [ ] Remove transactions from all .py files as we move away from autocommit
- [ ] Test site rebuilding on slow disk
- [ ] Test content.db rebuilding on slow disk (#1774)

Beitragsleitfaden

Für dieses Repository ist kein Beitragsleitfaden indexiert

Bewertung

Dieses Issue wurde noch nicht bewertet.

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.