Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Learning a few things about running SQLite

jvns.ca

Post reply on HN
Print view View original

11–20 of 101 posts

Re: Learning a few things about running SQLite

#11

Post by cogman10 »

simonw wrote: post #8
striking wrote: post #5

> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…

I've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.

Yeah, I think anyone that's done significant database work has come to the understanding that large updates need to be done in batches, otherwise you nuke performance.

Once you get to about 1M rows of data, batching is essential.

Re: Learning a few things about running SQLite

#12

Post by andrewaylett »

I run my backups like this:

    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
That doesn't block writers (when the writer uses WAL), and gives me a dump that's compressed well while also being easy to sync. My Home Assistant DB is 1.8GB, my dump is 286MB compressed, and I'd guess 90% of that is consistent from one day to the next.

Re: Learning a few things about running SQLite

#13

Post by EvanAnderson »

m0ose wrote: post #9

What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?

I don't call it a "dead man's switch", but I absolutely monitor some directories for new newest file. If the backup monitoring script doesn't find a file less than 24-ish hours old in the backup destination directory at any time it should send me an alert.

Re: Learning a few things about running SQLite

#14

Post by striking »

DANmode wrote: post #6
striking wrote: post #5

> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…

> they just tend to make it less obvious you're doing something unperformant Is this being positioned as a strength, in your comment?

It just is what it is. Sometimes you want to write the obvious query without the DB getting in your way, and other times you want to know as soon as possible that you're doing something that won't scale under exponential load. At this point in my career I prefer the latter, but the former will always have a special place in my heart.

Re: Learning a few things about running SQLite

#15

Post by The_Fox »

m0ose wrote: post #9

What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?

https://en.wikipedia.org/wiki/Dead_man%27s_switch

In this context it means upon a successful backup, update a timestamp somewhere. Some other system monitors the timestamp and if it ever becomes more than for example 1 day ago, it fires an alert.

Re: Learning a few things about running SQLite

#17

Post by kevincox »

m0ose wrote: post #9

What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?

Dead-man's switch means triggering when something doesn't happen. (The name comes from a switch that an alive operator would need to hold in such a way that if they died they would stop holding.) So in this case she means that her monitoring will fire if there wasn't a successful backup within some configured period of time.

I assume this is opposed to alerting when the backup job fails, which is an issue if the job never runs, or hangs forever, or crashes in a way that doesn't trigger your monitoring.

However I don't see how any of this solves the issue of not testing your backup. Because you can definitely have a backup task succeed regularly but the thing it is backing up is still unusable.

Re: Learning a few things about running SQLite

#18

Post by noxer »

As for the DELETE issue the easy solutions are:

-Delete it batches

-Delay between batches

-Preload the rowids before deleteing with SELECT (Select does not block)

Additionally if data was added sequentially primary to the same table the data is likely stored this way in the file and deleting it in this or in reversed order can be faster (depends on storage medium and other factors).

Post reply on HN