Vitess Migration

[Robert Navarro]

Hello,

We're currently running a PHP application that uses a sharded MySQL infrastructure with 15 shards (a pair of MySQL instances, master and slave). We have a tool that does this for us, but the company went out of business and it's no longer supported/developed.

As such we are looking to transition our setup to Vitess in lieu of coding the solution ourselves.

I've played with the demo's on the site and tested re-sharding, things look like they're the perfect fit for us.

We're hosted on Google's cloud and have some microservices that are running in kubernetes, so I'm not opposed to running Vitess there (I'm actually for it...less management for us!).

It's been suggested to run our existing setup in parallel to Vitess (which we can certainly do) but where do we get started?

How much of the kubernetes auto-provisioning scripts from the getting started guides can we use in this transition? (Are there any flags we'd need to change for a production setup?)

I'm assuming we'd have to skip the steps about (7) creating/starting vttablets and (8) initializing mysql databases.

In terms of schema, what are the best practices to get our schema into Vitess?

Sorry for all the questions, but I'm definitely eager to try this all out!

[Robert Navarro]

Also, does Vitess support tables that are not sharded?

We have a few tables in our schema that aren't sharded, but are joined with. Lookup tables of sorts.

[Sugu Sougoumarane]

I'll answer the tables question, and let Anthony answer the rest.

Vitess supports multiple keyspaces, where each keyspace is a logical database, and each keyspace can be either sharded or unsharded. In other words, you split your original database into sharded and unsharded keyspaces, and then you shard the sharded ones.

We have some pretty cool features in the lookup area:

- Lookup tables can be completely managed by vitess as cross-shard indexes. The app doesn't even have to know they exist.

- Lookup tables can themselves be sharded.

- We have an id generation ability for sharded tables that works in a backward compatible way to auto-inc.

- We support transparent (rudimentary) cross-shard and cross-keyspace joins.

In case you haven't, you should fire up the demo, which showcases all the above features.

Upcoming features:

- Support for pinning unsharded tables to a single shard so you don't have to create a separate keyspace for them.

- Support for 2PC, to make these cross-shard transactions atomic.

--
You received this message because you are subscribed to the Google Groups "vitess" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vitess+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony Yeh]

Can you elaborate on how your current sharding solution works? How does it divide data among shards? How does your app code connect to the DB? Is there any abstraction level in your app above which the existence of sharding is completely hidden?

The reason I ask is that these answers will determine whether you can run Vitess on top of your existing shards, to make it possible to migrate without downtime. To do that, Vitess needs to know how to figure out which shard a given record lives on. Then you can set up simultaneous access to the existing MySQL infrastructure through your existing interfaces and through Vitess at the same time.

[Robert Navarro]

The current sharding solution works very similar to Vitess + VTGate V3.

The application just "sees" a giant mysql database. We made little to no code changes when implementing this solution back in the day.

The solution handles cross shard queries, auto incrementing ID's, global tables, data migrations, etc.

The data is divided across shards based off of an account_id (integer) that's unique to each account.

The app code connects to the DB via proxy servers that expose the mysql protocol on port 3306.

This other solution has a configuration DB where it keeps track of what account_id ranges are on which shard. I'm assuming we can mimic that layout in vitess and it will know where all the data lives.

Let me know if you have any additional questions.

[Anthony Yeh]

We discussed this a bit on Slack. For the mailing list record, here's a transcript of my recommended approach for the already-sharded migration case:

The key is to define a mapping between the account_id ranges and the Vitess keyspace_id value, such that the account_id ranges that currently live on the same shard are guaranteed to be within a contiguous range of Vitess keyspace_id values.

As a simplified example, if the first shard currently holds account_ids 1-5 and 11-15, you could map those to keyspace_id values 0-9 like this:

1-5 => 0-4

11-15 => 5-9

Then you tell Vitess that its first shard should have keyspace_ids in the range 0-9, and it will make the same sharding decision as your existing scheme. This allows you to point Vitess at your existing shards and do the migration live.

Later you could tell Vitess to split shard 0-9 into (e.g.) 0-4 and 5-9, so resharding would still work.

The one difference of this approach compared to our normal hashed range-based sharding is that new account_ids would grow into only the last shard, as opposed to having new account_ids randomly distributed across shards.

Of course, once that last shard starts getting big, you could always split it with resharding. We also support merging, so if old account_ids go inactive and the lower shards become over-provisioned, you could merge them.

The key to this magic is writing a vindex plugin that defines this mapping from account_id to keyspace_id.

It would be similar to the Numeric vindex, but with an extra step in the middle that performs the mapping of ranges as described above:

https://github.com/youtube/vitess/blob/master/go/vt/vtgate/vindexes/numeric.go

[Robert Navarro]

To continue the conversation for everyone else, we replicated two of our shards into a test environment.

We have vttablet running on the two shards now and reporting as masters to the vtctld. I've attached the commands we used to fire up the tablets on our existing mysql instances.

A few more questions:

What are the difference between the app, dba, repl, and filtered configs when it comes to vttablet options?

In this post https://groups.google.com/d/msg/vitess/krcZFOTxCxU/6d7ScSXmAgAJ you mentioned "You'll need to add some extra parameters to tell vttablet the database name to assume."

What flags do we need to pass extra? -db-config-app-dbname?

[Sugu Sougoumarane]

The db config parameters are explained in the launch doc here: https://github.com/youtube/vitess/blob/master/doc/LaunchingVitess.md#db-config-parameters.

As for db name, I believe it's init_db_name_override. I'll wait for someone else to confirm.

[Michael Berlin]

On Fri, Jul 15, 2016 at 5:20 AM, 'Sugu Sougoumarane' via vitess <vit...@googlegroups.com> wrote:

As for db name, I believe it's init_db_name_override. I'll wait for someone else to confirm.

I can confirm that --init_db_name_override is the correct flag.

Michael

[Robert Navarro]

I'm making more progress here, however now I'm getting stuck with (and wrapping my head around)  the vschema/vindex stuff.

Can someone shed some more light on how a vschema with a numeric index works? I see one reference of the numeric type in the demo but that's about it. Here is the vschema I have:

{

    "sharded": true,

    "vindexes": {

        "keyspace_idx": {

            "type": "numeric"

        }

    },

    "tables": {

        "access_control": {

            "column_vindexes": [

                {

                    "column": "account_id",

                    "name": "keyspace_idx"

                }

            ]

        }

    }

}

based off of that, how does the vttgate route the query to the right shard? based off the shard name?

Trying to do as much reading as I can, this doc has been most helpful:

https://github.com/youtube/vitess/blob/master/doc/V3VindexDesign.md

[Sugu Sougoumarane]

Pasting response from slack:

The numeric vindex is an identity vindex. It converts the original number into a keyspace_id without hashing, etc.

And that keyspace id is used to compute the target shard.

--

[Robert Navarro]

To fill in some more color here (I also realized I was asking silly X-Y questions):

I'm trying to debug the query routing. I have two shards for my test data, split at 32000. This query returns the results I would expect:

SELECT * FROM access_control where account_id=9159 limit 10

This data lives on shard -32000 (-7d00)

However this one returns an empty result:

SELECT * FROM access_control where account_id=32001 limit 10

This data lives on shard 32000- (7d00-) and doesn't return any results.

I've ran the two queries using the ExecuteAsDba on the tablets directly and that returns the expected results

In trying to debug all of this I noticed that when I tried to create shard names of -32000 and 32000- it didn't like that.

Doing more research it appeared that I might have to convert that decimal to hex, so I used a random internet calculator that converted 32000 to 7d00 and I renamed the shards -7d00 and 7d00-

------

After talking to the guys a bit more on Slack the issue here may be related to shard naming. Anthony mentioned:

I think it is the shard naming. note that -7d00 is different from -0000000000007d00 

-7d00 is the same as -7d00000000000000

I'm going to try renaming the shards to see if that helps things with the query routing.

[Robert Navarro]

For those of you following along at home, I managed to figure this out!

I renamed the shards to:

-0000000000007D00

0000000000007D00-

and fired everything back up, it still didn't work and I was banging my head.

Just out of pure chance I ran:

./kvtctl.sh GetSrvKeyspace test <keyspace_id>

and I noticed that the names on the ranges were the OLD values. So I then ran:

./kvtctl.sh RebuildKeyspaceGraph <keyspace_id>

and the vtgates picked up the changes and started routing queries accordingly.

My test script now returns data for both queries and hits both shards.

On to the next challenge...

[Robert Navarro]

Hey Anthony,

I was reviewing the docs a bit more:

https://github.com/youtube/vitess/blob/ee59df6568f2133151b3d40e022ced25428a4434/doc/V3VindexDesign.md

and I noticed that there is a "lookup_hash" vindex type.

You had originally proposed that we modify the "numeric" index type to map our existing, non contiguous ranges to new contiguous ranges for vitess to use.

From reading the description the "lookup_hash" looks like it may actually do what we need (without creating another vindex type)

Can you shed some more light on the "lookup_hash" vindex type?

Would it not work in our use case?

[Anthony Yeh]

In general, a "lookup_*" vindex is one that requires a query to an external lookup DB (a MySQL table) to resolve a column value to a keyspace ID (i.e. to decide which shard to route to). Since this is slow, you should only use a lookup vindex if there's no other way to compute the shard location.

For your case, there is a relatively simple mapping from ranges of account IDs to shards. So you can avoid the lookup query by writing a custom vindex plugin that tells vtgate how to route account IDs the same way your current sharding scheme works.

[Robert Navarro]

Got it, thanks Anthony!

So we have a custom vindex plugin written now called "numeric_lookup.go"

We've included this in the "go/vt/vtgate/vindexes" directory.

I've also rebuilt a new container for us to test with following the instructions here:

http://vitess.io/getting-started/docker-build.html

I ended up using the command "make docker_lite"

I uploaded the custom docker image to our repository, fired up the container and when I try to import my new vindex schema I get the following error:

E0804 15:02:08.172970   17608 main.go:43] Remote error: rpc error: code = 2 desc = vindexType numeric_lookup not found

This is the vschema.json file I'm trying to load in: 

{

    "sharded": true,

    "vindexes": {

        "keyspace_idx": {

            "type": "numeric_lookup"

        }

    },

    "tables": {

        "product_sku": {

            "column_vindexes": [

                {

                    "column": "account_id",

                    "name": "keyspace_idx"

                }

            ]

        }

    }

}

Is there something special I have to do to get vtgate to pickup the numeric_lookup type?

Is there a definition or register function that we need to invoke for our plugin?

Here is a copy of the code (we'll polish it up and make a PR once we confirm this is working on our end)

// Copyright 2014, Google Inc. All rights reserved.

// Use of this source code is governed by a BSD-style

// license that can be found in the LICENSE file.

package vindexes

import (

"bytes"

"encoding/binary"

"encoding/json"

"fmt"

"io/ioutil"

"strconv"

)

// Stores the mapping of keys

type NumericLookupTable map[int64]int64

// Similar to vindex Numeric but first attempts a lookup via a json file

type NumericLookup struct {

name   string

lookup NumericLookupTable

}

// NewNumericLookup creates a NumericLookup vindex.

func NewNumericLookup(name string, params map[string]string) (Vindex, error) {

jsonPath, ok := params["json_path"]

if !ok {

jsonPath = "numeric_lookup_test.json"

}

lt, error := loadNumericLookupTable(jsonPath)

if error != nil {

return nil, error

}

return &NumericLookup{

name:   name,

lookup: lt,

}, nil

}

// String returns the name of the vindex.

func (vind *NumericLookup) String() string {

return vind.name

}

// Cost returns the cost of this vindex as 0.

func (*NumericLookup) Cost() int {

return 0

}

// Verify returns true if id and ksid match.

func (vind *NumericLookup) Verify(_ VCursor, id interface{}, ksid []byte) (bool, error) {

var keybytes [8]byte

num, err := getNumber(id)

lookupNum, ok := vind.lookup[num]

if ok {

num = lookupNum

}

if err != nil {

return false, fmt.Errorf("NumericLookup.Verify: %v", err)

}

binary.BigEndian.PutUint64(keybytes[:], uint64(num))

return bytes.Compare(keybytes[:], ksid) == 0, nil

}

// Map returns the associated keyspae ids for the given ids.

func (vind *NumericLookup) Map(_ VCursor, ids []interface{}) ([][]byte, error) {

out := make([][]byte, 0, len(ids))

for _, id := range ids {

num, err := getNumber(id)

lookupNum, ok := vind.lookup[num]

if ok {

num = lookupNum

}

if err != nil {

return nil, fmt.Errorf("NumericLookup.Map: %v", err)

}

var keybytes [8]byte

binary.BigEndian.PutUint64(keybytes[:], uint64(num))

out = append(out, []byte(keybytes[:]))

}

return out, nil

}

// ReverseMap returns the associated id for the ksid.

func (vind *NumericLookup) ReverseMap(_ VCursor, ksid []byte) (interface{}, error) {

if len(ksid) != 8 {

return nil, fmt.Errorf("NumericLookup.ReverseMap: length of keyspace is not 8: %d", len(ksid))

}

id := binary.BigEndian.Uint64([]byte(ksid))

for k, v := range vind.lookup {

if int64(id) == v {

id = uint64(vind.lookup[k])

}

}

return id, nil

}

func init() {

Register("numeric_lookup", NewNumericLookup)

}

func loadNumericLookupTable(path string) (NumericLookupTable, error) {

var m map[string]int64

lt := make(map[int64]int64)

data, error := ioutil.ReadFile(path)

if error != nil {

return lt, error

}

error = json.Unmarshal(data, &m)

for k, v := range m {

newK, _ := strconv.ParseInt(k, 10, 64)

lt[newK] = v

}

return lt, nil

}

Let me know if I can provide any additional information.

[Sugu Sougoumarane]

This change looks good overall. There should be nothing special you need to do beyond rebuilding vtgate. The Register call in the init function should make vtgate aware of the vindex.

Some comments:

- The lookup name is misleading. If you look at vindex.go, the vindex satisfies a Functional vindex and not a Lookup. Instead, it may be better to call this a static map. So, something like numeric_static_map may be better.

- Although not a big deal, recommended cost is 1. 0 presumes that it's the identity funciton.

- Eventually, the map should be loaded from topo. It's something we can do after the API revamp alain is working on.

To unsubscribe from this group and stop receiving emails from it, send an email to vitess+unsubscribe@googlegroups.com.

[Robert Navarro]

I figured this out.

I had updated the image for only the vtgate pods but I did NOT update the image for the vtctld pod.

I went ahead and updated ALL containers to use the new image and we're good to go.

The pieces are falling together for our migration!

Full steam ahead on our application changes now to make sure we comply with the vitess query restrictions.

[Anuj Nagpal]

I can see numeric_static_map.go has been included in vitess codebase but I am not able to send json_path parameter. 

1. Where should json_path file should reside?

2. Can anyone share sample vschema file which includes json_path parameter.

[ch...@stitchlabs.com]

Hi Anuj,

Here ya go: https://gist.github.com/chrisgillis/f0e9880ac338e5521a14f56992231404