4 ms·

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work track

It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom build with custom instrumentation and figuring out the problem, I found an email thread where the sqlite community was arguing about this exact issue and the default settings in question. A couple of people had forseen the exact problem I had run into and suggested a fix. Their concerns were dismissed on the grounds that the problem could be configured away, and their concerns about discoverability of configuration were ignored completely. I wasn't thrilled with the crummy defaults, but seeing that the consequences had been forseen, considered, and dismissed despite what seemed like widespread consensus on the fix being simple... it really damaged my trust. How many more landmines did SQLite have? Lack of perf tooling + bad defaults = recipe for pain.

dolmen 5y ago
Could you tell us more about that configure option?
- jjoonathan 5y ago
  This happened in 2013. A few years later the fix went through. The bug itself is water under the bridge, what you're hearing is that I'm still leery of "no perf tools + no perf foolproofing." These judgements may be obsolete -- once burned, twice shy.
srcreigh 5y ago
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.
remus 5y ago
Not to diminish your issue but choosing good defaults for software with a wide range of use cases is hard. One person's blindingly obvious use case is another's niche.
- jjoonathan 5y ago
  Exactly. That's why neglecting tools in favor of "simplicity" is so dangerous. If it Just Works, great! But what if it Just Doesn't?

dolmen 5y ago

Could you tell us more about that configure option?

jjoonathan 5y ago
This happened in 2013. A few years later the fix went through. The bug itself is water under the bridge, what you're hearing is that I'm still leery of "no perf tools + no perf foolproofing." These judgements may be obsolete -- once burned, twice shy.

srcreigh 5y ago

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.

remus 5y ago

Not to diminish your issue but choosing good defaults for software with a wide range of use cases is hard. One person's blindingly obvious use case is another's niche.

jjoonathan 5y ago
Exactly. That's why neglecting tools in favor of "simplicity" is so dangerous. If it Just Works, great! But what if it Just Doesn't?