cancel
Showing results for 
Search instead for 
Did you mean: 

Pem Purge Job taking more time and causing high CPU Utilisation.

Symptoms

 
Pem Purge Job taking more time and causing high CPU Utilisation.

 

Diagnosis

 

Rows gets deleted from pemhistory.* table when Database cleanup job is executed. All the rows with recorded time is greater than data retention time (for that probe) will be deleted.
Rows will be updated in pemdata.* when probe gets executed. It depends on probe's execution frequency.
   
So in some situation we may face the issue like the Pem Purge job is taking more time or increasing the CPU Utilisation.

Solution

 1> Too many rows/data for the pem purge activity for cleanup
-- We need set the proper retention policy for the historical records and may need to run the purge job more frequently.

2> Incorrect retention periods for the pem probes,
-- for example "pemhistory.table_statistics" and pemhistory.index_statistics stores statics of all the tables in the database which are check every 5 minutes by PEM probes. You can reduce the frequency at which the probes are fired thus reducing the amount of gathered data and in turn reduce the rate of increase in size of "pemhistory.table_statistics".

3> Tables are not getting analysed properly and hence statistics are not getting updated.
-- Checking the stats table for the autovaccuming/analysing status and if needed performing manual vacuuming or scheduling the vacuum analyse. As incase of bad/incorrect statistics will not get correct result.

4> Heavy bloating on the table can cause performance issue and load the server for processing the cleanup jobs.
-- Continuos Deletion and Updation of table will increase the bloat of the table. We can check the bloat of the table by using the query specified at below link and depends on the bloat result we need to decide abou the maintenace tasks,
https://wiki.postgresql.org/wiki/Show_database_bloat

5> Database is not tuned/configured properly

6> Concurrent sessions are blocking to the purge cleanup job
-- We can check pg_stat_activity if any query is blocking the pem purge job. Or we can also enable log_lock_wait parameter so that it will start logging everything about the locking and blocking session.

Version history
Revision #:
4 of 4
Last update:
‎09-04-2018 08:10 AM
Updated by:
 
Labels (1)
Contributors