Forum: Building VoltDB Applications

Post: Strange Performace Characteristics

Strange Performace Characteristics
caravone
Sep 28, 2010
I'm working with a simple prototype app based on the structure of the sample voter app.
My DDL looks something like this:

create table t (
partition_key varchar(40) not null,
time timestamp not null,
category integer not null,
value varchar(15) not null
);
create index idx_event_time on t(partition_key, category, time);

Basically, the app inserts a bunch of data and does queries based on a time range for particular partition_key and category values. The thought was that the index would give an efficient range query on time.

When I run the modified voter client app, if I do only the inserts, I get a blazing fast 20k tx/sec. If I try to run the queries on a fairly full database (1.5 million), the app seems to hang.
------
Now the strange part...

I change my index to be only on the partition_key value:

create index idx_event_time on t(partition_key);

Now I still get 20k/sec inserts, but I also get very fast queries (8k/sec or so).

I am really surprised that changing to a less selective index would give such a dramatic performance improvement.

I am running on a dual core machine, so I also tried a configuration with one VoltDB node instead of two. I get similar results.

Any idea what might be happening?
re: strange performance characteristics
tcallaghan
Sep 29, 2010
Caravone,

Can you email your entire application to us at support@voltdb.com? I want to make sure I'm using the same stored procedures that you are using.

-Tim
re: strange performance characteristics
caravone
Sep 30, 2010
Caravone,

Can you email your entire application to us at support@voltdb.com? I want to make sure I'm using the same stored procedures that you are using.

-Tim


I will try to get a small example together that I can email to you.

I tried a couple of variations on the index, and every composite index I try seems to be very slow.

Curtis
re: strange performance characteristics
tcallaghan
Sep 30, 2010
I will try to get a small example together that I can email to you.

I tried a couple of variations on the index, and every composite index I try seems to be very slow.

Curtis


Great, I'm very curious about what is going on. Having the actual code makes it a much easier process.

-Tun
re: strange performance characteristics
caravone
Sep 30, 2010
Great, I'm very curious about what is going on. Having the actual code makes it a much easier process.

-Tun


Once I changed the name of the index to include the string "tree", it got really fast.

According to the docs, you only need to do this naming convention to force a tree index for indexes with all integer columns. Maybe the docs got it backward? It seems to make more sense to make a tree by default for integer columns than for large strings.

I send the app anyway so you can take a look.

thanks,
Curtis
Curtis, I've created a defect
tcallaghan
Oct 1, 2010
Once I changed the name of the index to include the string "tree", it got really fast.

According to the docs, you only need to do this naming convention to force a tree index for indexes with all integer columns. Maybe the docs got it backward? It seems to make more sense to make a tree by default for integer columns than for large strings.

I send the app anyway so you can take a look.

thanks,
Curtis


Curtis,

I've created a defect for this, it is viewable at this link.

Right now our SQL planner is assuming that a hash index is getting created so a sequential scan is performed. Since one of the columns in the index is VARCHAR we are actually creating a tree index that should be used with an index scan.

You have already identified the current workaround, put the word "tree" somewhere in your index name and the planner will use it for an index scan.

Thanks for your help in identifying this issue.

-Tim
Re: Curtis, I've created a defect
caravone
Oct 1, 2010
Curtis,

I've created a defect for this, it is viewable at this link.

Right now our SQL planner is assuming that a hash index is getting created so a sequential scan is performed. Since one of the columns in the index is VARCHAR we are actually creating a tree index that should be used with an index scan...

-Tim



No problem.
Curtis
fairyanna
Dec 22, 2014
As per your email, an array cannot be a partitioning parameter to a stored procedure.
I will be creating a ticket in our bug database to improve the messaging for this scenario.