我们如何剖析JOOQ语句以提高速度 [英] How can we profile JOOQ statements for speed

问题描述

我已经将Union Platform的JOOQ实施为基于Java的游戏服务器，并使用Union Platform的Orbiter Micro(Union JS客户端)在浏览器上运行它.

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.

但是，在游戏中只有30-40名活跃玩家的情况下，联合平台在速度方面落后.

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 ,

每位玩家的回合，

• 播放器可以放入一个整数，在一系列条件检查(4个查询)之后，它可以通过JOOQ插入/更新数据库
• 然后取回整数的状态，它再次执行3个查询，有效地转换为3个链接的哈希映射以更新游戏的状态.

现在，响应花费了很多时间，玩家发现了性能问题.联合平台实际上声称可以轻松地在一个实例中处理1000多名活跃玩家.因此，这实际上意味着我的JOOQ查询中存在一些错误(我确实使用了许多LEFT OUTER JOINs).

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.

推荐答案

对我来说，这听起来像是普通的SQL调优问题(或者可能是几个不同的问题).有了jOOQ，您就可以执行实际的SQL，并且在使用OUTER JOIN时，如果没有正确的索引和约束，可能会出错，那么问题很可能出在SQL本身上.当您打开调试/跟踪日志记录时，jOOQ会将几条信息打印到日志输出中，包括

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

• SQL语句
• 结果的前5条记录
• 语句执行时间

• 与调试级别相同
• 陈述准备时间
• 绑定值
• 声明绑定时间

如果您输入 log4j 或博客文章

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

这是一个开始.您还可以使用 Yourkit Profiler 或 Postgres文档中对此有一些指示.

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.

这篇关于我们如何剖析JOOQ语句以提高速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！