Обсуждение: How to reduce IOWAIT and CPU idle time?
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.
version detials:
Linux zabbix-inst.novalocal 3.10.0-229.7.2.el7.x86_64 #1 SMP Fri May 15 21:38:46 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux
database version: postgrsql 9.2.13 community.
Thanks,
Samir Magar
On Sat, Sep 10, 2016 at 8:49 PM, Samir Magar <samirmagar8@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 = 4postgresql.conf parametre:shared_buffers = 2GBwork_mem = 100MBeffective_cache_size = 2GBmaintenance_work_mem = 500MBautovacuum = offwal_buffers = 64MBHow 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.
That does not point out the specific problem you are facing. Queries can slow down for a lot of reasons like as follows -
- Lack of maintenance
- Bloats in Tables and Indexes
- Data size growth
- If writes are slowing down, then it could be because of slow disks
Are you saying that queries are slowing down when there are heavy writes ? Are you referring to SELECTs or all types of queries ?
Regards,
Venkata B N
Fujitsu Australia
On Sat, Sep 10, 2016 at 3:49 AM, Samir Magar <samirmagar8@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 = 4postgresql.conf parametre:shared_buffers = 2GBwork_mem = 100MBeffective_cache_size = 2GBmaintenance_work_mem = 500MBautovacuum = offwal_buffers = 64MBHow 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
Hello Venkata,
Thank you for the reply!
I forgot to specify the application name.It is ZABBIX tool using postgreSQL database. All types of queries are running slow and i can see application is writing continuously.
Yesterday , i updated effective_io_concurrency to 25 which earlier set as default. But this has not helped me to solve the problem.
Yes, you are right, the database size has grown from 5 GB database to 100 GB database and may be there is problem in slownesss in disk. However we cannot replace the disk right now.
I ran vacuum and analyze manually on all the tables as well. Still it is not helping. Can you think of any other setting which i should enable?
Thanks,
Samir Magar
On Sun, Sep 11, 2016 at 6:04 AM, Venkata B Nagothi <nag1010@gmail.com> wrote:
On Sat, Sep 10, 2016 at 8:49 PM, Samir Magar <samirmagar8@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 = 4postgresql.conf parametre:shared_buffers = 2GBwork_mem = 100MBeffective_cache_size = 2GBmaintenance_work_mem = 500MBautovacuum = offwal_buffers = 64MBHow 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.That does not point out the specific problem you are facing. Queries can slow down for a lot of reasons like as follows -- Lack of maintenance- Bloats in Tables and Indexes- Data size growth- If writes are slowing down, then it could be because of slow disksAre you saying that queries are slowing down when there are heavy writes ? Are you referring to SELECTs or all types of queries ?Regards,Venkata B NFujitsu Australia
On Sat, Sep 10, 2016 at 4:19 PM, Samir Magar <samirmagar8@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 = 4postgresql.conf parametre:autovacuum = off
Thanks,
Pavan
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
PostgreSQL Development, 24x7 Support, Training & Services
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.janes@gmail.com> wrote:
On Sat, Sep 10, 2016 at 3:49 AM, Samir Magar <samirmagar8@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 = 4postgresql.conf parametre:shared_buffers = 2GBwork_mem = 100MBeffective_cache_size = 2GBmaintenance_work_mem = 500MBautovacuum = offwal_buffers = 64MBHow 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