profile picture

Testing Crystal SQLite3 PRAGMAs usage and some HTTP benchmarks 👨‍🚀🚀

February 11, 2023 - crystal sqlite3 benchmark

Wanted to test the PRAGMA changes to crystal-sqlite3 and perform some simple HTTP benchmarks, just for fun 😊

This uses the work shown on previous TIL, and the code from [PR #85][pr-85].

▶️ Similar to previous TIL, I made a recording of this test (and the errors) 💣 here.

1. A simple HTTP app that uses SQLite3

Started setting up a simple repository and define the dependencies that I needed in shard.yml:

+dependencies:
+  db:
+    github: crystal-lang/crystal-db
+    version: "~> 0.11.0"
+  sqlite3:
+    github: crystal-lang/crystal-sqlite3
+    version: "~> 0.19.0"

Installed using shards install and proceed to write the application code:

require "http/server"
require "sqlite3"

class RandomContactApp
  include HTTP::Handler

  getter db : DB::Database

  def initialize(@db)
  end

  def call(context)
    rand_id = rand(1..500)
    name = fetch_name(rand_id)

    context.response.content_type = "text/plain"
    context.response.print "Hello #{name}!"
  end

  private def fetch_name(id)
    db.scalar("SELECT name FROM contacts WHERE id = ? LIMIT 1;", id).as(String)
  end
end

A simple HTTP::Handler for my app. This is the most basic HTTP service you can create without the need of a web framework (you know, less is more 😉).

This app simply picks a random number (to be used for the id column) and then look up in the database for the name column of that record, returning it in the response as simple text/plain.

Next, we start the application and make sure we can stop the server when pressing Ctrl+C 😅:

app = RandomContactApp.new(db)
logger = HTTP::LogHandler.new
server = HTTP::Server.new([logger, app] of HTTP::Handler)

Signal::INT.trap do
  puts "Shutting down."
  server.close
end

server.bind_tcp "0.0.0.0", 8080

puts "Listening on:"
server.addresses.each do |addr|
  puts "- #{addr}"
end
puts "Use Ctrl-C to stop"

server.listen

puts "Shutdown completed."

2. The UX improvements for managing the connection

In order to pass a DB connection to my app, I would used something like the following:

db = DB.open("sqlite3://./contacts.db")
db.setup_connection do |conn|
  # 1. Avoid writers to block readers by using WAL mode
  # Ref. https://sqlite.org/pragma.html#pragma_journal_mode
  conn.exec "PRAGMA journal_mode = WAL;"

  # 2. Use normal synchronization to speed up operations (good combo with WAL)
  # Ref. https://sqlite.org/pragma.html#pragma_synchronous
  conn.exec "PRAGMA synchronous = NORMAL;"

  # 3. Increases cache size available (from 2MB to 16MB in KB)
  # Ref. https://sqlite.org/pragma.html#pragma_cache_size
  conn.exec "PRAGMA cache_size = -16000;"

  # 4. Allow waiting on periodic write locks from replication (or other process)
  # Refs.
  # - https://www.sqlite.org/pragma.html#pragma_busy_timeout
  # - https://litestream.io/tips/#busy-timeout
  conn.exec "PRAGMA busy_timeout = 5000;"
end

But thanks to the work done in PR #85, now I can manage all that in a single line:

db = DB.open("sqlite3://./contacts.db?journal_mode=wal&synchronous=normal&cache_size=-16000&busy_timeout=5000")

And remove the entire db.setup_connection block. 😄

Since the changes hasn't been merged yet, we need to point to my fork, so let's update shard.yml one more time:

-  sqlite3:
-    github: crystal-lang/crystal-sqlite3
-    version: "~> 0.19.0"
+  sqlite3:
+    github: luislavena/crystal-sqlite3
+    branch: accept-pragmas-for-connection-uri-query

And let's make sure the dependency gets updated:

$ shards install
Resolving dependencies
Fetching https://github.com/crystal-lang/crystal-db.git
Fetching https://github.com/luislavena/crystal-sqlite3.git
Using db (0.11.0)
Using sqlite3 (0.19.0 at caebc0a)

3. Using some dummy data

For this example decided to populate 500 entries of fake contacts, using a simple script:

require "sqlite3"

CREATE_SQL = <<-SQL
  CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
  );
SQL

INSERT_SQL = <<-SQL
  INSERT INTO contacts (id, name, age) VALUES (?, ?, ?);
SQL

DB.open("sqlite3://./contacts.db") do |db|
  db.exec CREATE_SQL

  db.transaction do
    500.times do |t|
      id = t + 1
      db.exec INSERT_SQL, id, "Name #{id}", id + 20
    end
  end
end

Named it generate and run it once to populate our contacts.db file.

4. Compiling our application

Since I mostly use containers to run my applications, decided to compile a static binary of my server so I could run that on the host OS (PopOS) instead of my container (Alpine Linux).

Used for this my hydrofoil-crystal container image that includes all the necessary dependencies (Eg. SQLite3) to produce these static binaries.

Added to my shard.yml the targets, but you could use crystal build directly:

 authors:
   - Luis Lavena <[email protected]>
+targets:
+  generate:
+    main: src/generate.cr
+  server:
+    main: src/server.cr
 dependencies:

And build the server in release (optimized) mode:

$ shards build server --release --static 
Dependencies are satisfied
Building: server

$ file bin/server
bin/server: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), statically linked, with debug_info, not stripped

5. Simple benchmarks

Before we benchmark, let's remove the logger as sending stuff to STDIO has an impact on performance too (remember: gotta go fast!⚡)

 app = RandomContactApp.new(db)
-logger = HTTP::LogHandler.new
-server = HTTP::Server.new([logger, app] of HTTP::Handler)
+server = HTTP::Server.new([app] of HTTP::Handler)

⚠️ Please note that the following benchmarks are synthetic and by no means they would represent real workloads or real world scenarios, where network overhead, latency, TLS negotiation and other factors would have an impact on the throughput (RPS) of your application.

Now that you've been warned, let's look at these numbers using plow:

$ plow -c 1 -d 10s http://localhost:8080
Benchmarking http://localhost:8080 for 10s using 1 connection(s).
@ Real-time charts is listening on http://[::]:18888

Summary:
  Elapsed        10s
  Count       146032
    2xx       146032
  RPS      14602.535
  Reads    1.445MB/s
  Writes   0.808MB/s

Statistics    Min       Mean    StdDev    Max   
  Latency     48µs      66µs     15µs   1.011ms 
  RPS       14178.55  14602.01  398.16  15328.13

Latency Percentile:
  P50   P75   P90   P95    P99   P99.9  P99.99
  62µs  71µs  77µs  85µs  142µs  191µs  333µs 

Latency Histogram:
  63µs   117952  80.77%
  72µs    20344  13.93%
  87µs     5580   3.82%
  128µs    1594   1.09%
  165µs     414   0.28%
  191µs     125   0.09%
  293µs      20   0.01%
  420µs       3   0.00%

14K RPS for a single-threaded server that randomly reads records from a DB, not bad.

But what about 5 or 10 concurrent connections:

$ plow -c 5 -d 10s http://localhost:8080
Benchmarking http://localhost:8080 for 10s using 5 connection(s).
@ Real-time charts is listening on http://[::]:18888

Summary:
  Elapsed        10s
  Count       210278
    2xx       210278
  RPS      21027.704
  Reads    2.081MB/s
  Writes   1.163MB/s

Statistics    Min       Mean    StdDev    Max  
  Latency     49µs     235µs     28µs   1.513ms
  RPS       20424.45  21024.54  402.87  21766.7

Latency Percentile:
  P50     P75    P90    P95    P99   P99.9  P99.99
  230µs  248µs  261µs  269µs  372µs  426µs  540µs

$ plow -c 10 -d 10s http://localhost:8080
Benchmarking http://localhost:8080 for 10s using 10 connection(s).
@ Real-time charts is listening on http://[::]:18888

Summary:
  Elapsed        10s
  Count       211867
    2xx       211867
  RPS      21186.662
  Reads    2.097MB/s
  Writes   1.172MB/s

Statistics    Min       Mean    StdDev    Max   
  Latency     49µs     470µs     56µs   4.638ms 
  RPS       20653.64  21184.31  485.09  21902.19

Latency Percentile:
  P50     P75    P90    P95    P99    P99.9   P99.99 
  470µs  496µs  516µs  535µs  657µs  1.032ms  1.466ms

A nice increase from 1 connection to 5, but not a great change from 5 to 10 concurrent connections.

Remember: this server was compiled using Crystal single-thread model, so it might not be taking advantage of all available CPUs. 🤔

6. Danger zone: multi-thread compilation 💣

Crystal introduced multi-thread support in 2019, but only in preview mode, which requires you use a compilation option to enable it: -Dpreview_mt

Decided to compile the server with that option and testing with 1 concurrent connection worked, but other concurrency options just made it crash:

$ CRYSTAL_WORKER=1 bin/server
Listening on:
- 0.0.0.0:8080
Use Ctrl-C to stop
Invalid memory access (signal 11) at address 0x7fc495324958
[0x4766b0] ???
[0x476565] ???
[0x8855ca] ???

I will investigate further testing crystal-sqlite3 in multi-thread mode and report that back to the project. 🕵️

Have a nice weekend!