Prabhjot Sheena <prabhjot.sheena@xxxxxxxxxxxxxx> wrote: > I m running postgresql 8.3 That is out of support. If you are at all concerned about performance, you would do well to upgrade to a recent and supported major release. http://www.postgresql.org/support/versioning/ Anyway, it is always a good idea to show the output of: SELECT version(); > temp_buffers = 64MB You don't say what you have set for max_connections, but temp_buffers is allocated at need *separately for each connection* and once allocated to a connection it is never released until the connection is closed. So, hypothetically, if you had 100 connections open and they each had used temporary tables at some time or other, even when all are idle they could have 6.4GB of your 12GB of RAM tied up. > work_mem = 512MB Unlike temp_buffers, this one is released at the end of the command (or earlier), but it is possible to have multiple allocations for a single connection. If you had 100 queries active, each using one work_mem allocation, that would allocate about 51.2GB of RAM, which looks like it is more than you have. I generally recommend setting max_connections only to what is needed (with a small reserve) and setting work_mem to 25% of machine (or VM) RAM divided by max_connections. On the face of it, neither of the above seems to be causing problems at the time you ran vmstat, but they worry me. > max_fsm_pages = 809600 Since the EXPLAIN ANALYZE output you later posted show estimates which were off by several orders of magnitude, quite possibly leading to a suboptimal plan, you should probably run queries to check for bloat and update statistics. If you find badly bloated tables you should run CLUSTER or VACUUM FULL on them to eliminate bloat. Any bloated indexes, and any indexed on bloated tables you cleaned up using VACUUM FULL should be fixed with REINDEX. (Once you are on 9.0 or later it is no longer necessary to REINDEX a table after using VACUUM FULL on it, although a regular VACUUM after the VACUUM FULL is still important.) If you find bloat you should make autovacuum more aggressive or add VACUUM ANALYZE crontab jobs, to prevent a recurrence. You may want to look at increasing statistics targets, either globally or on specific columns used for selection criteria. After doing the above, or if you decide not to do any or all of it, you should probably run EXPLAIN ANALYZE VERBOSE; (without specifying a table name and without the FULL option) and review the output. The most important part of the output is the last few lines, which in 8.3 might include warnings that suggest configuration changes. The above might help, but I think the biggest problem may be your VM. You show very low disk I/O numbers, but a pretty significant fraction of the time waiting for I/O. The previously-suggested iostat output may help nail it down more specifically, but basically you seem to have a big problem with bandwidth for storage access. It's pretty disturbing to see lines in vmstat output which show zero disk in or out, but over 10% of CPU time waiting for storage?!? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
