Postgres disk activity

Fred Kroeger · Post by **Fred Kroeger** » Wed Mar 08, 2017 4:14 am

I'm trying to track down some disk utilisation issues and I've found that the postgres processes are performing a large amount of disk writes.
Tracking via iotop, it shows that there are up to 12 MB/sec of writes for postgres.
Looking at the NagiosXI "Repairing the Database doco" , it states that postgres is used for storing the Nagios preferences. If that's the case why would there be so many constant disk writes?
I've checked another Nagios instance and it shows similar figures. I'm running NagiosXI 5.4.2 on both the instances mentioned here.

regards... Fred

spurrellian · Post by **spurrellian** » Wed Mar 08, 2017 6:02 am

I've noticed the same as you that since the last update (5.4.2) that disk I/O seems to have increased and running IOTOP seems to show postgresql doing a lot of writes.

I was planning on converting postgresql to mysql just havent had time to do it on my dev system

https://support.nagios.com/kb/article.p ... ategory=31

Post by **tgriep** » Wed Mar 08, 2017 4:28 pm

In the newer versions if Nagios 5.x.x, there were some changes done for how the Global Event handlers are run on the system.
The new way it runs is to add a new event to a table called xi_events which in your case it in the Postgres database.
Then a cron job runs, looks at the xi_events table, gets the event and runs it.
That process also uses the xi_meta and the xi_eventqueue tables for storing temporary data, etc. so that is why you are seeing more disk writes for the Postgres database.

Fred Kroeger · Post by **Fred Kroeger** » Wed Mar 08, 2017 5:41 pm

Thanks for the update, that woould be consistent with what I see - there are periods of no Reads then a burst of a small number of Reads.
However the amount of Writes happening seem way out of proportion? There is much more write activity in the Postgres d/base than in the MySQL d/base.
The Nagios instance that is impacted the most has 1500 Hosts & 13,000 Services configured.
Perhaps this change can be reviewed to be more efficient for large installations?

regards... Fred

ssax · Post by **ssax** » Wed Mar 08, 2017 5:50 pm

Please post the output of this command:

Code: Select all

echo "SELECT relname AS objectname, relkind AS objecttype, reltuples, pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class WHERE relpages >= 8 ORDER BY relpages DESC;" | psql nagiosxi nagiosxi

Fred Kroeger · Post by **Fred Kroeger** » Wed Mar 08, 2017 6:14 pm

This is from the instance last mentioned

Code: Select all

           objectname            | objecttype | reltuples |  size
---------------------------------+------------+-----------+---------
 xi_meta                         | r          |      2554 | 8712 kB
 pg_toast_74930                  | t          |       336 | 1120 kB
 xi_meta_keyname_idx             | i          |      2554 | 1008 kB
 event_time                      | i          |      2440 | 752 kB
 xi_meta_metatype_id_idx         | i          |      2554 | 728 kB
 xi_meta_pkey                    | i          |      2554 | 664 kB
 xi_auditlog                     | r          |      3680 | 664 kB
 xi_events_pkey                  | i          |      2440 | 592 kB
 xi_meta_metaobj_id_idx          | i          |      2554 | 584 kB
 xi_usermeta                     | r          |      3709 | 480 kB
 pg_proc                         | r          |      2220 | 432 kB
 pg_attribute                    | r          |      2386 | 368 kB
 pg_depend                       | r          |      5630 | 336 kB
 pg_depend_depender_index        | i          |      5630 | 320 kB
 pg_depend_reference_index       | i          |      5630 | 304 kB
 xi_events                       | r          |      2440 | 288 kB
 xi_auditlog_ip_address          | i          |      3680 | 280 kB
 xi_auditlog_source              | i          |      3680 | 272 kB
 xi_auditlog_user                | i          |      3680 | 264 kB
 pg_statistic                    | r          |       419 | 256 kB
 xi_usermeta_user_id_key         | i          |      3709 | 240 kB
 xi_auditlog_type                | i          |      3680 | 240 kB
 pg_proc_proname_args_nsp_index  | i          |      2220 | 208 kB
 pg_toast_2618                   | t          |       115 | 200 kB
 pg_toast_2619                   | t          |        34 | 176 kB
 pg_attribute_relid_attnam_index | i          |      2386 | 168 kB
 pg_description                  | r          |      2403 | 168 kB
 pg_toast_74930_index            | i          |       336 | 160 kB
 xi_usermeta_autoload_idx        | i          |      3709 | 160 kB
 xi_auditlog_pkey                | i          |      3680 | 152 kB
 xi_auditlog_log_time            | i          |      3680 | 152 kB
 pg_description_o_c_o_index      | i          |      2403 | 128 kB
 pg_attribute_relid_attnum_index | i          |      2386 | 120 kB
 xi_usermeta_pkey                | i          |      3709 | 112 kB
 pg_operator                     | r          |       704 | 104 kB
 pg_toast_74901_index            | i          |        10 | 72 kB
 pg_rewrite                      | r          |        86 | 72 kB
 pg_proc_oid_index               | i          |      2220 | 64 kB

avandemore · Post by **avandemore** » Thu Mar 09, 2017 2:10 pm

Can you describe more in details what you mean here:

Thanks for the update, that woould be consistent with what I see - there are periods of no Reads then a burst of a small number of Reads.
However the amount of Writes happening seem way out of proportion? There is much more write activity in the Postgres d/base than in the MySQL d/base.

Postgres is going to queue up certain types of writes and commit them in bulk, is that what you are seeing? Also Postgres's WAL guarantees disk consistency, but requires more IO.

Fred Kroeger · Post by **Fred Kroeger** » Thu Mar 09, 2017 11:10 pm

What I see via iotop is periodic Reads and fairly consistent Writes for the postgres processes. If the writes are batched that may account for large amount of data being written?

avandemore · Post by **avandemore** » Fri Mar 10, 2017 10:24 am

Please share the output of:

Code: Select all

# pidstat -dl -p [postgres PID] 5 12

Fred Kroeger · Post by **Fred Kroeger** » Mon Mar 13, 2017 2:41 am

Your request is easier said than done. By the time I can identify a PID in iotop and then run the query, it would often have terminated.
Eventually I was able to get a few that returned a few lines.

Code: Select all

 pidstat -dl -p 54109 5 12
Linux 2.6.32-358.23.2.el6.x86_64 (nagios.site.com.au)   03/13/2017      _x86_64_   (4 CPU)

03:27:36 PM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
03:27:41 PM     54109      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(36338) idle    
03:27:46 PM     54109      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(36338) idle    
03:27:51 PM     54109      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(36338) idle    
03:27:56 PM     54109      0.00      6.40      0.00  postgres: nagiosxi nagiosxi ::1(36338) idle    
03:28:01 PM     54109      0.00      9.60      0.00  postgres: nagiosxi nagiosxi ::1(36338) idle

Code: Select all

03:32:25 PM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
03:32:30 PM      7911      0.00      9.60      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:32:35 PM      7911      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:32:40 PM      7911      0.00      4.80      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:32:45 PM      7911      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:32:50 PM      7911      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:32:55 PM      7911      0.00      8.00      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle    
03:33:00 PM      7911      0.00      9.60      0.00  postgres: nagiosxi nagiosxi ::1(38430) idle

Code: Select all

03:38:07 PM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
03:38:12 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:17 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:22 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:27 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:32 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:37 PM      2386      0.00     40.00      0.00  postgres: writer process                       
03:38:42 PM      2386      0.00     12.80      0.00  postgres: writer process                       
03:38:47 PM      2386      0.00      0.00      0.00  postgres: writer process                       
03:38:52 PM      2386      0.00      0.00      0.00  postgres: writer process

Code: Select all

03:40:23 PM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
03:40:28 PM     38450      0.00     12.77      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:33 PM     38450      0.00     12.80      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:38 PM     38450      0.00      1.60      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:43 PM     38450      0.00      6.40      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:48 PM     38450      0.00      3.20      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:53 PM     38450      0.00      1.60      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:40:58 PM     38450      0.00      6.40      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle    
03:41:03 PM     38450      0.00      1.60      0.00  postgres: nagiosxi nagiosxi ::1(41751) idle

Nagios Support Forum

Postgres disk activity

Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity

Re: Postgres disk activity