Postgre + solaris + database monitor tool
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
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
earth man2
http://www.postgresql.org/docs/8.2/static/monitoring.html
Have you initialized your database statistics ?
Have you ever used the following commands ?
psql> analyze;
psql> vacuum full;
Have you ever used the following commands ?
psql> analyze;
psql> vacuum full;
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.
Regards,
Bill
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.
Regards,
Bill
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help Earthman