{"id":1650,"date":"2023-04-04T16:48:44","date_gmt":"2023-04-04T14:48:44","guid":{"rendered":"https:\/\/www.loicmathieu.fr\/wordpress\/?p=1650"},"modified":"2023-04-04T16:48:44","modified_gmt":"2023-04-04T14:48:44","slug":"le-profiler-sql-de-visualvm","status":"publish","type":"post","link":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/le-profiler-sql-de-visualvm\/","title":{"rendered":"VisualVM SQL profiler SQL"},"content":{"rendered":"

A little while ago, I discovered the SQL profiler of VisualVM and I thought I should share it with you ;).<\/p>\n

VisualVM is a tool that provides a visual interface to display detailed information about applications running on a Java Virtual Machine (JVM). VisualVM is designed for use in development and production.<\/p>\n

VisualVM provides lightweight profiling tools including a SQL profiler. This will detect each SQL query (passing through the JDBC layer) and group them in a tabular view with the number of executions of the query and the total execution time.<\/p>\n

This tabular view allows you to detect quickly the queries that have the greatest impact on performance. Indeed, a query that is called often but responds quickly is generally not problematic, and a query that has a long execution time but is rarely executed is generally not problematic for the overall performance of an application (but maybe for the part of the application that generates it). When we want to optimize the performance of an application, we will therefore generally look at the requests whose total execution time (the addition of the times of each execution of the request) is the most important. Generally, we will look at the top 3, 5 or 10 queries with the highest total execution time and try to optimize them.<\/p>\n

Here is an example of an optimization done following the performance analysis of Kestra using VisualVM’s SQL profiler. Kestra<\/a> is a highly scalable data scheduling and orchestration platform that creates, executes, schedules, and monitors millions of complex pipelines. It’s also the company I’ve been working at for the past 3 months!<\/p>\n

Kestra, in its open-source version, uses a database engine, the Enterprise Edition allows to use an alternative engine based on Kafka and Elasticsearch. The database engine supports H2, PostgreSQL, and MySQL. The optimization in question has been done for PostgreSQL.<\/p>\n

VisualVM’s JDBC profiler can be accessed via the Profiler<\/strong> tab by clicking on the JDBC<\/strong> button. It can be configured with a text to filter the queries to be analyzed. Once launched, it will record each executed query and display a table with, for each query, the total execution time, the percentage compared to all the queries, as well as the number of executions.<\/p>\n

For my use case, the result was as follows.<\/p>\n\"\"\n

As always in performance, you have to look at the n top<\/strong> problems. Here, I will focus on the first 4 queries, which are the variation of the same query on the queues<\/code> table. We can see that unitarily the queries take about 7ms; depending on the size of the database, they can take much longer to execute. Here, I started with a blank database, so a select should be almost immediate. Even if they are not the longest queries, as they are executed a lot of times, they contribute the most to the overall execution time on the database (about 72% here).<\/p>\n

Kestra has an asynchronous implementation based on message queues for communication between its internal components, among other things, during the execution of flows. When the database engine is used, these queues will poll the queues<\/code> table of the database: every 25ms a query will be executed to read the messages to be processed.<\/p>\n

The first four requests in terms of total execution are the poll requests, so they have a direct impact on the flow execution time.<\/p>\n

It is possible to see the entire query by right-clicking on a line and then View SQL<\/strong>.<\/p>\n

Here is the query:<\/p>\n

\nselect \"value\", \"offset\" from queues \nwhere ((type = CAST('io.kestra.core.models.executions.Execution' AS queue_type)) and \"consumer_scheduler\" = false) \norder by \"offset\" asc \nfetch next 100 rows only \nfor update skip locked\n<\/pre>\n
A explain plan of this query shows that despite the presence of an index on the queue_type<\/code> column, the PostgreSQL optimizer decides to scan the primary key index which is suboptimal because it will scan the entirety of the records in that index.<\/p>\n
Since PosgreSQL has no hint, it is difficult to force it to use a specific index. So I used a little workaround: I removed the primary key and replaced it with a hash index (to be able to quickly access a message when it is deleted). PostgreSQL then favors the existing index on the queue_type<\/code> column, and the query execution time is greatly improved at about 1ms.<\/p>\n
If we look at the top queries, we see that our 4 queries now account for only 23% of the total execution time.<\/p>\n\"\"\n
And the result is there! Almost 50% optimization of the execution time of a flow by changing an index. For more information see the PR #1012<\/a>!<\/p>\n
<\/p>","protected":false},"excerpt":{"rendered":"
A little while ago, I discovered the SQL profiler of VisualVM and I thought I should share it with you ;). VisualVM is a tool that provides a visual interface to display detailed information about applications running on a Java Virtual Machine (JVM). VisualVM is designed for use in development and production. VisualVM provides lightweight profiling tools including a SQL profiler. This will detect each SQL query (passing through the JDBC layer) and group them in a tabular view with…
 Read More  Read More<\/span><\/a><\/p><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[9],"tags":[203,211,159,213],"class_list":["post-1650","post","type-post","status-publish","format-standard","hentry","category-informatique","tag-database","tag-kestra","tag-performance","tag-postgres"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1731,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/optimisation-dindex-postgresql\/","url_meta":{"origin":1650,"position":0},"title":"PostgreSQL index optimization","author":"admin","date":"Tuesday August 22nd, 2023","format":false,"excerpt":"Some time ago, I worked on query execution time optimizations for PostgreSQL, I talk about it here: The VISUALVM SQL PROFILE. Kestra is a highly scalable data orchestration and scheduling platform that creates, executes, schedules, and monitors millions of complex pipelines. It's also the company I work for! The open\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1201,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/introduction-a-quickperf\/","url_meta":{"origin":1650,"position":1},"title":"Introduction to Quickperf","author":"admin","date":"Tuesday March  2nd, 2021","format":false,"excerpt":"QuickPerf is a test library for Java to quickly evaluate and improve the performance of your application. The great interest of Quickperf lies in the fact that this is done via unit tests, which allows, after detecting and correcting a performance problem, to have non-regression tests so that it never\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1611,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/introduction-a-kestra\/","url_meta":{"origin":1650,"position":2},"title":"Introduction to Kestra","author":"admin","date":"Monday March  6th, 2023","format":false,"excerpt":"Kestra is an open-source data orchestrator and scheduler. With Kestra, data workflows, called flows, use the YAML format and are executed by its engine via an API call, the user interface, or a trigger (webhook, schedule, SQL query, Pub\/Sub message, ...). The important notions of Kestra are : The flow:\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":685,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/optimisez-vos-options-avec-access-control-max-age\/","url_meta":{"origin":1650,"position":3},"title":"Optimisez vos OPTIONS avec Access-Control-Max-Age","author":"admin","date":"Wednesday April 12th, 2017","format":false,"excerpt":"Bonjour, Aujourd'hui, je partage avec vous un header HTTP que je viens de d\u00e9couvrir : Access-Control-Max-Age. Aujourd\u2019hui, on execute des requ\u00eates Ajax (ou XHR) sans arr\u00eat, et souvent d'un site vers un autre. Pour des raisons de s\u00e9curit\u00e9, pour qu'une page puisse r\u00e9aliser une requ\u00eate Ajax vers un autre site,\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/CORS_before.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/CORS_before.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/CORS_before.png?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/CORS_before.png?resize=700%2C400 2x"},"classes":[]},{"id":1258,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/profiler-une-application-java-dans-un-conteneur-deploye-dans-kubernetes-avec-jfr-java-flight-recorder\/","url_meta":{"origin":1650,"position":4},"title":"(Fran\u00e7ais) Profiler une application Java dans un conteneur d\u00e9ploy\u00e9 dans kubernetes avec JFR – Java Flight Recorder","author":"admin","date":"Monday April 12th, 2021","format":false,"excerpt":"Sorry, this entry is only available in Fran\u00e7ais.","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":827,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/devfest-lille-2018\/","url_meta":{"origin":1650,"position":5},"title":"Devfest Lille 2018","author":"admin","date":"Friday July  6th, 2018","format":false,"excerpt":"gRPC ----- - Rappel r\u00e9seau, internet - Probl\u00e9matique du format de donn\u00e9e dans les systeme distribu\u00e9 => modification difficile - Historique des protocoles RCP : CORBA, RMI, EJB, SOAP, REST -> 2008 Protocol Buffers : s\u00e9rialization (pr\u00e9mice du RCP chez Google : open sourcing partiel) -> 2009 Thrift (Fb) ->\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1650","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/comments?post=1650"}],"version-history":[{"count":9,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1650\/revisions"}],"predecessor-version":[{"id":1665,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1650\/revisions\/1665"}],"wp:attachment":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/media?parent=1650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/categories?post=1650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/tags?post=1650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}