quda

Phoenix json API extremely slow when serving CSV files

I am building a json REST API with Phoenix that (among other services) has to convert static CSV files from a repository and “serve” them as response in json format; that will be used downstream by other services in our project. Some of these csv files are quite big, ranging for a several hundred kb to tens of MB.
So far so good, I made it work with several lines of code.
So, in my backend side:

 defp csv(file) do
   file
    |> Path.expand(__DIR__)
    |> File.stream!()
    |> CSV.decode(headers: true)
    |> Enum.map(fn {:ok, val} -> val end)
  end

in the API controller:

  def show(conn, bhandler) do
    conn
    |> Plug.Conn.put_resp_header("content-type", "application/json; charset=utf-8")
    |> Plug.Conn.send_resp(200, Jason.encode!(bhandler, pretty: true))
  end
end

The program is doing well what is supposed to do but on small files.
When the file gets bigger and response time gets exponentially bigger:

for 69kb (csv file) → 124ms
234kb → 2.8s
1.1MB → 24s
22.4MB → 835s

Obviously something is very wrong with these implementation, or with its libraries (CSV, Jason). A similar (simple) API we have, in php, took a few of seconds to read/convert/send/receive 40MB csv file.

Do you have any suggestion ?

48 comments

/phoenix #api #csv #json-api

61 3961 48

2021-11-19 12:34:34 UTC

Marked As Solved

lud

I used the 10000_sales.csv file from NimbleCsv, I duplicated the content until the total size was 20M (19.2 Mio). It has 15 columns.

Now with this code using chunks it takes 2.8 seconds with wget to download the JSON data (size is 50M), although in the browser it loads forever.

defmodule XsvWeb.PageController do
  use XsvWeb, :controller

  alias NimbleCSV.RFC4180, as: CSV

  def index(conn, _params) do
    conn = send_chunked(conn, 200)
    send_csv(conn, "big_sales.csv")
  end

  defp send_csv(conn, file) do
    csv_stream = csv_stream(file)
    {:ok, conn} = chunk(conn, "[")

    conn =
      Enum.reduce_while(csv_stream, conn, fn chunk, conn ->
        case Plug.Conn.chunk(conn, chunk) do
          {:ok, conn} ->
            {:cont, conn}

          {:error, :closed} ->
            {:halt, conn}
        end
      end)

    {:ok, conn} = chunk(conn, "]")
    conn
  end

  defp csv_stream(file) do
    headers = headers(file)
    headers |> IO.inspect(label: ~S[headers])

    stream =
      file
      |> File.stream!()
      |> CSV.parse_stream(skip_headers: true)

    stream
    |> Stream.take(1)
    |> Stream.map(fn x -> Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!() end)
    |> Stream.concat(
      stream
      |> Stream.drop(1)
      |> Stream.map(fn x ->
        [?,, Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!()]
      end)
    )
  end

  defp headers(file) do
    file
    |> File.stream!()
    |> Stream.take(1)
    |> CSV.parse_stream(skip_headers: false)
    |> Enum.at(0)
  end
end

Note that the browser makes it slow to display but with fetch("http://localhost:4000/").then(x => x.json()).then(x => console.log('x', x)) it is still less than 4 seconds for 54.8 MB of JSON.

Now @quda I know it is not the original problem but converting to JSON makes the final file size more than twice as big. Is it possible to just send the CSV file? or JSON arrays instead of objects?

That being said I do not think it is possible to beat something as simple as basic PHP like you said:

<?php
$csvFile = file('big_sales.csv');
$data = [];
foreach ($csvFile as $i => $line) {

   if ($i == 0) {
      $headers = str_getcsv($line);
   } else {
      $row = str_getcsv($line);
      $data[] = array_combine($headers, $row);
   }

}

echo json_encode($data, JSON_PRETTY_PRINT);

Post #23

Also Liked

mmmrrr

?, means: "give me the codepoint of the comma (in this case it returns 44, which you can try on iex: iex> ?,). You can read about the usages of IO data here: IO — Elixir v1.12.3

So this function does (as I understand it):

    stream
    # Take the first line from the CSV (since we used "skip_headers" this is the first line of data)
    |> Stream.take(1)
    # 1. Merge the headers of the csv with the data we just read into tuples of {HEADER, DATA}
    # 2. Make a new map from that list of tuples
    # 3. Encode the map as json and output it as io_data instead of as string
    |> Stream.map(fn x -> Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!() end)
    # We now have a list of io_data lists, so something like: `[ [12, 31, 55], [13, 14, 99], ... ]`
    # which goes as the first argument into Stream.concat/2
    |> Stream.concat(
      # The second argument to the concat function
      stream
      # But this time we remove the first line...
      |> Stream.drop(1)
      # ... map over _each other line_ in the csv...
      |> Stream.map(fn x ->
        # ... and then prepend it with a comma in codepoint format
        [?,, Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!()]
      end)
    )

All this will result in something like this {"hello": "world"}, {"hello": "foo"}, {"hello": "bar"} (notice that we don’t have a comma at the first position?). The send_csv function wraps that into [ and ] which makes it a valid json array. This whole thing with the code points is probably done for performance reasons in this case and I personally have not had the need to use this form. This is relatively advanced stuff for a relatively advanced problem.

So please don’t beat yourself up about this. We’re all here to learn and to give back where we can! This forum, or rather it’s management and the people in it, might just be the best feature of Elixir in general - and that says a lot.

Post #38

quda

My petty contribution to the solution of @lud with Stream.intersperse/2:
file

...
|> Stream.intersperse(",")
|> Stream.chunk_every(2)

By applying Stream.every/2 with 2 as argument new chunks are created into the stream by combining the previous records with the comma from intersperse.
Proud of my finding!

Post #47

dimitarvp

I didn’t mean to be rude and sorry if it read that way – my point is that without some level of understanding we cannot help you because you might not understand and not be able to apply the suggestions given to you. So then how can we help you?

What I suggested to you is: integrate all processing of the CSV file together in one place so you can reap the full benefits of streaming and save CPU time and memory. If you need an even more detailed example, I’ll be happy to give it to you – can even make a GitHub repo for you if you provide some sample CSV data we can work with. (E.g. how much columns does your CSV file have?)

One of my first tasks with Elixir – 5.5 years ago now – was exactly to process hundreds of megabytes of CSV and XML files per hour. On a virtual hosting with a 4-core vCPU and using full parallelism (using Task.async_stream or Flow) I wasn’t able to load the CPU to more than 60% while still ingesting several CSV / XML files with sizes 500MB+, per hour. There are ways and we here are trying to show you those ways.

I don’t have a horse in this race – just don’t be too quick to discount Elixir as unsuitable. There’s a lot that can be done. And you won’t regret sticking with Elixir. It saves you from a ton of problems that no other language has solved yet (like transparent parallelism and concurrency).

Post #32