mnesia数据库性能测试

  本测试是mnesia表存在于磁盘，每条记录10个字段，总长度为139B，在一个mnesia节点上进行。

                             插入单条(ms)         查询单条(ms)       删除单条(ms)      按ID更新一条记录的两个字段(ms)

1000000               0.0432                      0.124                        0.113                            0.523

100000                 0.0562                      0.114                       0.124                              0.517

10000                   0.0426                      0.103                       0.12                                0.238

 1000                    0.0374                      0.015                        0.13                                0.424

测试结论：查询和更新操作相对稳定，而插入单条记录的变化浮动较大，需要进一步研究，mnesia插入数据的原理。

测试代码如下：

%% @author zcc

%% @doc @todo Add description to test_mnesia.
-module(test).
-include_lib("eunit/include/eunit.hrl").
-include_lib("stdlib/include/qlc.hrl").
-compile(export_all).
%% ====================================================================
%% API functions
%% ====================================================================
-export([random_seed/3]).
-export([start/0,batch_insert/2,search_test/2]).
%单条记录139B
-record(user,{
      id, %4B
      phone,%11B
      type,%10B
      key,%72B
      para,%32B
      flag1,%2B
      flag2,%2B
      flag3,%2B
      flag4,%2B
      flag5 %2B
      }).
%% ====================================================================
%% Internal functions
%% ====================================================================
% 初始化
% 创建数据库和表
init() ->
 io:format("Initionate begins..........................~n"),
 Schema = mnesia:delete_schema([node()|nodes()]),%如果已创建，执行删除操作
    case Schema of
  ok ->
   io:format("Delete schema successfully~n");
  {error,Reason1} ->
   io:format("Delete schema failure:~w~n",[Reason1])
 end,
 Result1 = mnesia:create_schema([node()|nodes()]),%,创建节点，相当于数据库
 case Result1 of
  ok ->
   io:format("Create schema successfully~n");
  {error,Reason} ->
   io:format("Create schema failure~w~n",[Reason]);
  _Err->
   io:format("Create schema failure~n")
 end,
 %lists:foreach(fun(N) ->spawn(N, mnesia, start,[])end,[node()|nodes()]),
 batch_op(fun() -> mnesia:start() end),
 sleep(1000),
 {P,O} = mnesia:create_table(user,[{disc_copies,[node()]},{type,set},{attributes,record_info(fields,user)}]),
 %batch_op(fun() -> mnesia:create_table(agent,[{type,set},{attributes,record_info(fields,agent)}]) end),
 % {disc_copies,[node()]},{ram_copies,nodes()}, 根据record创建agent表
    case {P,O} of
  {atomic,ok} ->
           io:format("Create user table successfully^!^..........................~n");
  _Other ->
      %New = setelement(3, O, list_to_atom(element(3, O))),
      io:format("Create user failure:~w~n",[O])
 end,
    io:format("Initionate end..........................~n").
start() ->
    io:format("Start begins..........................~n"),
    init(),
 log4erl:conf("D:/ErlangWorkspace1/MnesiaTest/log4erl.conf"),
 %lists:foreach(fun(N) -> spawn(N, mnesia, wait_for_tables,[[agent],10000])end,[node()|nodes()]),
 batch_op(fun() -> mnesia:wait_for_tables([user],10000) end),
 io:format("Start end..........................~n").

%停止结束
stop() ->
 %lists:foreach(fun(N) ->spawn(N, mnesia, stop,[])end,[node()|nodes()]).
    P=fun start/0,
 P(),
 batch_op(fun()-> mnesia:stop() end).

%定义批量操作
batch_op(Op) ->
 lists:foreach(fun(N) ->
         Pid = spawn(N, fun() ->
           %%在每个节点做的事情
           Op()
            end)
        end,
                   [node()|nodes()]
   ).
%% ====================================================================
%% mnesia performance test API: mnesia性能测试
%% ====================================================================

%% 产生从start开始到end结束，（end-start+1）条记录
data_set(List,Start,End) ->
 case Start =:= End+1 of
  true ->
   List;
  false ->
   Result = List ++ [{user,
          <<Start:32>>,
          "13051234992",
          <<(Start+2147483648):80>>,
          <<(Start+2147483648):576>>,
          <<(Start+2147483648):256>>,
          <<Start:16>>,
          <<Start:16>>,
          <<Start:16>>,
          <<Start:16>>,
          <<Start:16>>}],
   case length(Result) =:= 1000000 of
         true -> io:format("length=~p~n",[length(Result)]);
      false -> ok
   end,
            data_set(Result,Start+1,End)
 end.

 %%填充数据库从Start到End，由Threads个线程来做

fill_db(Start,End,Threads) ->

    Interval = (End - Start +1) div Threads,

   Rem = (End - Start +1) rem Threads,

   allocate(Start,Interval,1,Threads),

   case Rem =:= 0 of

           true -> list_to_atom("executing insert,please waiting.........");

           false ->

                      spawn(test,batch_insert,[End-Rem+1,End])

   end.

allocate(Start,End,Interval,ThreadNum,Threads) ->

                 case  ThreadNum =:= Threads+1 of

                        true -> ok;

                       false -> 

                                  spawn(test,batch_insert,[Start,Start+Interval-1]),

                                  allocate(Start+Interval,Interval,ThreadNum+1,Threads)

                   end.

%%改进后的批插入

batch_insert(Start,End) ->

     F = fun() ->insert(Start,End) end,

    mnesia:transaction(F),

   io:format("inserted from ~p to ~p recoreds completedly~n",[Start,End]).

insert(Start,End) ->

      case Start =:= End+1 of

             true -> ok;

             false ->

                        mnesia:write({user,

                                            <<Start:32>>,

                                            "13050093456",

                                            <<(Start+2147483648):80>>,

                                           <<(Start+2147483648):576>>,

                                          <<(Start+2147483648):256>>,

                                         <<Start:16>>,

                                        <<Start:16>>,

                                       <<Start:16>>,

                                      <<Start:16>>,

                                      <<Start:16>>}),

%                   case Start of

 %                            1 ->io:format("inserted ~p records completed ~n",[Start]);

  %                         10000 ->io:format("inserted ~p records completed ~n",[Start]);

  %                        100000 ->io:format("inserted ~p records completed ~n",[Start]);

  %                        1000000 ->io:format("inserted ~p records completed ~n",[Start]);

 %                         _ -> ok

    %                 end,

                  insert(Start+1,End)

end.
%插入测试数据，此方法对于大数据运行性能较慢:首先产生dateset；然后再写入，当数据量大的时候，两个过程是非常耗时的，可以合并

在产生的同时，便写入，封装成一个事务操作，如上面的batch_insert 所示，如果写入mnesia的事务，频繁发生，则mnesia会报mnesia is overloaded警告
batch_insert(Start,End)->
    %mnesia:clear_table(agent),
    F = fun() ->
%%      lists:foreach(fun mnesia:write/1, data_set([],Start,End))
  lists:foldl(fun(A,Acc)-> mnesia:write(A),case Acc of
             1-> log4erl:info("inserted ~p records completed~n",[Acc]);
             10000 -> log4erl:info("inserted ~p records completed~n",[Acc]);
             20000 -> log4erl:info("inserted ~p records completed~n",[Acc]);
             30000 -> log4erl:info("inserted ~p records completed~n",[Acc]);
             100000 -> log4erl:info("inserted ~p records completed~n",[Acc]);
             200000 -> io:format("inserted ~p records completed~n",[Acc]);
             300000 -> io:format("inserted ~p records completed~n",[Acc]);
             400000 -> io:format("inserted ~p records completed~n",[Acc]);
             500000 -> io:format("inserted ~p records completed~n",[Acc]);
             600000 -> io:format("inserted ~p records completed~n",[Acc]);
             700000 -> io:format("inserted ~p records completed~n",[Acc]);
             800000 -> io:format("inserted ~p records completed~n",[Acc]);
             900000 -> io:format("inserted ~p records completed~n",[Acc]);
                   1000000 -> io:format("inserted ~p records completed~n",[Acc]);
                _ -> ok
             end,
                                                 Acc+1
              end, 0,data_set([],Start,End))
        end,
 mnesia:transaction(F).

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%% 测试本地节点mnesia查询性能
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%利用read进行查找
find_user_ByItem(Item) ->
    F = fun() -> mnesia:read({user,Item}) end,
    {atomic,Result} = mnesia:transaction(F),
    Result.
%%按ID查询一次所花费的时间
search_onetime(Id) ->
 statistics(wall_clock),
 Result = apply(?MODULE,find_user_ByItem,[Id]),
 {_,TimeCost} = statistics(wall_clock),
 %{TimeCost,Result}=timer:tc(agent_table, find_agent_ByItem,[Id]),
 %io:fwrite("Time cost is:~w [ms]~n",[TimeCost]),
    %io:format("Result=~w~n",[Result]),
 TimeCost.
%% 找出1....Num中的N个不重复的随机数
random_seed(List,Hi,N) ->
 case N =:= length(List) of
       true ->
     List;
    false->
     P  = crypto:rand_uniform(1, Hi),
     case lists:member(P, List) of
           true ->
                  random_seed(List,Hi,N);
     false ->
      random_seed(List ++ [P],Hi,N)
        end
 end.

%% 查询num次所花费的总时间
batch_search_test(Hi,Num) ->
 List = random_seed([],Hi,Num),
    lists:foldl(fun(A,Acc) -> Acc+search_onetime(A) end, 0, List).

%从TotalRecord条记录中，查找出Num条记录，Flag为1表示从远程节点中取；为0从
search_test(TotalRecord,Num) ->
 TotalTime = batch_search_test(TotalRecord,Num),
 io:fwrite("Time cost of one time is:~w [ms]~n",[TotalTime/Num]).

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%% 测试插入性能
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% 测试插入Num条记录，Times次，取平均值
%% param：Num:记录条数；Times:测试次数
%% return：{total,aver}
insert_test(Start,End,Times) ->
 {Total,Ave}=insert_test2(Start,End,Times),
 %% 单条插入时间
 io:fwrite("Time cost is:~w [ms]~n",[Ave/1000/(End-Start+1)]).

insert_test2(Start,End,Times) ->
 %%结果是微秒
 {TimeCost,_Result} = timer:tc(?MODULE,batch_insert,[Start,End]),
 case Times of
  1 -> {TimeCost,TimeCost};
  N -> {OtherTimeCost,_OtherAvgTimeCost} = insert_test2(Start,End,N-1),
       {TimeCost+ OtherTimeCost,(TimeCost+OtherTimeCost)/N}
 end.
update(ID) ->
 statistics(wall_clock),
 %Q = qlc:q([X || X <- mnesia:table(user),X#user.id =:= <<ID:32>>]),
 %F = fun() -> qlc:e(Q) end,
 %{atomic,[Result]} = mnesia:transaction(F),
 %   NewKey = <<2147483648:576>>,
  % NewPara = <<2147483648:256>>,
  %  NewRes = Result#user{key=NewKey,para=NewPara},
%%  io:format("~p",[NewRes]),
% F1= fun() -> mnesia:write(NewRes) end,
% mnesia:transaction(F1),

%封装成单条事务，采用read方法更新

F = fun() ->  

   [Result] = mnesia:read({user,<<ID:32>>}),    

    NewKey = <<2147483648:576>>,
    NewPara = <<2147483648:256>>,
    NewRes = Result#user{key=NewKey,para=NewPara},
    mnesia:write(NewRes)

   end,

    mnesia:transaction(F),
    {_,TimeCost} = statistics(wall_clock),
 TimeCost.
%% 查询num次所花费的总时间
batch_update_test(Hi,Num) ->
 List = random_seed([],Hi,Num),
    lists:foldl(fun(A,Acc) -> Acc+update(A) end, 0, List).
%%@author zcc
%%@date   2013-11-25
%%@func   在TotalRecord条记录中，更新Num条记录取平均值 
update_test(TotalRecord,Num) ->
 TotalTime = batch_update_test(TotalRecord,Num),
 io:fwrite("Time cost of one time is:~w [ms]~n",[TotalTime/Num]).

delete(ID) ->

   statistics(wall_clock),

   F= fun() -> mnesia:delete({user,<<ID:32>>}) end,

  mnesia:transaction(F),

 {_,TimeCost} = statistics(wall_clock),

  TimeCost

generate_List(List,Num) ->

      case Num =:= 0 of

                true -> List;

               false ->

                         generate_List(List++[Num],Num-1)

     end.

%删除ID从1到Num的记录

delete_test(Num) ->

           List = generate_List([],Num),

          Time = lists:foldl(fun(A,Acc) -> Acc + delete(A) end,0,List),

          io:format("Time cost is :~w [ms] ~n",[Time/Num]).
sleep(I) ->
 receive after I -> ok end.
%% ====================================================================
%% eunit test
%% ====================================================================
search_test()->
 ?assert(ok  =:=io:format("I am Chinese!")),
 ?assert(1=/=2),
 ?assert(1=:=2).

%search_test() ->
% {TimeCost,Result}=timer:tc(agent_table, find_agent_ByItem,[1]),
% io:fwrite("Time cost is:~w [ms] Result=~w",[TimeCost,Result]).

%%附：eunit使用方法：
%%  (1)include，eunit的hrl文件；
%%  (2)方法名以"_test"后缀结束；
%%  (3)执行时eunit:test(Module)或Module:test()