Jun 11, 2015
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();
Client client = ClientFactory.createClient(config);
You may want to experiment a bit. YMMV.
Jun 19, 2015
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).
Jun 19, 2015
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.
Jun 19, 2015
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.