You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@cloudstack.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2016/01/30 16:54:39 UTC
[jira] [Commented] (CLOUDSTACK-8300) Add index on archived field in
cloud.event table
[ https://issues.apache.org/jira/browse/CLOUDSTACK-8300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15124939#comment-15124939 ]
ASF GitHub Bot commented on CLOUDSTACK-8300:
--------------------------------------------
GitHub user remibergsma opened a pull request:
https://github.com/apache/cloudstack/pull/1387
CLOUDSTACK-8300: Set indexes on event table
When there are many events in the cloud.event table, the UI throws an SQL exception and the management server spikes at 100% CPU for minutes. That causes other API calls to fail with 530 errors.
It seems an index on the 'archived' field is missing (since it is used in the WHERE clause).
We have 1.4M events in the table:
`select count(*) from event;`
1497838
This PR adds the index to the `archived` field and the `state` field.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/remibergsma/cloudstack CLOUDSTACK-8300
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/cloudstack/pull/1387.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1387
----
commit 14c837ab8329f98b3fe071dd69807dcae77f4f7c
Author: Remi Bergsma <gi...@remi.nl>
Date: 2016-01-30T15:50:46Z
CLOUDSTACK-8300: Set indexes on event table
----
> Add index on archived field in cloud.event table
> ------------------------------------------------
>
> Key: CLOUDSTACK-8300
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-8300
> Project: CloudStack
> Issue Type: Bug
> Security Level: Public(Anyone can view this level - this is the default.)
> Affects Versions: 4.4.2
> Reporter: Remi Bergsma
> Priority: Minor
>
> When there are many events in the cloud.event table, the UI throws an SQL exception and the management server spikes at 100% CPU for minutes. That causes other API calls to fail with 430 errors.
> Studying the error below, it seems an index on the 'archived' field is missing (since it is used in the WHERE clause).
> We have 1.4M events in the table:
> select count(*) from event;
> 1497838
> Solution:
> Please add the index to the archived field and consider doing the same for the state field.
> Work around:
> Delete events manually
> delete from event where state = "Completed";
> Since state also does not have an index, this takes some time.
> Error logged:
> 2015-03-04 14:19:28,429 ERROR [c.c.a.ApiServer] (TP-Processor50:ctx-3116c380 ctx-c861804c) unhandled exception executing api command: [Ljava.lang.String;@b96205f
> com.cloud.utils.exception.CloudRuntimeException: DB Exception on: com.mysql.jdbc.JDBC4PreparedStatement@17f33b92: SELECT event_view.id, event_view.uuid, event_view.type, event_view.state, event_view.description, event_view.created, event_view.user_id, event_view.user_name, event_view.l
> evel, event_view.start_id, event_view.start_uuid, event_view.parameters, event_view.account_id, event_view.account_uuid, event_view.account_name, event_view.account_type, event_view.domain_id, event_view.domain_uuid, event_view.domain_name, event_view.domain_path, event_view.project_id
> , event_view.project_uuid, event_view.project_name, event_view.archived, event_view.display FROM event_view WHERE event_view.account_type != 5 AND event_view.archived = 0 ORDER BY event_view.created DESC LIMIT 0, 20
> at com.cloud.utils.db.GenericDaoBase.searchIncludingRemoved(GenericDaoBase.java:425)
> at com.cloud.utils.db.GenericDaoBase.searchIncludingRemoved(GenericDaoBase.java:361)
> at com.cloud.utils.db.GenericDaoBase.search(GenericDaoBase.java:345)
> at com.cloud.utils.db.GenericDaoBase.searchAndCount(GenericDaoBase.java:1296)
> at sun.reflect.GeneratedMethodAccessor221.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
> at com.cloud.utils.db.TransactionContextInterceptor.invoke(TransactionContextInterceptor.java:34)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
> at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
> at com.sun.proxy.$Proxy268.searchAndCount(Unknown Source)
> at com.cloud.api.query.QueryManagerImpl.searchForEventsInternal(QueryManagerImpl.java:583)
> at com.cloud.api.query.QueryManagerImpl.searchForEvents(QueryManagerImpl.java:472)
> at org.apache.cloudstack.api.command.user.event.ListEventsCmd.execute(ListEventsCmd.java:112)
> at com.cloud.api.ApiDispatcher.dispatch(ApiDispatcher.java:141)
> at com.cloud.api.ApiServer.queueCommand(ApiServer.java:682)
> at com.cloud.api.ApiServer.handleRequest(ApiServer.java:511)
> at com.cloud.api.ApiServlet.processRequestInContext(ApiServlet.java:330)
> at com.cloud.api.ApiServlet.access$000(ApiServlet.java:54)
> at com.cloud.api.ApiServlet$1.run(ApiServlet.java:118)
> at org.apache.cloudstack.managed.context.impl.DefaultManagedContext$1.call(DefaultManagedContext.java:56)
> at org.apache.cloudstack.managed.context.impl.DefaultManagedContext.callWithContext(DefaultManagedContext.java:103)
> at org.apache.cloudstack.managed.context.impl.DefaultManagedContext.runWithContext(DefaultManagedContext.java:53)
> at com.cloud.api.ApiServlet.processRequest(ApiServlet.java:115)
> at com.cloud.api.ApiServlet.doGet(ApiServlet.java:77)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
> at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
> at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)