Improve device mgt core performance #394

Merged
pahansith merged 6 commits from improvement/dm-performance into master 6 months ago
Owner

When device count is exceeding more than 300,000 DM_DEVICE table and DM_ENROLMENT table related queries are taking more than 30s to execute. In order to resolve this downgraded DB normalization level to 2 from 3 and added DEVICE_TYPE and DEVICE_IDENTIFICATION columns to DM_ENROLMENT table, in order to minimize the requirement of joining DM_DEVICE table always for DM_ENROLMENT related queries.

Data migration:

ALTER TABLE `DM_ENROLMENT`
ADD COLUMN `DEVICE_TYPE` VARCHAR(300) NOT NULL AFTER `DEVICE_ID`,
ADD COLUMN `DEVICE_IDENTIFICATION` VARCHAR(300) NOT NULL AFTER `DEVICE_TYPE`,
DROP INDEX `IDX_ENROLMENT_DEVICE_ID_TENANT_ID`,
DROP INDEX `IDX_ENROLMENT_DEVICE_ID_TENANT_ID_STATUS`;

CREATE INDEX IDX_ENROLMENT_DATE_OF_LAST_UPDATE ON DM_ENROLMENT(DATE_OF_LAST_UPDATE);
CREATE INDEX IDX_ENROLMENT_DEVICE_IDENTIFICATION ON DM_ENROLMENT(DEVICE_IDENTIFICATION);
CREATE INDEX IDX_ENROLMENT_DEVICE_TYPE ON DM_ENROLMENT(DEVICE_TYPE);
CREATE INDEX IDX_ENROLMENT_STATUS ON DM_ENROLMENT(STATUS);
CREATE INDEX IDX_ENROLMENT_TENANT_ID ON DM_ENROLMENT(TENANT_ID);

UPDATE `DM_ENROLMENT` e
	JOIN `DM_DEVICE` d ON e.DEVICE_ID = d.ID
	JOIN `DM_DEVICE_TYPE` dt ON dt.ID = d.DEVICE_TYPE_ID
SET
e.DEVICE_TYPE = dt.NAME,
e.DEVICE_IDENTIFICATION = d.DEVICE_IDENTIFICATION;


CREATE INDEX IDX_DM_DEVICE_DEVICE_IDENTIFICATION ON DM_DEVICE(DEVICE_IDENTIFICATION);
CREATE INDEX IDX_DM_DEVICE_LAST_UPDATED_TIMESTAMP ON DM_DEVICE(LAST_UPDATED_TIMESTAMP);

When device count is exceeding more than 300,000 DM_DEVICE table and DM_ENROLMENT table related queries are taking more than 30s to execute. In order to resolve this downgraded DB normalization level to 2 from 3 and added DEVICE_TYPE and DEVICE_IDENTIFICATION columns to DM_ENROLMENT table, in order to minimize the requirement of joining DM_DEVICE table always for DM_ENROLMENT related queries. Data migration: ```sql ALTER TABLE `DM_ENROLMENT` ADD COLUMN `DEVICE_TYPE` VARCHAR(300) NOT NULL AFTER `DEVICE_ID`, ADD COLUMN `DEVICE_IDENTIFICATION` VARCHAR(300) NOT NULL AFTER `DEVICE_TYPE`, DROP INDEX `IDX_ENROLMENT_DEVICE_ID_TENANT_ID`, DROP INDEX `IDX_ENROLMENT_DEVICE_ID_TENANT_ID_STATUS`; CREATE INDEX IDX_ENROLMENT_DATE_OF_LAST_UPDATE ON DM_ENROLMENT(DATE_OF_LAST_UPDATE); CREATE INDEX IDX_ENROLMENT_DEVICE_IDENTIFICATION ON DM_ENROLMENT(DEVICE_IDENTIFICATION); CREATE INDEX IDX_ENROLMENT_DEVICE_TYPE ON DM_ENROLMENT(DEVICE_TYPE); CREATE INDEX IDX_ENROLMENT_STATUS ON DM_ENROLMENT(STATUS); CREATE INDEX IDX_ENROLMENT_TENANT_ID ON DM_ENROLMENT(TENANT_ID); UPDATE `DM_ENROLMENT` e JOIN `DM_DEVICE` d ON e.DEVICE_ID = d.ID JOIN `DM_DEVICE_TYPE` dt ON dt.ID = d.DEVICE_TYPE_ID SET e.DEVICE_TYPE = dt.NAME, e.DEVICE_IDENTIFICATION = d.DEVICE_IDENTIFICATION; CREATE INDEX IDX_DM_DEVICE_DEVICE_IDENTIFICATION ON DM_DEVICE(DEVICE_IDENTIFICATION); CREATE INDEX IDX_DM_DEVICE_LAST_UPDATED_TIMESTAMP ON DM_DEVICE(LAST_UPDATED_TIMESTAMP); ```
charithag force-pushed improvement/dm-performance from 0f58c484f0 to 7c0df33738 6 months ago
charithag added 1 commit 6 months ago
pahansith merged commit 9517e80939 into master 6 months ago
charithag deleted branch improvement/dm-performance 6 months ago
The pull request has been merged as 9517e80939.
Sign in to join this conversation.
No reviewers
No Milestone
No project
No Assignees
1 Participants
Notifications
Due Date

No due date set.

Reference: community/device-mgt-core#394
Loading…
There is no content yet.