How can we profile JOOQ statements for speed

Question

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.

Answer 1

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

• SQL statements
• First 5 records of result
• Statement execution time (that behaviour has been removed in jOOQ 3.10, see https://github.com/jOOQ/jOOQ/issues/8786)

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.