1 Reply Latest reply on Oct 9, 2019 11:06 AM by Heyward Drummond

    Server environment slow due to bad Tableau db index

    Toby Erkson

      Tableau Server 2018.1.12 on Windows 2008 (primary) and Windows 2012 (secondary).

      Yesterday our Tableau Server environment suddenly slowed way down, even logging on and interacting via remote desktop was very slow and I started to get complaints from end users.  It was actually my VizAlerts process that was my notification as I kept getting the error message "ALERT: VizAlerts failed to execute properly (PROD)" (so thank you Matt Coles, another useful side-effect for VA ).  Nothing had changed in the environment -- VM server, Tableau software, user load, etc. -- for several months so what was going on?!

       

      I emptied the Recycle Bin from all accounts that could access the Tableau Server machines.  I performed a Disk Cleanup for the 2008 server to give it more space (which it did...by a lot!).  The 2012 server is automatically cleaned up thru the VM software so those drives were OK.  The drives didn't appear to be over-used so it didn't look like a pagefile.sys thrashing issue.  Still, performance was terrible.  Memory wasn't pegged (less than 3/4 of it being used) so there was no need to add more.

       

      I contacted our VM server team and Mike was the one helping me.  He came across this error that exploded into the Windows event logs on Oct. 2nd and was filling it up:

      2019-10-02 10:13:58.612 -0700 catalina-exec-327 Default SVERNEK ERROR requestId=XZTa1maO02MvBSKTxPQk7AAAAv0             
      localRequestId=0:ddcea7c:16d8d65d94b:-5d00: wgsessionId=YxGd8YoZQI2QKtF4osv3PQ  serviceName=vizportal com.tableausoftware.api.rest.util.RestApiControllerAdvice  - com.tableausoftware.api.rest.exceptions.BadRequestRestException: The start index '400' is greater than or equal to the total count.
      com.tableausoftware.api.rest.exceptions.BadRequestRestException: The start index '400' is greater than or equal to the total count.        
      at com.tableausoftware.api.rest.util.RestApiUtils.verifyPagingParamsIsInBound(RestApiUtils.java:180)            
      at com.tableausoftware.api.rest.impl.RestApiAppService.getProjects(RestApiAppService.java:2006)   
      at com.tableausoftware.api.rest.RestApiController.getProjects_aroundBody98(RestApiController.java:1039)      
      at com.tableausoftware.api.rest.RestApiController$AjcClosure99.run(RestApiController.java:1)             
      at org.aspectj.runtime.reflect.JoinPointImpl.proceed(JoinPointImpl.java:149)   
      at com.tableausoftware.instrumentation.InstrumentedMethod.instrumentInvocation(InstrumentedMethod.java:66)      
      at com.tableausoftware.instrumentation.InstrumentationAspect.aroundAnnotatedMethod(InstrumentationAspect.java:57)            
      at com.tableausoftware.api.rest.RestApiController.getProjects(RestApiController.java:1039)  
      at sun.reflect.GeneratedMethodAccessor852.invoke(Unknown Source)          
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)            
      at java.lang.reflect.Method.invoke(Method.java:498) 
      at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)   
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)            
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)            
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)            
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)            
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)            
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)          
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)    
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)      
      at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)        
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)  
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)       
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)  
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)       
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)      
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)       
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)       
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)      
      at com.tableausoftware.core.controller.RelativeRedirectFilter.doFilter(RelativeRedirectFilter.java:62)      
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)       
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)      
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)    
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)          
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)       
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)      
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)   
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)       
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)     
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)    
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)       
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)          
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)       
      at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1137)     
      at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)            
      at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2555)     
      at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2544)         
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)             
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)             
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)      
      at java.lang.Thread.run(Thread.java:748) 
      

      It was the second and third lines from above that caught my attention, "The start index '400' is greater than or equal to the total count."  Now the value (400) wasn't important but the fact that the index was messed up was important.  I did scan the rest of the error but it's all gobbledygook to me and really gave me nothing else of value (I'm not a server expert).  Given the error message I decided to execute a reindex from the All Sites >> Status page:

      The rebuild took around 30 minutes...it was pretty slow!  However, after it completed the performance of the whole server environment was back to its normal, speedy self

       

      Should a reindex be part of a routine?  Monthly?  Quarterly?  I don't know as I haven't had the need to do this.  Plus, we are upgrading our environment to Windows Server 2016 and Tableau Server 2019.3.x so I can't say if the new version has updates within it that fix this situation. This is something to be aware of, something to check, in case the Tableau environment suddenly slows down.  I see no downside to a routine reindex if one chooses to do that.

       

      For 2018.TSM and newer versions [like 2019.x] it looks like the tsm command to use is "tsm maintenance reindex-search".