Search Postgresql Archives

Re: Checkpoint_segments optimal value

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux