How to Detect Slow Queries With jOOQ
Just recently, we implemented a nice little feature in jOOQ’s code generator; it detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Read on to learn more.
Join the DZone community and get the full member experience.
Join For FreeJust recently, we implemented a nice little feature in jOOQ’s code generator:
https://github.com/jOOQ/jOOQ/issues/4974
It detects whenever the jOOQ code generator runs a slow query to reverse engineer schema meta information. Why?
In our development and integration test environment, we don’t have huge schemas with all the different performance edge cases put in place. For instance, we don’t have 5000 Oracle synonyms. Or 10000 procedures with 500 parameters each. We do cover some common edge cases, but not in all databases.
Users, on the other hand, tend to accept the status quo after a while. The code generator is slow-ish? Sure, because we have a huge schema. This lazy acceptance is an impediment to our product quality. We had rather our users report all sorts of issues they encounter, so we want to encourage them.
And, We Did
In the upcoming jOOQ version 3.8 (and patch releases for 3.5.5, 3.6.5, and 3.7.3), we’ve added a nice little ExecuteListener
to jOOQ-meta, which looks roughly like this:
class PerformanceListener
extends DefaultExecuteListener {
StopWatch watch;
class SQLPerformanceWarning
extends Exception {}
@Override
public void executeStart(ExecuteContext ctx) {
super.executeStart(ctx);
watch = new StopWatch();
}
@Override
public void executeEnd(ExecuteContext ctx) {
super.executeEnd(ctx);
if (watch.split() > 5_000_000_000L)
log.warn(
"Slow SQL",
"jOOQ Meta executed a slow query"
+ "\n\n"
+ "Please report this bug here: "
+ "https://github.com/jOOQ/jOOQ/issues/new\n\n"
+ formatted(ctx.query()),
new SQLPerformanceWarning());
}
}
It’s very simple. Every time we start executing a query, a "stop watch" is started. Every time we end execution, we check if the watch elapsed more than 5 seconds. If so, we log a warning, a link to our issue tracker, a formatted version of the SQL query, and a stack trace to help find the exact spot where the slow statement was executed.
Let’s Run This
The reason why we did this is because we’ve seen for ourselves that the PostgreSQL code generator runs a slow query to fetch all the stored procedures (and to generate overload indexes). The produced error message is:
[WARNING] Slow SQL : jOOQ Meta executed a slow query (slower than 5 seconds)
Please report this bug here: https://github.com/jOOQ/jOOQ/issues/new
select
"r1"."routine_schema",
"r1"."routine_name",
"r1"."specific_name",
case when exists (
select 1 as "one"
from "information_schema"."parameters"
where (
"information_schema"."parameters"."specific_schema" = "r1"."specific_schema"
and "information_schema"."parameters"."specific_name" = "r1"."specific_name"
and upper("information_schema"."parameters"."parameter_mode") 'IN'
)
) then 'void'
else "r1"."data_type"
end as "data_type",
"r1"."character_maximum_length",
"r1"."numeric_precision",
"r1"."numeric_scale",
"r1"."type_udt_schema",
"r1"."type_udt_name",
case when exists (
select 1 as "one"
from "information_schema"."routines" as "r2"
where (
"r2"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and "r2"."routine_schema" = "r1"."routine_schema"
and "r2"."routine_name" = "r1"."routine_name"
and "r2"."specific_name" "r1"."specific_name"
)
) then (
select count(*)
from "information_schema"."routines" as "r2"
where (
"r2"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and "r2"."routine_schema" = "r1"."routine_schema"
and "r2"."routine_name" = "r1"."routine_name"
and "r2"."specific_name" <= "r1"."specific_name"
)
) end as "overload",
"pg_catalog"."pg_proc"."proisagg"
from "information_schema"."routines" as "r1"
join "pg_catalog"."pg_namespace"
on "pg_catalog"."pg_namespace"."nspname" = "r1"."specific_schema"
join "pg_catalog"."pg_proc"
on (
"pg_catalog"."pg_proc"."pronamespace" = "pg_catalog"."pg_namespace".oid
and (("pg_catalog"."pg_proc"."proname" || '_') || cast("pg_catalog"."pg_proc".oid as varchar)) = "r1"."specific_name"
)
where (
"r1"."routine_schema" in (
'public', 'multi_schema', 'pg_catalog'
)
and not("pg_catalog"."pg_proc"."proretset")
)
order by
"r1"."routine_schema" asc,
"r1"."routine_name" asc,
"overload" asc
org.jooq.util.AbstractDatabase$1$SQLPerformanceWarning
at org.jooq.util.AbstractDatabase$1.executeEnd(AbstractDatabase.java:230)
at org.jooq.impl.ExecuteListeners.executeEnd(ExecuteListeners.java:163)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:346)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:308)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2703)
at org.jooq.util.postgres.PostgresDatabase.getRoutines0(PostgresDatabase.java:707)
at org.jooq.util.AbstractDatabase.getRoutines(AbstractDatabase.java:1131)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:417)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:314)
at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:279)
at org.jooq.util.GenerationTool.run(GenerationTool.java:490)
at org.jooq.util.GenerationTool.generate(GenerationTool.java:193)
at org.jooq.util.maven.Plugin.execute(Plugin.java:131)
...
We can now proceed with fixing the query, easily.
You Can Do the Same!
The implementation of the ExecuteListener
was straight forward. You can do the same, very easily. Just hook a simple execute listener into your jOOQ Configuration
, measuring execution speeds and logging warnings after a threshold—done.
Happy debugging!
Further reading
Coincidentally, a very similar approach has been documented by the engineering team at Square–The Query Sniper:
Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments