Postgre + solaris + database monitor tool

Posted on 2007-09-30
Medium Priority
Last Modified: 2008-01-09
I have a postgre database installed on a Solaris platform. Unfortunately i am facing a lot of slowness in the processing of queries. I need to monitor the database queries in order to know the causes of the delay and slowness.
The only available postgre database monotoring software i found on the internet is for Windows platform.
Can someone suggest a software or maybe give me an alternative for tracing queries?

Thanks in advance
Question by:ANISBH
  • 3
  • 2
LVL 22

Expert Comment

by:earth man2
ID: 19986762
LVL 22

Expert Comment

by:earth man2
ID: 19986825
Have you initialized your database statistics ?
Have you ever used the following commands ?
psql> analyze;
psql> vacuum full;
LVL 19

Expert Comment

ID: 19987712
You can turn on a lot of logging options including logging SQL that takes more than some minimum amount of time.  You then just have to review the log file.

How much buffer cache do you have configured for your Postgres installation?  You want to have as much as possible up to a limit of 1GB.   After that the cache management overhead grows very rapidly.

After you identify the long running queries, you might want to look at the query plan(s).  You can then evaluate whether you need to rewrite the query, break it into multiple pieces with a temp table in between, or add/change an index.

Finally, if your system is using a lot of temp tables and/or sessions-specific query work tables, you might want to consider creating a tempfs and using links to put those objects on it.  A tempfs is essentially a RAM disk so you avoid hitting disk altogether and can make use of more physical memory than is health for the buffer cache.

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 19989176
Dear Earthman,

How do you initialize database statistics?

I want to be able to see statistics of the database without i having to analyze every query manually. The EXPLAIN command requires me to do that manually. Since i have hundreds of queries from an ongoing application, i need a tool that runs at the background and saves logs of the activity of every query without me having to interfere.
LVL 22

Accepted Solution

earth man2 earned 375 total points
ID: 19989987
You have to turn logging on -- see the link that I gave you in my first post

Author Comment

ID: 20040008
Thanks for your help Earthman

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question