Tag: Postgres

Description 

Some customers might have High CPU usage on their EM servers. In which the CPU runs at 100%.

Product 

EM- Version 23.0.3 and older.

Steps to Identify the Cause 

Log into the EM server and validate that the importers are running hard. Normally there will be several importer services opened and being used. You will have one that is taxed more than other as it is the brain importer.

Go to the User Role Section and see if there are any user roles with the Trash Can icon next to it. If so this could be an indicator that there is NO User attached to the User Role. In this case there are three options that can be performed.
1. Add at least 1 user to the user role.
2. Unpromote the user role.
3. Delete the user role if it is not needed.


Once we have identified this is the case for the UserRole we NEED to make sure the Postgres DB is in fact growing.

1. First we need to stop the EM services.
2. Open up windows and search for PGadmin and launch the application.
3. Use the tree to open up the Enterprise folder and log in using the password of postgres .
4. In the upper portion there is a Tools menu you can select to open a Query.
5. Run the following query.
   select count(*) from camera_camerapermission
   

• NOTE* Each Time you run the new Query make sure you do not have the last statement in the query box. Run a new Query.
  In the query box copy and paste the following in and execute the query. This will take a few minutes if there are a lot of table counts.

If the table shows millions of entry’s we need to run the following script in pgadmin and execute the query.

delete from camera_camerapermission
where id in (
select id from (
select id, row_number() OVER w as rnum
from camera_camerapermission
window w as ( partition by device_id, permission_id
order by id
)
) t
where t.rnum > 1);

select count(*) from camera_camerapermission;

When the query is done executing the count will show up with a certain number depending on the tables.
Take note to the count number.
Then start the EM services, and wait a few minutes and execute the query again.
select count (*) from camera_camerapermissions
The table will grow for about 24 hours.

This should stop the table from growing at an extremely fast rate which taxes the system CPU.

Expected Results 

The table should not be growing.

Actual Results 

In Fact when there is a UserRole with no user- the table will bloat.

Solution

Engineering has identified the potential cause. This might not be the actual table responsible in all situations. However they have began a new ticket in Jira to investigate this and make sure we put a fail safe into prohibit the creation of an Empty User Role.

Original Issue seen in:
Jira- AES-16
Trac 19900
Current Ticket AES-465