SQL select out of memory

Hi,

I have a simple a sql query like the one below running against a data source with approximately 650k rows and 50 columns, the csv I used to index the data source is around 300Mb on disk

select * from datasource

I need to this as I need to provide a download functionality to my users (data is saved to csv and returned to the browser), I have the same error if I run the query from a client like RazorSQL.

2018-02-15 14:58:01 [ERROR] o.a.c.c.C.[.[.[.[dispatcherServlet]:[log] - Servlet.service() for servlet [dispatcherServlet] in context with path [/xxxxxx] threw exception [Request processing failed; nested exception is java.sql.SQLException: Error while executing SQL “select * from datasource”: org.apache.calcite.avatica.com.fasterxml.jackson.core.JsonParseException: Unexpected character (’<’ (code 60)): expected a valid value (number, String, array, object, ‘true’, ‘false’ or ‘null’)

at [Source:

Error 500

HTTP ERROR: 500

Problem accessing /druid/v2/sql/avatica/. Reason:

    java.lang.OutOfMemoryError: Java heap space

Powered by Jetty:// 9.3.19.v20170502

; line: 1, column: 2]] with root cause

org.apache.calcite.avatica.com.fasterxml.jackson.core.JsonParseException: Unexpected character (’<’ (code 60)): expected a valid value (number, String, array, object, ‘true’, ‘false’ or ‘null’)

at [Source:

Error 500

HTTP ERROR: 500

Problem accessing /druid/v2/sql/avatica/. Reason:

    java.lang.OutOfMemoryError: Java heap space

Powered by Jetty:// 9.3.19.v20170502

; line: 1, column: 2]

at org.apache.calcite.avatica.com.fasterxml.jackson.core.JsonParser._constructError(JsonParser.java:1581)

at org.apache.calcite.avatica.com.fasterxml.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:533)

at org.apache.calcite.avatica.com.fasterxml.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:462)

at org.apache.calcite.avatica.com.fasterxml.jackson.core.json.ReaderBasedJsonParser._handleOddValue(ReaderBasedJsonParser.java:1624)

at org.apache.calcite.avatica.com.fasterxml.jackson.core.json.ReaderBasedJsonParser.nextToken(ReaderBasedJsonParser.java:689)

at org.apache.calcite.avatica.com.fasterxml.jackson.databind.ObjectMapper._initForReading(ObjectMapper.java:3776)

at org.apache.calcite.avatica.com.fasterxml.jackson.databind.ObjectMapper._readMapAndClose(ObjectMapper.java:3721)

at org.apache.calcite.avatica.com.fasterxml.jackson.databind.ObjectMapper.readValue(ObjectMapper.java:2726)

at org.apache.calcite.avatica.remote.JsonService.decode(JsonService.java:53)

at org.apache.calcite.avatica.remote.JsonService.apply(JsonService.java:132)

at org.apache.calcite.avatica.remote.RemoteMeta$13.call(RemoteMeta.java:270)

at org.apache.calcite.avatica.remote.RemoteMeta$13.call(RemoteMeta.java:262)

at org.apache.calcite.avatica.AvaticaConnection.invokeWithRetries(AvaticaConnection.java:756)

at org.apache.calcite.avatica.remote.RemoteMeta.prepareAndExecute(RemoteMeta.java:261)

at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)

at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)

at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)

``

I have set very high values of memory available to both the broker and the historical nodes

Broker

-server

-Xms1g

-Xmx4g

-XX:MaxDirectMemorySize=8g

Historical

-server

-Xms1g

-Xmx2g

-XX:MaxDirectMemorySize=8g

I have also set these properties for the broker

druid.sql.planner.maxQueryCount = 0

druid.sql.planner.maxSemiJoinRowsInMemory = 500000

druid.sql.planner.selectPageSize = 500000

druid.sql.avatica.maxRowsPerFrame = -1

What can I do to fix this issue?

Thanks for any help you might provide

Hi Frankie,

What version are you running? In Druid 0.11.0 we changed a SQL “select * from x” to use a scan query (http://druid.io/docs/latest/querying/scan-query.html) rather than select query. It has better memory usage behavior and will not cause OOM on historicals. Although, it is still not perfect (see https://github.com/druid-io/druid/issues/4933) and can still cause OOM on brokers in some cases. I would say give 0.11.0 a try if you haven’t yet and see if it works for you.

Hi, I am using 0.11.0. I have tried scan with a JSON query over http and it works ok, I have also tried sql in JSON over http and it works fine too, it’s just the “direct” sql query that generates the oom error. I can work around the problem using sql in JSON over http, but it is not ideal as it forces my application to use a hybrid approach.

Thanks as always for your help.

By “direct” SQL query do you mean JDBC? If so, interesting that you only have an issue when going through JDBC. Would you mind adding -XX:+HeapDumpOnOutOfMemoryError, collecting the heap dump and examining it for clues? A heap analysis tool should show you what classes of objects are taking up what % of space, and that might give a clue as to how to resolve it.

Hi, yes I meant JDBC.

I have used Eclipse Memory Analyzer to analyze the memory dump of the query performed with RazorSQL (select * from datasource).

There is clearly one thread that’s using all memory, the report says

The thread java.lang.Thread @ 0x6c0d67980 qtp823263265-125 keeps local variables with total size 2,347,939,008 (99.46%) bytes.

The memory is accumulated in one instance of “java.lang.Object” loaded by “”.

The stacktrace of this Thread is available. See stacktrace.

``

The stack strace is below, please let me know if you need any more information. Thank You,

qtp823263265-125

at java.lang.OutOfMemoryError.()V (OutOfMemoryError.java:48)

at java.util.Arrays.copyOf([CI)[C (Arrays.java:3332)

at java.lang.AbstractStringBuilder.ensureCapacityInternal(I)V (AbstractStringBuilder.java:124)

at java.lang.AbstractStringBuilder.append([CII)Ljava/lang/AbstractStringBuilder; (AbstractStringBuilder.java:596)

at java.lang.StringBuffer.append([CII)Ljava/lang/StringBuffer; (StringBuffer.java:367)

at java.io.StringWriter.write([CII)V (StringWriter.java:94)

at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator._flushBuffer()V (WriterBasedJsonGenerator.java:1880)

at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator.writeString(Ljava/lang/String;)V (WriterBasedJsonGenerator.java:334)

at com.fasterxml.jackson.databind.ser.std.StringSerializer.serialize(Ljava/lang/String;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (StringSerializer.java:37)

at com.fasterxml.jackson.databind.ser.std.StringSerializer.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (StringSerializer.java:21)

at com.fasterxml.jackson.databind.ser.std.ObjectArraySerializer.serializeContents([Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (ObjectArraySerializer.java:228)

at com.fasterxml.jackson.databind.ser.std.ObjectArraySerializer.serializeContents(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (ObjectArraySerializer.java:25)

at com.fasterxml.jackson.databind.ser.std.ArraySerializerBase.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (ArraySerializerBase.java:57)

at com.fasterxml.jackson.databind.ser.impl.IndexedListSerializer.serializeContents(Ljava/util/List;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (IndexedListSerializer.java:100)

at com.fasterxml.jackson.databind.ser.impl.IndexedListSerializer.serializeContents(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (IndexedListSerializer.java:21)

at com.fasterxml.jackson.databind.ser.std.AsArraySerializerBase.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (AsArraySerializerBase.java:183)

at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanPropertyWriter.java:505)

at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanSerializerBase.java:639)

at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanSerializer.java:152)

at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanPropertyWriter.java:505)

at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanSerializerBase.java:639)

at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeWithType(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;Lcom/fasterxml/jackson/databind/jsontype/TypeSerializer;)V (BeanSerializerBase.java:525)

at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serializeContents(Ljava/util/Collection;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (CollectionSerializer.java:119)

at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serializeContents(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (CollectionSerializer.java:23)

at com.fasterxml.jackson.databind.ser.std.AsArraySerializerBase.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (AsArraySerializerBase.java:183)

at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanPropertyWriter.java:505)

at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (BeanSerializerBase.java:639)

at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeWithType(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;Lcom/fasterxml/jackson/databind/jsontype/TypeSerializer;)V (BeanSerializerBase.java:525)

at com.fasterxml.jackson.databind.ser.impl.TypeWrappedSerializer.serialize(Ljava/lang/Object;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V (TypeWrappedSerializer.java:35)

at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(Lcom/fasterxml/jackson/core/JsonGenerator;Ljava/lang/Object;)V (DefaultSerializerProvider.java:128)

at com.fasterxml.jackson.databind.ObjectMapper._configAndWriteValue(Lcom/fasterxml/jackson/core/JsonGenerator;Ljava/lang/Object;)V (ObjectMapper.java:2881)

at com.fasterxml.jackson.databind.ObjectMapper.writeValue(Ljava/io/Writer;Ljava/lang/Object;)V (ObjectMapper.java:2320)

at org.apache.calcite.avatica.remote.JsonHandler.encode(Lorg/apache/calcite/avatica/remote/Service$Response;)Ljava/lang/String; (JsonHandler.java:70)

at org.apache.calcite.avatica.remote.JsonHandler.encode(Lorg/apache/calcite/avatica/remote/Service$Response;)Ljava/lang/Object; (JsonHandler.java:37)

at org.apache.calcite.avatica.remote.AbstractHandler.apply(Ljava/lang/Object;)Lorg/apache/calcite/avatica/remote/Handler$HandlerResponse; (AbstractHandler.java:96)

at org.apache.calcite.avatica.remote.JsonHandler.apply(Ljava/lang/String;)Lorg/apache/calcite/avatica/remote/Handler$HandlerResponse; (JsonHandler.java:52)

at org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(Ljava/lang/String;Lorg/eclipse/jetty/server/Request;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V (AvaticaJsonHandler.java:129)

at io.druid.sql.avatica.DruidAvaticaHandler.handle(Ljava/lang/String;Lorg/eclipse/jetty/server/Request;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V (DruidAvaticaHandler.java:60)

at org.eclipse.jetty.server.handler.HandlerList.handle(Ljava/lang/String;Lorg/eclipse/jetty/server/Request;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V (HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(Ljava/lang/String;Lorg/eclipse/jetty/server/Request;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V (HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Lorg/eclipse/jetty/server/HttpChannel;)V (Server.java:534)

at org.eclipse.jetty.server.HttpChannel.handle()Z (HttpChannel.java:320)

at org.eclipse.jetty.server.HttpConnection.onFillable()V (HttpConnection.java:251)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded()V (AbstractConnection.java:283)

at org.eclipse.jetty.io.FillInterest.fillable()V (FillInterest.java:108)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run()V (SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume()V (ExecuteProduceConsume.java:303)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume()V (ExecuteProduceConsume.java:148)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run()V (ExecuteProduceConsume.java:136)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(Ljava/lang/Runnable;)V (QueuedThreadPool.java:671)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run()V (QueuedThreadPool.java:589)

at java.lang.Thread.run()V (Thread.java:748)

``

Hi Frankie,

How big are your rows?

If you set druid.sql.avatica.maxRowsPerFrame = 1000 in your properties does that help? (The default is 100000)

Hi, thanks! that fixed the OOM problem :slight_smile:

I’ll do some tuning to see what the upper limit is before OOM.

Thanks again, I was going through hell with json queries as the post processing was very slow. Thanks!

Hey Frankie,

Great to hear!! Now that I think about it, the 100000 limit is a bit excessive. I raised a patch to lower it to 5000, which seems more reasonable to me: https://github.com/druid-io/druid/pull/5409. Please review the patch if you can.

Hi, reviewed and “approved”.

Thanks for all your help.

Cheers