Re: How do manage running queries across multiple DB servers?

## Sarah Newman

Puppet is meant for configuration management and it sounds like maybe you
need an orchestration tool instead. In that case look at ansible. You can
write modules for it in any language, so it may be less work to go from
what you have now to something repeatable.

I gave a talk at SVLUG about it and can dig up the slides if you're
interested, though it applies to a much older version and the syntax has
changed
significantly.

>>> We have Fabric currently for some of the deploy methods but for the
DB's it's much more hands on. I'm also thinking about using Ansible as it's
written a bit cleaner and easier than full on Python with Fabric module,
but I'll have to get the buy out from the team if that's ok. I'm also
thinking about using Puppet with Bolt, and deploying through Puppet
manifests that would copy the SQL file to each DB, then execute on the
correct database. It's a bit tricky as the full deployment flow is not very
clear and we may have a last minute change (which if so, it's easier to do
manually than the right way). Thanks for the suggestion, I'll keep this in
mind!

## Don Marti

Can you make the setup "idempotent" so that running your setup script
more than once will have the same result as running it once?  That will
help you no matter what method you use to deploy. (If the script runs
with the database already set up, just have it print a happy message and
exit). If all the setup commands are in one transaction you can avoid
the hard-to-troubleshoot situation of a partly set up database.

If it's all in one script that runs one transaction then it's easier to
make sense of when you run it from Puppet later.

>>> The problem is that the SQL scripts are run in order and are in some
cases dependent upon the previous script. So it's more like you need to run
these scripts like "run foo.SQL on dbserver1, database foo, next run
bar.SQL on dbserver2, database bar, ...".

Ideally, I know that fully automating this is going to be very difficult so
I'm thinking of mostly reducing the pain by making the deployment part
(where it actually executes) simpler. But I'm not experienced with managing
multiple shards/db's in this manner, so I'm thinking this is the best route
but will not be standard.

## Ivan Sergio Borgonovo

I'm guessing the DB don't contain the same data but at least they share
the schema and what you're trying to do is updating the schema, possibly
moving some data around and updating stored procedures.

Don Marti gave you a very good advice.

One way to achieve this is storing in a table in the DB the schema
version and update the schema version in the same transaction that
change the schema.

To be extra sure you'd put the logic that check the version inside the
transaction or think about some "locking" system that eg. avoid running
2 update script at the same time.
You'd consider some kind of locking anyway if you're changing the schema
because the program using the data and the data should be at the same
version if you want to keep things easy.
Otherwise if you need some kind of HA... you should give a look at
Refactoring Databases (Ambler, Sadalage), or maybe there is something
newer...

To be extra sure you're not going to mess up things not only you should
compare the current schema version with the script update version but
apply all the "steps" to reach that version.

There are further "tricks" to expand on this idea, but this should give
you a clue.

e.g. if your DB is made of "components", eg. on some instances you've
some tables and in some other you don't you could version every
component so that it would be easier to run everything you need in the
same transaction. Or you could write "fast path" script to move you from
version 3 to version 5 when following all the steps would be too slow etc...


>>> That's a great idea to keep versions of the database in the tables.
Which is something we're not currently doing and there has been some
confusion if the queries were executed on the database or not. I'm going to
ask if there is already a current way of checking database versions, or
something we could easily add.

The other issue is we have sharding, but only some of the environments are
fully sharded while others are sharded for certain databases, but others
are tied together. So it's slightly confusing to think of these copies of
each other since they are actaully each slightly unique (aside from the few
that are master/secondary configuration).

Thank you again for the replies, I'm reading up on database management.

Robert


On Sat, Nov 3, 2018 at 2:32 AM Ivan Sergio Borgonovo <[email protected]>
wrote:

>
>
> On 11/2/18 5:40 PM, Robert Freiberger wrote:
> > Hello,
> >
> > Currently, at my workplace, we have multiple MySQL instances running
> > where they have databases for certain functions. During a deploy, we
> > need to manually SSH into the server and run these queries (stored as
> > SQL files, about 10~25 lines long queries that make table/field changes)
> > manually using the source command.
> >
> > The entire process is very error-prone and time-consuming, to say the
> > least. While we have some tools written in house, I was exploring other
> > options of maybe using Puppet to store the queries in a new "deploy
> > module" and have these deployed but I like to hear from the team on
> > suggested methods.
> >
> > I'm new to the world of database administration and any comments are
> > greatly welcomed.
>
> I'm guessing the DB don't contain the same data but at least they share
> the schema and what you're trying to do is updating the schema, possibly
> moving some data around and updating stored procedures.
>
> Don Marti gave you a very good advice.
>
> One way to achieve this is storing in a table in the DB the schema
> version and update the schema version in the same transaction that
> change the schema.
>
> To be extra sure you'd put the logic that check the version inside the
> transaction or think about some "locking" system that eg. avoid running
> 2 update script at the same time.
> You'd consider some kind of locking anyway if you're changing the schema
> because the program using the data and the data should be at the same
> version if you want to keep things easy.
> Otherwise if you need some kind of HA... you should give a look at
> Refactoring Databases (Ambler, Sadalage), or maybe there is something
> newer...
>
> To be extra sure you're not going to mess up things not only you should
> compare the current schema version with the script update version but
> apply all the "steps" to reach that version.
>
> There are further "tricks" to expand on this idea, but this should give
> you a clue.
>
> e.g. if your DB is made of "components", eg. on some instances you've
> some tables and in some other you don't you could version every
> component so that it would be easier to run everything you need in the
> same transaction. Or you could write "fast path" script to move you from
> version 3 to version 5 when following all the steps would be too slow
> etc...
>
> --
> Ivan Sergio Borgonovo
> https://www.webthatworks.it https://www.borgonovo.net
>
>
> _______________________________________________
> svlug mailing list
> [email protected]
> http://lists.svlug.org/lists/listinfo/svlug
>
-- 
Robert Freiberger
510-936-1210

_______________________________________________
svlug mailing list
[email protected]
http://lists.svlug.org/lists/listinfo/svlug