RiakTS Query Question

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RiakTS Query Question

Joe Olson-2

Suppose I have the following table in RiakTS:


CREATE TABLE T1 (

id                VARCHAR NOT NULL, 

eventtime TIMESTAMP NOT NULL,

field2         SINT64,

data           BLOB NOT NULL, 

primary key (id, QUANTUM(eventtime, 365, 'd')),id)

)


Assume the BLOB field is close to the max size for a RiakTS BLOB value (~1MB)


Suppose I want to execute the following query:


Select id, eventtime, field2 from T1 where ((id = ID1) and (eventtime >= T1 and eventtime < T2))


I only want the SINT64 field, not the 1MB BLOB.


Am I paying for the bandwidth for the RiakTS cluster to pass around (internally) the 1MB BLOB field just to get the SINT64 field?


If so, is there a way to avoid this, besides creating a second table without the BLOB field?


_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RiakTS Query Question

Alexander Sicular
I'm not 100% certain but I do not believe that is the case. Part of the reason for structured data is efficient retrieval. I believe the data is read but only the data selected leaves the leveldb backend, unselected data never leaves leveldb so there's no overhead when passing data from level to Riak or on the network. 

I defer to the engineering folks working on TS tho. 

-Alexander 

@siculars

Sent from my iRotaryPhone

On Mar 29, 2017, at 15:08, Joe Olson <[hidden email]> wrote:

Suppose I have the following table in RiakTS:


CREATE TABLE T1 (

id                VARCHAR NOT NULL, 

eventtime TIMESTAMP NOT NULL,

field2         SINT64,

data           BLOB NOT NULL, 

primary key (id, QUANTUM(eventtime, 365, 'd')),id)

)


Assume the BLOB field is close to the max size for a RiakTS BLOB value (~1MB)


Suppose I want to execute the following query:


Select id, eventtime, field2 from T1 where ((id = ID1) and (eventtime >= T1 and eventtime < T2))


I only want the SINT64 field, not the 1MB BLOB.


Am I paying for the bandwidth for the RiakTS cluster to pass around (internally) the 1MB BLOB field just to get the SINT64 field?


If so, is there a way to avoid this, besides creating a second table without the BLOB field?

_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com

_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RiakTS Query Question

Andrei Zavada
Joe,

TS records in a given table all have the same structure and are stored
and retrieved as single objects (in Riak KV sense); the backend cannot
introspect them and only extract some fields.

Full records are read from backend and these are delivered, in chunks,
to the coordinator node (the node your client has connected to). On
receipt, columns not appearing in the SELECT clause are dropped; thus,
only the relevant columns are kept while the coordinator collects the
remaining chunks. From there, records are shipped to the client (via
temp tables if the query had an ORDER BY or LIMIT clause).

So no, the 1M blobs will be read from the leveldb backend, and they
will hog the bandwidth between storage and coordinator nodes. They
will not appear in the traffic going from the coordinator to the
client, though.

Andrei

On Wed, Mar 29, 2017 at 10:17 PM, Alexander Sicular <[hidden email]> wrote:

> I'm not 100% certain but I do not believe that is the case. Part of the
> reason for structured data is efficient retrieval. I believe the data is
> read but only the data selected leaves the leveldb backend, unselected data
> never leaves leveldb so there's no overhead when passing data from level to
> Riak or on the network.
>
> I defer to the engineering folks working on TS tho.
>
> -Alexander
>
> @siculars
> http://siculars.posthaven.com
>
> Sent from my iRotaryPhone
>
> On Mar 29, 2017, at 15:08, Joe Olson <[hidden email]> wrote:
>
> Suppose I have the following table in RiakTS:
>
>
> CREATE TABLE T1 (
>
> id                VARCHAR NOT NULL,
>
> eventtime TIMESTAMP NOT NULL,
>
> field2         SINT64,
>
> data           BLOB NOT NULL,
>
> primary key (id, QUANTUM(eventtime, 365, 'd')),id)
>
> )
>
>
> Assume the BLOB field is close to the max size for a RiakTS BLOB value
> (~1MB)
>
>
> Suppose I want to execute the following query:
>
>
> Select id, eventtime, field2 from T1 where ((id = ID1) and (eventtime >= T1
> and eventtime < T2))
>
>
> I only want the SINT64 field, not the 1MB BLOB.
>
>
> Am I paying for the bandwidth for the RiakTS cluster to pass around
> (internally) the 1MB BLOB field just to get the SINT64 field?
>
>
> If so, is there a way to avoid this, besides creating a second table without
> the BLOB field?
>
> _______________________________________________
> riak-users mailing list
> [hidden email]
> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>
>
> _______________________________________________
> riak-users mailing list
> [hidden email]
> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>

_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RiakTS Query Question

Joe Olson-2

>So no, the 1M blobs will be read from the leveldb backend, and they will hog the bandwidth between storage and coordinator nodes.


Ok that is what I thought. Thanks for the info. 


One more question...what is the penalty for doing an in place update (i.e. an insert of the same exact keys) of the BLOB in the same situation? I have a new blob, and I want to overwrite the BLOB field with a new one. Obviously I'll have to pay to shuffle the new blob around the cluster. Am I paying the same as it were a clean, new insert? Do I get to re-use the existing (already sorted in its correct place) record, and just update the object component? Or when I do a duplicate insert, am I paying the price for a delete + insert?


Thanks again!


From: Andrei Zavada <[hidden email]>
Sent: Wednesday, March 29, 2017 3:14:58 PM
To: Alexander Sicular
Cc: Joe Olson; [hidden email]
Subject: Re: RiakTS Query Question
 
Joe,

TS records in a given table all have the same structure and are stored
and retrieved as single objects (in Riak KV sense); the backend cannot
introspect them and only extract some fields.

Full records are read from backend and these are delivered, in chunks,
to the coordinator node (the node your client has connected to). On
receipt, columns not appearing in the SELECT clause are dropped; thus,
only the relevant columns are kept while the coordinator collects the
remaining chunks. From there, records are shipped to the client (via
temp tables if the query had an ORDER BY or LIMIT clause).

So no, the 1M blobs will be read from the leveldb backend, and they
will hog the bandwidth between storage and coordinator nodes. They
will not appear in the traffic going from the coordinator to the
client, though.

Andrei

On Wed, Mar 29, 2017 at 10:17 PM, Alexander Sicular <[hidden email]> wrote:
> I'm not 100% certain but I do not believe that is the case. Part of the
> reason for structured data is efficient retrieval. I believe the data is
> read but only the data selected leaves the leveldb backend, unselected data
> never leaves leveldb so there's no overhead when passing data from level to
> Riak or on the network.
>
> I defer to the engineering folks working on TS tho.
>
> -Alexander
>
> @siculars
> http://siculars.posthaven.com
>
> Sent from my iRotaryPhone
>
> On Mar 29, 2017, at 15:08, Joe Olson <[hidden email]> wrote:
>
> Suppose I have the following table in RiakTS:
>
>
> CREATE TABLE T1 (
>
> id                VARCHAR NOT NULL,
>
> eventtime TIMESTAMP NOT NULL,
>
> field2         SINT64,
>
> data           BLOB NOT NULL,
>
> primary key (id, QUANTUM(eventtime, 365, 'd')),id)
>
> )
>
>
> Assume the BLOB field is close to the max size for a RiakTS BLOB value
> (~1MB)
>
>
> Suppose I want to execute the following query:
>
>
> Select id, eventtime, field2 from T1 where ((id = ID1) and (eventtime >= T1
> and eventtime < T2))
>
>
> I only want the SINT64 field, not the 1MB BLOB.
>
>
> Am I paying for the bandwidth for the RiakTS cluster to pass around
> (internally) the 1MB BLOB field just to get the SINT64 field?
>
>
> If so, is there a way to avoid this, besides creating a second table without
> the BLOB field?
>
> _______________________________________________
> riak-users mailing list
> [hidden email]
> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>
>
> _______________________________________________
> riak-users mailing list
> [hidden email]
> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>

_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RiakTS Query Question

Andrei Zavada
Hi Joe,

First of all, updates in Riak TS are not safe. There are write-once
optimizations which take shortcuts in the logic that ensures your
writes are consistent. In the simplest scenario of a single client
doing the writes at one and the same node of a cluster that completes
the writing faster than the client issues a new request, and no
partitions occur, data will be successfully overwritten. If any of the
above conditions are not met, you may see old values in subsequent
reads.

That said, there is no difference, in terms of amount of data going
from coordinator to the leveldb nodes, between initial (clean) writes
and overwrites. If you do a deletion before writing, however, the full
record will be read (but not delivered from the eleveldb node to the
coordinator). The latter issue was fixed in
https://github.com/basho/riak_kv/pull/1630 and should be gone in 1.6.

Regards,
Andrei

On Thu, Mar 30, 2017 at 5:06 PM, Joe Olson <[hidden email]> wrote:

>>So no, the 1M blobs will be read from the leveldb backend, and they will
>> hog the bandwidth between storage and coordinator nodes.
>
>
> Ok that is what I thought. Thanks for the info.
>
>
> One more question...what is the penalty for doing an in place update (i.e.
> an insert of the same exact keys) of the BLOB in the same situation? I have
> a new blob, and I want to overwrite the BLOB field with a new one. Obviously
> I'll have to pay to shuffle the new blob around the cluster. Am I paying the
> same as it were a clean, new insert? Do I get to re-use the existing
> (already sorted in its correct place) record, and just update the object
> component? Or when I do a duplicate insert, am I paying the price for a
> delete + insert?
>
>
> Thanks again!
>
> ________________________________
> From: Andrei Zavada <[hidden email]>
> Sent: Wednesday, March 29, 2017 3:14:58 PM
> To: Alexander Sicular
> Cc: Joe Olson; [hidden email]
> Subject: Re: RiakTS Query Question
>
> Joe,
>
> TS records in a given table all have the same structure and are stored
> and retrieved as single objects (in Riak KV sense); the backend cannot
> introspect them and only extract some fields.
>
> Full records are read from backend and these are delivered, in chunks,
> to the coordinator node (the node your client has connected to). On
> receipt, columns not appearing in the SELECT clause are dropped; thus,
> only the relevant columns are kept while the coordinator collects the
> remaining chunks. From there, records are shipped to the client (via
> temp tables if the query had an ORDER BY or LIMIT clause).
>
> So no, the 1M blobs will be read from the leveldb backend, and they
> will hog the bandwidth between storage and coordinator nodes. They
> will not appear in the traffic going from the coordinator to the
> client, though.
>
> Andrei
>
> On Wed, Mar 29, 2017 at 10:17 PM, Alexander Sicular <[hidden email]>
> wrote:
>> I'm not 100% certain but I do not believe that is the case. Part of the
>> reason for structured data is efficient retrieval. I believe the data is
>> read but only the data selected leaves the leveldb backend, unselected
>> data
>> never leaves leveldb so there's no overhead when passing data from level
>> to
>> Riak or on the network.
>>
>> I defer to the engineering folks working on TS tho.
>>
>> -Alexander
>>
>> @siculars
>> http://siculars.posthaven.com
>>
>> Sent from my iRotaryPhone
>>
>> On Mar 29, 2017, at 15:08, Joe Olson <[hidden email]> wrote:
>>
>> Suppose I have the following table in RiakTS:
>>
>>
>> CREATE TABLE T1 (
>>
>> id                VARCHAR NOT NULL,
>>
>> eventtime TIMESTAMP NOT NULL,
>>
>> field2         SINT64,
>>
>> data           BLOB NOT NULL,
>>
>> primary key (id, QUANTUM(eventtime, 365, 'd')),id)
>>
>> )
>>
>>
>> Assume the BLOB field is close to the max size for a RiakTS BLOB value
>> (~1MB)
>>
>>
>> Suppose I want to execute the following query:
>>
>>
>> Select id, eventtime, field2 from T1 where ((id = ID1) and (eventtime >=
>> T1
>> and eventtime < T2))
>>
>>
>> I only want the SINT64 field, not the 1MB BLOB.
>>
>>
>> Am I paying for the bandwidth for the RiakTS cluster to pass around
>> (internally) the 1MB BLOB field just to get the SINT64 field?
>>
>>
>> If so, is there a way to avoid this, besides creating a second table
>> without
>> the BLOB field?
>>
>> _______________________________________________
>> riak-users mailing list
>> [hidden email]
>> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>>
>>
>> _______________________________________________
>> riak-users mailing list
>> [hidden email]
>> http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
>>

_______________________________________________
riak-users mailing list
[hidden email]
http://lists.basho.com/mailman/listinfo/riak-users_lists.basho.com
Loading...