5 ms·

How to copy databases between computers? Just send a circle and forget about the rest of the owl. As others have mentioned an incremental rsync would be much f

by zeroq 1y ago

How to copy databases between computers? Just send a circle and forget about the rest of the owl. As others have mentioned an incremental rsync would be much faster, but what bothers me the most is that he claims that sending SQL statements is faster than sending database and COMPLETELY omiting the fact that you have to execute these statements. And then run /optimize/. And then run /vacuum/. Currently I have scenario in which I have to "incrementally rebuild *" a database from CSV files. While in my particular case recreating the database from scratch is more optimal - despite heavy optimization it still takes half an hour just to run batch inserts on an empty database in memory, creating indexes, etc.

JamesonNetworks 1y ago
30 minutes seems long. Is there a lot of data? I’ve been working on bootstrapping sqlite dbs off of lots of json data and by holding a list of values and then inserting 10k at a time with inserts, Ive found a good perf sweet spot where I can insert plenty of rows (millions) in minutes. I had to use some tricks with bloom filters and LRU caching, but can build a 6 gig db in like 20ish minutes now
- pessimizer 1y ago
  Saying that 30 minutes seems long is like saying that 5 miles seems far.
- thechao 1y ago
  Millions of rows in minutes sounds not ok, unless your tables have a large number of columns. A good rule is that SQLite's insertion performance should be at least 1% of sustained max write bandwidth of your disk; preferably 5%, or more. The last bulk table insert I was seeing 20%+ sustained; that came to ~900k inserts/second for an 8 column INT table (small integers).
- zeroq 1y ago
  It's roughly 10Gb across several CSV files. I create a new in-mem db, run schema and then import every table in one single transaction (in my testing it showed that it doesn't matter if it's a single batch or multiple single inserts as long are they part of single transaction). I do a single string replacement per every CSV line to handle an edge case. This results in roughly 15 million inserts per minute (give or take, depending on table length and complexity). 450k inserts per second is a magic barrier I can't break. I then run several queries to remove unwanted data, trim orphans, add indexes, and finally run optimize and vacuum. Here's quite recent log (on stock Ryzen 5900X): 08:43 import 13:30 delete non-essentials 18:52 delete orphans 19:23 create indexes 19:24 optimize 20:26 vacuum
iveqy 1y ago
I hope you've found https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/1711631/improve-insert-p... It's a very good writeup on how to do fast inserts in sqlite3
- jgalt212 1y ago
  yes, but they punt on this issue: CREATE INDEX then INSERT vs. INSERT then CREATE INDEX i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.
  - deleted 1y ago
    [deleted]
- zeroq 1y ago
  Yes! That was actually quite helpful. For my use case (recreating in-memory from scratch) it basically boils down to three points: (1) journal_mode = off (2) wrapping all inserts in a single transaction (3) indexes after inserts. For whatever it's worth I'm getting 15M inserts per minute on average, and topping around 450k/s for trivial relationship table on a stock Ryzen 5900X using built-in sqlite from NodeJS.
  - vlovich123 1y ago
    Would it be useful for you to have a SQL database that’s like SQLite (single file but not actually compatible with the SQLite file format) but can do 100M/s instead?
    - zeroq 1y ago
      Not really. I tested couple different approaches, including pglite, but node finally shipped native sqlite with version 23 and it's fine for me. I'm a huge fan of serverless solutions and one of the absolute hidden gems about sqlite is that you can publish the database on http server and query it extremely efficitent from a client. I even have a separate miniature benchmark project I thought I might publish, but then I decided it's not worth anyones time. x]
stackskipton 1y ago
As with any optimization, it matters where your bottleneck is here. Sounds like theirs is bandwidth but CPU/Disk IO is plentiful since they mentioned that downloading 250MB database takes minute where I just grabbed 2GB SQLite test database from work server in 15 seconds thanks to 1Gbps fiber.

by zeroq 1y ago

JamesonNetworks 1y ago

30 minutes seems long. Is there a lot of data? I’ve been working on bootstrapping sqlite dbs off of lots of json data and by holding a list of values and then inserting 10k at a time with inserts, Ive found a good perf sweet spot where I can insert plenty of rows (millions) in minutes. I had to use some tricks with bloom filters and LRU caching, but can build a 6 gig db in like 20ish minutes now

pessimizer 1y ago
Saying that 30 minutes seems long is like saying that 5 miles seems far.
thechao 1y ago
Millions of rows in minutes sounds not ok, unless your tables have a large number of columns. A good rule is that SQLite's insertion performance should be at least 1% of sustained max write bandwidth of your disk; preferably 5%, or more. The last bulk table insert I was seeing 20%+ sustained; that came to ~900k inserts/second for an 8 column INT table (small integers).
zeroq 1y ago
It's roughly 10Gb across several CSV files. I create a new in-mem db, run schema and then import every table in one single transaction (in my testing it showed that it doesn't matter if it's a single batch or multiple single inserts as long are they part of single transaction). I do a single string replacement per every CSV line to handle an edge case. This results in roughly 15 million inserts per minute (give or take, depending on table length and complexity). 450k inserts per second is a magic barrier I can't break. I then run several queries to remove unwanted data, trim orphans, add indexes, and finally run optimize and vacuum. Here's quite recent log (on stock Ryzen 5900X): 08:43 import 13:30 delete non-essentials 18:52 delete orphans 19:23 create indexes 19:24 optimize 20:26 vacuum

iveqy 1y ago

I hope you've found https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/1711631/improve-insert-p... It's a very good writeup on how to do fast inserts in sqlite3

jgalt212 1y ago
yes, but they punt on this issue: CREATE INDEX then INSERT vs. INSERT then CREATE INDEX i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.
- deleted 1y ago
  [deleted]
zeroq 1y ago
Yes! That was actually quite helpful. For my use case (recreating in-memory from scratch) it basically boils down to three points: (1) journal_mode = off (2) wrapping all inserts in a single transaction (3) indexes after inserts. For whatever it's worth I'm getting 15M inserts per minute on average, and topping around 450k/s for trivial relationship table on a stock Ryzen 5900X using built-in sqlite from NodeJS.
- vlovich123 1y ago
  Would it be useful for you to have a SQL database that’s like SQLite (single file but not actually compatible with the SQLite file format) but can do 100M/s instead?
  - zeroq 1y ago
    Not really. I tested couple different approaches, including pglite, but node finally shipped native sqlite with version 23 and it's fine for me. I'm a huge fan of serverless solutions and one of the absolute hidden gems about sqlite is that you can publish the database on http server and query it extremely efficitent from a client. I even have a separate miniature benchmark project I thought I might publish, but then I decided it's not worth anyones time. x]

stackskipton 1y ago

As with any optimization, it matters where your bottleneck is here. Sounds like theirs is bandwidth but CPU/Disk IO is plentiful since they mentioned that downloading 250MB database takes minute where I just grabbed 2GB SQLite test database from work server in 15 seconds thanks to 1Gbps fiber.