MySQL

Percona Live 2017: Beringei – Facebook’s Open Source, In-Memory Time Series Database (TSDB)

MySQL Performance Blog - Thu, 2017-04-27 23:20

So that is just about a wrap here at Percona Live 2017 – except for the closing comments and prize giveaway. Before we leave, I have one more session to highlight: Facebook’s Beringei.

Beringei is Facebook’s open source, in-memory time series database. Justin Teller, Engineering Manager at Facebook, presented the session. According to Justin, large-scale monitoring systems cannot handle large-scale analysis in real time because the query performance is too slow. After evaluating and rejecting several disk-based and existing in-memory cache solutions, Facebook turned their attention to writing their own in-memory TSDB to power the health and performance monitoring system at Facebook. They presented “Gorilla: A Fast, Scalable, In-Memory Time Series Database (http://www.vldb.org/pvldb/vol8/p1816-teller.pdf)” at VLDB 2015.

In December 2016, they open sourced the majority of that work with Beringei (https://github.com/facebookincubator/beringei). In this talk, Justin started by presenting how Facebook uses this database to serve production monitoring workloads at Facebook, with an overview of how they use it as the basis for a disaster-ready, high-performance distributed system. He closed by presenting some new performance analysis comparing (favorably) Beringei to Prometheus. Prometheus is an open source TSDB whose time series compression was inspired by the Gorilla VLDB paper and has similar compression behavior.

After the talk, Justin was kind enough to speak briefly with me. Check it out:

It’s been a great conference, and we’re looking forward to seeing you all at Percona Live Europe!

Categories: MySQL

Percona Live 2017: Hawkular Metrics, An Overview

MySQL Performance Blog - Thu, 2017-04-27 22:23

The place is still frantic here at Percona Live 2017 as everybody tries to squeeze in a few more talks before the end of the day. One such talk was given by Red Hat’s Stefan Negrea on Hawkular Metrics.

Hawkular Metrics is a scalable, long-term, high-performance storage engine for metric data. The session was an overview of the project that includes the history of the project, an overview of the Hawkular ecosystem, technical details of the project, developer features and APIs and third party integrations.

Hawkular Metrics is backed by Cassandra for scalability. Hawkular Metrics is used and exposed by Hawkular Services.The API uses JSON to communicate with clients.

Users of Hawkular Metrics include:

  • IoT enthusiasts who need to collect metrics, and possibly trigger alerts
  • Operators who are looking for a solution to store metrics from statsD, collectd, syslog
  • Developers of solutions who need long-term time series database storage
  • Users of ManageIQ who are looking for Middleware management
  • Users of Kubernetes/Heapster who want to store Docker container metrics in a long-term time series database storage, thanks to the Heapster sink for Hawkular.

Stefan was kind enough to speak with me after the talk. Check it out below:

There are more talks today. Check out Thursday’s schedule here. Don’t forget to attend the Closing Remarks and prize give away at 4:00 pm.

Categories: MySQL

Percona Live 2017: Lessons Learned While Automating MySQL Deployments in the AWS Cloud

MySQL Performance Blog - Thu, 2017-04-27 19:37

The last day of Percona Live 2017 is still going strong, with talks all the way until 4:00 pm (and closing remarks and a prize giveaway on the main stage then). I’m going to a few more sessions today, including one from Stephane Combaudon from Slice Technologies: Lessons learned while automating MySQL deployments in the AWS Cloud.

In this talk, Stephane discussed how automating deployments is a key success factor in the cloud. It is actually a great way to leverage the flexibility of the cloud. But often while automation is not too difficult for application code, it is much harder for databases. When Slice started automating their MySQL servers at Slice, they chose simple and production-proven components: Chef to deploy files, MHA for high availability and Percona XtraBackup for backups. But they faced several problems very quickly:

  • How do you maintain an updated list of MySQL servers in the MHA configuration when servers can be automatically stopped or started?
  • How can you coordinate your servers for them to know that they need to be configured as a master or as a replica?
  • How do you write complex logic with Chef without being trapped with Chef’s two pass model?
  • How can you handle clusters with different MySQL versions, or a single cluster where all members do not use the same MySQL version?
  • How can you get reasonable backup and restore time when the dataset is over 1TB and the backups are stored on S3?

This session discussed the errors Slice made, and the solutions they found while tackling MySQL automation.

Stephane was kind enough to let me speak with him after the talk: check it out below:

There are more talks today. Check out Thursday’s schedule here. Don’t forget to attend the Closing Remarks and prize give away at 4:00 pm.

Categories: MySQL

Percona Live 2017: Day Three Keynotes

MySQL Performance Blog - Thu, 2017-04-27 17:58

Welcome to the third (and final) day of the Percona Live Open Source Database Conference 2017, and the third (and final) set of Percona Live keynotes! The enthusiasm hasn’t waned here at Percona Live, and we had a full house on Thursday morning!

Day three of the conference kicked off with three keynotes talks, and ended with the Community Awards Ceremony:

Spinaltap: Airbnb’s Change Data Capture System

Xinyao Hu (AirBnB)

In this talk, Xinyao introduced Airbnb’s change data change system, Spinaltap. He briefly covered its design, and focused on various use cases inside Airbnb. These use cases covered both online serving production and offline large distributed batch processing.

How Percona Contributes to the Open Source Database Ecosystem

Peter Zaitsev (Percona)

Peter Zaitsev, CEO of Percona, discussed the growth and adoption of open source databases, and Percona’s commitment to remaining an unbiased champion of the open source database ecosystem. Percona remains committed to providing open source support and solutions to its customers, users and the community. He also provided updates and highlighted exciting new developments in Percona Server software for MySQL and MongoDB.

Monitoring Booking.com without looking at MySQL

Jean-François Gagné (Booking.com)

Jean-François Gagné presented a fascinating talk about using a metric for observing Booking.com’s system health: bookings per second. It wasn’t a technical deep-dive (not MySQL- or Linux-related) but it is one of the most important metric Booking.com has to detect problems (and customer behavior) on the website. Many things impact this metric, including the time of the day, the day of the week or the season of the year.

Community Award Ceremony

Daniel Nichter (Square), Emily Slocombe (SurveyMonkey)

The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporations for their contributions to the MySQL ecosystem. It is a from-the-community, by-the-community and for-the-community effort. Awards are given for Community Contributor, Application, and Corporate Contributor. More information can be found here: http://mysqlawards.org.

This year’s winners were:

  • Community: René Cannaò, Simon Mudd, Shlomi Noach
  • Application: Sysbench, Gh-ost
  • Corporate: GitHub, Percona

Congrats to the winners, the entire open source community, and to all the Percona Live attendees this year. There are still sessions today, check them out.

It’s been a great conference, and we’re looking forward to seeing you all at Percona Live Europe!

Categories: MySQL

Percona Live 2017: MySQL Makes Toast

MySQL Performance Blog - Thu, 2017-04-27 03:56

Every day at Percona Live 2017 brings something new and unusual – and on this particular day, we found out that MySQL makes toast.

A lot of people think that with MySQL and open source software, you can do anything. While many might view this metaphorically, Percona’s Alexander Rubin (Principal Consultant) takes this statement very seriously. He demonstrated on Tuesday at Percona Live that not only is possible to accomplish just about anything with MySQL, but MySQL makes toast!

Originally, Alexander took on this project to provide an open source fix for MySQL Bug#2 (MySQL Doesn’t Make Toast). After some effort, and some ingenuity, he provided a patch for the infamous bug.

(You can find out all the details in his blog post here.)

Read up on how this was accomplished, and check out the pics below of Alexander demonstrating his ingenious method of grilling breakfast bread!

 

Alex Prepares to Amaze the Crowd with an Open Source Breakfast

The Crowd Gathers for a Tasty MySQL-Born Treat

Open Source Breakfast is Tiring, Time for a Rest

Don’t miss any of the fun tomorrow! You can find Thursday’s (4/27) session schedule here.

Categories: MySQL

Percona Live 2017: Database Management Made Simple – Amazon RDS

MySQL Performance Blog - Thu, 2017-04-27 03:28

Percona Live 2017 is done for Wednesday, but there was still time to get in one more talk before tonight’s Community Networking Reception – and the last one of the evening was about Amazon RDS.

Darin Briskman, Lead Developer Outreach & Technical Evangelist for Amazon, held two back-to-back sessions on Database management made simple – Amazon RDS. Amazon Relational Database Service (or Amazon RDS) is a distributed relational database service by Amazon Web Services (AWS).

Darin reviewed how Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. He showed how it provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you to focus on your applications and business. This talk provided guidance and tips for optimizing MySQL-compatible workloads on RDS.

Darin was kind enough to speak with me about his talk afterward. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

Categories: MySQL

Percona Live 2017: Histograms in MySQL and MariaDB

MySQL Performance Blog - Thu, 2017-04-27 00:00

The afternoon at Percona Live 2017 is slipping by quickly, and people are still actively looking for sessions to attend – like the session I just sat in on histograms in MySQL and MariaDB.

Histograms are a type of column statistic that provides more detailed information about data distributions in table columns. A histogram sorts values into buckets.

MariaDB Server has had histograms since MariaDB 10.0. Now, MySQL 8.0 will have them too. This session presented an overview of histogram implementations in MariaDB, MySQL 8.0, and looked at PostgreSQL for comparison. The session covered everything about histograms:

  • Why do query optimizers need histograms
  • What are the costs of collecting and maintaining a histogram in each database
  • How the query optimizers use histogram data
  • What are the strong and weak points of histogram implementation in each database

At the end, Sergei talked a bit about a related development in MariaDB Server: the optimizer will have the capability of using constraints.

Sergei was kind enough to speak briefly with me after his talk on histograms in MySQL and MariaDB. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

Categories: MySQL

Percona Live 2017: Deploying MongoDB on Public Clouds

MySQL Performance Blog - Wed, 2017-04-26 22:27

Today at Percona Live 2017, the afternoon is jam-packed with open source technology lectures filled with community members eager for the latest on the best strategies – including how you should deploy MongoDB on public clouds.

Dharshan Rangegowda (CEO of ScaleGrid) discussed deploying MongoDB on public clouds. ScaleGrid provides a fully managed Database-as-a-Service (DBaaS) solution used today by thousands of developers, startups, and enterprise customers. In this session, Dharshan talked about how public clouds like AWS and Azure have become very popular platforms over the past few years. These public clouds provide a plethora of infrastructure features to help make life easier, He dug into the features/assets that one should be actively leveraging.

On the flip side, there are also a number of potential pitfalls that require attention and might need a workaround. Dharshan reviewed some common architecture patterns you need to have in place to be successful with MongoDB on the public cloud, including high availability, disaster recovery, scaling, performance and others.

After the lecture, Dharshan was kind enough to talk briefly with me about his session. Check it out:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

Categories: MySQL

Percona Live 2017: A Deep-Dive Into What’s New in Amazon Aurora

MySQL Performance Blog - Wed, 2017-04-26 21:34

Percona Live 2017 is rolling along, and now that everybody got through lunch we’re all recharged and ready for the afternoon. Let’s start it out with Amazon Aurora.

Once of the best-attended sessions was Sailesh Krishnamurthy’s (Senior Engineering Manager at Amazon Web Services) talk on a deep dive into what is new in Amazon Aurora. Amazon Aurora is a fully managed relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. It is purpose-built for the cloud using a new architectural model and distributed systems techniques to provide far higher performance, availability and durability than previously possible using conventional monolithic database architectures.

Amazon Aurora packs a lot of innovations in the engine and storage layers. In this session, Sailesh looked at some of the key innovations behind Amazon Aurora, new improvements to Aurora’s performance, availability and cost-effectiveness and discussed best practices and optimal configurations.

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

Categories: MySQL

Percona Live 2017: Day Two Keynotes

MySQL Performance Blog - Wed, 2017-04-26 19:46

Welcome to the second day of the Percona Live Open Source Database Conference 2017, and the second set of Percona Live keynotes! It’s a bit rainy outside today, but that isn’t bothering the Percona Live attendees (we’re all indoors learning about new open source technologies)!

Day two of the conference kicked off with another four keynote talks, all of which discussed issues and technologies that are addressed by open source solutions:

The Open Source Database Business Model is Under Siege

Paul Dix (InfluxData)

Paul Dix’s keynote may have ruffled a few feathers, as he looked at possible futures for the open source software model (and community). The traditional open source infrastructure business model relied on the backing company providing either support and professional services or closed source tools to provide additional functionality like production tooling, enhanced security and monitoring. For a time this model was good, and it worked. But will it be feasible in the future to maintain this model, given the nature of how markets are developing? Paul discussed various reasons why he thinks the answer is “no.”

Keynote Panel (Percona, VividCortex, Continuent)

Richard Hipp (SQLite.org), Baron Schwartz (VividCortex), MC Brown(Continuent), Peter Zaitsev (Percona)

Join Percona and the Diamond sponsors of the conference, VividCortex and Continuent to talk database trends. They provided their thoughts on Paul’s talk (above), time series data and database development, whether SQL databases should continue to develop for “niche” functions and what we should be looking for at Percona Live 2020.

MySQL 8.0: Powering the next generation of Web, SaaS, Cloud

Tomas Ulin (Oracle)

Tomas Ulin, VP of MySQL Engineering at Oracle, provided this morning’s audience with a big picture overview of MySQL 8.0 development priorities, features that are available, and what is coming in upcoming releases. He covered MySQL 5.7, MySQL 8.0 and InnoDB Cluster. He also discussed MySQL’s roadmap and featured some benchmark performance comparisons.

The Future Of Monitoring Is Distributed

Baron Schwartz (VividCortex)

Baron Schwartz of VividCortex gave an insightful lecture on “observability” in the data layer. The world of technology is undergoing a rapid and permanent shift. He pointed out how we all know that we’re moving to the cloud at an accelerating pace. The fundamental change that’s taking place today is that our applications are far more distributed than they’ve ever been, in nearly invisible ways. And that’s not good, because invisible means unmeasurable. He posited that “observability” is the new goal for all the data that is available to businesses, and that businesses should make decisions based on what the data says about what customers want.

All the keynotes today highlighted the many different aspects of the open source database community that come together to solve database challenges. Percona Live runs through Thursday 4/27. Check out tomorrow’s keynotes here, as well as the numerous breakout sessions with top open source database experts.

Categories: MySQL

Migrate from TokuMX to Percona Server for MongoDB

MySQL Performance Blog - Wed, 2017-04-26 18:41

This blog post details how to migrate from TokuMX to Percona Server for MongoDB.

As part of our ongoing plans to embrace the MongoDB community, we have increased support and software around MongoDB. Percona’s last release of the TokuMX software platform was on September 15th, 2015.  Percona is announcing a General EOL of TokuMX Support as of May 1st, 2017.

If you are currently a MongoDB Support customer running TokuMX, we highly recommend migrating to Percona Server for MongoDB 3.4.x (and the WiredTiger or MongoRocks storage engine). We would be happy to assist in this migration through our regular Support and Consulting communications channels.

A Brief History of TokuMX

A group of engineers at TokuTek started the TokuMX project. The TokuFT fractal tree engine, now called PerconaFT, was already implemented as the MySQL storage engine plug-in known as TokuDB. The group needed new database technologies where a fractal tree might be of use. MongoDB 2.2 had serious performance limitations and inadequate concurrency and durability, making it a good candidate. The team committed the first work on TokuMX to the source tree in September of 2012. TokuMX v1.0 was released by June 2013.

When TokuMX was first implemented, there was no storage engine interface in the MongoDB code. This meant that the integration of the fractal tree required considerable changes to the base code of MongoDB 2.x. They made very impressive performance gains and implemented a full SQL-style stateful transaction interface. This allowed multi-operation, multi-document, and multi-collection concurrent transactions to remain consistent.

In mid-2014, MongoDB, Inc. began work on a storage engine API in the 2.7 development code base. A few months later, the MongoDB storage engine implementation of TokuFT began as a project known as TokuMXse. This became the basis for the PerconaFT storage engine in Percona Server for MongoDB 3.0. During this time, MongoDB, Inc. also worked closely with WiredTiger to implement their high-speed storage engine which also provided better concurrency and durability. In December of 2014, MongoDB, Inc. acquired WiredTiger. The WiredTiger storage engine was released with MongoDB 3.0.0 a few months later in March 2015.

The reason Percona Server for MongoDB does not implement stateful transactions:

Unfortunately, some core design decisions in MongoDB 3.x made it impractical to implement the stateful transactions that SQL databases and TokuMX have. The MongoDB 3.x storage engine API is very closely tied to a concurrency model known as optimistic concurrency. This is implemented using C++ exceptions which throw and retry a single operation until it doesn’t conflict with another. This strategy has advantages and disadvantages. Developers don’t need to worry about implementing advanced transaction handlers for deadlocks and rollbacks. However, MongoDB doesn’t support transactions that involve multiple operations, documents or collections defined and controlled by the user application. MongoDB can’t implement this without far-reaching modifications to the MongoDB code base.

Without a major redesign of the MongoDB 3.x storage engine layer and the higher level database code, SQL style stateful transactions will not be possible. The MongoDB 3.x design also dramatically reduced the performance of the fractal tree engine which is designed around the traditional ACID model and does not support optimistic concurrency. As a result, we deprecated the fractal tree engine (PerconaFT) and removed it from Percona Server for MongoDB 3.4.

Performing a Migration

There are a few things to consider before beginning migration. Percona Server for MongoDB doesn’t implement a few TokuMX features.

Some TokuMX commands that are not available in PSMDB

  •  Stateful transaction commands
    • beginTransaction
    • rollbackTransaction
    • commitTransaction
  • Bulk loader commands
    • beginLoad
    • commitLoad
    • abortLoad
  • Partition commands
    • addPartition
    • dropPartition
    • getPartitionInfo
    • replAddPartition
    • clonePartitionInfo

If your application depends on these features, migration may require some changes to function correctly.  You will also want to review the upstream MongoDB 2.x to 3.x documentation as well as the custom commands section of the TokuMX documentation to identify differences between the versions.

There are two methods to migrate your data from TokuMX to Percona Server for MongoDB. We’ve documented the methods in a Percona Lab GitHub repository.

If you have any questions or concerns about your TokuMX to Percona Server for MongoDB migration, please contact Percona Support.  We are here to help 24x7x365 online or by phone.

Categories: MySQL

Percona Live 2017: Real-Time Data Loading from MySQL and Oracle into Analytics/Big Data

MySQL Performance Blog - Wed, 2017-04-26 01:10

It looks like the first day of the Percona Live Open Source Database Conference 2017 is coming to a close. Before we shut it down for today, we’ll look at a presentation on Real-Time Data Loading from MySQL and Oracle into Analytics/Big Data.

In this session, Continuent’s VP of Products MC Brown looked at Tungsten Replicator, which enables real-time and efficient replication of data from your transactional database. He focused on the filtering side, for massaging your data before/during replication.

Continuent is back with their Tungsten Replicator product, after splitting off from VMware. You can learn more about that process from the earlier keynote session today.

During this session, MC covered various solutions used by Continuent’s customers, some of the complex deployment models, and how that information can be modified as part of the load into analytics targets. He also looked into how to replicate to non-transactional environments and how to customize and develop your own appliers and customizations. This included an examination of the applier system and the JavaScript environment for batch applications.

MC was kind enough to speak with me after his session. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Wednesday’s (4/26) session schedule here.

Categories: MySQL

Percona Live 2017: Designing your SaaS Database for Scale with Postgres

MySQL Performance Blog - Tue, 2017-04-25 23:29

The Percona Live Open Source Data Conference 2017 day one is rolling right along, and we’re already in the afternoon sessions. In this blog, we’ll look at Citus Data’s presentation on how to design your SaaS database to scale with Postgres.

If you’re building a SaaS application, you probably already have the notion of tenancy built in your data model. Typically, most information relates to tenants/customers/accounts and your database tables capture this natural relation. With smaller amounts of data, it’s easy to throw more hardware at the problem and scale up your database. As these tables grow, however, you need to think about ways to scale your multi-tenant database across dozens or hundreds of machines. In this talk, Citus Data’s Lukas Fittl and Ozgun Erdogan (CTO) talked about the motivations behind scaling your SaaS (multi-tenant) database and several heuristics they found helpful in deciding when to scale.

They then described three design patterns that are common in scaling SaaS databases:

  1. Create one database per tenant
  2. Create one schema per tenant
  3. Have all tenants share the same table(s).

Next, they highlighted the tradeoffs involved with each design pattern and focused on one pattern that scales to hundreds of thousands of tenants. They also shared an example architecture from the industry that describes this pattern in more detail. Lastly, they talked about key PostgreSQL properties, such as semi-structured data types, that make building multi-tenant applications easy.

After the talk, Lukas and Ozgun were kind enough to speak with me about their session. Check it out below:

Don’t miss any of tomorrow’s session. See Wednesday’s (4/26) full schedule here.

Categories: MySQL

Percona Live 2017 – MySQL 8.0: Major New Features

MySQL Performance Blog - Tue, 2017-04-25 21:35

Breakout sessions are in full swing at the Percona Live Open Source Database Conference 2017. Check out what’s new in MySQL 8.0.

We’ve finished with this morning’s sessions, and this morning saw a lot of amazing open source talks. One of the most well-attended was MySQL 8.0: Major New Features given by Geir Høydalsvik, Senior Software Development Director at Oracle. MySQL is the next major version of the MySQL platform from Oracle, the open source community is expectantly waiting for the official release.

In this session, Geir described many of the new features announced for MySQL 8.0. In addition to Data Dictionary, CTEs and Windows functions, the session covered:

  • Move to utf8 (mb4) as MySQL’s default character set
  • Language specific case insensitive collation for 21 languages (utf8)
  • Invisible index
  • Descending indexes
  • Improve usability of UUID and IPV6 manipulations
  • SQL roles
  • SET PERSIST for global variable values
  • Performance Schema, instrumenting data locks
  • Performance Schema, instrumenting error messages
  • Improved cost model with histograms

The presentation ended with some words on scalability, plugin infrastructure, and GIS.

After the talk, Geir was kind enough to speak with me for a minute or so about his talk. Check out the video below to see MySQL’s 8.0 features.

Don’t miss tomorrow’s sessions! You can check out the schedule for tomorrow (4/26) here.

Categories: MySQL

Percona Live 2017: Day One Keynotes

MySQL Performance Blog - Tue, 2017-04-25 20:44

Welcome to the first day of the Percona Live Open Source Database Conference 2017, and the first set of Percona Live keynotes!

It’s a beautiful day in Santa Clara, but we don’t know because we’re inside the Hyatt Regency Convention Center listening to various rock stars in the open source database community talk about open source technologies. Day one of the conference kicked off with four keynote talks, all of which discussed issues and technologies that are addressed by open source solutions:

Percona Welcoming Keynote

Peter Zaitsev (Percona)

Peter Zaitsev, CEO of Percona, welcomed everyone to Percona Live Open Source Database Conference 2017 and discussed the history of Percona Live. Percona Live has grown significantly over the years, changing from just a MySQL conference into an open source database technology conference. This change has mirrored the growth and acceptance of open source technologies in what traditionally were commercial marketplaces.

Continuent is back! But what does Continuent do Anyway?

Eero Teerikorpi, MC Brown (Continuent)

Eero Teerikorpi from Continuent discussed how Continuent as a company has developed over the years – from startup to acquisition by VMware back to separate entity, with regards to its Tungsten database replication and clustering product. Eero explained how Continuent’s swan logo represents it’s long relationship it has had with its customers, and how its Tungsten product is an outstanding replication solution. Eero asked select Continuent customers to tell everyone about how they use their multi-site/multi-master and advanced replication solutions, and explain how Tungsten helped them solve database replication issues.

Open Source Database Ecosystem

Peter Zaitsev (Percona), Colin Charles (Percona), Dmitry Andreev (Yandex), Justin Teller (Facebook), Tal Levy (Elastic Search), Björn Rabenstein (SoundCloud Ltd.), Paul Dix (InfluxData), Michael J. Freedman (TimescaleDB)

For our third keynote, we had a panel of speakers from a variety of open source companies: Yandex, Facebook, Elastic Search, SoundCloud, InfluxData,TimescaleDB and of course Percona. The topic was the explosion of time series data. Percona sees time series databases as a trend of 2017, hence the idea of having quick 5-minute lightning talks from projects that are stellar. With the increasing availability of IoT devices and the data they provide, time series data is more and more important in the database landscape. All of these companies provide people with an interest in capturing, monitoring, and analyzing time series data could use their various solutions for that purpose.

SQLite: The Most Important Software Component That Many People Have Never Heard Of

Richard Hipp (SQLite.org)

The final keynote this morning was a discussion with the creator of SQLite, one of the most used RDBMS. Rather than use a client-server model, SQLite is embedded in the application. There are more instances of SQLite running today than all other database engines combined. This talk reviewed the features, capabilities, limitations, and usage patterns of SQLite and asks why you are not using SQLite more yourself.

All the keynotes today highlighted the many different aspects of the open source database community that come together to solve database challenges. Percona Live runs through Thursday 4/27. Check out tomorrow’s keynotes here, as well as the numerous breakout sessions with top open source database experts.

Categories: MySQL

Percona Live 2017 Tutorials Day

MySQL Performance Blog - Tue, 2017-04-25 05:38

Welcome to the first day of the Percona Live Open Source Database Conference: Percona Live 2017 tutorials day! While technically the first day of the conference, this day focused on provided hands-on tutorials for people interested in learning directly how to use open source tools and technologies.

Today attendees went to training sessions taught by open source database experts and got first-hand experience configuring, working with, and experimenting with various open source technologies and software.

The first full day (which includes opening keynote speakers and breakout sessions) starts Tuesday 4/25 at 9:00 am.

Some of the tutorial topics covered today were:

MySQL Performance Schema in Action

Sveta Smirnova, Alexander Rubin

Performance Schema in MySQL is becoming more mature from version to version. In version 5.7, it includes extended lock instrumentation, memory usage statistics, new tables for server variables, first time ever instrumentation for user variables, prepared statements and stored routines. In this tutorial Sveta and Alexander helped the participants try these new instruments out. They provided a test environment and a few typical problems that were hard to solve before MySQL 5.7.

Just a few examples: “Where is memory going?” , “Why do these queries hang?”, “How huge is the overhead of my stored procedures?”, “Why are queries waiting for metadata locks?”. Attendees learned how to collect and use this information.

Best Practices for MySQL High Availability in 2017

Colin Charles

The MySQL world is full of tradeoffs, and choosing a high availability (HA) solution is no exception. This session aims to look at all of the alternatives in an unbiased nature. Topics covered included but weren’t limited to MySQL replication, MHA, DRBD, Tungsten Replicator, Galera Cluster, NDB Cluster, Vitess, etc. The focus of the talk was what is recommended for today, and what to look out for.

InnoDB Architecture and Performance Optimization

Peter Zaitsev

InnoDB is the most commonly used storage engine for MySQL and Percona Server and is the focus for the majority of storage engine development by the MySQL and Percona Server teams. This tutorial looked at the InnoDB architecture, including new developments in MySQL 5.6 as well as Percona Server. It provided specific advice on server configuration, schema design, application architecture, and hardware choices.

MongoDB 101: What NoSQL is All About

Jon Tobin, Rick GolbaBarrett Chambers

MongoDB is quickly becoming one of the NoSQL standards, but represents a very different way of thinking from traditional RDBMSs. Many database users tend to think of things from the perspective of the transactional DBs that they know and love, but there are other ways of doing things. The Percona Solutions Engineering team helped attendees fill out their database resume and become a more knowledgeable user by showing them the basics. This tutorial gave users with little or no experience with NoSQL databases an introduction to MongoDB.

Join us tomorrow for the first full day of the Percona Live Open Source Database Conference 2017!

Categories: MySQL

Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster

MySQL Performance Blog - Mon, 2017-04-24 21:22

In this blog post, we’ll look at how we’ve improved wsrep-stages and related instrumentation in Percona XtraDB Cluster.

Introduction

When you execute a workload and need to find out what the given thread is working on, “SHOW PROCESSLIST” comes to the top of your mind. It is an effective way to track the thread status. We decided to improve the stages in Percona XtraDB Cluster to make “SHOW PROCESSLIST” more meaningful.

In the blog below, we will check out the different wsrep-stages and the significance associated with them.

Loading of data

Running a simple insert/sysbench prepare workload. The state is stable as it mainly captures MySQL stages indicating that the table is being updated:

| 9 | root | localhost | test | Query | 0 | update | INSERT INTO sbtest3(id, k, c, pad) VALUES(893929,515608,'28459951974-62599818307-78562787160-7859397 | 0 | 0 |

Running UPDATE workload

Running simple sysbench update-key workload. Let’s look at the different states that the user sees and their significance. (MASTER and SLAVE states are different and are marked accordingly.)

MASTER view:

  • This stage indicates that the write-set is trying to replicate. Global sequence numbers are assigned after the write-set is replicated and so the global-seqno is currently -1:

| 80 | root | localhost | test | Query | 0 | wsrep: initiating replication for write set (-1) | UPDATE sbtest4 SET k=k+1 WHERE id=502338 | 0 | 1 |

  • This stage indicates successful replication of the write-set. This means the write-set is now added to the group-channel. Global-seqno is updated in the message too:

| 79 | root | localhost | test | Query | 0 | wsrep: write set replicated (196575) | UPDATE sbtest3 SET k=k+1 WHERE id=502723 | 0 | 1 |

  • This stage indicates the write-set has successfully passed the certification stage (making its path clear for commit):

| 85 | root | localhost | test | Query | 0 | wsrep: pre-commit/certification passed (196574) | UPDATE sbtest7 SET k=k+1 WHERE id=495551 | 0 | 1 |

  • This stage indicates that InnoDB commit has been triggered for the write-set:

| 138 | root | localhost | test | Query | 0 | innobase_commit_low (585721) | UPDATE sbtest6 SET k=k+1 WHERE id=500551 | 0 | 1 |

SLAVE/Replicating node view:

  • This stage indicates that the slave thread is trying to commit the replicated write-set with the given seqno. It is likely waiting for its turn of the CommitMonitor:

|  6 | system user |           | NULL | Sleep   |    0 | wsrep: committing write set (224905) | NULL             |         0 |             0 |

  • This stage indicates a successful commit of the replicated write-set with the given seqno:

| 2 | system user | | NULL | Sleep | 0 | wsrep: committed write set (224896) | NULL | 0 | 0 |

  • This stage indicates that updating the rows is in progress. (Often it was difficult to know what the workload is trying to do: UPDATE/INSERT/DELETE.) Now there is an easy way to find out:

| 13 | system user |           | NULL | Sleep   |    0 | wsrep: updating row for write-set (178711) | NULL             |         0 |             0 |

| 18 | system user | | NULL | Sleep | 0 | wsrep: writing row for write-set (793454) | NULL | 0 | 0 |

| 11 | system user | | NULL | Sleep | 0 | wsrep: deleting row for write-set (842123) | NULL | 0 | 0 |

  • This stage indicates that the given write-set is being applied:

| 10 | system user | | NULL | Sleep | 0 | wsrep: applying write-set (899370) | NULL | 0 | 0 |

Improved Instrumentation

Let’s answer some simple questions that most profiling experts will face:

  • How long did replication take (adding write-set to channel)?

mysql> select event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%in replicate%' order by time_in_mics desc limit 5; +---------------------------------------+--------------+ | event_name | time_in_mics | +---------------------------------------+--------------+ | stage/wsrep/wsrep: in replicate stage | 1.2020 | | stage/wsrep/wsrep: in replicate stage | 0.7880 | | stage/wsrep/wsrep: in replicate stage | 0.7740 | | stage/wsrep/wsrep: in replicate stage | 0.7550 | | stage/wsrep/wsrep: in replicate stage | 0.7480 | +---------------------------------------+--------------+ 5 rows in set (0.01 sec)

  • How long did it take for pre-commit/certification checks?

mysql> select event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%in pre-commit%' order by time_in_mics desc limit 5; +----------------------------------------+--------------+ | event_name | time_in_mics | +----------------------------------------+--------------+ | stage/wsrep/wsrep: in pre-commit stage | 1.3450 | | stage/wsrep/wsrep: in pre-commit stage | 1.0000 | | stage/wsrep/wsrep: in pre-commit stage | 0.9480 | | stage/wsrep/wsrep: in pre-commit stage | 0.9180 | | stage/wsrep/wsrep: in pre-commit stage | 0.9030 | +----------------------------------------+--------------+ 5 rows in set (0.01 sec)

  • How long did it take to commit a transaction on the slave (slave_thread=16 threads)?

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%committing%' order by time_in_mics desc limit 5; +-----------+-------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+-------------------------------+--------------+ | 56 | stage/wsrep/wsrep: committing | 0.5870 | | 58 | stage/wsrep/wsrep: committing | 0.5860 | | 47 | stage/wsrep/wsrep: committing | 0.5810 | | 59 | stage/wsrep/wsrep: committing | 0.5740 | | 60 | stage/wsrep/wsrep: committing | 0.5220 | +-----------+-------------------------------+--------------+ 5 rows in set (0.00 sec)

  • Increasing the number of slave thread creates more contention (slave_thread=64):

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%committing%' order by time_in_mics desc limit 5; +-----------+-------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+-------------------------------+--------------+ | 90 | stage/wsrep/wsrep: committing | 1.6930 | | 97 | stage/wsrep/wsrep: committing | 1.5870 | | 103 | stage/wsrep/wsrep: committing | 1.5140 | | 87 | stage/wsrep/wsrep: committing | 1.2560 | | 102 | stage/wsrep/wsrep: committing | 1.1040 | +-----------+-------------------------------+--------------+ 5 rows in set (0.00 sec)

  • The amount oftTime taken to apply a write-set:

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%applying%' order by time_in_mics desc limit 5; +-----------+---------------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+---------------------------------------+--------------+ | 166 | stage/wsrep/wsrep: applying write set | 1.6880 | | 168 | stage/wsrep/wsrep: applying write set | 1.5820 | | 146 | stage/wsrep/wsrep: applying write set | 1.5270 | | 124 | stage/wsrep/wsrep: applying write set | 1.4760 | | 120 | stage/wsrep/wsrep: applying write set | 1.4440 | +-----------+---------------------------------------+--------------+ 5 rows in set (0.00 sec)

Conclusion

The improved wsrep-stage framework makes it more effective for a user to find out the state of a given thread. Using the derived instrumentation through wsrep-stage is a good way to understand where the time is being spent.

Categories: MySQL

Percona XtraDB Cluster: SST tmpdir option

MySQL Performance Blog - Mon, 2017-04-24 20:46

In this blog post, I’ll discuss some changes to the behavior of the Percona XtraDB Cluster SST tmpdir option in the latest versions of Percona XtraDB Cluster 5.6.35-26.20-3 and 5.7.17-29.30.

Previously, we did not use the path specified by the tmpdir. From Percona XtraDB Cluster 5.6.35-26.20-3 and 5.7.17-29.20, we use the tmpdir option to specify the location of temporary files used by the SST (on the DONOR this may be very large since the backup logs may be stored here).

Specifying the tmpdir is as expected. You supply the directory path, as in the following example:

[sst] tmpdir=/path/to/tmp/dir/

We look for the tmpdir in the [sst], [xtrabackup], and [mysqld] sections, in that order. If left unspecified, “mktemp” follows the default behavior (on Linux distributions this will typically create the directory in $TMPDIR, followed by /tmp).

Normal security settings and permissions apply here. The directory must already exist and be readable and writable by MySQL otherwise a fatal error will be generated by the SST script.

Categories: MySQL

Percona XtraDB Cluster: “dh key too small” error during an SST using SSL

MySQL Performance Blog - Sun, 2017-04-23 16:16

If you’ve tried to use SSL in Percona XtraDB Cluster and saw an error in the logs like SSL3_CHECK_CERT_AND_ALGORITHM:dh key too small, we’ve implemented some changes in Percona XtraDB Cluster 5.6.34 and 5.7.16 that get rid of these errors.

Some background

dh key too small refers to the Diffie-Hellman parameters used by the SSL code that are shorter than recommended.

Due to the Logjam vulnerability (https://weakdh.org/), the required key-lengths for the Diffie-Hellman parameters were changed from 512 bits to 2048 bits. Unfortunately, older versions of OpenSSL/socat still use 512 bits (and thus caused the error to appear).

Changes made to Percona XtraDB Cluster

Since versions of socat greater than 1.7.3 now use 2048 bits for the Diffie-Hellman parameters, we only do extra work for the older versions of socat (less than 1.7.3). The SST code now:

  1. Looks for a file with the DH params
    1. Uses the “ssl_dhparams” option in the [sst] section if it exists
    2. Looks for a “dhparams.pem” file in the datadir
  2. If the file is specified and exists, uses that file as a source for the DH parameters
  3. If the file does not exist, creates a dhparams.pem file in the datadir
Generating the dhparams yourself

Unfortunately, the time it can take several minutes to create the dhparams file. We recommend that the dhparams.pem be created prior to starting the SST.

openssl dhparam -out path/to/datadir/dhparams.pem 2048

Categories: MySQL

Percona XtraDB Cluster Transaction Replay Anomaly

MySQL Performance Blog - Sun, 2017-04-23 16:05

In this blog post, we’ll look at a transaction replay anomaly in Percona XtraDB Cluster.

Introduction

Percona XtraDB Cluster/Galera replays a transaction if the data is non-conflicting but, the transaction happens to have conflicting locks.

Anomaly

Let’s understand this with an example:

  • Let’s assume a two-node cluster (node-1 and node-2)
  • Base table “t” is created as follows:

create database test; use test; create table t (i int, c char(20), primary key pk(i)) engine=innodb; insert into t values (1, 'abc'), (2, 'abc'), (4, 'abc'); select * from t; mysql> select * from t; +---+------+ | i | c | +---+------+ | 1 | abc | | 2 | abc | | 4 | abc | +---+------+

  • node-2 starts runs a transaction (trx-2):

trx-2: update t set c = 'pqr';

  • node-2 creates a write-set and is just about to replicate it. At the same time, node-1 executes the following transaction (trx-1), and is first to add it to the group-channel (before node-2 adds transaction (trx-2))

trx-1: insert into t values (3, 'a');

  • trx-1 is replicated on node-2, and it proceeds with the apply action. Since there is a lock conflict (no certification conflict), node-2 local transaction (trx-2) is aborted and scheduled for replay.
  • trx-1 causes addition of (3, ‘a’) and then node-2 transaction is REPLAYed.
  • REPLAY is done using the pre-created write-set that only modifies existing entries (1,2,4).

End-result:

mysql> select * from t; +---+------+ | i | c | +---+------+ | 1 | pqr | | 2 | pqr | | 3 | a | | 4 | pqr | +---+------+

  • At first, nothing looks wrong. If you look closely, however, the REPLAYed transaction “UPDATE t set c= ‘pqr'” is last to commit. But the effect of it is not seen as there is still a row (3, ‘a’) that has ‘a’ instead of ‘pqr’.

| mysql-bin.000003 | 792 | Gtid | 2 | 857 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:4' | | mysql-bin.000003 | 857 | Query | 2 | 925 | BEGIN | | mysql-bin.000003 | 925 | Table_map | 2 | 972 | table_id: 219 (test.t) | | mysql-bin.000003 | 972 | Write_rows | 2 | 1014 | table_id: 219 flags: STMT_END_F existing| | mysql-bin.000003 | 1014 | Xid | 2 | 1045 | COMMIT /* xid=4 */ | | mysql-bin.000003 | 1045 | Gtid | 3 | 1110 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:5' | | mysql-bin.000003 | 1110 | Query | 3 | 1187 | BEGIN | | mysql-bin.000003 | 1187 | Table_map | 3 | 1234 | table_id: 219 (test.t) | | mysql-bin.000003 | 1234 | Update_rows | 3 | 1324 | table_id: 219 flags: STMT_END_F | | mysql-bin.000003 | 1324 | Xid | 3 | 1355 | COMMIT /* xid=5 */ | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------+ 21 rows in set (0.00 sec)

  • We have used a simple char string, but if there is a constraint here, like c should have X after UPDATE is complete, than the CONSTRAINT will be violated even though the application reports UPDATE as a success.
  • Is it interesting to note what happens on node-1:
    • node-1 applies the local transaction (trx-1) and then gets the replicated write-set from node-2 (trx-2) that has changes only for (1,2,4). Thereby data consistency is not compromised.
Categories: MySQL
Syndicate content