[erlang-questions] Deleting millions of rows in mnesia

[Ulf Wiger]

Den 2007-03-02 07:29:01 skrev Bengt Kleberg <bengt.kleberg@REDACTED>:

> On 2007-03-02 00:44, Anders Nygren wrote:
>> Hi
>> I have a mnesia table that collects some data during one month, and then
>> it should be cleared, in order to collect data for the next month.
>> The table could be up to 10 million rows.
>>
>> What is the best way to clear the table?
>> - mnesia:clear_table/1
>> - delete and recreate the table
>
> i would be greatful i you filled a table and measured both ways and then
> email this list with the result. please include erlang version,
> operating system and hardware.

I agree. I don't suspect clear_table/1 to be necessarily
more expensive than delete_table/create_table. The only
thing I paused at when browsing through the code was
a call to dets:match_delete(Tab, '_'). I don't know if
this is faster than simply deleting the file and creating
a new one, but presumably someone has thought about that... (:

Semantically, there's a difference in that if you
call mnesia:delete_table(Tab), mnesia:create_table(Tab, Opts),
there is no possibility of rollback(*), and the table will
actually disappear for a while, which may or may not be
difficult to handle for other parts of the application.

A construct that is more similar to clear_table/1
would be

mnesia:activity(
    transaction,
    fun() ->
         mnesia_schema:do_delete_table(Tab),
         Cs = mnesia_schema:list2cs([{name,Tab}|Opts]),
         mnesia_schema:do_create_table(Cs)
    end).

(*) The issue of rollback is less important, since
clear_table, as it is currently implemented, cannot
be combined with any other operation within the
same transaction. There is a do_clear_table/1, but
it can only be combined with operations that do not
address the same table.  The main difference that
remains, then, is the fact that the table doesn't
mysteriously disappear for a while.

BR,
Ulf W


-- 
Ulf Wiger