1. 48
  1.  

  2. 11

    This is a great write up, but I’m a bit confused by the title and headline:

    Inserting One Billion Rows in SQLite Under A Minute

    Current Best: 100M rows inserts in 33 seconds.

    Shouldn’t this blog post be called Inserting One Hundred Million Rows in SQLite Under A Minute?

    1. 3

      Hey, sorry for the misleading title. I started with ‘Fast SQLite Inserts’ and it had many iterations. In the title, I wanted to intend that I want to insert 1 billion rows under a minute on my machine. I thought the current title is fine, since I got LGTM for earlier drafts. The detail about on my machine is also important since mine is a two year old laptop and all the measurements are done on it.

      Also I got another feedback that title should indicate that it is a test database and emphasise that it is not durable.

      I am wondering the right way to convey all of this in the title yet also keep it short.

      1. 10

        I think the title is close enough but could be renamed to 100M and still be impressive. What you said makes sense, but generally we don’t see titles that didn’t actually happen unless they’re prefixed with “Attempting to” or something.

        1. 3

          hey yellow, thank you for reading my post and for the feedback. I have updated the title on my blog post, prefixed with “Towards” and also requested mods to edit title here as well.

          1. 1

            Thanks! Nice post!! :)

          2. 3

            Yeah, I agree. I think it’s a great blog post and the results are impressive. The title is just a bit misleading.

      2. 5

        If anyone is interested in this sort of problem I looked at speeding up imports into ClickHouse in 2019 https://tech.marksblogg.com/faster-clickhouse-imports-csv-parquet-mysql.html The blog compares the difference between CSV, JSON, Parquet as well as importing off of local disk versus HDFS. I also did a comparison of CSV import speeds against stock PostgreSQL, MySQL and Kafka.

        1. 3

          Another interesting way to look at this is that PyPy gets within 5x the speed of the Rust version. It’s a great project.

          1. 2

            How large is the data in uncompressed CSV format? How long does the data take when loading in with the sqlite CLI?

            1. 4

              I can answer my own question, the 100,000,000 rows sqlite3_opt.py produced when I ran it created a 1.5 GiB SQLite3 file and a 1.8 GiB CSV file when I dumped it out. If you just wrote zeros to a file on NVMe-backed storage the above files could be generated in 0.5 - 0.8 seconds. So 1B rows, or ~15GiB in SQLite format could be written in 8 seconds if it was being copied from another NVMe-backed storage device. That gives another 52 seconds to all the overhead of generating random data and writing it out in SQLite3’s internal layout.

              1. 3

                So do you estimate it will faster than the fastest Rust version? That was 100 M rows in 33 seconds.

                I’d expect you could do .import csv of a 1.5 GB CSV file on a modern machine in less than 30 seconds?

                Also this architecture is parallelizable over 2 cores – one generating CSV, and one sqlite process writing the data.

                My instinct was that the “shell version” of this program would be the fastest :)

                1. 3

                  Without the overhead of generating the dataset, loading the CSV version into SQLite3 via a single import command took 3m5s seconds on my 2020 MBP with the DB on an SSD. This works out to about 10 MiB/s.

                  $ cat load.sql 
                  PRAGMA journal_mode = OFF;
                  PRAGMA synchronous = 0;
                  PRAGMA cache_size = 1000000;
                  PRAGMA locking_mode = EXCLUSIVE;
                  PRAGMA temp_store = MEMORY;
                  .mode csv
                  .separator ","
                  .timer on
                  .import sqlite3_opt.csv user
                  
                  $ time sqlite3 import.db < load.sql
                  
                  real    3m5.419s
                  user    2m46.685s
                  sys     0m12.391s
                  

                  I’m not sure if there is a good way to break past the single-core bottleneck when loading data in. I can see one core sat at 100% when this above import happens.

                  Even on Presto+HDFS clusters, creating a new table based on a SELECT * from another table will result in each node in the cluster building their own section of the dataset with a single core. There seems to be some enforced linearization there as well. Using Zstd instead of Zlib for compression at best improves perf by ~30%.

                  Can anyone shed some light on getting past the single-core compute restrictions of SQLite when loading CSV data in?

                  1. 1

                    Thanks for the info! I wonder if the CSV parser is kinda slow? I hope I will find time to test it out, as I’m kinda surprised that 100M rows or 1.5 GB can’t be loaded faster than 3 minutes.

                    I was also wondering about a single INSERT statement of multiple rows would be faster. You use the SQL parser instead of the CSV parser.

                    INSERT into user VALUES (row 1 ...), (row 2 ...), ... (row N ...) ;
                    

                    I think one issue is that the example code uses an if statement and separate INSERT statements, and I think the Rust version does too:

                    https://github.com/avinassh/fast-sqlite3-inserts/blob/master/sqlite3_opt.py#L26

                    Basically I think you could factor that code into data, and it would be faster (?). I guess the CSV method is already doing that though. I’d be interested to see your code and maybe run it on my machine.

                    I don’t think sqlite has any multicore / multithreading features – which is why I think splitting it up into 2 processes (generation and loading) is probably a decent win.

                    1. 2

                      I did a quick test comparing inserting via CSV versus SQL. It seems SQL triggers about the same number of calls to fdatasync but they take about ~3x longer.

                      $ cat test1.csv
                      1,hello,123
                      
                      $ cat load.sql
                      .mode csv
                      .separator ","
                      .import test1.csv test
                      
                      $ cat load2.sql
                      INSERT INTO test (a,b,c) VALUES (1, 'hello', 123);
                      
                      $ strace -wc sqlite3 import.db < load.sql
                      
                      % time     seconds  usecs/call     calls    errors syscall
                      ------ ----------- ----------- --------- --------- ----------------
                       78.02    0.005216        1304         4           fdatasync
                        4.29    0.000287           7        41           mmap
                        2.53    0.000169           8        19         1 openat
                        1.67    0.000111         111         1           execve
                      ...
                      ------ ----------- ----------- --------- --------- ----------------
                      100.00    0.006685                   213        10 total
                      
                      
                      $ strace -wc sqlite3 import.db < load2.sql
                      % time     seconds  usecs/call     calls    errors syscall
                      ------ ----------- ----------- --------- --------- ----------------
                       79.94    0.015514        3878         4           fdatasync
                        3.61    0.000701          17        41           mmap
                        2.34    0.000454         453         1           execve
                      ...
                      ------ ----------- ----------- --------- --------- ----------------
                      100.00    0.019406                   209        10 total
                      

                      I suspect something like valgrind could give a much more authoritative answer on the number of op codes needed for the operations above.

                  2. 1

                    I was going to mention that there’s the .import function as well, and pythons sqlite3 package I think has the ability to call functions from the package on the db. I was loading a large set on Friday and a 6.44Gb CSV with about 6 columns took single-digit minutes using .import and I wasn’t using any of the additional performance settings.

                  3. 1

                    You could pipe the CSV output from Python to the sqlite tool instead of writing it to a file first.

                    But the tool isn’t doing anything magic, so I wouldn’t expect it to be faster than a C or Rust program. This would just be a fast solution involving a lot less coding.

                2. 1

                  This is an interesting read. A while back I did some experimenting with postage SQL and SQLite for message queue. This is producing and consuming so not just insert optimization. I used an old reddit comment dataset to simulate realwold data. My results where very different. https://github.com/lateefj/gq/blob/master/NOTES.rst

                  1. 1

                    While I definitely applaud the initiative (and speed for it’s own sake), is it necessary to generate this more than once (i.e. does speed matter)? I guess the data could be customer-specific in some way, but that seems not needed for a test db.

                    1. 1

                      Huh the async slowdown is also interesting

                      1. 1

                        If generating the file is more important than learning how to tune sqlite, one faster way would be to write a raw file and not use sqlite to generate the rows at all.