What is the idiomatic way of limiting the number of records under a certain key in mnesia?

Question

I use mnesia to store data for users, and the record is a bag structured like

{ username, field1, filed2, timestamp }

In order not to let the database explode, I want to set a limit for the number of records belonging to a certain user, say, if the number of records for a user reaches 500, then the record with the oldest timestamp is deleted before a new record is inserted.

Is there an efficient way to do this?

Thanks in advance.

score 1 · Answer 1 · answered Dec 10 '11 at 21:06

Another way might be to have your record be

{username, value_list, timestamp}

where value_list contains a list of values. Your table can now be a set instead of a bag and you can do this type of thing

{NewList,_ignore}=lists:Split(500, [{NewFld1,NewFld2}|Value_list]),
%mnesia:write(Rec#{value_list=NewList}) type of code goes next

whenever you insert. NewList will contain at most 500 elements and since the oldest elements are on the end, if you have 501 elements the last will be in the _ignore list which you will... Ignore.

Your trading a constant time lookup on your key for some list manipulation, but might be a good trade-off depending on your application. You also may be able to get rid of the timestamp field and associated code to maintain that field.

This approach is very simple (I actually have something similar in production), but it can be extremely inefficient if the limit on the number of records is high, because Mnesia will have to read or write all data of the user, for every operation performed on that user. — igorrs, Jan 08 '13 at 02:35

score 0 · Answer 2 · answered Nov 10 '11 at 06:47

I have provided two possibilities. One that fits within your design and one that introduces a small change in your record definition. See which one best fits your needs. The first one below is working within your design.

-record(user,{username,field1,field2,timestamp}).

%% execute the function below in a mnesia transaction

insert(#user{username = U,timestamp = _T} = User)->
    case mnesia:read({user,U}) of
        [] -> mnesia:write(User);
        AllHere -> 
            case length(AllHere) == 500 of
                false -> %% not yet 500
                      mnesia:write(User);
                true -> 
                    %% value has reached 500
                    %% get all timestamps and get the 
                    %% oldest record and delete it
                    %% 
                    OldRecord = get_oldest_stamp(AllHere),
                    ok = mnesia:delete_object(Record),
                    mnesia:write(User)
            end
    end.

get_oldest_stamp(UserRecords)-> 
    %% here you do your sorting
    %% and return the record with
    %% oldest timestamp
    ....
    OldRecord.

The length/1 function within the transaction is not optimal. Lets think of a better way. Also, i do not know how your timestamps look like so am sure you have a way of sorting them and finding out the latest timestamp, pick out the record which owns this timestamp and then return it. I have given this solution just to fit your design. I also think the some where we need some indexing. The next implementation seems better to me. Some how if we can change the record definition and we introduce a field oldestwhich takes a bool() and we index it like this

-record(user,{
            username,
            field1,
            field2,
            timestamp,
            oldest      %% bool(), indexed
      }).

insert_user(Username,Field1,Field2)->
    User = #user{
                username = Username,
                field1 = Field1,
                field2 = Field2,
                timestamp = {date(),time()}

                }.
    insert(User).

%% execute this within a mnesia transaction

insert(#user{username = U} = User)->
    case mnesia:read({user,U}) of
        [] -> mnesia:write(User#user{oldest = true});
        AllHere -> 
            case length(AllHere) == 500 of
                false ->
                    %% unset all existing records' oldest field
                    %% to false
                    F = fun(UserX)-> 
                            ok = mnesia:delete_object(UserX),
                            ok = mnesia:write(UserX#user{oldest = false})
                        end,
                    [F(XX) || XX <- AllHere],
                    ok = mnesia:write(User#user{oldest = true});
                true -> 
                    [OldestRec] = mnesia:index_read(user,true,oldest),
                    ok = mnesia:delete_object(OldestRec),
                    ok = mnesia:write(User#user{oldest = true})
            end
    end.

The above implementation seems better to me. success !!

What is the idiomatic way of limiting the number of records under a certain key in mnesia?

2 Answers2