#DBHangOps 06/11/15 -- DBA Expectations and Automation
Hello everybody!
Join in #DBHangOps this Thursday, June, 11, 2015 at 11:00am pacific (18:00 GMT), to participate in the discussion about:
- What does being a DBA mean to you?
- What are your expectations of a DBA?
- What's the last thing you automated and why?
You can check out the event page at https://plus.google.com/events/ctmk6ua93affd01jnfmm73i68fo on Thursday to participate.
As always, you can still watch the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!
See all of you on Thursday!
You can catch a livestream at:
DBA Expectations
- What's the role of a DBA at your company?
- Managing the infrastructure
- Interacting with engineers and developers on good access patterns with the databases layer
- In the case of a shared hosting environment, DBAs are also responsible for interfacing with customers and their database-related issues.
- It also really depends on the size of a team and infrastructure
- Larger teams may have more specialization on various aspects such as: query tuning, performance, system administration, networking, etc.
- Are you responsible for researching new data stores? Should you become an expert in them?
- "Do not be in the cave" as a DBA. You need to be open to hearing about new technologies and solutions. What else is out there besides MySQL.
- While MySQL may have the largest footprint in some shops, you need to be familiar with and comfortable with other datastores for different use cases
- It's hard to carve out time for looking into new datastores, but you need to.
- DBAs ask the questions around:
- How does it scale?
- For what types of data and access patterns is this best suited for?
- How do you do backups?
- How do you manage user access?
- How do you secure it?
- If you have a larger team, individuals in the group can specialize or research new solutions.
- Engagement of your development group also influences this. Perhaps your engineering organization is comfortable treating MySQL as a simple key-value store and building the application around that assumption.
- What tactics do you take to be involved is decisions for new datastores?
- Engage in roadmap meetings with stakeholders across your organization
- Write up blog posts reflecting on new technologies learned about at conferences (E.g. Percona, MySQLConnect)
- Perhaps embed yourself with your dev teams and meet with them regularly
- A lot of communication! Meet regularly with team leads to stay in sync and let them know if you've been blindsided by a new datastore you weren't ready for
- Don't be dismissive of questions about datastores. These systems will find their way into production.
- be proactive in providing answers and solutions to questions about storing different types of data.
- It's important to explain the "Why" for preferring certain datastores over others
Automation!
- What's the last thing you automated or last bit of automation you setup?
- For Silvia Botros
- Orchestrator!
- Creating Chef recipes to bootstrap MySQL and orchestrator
- Silvia also blogged about her experience Learning Configuration Management as a DBA
- For Phil Hildebrand
- Backups for Rethink
- All backups are done by schema. Needed to write tools to verify that each schema was backed up and restorable
- For Pim van der Wal
- A better MySQL upgrade tool
- Previously, MySQL was upgraded by changing a setting in puppet and having new packages installed.
- Now having Master<->Master setup, there's a desire for more control in the upgrade process.
- From Mark Leith's perspective
- a lot of automation exists for validating, installing, and testing changes in MySQL
- How does internal Oracle testing automate issues with auto.cnf, user setup, and so on?
- Most recent automation was: installing the SYS schema by default in new versions of MySQL
- Automating user management
- Can easily build something from scratch for this, but curious about other solutions
- Geoffrey Anderson built a tool to reconcile grants on an instance of MySQL based on a file of grant statements
- if differences are identified, it can add missing grants, remove accounts that have invalid grants, or both.
- The grant files are generated using cluster membership information fed from puppet facts
- Puppet manifests dictate roles for privileges and user membership in the roles
- LDAP plugins didn't seem to solve the problem well enough which is why this was the path forward
- Some other places simply have output from
pt-show-grants that's fed into new instances as they come up.
- Silvia Botros has a Chef recipe at the moment that defines users and privileges on infrastructure and environment influences which password hashes are used
- output from
pt-show-grants is used to validate the unit tests for each environment.
- This still feels brittle at times so more granluar privileges like column-level privileges are curently avoided
- Need to verify that schemas all properly exist before creating table and schema level privileges as well.
- The database cookbook has some providers for the interactions in creating the accounts
- Legacy issues that cause some challenges with MySQL automation
- E.g. mysql_install_db script writes to the binary log by default or mysqldump not dumping user information by default
- Bootstrapping new MySQL setups across various platforms (Linux, Windows, etc.).
- Some changes to mysql_install_db have been made to further improve automation of MySQL bootstrapping
- Challenges in needing a basic password to connect to a fresh instance in order to change the default passwords
- About a year ago, Oracle focused on improving the RPM/DEB packages and dogfooding it internally
- Issues and semantics around the various mysql scripts are improving
- What's challenging is that other distributions of MySQL (e.g. Percona) have different semantics with their packaging
- E.g. percona packages run mysql_install_db as part of their post install scripts
- Containerizing / Abstraction / Encapsulation of MySQLs
- Oracle is also starting to provide Docker images.
- These are used for testing internally at Oracle
- Interesting learnings around multiple Docker images stacking up on test environments because of port availability
- Oracle has configured Jenkins to bootstrap a docker image, run all unit tests in the docker image, and then return results to the devs.
- Much nicer environment isolation with this
- It's become much easier to "trick" the software in containers since the software assumes it's in its own machine
- Testing replication has been interesting here, too
- Can bootstrap multiple Docker images and hook up replication between all the MySQLs in them
- MySQL Sandbox is also used heavily since you can define a replication topology in a dot file
- Running MySQL tasks through Apache Mesos is also gaining traction for some companies (e.g. Moz)
- Definitely some interest in hearing about running stateful daemons via Mesos to see how things will work and having state maintained.
- Twitter recently open sourced Mysos for running MySQL on Mesos
- Easiest method presently is to use local disk and just accept that the workers need to live on the same physical machines
- Other methods might include mounting a remote network volume or plugging into shared storage. These may have problems with I/O however.