Enabling `WAL` mode at scale increases failures and timeouts

[badlydrawnrob]

I'm using Piccolo with SQLite and have run quite a few tests with Bombardier. You can recreate this test with this app and this setup.

Findings

SQLite writes one at a time and blocks reads: WAL mode should help here!

However aiosqlite fails when the concurrency is high (100 concurrent writes).

• WAL mode seems to work fine with less than -c 75 connections
• WAL mode at scale (-c 100) makes success rates mostly fail!

Without WAL mode the endpoint with -c 100 has a 99% success rate.

Errors

I'm assuming this is mostly due to database locking. 500 internal server error is sqlite3.OperationalError: unable to open database file.

Bombarding http://localhost:8000/event/new with 10000 request(s) using 100 connection(s)
 10000 / 10000 [=============================================] 100.00% 585/s 17s
Done!
Statistics        Avg      Stdev        Max
  Reqs/sec       593.62    1170.57    7255.60
  Latency      153.33ms   806.26ms     10.01s
  HTTP codes:
    1xx - 0, 2xx - 440, 3xx - 0, 4xx - 0, 5xx - 1312
    others - 8248
  Errors:
    the server closed connection before returning the first response byte. Make sure the server returns 'Connection: close' response header before closing the connection - 8228
       timeout - 20