Re: How to reduce IOWAIT and CPU idle time?

Hello Jeff,

Thanks for the reply!

Yes, you are right, the database size has grown from 5 GB database to 100
GB database and may be there is problem in slowness in disk. However we
cannot replace the disk right now.

Sure. i will try to increase the shared_buffer value to 90% and see the
performance.

Thanks,
Samir Magar

On Sun, Sep 11, 2016 at 7:16 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Sat, Sep 10, 2016 at 3:49 AM, Samir Magar <samirmagar8(at)gmail(dot)com>
> wrote:
>
>> Hello,
>>
>>
>> My Application has normally 25 to 30 connections and it is doing lot of
>> insert/update/delete operation.
>> The database size is 100GB.
>> iowait is at 40% to 45 % and CPU idle time is at 45% to 50%
>> TOTAL RAM = 8 GB TOTAL CPU = 4
>>
>> postgresql.conf parametre:
>>
>> shared_buffers = 2GB
>> work_mem = 100MB
>> effective_cache_size = 2GB
>> maintenance_work_mem = 500MB
>> autovacuum = off
>> wal_buffers = 64MB
>>
>>
>> How can i reduce iowait and CPU idle time. It is slowing all the queries.
>> The queries that used to take 1 sec,it is taking 12-15 seconds.
>>
>
> What changed between the 1 sec regime and the 12-15 second regime? Just
> growth in the database size?
>
> Index-update-intensive databases will often undergo a collapse in
> performance once the portion of the indexes which are being rapidly dirtied
> exceeds shared_buffers + (some kernel specific factor related
> to dirty_background_bytes and kin)
>
> If you think this is the problem, you could try violating the conventional
> wisdom by setting shared_buffers 80% to 90% of available RAM, rather than
> 20% to 25%.
>
> Cheers,
>
> Jeff
>