Grimoire-
Command
.es

GNU+Linux command memo

Local database speed

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

  • sqlite3 is twice as slow as PostgreSQL. But sqlite3 is twice as fast as PostgreSQL when 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 ;

  • tmpfs achieves 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.