This week, we continue the digest from the Slack discussions for Sep 1 2018 to Sep 21 2018. As of this post, we are fully caught up on our backlog.
Tracking progress of resharding #
jk [Sep 5th]
In the SplitClone Stage of resharding, how can i get the percent of process ? Or Could I estimate the time left ?
I don't know of a formal way, but you can look at the database size. It should be a good indicator hopefully.
Alright. I'll check the mysql data size and compare. It is almost 400G in the source shard. So I'm not sure how long it will take. Thank you.
@jk just check the row size of the destination shard and the source shard in vtctld.
Proposal to reduce vitess CPU usage #
sougou [Sep 9th]
Whoever is concerned about CPU: https://github.com/vitessio/vitess/issues/4191
SHOW FULL FIELDS FROM #
captaineyesight [Sep 9th at 6:15 PM]
Is there anyway to get around
1105 vtgate: http://<vtgate-addr>/: syntax error at position 17 near 'FIELDS': SHOW FULL FIELDS FROM? We are using rails, and I believe this is required.
Do you have the full query?
It looks like the grammar support
SHOW FULL COLUMNS FROM.
I may have a simple PR that will add in FIELDS for you.
Excellent. I think it is
show full fields for table name.
@captaineyesight try this https://github.com/vitessio/vitess/pull/4192
(PR has now been merged)
Locking primitives #
sjmudd [Sep 10th at 1:40 AM]
Does vitess expose any locking primitives to do administrative tasks? I want to do a backup on one of my replicas globally but have more than one cell. I can clearly use things outside of vitess (e.g. talk to zookeeper/etcd directly) but given vitess is talking to the topology service it would be nice to use vitess to do this and thus be agnostic to whatever vitess is using.
vitess has built an api for such a thing which it uses internally. For example, to prevent multiple vtctlds from racing on the same action. But it's all go code.
That would be fine. Perhaps you can point me at that or a sample way of getting some global lock that would be useful.
i found this: https://github.com/vitessio/vitess/blob/master/go/vt/topo/locks.go
you can obtain keyspace or shard level locks
but i thought there was a different locking scheme. i'll need to dig further
I'll post here if i find something better.
Found it: https://github.com/vitessio/vitess/blob/master/go/vt/topo/conn.go#L165
// NewMasterParticipation creates a MasterParticipation
Thanks. I will take a look. In many ways this simplifies the doing stuff and means that the process is agnostic to the topo service being used.
Vitess with no special drivers #
Mark Solters [Sep 10th]
I have what I hope is a very trivial question about
vtgate: is it possible to perform a simple SQL operation such as e.g. a data import, but have that come in through
vtgate so its automatically routed appropriately? Without using some kind of application-level driver?
In the tutorial and documentation material everything I’ve seen along these lines already has a specific master tablet in mind.
For example, if I have some
.sql file that has data from many databases, and I have a schema such that those databases are separated into their own keyspaces, is there a single endpoint I can hit with vitess so that the SQL from each database goes to the appropriate keyspace, instead of having to manually connect to each shard’s master?
@Mark Solters you just summarized the whole purpose of vitess :slightly_smiling_face:
I know, but my difficulty is this particular implementation: using the vtgate, but without using e.g. some python or golang driver.
vtgates support mysql protocol.
yes, is there more documentation than this? https://vitess.io/user-guide/mysql-server-protocol/.
Basically, any program that speaks mysql, you should be able to point it at vtgate. there are some caveats: some things that work with single mysql don't work in a sharded system. Like setting global parameters, or information_schema queries etc.
So just to clarify, there is no documentation of the actual implementation of the mysql protocol? Or maybe it is so simple that i am simply looking for more than there is.
Yup. Just point your mysql app at it. We do need to document what works and what doesn't. That's coming soon.
When vtgate looks at the
/mysqlcreds/creds.json file, does the
vtgate binary actually create those users in the tablets? or does authentication “terminate” at the
vtgate binary, and all tablet operations are performed with e.g.
The latter, but they're performed as
vt_app. You can use the table acl feature to do access control at the vitess level.
Cancel resharding #
** weitzman [Sep 10th at 4:02 PM]**
I haven’t synced in the vreplication stuff yet, but I’ve noticed that pre-vreplication things get weird if you forget to clean up blp_checkpoint and re-run SplitClone. You may end up with having to manually populate blp_checkpoint and create the source shard entries
in the future, there may be other workflows that may create vreplication streams
the more correct thing will be to cleanly delete vreplication through a
CancelResharding command, which I intend to implement.
How to write a parser in Go #
sougou [Sep 11th]
Gophercon video is out: https://youtu.be/NG0s3-s3whY.
Resharding in Kubernetes #
Sean Gillespie [Sep 12th at 9:06 AM]
Using vitess in kubernetes what is the best way to add shards to a keyspace? I’m using vitess-operator and it seems like the obvious solution is update the yaml with sharding info and let it create new pods, but I kinda assume there are some extra steps to migrate data and create new masters and such
you’ll have to run through the resharding workflow I’m not sure how much the operator supports you on that https://vitess.io/user-guide/horizontal-sharding-workflow/
I think you can add new shards to the operator, but you need to add all the new shards, then run through the workflow and fail over traffic, then you can remove the old shards So for while you are doing split clone you need to have the old shards and new shards online
Ah so that may be tricky with operator then huh? Would I just give an additional definition for that keyspace that includes shards and then do the migration from the old ones to the new ones?
If you do a
kubectl apply that should just spin out the extra nodes
you would just need the cluster record to contain both.
( the old shards and the new shards )
Emergency reparent #
longfei [Sep 14th at 9:17 PM]
How to change replica to master when the original master is down? Is there any document?
is EmergencyReparentShard ? :+1: i'll take a look at it. thanks
sougou or that one
Resource estimation #
** Mark Solters [Sep 17th at 7:11 AM]**
in the documentation (production planning) it notes that you can think of giving vttablet as much CPU as your mysqls may have been using previously. this gets me in the ballpark but what I’m left wondering is what is the recommendation as far as CPU assigned to the mysql container itself?
e.g. if i have a vttablet that has 2-4 CPUs, is the rule of thumb to give the mysql sidecar that same amount of CPU? are there any guidelines here?
Or are the estimates being provided (2-4CPUs) actually the total for both the
mysql containers taken together, and if so, what should the relative CPU ratio between them be?
MySQL's CPU is harder to estimate because it's workload dependent. Best way is to start with some assumptions and iterate. Range should be in the order of 2-10K QPS per core. Even for vttablet, we've seen 50-100% in CPU variance. So, take those recommendations with a pinch of salt :slightly_smiling_face:
@Mark Solters the recommendations in the helm chart are sourced from a few companies in production and are per container, not combined.
HubSpot use of Vitess #
skyler [Sep 17th]
Our first big project has been migrating all 400+ of our MySQL databases from standalone instances into Kubernetes with the help of the Vitess project. I know some folks from HubSpot are in this channel. Are you able to give any more details on this? Are you really running all of your MySQL in Vitess? That’s pretty impressive.
We're on our way there. It's a pretty cool project.
Very big portion of our production env is on vitess, yes.
And all of our QA (that we care to mention).
With the goal of having 100% of prod vitess very soon.
That’s awesome. That’s a big sign of confidence.
@skyler HubSpot is also unique in how they use Vitess, based on their original microservice setup. They had hundreds of small dbs, as mentioned in the article, vs having a few big dbs that needed sharding.
Right, true. That’s a fortunate starting position to have.
It helps to show how flexible Vitess is.