Smarthome: [Persistence REST API] Java heap space OutOfMemoryError when querying more than 200.000 datapoints

Created on 3 Feb 2017 · 10Comments · Source: eclipse-archived/smarthome

Hi guys!

I have being testing openHAB2.0 in a Rasperry Pi 3 Model B for the last few days using the Z-Wave binding and things have worked really great, congratulations to the whole team!

I'm using this device to register the voltage levels at home and, because the device sends the data very frecuently, in a week I have collected more than 200.000 datapoints in the mySQL database. First I tried to graph the data through HABmin, but I couldn't see any value, so I decided to query the REST API directly and have a look at the log files. This is the HTTP GET command I'm using to call the persistence endpoint:

http://openhabianpi.local:8080/rest/persistence/items/fridge_voltage?endtime=2017-01-31T07:49:05.307Z&serviceId=mysql&starttime=2017-01-20T00:49:05.307Z

The result is a HTTP ERROR 500, Problem accessing /rest/persistence/items/fridge_voltage. Reason:

Request failed.

And here you have the relevant part of the log file:

2017-02-03 08:37:41.797 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: query:SELECT Time, Value FROM Item1 WHERE TIME>'2017-01-20 01:49:05' AND TIME<'2017-01-31 08:49:05' ORDER BY Time ASC 2017-02-03 08:37:48.609 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: query returned 231948 rows in 6813ms 2017-02-03 08:38:10.231 [WARN ] [eclipse.jetty.servlet.ServletHandler] - javax.servlet.ServletException: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:489)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at com.eclipsesource.jaxrs.publisher.internal.ServletContainerBridge.service(ServletContainerBridge.java:76)[10:com.eclipsesource.jaxrs.publisher:5.3.1.201602281253] at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)[79:org.eclipse.jetty.security:9.2.19.v20160908] at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:287)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.Server.handle(Server.java:499)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)[72:org.eclipse.jetty.io:9.2.19.v20160908] at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)[83:org.eclipse.jetty.util:9.2.19.v20160908] at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)[83:org.eclipse.jetty.util:9.2.19.v20160908] at java.lang.Thread.run(Thread.java:745)[:1.8.0_111] Caused by: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space at org.glassfish.jersey.servlet.internal.ResponseWriter.rethrow(ResponseWriter.java:278)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.internal.ResponseWriter.failure(ResponseWriter.java:260)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.server.ServerRuntime$Responder.process(ServerRuntime.java:509)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:334)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:315)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:297)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:267)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] ... 26 more Caused by: java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOf(Arrays.java:3332)[:1.8.0_111] at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124)[:1.8.0_111] at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448)[:1.8.0_111] at java.lang.StringBuffer.append(StringBuffer.java:270)[:1.8.0_111] at java.io.StringWriter.write(StringWriter.java:112)[:1.8.0_111] at com.google.gson.stream.JsonWriter.string(JsonWriter.java:559) at com.google.gson.stream.JsonWriter.value(JsonWriter.java:419) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:690) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:704) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:696) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:704) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:642) at com.google.gson.internal.Streams.write(Streams.java:68) at com.google.gson.Gson.toJson(Gson.java:668) at com.google.gson.Gson.toJson(Gson.java:634) at com.google.gson.Gson.toJson(Gson.java:619) at org.eclipse.smarthome.io.rest.JSONResponse.createResponse(JSONResponse.java:138) at org.eclipse.smarthome.io.rest.core.persistence.PersistenceResource.getItemHistoryDTO(PersistenceResource.java:314) at org.eclipse.smarthome.io.rest.core.persistence.PersistenceResource.httpGetPersistenceItemData(PersistenceResource.java:148) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_111] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_111] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_111] at java.lang.reflect.Method.invoke(Method.java:498)[:1.8.0_111] at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161) at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99) at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102) at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)[158:org.glassfish.jersey.core.jersey-server:2.22.2] 2017-02-03 08:38:10.325 [WARN ] [org.eclipse.jetty.server.HttpChannel] - /rest/persistence/items/tension_frigorifico?endtime=2017-01-31T07:49:05.307Z&serviceId=mysql&starttime=2017-01-20T00:49:05.307Z javax.servlet.ServletException: javax.servlet.ServletException: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:88)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.Server.handle(Server.java:499)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)[72:org.eclipse.jetty.io:9.2.19.v20160908] at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)[83:org.eclipse.jetty.util:9.2.19.v20160908] at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)[83:org.eclipse.jetty.util:9.2.19.v20160908] at java.lang.Thread.run(Thread.java:745)[:1.8.0_111] Caused by: javax.servlet.ServletException: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:489)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at com.eclipsesource.jaxrs.publisher.internal.ServletContainerBridge.service(ServletContainerBridge.java:76)[10:com.eclipsesource.jaxrs.publisher:5.3.1.201602281253] at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)[79:org.eclipse.jetty.security:9.2.19.v20160908] at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:287)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)[81:org.eclipse.jetty.servlet:9.2.19.v20160908] at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)[80:org.eclipse.jetty.server:9.2.19.v20160908] at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80)[172:org.ops4j.pax.web.pax-web-jetty:4.3.0] ... 8 more Caused by: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space at org.glassfish.jersey.servlet.internal.ResponseWriter.rethrow(ResponseWriter.java:278)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.servlet.internal.ResponseWriter.failure(ResponseWriter.java:260)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] at org.glassfish.jersey.server.ServerRuntime$Responder.process(ServerRuntime.java:509)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:334)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:315)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:297)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.internal.Errors.process(Errors.java:267)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)[157:org.glassfish.jersey.core.jersey-common:2.22.2] at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)[158:org.glassfish.jersey.core.jersey-server:2.22.2] at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)[155:org.glassfish.jersey.containers.jersey-container-servlet-core:2.22.2] ... 26 more Caused by: java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOf(Arrays.java:3332)[:1.8.0_111] at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124)[:1.8.0_111] at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448)[:1.8.0_111] at java.lang.StringBuffer.append(StringBuffer.java:270)[:1.8.0_111] at java.io.StringWriter.write(StringWriter.java:112)[:1.8.0_111] at com.google.gson.stream.JsonWriter.string(JsonWriter.java:559) at com.google.gson.stream.JsonWriter.value(JsonWriter.java:419) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:690) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:704) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:696) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:704) at com.google.gson.internal.bind.TypeAdapters$25.write(TypeAdapters.java:642) at com.google.gson.internal.Streams.write(Streams.java:68) at com.google.gson.Gson.toJson(Gson.java:668) at com.google.gson.Gson.toJson(Gson.java:634) at com.google.gson.Gson.toJson(Gson.java:619) at org.eclipse.smarthome.io.rest.JSONResponse.createResponse(JSONResponse.java:138) at org.eclipse.smarthome.io.rest.core.persistence.PersistenceResource.getItemHistoryDTO(PersistenceResource.java:314) at org.eclipse.smarthome.io.rest.core.persistence.PersistenceResource.httpGetPersistenceItemData(PersistenceResource.java:148) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_111] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)[:1.8.0_111] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)[:1.8.0_111] at java.lang.reflect.Method.invoke(Method.java:498)[:1.8.0_111] at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161) at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99) at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102) at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326) 2017-02-03 08:38:10.369 [WARN ] [org.eclipse.jetty.server.HttpChannel] - Could not send response error 500: javax.servlet.ServletException: javax.servlet.ServletException: org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space
I would expect the Raspberry Pi 3 to be powerfull enough to return 200.000 value through the persistence service, am I wrong?

Many thanks for all your help and great work, if you need more information don't hesitate to ask!

Best regards,

Aitor

Persistence

Source

bodiroga

Most helpful comment

I´m about to push a PR for this: Right now the complete collection from the storage will be serialised to JSON and held as a separate String in memory. This can get really large as your observations show. My solution is to directly stream during JSON serialisation which will reduce memory usage significantly for large collections. In addition I´m trying to build something similar for the other REST APIs. The /rest/thing-types resource has a similar issue when large thing type descriptions are loaded. stay tuned.

htreu on 22 May 2017

👍2

All 10 comments

Hi @bodiroga! I had raised such a use case as a concern when the REST service was implemented, but the outcome of the discussion was that the user should rather look into optimizing his persistence strategies, e.g. using an rrd database for cases, where high volume data is to be visualised for a longer time span.

kaikreuzer on 15 Feb 2017

Hi Kai!

Thanks for your answer! It's a pleasure to talk to you!

I understand that the user should optimize his persistence strategy and that more than 200.000 values is an enormous amount of data for a week, but our use case was to record the voltage level at home during a week because we suspect that our electricity provider was not giving us enough voltage (less than 213V).

Anyway, shouldn't the Raspberry Pi (or openHAB2.0, I haven't test the same amount of data in a more powerful system) be able to return that amount of data through the REST API? Is 200.000 above the "logical" limit? I would expect to be able to use the REST API to download my home temperature (or power consumption) data of the whole year to a csv for further analysis, but with this memory restriction we won't be able to achieve it. Isn't this a valid user case?

By the way, reading again the topic you linked, I have seen the "paging" option for the REST call, so that must be enough to resolve our issue, great!

Is there any plan to support more complex querys through the REST API? Things like MAX, MIN, AVG,... values or querying data being grouped by time intervals (5 minutes, 1 hour, 1 day, 1 week,...) would allow building richer graphing system. Or is something like Grafana+Influxdb the preferred way? Despite Grafana being a really cool tool, having something builtin ESH would be awesome!

Many thanks again for all your hard work and best regards,

Aitor

bodiroga on 15 Feb 2017

Is 200.000 above the "logical" limit?

I would say yes, the persistence service API has not been built for that - note that values are not streamed from the DB directly to the HTTP response, but all are kept in memory (as a Java object structure, which can consume some memory).

I have seen the "paging" option for the REST call, so that must be enough to resolve our issue, great!

I doubt that this will solve your issue, because paging is only done for the REST API, while the runtime always request all data from the db (as there is no paging on persistence services).

Is there any plan to support more complex querys through the REST API?

The "plans" are what people want to contribute. I am not aware of anybody working on such topics, but if this is important to you, feel free to suggest a way to implement it and come up with a PR for it!
My fear is that it can easily become very complex, especially as you will make it hard to implement the persistence services accordingly. That's why the current approach isn't that bad: Have a simple API that covers 80% of the use cases as a generic concept in ESH, and allow people to do specific powerful solutions (like grafana), if they really have the need for them.

kaikreuzer on 16 Feb 2017

note that values are not streamed from the DB directly to the HTTP response, but all are kept in memory (as a Java object structure, which can consume some memory).

Is this a technical limitation or would it be possible to stream it instead?

shoeper on 16 Feb 2017

As explained above, the persistence service API (and thus the service implementations) does not allow any streaming.

kaikreuzer on 16 Feb 2017

I doubt that this will solve your issue, because paging is only done for the REST API, while the runtime always request all data from the db (as there is no paging on persistence services).

Then perhaps there is a bug in the way the persistence service return the data through the REST API, because the paging "workaround" has worked perfectly fine here. I have made a python script that requests the data using paging with 100.000 datapoints per page and the data has been downloaded correctly. Here you have the output of my script:

"239041 datapoints downloaded in 32.8137860298 seconds."

It's not very fast, but after three iterations (page 0, page 1 and page 2), all the data has been saved to disk in a .csv file (6.2M).

Having a look closely to the reported log snippet, the root cause of the OutOfMemoryError seems to be the "java.util.Arrays.copyOf(Arrays.java:3332)" call, as the API is duplicating all the data twice and thus overflowing the available memory. I won't be able to investigate the issue further, so for now, if the paging workaround works file, it's ok. I don't know if @cdjackson has something to tell about the implemented code and if something can be done to reduce the memory footprint.

Many thanks again for all the amazing job and best regards,

Aitor

bodiroga on 16 Feb 2017

As explained above, the persistence service API (and thus the service implementations) does not allow any streaming.

Having a look at the behaviour @bodiroga reported, it could be a solution to always query the next 10000 (up to) entries from the persistence and stream the response to the user.

shoeper on 16 Feb 2017

htreu on 22 May 2017

👍2

Hi @bodiroga and @shoeper: It would be awesome to get some feedback if you could test with the results from the PR #3500. I only did rudimentary memory tests with VisualVM but not on a restricted device. In case of the ~200k data points from MySQL the result was a decrease of ~200MB in memory usage.

htreu on 22 May 2017

👍1

Fixed by #3500.

kaikreuzer on 16 Jun 2017

Was this page helpful?

0 / 5 - 0 ratings