Opa: How to Efficiently Read/Write a Large Number of Records

Question

The Problem

I need to read and write a large number of records (about 1000). The example below takes as long as 20 minutes to write 1000 records, and as long as 12 seconds to read them (when doing my "read" tests, I comment out the line do create_notes()).

The Source

This is a complete example (that builds and runs). It only prints output to the console (not to the browser).

type User.t =
  { id : int
  ; notes : list(int) // a list of note ids
  }

type Note.t =
  { id : int
  ; uid : int // id of the user this note belongs to
  ; content : string
  }

db /user : intmap(User.t)
db /note : intmap(Note.t)

get_notes(uid:int) : list(Note.t) =
  noteids = /user[uid]/notes
  List.fold(
    (h,acc -> 
      match ?/note[h] with
      | {none} -> acc
      | {some = note} -> [note|acc]
    ), noteids, [])

create_user() =
  match ?/user[0] with
  | {none} -> /user[0] <- {id=0 notes=[]}
  | _ -> void

create_note() =
  key = Db.fresh_key(@/note)
  do /note[key] <- {id = key uid = 0 content = "note"}
  noteids = /user[0]/notes
  /user[0]/notes <- [key|noteids]

create_notes() =
  repeat(1000, create_note)

page() =
  do create_user()
  do create_notes()
  do Debug.alert("{get_notes(0)}")
  <>Notes</>

server = one_page_server("Notes", page)

One More Thing

I also tried getting notes via a transaction (shown below). It looks like a Db.transaction might be the right tool, but I haven't found a way to successfully employ it. I've found this get_notes_via_transaction method to be exactly as slow as get_notes.

get_notes_via_transaction(uid:int) : list(Note.t) =
  result = Db.transaction( ->
    noteids = /user[uid]/notes
    List.fold(
      (h,acc -> 
        match ?/note[h] with
        | {none} -> acc
        | {some = note} -> [note|acc]
      ), noteids, [])
  )
  match result with
  | {none} -> []
  |~{some} -> some

Thanks for your help.

Edit: More Details

A little extra info that might useful:

After more testing I've noticed that writing the first 100 records takes only 5 seconds. Each record takes longer to write than the previous one. At the 500th record, it takes 5 seconds to write each record.

If I interrupt the program (when it starts feeling slow) and start it again (without clearing the database), it writes records at the same (slow) pace it was writing when I interrupted it.

Does that get us closer to a solution?

Answer 1

Nic, this is probably not the answer you were hoping for, but here it is:

1. I'd suggest for this kind of performance experiments to change the framework; for instance not to use the client at all. I'd replace the code from create_node function with this:

   counter = Reference.create(0)
   create_note() =
     key = Db.fresh_key(@/note)
     do /note[key] <- {id = key uid = 0 content = "note"}
     noteids = /user[0]/notes
     do Reference.update(counter, _ + 1)
     do /user[0]/notes <- [key|noteids]
     cntr = Reference.get(counter)
     do if mod(cntr, 100) == 0 then
          Log.info("notes", "{cntr} notes created")
        else
          void
     void
   
   import stdlib.profiler
   
   create_notes() =
     repeat(1000, -> P.execute(create_note, "create_note"))
   
   P = Server_profiler
   
   _ =
     do P.init()
     do create_user()
     do create_notes()
     do P.execute(-> get_notes(0), "get_notes(0)")
     P.summarize()
   

2. With intermediate timing being printer per every 100 inserts you'll quickly see that the inserts times are quadratically to the number of inserted items, not linear. This is because of the list update /user[0]/notes <- [key|noteids] which apparently causes the whole list to be written again. AFAIK we had optimizations to avoid that, but either I'm wrong or for some reasons they do not work here -- I'll try to look into that and will let you know once I know more.

3. Previously mentioned optimization aside, a better approach to model this data in Opa would be using sets as in the following program:

   type Note.t =
   { id : int
   ; uid : int // id of the user this note belongs to
   ; content : string
   }
   
   db /user_notes[{user_id; note_id}] : { user_id : int; note_id : int }
   db /note : intmap(Note.t)
   
   get_notes(uid:int) : list(Note.t) =
     add_note(acc : list(Note.t), user_note) =
       note = /note[user_note.note_id]
       [note | acc]
     noteids = /user_notes[{user_id=uid}] : dbset({user_id:int; note_id:int})
     DbSet.fold(noteids, [], add_note)
   
   counter = Reference.create(0)
   
   create_note() =
     key = Db.fresh_key(@/note)
     do /note[key] <- {id = key uid = 0 content = "note"}
     do DbVirtual.write(@/user_notes[{user_id=0}], {note_id = key})
     do Reference.update(counter, _ + 1)
     cntr = Reference.get(counter)
     do if mod(cntr, 100) == 0 then
          Log.info("notes", "{cntr} notes created")
        else
          void
     void
   
   import stdlib.profiler
   
   create_notes() =
     repeat(1000, -> Server_profiler.execute(create_note, "create_note"))
   
   _ =
     do Server_profiler.init()
     do create_notes()
     do Server_profiler.execute(-> get_notes(0), "get_notes(0)")
     Server_profiler.summarize()
   

   where you'll set that filling in the database takes ~2 seconds. Unfortunately this feature is heavily experimental and hence undocumented and, as you'll see, indeed it explodes on this example.

4. I'm afraid we don't really plan to improve on (3) and (4) as we realized that providing an in-house DB solution that is up to industrial standards is not very realistic. Therefore at the moment we're concentrating all our efforts on tight integration of Opa with existing No-SQL databases. We hope to have some good news about that in the coming weeks.

I'll try to learn more about this issue from our team and will make correction if I learn that I missed/got something wrong.