DEV Community

loading...

Database Query Statistics

rohithmenon89 profile image Rohith Menon ・1 min read

I am pretty sure most of us developers will face some database performance problem every day.
Ninety percent of the time this could be due to the missing indexes.

Otherwise, the query does not use the newly created index and instead scans the whole table.

Most of the time we will not notice this since the application works perfectly fine for the first few weeks/months and performance gets worse slowly every day as the table grows.

I myself would prefer something automated which will prompt or notify in-case queries take a long time than expected. This is when I thought of making use of the hibernate-statistics setting and turning on this log. But this is doing a lot of things in the background and making use of a lot of non-week references which also takes a fair amount of JVM memory.

I thought of implementing a small library that will help me to customize to enable what we need and not store any other information.

My motivation was

  1. Easy customizations using system properties.
  2. Injectable even on non-hibernate or non-spring projects by little tweaks.
  3. Statistics reporting will help the data to be sent to Prometheus or similar databases and create alerts to notify whether there are queries that need to be improved.
  4. Developer need logs for SQL queries with the time taken to execute.
  5. Developers can create alerts based on the Splunk (logging system) or Prometheus if there are queries taking more time or fetching rows more than expected.

Library link here

Discussion (0)

Forem Open with the Forem app