Re: understanding postgres issues/bottlenecks

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

 



David Rees wrote:
> On Tue, Jan 6, 2009 at 11:02 AM, Stefano Nichele
> <stefano.nichele@xxxxxxxxx> wrote:
>> BTW, why did you said I/O bound ? Which are the parameters that highlight
>> that ? Sorry for my ignorance....
> 
> In addition to the percentage of time spent in wait as Scott said, you
> can also see the number of processes which are blocked (b column on
> the left). Those are the processes which would like to run but are
> waiting for IO to complete.
> 
>>From your earlier vmstat output, you can see that often quite a large
> number of processes were waiting on IO - 20-30 of them. This is
> confirmed by your load average. Since you only have 6 spindles, during
> those periods, request service latency was likely very high during
> those periods.
> 
> Anyway, I would start with significantly increasing the amount of
> memory you have allocated to shared_buffers.
> 
> 32MB is _way_ too low. For a dedicated PostgreSQL machine with 4GB
> RAM, 1GB going to be a lot closer to optimal, with the optimal setting
> probably somewhere between 500MB and 2GB.
> 
> And please post the rest of the information that the others have asked for...
> 
> -Dave
> 

A couple of notes on "iostat":

1. The package it lives in is called "sysstat". Most Linux distros do
*not* install "sysstat" by default. Somebody should beat up on them
about that. :)

2. It does not require admin privileges to install or execute if you
want to have your own personal copy. You will, however, need the Gnu C
compiler, "make", and some other things that also may not be installed.
In any event, you can build "sysstat" from source in your home directory
if you can't get the sysadmin to install it globally.

3. Once you do get it installed, the command line to run it that gives
the most information is

$ iostat -cdmtx <sampling interval> <number of samples>

That will give you CPU utilization, throughputs in megabytes, time
stamps and extended statistics like device utilizations, queue lengths
and average service times. And if you're running 2.6.25 or later, it
should give you all the statistics for all partitions, not just devices!

Somewhere buried in one of my open source projects is a Perl script that
will parse the "iostat" output into a CSV file. By the way, if you need
to, you can be fairly aggressive with small sampling intervals. "iostat"
is very efficient, and chances are, you're I/O bound anyhow so your
processors will have plenty of bandwidth to format numbers out of /proc.
:) I normally run with samples every ten seconds, but you can probably
go even smaller if you need to.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux