Mnesia 100x slower to query compared with SQLite?

Questions / Help
,
1

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!

2

hi, try mnesia directly maybe get a better answer

1 Like
3

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:

2 Likes
4

As mentioned, I did. :slight_smile:

It saved the execution time from 1600ms to 1400ms, still not comparable to SQLite.

5

You are right. I understood this this part. :slight_smile:

The problem is, Memento does not support string match of %key%. I also checked the Mnesia documents and the nearest thing I could found was foldl, which was still not fast…

Is there a better way to accomplish this in Mnesia, apart from Memento.Query.all() or foldl?

6

Mnesia is more of a key-value storage than a full-featured database.

1 Like
7

mnesia and ets are 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.select can be used then it is much faster. Mnesia select relies on match specifications (Erlang -- Match Specifications in Erlang) 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) and select_raw with the full power of select. Perhaps it is worth investigating.

4 Likes
8

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?

9

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 .

  1. Mnesia intenal use ETS for disc_copies and ram_copies
    and that have many parameter for tune maximize performance

  2. if you want for a single node/machine and maximize performance use ETS

  3. if you want use mnesia use itself module → :mnesia

  4. for increase performance for read/query use :mnesia.activity with <:async_dirty > parameter

  5. if you want use mnesia with maximize performance for read/query use
    :mnesia.activity with < :ets > parameter

5 Likes
10

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.