Checkpoints and slow queries - Mailing list pgsql-performance

># sysctl -a | grep dirty

vm.dirty_background_ratio = 1

vm.dirty_background_bytes = 0

vm.dirty_ratio = 20

vm.dirty_bytes = 0

vm.dirty_writeback_centisecs = 500

vm.dirty_expire_centisecs = 500

This also didn't improve the situation.

• EXPLAIN ANALYZE:
  • http://explain.depesz.com/s/BNva - An insert query inserting just 129 rows takes 20 seconds.
    
  • http://explain.depesz.com/s/5hA - An update query updating 43926 rows takes 55 seconds.
• History: It gets slower after a few days of the system running.

• The tables get updated every 5 minutes. Utmost 50000 rows in a table get updated every 5 minutes. About 50000 rows get inserted every 1 hour.
• There are 90 tables in the DB. 43 of these are updated every 5 minutes. 8/90 tables receive a high update traffic of 50000 updates/5mins. Remaining tables receive an update traffic of 2000 updates/5min. 43/90 tables are updated every 1 hour. 

             name             |    current_setting     |        source        

------------------------------+------------------------+----------------------

 application_name             | psql                   | client

 checkpoint_completion_target | 0.9                    | configuration file

 checkpoint_segments          | 250                    | configuration file

 checkpoint_timeout           | 1h                     | configuration file

 client_encoding              | SQL_ASCII              | client

 client_min_messages          | error                  | configuration file

 constraint_exclusion         | on                     | configuration file

 DateStyle                    | ISO, MDY               | configuration file

 default_statistics_target    | 800                    | configuration file

 default_text_search_config   | pg_catalog.english     | configuration file

 effective_cache_size         | 4GB                    | configuration file

 lc_messages                  | C                      | configuration file

 lc_monetary                  | C                      | configuration file

 lc_numeric                   | C                      | configuration file

 lc_time                      | C                      | configuration file

 listen_addresses             | localhost              | configuration file

 log_autovacuum_min_duration  | 20s                    | configuration file

 log_checkpoints              | on                     | configuration file

 log_connections              | on                     | configuration file

 log_destination              | syslog                 | configuration file

 log_disconnections           | on                     | configuration file

 log_line_prefix              | user=%u,db=%d          | configuration file

 log_lock_waits               | on                     | configuration file

 log_min_duration_statement   | 1s                     | configuration file

 log_min_messages             | error                  | configuration file

 log_temp_files               | 0                      | configuration file

 log_timezone                 | PST8PDT,M3.2.0,M11.1.0 | environment variable

 maintenance_work_mem         | 64MB                   | configuration file

 max_connections              | 12                     | configuration file

 max_locks_per_transaction    | 700                    | configuration file

 max_stack_depth              | 2MB                    | environment variable

 port                         | 5432                   | configuration file

 shared_buffers               | 500MB                  | configuration file

 ssl                          | off                    | configuration file

 statement_timeout            | 4min                   | configuration file

 syslog_facility              | local1                 | configuration file

 syslog_ident                 | postgres               | configuration file

temp_buffers                 | 256MB                  | configuration file

 TimeZone                     | PST8PDT,M3.2.0,M11.1.0 | environment variable

 wal_buffers                  | 1MB                    | configuration file

 work_mem                     | 128MB                  | configuration file