[CLOUDSTACK-8917] Instance tab takes long time to load with 12K active VM (total vms: 190K)

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.6.0
• Fix Version/s: 4.6.0
• Component/s: Management Server
• Security Level: Public (Anyone can view this level - this is the default.)
• Assignee: Sudhansu Sahu
• Reporter: Sudhansu Sahu
• Created: 28/Sep/15 09:11
• Updated: 27/Oct/15 06:50
• Resolved: 27/Oct/15 06:50

Description

In load test environment listVirtualmachine takes 8-11 sec to load. This environment has around 12k active VMs. Total number of rows is 190K.

Performance bottleneck in listVirtualmachine command is fetching the count and distinct vms.

        // search vm details by ids
        Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndCount(sc, searchFilter);
        Integer count = uniqueVmPair.second();

This takes 95% of the total time.

To fetch the count and distinct vms we are using below sqls.

Query 1:

SELECT DISTINCT(user_vm_view.id) FROM user_vm_view WHERE user_vm_view.account_type != 5  AND user_vm_view.display_vm = 1  AND user_vm_view.removed IS NULL  ORDER BY user_vm_view.id ASC  LIMIT 0, 20
 

Query 2:

select count(distinct id) from user_vm_view WHERE user_vm_view.account_type != 5  AND user_vm_view.display_vm = 1  AND user_vm_view.removed IS NULL
 

Query 2 is a problematic query.

If we rewrite the query as mentioned below then it will be ~2x faster.

select count(*) from (select distinct id from user_vm_view WHERE user_vm_view.account_type != 5  AND user_vm_view.display_vm = 1  AND user_vm_view.removed IS NULL) as temp;