[erlang-questions] Mnesia Query Performance

[Dan Gudmundsson]

A separate transaction for each read will have much more overhead.

Much of the overhead in the transaction is grabbing locks, if you grab
a table lock first
then each read (or write if write-locked) can skip taking a lock.

The quadratic behaviour comes from the implementation, mnesia stores
the grabbed locks
in a ets bag table. Probably using the table as key, which causes the
longer and longer insertion time
for each lock that is taken on the same table*.
This shouldn't be a "real" problem because if you access so many
records from the same table
you should grab a table lock instead of taking a lock per record.

/Dan
* I guess I don't have the code on my windows machine at home.

On Thu, Mar 10, 2011 at 8:34 PM, Evans, Matthew <mevans@REDACTED> wrote:
> That's interesting.
>
> I wonder why that would be? By that what is the difference between getting a lock first in a transaction, then performing the mnesia:read within a list comprehension in that transaction verses separate transactions for each read?
>
> I would've thought the two would be the same....
>
> Matt
>
> -----Original Message-----
> From: Rob Harrop [mailto:rob@REDACTED]
> Sent: Thursday, March 10, 2011 1:08 PM
> To: Dan Gudmundsson
> Cc: Evans, Matthew; erlang-questions@REDACTED
> Subject: Re: [erlang-questions] Mnesia Query Performance
>
> That makes a big difference - performance is linear when the transaction
> takes a read lock on the table first.
>
> Thanks for that,
>
> Rob
>
> On 10/03/11 17:56, Dan Gudmundsson wrote:
>> What happens if you grab a table lock the first thing in the transaction?
>>
>> /Dan
>>
>> On Thu, Mar 10, 2011 at 5:55 PM, Rob Harrop<rob@REDACTED>  wrote:
>>> Hi Matt,
>>>
>>> Thanks for taking the time to look at this.
>>>
>>> I should've said that we'd reached a similar conclusion that deleting our records one-by-one was significantly faster than doing them all in one transaction.
>>>
>>> Is this the recommended solution? It's a real shame to lose the transaction protection here.
>>>
>>> Regards,
>>>
>>> Rob
>>>
>>>
>>>
>>> On 10 Mar 2011, at 15:25, "Evans, Matthew"<mevans@REDACTED>  wrote:
>>>
>>>> Hi,
>>>>
>>>> I changed your read function to look like this instead:
>>>>
>>>> read([]) ->
>>>>     ok;
>>>> read([Key|Keys]) ->
>>>>     {atomic, _} =
>>>>         tx(fun() ->
>>>>                    mnesia:read(test_record, Key)
>>>>            end),
>>>>     read(Keys).
>>>>
>>>> Before the change my test mirrored yours:
>>>>
>>>> [{ordered_set,500,17},
>>>> {ordered_set,1000,43},
>>>> {ordered_set,2000,139},
>>>> {ordered_set,4000,492},
>>>> {ordered_set,8000,1882},
>>>> {ordered_set,16000,7156},
>>>> {set,500,14},
>>>> {set,1000,49},
>>>> {set,2000,140},
>>>> {set,4000,493},
>>>> {set,8000,1846},
>>>> {set,16000,7123}]
>>>>
>>>>
>>>> After the change it looks like:
>>>>
>>>> [{ordered_set,500,28},
>>>> {ordered_set,1000,41},
>>>> {ordered_set,2000,82},
>>>> {ordered_set,4000,166},
>>>> {ordered_set,8000,335},
>>>> {ordered_set,16000,689},
>>>> {set,500,20},
>>>> {set,1000,41},
>>>> {set,2000,84},
>>>> {set,4000,167},
>>>> {set,8000,337},
>>>> {set,16000,701}]
>>>>
>>>> I think that handling the whole "large" list within a single transaction is the cause of the slowdown. Not sure why.
>>>>
>>>> Matt
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: erlang-questions@REDACTED [mailto:erlang-questions@REDACTED] On Behalf Of Rob Harrop
>>>> Sent: Thursday, March 10, 2011 10:03 AM
>>>> To: erlang-questions@REDACTED
>>>> Subject: [erlang-questions] Mnesia Query Performance
>>>>
>>>> Hi,
>>>>
>>>> I've been doing a bit of tuning on our use of mnesia in RabbitMQ and I'm
>>>> a bit puzzled by the fact that read performance in transactions appears
>>>> to be quadratic (or worse).
>>>>
>>>> I ran a test in which I do only reads (no writes, no matching) on both
>>>> set and ordered_set tables. From the results, it is clear that
>>>> transaction execution time does not increase linearly:
>>>>
>>>> [{ordered_set,500,11},
>>>>   {ordered_set,1000,34},
>>>>   {ordered_set,2000,120},
>>>>   {ordered_set,4000,418},
>>>>   {ordered_set,8000,1800},
>>>>   {ordered_set,16000,9981},
>>>>   {set,500,11},
>>>>   {set,1000,35},
>>>>   {set,2000,115},
>>>>   {set,4000,406},
>>>>   {set,8000,1808},
>>>>   {set,16000,9804}]
>>>>
>>>> Here the second slot in each tuple is the number of reads and the third
>>>> slot is tx execution time in milliseconds.
>>>>
>>>> I have attached the test code.
>>>>
>>>> Am I doing something horribly wrong, or is this expected behaviour? If
>>>> this is expected behaviour are there are known/accepted workarounds?
>>>>
>>>> Kind Regards,
>>>>
>>>> Rob Harrop
>>>> RabbitMQ
>>>>
>>>> ________________________________________________________________
>>>> erlang-questions (at) erlang.org mailing list.
>>>> See http://www.erlang.org/faq.html
>>>> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>>>>
>>>
>>> ________________________________________________________________
>>> erlang-questions (at) erlang.org mailing list.
>>> See http://www.erlang.org/faq.html
>>> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>>>
>>>
>
> ________________________________________________________________
> erlang-questions (at) erlang.org mailing list.
> See http://www.erlang.org/faq.html
> To unsubscribe; mailto:erlang-questions-unsubscribe@REDACTED
>
>