Vitesses de base de données locales
1. Measures
Importing 30 000 lines of csv (3 files, 100 columns) in an SQL database (15 tables) via Django 1.11.
$ time ./import_csv_to_sqlite3_SSD.py (1)
135.49s user 28.63s system 6:47.77 elapsed 40% CPU
$ time ./import_csv_to_postgresql.py (2)
60.48s user 3.84s system 2:16.50 elapsed 47% CPU
$ time ./import_csv_to_sqlite3_RAM.py (3)
30.27s user 2.52s system 0:32.78 elapsed 100% CPU| 1 | With sqlite3 database file stored on a SATA SSD (452 Mo/s writing, 379Mo/s reading, speeds measured like explained here : Test storage peripheral read / write speed.) |
| 2 | With PostgreSQL 9.6 on Debian 9.6, driver psycopg2. |
| 3 | With sqlite3 database file stored in a tmpfs so in RAM (3,5 Go/s writing, 5,2Go/s reading) like explained here Mount /tmp in a tmpfs. |
2. Observations
-
sqlite3is twice as slow asPostgreSQL. Butsqlite3is twice as fast asPostgreSQLwhen running in RAM ; -
Writing in RAM is 8x faster than writing in SSD (at least for one piece 1 Go file), and reading in RAM is 14x time faster ;
-
tmpfsachieves twice the performance of an NVME storage in 2018 ; -
with
sqlite3+tmpfs, it’s the CPU that slows down the process.
3. On batteries
On batteries, using TLP (with near default parameters) the results are significantly slower :
-
166.26s user 37m25s system 31:36.98 elapsed 10% CPU
-
155.84s user 9m86s system 10:17.18 elapsed 26% CPU
-
39.81s user 2.95s system 0:42.84 elapsed 99% CPU
Speeds of storages :
-
SSD : 20Mo/s writing, 75Mo/s reading
-
RAM : 1,5Go/s writing, 2,2Go/s reading
4. More hardware info
The computer used for this tests is an Asus UX305CA, Intel Core i7 M (6Y75 / 600MHz - 3,1GHz), 8Go DDR3 1866MHz. More info here : Memo_10.
Meta-Press.es: search-engine