Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Default to opening files in mutable mode, special option for immutable files #419

Closed
simonw opened this issue Mar 15, 2019 · 10 comments

Comments

Projects
None yet
2 participants
@simonw
Copy link
Owner

commented Mar 15, 2019

One of the original ideas behind Datasette was that serving immutable data makes everything way easier. Two examples: You don't have to worry about SQLite concurrency and you can bundle the database inside a Docker container and deploy it to immutable hosting. See The interesting ideas in Datasette for more on this.

I'm beginning to see a much stronger case for being able to serve mutable data as well.

SQLite is actually perfectly capable of handling reads against a database that is also being written to, even if the writes are coming from another process. https://www.sqlite.org/wal.htm

There are all kinds of interesting use-cases which Datasette is currently unsuitable for due to its insistence on immutable databases. Some examples:

  • Continually run Datasette against a SQLite database updated by another process, e.g. Firefox bookmarks
  • Projects where a cron runs every X minutes and writes new entries gathered from other sources to SQLite
  • Tail a log file, write those log updates to a SQLite file, view recent log entries in Datasette

This is also relevant to #417, Datasette Library.

@simonw simonw changed the title Option to run against DB that may be modified Datasette should work against mutable database files Mar 17, 2019

@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

Thinking about this further: I think I may have made a mistake establishing "immutable" as the default mode for databases opened by Datasette.

What would it look like if files were NOT opened in immutable mode by default?

Maybe the command to start Datasette looks like this:

datasette mutable1.db mutable2.db --immutable=this_is_immutable.db --immutable=this_is_immutable2.db

So regular file arguments are treated as mutable (and opened in ?mode=ro) while file arguments passed using the new --immutable option are opened in immutable mode.

The -i shortcut has not yet been taken, so this could be abbreviated to:

datasette mutable1.db mutable2.db -i this_is_immutable.db -i this_is_immutable2.db
@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

Some problems to solve:

  • Right now Datasette assumes it can always show the count of rows in a table, because this has been pre-calculated. If a database is mutable the pre-calculation trick no longer works, and for giant tables a select count(*) from X query can be expensive to run. Maybe we set a time limit on these? If time limit expires show "many rows"?
  • Maintaining a content hash of the table no longer makes sense if it is changing (though interestingly there's a .sha3sum built-in SQLite CLI command which takes a hash of the content and stays the same even through vacuum runs). Without that we need a different mechanism for calculating table colours. It also means that we can't do the special dbname-hash URL trick (see #418) at all if the database is opened as mutable.

@simonw simonw changed the title Datasette should work against mutable database files Default to opening files in mutable mode, special option for immutable files Mar 17, 2019

@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

Could I persist the last calculated count for a table and somehow detect if that table has been changed in any way by another process, hence invalidating the cached count (and potentially scheduling a new count)?

https://www.sqlite.org/c3ref/update_hook.html says that sqlite3_update_hook() can be used to register a handler invoked on almost all update/insert/delete operations to a specific table... except that it misses out on deletes triggered by ON CONFLICT REPLACE and only works for ROWID tables.

Also this hook is not exposed in the Python sqlite3 library - though it may be available using some terrifying ctypes hacks: https://stackoverflow.com/a/16920926

So on further research, I think the answer is no: I should assume that it won't be possible to cache counts and magically invalidate the cache when the underlying file is changed by another process.

Instead I need to assume that counts will be an expensive operation.

As such, I can introduce a time limit on counts and use that anywhere a count is displayed. If the time limit is exceeded by the count(*) query I can show "many" instead.

That said... running count(*) against a table with 200,000 rows in only takes about 3ms, so even a timeout of 20ms is likely to work fine for tables of around a million rows.

It would be really neat if I could generate a lower bound count in a limited amount of time. If I counted up to 4m rows before the timeout I could show "more than 4m rows". No idea if that would be possible though.

Relevant: https://stackoverflow.com/questions/8988915/sqlite-count-slow-on-big-tables - reports of very slow counts on 6GB database file. Consensus seems to be "yeah, that's just how SQLite is built" - though there was a suggestion that you can use select max(ROWID) from table provided you are certain there have been no deletions.

Also relevant: http://sqlite.1065341.n5.nabble.com/sqlite3-performance-on-select-count-very-slow-for-16-GB-file-td80176.html

@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

So the differences here are:

  • For immutable databases we calculate content hash and table counts; mutable databases we do not
  • Immutable databasse open with file:{}?immutable=1, mutable databases open with file:{}?mode=ro
  • Anywhere that shows a table count now needs to call a new method which knows to run count(*) with a timeout for mutable databases, read from the precalculated counts for immutable databases
  • The url-hash option should no longer be available at all for mutable databases
  • New command-line tool syntax: datasette mutable.db v.s. datasette -i immutable.db
@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

And a really important difference: the whole model of caching inspect data no longer works for mutable files, because another process might make a change to the database schema (adding a new table for example).

https://fivethirtyeight.datasettes.com/-/inspect

So everywhere that uses self.ds.inspect() right now will have to change to calling a routine which knows the difference between mutable and immutable databases and queries for live schema data for mutables while using a cache for immutables.

I'll track this as a separate ticket.

simonw added a commit that referenced this issue Mar 17, 2019

URL hashing is now off by default - closes #418
Prior to this commit Datasette would calculate the content hash of every
database and redirect to a URL containing that hash, like so:

    https://v0-27.datasette.io/fixtures => https://v0-27.datasette.io/fixtures-dd88475

This assumed that all databases were opened in immutable mode and were not
expected to change.

This will be changing as a result of #419 - so this commit takes the first step
in implementing that change by changing this default behaviour. Datasette will
now only redirect hash-free URLs under two circumstances:

* The new `hash_urls` config option is set to true (it defaults to false).
* The user passes `?_hash=1` in the URL

simonw added a commit that referenced this issue Mar 17, 2019

@simonw

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2019

I've added the -i option, so this now works:

datasette -i fixtures.db

This feature is incomplete though. Some extra changes I need to make:

  • The ?_hash=1 and --config hash_urls:1 options (introduced in #418) should only work for immutable databases #471
  • Would be useful if there was a debug screen that could show which databases were mounted as mutable v.s. immutable - maybe a /-/databases page? - #470
  • Need to rework how .inspect() works, see #420
  • Documentation is needed #421
@simonw

This comment has been minimized.

Copy link
Owner Author

commented May 2, 2019

I just closed #420 - all of the places in the codebase that were using .inspect() should have been eliminated.

simonw added a commit that referenced this issue May 2, 2019

Entirely removed table_rows_count table property
We were not displaying this anywhere, and it is now expensive to calculate.

Refs #419, #420
@russss

This comment has been minimized.

Copy link
Contributor

commented May 3, 2019

Are you planning on removing inspect entirely?

I didn't spot this work before I started on datasette-geo, but ironically I think it has a use case which really needs the inspect functionality (or some replacement).

Datasette-geo uses it to store the bounding box of all the geographic features in the table. This is needed when rendering the map because it avoids having to send loads of tile requests for areas which are empty.

Even with relatively small datasets, calculating the bounding box seems to take around 5 seconds, so I don't think it's really feasible to do this on page load.

One possible fix would be to do this on startup, and then in a thread which watches the database for changes.

@simonw

This comment has been minimized.

Copy link
Owner Author

commented May 16, 2019

@russss sorry I only just spotted your comment here.

I think I have an alternative suggestion for what you need to do here. It sounds to me like you need to calculate a specific piece of information against a specific database. Instead of doing this in inspect, how about having a separate tool which runs this once against the database file and writes the result into a database file there?

I've been thinking about this pattern a bit as part of the sqlite-utils work I've been doing. It's already something that's needed for SQLite FTS support - it's no good just creating a FTS index, you have to populate it as well. In sqlite-utils world you do that like this: https://sqlite-utils.readthedocs.io/en/latest/cli.html#configuring-full-text-search

$ sqlite-utils enable-fts mydb.db documents title summary

But then later if you've inserted new records you have to call this:

$ sqlite-utils populate-fts mydb.db documents title summary

So one option here could be for datasette-geo to know to look for a special datasette_geo_bounding_box database table and, if it's missing, to calculate at runtime (probably once on startup and then cache it).

Another option: Datasette now has an option to open a database file in "immutable" mode, using datasette -i mydatabase.db. When you do that we calculate counts on startup - and we'll also be able to load counts from the inspect-data.json file (that's pretty much all that will be in there). I'm open to making this available as a plugin hook - all kinds of optimizations could be run against these -i databases. It would essentially be what we have with inspect today but just for databases opened in that specific mode.

@simonw simonw added the large label May 16, 2019

simonw added a commit that referenced this issue May 16, 2019

@simonw

This comment has been minimized.

Copy link
Owner Author

commented May 16, 2019

This is done bar the documentation, which is tracked in #421

@simonw simonw closed this May 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.