exqlite: Performance issues / possible memory leak

Howdy 👋

I’m in the process of converting a market making trading system that creates many orders from ETS to Ecto. I plan on supporting any database that Ecto currently has support for, but I’ve started with SQLite because I want to be able to distribute it without any external dependencies.

SQLite is probably not the best choice for this kind of system due to the high number of writes. But I’ve pushed forward with the philosophy that if I can make it work somewhat performant in SQLite it should be great with other DB’s.

Everything has gone pretty well so far, however now that I’m running it in production for long periods of time I’m noticing that there is severe performance degradation over time that causes a total lock in the SQLite DB. I’ve attached a graph below of my telemetry output. It shows:

• Linear memory growth
• Periodic spikes down in memory (I’m assuming GC or freed memory)
• Eventually a total lock where the metrics are no longer getting updated

I’m also attaching a graph of query times with the hope that they’re helpful

The following SQL statements are executed regularly as the hot path

06:12:03.214 [debug] QUERY OK source="orders" db=1.1ms idle=181.7ms
SELECT o0.client_id, o0.close, o0.credential, o0.cumulative_qty, o0.last_received_at, o0.last_venue_timestamp, o0.leaves_qty, o0.post_only, o0.price, o0.product_symbol, o0.product_type, o0.qty, o0.side, o0.status, o0.time_in_force, o0.type, o0.venue, o0.venue_order_id, o0.venue_product_symbol, o0.inserted_at, o0.updated_at FROM orders AS o0 WHERE (o0.client_id = ?) [<<14, 229, 20, 50, 21, 17, 75, 71, 159, 117, 143, 85, 153, 162, 63, 28>>]

06:12:03.217 [debug] QUERY OK db=2.2ms queue=0.1ms idle=111.7ms
begin []

06:12:03.219 [debug] QUERY OK source="orders" db=1.9ms
UPDATE orders AS o0 SET status = ?, leaves_qty = ?, last_received_at = ?, last_venue_timestamp = ? WHERE ((o0.client_id = ?) AND o0.status IN (?,?,?,?)) RETURNING client_id, close, credential, cumulative_qty, last_received_at, last_venue_timestamp, leaves_qty, post_only, price, product_symbol, product_type, qty, side, status, time_in_force, type, venue, venue_order_id, venue_product_symbol ["canceled", "0", ~U[2021-06-01 06:12:03Z], ~U[2021-06-01 06:12:02Z], <<14, 229, 20, 50, 21, 17, 75, 71, 159, 117, 143, 85, 153, 162, 63, 28>>, "create_accepted", "open", "pending_cancel", "cancel_accepted"]

06:12:03.222 [debug] QUERY OK db=1.7ms
INSERT INTO order_transitions (order_client_id,transition,id,inserted_at,updated_at) VALUES (?,?,?,?,?) [<<14, 229, 20, 50, 21, 17, 75, 71, 159, 117, 143, 85, 153, 162, 63, 28>>, "\"{\\\"last_received_at\\\":\\\"2021-06-01T06:12:03.212374Z\\\",\\\"last_venue_timestamp\\\":\\\"2021-06-01T06:12:02.986417Z\\\",\\\"__type__\\\":\\\"cancel\\\"}\"", <<29, 70, 168, 213, 105, 47, 77, 115, 169, 224, 255, 68, 17, 101, 121, 245>>, ~U[2021-06-01 06:12:03.219952Z], ~U[2021-06-01 06:12:03.219952Z]]

06:12:03.222 [debug] QUERY OK db=0.7ms
commit []

They’re issued from this Elixir module https://github.com/fremantle-industries/tai/blob/orders-ecto-repo/apps/tai/lib/tai/new_orders/services/apply_order_transition.ex

  defp update_order_and_save_transition(client_id, %transition_mod{} = transition, order_transition_changeset) do
    from_status = transition_mod.from()
    attrs = transition_mod.attrs(transition)
    update_order_query = build_update_order_query(client_id, from_status, attrs)

    # The previous order needs to be selected outside of the transaction to
    # prevent a possible deadlock.
    case OrderRepo.get(Order, client_id) do
      %Order{} = previous_order_before_update ->
        # Check if the existing order has a status that supports this
        # transition in memory and only rely on the transaction rollback
        # as a fallback. There is a performance penalty to rolling back
        # a transaction.
        if Enum.member?(from_status, previous_order_before_update.status) do
          fn ->
            case OrderRepo.update_all(update_order_query, []) do
              {0, []} ->
                status_was = previous_order_before_update.status
                reason = {:invalid_status, status_was}
                OrderRepo.rollback(reason)

              {1, [current_order]} ->
                case OrderRepo.insert(order_transition_changeset) do
                  {:ok, _} -> {previous_order_before_update, current_order}
                  {:error, reason} -> OrderRepo.rollback(reason)
                end

              {:error, reason} ->
                OrderRepo.rollback(reason)
            end
          end
          |> OrderRepo.transaction()
        else
          status_was = previous_order_before_update.status
          reason = {:invalid_status, status_was}
          {:error, reason}
        end

      nil ->
        {:error, :order_not_found}
    end
  end