Return-Path: X-Original-To: apmail-cloudstack-issues-archive@www.apache.org Delivered-To: apmail-cloudstack-issues-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9E49318C38 for ; Fri, 20 Nov 2015 11:54:11 +0000 (UTC) Received: (qmail 29458 invoked by uid 500); 20 Nov 2015 11:54:11 -0000 Delivered-To: apmail-cloudstack-issues-archive@cloudstack.apache.org Received: (qmail 29428 invoked by uid 500); 20 Nov 2015 11:54:11 -0000 Mailing-List: contact issues-help@cloudstack.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@cloudstack.apache.org Delivered-To: mailing list issues@cloudstack.apache.org Received: (qmail 29418 invoked by uid 500); 20 Nov 2015 11:54:11 -0000 Delivered-To: apmail-incubator-cloudstack-issues@incubator.apache.org Received: (qmail 29415 invoked by uid 99); 20 Nov 2015 11:54:11 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 20 Nov 2015 11:54:11 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 290742C1F51 for ; Fri, 20 Nov 2015 11:54:11 +0000 (UTC) Date: Fri, 20 Nov 2015 11:54:11 +0000 (UTC) From: "ASF GitHub Bot (JIRA)" To: cloudstack-issues@incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (CLOUDSTACK-8485) listAPIs are taking too long to return results MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/CLOUDSTACK-8485?page=3Dcom.atla= ssian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId= =3D15015997#comment-15015997 ]=20 ASF GitHub Bot commented on CLOUDSTACK-8485: -------------------------------------------- Github user DaanHoogland commented on the pull request: https://github.com/apache/cloudstack/pull/1021#issuecomment-158374179 =20 code reviewed. LGTM awaiting tests. it is running on one of the INFRA-1= 0703 hosts but I know @abayer did some work on that issue... > listAPIs are taking too long to return results > ---------------------------------------------- > > Key: CLOUDSTACK-8485 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-848= 5 > Project: CloudStack > Issue Type: Bug > Security Level: Public(Anyone can view this level - this is the defa= ult.)=20 > Components: Management Server > Affects Versions: 4.5.1, 4.6.0 > Reporter: Sowmya Krishnan > Assignee: Koushik Das > Fix For: 4.6.0 > > > listAPIs are taking significantly longer than before (4.2.x) > I tried out few listAPI calls using a simulator set up with ~ 10K VMs and= 8K Routers. Here are few results: > listVirtualMachines is taking > 25 sec to return with pagesize set to 50.= This is in comparison to 2 sec in earlier cases such as 4.2. > listVolumes with pagesize =3D 1000 took more than 10 mins and finally tim= es out. > Further observations show that there are also lot of slow queries being l= ogged in catalina.out and in MySQL slow query logs. I am not sure if this c= ould be the reason for DB performance getting impacted in turn causing an i= mpact on listAPIs too. > Here's a sample of slow queries from catalina.out: > Mon May 11 07:31:15 UTC 2015 INFO: Profiler Event: [SLOW QUERY] a= t org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Delegati= ngPreparedStatement.java:96) duration: 3305 ms, connection-id: 637759, stat= ement-id: 3218312, resultset-id: 0, message: Slow query (exceeded 2,000 ms,= duration: 3,305 ms):SELECT user_vm_details.id, user_vm_details.vm_id, user= _vm_details.name, user_vm_details.value, user_vm_details.display FROM user_= vm_details WHERE user_vm_details.vm_id =3D 9117Mon May 11 07:31:15 UTC 2015= INFO: Profiler Event: [SLOW QUERY] at org.apache.commons.dbcp.Dele= gatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) d= uration: 3305 ms, connection-id: 637843, statement-id: 3218311, resultset-i= d: 0, message: Slow query (exceeded 2,000 ms, duration: 3,305 ms):SELECT ho= st.id, host.disconnected, host.name, host.status, host.type, host.private_i= p_address, host.private_mac_address, host.private_netmask, host.public_netm= ask, host.public_ip_address, host.public_mac_address, host.storage_ip_addre= ss, host.cluster_id, host.storage_netmask, host.storage_mac_address, host.s= torage_ip_address_2, host.storage_netmask_2, host.storage_mac_address_2, ho= st.hypervisor_type, host.proxy_port, host.resource, host.fs_type, host.avai= lable, host.setup, host.resource_state, host.hypervisor_version, host.updat= e_count, host.uuid, host.data_center_id, host.pod_id, host.cpu_sockets, hos= t.cpus, host.url, host.speed, host.ram, host.parent, host.guid, host.capabi= lities, host.total_size, host.last_ping, host.mgmt_server_id, host.dom0_mem= ory, host.version, host.created, host.removed FROM host WHERE host.id =3D 3= 45 AND host.removed IS NULL > Mon May 11 07:31:17 UTC 2015 INFO: Profiler Event: [SLOW QUERY] = at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Delegat= ingPreparedStatement.java:96) duration: 6458 ms, connection-id: 637623, sta= tement-id: 3218243, resultset-id: 0, message: Slow query (exceeded 2,000 ms= , duration: 6,458 ms):SELECT storage_pool_host_ref.host_id FROM storage_poo= l_host_ref INNER JOIN host ON storage_pool_host_ref.host_id=3Dhost.id WHER= E storage_pool_host_ref.pool_id =3D 197 AND (host.status =3D 'Up' AND ho= st.resource_state =3D 'Enabled' )Mon May 11 07:31:17 UTC 2015 INFO: Profile= r Event: [SLOW QUERY] at org.apache.commons.dbcp.DelegatingPrepared= Statement.executeQuery(DelegatingPreparedStatement.java:96) duration: 2402 = ms, connection-id: 637754, statement-id: 3218371, resultset-id: 0, message:= Slow query (exceeded 2,000 ms, duration: 2,402 ms):SELECT host.id, host.di= sconnected, host.name, host.status, host.type, host.private_ip_address, hos= t.private_mac_address, host.private_netmask, host.public_netmask, host.publ= ic_ip_address, host.public_mac_address, host.storage_ip_address, host.clust= er_id, host.storage_netmask, host.storage_mac_address, host.storage_ip_addr= ess_2, host.storage_netmask_2, host.storage_mac_address_2, host.hypervisor_= type, host.proxy_port, host.resource, host.fs_type, host.available, host.se= tup, host.resource_state, host.hypervisor_version, host.update_count, host.= uuid, host.data_center_id, host.pod_id, host.cpu_sockets, host.cpus, host.u= rl, host.speed, host.ram, host.parent, host.guid, host.capabilities, host.t= otal_size, host.last_ping, host.mgmt_server_id, host.dom0_memory, host.vers= ion, host.created, host.removed FROM host WHERE host.id =3D 669 AND host.r= emoved IS NULL > The following is from MySQL slow query log: > SELECT vm_instance.id, vm_instance.name, vm_instance.vnc_password, vm_ins= tance.proxy_id, vm_instance.proxy_assign_time, vm_instance.state, vm_instan= ce.private_ip_address, vm_instance.instance_name, vm_instance.vm_template_i= d, vm_instance.guest_os_id, vm_instance.host_id, vm_instance.last_host_id, = vm_instance.pod_id, vm_instance.private_mac_address, vm_instance.data_cente= r_id, vm_instance.vm_type, vm_instance.ha_enabled, vm_instance.display_vm, = vm_instance.limit_cpu_use, vm_instance.update_count, vm_instance.created, v= m_instance.removed, vm_instance.update_time, vm_instance.domain_id, vm_inst= ance.account_id, vm_instance.service_offering_id, vm_instance.reservation_i= d, vm_instance.hypervisor_type, vm_instance.dynamically_scalable, vm_instan= ce.uuid, vm_instance.disk_offering_id, vm_instance.power_state, vm_instance= .power_state_update_time, vm_instance.power_state_update_count, vm_instance= .power_host FROM vm_instance WHERE vm_instance.instance_name =3D _binary'r-= 16916-VM' AND vm_instance.removed IS NULL ORDER BY RAND() LIMIT 1;# User@= Host: cloud[cloud] @ x3 [10.81.28.128] Id: 637881# Query_time: 8.193784 L= ock_time: 0.000107 Rows_sent: 1 Rows_examined: 19935SET timestamp=3D143132= 9557; > SET timestamp=3D1431329601;SELECT host.id, host.disconnected, host.name, = host.status, host.type, host.private_ip_address, host.private_mac_address, = host.private_netmask, host.public_netmask, host.public_ip_address, host.pub= lic_mac_address, host.storage_ip_address, host.cluster_id, host.storage_net= mask, host.storage_mac_address, host.storage_ip_address_2, host.storage_net= mask_2, host.storage_mac_address_2, host.hypervisor_type, host.proxy_port, = host.resource, host.fs_type, host.available, host.setup, host.resource_stat= e, host.hypervisor_version, host.update_count, host.uuid, host.data_center_= id, host.pod_id, host.cpu_sockets, host.cpus, host.url, host.speed, host.ra= m, host.parent, host.guid, host.capabilities, host.total_size, host.last_pi= ng, host.mgmt_server_id, host.dom0_memory, host.version, host.created, host= .removed FROM host WHERE host.id =3D 913 AND host.removed IS NULL;# User@H= ost: cloud[cloud] @ x3 [10.81.28.128] Id: 637865# Query_time: 5.861241 Lo= ck_time: 0.000139 Rows_sent: 1 Rows_examined: 1 -- This message was sent by Atlassian JIRA (v6.3.4#6332)