Hi, I'm sorry and thanks for the help, concerning your question : "Out of interest, how are you talking to PostgreSQL? Hand-written SQL via JDBC? Hibernate / TopLink / some other ORM (possibly via JPA)?"
I'm using JPA/Toplink to talk to the PostgresSQL, and I asked my question about profiling because of a slow simple query "SELECT i FROM Item i" which takes 4s to execute. Cordially and best regards. 2009/11/26 Craig Ringer <cr...@postnewspapers.com.au> > On 26/11/2009 5:48 PM, aymen marouani wrote: > > Hi, > > I'm using the Postgres database system version 8.3 for a J2EE > application. > > I'd like to profile and monitor in "real time" the status of my queries > > because I notice some slow loading. > > Can anyone tell how to get a "good" profiling tool for the Postgres > > database system ? > > I'm not aware of any tools that connect to the database to provide > profiling and monitoring. > > What the right course of action is depends on what sort of slowdown > you're encountering. If it's particular activities within the program > that're a problem, then you should probably use logging in your > application to record database activity (including query runtimes) to > see what exactly it's doing. > > If it's unpredictable slowdowns in operations that are normally fast, > then you need to look at the database end. Look into locking issues ( > see: pg_catalog.pg_locks ), table bloat and VACUUM / autovacuum, and the > possibility of simple concurrent load spikes ( see > pg_catalog.pg_stat_activity ). > > Typically what you'll do if you're seeing unpredictably slow queries is > use log_min_duration to log problem statements to the PostgreSQL log, > which you can then analyse. If you enable CSV logging, you can pull the > PostgreSQL log into a database, spreadsheet, or whatever for easier > analysis. > > The new auto_explain module in 8.4 is excellent and very helpful too, > since it can help you find out what plans were used to execute problem > queries easily and conveniently. > > Of course, this won't help you much if your problem is an application > issuing *huge* numbers of very small queries. You can set Pg to log > every query, but you'll rapidly have an apalling amount of data to troll > through, and it's hard to know which ones are related to user-perceived > slowdowns. Application logging is usually a better option for tracking > this sort of thing down. > > Out of interest, how are you talking to PostgreSQL? Hand-written SQL via > JDBC? Hibernate / TopLink / some other ORM (possibly via JPA)? > > If you're using Hibernate, just enable its query logging features via > log4j and watch what happens. > > -- > Craig Ringer >
