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

SuperSQLite: SQLite library for Python (2018)

github.com

Post reply on HN
Print view View original

51–60 of 78 posts

Re: SuperSQLite: SQLite library for Python (2018)

#51

Post by skrebbel »

marktangotango wrote: post #39
dest wrote: post #9

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performa…

When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Po…

I really like this idea.

Does anyone use this seriously in production on a typical web service? I wonder about how eg backups and stuff like that work out in real scenarios.

Re: SuperSQLite: SQLite library for Python (2018)

#52

Post by skrebbel »

firebacon wrote: post #47
marktangotango wrote: post #43

Earlier quoted context omitted.

For reads just create a new connection for every request (be sure to set connection properties for wal etc). Then create a global (or something equivalent to global, like a singleton) connection to serve as the writer and put a mutex around it when doing writes. Easy, scales like a mofo.

What does "mofo" mean here. Can you give us a rough estimate on the transaction rate you achieved with this setup? My own experience and all independent benchmarks I can find seem to indicate a limit of 100-1000TPS on reasonable hardware. Note that you can "batch" up many inserts into a transaction, which gives you a high "nominal" insert rate but still only ~100 actual transactions or so per second. To see why this…

> What does "mofo" mean here

Motherfucker

Re: SuperSQLite: SQLite library for Python (2018)

#53

Post by marktangotango »

firebacon wrote: post #47
marktangotango wrote: post #43

Earlier quoted context omitted.

For reads just create a new connection for every request (be sure to set connection properties for wal etc). Then create a global (or something equivalent to global, like a singleton) connection to serve as the writer and put a mutex around it when doing writes. Easy, scales like a mofo.

What does "mofo" mean here. Can you give us a rough estimate on the transaction rate you achieved with this setup? My own experience and all independent benchmarks I can find seem to indicate a limit of 100-1000TPS on reasonable hardware. Note that you can "batch" up many inserts into a transaction, which gives you a high "nominal" insert rate but still only ~100 actual transactions or so per second. To see why this…

Test it yourself, you can try all the different combinations of journalling mode and synchonous modes. I'm not here to proscribe anything to anyone. Every use case is different.

Re: SuperSQLite: SQLite library for Python (2018)

#54

Post by Moto7451 »

marktangotango wrote: post #39
dest wrote: post #9

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performa…

When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Po…

I had great success using SQLite as the backing store for fast sharing and reindexing of a domain specific search engine component my company licensed. Before we developed our own algorithms and moved to Elasticsearch I was playing with augmenting the licensed component with the full text search capabilities of SQLite. The whole thing ended up being shuffled around via Gluster so I was able to offload the responsibility for sharing shards to it.

My experience pretty much matched what you describe and it was such a great opportunity to really lean on a fabulous piece of software. Given this was many years ago, before ES was stable and SOLR wasn't working well for us, I think it was the right choice. These days, ES is good enough that if I had to do it all over I'd go straight to it. None the less, I don't think SQLite should be ignored as an option when you have high levels of control over data access patterns.

Re: SuperSQLite: SQLite library for Python (2018)

#55

Post by no_wizard »

skrebbel wrote: post #52
firebacon wrote: post #47

Earlier quoted context omitted.

What does "mofo" mean here. Can you give us a rough estimate on the transaction rate you achieved with this setup? My own experience and all independent benchmarks I can find seem to indicate a limit of 100-1000TPS on reasonable hardware. Note that you can "batch" up many inserts into a transaction, which gives you a high "nominal" insert rate but still only ~100 actual transactions or so per second. To see why this…

> What does "mofo" mean here Motherfucker

I think they meant what makes it a positive `mofo`, if you will.

Re: SuperSQLite: SQLite library for Python (2018)

#56

Post by skrebbel »

no_wizard wrote: post #55
skrebbel wrote: post #52

Earlier quoted context omitted.

> What does "mofo" mean here Motherfucker

I think they meant what makes it a positive `mofo`, if you will.

Ah yes of course. Not deleting my comment because I think my misunderstanding is funny.

Re: SuperSQLite: SQLite library for Python (2018)

#58

Post by no_wizard »

rogerbinns wrote: post #44
nerdponx wrote: post #37

Earlier quoted context omitted.

In the standard library? Probably nothing. But if someone published an alternative APSW wheel with JSON1, ICU, and FTS5 enabled, I'd be happy.

I'm the APSW author. The binary builds for Windows are distributed with those extensions all compiled in, although my doc needs some updating. It is also only a single flag for other platforms to include all extensions during compilation. What can I do?

Whats the hold back to getting it via pip install via a wheel? I'm curious (happy APSW user here). Its not well elaborated from what I could find. This is something I would love to help with but not sure where to start, as I'm not sure where in the process of doing the builds it falls down.

Would Cython help, perhaps?

Re: SuperSQLite: SQLite library for Python (2018)

#59

Post by patelajay285 »

nabdab wrote: post #11

This looks like a dead project that just bundles together the built-in sqlite3 And another wrapper library APSW which itself is more active than this project. Why is this on hn?

Hi, I'm the author! It's not a dead project, it's just not released yet, someone seems to have submitted it early :). Will post it back here when it's ready for prime time.

Re: SuperSQLite: SQLite library for Python (2018)

#60

Post by patelajay285 »

Gys wrote: post #2

Its supports 'Remote Streaming over HTTP' without explaining what that means. Maybe someone here knows?

I'm the author, yes @niea_11's comment is right. We add a HTTP Virtual File System. So you can stream the SQLite files over a static HTTP Server like Amazon S3 using HTTP Range Headers to do it efficiently. No dynamic web software needed.
Post reply on HN