Performance issues with huge ecto datasets involving mutliple ecto select and updates

Hello,

Suppose I have a database table with hundreds of thousands of records for three different users who want to send birthday wishes to their different customers via email and text message.
How do I achieve this by concurrently retrieving the many records of all three users from the table and sending out the notification, while adjusting their billing account balances for each birthday alert sent out without sacrificing performance?

The current challenge is that the records are retrieved in a sequential order from the table and it takes several hours, even days, to process all requests because of the updates that must happen at the same time the requests are being pushed out to the customers.
Besides, until all records for one user are processed sequentially from the table, the next user’s records are pending in the table.

I would be grateful for any ideas on how to overcome this in my Elixir code.

Thanks.

it’s difficult to understand what is your actual usecase. why are the requests taking so long. this is what i would do to debug database performance:

  1. check that you have created indexes in the migration files
  2. use postgre’s native EXPLAIN command to figure out if it is actually using indexes.

but really it’s confusing what is actually slow. is the db or is it your elixir code? remember to do as much calculations on the DB side as possible.

you really need to measure what exactly takes time and then ask a more specific question.

2 Likes

Spot on. In this day and age, hundreds of thousands of records is a tiny amount of data for a database to manage. Pretty well any manipulation should be seconds at most.

I’d be looking at locking / deadlocking potential issues first (this includes transaction scopes with a lot of work in them - e.g. are you holding a DB transaction open while you are waiting for an email to send), and big nested loops second. You may reduce your search space by checking whether app or db is consuming CPU.

1 Like

Thanks very much, @KristerV, for your suggestions.
I am currently exploring the indexing idea to see how best it can help improve the performance of the application.

Regards,

Jerry

Thanks, @mindok.
Suggestion well noted.

I am exploring all the ideas suggested here on this platform.

Grateful for your inputs.

Regards,

Jerry

As with anything that is “too big” break it down it to smaller parts.

I just personally ran a query that first read 50k+ worth of ids then re ran a query for all of the ids to create a record on the same db. It took less than 10 seconds to run. Though this would easily timeout on my cheap heroku instance if not for breaking it into smaller parts.

alias MyApp.MyResource

query =
  from(r in MyResource,
    select: [r.id],
    where: r.is_deleted == false,
    order_by: [desc: r.inserted_at]
  )

chunk_size = 1000

Stream.resource(
  fn -> 0 end,
  fn
    :stop ->
      {:halt, :stop}

    offset ->
      rows = Repo.all(from(query, limit: ^chunk_size, offset: ^offset))

      if Enum.count(rows) < chunk_size do
        {rows, :stop}
      else
        {rows, offset + chunk_size}
      end
  end,
  fn _ -> :ok end
)
|> Stream.chunk_every(1000) \
|> Elixir.Task.async_stream(fn ids -> 
  List.flatten(ids) |> Enum.map(fn id -> MyApp.SomeBgJob.new(%{"id" => id}) end) |> Oban.insert_all() end, max_concurrency: 1) \
|> Enum.to_list()

This was pulled right from the ecto docs around stream.

The idea here is we first break up the request into smaller chunks using streams. I would start there.
Note I even limited the max_concurrency so that I didnt tax my db connection pool.

Extra credit. I highly recommend reading Concurrent Data Processing in Elixir: Fast, Resilient Applications with OTP, GenStage, Flow, and Broadway by Svilen Gospodinov

2 Likes