Remi Bergsma created CLOUDSTACK-8300:
----------------------------------------
Summary: 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)
