Vitess Weekly Digest Aug 24, 2018


This week, we continue the digest from the Slack discussions for Jul 25 2018 to Aug 2 2018 .

Update stream


Jian [Jul 25th at 1:27 PM]
hi there, I'm new to Vitess, now I'm following the user-guide from vitess.io to explore vitess, in update stream section, I notice they have change log, where could I see these change logs so I can have a better understanding of the update stream?

sougou 
That's the only documentation we have about the update stream, but we'll be fixing docs for all vitess very soon.

Jian 
sure sure, thank you very much!

vamsi 
@sougou even if documentation is not ready yet, is there some info you can provide to Jian about where he can see change logs?

sougou 
The end to end test can actually be handy. Let me get the link.

sougou 

sougou 

Jian [1 day ago]
:+1:

Fixing a failed MigrateServedTypes

Vidhi [2:10 AM]
Hi

for slave rollback, this will work? ./lvtctl.sh MigrateServedTypes -reverse test_keyspace/0  rdonly

sougou [6:36 AM]
yes. that should work

Vidhi [6:48 AM]
If in case some error came during master switch, as for rollback (no reads and writes are happening), if I update the old master end-point in zookeeper . Will it work?

sougou [6:50 AM]
i think you have to manually repair. can you show me where it failed?

Vidhi [6:50 AM]
It didnt failed yet. I havent done the switch. Just want to figure out rollback plan if something went wrong
Can you please elaborate on manually repair. How to do that?

sougou [6:51 AM]
let me look it up

for master switch, what vtctld does is the following:
set a shard control record to disable query service on source master, and issue a refresh which also sets the source master read-only (edited)
then waits for replication to catch up.
Once caught up, it sets the shard control record to enable query service on destination masters, and issue a refresh on destination masters that makes them read-write.
If there is a failure in the middle, you have to manually do or undo the setting of the tablet control
using SetShardTabletControl command
and then issue a RefreshStateByShard to the relevant tablets
i'm working on improving this part: https://github.com/vitessio/vitess/pull/4034
sougou
#4034 vreplication: change to use new _vt.vreplication
This change deprecates _vt.blp_checkpoint in favor  
of vreplication, which stands for Vitess Replication.  
The goal is to make vreplication a standalone sub-module  
of vitess that other services can use, including the  
resharding worflow.

The big change in the model is that vreplication is not owned by the resharding workflow. The workflow instead creates vreplication streams as needed, and controls them individually. The stream id for a replication is now generated by vreplication, which the resharding workflow stores and tracks.

This also means that a vreplication stream can be directly created and managed by anyone as needed. This allows for newer and more flexible workflows in the future.

Vidhi [7:00 AM]
Can you share the complete command to do these steps. I coulnt find it vitess docs

sougou [7:01 AM]
vtctl -h gives me this: SetShardTabletControl [--cells=c1,c2,...] [--blacklisted_tables=t1,t2,...] [--remove] [--disable_query_service]
to enable query service, you probably should use --remove
to disable --disable_query_service
I haven't used these myself. So, you should test them out yourself to make sure they work as intended.
You can try it out on the source master while it's serving queries to see if it stops serving
and re-enable it with --remove 

Vidhi [7:04 AM]
Sure, will try this setup on stage first.
Thank you very much for the help :)

Reading from replicas


skyler [Jul 31]
Does vtgate support rewrite rules similar to ProxySQL? We’re using ProxySQL to send queries to a replica if it’s not too laggy.

Does vtgate, or some other component of the stack, support something similar?


I haven’t found much in docs and via google, so I assume no, but I thought I’d ask anyway.


sougou
@skyler can you give an example?


skyler
The actual config is pretty lengthy, but what we’re doing is matching for the string `/*SLAVE OK*/` at the beginning of every query. If that string exists, then we route a query to a read replica if it’s replication lag is less than some threshold. If a replica’s replication lag is greater than the threshold, ProxySQL “shuns” it, which means that it removes the replica from the list of replicas that are available for querying.


sougou
this is supported differently by vitess


sougou
you can specify db name as `db@replica`


sougou
and the tolerances you mention can be specified to vttablet


skyler

Oh interesting, that’s very cool.


Reconstructing zk data


vamsi [Jul 31st]

Do people who use vitess with ZK generally backup ZK data regularly? If not, what would happen if ZK data is somehow corrupt or if ZK dies for some unexpected reason?

sougou

zk data can be reconstructed if needed.

it's mostly metadata about keyspaces and shards

but it's still a good idea to back it up

vamsi
any tools that can reconstruct it?

sougou
to manually reconstruct? they would be the vtctl commands like `CreateKeyspace` etc.
You could probably write a shell script to do this
Will be interesting if we could do a feature that generates this.

ameet
@vamsi we are using consul.  We backup the vitess metadata every 30 mins. It has saved us at least once where an operator deleted the metadata by mistake. Also, we manually backup before doing the cutover operation for a shard split

sougou
If you loose all data. I think these steps will also work:
1. Recreate all the cells
2. Restart all vttablets
3. Perform `TabletExternallyReparented` on all master tablets
Your system should be pretty much restored to the old state.


Are primary keys needed



faut [Aug 1st]

Is it imperative for tables to have a primary key in vitess?

derekperkins

it’s pretty much imperative in MySQL to have a PK, but I don’t think Vitess adds any more need for it. Are you wanting to run sharded or non-sharded?

faut

non-sharded. We have some tables that don’t have PKs, and vitess throws `cannot identify primary key of statement` on updates and inserts.

sougou
it will work if you change mysql to RBR


Can sequence tables be in a sharded keyspace




captaineyesight [Aug 1st]

Hi. I’m looking at sequences and I’m a little confused. Lets say I have a sharded cluster: foo 00-80 and foo 80-FF. In foo, I have a table named bar that has a lovely vschema that splits it between shards. Where does the bar_seq table go? 00-80 or 80-FF or should it be in a completely different place?

weitzman

The sequence table does not need to be in the same keyspace. The vitess examples tend to use a keyspace called “lookup” or something like that

The sequence table only has one row, so if you put it in the same keyspace it would end up in whatever tablet the primary key “0” maps to

If someone really didn’t want to go through the trouble of having multiple keyspaces there might be an argument to do that, but under normal circumstances you’d probably want the sequences in an unsharded keyspace

captaineyesight
thanks

sougou (update)
Submitted https://github.com/vitessio/vitess/pull/4134: vschema: allow pins in vschema. This allows you to pin a table to a specific shard by assigning a keyspace id to it.


Creating replicas for devs


faut [Aug 2nd] What are the suggestion for devs in minikube and simulating the effects of vitess (Assuming they will just run mysql with a DB named the same as the keyspace? So if they write toxic queries they know before they get to a staging environment etc. And is it possible to dump a keyspace(sharded/unsharded) so you can replicate that in a standalone mysql? ie: Is it possible to migrate out of vitess? (edited) sougou @faut I don't fully understand the question. Are you talking about migrating into vitess, or out? To migrate out, you can just start sending queries directly to the mysql instances and tear down the vitess components. You could also replicate the data out and failover. faut :+1: Makes sense. But we’d need to rebuild/revert all the sharding? sougou Or reimplement sharding at the app layer If mysql can handle, you can also merge back all the shards into one faut And do you have suggestions for how to ‘replicate’ the database for devs. Or what to do for a dev environment? running vitess locally seems overkill. sougou If it's just to make the data available to devs, you can always setup a standalone replica from a vitess master. faut How can I restore that standalone from the backups created from vitess backup? sougou yeah. you can restore from those backups and point the restored db to the master if you're lazy, you could make vitess do it for you bring up a replica vttablet. once it's brought up, kill just the vttablet (and delete its tablet record) faut is manually restoring the data just a case of copying the GCS bucket to datadir? sougou i believe so (don't know the mechanism for GCS) vitess copies the data files into the datastore as files so, if bucket==file, it should work the same way in reverse faut cool. Then theoretically I should be able to make a backup by just copying the files there. Then restoring from that on vttablet. sougou should work

faut Thank you, I’ve got a couple of ideas I will try.



Hackathon!




raj.veerappan [Aug 2nd]

Another question on https://vitess.io/overview/scaling-mysql/#migrating-production-data-to-vitess


In that approach, you'd enable MySQL replication from your source database to the Vitess master database.


In the replication approach, does "Vitess master database" mean use the VTGate as the replication slave? Or the VTTablet of the master or the mysql of the master? If it's mysql of the master, does that populate the schema properly in Vitess?

faut

hey raj, if you’re planning to do a production migration to vitess maybe we can chat. We’re also planning to move to vitess so we’re struggling through similar issues.

raj.veerappan
I'm just doing this for a hackathon to prove things out and see if it'll work for us

sougou
People have adopted more approaches than those mentioned in that write-up. We need to update it with the new strategies

sougou
Dual-writes seems to be a popular approach
In that particular descrption, I think it meant mysql->mysql

raj.veerappan
what happens to the schema in that case?
I guess I thought updates to the schema have to go through vtgate

sougou
not necessary
even after you're fully migrated to vitess, you can deploy schema changes directly to the mysqls
and people often do, using tools like gh-ost, etc
the `ApplySchema` is just a convenience

raj.veerappan
hmm, ok, I made that assumption because one approach I tried was to copy over the data files from my non-vitess mysql to the data directories of the vitess mysql instances. Then when I fired up vtgate and used the mysql command line client to inspect the db, I could see all the tables were there
but when I tried to select rows from a table, vtgate complained that it didn't recognize the table

sougou
ohh. you still need a `vschema`
something that describes how your shards are layed out
https://vitess.io/user-guide/vschema/

sougou [18 days ago]
if the target db is not sharded yet, the vschema is a simple json that lists the table names

raj.veerappan
nice! thank you, will try that now

sougou
examples/demo/schema/lookup/vschema.json
{
 "sharded": false,
 "tables": {
   "user_seq": {
     "type": "sequence"
   },
   "music_seq": {
     "type": "sequence"
   },
   "name_keyspace_idx": {}
 }
}
tables should have no types. the `sequence` tables are special case

raj.veerappan
right was gonna say, I didn't think I needed to create those until I sharded things

sougou
vitess will work without a vschema as long as there's only one keyspace, because it knows there's only one
as soon as you have more than one, it needs to know where to route the queries

raj.veerappan
when you say work without a vschema, will it function purely as a "connection pool" or will it still need to parse the queries and will only support the statements it supports?

sougou
it will still do some work, but most queries will just be passed through

raj.veerappan
one of the reasons I tried copying over the data files directly was that when I tried restoring from a mysqldump vtgate complained that it couldn't handle one of the insert statements to a many-to-many mapping table because it didn't understand the primary key

sougou
it's probably because the mysqls are setup as SBR
we recommend RBR now. Hopefully we can deprecate SBR support soon :slightly_smiling_face:

raj.veerappan
oh interesting, I didn't realize that would affect mysqldump

faut
would the vschema tables just be: `tables: { user: {} }`?

sougou
`"user":...` yeah

raj.veerappan
will retry importing using mysql dump after switching all the vitess instances to RBR, seems easier than creating that json

faut
raj, are you working in GCP or baremetal?

sougou
if it's a single keyspace, you shouldn't need that json (irrespective of how you do the import)

raj.veerappan
@sougou I think I may just be in a weird state right now because the mysql import failed halfway, will start over after wiping things out and see if I can just copy the data files over without doing anything with vschema

raj.veerappan
@faut I'm just doing baremetal for the hackathon, if we start using it in production it would be with k8s/AWS (edited)

fautI had the same problems when I mounted the datadir for a single database. It showed all the tables if i did `show tables` it showed everything. But any query would say. the table didnt exist. Even direct to mysql

sougou
it may be related to vttablet not having reloaded the schema
vttablet reloads the schema every X minutes

faut
I did a vschema reload. But the problem is with mysql. Because even when querying directly it would fail

sougou
this is vttablet seeing the table. vschema is for vtgate (edited)

raj.veerappan
vttablet reloads the schema every X minutes
is there a way to force this?

sougou
yeah. `vtctl ReloadSchema`

faut
raj, if you come right with the datapath mounting please let me know. I couldn’t get it to work

sougou
there is a way to make vttablet auto-detect by making it watch the replication stream. most people prefer not to use that feature
i think the flag is `-enable_replication_watcher` (not at my comp)

raj.veerappan
I wiped everything out and restarted and copied the data files over, when I login through vtgate I see the tables but in the UI for vitess the schema says empty and I'm not able to select from any of the tables in mysql client connected to vtgate
did `vtctl ReloadSchema` against my master vttablet but the schema did not populate in web UI
so will try using the json and enumerate the table names
actually, will switch all the vitess mysql instances to RBR and try loading from mysqldump first
nice, that seems to be the way to go, only problem now is that our mysqldump has tables with foreign key constraints on tables that are defined further down in the dump and vtgate doesn't like that, will need to edit the dump and reorder the create table statements

sougou
whatever works :slightly_smiling_face:

Raj.veerappan
problem is that it seems like vtgate does not support disabling foreign key checks for loading from dump

raj.veerappan
even trying to disable for session throws

```mysql> set foreign_key_checks=0;
ERROR 1105 (HY000): vtgate: http://localhost:15001/: unsupported construct: set foreign_key_checks=0```
(edited)

raj.veerappan
well, I found a janky workaround that makes this easy, create a schema only mysqldump, open up mysql cli onto vtgate, run `source ` repeatedly until the table count stabilizes. Then source your data only dump, super janky but it works for my hackathon :slightly_smiling_face:
I made it work the proper way, didn't realize I just needed to load the mysqldump directly against the vitess mysql master instance and reloadschema and everything would "just work"

sougou
yeah. that would be the best.

faut
The problem for me with the mysqldump is the downtime. Snapshotting a disk and using it as a mount is much quicker. I have got things to work with the mysqldump. Just trying to figure out the best way to migrate in production.








Configuring the app to use VTGate

Sean Gillespie [Aug 2nd]

Is there documentation on setting up an app to use vtgate?  I can’t find much beyond saying the apps can use it like MySQL

sougou

there's not much to it. just point the app at vtgate on the mysql port
https://vitess.slack.com/archives/C0PQY0PTK/p1527271545000268
Command to connect to vtgate: `mysql -h 127.0.0.1 -P 15306 -u mysql_user --password=mysql_password`
Posted in #vitessMay 25th

if you have many vtgates, you can put them behind an ELB

Sean Gillespie
Where do you set the user/pass?

sougou
in a credentials file like this https://github.com/vitessio/vitess/blob/master/examples/local/mysql_auth_server_static_creds.json
examples/local/mysql_auth_server_static_creds.json
```{
 "mysql_user": [
   {
     "MysqlNativePassword": "*9E128DA0C64A6FCCCDCFBDD0FC0A2C967C6DB36F",
     "Password": "mysql_password",```
...
and give that to vtgate (look at vtgaet-up,sh) in that same directory

Overriding the db name


raj.veerappan [Aug 2nd]

unfortunately looks like flyway relies on `information_schema` for a bunch of logic and that's not available through vtgate
sougou
if you connect to a specific shard, vtgate will pass it through
it should be an unsharded keyspace, or something like `ks:-80`

raj.veerappan
but then the db name will be `vt_db` instead of just `db`
I'll just disable flyway for now since migrations will probably need to be reworked if we use vitess

sougou
you have another option
you can override the dbname
vttablet command line `-init_db_name_override` (edited)
and name the db as `db` instead of `vt_db`

raj.veerappan [18 days ago]
lol, that might simplify things


Overriding the db name



raj.veerappan [Aug 2nd]

Seems like the `./lvtctl.sh CopySchemaShard test_keyspace/0 target/0` doesn't work if `test_keyspace` has tables with foreign keys in it
sougou
yeah. You can do a custom schema deploy in that case
it's only a convenience

raj.veerappan
is there a gist for that too :slightly_smiling_face:
I guess I only need to deploy the schema for the particular tables that I'm vertically sharding?
will just do a `show create table` on it on test_keyspace and just run directly using mysql on `target`

sougou
yup

raj.veerappan
if vtworker `cannot find MASTER tablet for destination shard for target/0` even though I did the `InitShardMaster` step, is there something else I need to do?
I see the `target` keyspace in the web ui with its shards and one tagged as master correctly

sougou
check the status page for vttablet `/debug/status` and the logs. Maybe it didn't initialize correctly

raj.veerappan
status is healthy

sougou
and it shows up as master in vtctld?

raj.veerappan
yes

sougou
the vtworker would have written a logfile
can you see if it has more info there?
can you also show me your vtworker command?

raj.veerappan
`./sharded-vtworker.sh VerticalSplitClone --tables my_table target/0`
will check the log file
the only error besides the `cannot find MASTER...` one is `proc.go:85] unexpected error on port 0: Get http://localhost:0/debug/pid: dial tcp [::1]:0: connect: can't assign requested address, trying to start anyway`

sougou
what is the full error? (that error can come from three different places)

raj.veerappan
ohh, just noticed that it was in a cell that doesn't match mine
ahh, I updated the cell name in the other scripts but not in `sharded-vtworker.sh`

sougou
that will do it :slightly_smiling_face:

raj.veerappan
that was it :slightly_smiling_face:
been at it all day, starting to miss things

sougou [18 days ago]
don't forget about `MigrateServedFrom` (not `MigrateServedTypes`)

Comments

Popular posts from this blog

Vitess Weekly Digest Aug 5, 2018

Distributed Transactions in Vitess

Custom Sharding With Vitess