0

My current setup:

  • Java backend
  • Ebean
  • HikariCP
  • RDS Aurora MySQL v5.7 having writer and reader nodes

We use reader RDS node for business operations which only require read access to the database. This works just fine (no db locks, better performance, yay!). However looking into AWS Performance insights I can see that a lot of time is spent on COMMIT operation. In fact, it's the most expensive operation on read instance by far.

Not only it takes time to process but requires extra client-server roundtrip. My naïve self suggests this could be entirely avoided but I could not find any HikariCP settings on this matter. Surely there's nothing to commit for read-only database access, no?

Above said, I do know that databases are allowed to create temporary tables even for read only replicas but to me it seems that they should be equally smart enough to destroy them once the transaction is over and connection is returned to the pool.

FWIW, we never use autocommit=true for write access due to the nature of our app. I'd prefer not using it for read only access as well.

Has anybody managed to get COMMIT-less setup working, or perhaps this is a bad idea?

mindas
  • 26,463
  • 15
  • 97
  • 154
  • Did you tried to force `autocommit=false`? I also saw that ebean has some sort of readonly option, did you already tried it? https://ebean.io/docs/query/option#readOnly – rascio Nov 19 '22 at 23:31
  • @rascio yes, we are using Ebean's readonly mode. Which is still sending COMMITs (or perhaps HikariCP is doing that). – mindas Nov 21 '22 at 09:01
  • what about forcing the autocommit to false? Another question, to check if it is Hikari or EBean doing the commit, is the commit sent after each select, or just after a batch of queries grouped in some of your methods? – rascio Nov 21 '22 at 10:58

0 Answers0