[
https://issues.apache.org/jira/browse/CLOUDSTACK-8300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15125433#comment-15125433
]
ASF subversion and git services commented on CLOUDSTACK-8300:
-------------------------------------------------------------
Commit 476e9f02106de945746e6f5a6e1cd716d6d3c010 in cloudstack's branch
refs/heads/master from [~remibergsma]
[ https://git-wip-us.apache.org/repos/asf?p=cloudstack.git;h=476e9f0 ]
Merge pull request #1387 from remibergsma/CLOUDSTACK-8300
CLOUDSTACK-8300: Set indexes on event tableWhen 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.
* pr/1387:
CLOUDSTACK-8300: Set indexes on event table
Signed-off-by: Remi Bergsma <git...@remi.nl>
> 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)
