I have been trying to diagnose a performance problem we have been seeing with
a postgres application. The performance of the database server is usually
quite good but every now and then it slows to a crawl. The output of vmstat
does not show excessive CPU usage or disk IO. The output of ps
I'm working with an application where the database is entirely resident in RAM
(the server is a quad opteron with 16GBytes of memory). It's a web
application and handles a high volume of queries. The planner seems to be
generating poor plans for some of our queries which I can fix by raising
When you do explain analyze of a query that you have difficulties
with, how are the planner's estimates. Are the estimated number of rows
about equal to the actual number of rows?
Some of them are pretty far off. For example
- Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161)
I have an application that has a table that is both read and write intensive.
Data from iostat indicates that the write speed of the system is the factor
that is limiting performance. The table has around 20 columns and most of the
columns are indexed. The data and the indices for the table
Talk about your IO system a bit. There might be obvious ways to improve.
What System/Motherboard are you using?
What Controller Cards are you using?
What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
What kind of RAIDs do
What kind of order of improvement do you need to see?
A lot since the load on the system is expected to increase by up to 100% over
the next 6 months.
What period are these number for? Were they collected over 1 hour, 1 day, 1
month?
I thought I mentioned that in the earlier post but it
Hi all,
After a long time of reading the general list it's time to subscribe to
this one...
We have adapted our application (originally written for oracle) to
postgres, and switched part of our business to a postgres data base.
The data base has in the main tables around 150 million rows,
[snip]
Have you tried reindexing your active tables?
Not yet, the db is in production use and I have to plan for a down-time
for that... or is it not impacting the activity on the table ?
It will cause some performance hit while you are doing it. It sounds like
something is bloating
