cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-8300) Add index on archived field in cloud.event table
Date Sun, 31 Jan 2016 18:00:41 GMT

    [ 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 <github@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)

Mime
View raw message