Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Imagine you have inherited some project with extensive usage of SQL queries.

Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.

A question is: could you suggest a plan to sequentially scan the system and optimize the SQL queries performance?

I suggest the following steps of that plan:

  1. check which queries take the longest time to execute - how can we do it in PostgreSQL?
  2. check which queries are executed most often - how can we do it in PostgreSQL?
  3. find out which queries are not using the indexes - how can we do it in PostgreSQL?
  4. find out if the connection pool is operating without any delays - - how can we do it in PostgreSQL?
  5. check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
  6. which queries tend to hit the database instead of hitting the cache - how can we do it in Hibernate?

Please suggest your own options.

May be there is some soft to recommend for this task?

May be there are some other database parameters to check, to log, to plot to monitor?

share|improve this question
add comment

migrated from stackoverflow.com Apr 4 '13 at 16:37

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote accepted

Here are some more checks I can think of:

  • Check that PostgreSQL prepared statements use is enabled.

  • If you have a table with many elements, check if some of those can be "moved" to another table of archived items. (for instance, one query that returns the transactions of the last 7 days)

  • Check that you are not only using the first level cache, but 2nd level as well. Maybe with ehcache or infinispan. Those can cache many objects, even persisting some to disk. You have to make sure that modifications in other threads invalidates those objects. This also depends on the transaction isolation that you are using.

  • If you have entities that are readonly (they are never modified) you may consider loading all of those into memory as java objects. Then you can reduce some joins.

  • For your entities, if you have a dependency to another object, you may save its ID in your entity field. Then when you load your object it doesn't do a join.

  • Related to the previous point, you can also use lazy loading of entities.

  • Use P6Spy or a similar library to know how long the queries are taking.

share|improve this answer
add comment
  1. Get the dynamic SQL generated by Hibernate and ask PostgreSQL to EXPLAIN PLAN.
  2. Log the queries to your database using an aspect. Add in wall time while you're at it so you can calculate mean wall time and standard deviation.
  3. EXPLAIN PLAN and look for TABLE SCAN
  4. Don't know - it's not under PostgreSQL control. Better to look into your app server.
  5. Aspects and JMX
  6. Aspects and JMX
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.