[Labs-l] [Toolforge] /usr/bin/sql updated for connecting to new Wiki Replica clusters

Thu Sep 28 15:56:17 UTC 2017

On Thu, Sep 28, 2017 at 4:04 AM, Jaime Crespo <jcrespo at wikimedia.org> wrote:
> On Thu, Sep 28, 2017 at 10:40 AM, MarcoAurelio <strigiwm at gmail.com> wrote:
>> Hello Bryan,
>>
>> So let's say I want to conect to centralauth_p using the new servers. Shall
>> I 'sql --cluster=analytics centralauth_p'?

In theory this would work. It looks like in practice however it
currently will not work. The process I used to make the
*.{analytics,web}.db.svc.eqiad.wmflabs service name aliases is based
on the production dblist files. Specifically the script makes CNAME
DNS records for each host listed in s[1-7].dblist as read from
https://noc.wikimedia.org/conf/ which point to the correct
s[1-7].{analytics,web}.db.svc.eqiad.wmflabs A record.

The centralauth database isn't a wikidb properly so it is not listed
in the dblist files. I opened a bug about this
(<https://phabricator.wikimedia.org/T176978>). I'll find a way to
create additional aliases for the handful of special databases that
are in the cluster.

>> In case I later have to change to another database, would 'use metawiki_p;'
>> keep me logged in the new servers as well?

Any `use $FOO;` command will only attempt to switch your active
database in the current connection, so things will work as Jaime
explains in much more detail below.

The TL;DR of that is yes it will work today, but if the databases you
are switching between cross 'shard' boundaries then it may stop
working at some point in the future. Also be aware that shards are not
a fixed thing. Databases may migrate from one shard to another
occasionally and new shards may be created. The safest and most future
proof method is use the $dbname.{web,analytics}.db.svc.eqiad.wmflabs
service names *or* do you own shard lookup via the meta_p database
which will always be available on every mysql server.

> I cannot answer your first question, but I can do the second one:
>
> Right now, all databases are in all servers, but because of the growth
> in data, we cannot guarantee that that will be happening forever. You
> are free to jump between databases from the command line, but if you
> program a script/application from 0, it is safer to disconnect and
> connect again, specially if using a separate replication group[0], as
> one day, due to contraint of resouces/too much load, we may have to
> separate some databases away. As centralauth and meta are both on s7,
> separation is even more and more unlikely, but if in the future
> something changes, we want to warn in advance (potentially years
> ahead).
>
> Whatever is the state of shards, our promise is that you will be able
> to use the metadata databases meta_p and information_schema_p to know
> if the project you are interested is locally available. Because the
> new servers are "new", we are open for feedback and are monitoring
> issues to always provide a better service.
>
> In summary, if someone has a script that connects to every single
> database and runs some command, the safest option is to connect 7
> times to each s* replica set or to specific databases. Heads up also
> for the future creation of s8 (and replica set changes), which will
> likely happen on the upcoming quarter.
>
> [0] <url:https://noc.wikimedia.org/db.php>

The creation of s8 that Jaime mentions will be a cross-team project in
the Technology group for the October - December 2017 quarter [1]. We
will make more announcements as the change gets closer.

[1]: https://www.mediawiki.org/wiki/Wikimedia_Technology/Goals/2017-18_Q2#Program_1:_Availability.2C_performance.2C_and_maintenance

Bryan
-- 
Bryan Davis              Wikimedia Foundation    <bd808 at wikimedia.org>
[[m:User:BDavis_(WMF)]] Manager, Cloud Services          Boise, ID USA
irc: bd808                                        v:415.839.6885 x6855