4 ms·

Most of my python projects use SQLite; 1 exception where i need multiprocessing access to the database and no locking problems and speed so i need to run the en

Most of my python projects use SQLite; 1 exception where i need multiprocessing access to the database and no locking problems and speed so i need to run the entire db in memory. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html The built in library makes it really quick and easy to use it. Whereas mysql or in my case id use postgresql if i needed a full db. You're looking for a third party library? I have used Psycopg before but its just not needed. Yes, ive come up against the sqlite locked database performance troubles; and failed to actually get the multi user thing working properly. But I moreso just needed to reapproach the issue. My new startup http://mapleintel.ca http://mapleintel.ca is db.sqlite3 based. thousands of lines in it so far and growing every day.

noduerme 1y ago
I've used sqlite3 in node, and it's nice and performant for small cases, yes. Mostly I've used it for things small enough where a user could download an entire sqlite file of their data, and then re-upload it in case their data got lost. But ultimately this data gets stored in a true MySQL DB. I don't think I'd trust it to run a whole system with thousands of users and millions of entries... honestly, maybe my issue is that I don't trust NodeJS enough...
- hruk 1y ago
  FWIW, I've been running a system with roughly 100K users, about 25 qps on average, with a single SQLite file for several years. No issues with data.
  - noduerme 1y ago
    That's... pretty amazing. It sounds crazy to me, I'm obsessive about hourly backups, but do you use something like Litestream to keep copies?
    - arkh 1y ago
      From some months ago: https://news.ycombinator.com/item?id=43076785 https://news.ycombinator.com/item?id=43076785 > searchcode.com’s SQLite database is probably one of the largest in the world, at least for a public facing website. It’s actual size is 6.4 TB.
    - hruk 1y ago
      Yep, we use Litestream. It's been very reliable.
- hiAndrewQuinn 1y ago
  SQLite is almost certainly more battle tested by this point than even MySQL for things like this. Alongside having somewhere around the ballpark of 10¹² current deployments (yes, 1 trillion) it has about 600 lines of testing code per line of its actual source code. https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html To give you an idea of just how hardcore this is, they stress test something as fundamental as malloc() independently: >SQLite, like all SQL database engines, makes extensive use of malloc() [...] On servers and workstations, malloc() never fails in practice and so correct handling of out-of-memory (OOM) errors is not particularly important. But on embedded devices, OOM errors are frighteningly common and since SQLite is frequently used on embedded devices, it is important that SQLite be able to gracefully handle OOM errors. >OOM testing is accomplished by simulating OOM errors. SQLite allows an application to substitute an alternative malloc() implementation using the sqlite3_config(SQLITE_CONFIG_MALLOC,...) interface. The TCL and TH3 test harnesses are both capable of inserting a modified version of malloc() that can be rigged to fail after a certain number of allocations. These instrumented mallocs can be set to fail only once and then start working again, or to continue failing after the first failure. OOM tests are done in a loop. On the first iteration of the loop, the instrumented malloc is rigged to fail on the first allocation. Then some SQLite operation is carried out and checks are done to make sure SQLite handled the OOM error correctly. Then the time-to-failure counter on the instrumented malloc is increased by one and the test is repeated. The loop continues until the entire operation runs to completion without ever encountering a simulated OOM failure. Tests like this are run twice, once with the instrumented malloc set to fail only once, and again with the instrumented malloc set to fail continuously after the first failure. I don't say this as a hater of MySQL! SQLite is built with very different constraints in mind. But data consistency is something it really shines at.
- deleted 1y ago
  [deleted]

noduerme 1y ago

I've used sqlite3 in node, and it's nice and performant for small cases, yes. Mostly I've used it for things small enough where a user could download an entire sqlite file of their data, and then re-upload it in case their data got lost. But ultimately this data gets stored in a true MySQL DB. I don't think I'd trust it to run a whole system with thousands of users and millions of entries... honestly, maybe my issue is that I don't trust NodeJS enough...

hruk 1y ago
FWIW, I've been running a system with roughly 100K users, about 25 qps on average, with a single SQLite file for several years. No issues with data.
- noduerme 1y ago
  That's... pretty amazing. It sounds crazy to me, I'm obsessive about hourly backups, but do you use something like Litestream to keep copies?
  - arkh 1y ago
    From some months ago: https://news.ycombinator.com/item?id=43076785 https://news.ycombinator.com/item?id=43076785 > searchcode.com’s SQLite database is probably one of the largest in the world, at least for a public facing website. It’s actual size is 6.4 TB.
  - hruk 1y ago
    Yep, we use Litestream. It's been very reliable.
hiAndrewQuinn 1y ago
SQLite is almost certainly more battle tested by this point than even MySQL for things like this. Alongside having somewhere around the ballpark of 10¹² current deployments (yes, 1 trillion) it has about 600 lines of testing code per line of its actual source code. https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html To give you an idea of just how hardcore this is, they stress test something as fundamental as malloc() independently: >SQLite, like all SQL database engines, makes extensive use of malloc() [...] On servers and workstations, malloc() never fails in practice and so correct handling of out-of-memory (OOM) errors is not particularly important. But on embedded devices, OOM errors are frighteningly common and since SQLite is frequently used on embedded devices, it is important that SQLite be able to gracefully handle OOM errors. >OOM testing is accomplished by simulating OOM errors. SQLite allows an application to substitute an alternative malloc() implementation using the sqlite3_config(SQLITE_CONFIG_MALLOC,...) interface. The TCL and TH3 test harnesses are both capable of inserting a modified version of malloc() that can be rigged to fail after a certain number of allocations. These instrumented mallocs can be set to fail only once and then start working again, or to continue failing after the first failure. OOM tests are done in a loop. On the first iteration of the loop, the instrumented malloc is rigged to fail on the first allocation. Then some SQLite operation is carried out and checks are done to make sure SQLite handled the OOM error correctly. Then the time-to-failure counter on the instrumented malloc is increased by one and the test is repeated. The loop continues until the entire operation runs to completion without ever encountering a simulated OOM failure. Tests like this are run twice, once with the instrumented malloc set to fail only once, and again with the instrumented malloc set to fail continuously after the first failure. I don't say this as a hater of MySQL! SQLite is built with very different constraints in mind. But data consistency is something it really shines at.
deleted 1y ago
[deleted]