I am currently running Zabbix 4.0.17 with postgres 10.12 on Unbuntu 18.04. Suddenly the bgwritter process for postgress is showing high usage, previously it was running with next to no activity. Please see attached graph of the last three months activity.
I took the DB offline recently and ran a full vacuum and it seemed to fix the high activity for a few days but it came back as you can see in the graph.
A few things that you will need to know;
- I have housekeeper setup to run every minute, this SHOULD remove only a few hundred history/trends every time it runs and should only take a few seconds, however at the moment it seems to remove about 30,000 and takes up to 180 seconds to run. (no doubt this is what is causing the high I/O)
- The server is a DELL Poweredge R330 with 16GB Ram
- Zabbix and the DB are on ZFS filesystem.
I'm not a DBA by any stretch of the imagination, but here are some of the non-default parameters we have set for postgres;
shared_buffers = 4096MB
huge_pages = try
temp_buffers = 64MB
work_mem = 128MB
maintenance_work_mem = 512MB
max_stack_depth = 7MB
max_wal_size = 1024MB
autovacuum = on
I could probably bump the RAM to 32GB if that is what is necessary.
Any tips on tuning this properly are appreciated.
Steve
I took the DB offline recently and ran a full vacuum and it seemed to fix the high activity for a few days but it came back as you can see in the graph.
A few things that you will need to know;
- I have housekeeper setup to run every minute, this SHOULD remove only a few hundred history/trends every time it runs and should only take a few seconds, however at the moment it seems to remove about 30,000 and takes up to 180 seconds to run. (no doubt this is what is causing the high I/O)
- The server is a DELL Poweredge R330 with 16GB Ram
- Zabbix and the DB are on ZFS filesystem.
Code:
6117:20200420:135601.355 housekeeping procedure is already in progress 6117:20200420:135635.276 housekeeper [deleted 23273 hist/trends, 0 items/triggers, 2 events, 2 problems, 0 sessions, 0 alarms, 0 audit items in 153.926886 sec, waiting for user command] 6117:20200420:135701.358 forced execution of the housekeeper 6117:20200420:135701.358 executing housekeeper 6117:20200420:135801.375 housekeeping procedure is already in progress 6117:20200420:135901.435 housekeeping procedure is already in progress 6117:20200420:135915.785 housekeeper [deleted 23403 hist/trends, 0 items/triggers, 0 events, 98 problems, 0 sessions, 0 alarms, 0 audit items in 134.392366 sec, waiting for user command] 6117:20200420:140001.388 forced execution of the housekeeper 6117:20200420:140001.388 executing housekeeper 6117:20200420:140101.404 housekeeping procedure is already in progress 6117:20200420:140201.424 housekeeping procedure is already in progress 6117:20200420:140301.425 housekeeping procedure is already in progress 6117:20200420:140341.422 housekeeper [deleted 33195 hist/trends, 0 items/triggers, 2 events, 160 problems, 0 sessions, 0 alarms, 0 audit items in 220.002470 sec, waiting for user command] 6117:20200420:140401.444 forced execution of the housekeeper 6117:20200420:140401.445 executing housekeeper 6117:20200420:140501.480 housekeeping procedure is already in progress 6117:20200420:140601.463 housekeeping procedure is already in progress 6117:20200420:140655.090 housekeeper [deleted 31127 hist/trends, 0 items/triggers, 16 events, 65 problems, 0 sessions, 0 alarms, 0 audit items in 173.628009 sec, waiting for user command] 6117:20200420:140701.469 forced execution of the housekeeper 6117:20200420:140701.469 executing housekeeper 6117:20200420:140801.485 housekeeping procedure is already in progress
shared_buffers = 4096MB
huge_pages = try
temp_buffers = 64MB
work_mem = 128MB
maintenance_work_mem = 512MB
max_stack_depth = 7MB
max_wal_size = 1024MB
autovacuum = on
I could probably bump the RAM to 32GB if that is what is necessary.
Any tips on tuning this properly are appreciated.
Steve
Attached Files