hi
i have a question about key & column design.
in my application we have 3,000,000,000 record in every day
each record contain : user-id, "time stamp", content(max 1KB).
we need to store records for one year, this means we will have about
1,000,000,000,000 after 1 year.
we just search a user-id over rang of "time stamp"
table can design in two way
1.key=userid-timestamp and column:=content
2.key=userid-yyyyMMdd and column:HHmmss=content
in first design we have tall-narrow table but we have very very records, in
second design we have flat-wide table.
which of them have better performance?

thanks.

Hello folks,

i am trying to come up with a nice key design for storing logs in the
company. I am planning to index them  and store row key in the index for
random reads.

I need to balance the writes equally between the R.S. and i could not
understand how opentsdb does that with prefixing the metric id. (i related
metric id with the log type) In my log storage case a log line just has a
type and a date and the rest of it is not really very useful information.

So i think that i can create a table for every distinct log type and i need
a random salt to route to a different R.S. similar to this:
<salt>-<date>

But with this approach i believe i will lose the ability to do effective
partial scans to a specific date. (if for some reason i need that) What do
you think? And for the salt approach do you use randomly generated salts or
hashes that actually mean something? (like the hash of the date)

I am using random uuids at the moment but i am trying to find a better
approach, any feedback is welcome

cheers
Mete

Mete,

Have you tried https://github.com/sematext/HBaseWD ?  WD stands for Writes Distributor.

For a clear introductory post with code samples read: http://blog.sematext.com/2012/04/09/hbasewd-avoid-regionserver-hotspotting-despite-writing-records-with-sequential-keys/

Otis
----
Performance Monitoring for Solr / ElasticSearch / HBase - http://sematext.com/spm

>________________________________
> From: mete <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Sent: Monday, May 21, 2012 10:56 AM
>Subject: key design
>
>Hello folks,
>
>i am trying to come up with a nice key design for storing logs in the
>company. I am planning to index them  and store row key in the index for
>random reads.
>
>I need to balance the writes equally between the R.S. and i could not
>understand how opentsdb does that with prefixing the metric id. (i related
>metric id with the log type) In my log storage case a log line just has a
>type and a date and the rest of it is not really very useful information.
>
>So i think that i can create a table for every distinct log type and i need
>a random salt to route to a different R.S. similar to this:
><salt>-<date>
>
>But with this approach i believe i will lose the ability to do effective
>partial scans to a specific date. (if for some reason i need that) What do
>you think? And for the salt approach do you use randomly generated salts or
>hashes that actually mean something? (like the hash of the date)
>
>I am using random uuids at the moment but i am trying to find a better
>approach, any feedback is welcome
>
>cheers
>Mete
>
>
>  

Hi Mete,

OpenTSDB uses the "natural" availability of the metrics ID to bucket the metrics by type. After that it relies on scanner batching, and block loads.

For your uses case you could bin by time frames, say for example hash the start of each hour into an MD5 and concatenate it with the actual epoch like so

  <MD5(hour(epoch))>-<epoch>

That way you have the ability to range scan the bin but also distribute the bins across the server evenly.

HTH,
Lars

On May 21, 2012, at 7:56 AM, mete wrote:

> Hello folks,
>
> i am trying to come up with a nice key design for storing logs in the
> company. I am planning to index them  and store row key in the index for
> random reads.
>
> I need to balance the writes equally between the R.S. and i could not
> understand how opentsdb does that with prefixing the metric id. (i related
> metric id with the log type) In my log storage case a log line just has a
> type and a date and the rest of it is not really very useful information.
>
> So i think that i can create a table for every distinct log type and i need
> a random salt to route to a different R.S. similar to this:
> <salt>-<date>
>
> But with this approach i believe i will lose the ability to do effective
> partial scans to a specific date. (if for some reason i need that) What do
> you think? And for the salt approach do you use randomly generated salts or
> hashes that actually mean something? (like the hash of the date)
>
> I am using random uuids at the moment but i am trying to find a better
> approach, any feedback is welcome
>
> cheers
> Mete

Hello Lars,

thank you for the hint. I will try the hashed timestamp prefix.

Cheers
On Mon, May 21, 2012 at 6:14 PM, Lars George <[EMAIL PROTECTED]> wrote:

> Hi Mete,
>
> OpenTSDB uses the "natural" availability of the metrics ID to bucket the
> metrics by type. After that it relies on scanner batching, and block loads.
>
> For your uses case you could bin by time frames, say for example hash the
> start of each hour into an MD5 and concatenate it with the actual epoch
> like so
>
>  <MD5(hour(epoch))>-<epoch>
>
> That way you have the ability to range scan the bin but also distribute
> the bins across the server evenly.
>
> HTH,
> Lars
>
> On May 21, 2012, at 7:56 AM, mete wrote:
>
> > Hello folks,
> >
> > i am trying to come up with a nice key design for storing logs in the
> > company. I am planning to index them  and store row key in the index for
> > random reads.
> >
> > I need to balance the writes equally between the R.S. and i could not
> > understand how opentsdb does that with prefixing the metric id. (i
> related
> > metric id with the log type) In my log storage case a log line just has a
> > type and a date and the rest of it is not really very useful information.
> >
> > So i think that i can create a table for every distinct log type and i
> need
> > a random salt to route to a different R.S. similar to this:
> > <salt>-<date>
> >
> > But with this approach i believe i will lose the ability to do effective
> > partial scans to a specific date. (if for some reason i need that) What
> do
> > you think? And for the salt approach do you use randomly generated salts
> or
> > hashes that actually mean something? (like the hash of the date)
> >
> > I am using random uuids at the moment but i am trying to find a better
> > approach, any feedback is welcome
> >
> > cheers
> > Mete
>
>

Mete,

Why separate tables per log type? Why not a single table with the key:

<log type><date>

That's roughly the approach used by OpenTSDB (with "metric id" instead of "log type", but same idea). OpenTSDB goes further by "bucketing" values into rows using a base timestamp in the row key and offset timestamps in the column qualifiers, for more efficiency.

If you start the key with log type, you can do partial scans for a specific date, but only within a single log type; to scan across all log types, you'd need to do multiple scans (one per log type). If you have a fixed and relatively small number of log types (less than 20, say), this could still be the best approach, but if it's a very frequent operation to scan by time across all log types and you have a lot of log types, you might want to reconsider that.

The case for using a hash as the start of the key is really just to avoid region server "hot spotting" (where, even though you have lots of machines, all your insert traffic is going to one of them because all inserts are happening "now" and only one region server contains the range that "now" is in). Salting or hashing a timestamp based key spreads that out so the load is evenly distributed; but it prevents you from doing linear scans over the time dimension. That's why OpenTSDB (and similar models) start the key with another value that "spreads" the data over all servers.

Ian

On May 21, 2012, at 7:56 AM, mete wrote:

> Hello folks,
>
> i am trying to come up with a nice key design for storing logs in the
> company. I am planning to index them  and store row key in the index for
> random reads.
>
> I need to balance the writes equally between the R.S. and i could not
> understand how opentsdb does that with prefixing the metric id. (i related
> metric id with the log type) In my log storage case a log line just has a
> type and a date and the rest of it is not really very useful information.
>
> So i think that i can create a table for every distinct log type and i need
> a random salt to route to a different R.S. similar to this:
> <salt>-<date>
>
> But with this approach i believe i will lose the ability to do effective
> partial scans to a specific date. (if for some reason i need that) What do
> you think? And for the salt approach do you use randomly generated salts or
> hashes that actually mean something? (like the hash of the date)
>
> I am using random uuids at the moment but i am trying to find a better
> approach, any feedback is welcome
>
> cheers
> Mete

Hello,

I wanted  to use separate tables for each log type since they are
considerably big. Around 100gb per month, so it just seemed natural to put
them into different tables since i dont need to query them all together.

Thanks for the heads up
Mete

On Mon, May 21, 2012 at 6:12 PM, Ian Varley <[EMAIL PROTECTED]> wrote:

> Mete,
>
> Why separate tables per log type? Why not a single table with the key:
>
> <log type><date>
>
> That's roughly the approach used by OpenTSDB (with "metric id" instead of
> "log type", but same idea). OpenTSDB goes further by "bucketing" values
> into rows using a base timestamp in the row key and offset timestamps in
> the column qualifiers, for more efficiency.
>
> If you start the key with log type, you can do partial scans for a
> specific date, but only within a single log type; to scan across all log
> types, you'd need to do multiple scans (one per log type). If you have a
> fixed and relatively small number of log types (less than 20, say), this
> could still be the best approach, but if it's a very frequent operation to
> scan by time across all log types and you have a lot of log types, you
> might want to reconsider that.
>
> The case for using a hash as the start of the key is really just to avoid
> region server "hot spotting" (where, even though you have lots of machines,
> all your insert traffic is going to one of them because all inserts are
> happening "now" and only one region server contains the range that "now" is
> in). Salting or hashing a timestamp based key spreads that out so the load
> is evenly distributed; but it prevents you from doing linear scans over the
> time dimension. That's why OpenTSDB (and similar models) start the key with
> another value that "spreads" the data over all servers.
>
> Ian
>
> On May 21, 2012, at 7:56 AM, mete wrote:
>
> > Hello folks,
> >
> > i am trying to come up with a nice key design for storing logs in the
> > company. I am planning to index them  and store row key in the index for
> > random reads.
> >
> > I need to balance the writes equally between the R.S. and i could not
> > understand how opentsdb does that with prefixing the metric id. (i
> related
> > metric id with the log type) In my log storage case a log line just has a
> > type and a date and the rest of it is not really very useful information.
> >
> > So i think that i can create a table for every distinct log type and i
> need
> > a random salt to route to a different R.S. similar to this:
> > <salt>-<date>
> >
> > But with this approach i believe i will lose the ability to do effective
> > partial scans to a specific date. (if for some reason i need that) What
> do
> > you think? And for the salt approach do you use randomly generated salts
> or
> > hashes that actually mean something? (like the hash of the date)
> >
> > I am using random uuids at the moment but i am trying to find a better
> > approach, any feedback is welcome
> >
> > cheers
> > Mete
>
>