sheepduke
Hi folks,
I just started to explore the Mnesia database. I read many docs, posts and resources provided by Elixir forum, but still could not found a solution:
My Mnesia is 100x times slower to query (scan) compared with Ecto/SQLite3.
Initialization
The following code shows how Mnesia was initialized:
defmodule Data.Test do
use Memento.Table,
attributes: [
:uuid,
:name
],
index: [:name]
end
Memento.stop()
Memento.Schema.create([node()])
Memento.start()
Memento.Table.create!(Data.Test, disc_copies: nodes)
Memento.transaction!(fn ->
Enum.each(1..100_000, fn i ->
Memento.Query.write(%Data.Test{
uuid: Ecto.UUID.generate(),
name: "Test #{i}"
})
end)
end)
Then I created an exactly same table in SQLite3, without index.
Query
The following code shows the query process:
:timer.tc(fn ->
Memento.transaction!(fn ->
Memento.Query.all(Data.Test)
|> Stream.filter(&String.contains?(&1.name, "12345"))
|> Enum.to_list()
end)
end)
Test.Repo.query("select * from test where name like '%12345%'")
The result was about 1600 ms and 16 ms respectively.
I replaced the Stream in Mnesia query with :mnesia.foldl (without Memento) and the result was almost identical (1400 ms maybe, still ~100x times).
Question
My understanding is that SQL like '%xxx%' leads to a full table scan, which is same as Mnesia.
Since Mnesia stores the data in memory, why is the table scan so slow? Am I miss anything?
Any clue is sincerely appreciated!
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Enabling companies to succeed by building software people love.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #blog-post
- #elixir-ls
- #elixirconf-us
- #ai
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!
Bringing learning, work and compliance together since 1987.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 9- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
bit4bit
hi, try mnesia directly maybe get a better answer
Exadra37
The queries are not comparable at all.
In SQLITe everything its done by the database engine, but in the Mnesia query you are splitting the work between Mnesia and Elixir.
You can read the Memento Docs to see how you can properly do your SQLITe query only with Memento or you can read the Mnesia docs:
sheepduke
As mentioned, I did.
It saved the execution time from 1600ms to 1400ms, still not comparable to SQLite.
sheepduke
You are right. I understood this this part.
The problem is, Memento does not support string match of
%key%. I also checked the Mnesia documents and the nearest thing I could found wasfoldl, which was still not fast…Is there a better way to accomplish this in Mnesia, apart from
Memento.Query.all()orfoldl?japplegame
Mnesiais more of a key-value storage than a full-featured database.cmkarlsson
mnesiaandetsare key/value database. It is best used when you have a specific value to retrieve.That said, if you can structure your object in a way that
:mnesia.selectcan be used then it is much faster. Mnesia select relies on match specifications (Match Specifications in Erlang — OTP 29.0.2 (erts 17.0.2)) which are a bit gnarly to get your head around but very useful, not only in mnesia but also in ets and if you are doing tracing.Match specifications do not support matching on parts of binary strings so your specific example would not work as is, but they are a faster way to get information out of mnesia and ets compared to foldl or iterating the table.
I see that Memento also support
select(a simplified version of erlang’s matchspecs) andselect_rawwith the full power of select. Perhaps it is worth investigating.sheepduke
Thank you all for your replies!
I think I made a mistake. When I did the test, the schema of Mnesia contains more attributes than SQLite. It should be 10x times slower instead of 100x when iterate Mnesia, while accessing via key is instant.
Having said that, is it better to use SQL when storing data that will probably be queried not only by keys?
What would you use Mnesia for? Storing runtime configurations? Caches?
Rustixir
Hi , I working with mnesia for distributed read-heavy data application for three last year.
Mnesia is a distributed, concurrency DBMS but Sqlite is a embedded database.
for real-project if you need high concurrency access to Database with Sqlite become bottleneck .
Mnesia intenal use ETS for disc_copies and ram_copies
and that have many parameter for tune maximize performance
if you want for a single node/machine and maximize performance use ETS
if you want use mnesia use itself module → :mnesia
for increase performance for read/query use :mnesia.activity with <:async_dirty > parameter
if you want use mnesia with maximize performance for read/query use
:mnesia.activity with < :ets > parameter
ourway
The query is not optimised to use the full power of Mnesia. That being said, you will benefit from Mnesia if you scale up to dozens or even hundreds of nodes. You are comparing apples and oranges.