Page 1 of 2
Postgres disk activity
Posted: Wed Mar 08, 2017 4:14 am
by Fred Kroeger
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
Re: Postgres disk activity
Posted: Wed Mar 08, 2017 6:02 am
by spurrellian
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
Re: Postgres disk activity
Posted: Wed Mar 08, 2017 4:28 pm
by tgriep
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.
Re: Postgres disk activity
Posted: Wed Mar 08, 2017 5:41 pm
by Fred Kroeger
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
Re: Postgres disk activity
Posted: Wed Mar 08, 2017 5:50 pm
by ssax
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
Re: Postgres disk activity
Posted: Wed Mar 08, 2017 6:14 pm
by Fred Kroeger
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
Re: Postgres disk activity
Posted: Thu Mar 09, 2017 2:10 pm
by avandemore
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.
Re: Postgres disk activity
Posted: Thu Mar 09, 2017 11:10 pm
by Fred Kroeger
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?
Re: Postgres disk activity
Posted: Fri Mar 10, 2017 10:24 am
by avandemore
Please share the output of:
Code: Select all
# pidstat -dl -p [postgres PID] 5 12
Re: Postgres disk activity
Posted: Mon Mar 13, 2017 2:41 am
by Fred Kroeger
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