Forum: Building VoltDB Applications

Post: overflowed output/network buffer of 10mb

overflowed output/network buffer of 10mb
etumbokon
Dec 13, 2010
Hi,
I'm getting the following error from results = voltclient.callProcedure("@AdHoc","SELECT * FROM <table name> LIMIT 1").getResults();

===============================================================================
VOLTDB ERROR: SQL ERROR
Output from SQL stmt overflowed output/network buffer of 10mb. Try a "limit" clause or a stronger predicate.
at org.voltdb.sysprocs.AdHoc.executePlanFragment(AdHoc.java:80)
at org.voltdb.sysprocs.AdHoc.run(AdHoc.java:162)
===============================================================================

The table has 32 columns with 1 column as VARCHAR(20480) and another as VARCHAR(2048) with the rest a combination of TINYINT, SMALLINT, TIMESTAMPTYPE, and more VARCHAR. The record is about 23K bytes long.

The above error only manifest when the total number of records in the table reaches about 79,000. Prior to 79K, everything works perfectly.
Is there a way to fix this short of restructuring the table?

Thanks,
Edgar
re: overflowed output/network buffer of 10mb
tcallaghan
Dec 14, 2010
Edgar,

One drawback to the @AdHoc system procedure is that the SQL is always executed as multi-partition. If the table you are selecting from is partitioned you should create a single-partition stored procedure to do this select.

If you need stored procedures that are just a single SQL statement you can create them in your project.xml file as follows:

Project Based Single-Partition Stored Procedure Example


<procedure class="procedures.simple.FetchReservations" partitioninfo="Reservation.flightid:0">
<sql>SELECT * FROM RESERVATION WHERE FLIGHTID=?</sql>
</procedure>


Project Based Multi-Partition Example


<procedure class="procedures.simple.MyReservationsByTrip">
<sql>SELECT R.RESERVEID, F.FLIGHTID, F.DEPARTTIME
FROM RESERVATION AS R, FLIGHT AS F
WHERE R.CUSTOMERID = ?
AND R.FLIGHTID = F.FLIGHTID
AND F.ORIGIN=? AND F.DESTINATION=?
</sql>
</procedure>


More information is available in our documentation at DesignProc#DesignCodeFreeProcs

-Tim
Hi, The use of @AdHoc was for
etumbokon
Dec 14, 2010
Edgar,

One drawback to the @AdHoc system procedure is that the SQL is always executed as multi-partition. If the table you are selecting from is partitioned you should create a single-partition stored procedure to do this select...

-Tim


Hi,

The use of @AdHoc was for illustration purpose only. I do have a stored procedure where I first encountered the error. I used @AdHoc to validate the error I encountered and for posting here.

I did more tests and it looks like the number of columns in the result set is what is triggering the error. Is there a maximum number of columns limitation in the result set currently?

Thanks,
Edgar
re: overflowed output/network buffer of 10mb
tcallaghan
Dec 14, 2010
Hi,

The use of @AdHoc was for illustration purpose only. I do have a stored procedure where I first encountered the error. I used @AdHoc to validate the error I encountered and for posting here.

I did more tests and it looks like the number of columns in the result set is what is triggering the error. Is there a maximum number of columns limitation in the result set currently?

Thanks,
Edgar


Edgar,

Is your stored procedure single-partition or multi-partition?

If it is multi-partition you'll need to perform some filtering within the partitions via a SQL where clause. VoltDB does not currently perform the LIMIT operation at the remote partitions so a significant amount of data is passed between partitions (the amount depends on the number of rows in your table and the number of columns in the select statement).

-Tim
Hi, Yes, it's
etumbokon
Dec 14, 2010
Edgar,

Is your stored procedure single-partition or multi-partition?

If it is multi-partition you'll need to perform some filtering within the partitions via a SQL where clause. VoltDB does not currently perform the LIMIT operation at the remote partitions so a significant amount of data is passed between partitions (the amount depends on the number of rows in your table and the number of columns in the select statement).

-Tim


Hi,

Yes, it's multi-partition.

Thanks,
Edgar