Mnesia and additional indexes: a cautionary tale

[Rudolph van Graan]

Hi Scott,

> Later in the afternoon, I shutdown Mnesia, then restart it.  My
> application starts timing out at mnesia:wait_for_tables/2.  So I start
> mnesia manually, then go get coffee and make a phone call.  When I
> return 15 minutes later, Mnesia *still* hasn't finished starting up.

I have seen similar things happen in production systems. In our case,  
the database was about 700Mb in size, roughly split between three  
tables of similar size. When the system gets to this point, we always  
have to clean the tables and dump the data (not nice, but acceptable  
in this system) as restoring from backups will only delay the problem  
from reoccurring in a couple of hours.

> The "beam" process size should've been about 1,400KB with everything
> loaded.  But the process size was only 390MB, and "beam" was still
> using 100% CPU time ... doing something, I dunno what!

When this happens, it is mostly DETS related processes running  
doing... what?

If you kill the VM when in this state, I've seen the following happen:

=ERROR REPORT==== 29-Dec-2005::16:12:01 ===
Mnesia(vm1@REDACTED): ** ERROR ** (core dumped to file: "d:/Mnesi
aCore. vm1@REDACTED")
  ** FATAL ** {error,{"Cannot open dets table",
                      ssycpy,
                      [{file,"d:/db/ssycpy.DAT"},
                       {keypos,2},
                       {repair,true},
                       {type,set}],
                      {bad_freelists,"d:/db/ssycpy.DAT"}}}

=ERROR REPORT==== 29-Dec-2005::16:12:11 ===
Mnesia(vm1@REDACTED): ** ERROR ** mnesia_event got unexpected event:  
{'EXIT',
                                                                         
    <0.39.
0>,
                                                                         
    killed
}

=INFO REPORT==== 29-Dec-2005::16:12:11 ===
     application: mnesia
     exited: {killed,{mnesia_sup,start,[normal,[]]}}
     type: permanent
{"Kernel pid terminated",application_controller,shutdown}

Crash dump was written to: erl_crash.dump
Kernel pid terminated (application_controller) (shutdown)

I tried figuring out what this means in the mnesia code, but gave up.
>
> The table 'Tab3' contains 200K of debugging/development records.  When
> the code to create those records was first written, the attribute at
> position #5 was a constant binary term.

Do you mean constant as in it will never change after writing the  
first time or constant as in all of them the same?

> Then "feature creep" happened, and an extra Mnesia index was created
> for position #5.  At the 200K records were added slowly, no one
> noticed the performance impact using the exact same term for position
> #5 ... until I did, last last night.

I guess you mean all the entries have the same term in position 5.

So in essence you suggest this happens when a lot of records contain  
the same value in an indexed field?

In our system, I have slowly started to lose trust in mnesia and  
currently our design policy excludes using mnesia for tables that  
"grow", i.e. new records being added.

The issue is that I cannot easily describe the symptoms - is it  
because the DETS table got corrupted due to a dirty shutdown of the  
machine? Is it the fact that we use large tables for which recovery  
is difficult? This is the first time that I saw anyone with similar  
symptoms.

Last question - is this running Windows as host OS? I have not seen  
this happen on Mac OS or Linux, but that might be incidental.

Rudolph