Forum: Managing VoltDB

Post: How to resolve exception : Ad Hoc Planner task queue is full. Try again.

How to resolve exception : Ad Hoc Planner task queue is full. Try again.
pavangadiya
Jun 10, 2015
Hello all,

I am trying to execute some test with VoltDB and I am getting this exception.

Ad Hoc Planner task queue is full. Try again.

I think the SQL planner becomes full and starting to reject incoming operations.

I have to somehow control the transaction rate in my client application. But, How can I do that ?

I am using Voltdb API with Java in client application. The client consist a cluster of 2 nodes and I am using Adhoc sql to interact with the DB.

So, Please tell me the way to resolve this exception.

Your help will be highly appreciated.

Thank You!

Regards,
Pavan
bballard
Jun 11, 2015
Hi Pavan,

VoltDB will return a SQL Error with this message when you have sent too many Ad Hoc SQL requests in a short amount of time. It is a form of back-pressure.

You can avoid getting this by controlling the rate of the client application. The Java client library has a built-in rate limiter that will slow down the execution of callProcedure(...) to maintain the desired rate. It can also be limited by maintaining a limit on the # of outstanding requests. You can set these limits on the ClientConfig object before you create the Client instance, by calling these methods:





I have run into this error when performance testing ad hoc sql queries, and have used the following settings:

ClientConfig config = new ClientConfig();
config.setMaxOutstandingTxns(250);
Client client = ClientFactory.createClient(config);

You may want to experiment a bit. YMMV.

Regards,
Ben
pavangadiya
Jun 19, 2015
Hello,

Thank you for the reply on thread!

Well, limiting the no. of outstanding transaction by (config.setMaxOutstandingTxns(250) affects the performance of VoltDB. By using this function, the throughput(Transaction per second [TPS]) is coming less.

You suggested to use Java client library built-in rate limiter to maintain the desired rate but the methods like :void backpressureBarrier(), void configureBlocking(boolean blocking), boolean blocking() ... all are marked deprecated.

So, could you please suggest the another solution?

I am doing the performance testing for ad hoc sql (insert statements).

Regards,
Pavan
bballard
Jun 19, 2015
Hi Pavan,

Setting either the config.setMaxOutstandingTxns() or config.setMaxTransactionsPerSecond() was what I meant by using the java client library built-in rate limiter. These work together, so you can set them both if that helps. The whole idea of this is to hold back the rate of requests sent by the client so that it does not quite exceed the rate at which the database can handle the transactions. For Ad Hoc SQL this will be at a lower rate than simpler transactions such as a stored procedure call that inserts one row.

Are you testing only Ad Hoc SQL, or is there another workload you are running simultaneously. If you are running multiple types of transactions, then you are right that by setting the client's built-in rate limiter, it will affect all of the requests sent by the client. One way around this would be to use multiple client instances for different types of transactions, with different rate limits set. Another way would be to implement your own method of limiting the rate of requests, that handles Ad Hoc SQL differently than other requests.

This may sound complicated, so it's much easier to test one workload at a time. I'm not saying you should have these rate limits in place for production. This is for benchmarking. The goal should be to size the cluster and optimize the schema, procedures, and SQL so that you have the capacity to handle your real-world workload. Finding the upper limit of throughput without overshooting it and experiencing side-effects like longer response times or these Planner task queue full Exceptions isn't always easy. Sometimes the best approach is to run things slowly at first, and ratchet it up gradually to find the point where you begin to see these effects, rather than trying to hit the 99% "redline" every time.

I hope this helps.

Best regards,
Ben
bballard
Jun 19, 2015
Hi Pavan,

One more thing. You mentioned "I am doing the performance testing for ad hoc sql (insert statements)."

Are you aware that every table in VoltDB automatically has a default "insert" stored procedure generated for it? Rather than calling "INSERT INTO tablename VALUES (...)", it is much more efficient to call this default procedure "tablename.INSERT(...)" which takes the same values.

Also, if you wanted a more custom insert statement like "INSERT INTO tablename (...list of columns...) VALUES (... perhaps some functions used on the values ... )" you can also declare such as SQL statement as a procedure in your DDL and then call it by name. For example "CREATE PROCEDURE tablename_insert_1 AS INSERT INTO tablename ..." This will eliminate the need to go through the Ad Hoc SQL planner.

To get the best performance out of VoltDB, you definitely want to do your high throughput inserts using stored procedures, rather than Ad Hoc SQL insert statements.

Best regards,
Ben