Singleton or Connection pool for high perfs?

Question

Context

I have a RESTful API for a versus fighting game, using JAX-RS, tomcat8 and Neo4j embedded.

Today I figured that a lot of queries will be done in a limited time, I'm using embedded for faster queries but I still want to go as fast as possible.

Problem

In fact, the problem is a bit different but not that much.

Actually, I'm using a Singleton with a getDabatase() method returning the current GraphDatabaseServiceinstance to begin a transaction, once it's done, the transaction is closed... and that's all.

I don't know if the best solution for optimal perfs is a Singleton pattern or a pool one (like creating XX instances of database connection, and reuse them when the database operation is finished).

I can't test it myself actually, because I don't have enough connections to even know which one is the fastest (and the best overall).

Also, I wonder if I create a pool of GraphDatabaseService instances, will they all be able to access the same datas without getting blocked by the lock?

Answer 1

Crate only one on GraphDatabaseService instance and use it everywhere. There are no need to create instance pool for them. GraphDatabaseService is completely thread-safe, so you can not worry about concurrency (note: transaction are thread-bound, so you can't run multiple transactions in same thread).

All operations in Neo4j should be executed in Transaction. On commit transaction is written in transaction log, and then persisted into database. General rules are:

• Always close transaction as early as possible (use try-with-resource)
• Close all resources as early as possible (ResourceIterator returned by findNodes() and execute())

Here you can find information about locking strategy.

---

To be sure that you have best performance, you should:

• Check database settings (memory mapping)
• Check OS settings (file system)
• Check JVM settings (GC, heap size)
• Data model

Here you can find some articles about Neo4j configuration & optimizations. All of them have useful information.

Answer 2

Use a pool - definitely.

Creating a database connection is generally very expensive. Using a pool will ensure that connections are kept for a reasonable mount of time and re-used whenever possible.