Slow performance of activate framework when inserting/selecting large amounts of data

[fry...@googlemail.com]

Hello,

I've been working with Activate in some scenarios and was very impressed. Unfortunately, I've noticed some performance issues when inserting and selecting large amounts of data (both with Postgresql and in-memory).

The full source code is here: https://github.com/fry1026/Activate_benchmark

Here is a snippet of the code, it's a simple data model

Author - (1-to-Many) - Book - (Many-to-Many, via AwardPresentation) - Award

and inserts/selects lots of data via some loops.

1 to 5 foreach { _ =>
    transactional {
      time("insert statements", {
        1 to 100 foreach { n =>
          val jrrt = new Author("JRR", s"Tolkien$n")
          new Author("Jane", "Austen")
          new Author("Philip", "Pullman", None, Some( """ <xml>Test</xml> """))
        }

The results are (shortened output)

Elapsed time for insert statements: 2368ms
11:25:32.921 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - Before pool cleanup Pool stats HikariPool-0 (total=20, inUse=1, avail=19, waiting=0)
11:25:32.923 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - After pool cleanup Pool stats HikariPool-0 (total=20, inUse=1, avail=19, waiting=0)
11:26:02.921 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - Before pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
11:26:02.921 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - After pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
Elapsed time for select statements: 86943ms
11:26:32.921 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - Before pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
11:26:32.921 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - After pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
Elapsed time for insert statements: 3885ms
11:27:02.920 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - Before pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
11:27:02.920 [Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool - After pool cleanup Pool stats HikariPool-0 (total=20, inUse=0, avail=20, waiting=0)
Elapsed time for select statements: 37942ms
Elapsed time for insert statements: 3259ms

I've tried the same with Squeryl, and it's much faster (source code here https://github.com/fry1026/Squeryl_benchmark), results are (shortened output).

Elapsed time for insert statements: 2023ms
Elapsed time for select statements: 1280ms
Elapsed time for insert statements: 549ms
Elapsed time for select statements: 817ms
Elapsed time for insert statements: 403ms
Elapsed time for select statements: 860ms

Is there a better way to write the Activate code or optimise it, e.g. reduce the 3.2 seconds for insert with Activate towards the 0.8 second for insert with Squeryl?

Also the pool cleanup seems to be blocking things (86s for one select).

Thanks for your help in advance.

F

[Flavio W. Brasil]

Hi,

Thanks for sharing the code, I love benchmarks! :)

I’ve noticed two things:

1. The Activate's migration runs only one time[0], so probably you are running the benchmarks with a lot of data from previous executions. For tests, you can use “timestamp = System.currentTimeMillis” to force the migration.

2. The Activate’s benchmark uses only one transaction (the nested transactional block doesn’t trigger a new transaction[1]), while the Squeryl’s uses two.

I also can’t reproduce your results, please see this gist:

https://gist.github.com/fwbrasil/39789d25958e808c6ad6

The Activate’s results look to be better than Squeryl’s. There are some factors that can lead to the difference between our executions like garbage collector and jvm warmup, but the dirty database is probably the main factor.

In case you are interested on a more complete benchmark: http://databen.ch

Regards,

Flavio

[0] https://github.com/fry1026/Activate_benchmark/blob/master/src/main/scala/Activate.scala#L62

[1] https://github.com/fry1026/Activate_benchmark/blob/master/src/main/scala/Activate.scala#L93

--
You received this message because you are subscribed to the Google Groups "Activate Persistence Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to activate-persist...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[fry...@googlemail.com]

Hello Flavio,

thanks for the quick reply. Sorry I didn't see the nested transaction, I've fixed this, switched both to Postgresql and adjusted the migration so it's always a clean database.

There is also now an output on the number of Author objects in order to make it comparable.

I've run the benchmark again and Activate is in fact faster now, well done. I've pushed the changes to the 2 repositories.

The only issue which still comes up is if there are too many objects in the database:

[Hikari Housekeeping Timer] DEBUG com.zaxxer.hikari.pool.HikariPool

Things get super slow then (much slower than Squeryl). In my case it appeared when I increased the insert loop from 100 to 1000.

Are there some tweaks to fix that issue as well? I couldn't see a solution in the forum, there was some discussion around thread pools but not sure how to adjust the required settings.

With best regards,

F