4

I already have implemented JOOQ with Union Platform as a java based game server and using Union Platform's Orbiter Micro (Union JS Client) for running it on a browser.

However, event with small 30-40 active players in the game, the Union Platform is lagging in terms of speed.

For each game instance (i.e each room) , i have configured one factory connection which executes all the queries. What it does is ,

For each player's turn,

  • player can put in an integer and after a series (4 queries) of condition checks, it inserts/updates the db via JOOQ
  • and then to get back the integer's status, it again executes 3 queries which effectively translates into 3 linked-hashmaps for updating the status of the game.

Now, the response is taking so much time, that players are seeing performance issues. While Union Platform actually claims to handle more than 1000 active players at an instance easily. So this effectively means that there is some mistakes in my JOOQ queries (I did used a lot of LEFT OUTER JOINs).

So any suggestion to improve the throughput and to profile each query is very much welcome.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Shekhar
  • 787
  • 2
  • 7
  • 18

1 Answers1

7

That sounds like normal SQL tuning question (or maybe several distinct ones) to me. Given that jOOQ lets you execute actual SQL, and there is a lot that can go wrong when using OUTER JOIN without correct indexing and constraints, it is likely that the problem is with the SQL itself. When you turn on debug / trace logging, jOOQ will print out several pieces of information to the log output, including

DEBUG level

TRACE level

  • Same as DEBUG level
  • Statement preparation time
  • Bind values
  • Statement bind time

This will work if you put log4j or slf4j on your classpath along with jOOQ. More details are documented in this blog post

That's a start. You could also use a tool like Yourkit Profiler or JProfiler. And obviously, you should consider your Postgres execution plans. There are some indications about that here in the Postgres documentation.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks Lukas for the direction. I will check it out – Shekhar Dec 19 '11 at 13:18
  • Hey Lukas, I am still not able to find where to set JOOQ's logging information. Is it in jooq.properties?? – Shekhar Dec 20 '11 at 08:01
  • @Shekhar: I have updated the answer. As documented in the blog and in the jOOQ manual, you could use **log4j** or **slf4j** as your application's logging library – Lukas Eder Dec 20 '11 at 08:21
  • Hi @LukasEder, DEBUG level does not seem to print the execution time any longer. Can it be done by using a custom ExecuteListener with an IdentityMap of ResultSet/Long, override fetchStart to store the start nanotime in the map, and override fetchEnd to calculate and log the duration? – assylias Nov 22 '22 at 10:00
  • 1
    @assylias Thanks for your message. Indeed, 3.10 changed the default, because the default was quite inaccurate and produced the wrong picture. E.g. it also measures logging overhead and all sorts of other things. Yes, you can use an `ExecuteListener`, or use the built-in `StopWatchListener` (which still has this inaccuracy) – Lukas Eder Nov 22 '22 at 10:02
  • Understood many thanks. I'm afraid in my use case, the logging overhead would not make a difference considering the slowness of what is being measured, haha... – assylias Nov 22 '22 at 10:06
  • 1
    @assylias: Sure, that's why the `StopWatchListener` is still around. It's still useful. It just shouldn't be enabled by default. People have started complaining about jOOQ being slow, when in fact, it was mostly just logging overhead :) – Lukas Eder Nov 22 '22 at 10:07