Re: slow queries, possibly disk io

I didn't see iostat as available to install, but I'm using dstat to see this.

The server has constant disk reads averaging around 50M and quite a
few in the 60M range. This is when selects are being done, which is
almost always. I would think if postgres is grabbing everything from
memory that this wouldn't happen. This is why I think there must be
some way to allocate more mem to postgres.

There is 2 gigs of mem in this server. Here are my current settings.

max_connections = 100
shared_buffers = 50000
sort_mem = 4096
vacuum_mem = 32768
effective_cache_size = 450000

Shared buffers is set to 10% of total mem. Effective cache size is 90% of mem.

Is there anything that can be done to have postgres grab more from
memory rather than disk?


On 5/30/05, Rudi Starcevic <tech@wildcash.com> wrote:
> Hi,
>
> I had some disk io issues recently with NFS, I found the command 'iostat
> -x 5' to be a great help when using Linux.
>
> For example here is the output when I do a 10GB file transfer onto hdc
> Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s
> avgrq-sz avgqu-sz   await  svctm  %util
> hdc          0.00 875.95  0.00 29.66    0.00 7244.89     0.00  3622.44
> 244.27     3.07  103.52   1.78   5.27
>
> The last field show the disk is 5.27% busy.
>
> I have seen this value at 100%, adding more server brought it under 100%.
> It seems that if you hit 100% problems sort of cascade all over that
> place. For example Apache connections went right up and hit their max.
>
> I am not sure how accurate the % is but it has work pretty well for me.
>
> Perhaps use this command in another window with you run your SQL and see
> what it shows.
>
> HTH.
> Kind regards,
> Rudi.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>


--
-Josh