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.ja...@gmail.com> wrote: > On Sat, Sep 10, 2016 at 3:49 AM, Samir Magar <samirmag...@gmail.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 >
