HelloZeroNet / HelloZeroNet/ZeroNet

Move database connection to separate thread

Ouverte
#1,794 0 commentaires 4 réactions 0 personnes assignées Voir sur GitHub
Langage dominant
JavaScript
Étoiles
18.8k
Forks
2.3k
Métriques de merge des PR
Aucune PR mergée en 30 j

Description

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

Guide de contribution

Aucun guide de contribution indexé pour ce dépôt

Piste de recherche

Commencez par localiser les fichiers .py liés à la base de données et vérifier si les anciens fichiers de base de données sont compatibles avec les modes proposés. Exécutez ensuite les tests prévus concernant les disques lents et les défaillances matérielles, notamment la reconstruction de site et content.db ainsi que les scénarios associés dans #1782 et #1774. Le travail sera considéré comme terminé lorsque les modifications de compatibilité, de gestion des défaillances, de reconstruction et de transactions prévues dans le plan auront été vérifiées.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
python
Domaine
databases
Type d'issue
Fonctionnalité
Difficulté
5/5
Temps estimé
Plus d'une semaine
Activité
À l'abandon
Clarté
À clarifier
Accessibilité débutants
20/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.