Storage for a web server?

Learn
#1

Merry Xmas all,

Do you have a recommandation for a simple, embedded storage for a web server application.

I was pretty happy with SQLite.Simple until I went into concurrency issues - Sometimes a web query hangs (?!). And noticed I used a single connection for every request for every thread.

Statement of the problem

  • I have several potentially concurrent read/write request to a Storage
  • SQLite Simple is tied to SQLite which isn’t thread safe (no reproducible bug so far, just guessing)
  • I’d like to keep it embedded - flat files with Read/Show is suitable if you recommend it

What would you recommend as a simple embedded Storage for a web server - again without any performance/load issue ?

2 Likes
#2

Maybe manually download something like this - GitHub - zonkyio/embedded-postgres-binaries: Lightweight bundles of PostgreSQL binaries with reduced size intended for testing purposes. ? Or use regular postgres and jsonb? Or use redis with persistence?

#3

SQLite3 is thread safe by default Using SQLite In Multi-Threaded Applications
But using a single connection for multiple threads might be problematic from a logic perspective. Why not use multiple connections?

#4

But using a single connection for multiple threads might be problematic from a logic perspective.

Apparently, even in the default “serialized” mode, “there is no isolation between operations that occur within the same database connection.”

If that’s the problem, perhaps introducing some pooling library like resource-pool to manage connections could be the simplest way forward.

2 Likes
#6

@kanishka : If postgres can be embedded (in Haskell ?) I will try. Thanks for the pointers
@gilmi : I was fairly newbie when I wrote this. It boils down to :

1.I’m using warp runSettings
2. How to check if each request is on its own thread ?
3. In that case will multiple threads accessing the same “Game” - basically a unique row of the single, unique table - conflict/corrupt the db ?

It looks like I now have two options :

  1. Embrace concurrency and switch to Postgres (goodbye the dream of having one single, simple, self-contained executable, hello the external DB and the setup)

  2. Write custom logic with some kind of MVars or STM : After all the data model is

    Server -- one instance
    [Game] -- a list of
    [Player] -- 4 players act one a single Game
    -- Each player plays *in turn* there is no risk or need for concurrent modification

#7

I believe warp does fork a new thread for each request.

I’m not sure what the logic of the game is - some games require that only one user can update the game/database in each given time and they cannot compete - say the game is in state A, then user1 sees A and wants to change it to B, but also at the same time user2 sees A and wants to change it to C - should they be able to compete for updating the state, or should one be able to update and the second update fail?
On the other hand, concurrent updates might be fine for other cases, for example user1 changes their coordinates on the map and user2 changes their hairstyle - the order of operations is not important.

For scenario 1 you might want to force serial updates and not have users compete, for scenario 2 you can use multiple threads, just make sure to use multiple connections.

I don’t think postgres or sqlite is the question, but rather which scenario do you have.
I would also suggest reading the docs for sqlite-easy to get a better understanding of db usage such as migrations, resource-pooling/multiple connections, and transactions.

#8

The scenario is

  • No concurrent update
  • A plays, then B, then C, D, then A again
  • Every tentative of update by a player outside of its turn is rejected

I could rely on flat files (that’s what I did in the first versions) except for the home screen which lists the (read-only) state of each game : ex:

  • game “My xmas party” was started 3 mins ago and the four places are taken
  • …
  • game “My new one” was created 10secs ago, Alice and Bob have registered, 2 places remaining

This listing triggered a full scan of the games which made me switch to SQLite. Out of curiosity I was wondering if an ad hoc solution existed (having some very simple persistence without relying on a third party product)

suggest reading the docs for sqlite-easy

Will do, thanks

#9

After weeks of test it looks like, even using a single connection SQLite runs flawlessly

  • I know (been programming decades of Java) that for complex queries, transactions and multiple table joins you have to use a connection pool and do things properly
  • Can you comment on this small threads stress test : SQLiteFlawless.hs · GitHub ?
    • 20 threads (forkIO) using a single SQLite connection
    • The database is never corrupt, and randomly contains the name of the last thread which wrote a value
    • During execution a SELECT works - but lags a little
    • After execution the database is readable by sqlite3 CLI

SQLite runs flawlessly even if the threads are slowed because waiting for some internal MVar
=> I will keep on using SQLite with warp :slight_smile: