4 ms·

More details please if you have them. What was the throughput? number of transactions? Data size? If you have that thread would be great to see it as well.

jjoonathan 5y ago
It was chugging at ~100 inserts per second on about ~300k rows of <100 bytes each. Just inserts, no index (it was the first thing to go), no foreign keys, one table only. SQLite was spending all the disk bandwidth copying data in and out of a tiny designated working area and flushing. This was in 2013 -- I'm afraid I've forgotten the details. It's an old problem, long fixed by now. The problem itself didn't concern me nearly as much as "no perf tools + no perf foolproofing." That's a rough combo. If a problem this simple required this much debugging, extrapolations to problems of any complexity are terrifying. I knew that simplicity implied limitations, but this lesson taught me that simplicity could also imply danger.
- srcreigh 5y ago
  This is a common problem. Bulk inserts should be done within a single txn if possible. The limit isn't insert throughput, but transaction throughput. I've commented elsewhere here for docs referencing this problem. It's FAQ#19 on the SQLite website. Was your inserts based on HTTP requests or was it more of a batch process? we're they grouped in txns? Obviously user http requests would be harder to group up, but kudos if your service is handling 100 QPS writes as that's pretty rare level of scale approaching the real "need a beefy concurrent db server" type of problem statement.
  - jjoonathan 5y ago
    The inserts were batched. That was the second thing I tried after getting rid of the index, and that brought it from 20/s to 100/s. This wasn't a website, it was HPC job tracking. SQLite chugged so hard that the tail wagged the dog, though.
    - srcreigh 5y ago
      I'm not contradicting you, it is curious what you saw, but this SQLite FAQ does claim that 50k/s inserts on a normal computer HDD is possible. Maybe there was some other resource constraint or configuration somewhere. It is very curious. > Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

jjoonathan 5y ago

It was chugging at ~100 inserts per second on about ~300k rows of <100 bytes each. Just inserts, no index (it was the first thing to go), no foreign keys, one table only. SQLite was spending all the disk bandwidth copying data in and out of a tiny designated working area and flushing. This was in 2013 -- I'm afraid I've forgotten the details. It's an old problem, long fixed by now. The problem itself didn't concern me nearly as much as "no perf tools + no perf foolproofing." That's a rough combo. If a problem this simple required this much debugging, extrapolations to problems of any complexity are terrifying. I knew that simplicity implied limitations, but this lesson taught me that simplicity could also imply danger.

srcreigh 5y ago
This is a common problem. Bulk inserts should be done within a single txn if possible. The limit isn't insert throughput, but transaction throughput. I've commented elsewhere here for docs referencing this problem. It's FAQ#19 on the SQLite website. Was your inserts based on HTTP requests or was it more of a batch process? we're they grouped in txns? Obviously user http requests would be harder to group up, but kudos if your service is handling 100 QPS writes as that's pretty rare level of scale approaching the real "need a beefy concurrent db server" type of problem statement.
- jjoonathan 5y ago
  The inserts were batched. That was the second thing I tried after getting rid of the index, and that brought it from 20/s to 100/s. This wasn't a website, it was HPC job tracking. SQLite chugged so hard that the tail wagged the dog, though.
  - srcreigh 5y ago
    I'm not contradicting you, it is curious what you saw, but this SQLite FAQ does claim that 50k/s inserts on a normal computer HDD is possible. Maybe there was some other resource constraint or configuration somewhere. It is very curious. > Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.