Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 33C007FF9 for ; Fri, 18 Nov 2011 01:08:33 +0000 (UTC) Received: (qmail 3453 invoked by uid 500); 18 Nov 2011 01:08:30 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 3428 invoked by uid 500); 18 Nov 2011 01:08:29 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 3420 invoked by uid 99); 18 Nov 2011 01:08:29 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Nov 2011 01:08:29 +0000 X-ASF-Spam-Status: No, hits=-0.1 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_MED,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of potekhin@bnl.gov designates 130.199.3.132 as permitted sender) Received: from [130.199.3.132] (HELO smtpgw.bnl.gov) (130.199.3.132) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Nov 2011 01:08:24 +0000 X-BNL-policy-q: X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AmcMAKOuxU6Cx1r6/2dsb2JhbABChQGCWaVfAQEFI1URCQIYCQ0BAQcIAwICCQMCAQIBNBETBgIBAResQIkuiCyGfgEFgX2BFgSIFZ46 X-IronPort-AV: E=Sophos;i="4.69,530,1315195200"; d="scan'208,217";a="153442182" Received: from dh10.s90.bnl.gov (HELO [130.199.90.250]) ([130.199.90.250]) by smtpgw.sec.bnl.local with ESMTP; 17 Nov 2011 20:08:02 -0500 Message-ID: <4EC5AFF2.9030100@bnl.gov> Date: Thu, 17 Nov 2011 20:08:02 -0500 From: Maxim Potekhin Reply-To: potekhin@bnl.gov Organization: Brookhaven National Laboratory User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: user@cassandra.apache.org Subject: Re: Data Model Design for Login Servie References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------000404090001010004020603" This is a multi-part message in MIME format. --------------000404090001010004020603 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 1122: { gender: MALE birthdate: 1987.11.09 name: Alfred Tester pwd: e72c504dc16c8fcd2fe8c74bb492affa alias1: alfred.tester@xyz.de alias2: alfred@aad.de alias3: alf@dd.de } ...and you can use secondary indexes to query on anything. Maxim On 11/17/2011 4:08 PM, Maciej Miklas wrote: > Hallo all, > > I need your help to design structure for simple login service. It > contains about 100.000.000 customers and each one can have about 10 > different logins - this results 1.000.000.000 different logins. > > Each customer contains following data: > - one to many login names as string, max 20 UTF-8 characters long > - ID as long - one customer has only one ID > - gender > - birth date > - name > - password as MD5 > > Login process needs to find user by login name. > Data in Cassandra is replicated - this is necessary to obtain all > required login data in single call. Also usually we expect low write > traffic and heavy read traffic - round trips for reading data should > be avoided. > Below I've described two possible cassandra data models based on > example: we have two users, first user has two logins and second user > has three logins > > A) Skinny rows > - row key contains login name - this is the main search criteria > - login data is replicated - each possible login is stored as single > row which contains all user data - 10 logins for single customer > create 10 rows, where each row has different key and the same content > > // first 3 rows has different key and the same replicated data > alfred.tester@xyz.de { > id: 1122 > gender: MALE > birthdate: 1987.11.09 > name: Alfred Tester > pwd: e72c504dc16c8fcd2fe8c74bb492affa > }, > alfred@aad.de { > id: 1122 > gender: MALE > birthdate: 1987.11.09 > name: Alfred Tester > pwd: e72c504dc16c8fcd2fe8c74bb492affa > }, > alf@dd.de { > id: 1122 > gender: MALE > birthdate: 1987.11.09 > name: Alfred Tester > pwd: e72c504dc16c8fcd2fe8c74bb492affa > }, > > // two following rows has again the same data for second customer > manfred@xyz.de { > id: 1133 > gender: MALE > birthdate: 1997.02.01 > name: Manfredus Maximus > pwd: e44c504ff16c8fcd2fe8c74bb492adda > }, > roberrto@xyz.de { > id: 1133 > gender: MALE > birthdate: 1997.02.01 > name: Manfredus Maximus > pwd: e44c504ff16c8fcd2fe8c74bb492adda > } > > B) Rows grouped by alphabetical prefix > - Number of rows is limited - for example first letter from login name > - Each row contains all logins which benign with row key - row with > key 'a' contains all logins which begin with 'a' > - Data might be unbalanced, but we avoid skinny rows - this might have > positive performance impact (??) > - to avoid super columns each row contains directly columns, where > column name is the user login and column value is corresponding data > in kind of serialized form (I would like to have is human readable) > > a { > alfred.tester@xyz.de :"1122;MALE;1987.11.09; > Alfred > Tester;e72c504dc16c8fcd2fe8c74bb492affa", > > alfred@aad.de@xyz.de :"1122;MALE;1987.11.09; > Alfred > Tester;e72c504dc16c8fcd2fe8c74bb492affa", > > alf@dd.de@xyz.de :"1122;MALE;1987.11.09; > Alfred > Tester;e72c504dc16c8fcd2fe8c74bb492affa" > }, > > m { > manfred@xyz.de :"1133;MALE;1997.02.01; > Manfredus Maximus;e44c504ff16c8fcd2fe8c74bb492adda" > }, > > r { > roberrto@xyz.de :"1133;MALE;1997.02.01; > Manfredus Maximus;e44c504ff16c8fcd2fe8c74bb492adda" > > } > > Which solution is better, especially for better read performance? Do > you have better idea? > > Thanks, > Maciej --------------000404090001010004020603 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 1122: {
          gender: MALE
          birthdate: 1987.11.09
          name: Alfred Tester
          pwd: e72c504dc16c8fcd2fe8c74bb492affa
          alias1: alfred.tester@xyz.de
          alias2: alfred@aad.de
          alias3: alf@dd.de
         }

...and you can use secondary indexes to query on anything.

Maxim


On 11/17/2011 4:08 PM, Maciej Miklas wrote:
Hallo all,

I need your help to design structure for simple login service. It contains about 100.000.000 customers and each one can have about 10 different logins - this results 1.000.000.000 different logins.
   
Each customer contains following data:
- one to many login names as string, max 20 UTF-8 characters long
- ID as long - one customer has only one ID
- gender
- birth date
- name
- password as MD5

Login process needs to find user by login name.
Data in Cassandra is replicated - this is necessary to obtain all required login data in single call. Also usually we expect low write traffic and heavy read traffic - round trips for reading data should be avoided.
Below I've described two possible cassandra data models based on example: we have two users, first user has two logins and second user has three logins
  
A) Skinny rows
 - row key contains login name - this is the main search criteria
 - login data is replicated - each possible login is stored as single row which contains all user data - 10 logins for single customer create 10 rows, where each row has different key and the same content

    // first 3 rows has different key and the same replicated data
        alfred.tester@xyz.de {
          id: 1122
          gender: MALE
          birthdate: 1987.11.09
          name: Alfred Tester
          pwd: e72c504dc16c8fcd2fe8c74bb492affa 
        },
        alfred@aad.de {
          id: 1122
          gender: MALE
          birthdate: 1987.11.09
          name: Alfred Tester
          pwd: e72c504dc16c8fcd2fe8c74bb492affa 
        },
        alf@dd.de {
          id: 1122
          gender: MALE
          birthdate: 1987.11.09
          name: Alfred Tester
          pwd: e72c504dc16c8fcd2fe8c74bb492affa 
        },
   
    // two following rows has again the same data for second customer
        manfred@xyz.de {
          id: 1133
          gender: MALE
          birthdate: 1997.02.01
          name: Manfredus Maximus
          pwd: e44c504ff16c8fcd2fe8c74bb492adda 
        },
        roberrto@xyz.de {
          id: 1133
          gender: MALE
          birthdate: 1997.02.01
          name: Manfredus Maximus
          pwd: e44c504ff16c8fcd2fe8c74bb492adda 
        }
   
B) Rows grouped by alphabetical prefix
- Number of rows is limited - for example first letter from login name
- Each row contains all logins which benign with row key - row with key 'a' contains all logins which begin with 'a'
- Data might be unbalanced, but we avoid skinny rows - this might have positive performance impact (??)
- to avoid super columns each row contains directly columns, where column name is the user login and column value is corresponding data in kind of serialized form (I would like to have is human readable)

    a {
        alfred.tester@xyz.de:"1122;MALE;1987.11.09;
                                 Alfred Tester;e72c504dc16c8fcd2fe8c74bb492affa",
       
        alfred@aad.de@xyz.de:"1122;MALE;1987.11.09;
                                 Alfred Tester;e72c504dc16c8fcd2fe8c74bb492affa",
           
        alf@dd.de@xyz.de:"1122;MALE;1987.11.09;
                                 Alfred Tester;e72c504dc16c8fcd2fe8c74bb492affa"
      },
           
    m {
        manfred@xyz.de:"1133;MALE;1997.02.01;
                  Manfredus Maximus;e44c504ff16c8fcd2fe8c74bb492adda"   
      },
           
    r {
        roberrto@xyz.de:"1133;MALE;1997.02.01;
                  Manfredus Maximus;e44c504ff16c8fcd2fe8c74bb492adda"   
           
      }

Which solution is better, especially for better read performance? Do you have better idea?

Thanks,
Maciej

--------------000404090001010004020603--