Month: April 2015

Better DevOps with MySQL and Docker – Percona Live 2015

moderndba MySQL , ,

So I ran into the Docker session “Better DevOps with MySQL and Docker” by Sunny Gleason from SunnyCloud late.  Docker can help you lay down images of your system but it is not the be all end all for DevOps.  Docker is a replacement for your Hypervisor.  Docker instead is a way to isolate workloads on a single Operating system.  You can run docker on top of a Hypervisor but it is not required.

Need to be careful with public images.  Hard to tell the good from the bad.  Docker working on a process to improve this.  Some of the continuing issues are managing the connectivity between containers and the physical environment.  Cross platform is also very new.  Based on Linux containers so security is still an issue.  Not really for multi tenant but for running multiple trusted solutions on the same hardware.  Deployment can be I/O intensive.

InnoDB Change Buffering – Percona Live 2015

moderndba MySQL , ,

My first real session of the day today at Percona Live was “InnoDB Change Buffering” with Davi Arnaut from LinkedIn.

The change buffer is used to batch changes to non-unique secondary indexes to avoid random I/O. This is a feature that has to be enabled and you can use innodb_change_buffering to

  • inserts
  • deleted
  • purges
  • changes (inserts and delete-mark)
  • all (default)
  • none

If the page is not in the buffer pool then the changes is buffered and it marks the buffer bitmap that the page has pending changes and then the changes are merged on read from disk.  The change buffer is actually just a separate index not exposed with page number as the index.  It is also smart enough to know that a change  will cause a page to be empty or to split and it will not buffer those changes.

Need to grab these slides and go over it as to keep up and take notes was not working very well.

Key Notes Day 2 – Percona Live 2015

moderndba MySQL ,

After the standard house cleaning items, Steaphen Greene from Facebook came out to discuss WebScaleSQL.  WebScaleSQL is a collaboration project between a number of companies supporting large Web applications on MySQL (Alibaba, FaceBook, Google, LinkedIn, and Twitter).  This is a branch of MySQL modified by all the companies.  They also work with MariaDB, Oracle, and Percona on their development.  He gave a lot of praise to Oracle for their work on MySQL.  Some interesting features are:

  • Asynchronous MySQL Client
  • Lower InnoDB buffer pool memory footprint
  • Reduced double-write mode
  • Server-side query management features
    • Query throttling etc.
  • Logical read-ahead (faster backups)

Coming features

  • On-line InnoDB rebuilds
  • DocStore
  • MySQL and RocksDB

He then wrapped up with a discussion on the legal challenges of trying to get WebScale changes into the main MySQL code branch.

Then Patrik Sallner from MariaDB came up and gave a talk on how open source can help solve data management problems.  His slide on the data storage subway map was very interesting and worth trying to find.  He points out that open source provides for greater collaboration between companies and reduce the duplication of efforts.  He showed how MariaDB has included changes from a large list of companies into their code base.  One feature discussed of interest was the Connect Storage Engine which allows federation of data from other data platforms.  MariaDB 10.1 will have Galera in the base code so no separate binaries, activation is based on configuration.  10.1 will also have tablespace and table level encryption built in. My take away was that I need to look into Webyog and Sea Lion for monitoring.

After Patrik, Tomas Ulin from Oracle came out to celebrate the 20th anniversary of MySQL.  It is also been 10 years that Oracle has owned InnoDB and 5 owning MySQL.  5.7 Release candidate was announced last week.  Highlights of 5.7 are:

  • 2 x faster then 5.6
  • Enhanced InnoDB: faster online and bulk load operations
  • Replication Improvements: multi master multi threaded slaves (8-10x faster slaves)
  • New optimizer cost model: greater user control and better query performance
  • performance schema improvements
  • Query Rewrite Plugin
  • JSON support added (labs now but planned for GA)
  • Functional indexes (wasn’t clear if this was new)
  • SYS Schema improvements to help with monitoring and administration
  • GIS improvements
  • InnoDB improvements
    • Native partitioning
    • configurable fill factor
    • Buffer Pool resize online
    • Separate UNDO tablespace
  • Group Replication (probably not available for GA see labs.mysql.com)

Then he talked about MySQL Fabric 1.5.  The product sounds promising and we will take a look at this.  Also, Workbench 6.3 will have support for Fabric and many of these other features (just went RC).  Call for papers ends in two weeks for Open World.

Last but not least Robert Hodges from VMWare came out to talk about the cloud.  He talked about how VMWare is looking to build a public cloud that better integrates with private clouds that Enterprises already have.  The talk was basically a overview of the features of the newest version of VMWare.

Performance Birds of a Feather – Percona Live 2015

moderndba MySQL , ,

This session was a talk with Rick James where we just talked about performance issues we in the audience were interested in.  (http://mysql.rjweb.org) My Notes are:

  • Start with InnoDB buffer pool at 70% of memory unless really small VM
  • Partitioning should not be done for performance
  • Stored procedures can provide better performance over views
  • If you are replicating then break up your deletes into small batches
  • We talked about ways to create shards to minimize impact
  • Slow Query log at 2 seconds or less and use pt-query-digest to analyze
  • Batch your inserts to have one insert statement with up to a 1,000 rows to add
  • For Galera you can make an argument that you can run innodb to 0 or 2 because a node can be rebuilt with SST
  • You can use INFORMATION_SCHEMA tables to build a blocking chain
  • subqueries can be poor performing because the optimizer is not smart enough to handle them
  • Try your best to turn them into a join unless you have a group by in the subquery to limit the rows
  • Also can create a temp table in memory and join to it instead of a subquery

I need to grab Rick’s information on Galera for developers.

Scaling MySQL in the cloud with Vitess and Kubernetes – Percona Live 2015

moderndba MySQL , , , ,

“Scaling MySQL in the cloud with Vitess and Kubernetes” by Anthony Yeh from Google.  He works on the YouTube team at Google.  The talk was basically how they use Vitess and their container platform (Kubernetes).  Vitess provides

  • Performance
  • Protection
  • Monitoring/Analysis
  • Mangement
  • Sharding

It sits between the application and database layer. It leverages a vtgate which abstracts the data layer and what shard to go get the information from.  Can separate live traffic from analytic traffic.  Vitess can cache based on primary key identified rows.  This is different from the block caching performed by MySQL.  Vitess will kill poorly performing queries automatically to avoid replicating bad queries.  It performs transparent rerouting.  It also performs live resharding.

Vitess is a container cluster.  Kubernates is a Container Cluster OS built on top of Docker.  For more info see vitess.io/getting-started.  Looks very promising and will need to see how this fits with Trove.

Sharding, horizontal and vertical scaling – Percona Live 2015

moderndba MySQL , , ,

In “Sharding, horizontal and vertical scaling“, Tom Christ from Tumblr discussed sharding for scale.  He discussed scale dimensions of read, write, and size.  While challenges are often specific to data sets and/or applications, there are ways to categorize and reason about them.  Read challenges include query complexity, query volume, and buffer pool effectiveness.  To fix read issues you can scale horizontally with replica fanout, or you can add caching.  You can also scale vertical with bigger servers, or leverage smaller concurrent queries.

He discussed the options for write and size scaling and this is where sharding comes into help.  He discussed the trade off between costs, complexity, write amplification, and concurrency when deciding how to scale and if sharding should be part of your solution.  Tom discussed the types of sharding lookup, range, and  mod sharding.  He also went over forms of caching: key/value (memcache) and structured (redis).

All in all a good overview of issues with scale and how sharding and cache can help and the pitfalls.

Improving Performance with Better Indexes – Percona Live 2015

moderndba MySQL , , ,

In the session titled “Improving Performance with Better Indexes” by Ronald Bradford from EffectiveMySQL went over a test case he had with a customer to improve the performance of an already indexed system.  Basically we learned how to use the USING INDEX information in the explain plan.  He has a 6 step process:

  • Capture
  • Identify
  • Confirm
  • Analyze
  • Optimize
  • Verify

Performance Schema provides 3 tables events_statements_…  you can use to identify queries.  Ronald also went over several other ways to log SQL Statements.  Then he discussed how to identify the queries to look at.  Slowest query is not the best place to start.  Instead you need to look at total duration for all queries with the same signature in a given time window.  This is the same issue and common mistake made by DBAs on all platforms.  He then went over the following commands:

  • EXPLAIN – The explain plan
  • SHOW CREATE TALBE – shows DDL to create a table
  • SHOW INDEXES FROM – shows statistics (cardinality only no histogram)
  • INFORMATION_SCHEMA.TABLES – shows size of data on disk
  • SHOW TABLE STATUS LIKE – also shows size of table on disk
  • EXPLAIN EXTENDED – adds more information to explain plan

Talked about optimized trace https://dev/mysql.com/doc/internals/en/optimizer-tracing.html very briefly.

When looking at explain key: NULL means it is not using an index.  row: tells you the number or rows compared.  Also, adds Using Index to the Extra:.  This means that we are using a covering index and are not having to go to the table to get information from the rows.  key_len tells us the number of bytes being used for comparison, so bigger the better in query tuning.  Covering indexes can be an issue when large columns are involved.  If it is too big then you will add I/O to use your indexes.  If they are small enough then you will be entirely in memory.

This session had some great information.  I will probably take it back and combine it with my SQL Server 12 step query tuning session to make a MySQL tuning 12 step presentation to give to our developers.

Pseudo GTID and easy replication management – Percona Live 2015

moderndba MySQL , ,

I attended the session titled “Pseudo GTID and easy replication management” by Shlomi Noach from Booking.com.  In this session Shlomi went over replication, GTID, then Pseudo GTID and some demos.

Pseudo GTID provides:

  • Slave repointing
  • Failover schemas
  • less requirements

Binary logs are pulled by the slave where it is written to the slaves relay logs.  Nothing ties these two together.  They can be flushed at different times, etc.  With log slave updates the relay logs are written to the slaves binary logs.  The problem only grows when you add multiple slaves and relay slaves.  GTID has the following properties

  • Every transaction has a unique identifier
  • When slave connects it looks for last GTID it already executed
  • Requires log slave updates (fixed in 5.7) on all slaves (Disk I/O)
  • Migration is all at once (master and slaves all at the same time)
  • Oracle GTID and MariaDB GTID are different implementations so can cause vendor lock-in

Pseudo GTID is an application side enhancement.  The application injects every few seconds which you can uniquely identify.  It is like “tracer tokens” in SQL Server replication.  This gives you something to measure where the tracer is in your environment.  This gives you information you can use to more easily identify how to reconnect a slave to a master.

Then Shlomi went over a tool called Orchestrator that is topology aware to help you manage replication.  Works with command line or web api.  He killed a live server as part to the demo.  That is some confidence in your tool and your presentation.  With Pseudo GTID it can automatically move slaves to a new candidate master based on a number of different factors.  Pretty impressive.

Next generation monitoring: moving beyond Nagios – Percona Live 2015

moderndba MySQL , ,

This session titled “Next generation monitoring: moving beyond Nagios” by Jenni Snyder and Josh Snyder at Yelp was my first session after lunch.  Always a tough time slot for speakers.  This session was on how they migrated from Nagios to Sensu.  Sensu is great because it is specialized vs. Nagios which does a lot of things well.  The session started with an overview of some of the pains associated with Nagios at Yelp.  Big pain points were related with scaling and a lack of HA.  Also pains associated with the Nagios GUI.  Issue if you acknowledge a warning it will not fire if then becomes critical.

Sensu uses the Nagios plugin interface.  Existing Nagios checks work in Sensu.  Sensu uses RabbitMQ to decouple the clients from the server.  You can use standalone checks (agent decides when to run them) or server directed checks.  Yelp uses Nagios and then Sensu to monitor Sensu.  They deploy Sensu using puppet and deploy each component 3 times.  They use HAProxy to handle failover.  They use puppet to configure the checks in Sensu.

Yelp uses Sensu for host-based condition monitoring not for graphing.  They have A LOT of checks!!!  They like the tight integration between Sensu and Puppet.  Sensu only run checks, generates events and then calls handlers.  So it does a lot less than Nagios but it does it really well.  Yelp has open sourced several of their handlers.  You can stash Sensu events or a host to disable checks for maintenance or reboots.  Handlers include PagerDuty, JIRA, IRC announcements, and email.  They also have handlers for Graphite, aws_prune, and OpsGenie.  They use Uchiwa as a GUI for Sensu.  They shared some information on how they build parameterization into checks.  They basically use config files and symlinks to achieve this.

Overall a good session.  We are pretty happy with Zenoss right now, but with the migration to Puppet Sensu may become a viable replacement.

An Introduction to Database as a Service with an emphasis on OpenStack using Trove – Percona Live 2015

moderndba MySQL, OpenStack , , , ,

My first real session of the conference was “An introduction to Database as a Service with an emphasis on OpenStack using Trove” by Amrith Kumar (Tesora) and Matt Griffin (Percona).  I had originally planned on attending “MySQL and Openstack deep dive” by Peter Boros (Percona), but after speaking with him last night in the vendor hall I realized that this session could have more immediate applicability to my team.

The session started with an overview of the cloud landscape which showed AWS and Microsoft at the top of the cloud market for Enterprises.  But there are a lot of other clouds that are getting evaluated and the market is growing fast and changing.  68% of enterprises have less than 20% of their application in a public cloud so a lot of room for growth.

Clouds provide a lot of value but this session concentrated on Speed, Low Cost, and Flexibility.  They pointed out that most of the cost savings come in the early phases of a project/application because you don’t have to oversize your infrastructure.  Database as a Service provides simplified database provisioning and the ability to manage at scale.  OpenStack and Trove help companies provide Database as a Service.

Next they provided a good overview of OpenStack and the services it provides.  Then they introduced Sahara (Hadoop as a Service) and Trove (Database as s Service).  Since this was a session on Trove there was very little information on Sahara.  Trove has to run ontop of OpenStack today, but there are discussions of breaking that dependency in the future.  Trove supports multiple databases (MySQL, PostgreSQL, MongoDB, Counchbase, ….).  It provides framework for higher level operations like backup and recovery, replication, clustering, HA, etc.  They are adding new database platforms with just about every release (twice a year release cycle).  The real value of Trove is that the configurations contributed are from industry experts.  Trove does not remove the need for expertise, but it frees organizations to roll out quicker because you can get expertise as you go.  This helps with dev ops because you don’t have to have a platform expert on each project.

Trove supports a number of different database platforms, but they are all at different levels of maturity.  There is some loss of control because o the standardization of instances, but Trove is open source so you can modify it as needed.  Configuration groups in Trove is one way you can modify the default behaviors.  Trove will add Galera clustering in Liberty.  Oracle support in Trove can be purchased.  Biggest advantage provided by Trove and OpenStack is a common API to provision resources from private, to public, to hybrid cloud deployments.

All in all this looks like a good project that we should dive into to see how it can increase our effectiveness.