I agree with you and I was skeptical, but I wanted to make sure that argument was well and truly dead. The Changeset vs raw SQL approach benchmarked at almost exactly the same performance. Using Repo.update_all/3 as suggested by @hauleth just now yielded a slight perf boost, but there’s still something larger to be found.
scouten
0
kip
ex_cldr Core Team
Could that be related to pgbench and ecto using different transaction isolation levels? Or perhaps connection contention? Just spitballing because update_all/3 is just a single SQL command so hard to imagine there is material ecto computation behind it beyond transforming types to the binary wire protocol.
1
scouten
Transaction isolation levels rings true as a theory. FWIW we are seeing a pretty substantial fall-off in performance as we increase the initial size of the items table. This happens only in the Ecto version of this test; it does not reproduce when using pgbench.
It feels like some kind of full-table lock or table scan is occurring when using Ecto that is not happening when using pgbench.
I’m looking for ways to tune the isolation level and not finding it yet.
0
scouten
Also agree that the amount of data transmitted between Ecto and PG should not be materially different between create and update tests, so that does’t feel like a valid cause.
0
kip
ex_cldr Core Team
This can help set isolation level:
Repo.query!("set transaction isolation level repeatable read;")
This article might help.
2
scouten
Since my posts a couple of hours ago, I’ve done quick experiments with the following:
- Revert to Ecto 2.2 and Postgrex 0.13.
- Removed use of
Enum.randomfrom the update test. - Use an explicit transaction around the update operation.
- Run
ANALYZEafter seeding the database with the item records to be updated.
None of these had any appreciable impact on update performance.
As mentioned a few minutes ago, the size of the items table does have a dramatic impact on perf.
0
scouten
I tried this. (See Sign in to GitHub · GitHub)
At 5000 rows in the initial table, it crashed repeatedly with the following message:
12:53:02.386 [error] Task #PID<0.241.0> started from #PID<0.92.0> terminating
** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{connection_id: 19800, message: nil, postgres: %{code: :serialization_failure, file: "nodeModifyTable.c", line: "1257", message: "could not serialize access due to concurrent update", pg_code: "40001", routine: "ExecUpdate", severity: "ERROR", unknown: "ERROR"}, query: nil}}
bench/update_item_sql.exs:37: anonymous fn/2 in :elixir_compiler_1.__FILE__/1
(ecto_sql) lib/ecto/adapters/sql.ex:898: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
(db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4
bench/update_item_sql.exs:34: anonymous fn/1 in :elixir_compiler_1.__FILE__/1
(pghr) lib/parallel_bench.ex:29: ParallelBench.iterate/3
(elixir) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
(elixir) lib/task/supervised.ex:35: Task.Supervised.reply/5
(stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
At 50,000 rows, it succeeded, but the performance was not markedly different from the version with that line commented out (both 19xx requests per second).
0
scouten
Update: I’ve posted new results to the repo based on some research by my team today.
There’s a very important difference in how Ecto is processing update requests compared to create requests that I can not replicate with pgbench.
In the create benchmark, we see both pgbench and Ecto have almost constant performance regardless of how many records are created. (I use length of time the test is run as a proxy for table size.)
In the update benchmark, we see pgbench have almost constant performance regardless of the number of records that were posted into the table.
But Ecto – running the exact same SQL query (via Ecto.Adapters.SQL.query) – has performance fall off almost proportionally to the number of records in the table. I didn’t post those numbers, but the performance characteristics are fundamentally similar if I use Ecto changesets or Repo.update_all/3.
So … What could Ecto (or Postgrex or DBConnection?) be doing differently that’s specific to update queries? It almost feels like there’s some kind of directive being issued to cause Postgres to ignore the primary key index.
2
scouten
Filed as an issue at Ecto: Update performance differs greatly from pgbench results · Issue #148 · elixir-ecto/ecto_sql · GitHub.
0
scouten
As @josevalim patiently noted in the GitHub issue, Enum.random turns out to be the culprit here. I thought I had controlled for that in a previous experiment, but apparently I had not. Uff. Been a rough week. My apologies.
8