Comment by nlh | Hacker News Reader

Comment by nlh | Hacker News Reader

I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance).

But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions:

My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run several million database transactions, N+1 queries, etc. Because of the high number of IOPS needed, a small instance falls down and runs suuuuuper sluggishly, so I've found myself needing to shut everything down, resize the instance to something with more CPUs, memory, and IOPS ($$$), doing the big batch, then scaling down again. That whole dance is a pain.

Were I using a more traditional postgres setup, I'd probably architect this differently -- the day-to-day stuff I'd run on Cloud Run and I'd spin up a separate beefy instance just for the daily batch job rather than resizing one instance up and down over and over again. The constraint here is that I have a 50GB+ sqlite db file that basically lives on local SSD.

Any thoughts?

Interesting problem. Did you try grouping up transactions? Ex instead of a few hundred million txns, do a few hundred thousand 1000op chunk txns. SQLite is much much faster within a txn.

Edit: a several hundred million txns over a few hours math. How many per second? ~500

According to here (question 19), for old HDDs you could expect 3 orders of magnitude improvement by using bigger txns. Not sure SSD wise but worth a shot.

https://www.sqlite.org/faq.html

I have not tried that yet (I was being lazy since it all ran so fast on my M1), but that's a good idea for something to investigate. That way I suppose I can run the whole thing on a less-beefy instance and avoid the scale-up/scale-down cycle.

Yeah I think if you are doing 1 txn per second roughly instead of 100s, it may just work fine. I'm curious how it goes so shoot me an email if you want with updates

Maybe use a disk that's "independent" on the VM? That way you can spin up a temporary beefy VM, attach the disk and get instant access to your DB.

Never tried, but I believe that's how these GCP disk work: https://cloud.google.com/compute/docs/disks#pdspecs

How do you deal with the downtime. I also find myself constantly resizing instances (although for data analysis where I sometimes need 80+ cores only for a few hours), and the constant restarting and reloading drives me crazy

Maybe for sqlite because it's stored in ssd, it's only 1-2 minutes to shutdown+change parameter+restart if it's scripted?

So far, I just deal with the annoyance. It is indeed only a <5 minute operation, but it's just a pain (shut services down, shut instance down, resize, start instance, start services).

The problem is it's the exact kind of pain that I avoid, so many times I just keep the larger (8-core) instance up and just eat the cost. I should probably script this to solve this pain point, but I'd SO rather have a setup that doesn't require this sort of jiggering.

Maybe `BEGIN CONCURRENT` [1] could help in your case? :thinking:

[1] https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

Interesting - I'll explore! Thanks.

Reply on news.ycombinator.com