Forum: Building VoltDB Applications

Post: Single Partition Joins

Single Partition Joins
ajgent
Apr 16, 2010
This discussion is being moved to a separate topic. Please feel free to continue the thread here.
In reply to another post, Henning said:
Killer Bug


"you must be careful when doing joins in a single-partitioned stored procedure that all records are within that partition."

I am not sure if I fully understood, but this implies that VoltDB will basically believe me and follow orders blindly when I tell it that a join will be single partition? And deliver only a subset of data, if I make a mistake, with no warning?

If this is right, it looks like VoltDB's equivalent to C's zero pointers as an error source to me. Like, it will run a decent part of your user base crazy and globally will cost man-years of frustrated debugging efforts. :-)

I think I would be quite grateful if you were to build a very simple asynchronous error checking mechanism in that runs some intelligent check in the 'background' and informs me of a potential error if it finds that a join I just executed single-partition would have yielded different results if it hadn't been flagged single-partition. And that this flagging must have been a mistake in the first place.

I could imagine that I may come around to actually like the feature as is, if it was guaranteed that I can query reliably only the data subset of a single node. But I would be surprised if this would be on offer? It's simply a mistake and can't be consistent in its results I would assume.

If so, then an intelligent, pro-active check will make sure that VoltDB minimizes pitfalls that are very specific to the special ways it introduces, i.e. will rarely be anticipated, and that seem guaranteed to be beginner's favored errors, as well as advanced user's most dreaded.
Hi Henning, We apologize that
ajgent
Apr 16, 2010
Hi Henning,

We apologize that this aspect of designing stored procedures is not called out clearly enough in the documentation. I will make sure it is highlighted in the next release.

This behavior, although not immediately self-evident, is not a bug. The goal of VoltDB is to optimize single-partition transactions and part of the responsibility for that falls on the application developer. You must write the queries to operate properly within a single partition and then declare the procedure to be single-partitioned.

For simple statements (such as INSERT or SELECT ... WHERE partition-key=?) this is obvious. When you start discussing joins, it becomes more complex. And this is where the documentation is lacking at the moment.

Today, VoltDB does not verify that the SQL queries within a single-partitioned procedure are actually single-partitioned. Because our focus is on performance, it is unlikely that we would implement additional background tasks to check up on the procedures at runtime. However, a future enhancement may be to check the queries for single-partition procedures at compile time.

I hope that helps explain the situation.

--Andrew
Yes
henning
Apr 16, 2010
Hi Henning,

We apologize that this aspect of designing stored procedures is not called out clearly enough in the documentation. I will make sure it is highlighted in the next release.

This behavior, although not immediately self-evident, is not a bug. The goal of VoltDB is to optimize single-partition transactions and part of the responsibility for that falls on the application developer. You must write the queries to operate properly within a single partition and then declare the procedure to be single-partitioned...


--Andrew

Absolutely, thanks!

I'd reiterate that this check should be introduced with modest priority.

Of course as a debug-mode that can be switched of for production deployment.
Hm
chbussler
Apr 16, 2010
Absolutely, thanks!

I'd reiterate that this check should be introduced with modest priority.

Of course as a debug-mode that can be switched of for production deployment.


Hi,

so I am still trying to understand this fully. I think we have two different items here.

a) One is that of a join at design time. A join might come into picture because of an application requirement. So in the end, I can either write that join in a stored procedure, or select the base tables or do the join myself in my application. Ideally, I'd like the join to be in the database.

b) At run time, the rows that are touched by the join might or might not be in a single partition. This depends on the partition keys of the tables. So it might be that the rows are in the same partition, or not. That is something I cannot enforce from an application, but VoltDB decides where to place the rows (if I understand this correctly).

So that fact that a join is single-sited or not is not a design time property, but a run time property (seems like). If it is a run time property, then we have a best case / worst case situation in the sense that in the best case it is single-sited, in the worst case it is a multi-sited operation.

Fundamentally, at compile time, I cannot assume the best case any more as it is a run time property. So I think that the compiler should tell me, that while I said single-sited == true, it might actually not be the case at run time. The compiler can only warn, but not fail my code. And at run time I'd assume that VoltDB figures this out and executes the join correctly.

So I don't really see that this is a debug-feature only as it is a run time issue that can occur any time, depending on how my database grows.

How off am I here in my understanding?

Thanks,
Christoph
Hi Christoph, The choice of
ajgent
Apr 16, 2010

Hi,

so I am still trying to understand this fully. I think we have two different items here.

a) One is that of a join at design time. A join might come into picture because of an application requirement. So in the end, I can either write that join in a stored procedure, or select the base tables or do the join myself in my application. Ideally, I'd like the join to be in the database.

b) At run time, the rows that are touched by the join might or might not be in a single partition. This depends on the partition keys of the tables. So it might be that the rows are in the same partition, or not. That is something I cannot enforce from an application, but VoltDB decides where to place the rows (if I understand this correctly)...

Thanks,
Christoph


Hi Christoph,

The choice of whether a stored procedure is single-partitioned is definitely a design time decision. You as the designer specify how the table is partitioned (that is, on what column). In the @ProcInfo you tell the stored procedure what parameter specifies the partition key and what table & column it applies to. (VoltDB uses this to verify that the partitioning info for the stored procedure matches what is specified in schema and the project definition file.) You are then responsible for making sure the SQL queries in the procedure use that key for accessing the rows. It is this last step that VoltDB currently does not validate.

At run-time, there is only an issue if you have written queries that contradict the partition information in the @ProcInfo.

Is that clearer?

--Andrew
Still unclear to me
chbussler
Apr 16, 2010
Hi Henning,

We apologize that this aspect of designing stored procedures is not called out clearly enough in the documentation. I will make sure it is highlighted in the next release.

This behavior, although not immediately self-evident, is not a bug. The goal of VoltDB is to optimize single-partition transactions and part of the responsibility for that falls on the application developer. You must write the queries to operate properly within a single partition and then declare the procedure to be single-partitioned.

For simple statements (such as INSERT or SELECT ... WHERE partition-key=?) this is obvious. When you start discussing joins, it becomes more complex. And this is where the documentation is lacking at the moment.

Today, VoltDB does not verify that the SQL queries within a single-partitioned procedure are actually single-partitioned. Because our focus is on performance, it is unlikely that we would implement additional background tasks to check up on the procedures at runtime. However, a future enhancement may be to check the queries for single-partition procedures at compile time.

I hope that helps explain the situation.

--Andrew

Hi,
you wrote (in the original thread):

>>>
3) Both tables are partitioned on the same key. For example, if EMPLOYEE is partitioned on EMPLOYEE_ID and DEPARTMENT is partitioned on MANAGER_ID (a silly example, I grant you), then the following could be single-partitioned:

SELECT E.EMP_NAME, D.DEPT_NAME FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.EMPLOYEE_ID=D.MANAGER_ID AND E.EMPLOYEE_ID=?

>>>

Now, I thought I understood 'single-partitioned' to mean that all rows that my query will touch are in a single partition. Now, in your original reply - 1) all tables are replicated, and 2) on table replicated, one partitioned - I understand as in these cases the replication makes sure that the whole table is in the partition.

However, in the case 3) (copied above), I am not sure why I can assume that the rows that satisfy the join are actually in the same partition?

As you write "could be" single-partitioned, I assume it is a possibility, but not ensured. So from an application development viewpoint, I have to assume the worst case, as I cannot check for it, and assume that this join is multi-sited. Is that true?

Thanks,
Christoph
Hi Christoph, This is a bit
ajgent
Apr 16, 2010
Hi,
you wrote (in the original thread):

>>>
3) Both tables are partitioned on the same key. For example, if EMPLOYEE is partitioned on EMPLOYEE_ID and DEPARTMENT is partitioned on MANAGER_ID (a silly example, I grant you), then the following could be single-partitioned:

SELECT E.EMP_NAME, D.DEPT_NAME FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.EMPLOYEE_ID=D.MANAGER_ID AND E.EMPLOYEE_ID=?...

Thanks,
Christoph

Hi Christoph,

This is a bit of a trick, since we are stepping into how VoltDB actually does partitioning. But if two tables are partitioned on columns of the same datatype, their rows will be partitioned on the same hash indexes.

Say EMPLOYEE is partitioned on INTEGER EMPLOYEE_ID and DEPARTMENT is partitioned on INTEGER MANAGER_ID. Then the EMPLOYEE record for EMPLOYEE_id=1234 will be in the same partition as the DEPARTMENT record where MANAGER_ID=1234. That is why the previous example is single-partitioned.

I was being overly careful when I said "could be". If the procedure contained other queries, they would also need to be single-partitioned on the same key to be successfully single-partitioned. I probably should have just said "is".

Is that clearer?

Andrew
Operators?
chbussler
Apr 16, 2010
Hi Christoph,

This is a bit of a trick, since we are stepping into how VoltDB actually does partitioning. But if two tables are partitioned on columns of the same datatype, their rows will be partitioned on the same hash indexes.

Say EMPLOYEE is partitioned on INTEGER EMPLOYEE_ID and DEPARTMENT is partitioned on INTEGER MANAGER_ID. Then the EMPLOYEE record for EMPLOYEE_id=1234 will be in the same partition as the DEPARTMENT record where MANAGER_ID=1234. That is why the previous example is single-partitioned...

Andrew


Hi,

thanks! This helps a lot (and definitely a good documentation entry:-))

Which then immediately leads me to the next question. So if the operator is '=' then I can be sure that the rows are in the same
partition. But if the operator is e.g. '>', then not any more. Is that correct?

Thanks,
Christoph
"So if the operator is '='
ajgent
Apr 16, 2010
Hi,

thanks! This helps a lot (and definitely a good documentation entry:-))

Which then immediately leads me to the next question. So if the operator is '=' then I can be sure that the rows are in the same
partition. But if the operator is e.g. '>', then not any more. Is that correct?

Thanks,
Christoph


"So if the operator is '=' then I can be sure that the rows are in the same partition. But if the operator is e.g. '>', then not any more. Is that correct?"

Absolutely. If a stored procedure is single-partitioned, it uses the specified parameter as the hash and assumes all records will be accessed by that value. So even a simple SELECT (no join) must use equals as a condition.

Andrew
No NOT
henning
Apr 17, 2010

"So if the operator is '=' then I can be sure that the rows are in the same partition. But if the operator is e.g. '>', then not any more. Is that correct?"

Absolutely. If a stored procedure is single-partitioned, it uses the specified parameter as the hash and assumes all records will be accessed by that value. So even a simple SELECT (no join) must use equals as a condition.

Andrew


Now it dawns upon me why even "NOT" is disallowed in WHERE clauses.

Maybe add a note on that to the docs there, it clarifies quite a bit!
Guaranteed?
henning
Apr 17, 2010
Hi Christoph,

This is a bit of a trick, since we are stepping into how VoltDB actually does partitioning. But if two tables are partitioned on columns of the same datatype, their rows will be partitioned on the same hash indexes.

Say EMPLOYEE is partitioned on INTEGER EMPLOYEE_ID and DEPARTMENT is partitioned on INTEGER MANAGER_ID. Then the EMPLOYEE record for EMPLOYEE_id=1234 will be in the same partition as the DEPARTMENT record where MANAGER_ID=1234...

Andrew

"if two tables are partitioned on columns of the same datatype, their rows will be partitioned on the same hash indexes."

Is this guaranteed behavior? I promise I'll find a nice legal performance exploit for this, if it is.

Fancy side effects of a new era ...
Hi Henning, Is the current
ajgent
Apr 20, 2010
"if two tables are partitioned on columns of the same datatype, their rows will be partitioned on the same hash indexes."

Is this guaranteed behavior? I promise I'll find a nice legal performance exploit for this, if it is.

Fancy side effects of a new era ...

Hi Henning,

Is the current hashing scheme guaranteed to operate this way for all time? I can't say that. However, it is how it works today and there are no plans to change it in the foreseeable future.

Andrew