MySQL High Performance

Syndicate content
Percona's Data performance and scalability blog
Updated: 17 hours 50 min ago

Upgrading to MySQL 5.7, focusing on temporal types

Mi, 2016-04-27 20:30

In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.

MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?

Are they converted automatically to the new format?

If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we run mysql_upgrade, it does not warn us about the old format. If we check the MySQL error log, we cannot find anything regarding this. But the newly created tables are going to use the new format so that we will have two different types of temporal fields.

How can we find these tables?

The following query gives us a summary on the different table formats:

SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, count(*) TOTAL FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id AND isc.name = c.column_name WHERE c.column_type IN ('time','timestamp','datetime') AND t.table_schema NOT IN ('mysql','information_schema','performance_schema') AND t.table_type = 'base table' AND (t.engine = 'innodb') GROUP BY isc.mtype;

+--------+-------+ | FORMAT | TOTAL | +--------+-------+ | NEW | 1     | | OLD | 9 | +--------+-------+

Or we can use show_old_temporals, which will highlight the old formats during a show create table.

CREATE TABLE `mytbl` ( `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP, `dt` datetime /* 5.5 binary format */ DEFAULT NULL, `t` time /* 5.5 binary format */ DEFAULT NULL ) DEFAULT CHARSET=latin1

MySQL can handle both types, but with the old format you cannot use microseconds, and the default DATETIME takes more space on disk.

Can I upgrade to MySQL 5.7?

Of course you can! But when mysql_upgrade is running it is going to convert the old fields into the new format by default. This basically means an alter table on every single table, which will contain one of the three types.

Depending on the number of tables, or the size of the tables, this could take hours – so you may need to do some planning.

.... test.t1 error : Table rebuild required. Please do "ALTER TABLE `t1` FORCE" or dump/reload to fix it! test.t2 error : Table rebuild required. Please do "ALTER TABLE `t2` FORCE" or dump/reload to fix it! test.t3 error : Table rebuild required. Please do "ALTER TABLE `t3` FORCE" or dump/reload to fix it! Repairing tables mysql.proxies_priv OK `test`.`t1` Running : ALTER TABLE `test`.`t1` FORCE status : OK `test`.`t2` Running : ALTER TABLE `test`.`t2` FORCE status : OK `test`.`t3` Running : ALTER TABLE `test`.`t3` FORCE status : OK Upgrade process completed successfully. Checking if update is needed.

Can we avoid this at upgrade?

We can run alter tables or use pt-online-schema-schange (to avoid locking) before an upgrade, but even without these preparations we can still avoid incompatibility issues.

My colleague Daniel Guzman Burgos pointed out that mysql_upgrade has an option called upgrade-system-tables. This will only upgrade the system tables, and nothing else.

Can we still write these fields?

The following query returns the schema and the table names that still use the old formats.

SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, t.schema_name, t.table_name FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id AND isc.name = c.column_name WHERE c.column_type IN ('time','timestamp','datetime') AND t.table_schema NOT IN ('mysql','information_schema','performance_schema') AND t.table_type = 'base table' AND (t.engine = 'innodb');

+--------+--------------+------------+ | FORMAT | table_schema | table_name | +--------+--------------+------------+ | OLD | test | t | | OLD | test | t | | OLD | test | t | | NEW | sys | sys_config | +--------+--------------+------------+ 4 rows in set (0.03 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.11-4 | +-----------+ 1 row in set (0.00 sec)

As we can see, we’re using 5.7 and table “test.t” still has the old format.

The schema:

CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `t1` time DEFAULT NULL, `t2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `t3` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | +----+----------+---------------------+---------------------+

Let’s try to insert a new row:

mysql> insert into `t` (t1,t3) values ('20:28','2016:04:23 22:22:06'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | | 6 | 20:28:00 | 2016-04-09 01:56:38 | 2016-04-23 22:22:06 | +----+----------+---------------------+---------------------+ 6 rows in set (0.00 sec)

It was inserted without a problem, and we can’t see any related info/warnings in the error log.

Does the Replication work?

In many scenarios, when you are upgrading a replicaset, the slaves are upgraded first. But will the replication work? The short answer is “yes.” I configured row-based replication between MySQL 5.6 and 5.7. The 5.6 was the master, and it had all the temporal types in the old format. On 5.7, I had new and old formats.

I replicated from old format to old format, and from old format to new format, and both are working.

Conclusion

Before upgrading to MySQL 5.7, tables should be altered to use the new format. If it isn’t done, however, the upgrade is still possible without altering all the tables – the drawbacks are you cannot use microseconds, and it takes more space on disk. If you had to upgrade to 5.7, however, you could change the format later using alter table or pt-online-schema-schange.

 

How We Made MySQL Great Again, or Upgrading MySQL with Orchestrator

Di, 2016-04-26 19:56

In this blog post, we’ll discuss upgrading MySQL with Orchestrator.

I recently had a client, Life360, that wanted to upgrade from Percona Server 5.5 to Percona Server 5.6, and implement GTID in their high transaction environment. They had co-masters and multiple read slaves.

Orchestrator made this job much easier for us. My colleague, Tibi, recently posted about Orchestrator here and here.

Daniel from Life360 saw Orchestrator and was very interested. So here is how he setup Orchestrator in his own words:

I did some initial testing with the vagrant boxes provided in the Orchestrator repo, to see how to configure the agents and get the Orchestrator server to do what we want.

I then moved to install the Orchestrator server, Orchestrator backend on RDS, and deploy the clients on the slaves and masters in our Amazon VPC MySQL instances.

Once the server setup was done, the clients were auto-detected through CNAME discovery of the masters, and the agents talked to the server (it took a while as CNAMES wasn’t working as expected, but that’s fixed in the new server version).

We were pretty amazed at the number of actions you can do through orchestrator itself, such as: moving slaves to a different master through drag and drop, enabling GTID on a node with the push of a button, setting up GTID based failover, taking LVM snapshots using Orchestrator Agent, etc.

We went ahead and tested the master change on drag and drop, and after a few successful attempts, we even brought it back to where it was initially. After those tests, we were pretty confident that we could leverage Orchestrator as one of our main tools to assist in the coming upgrade.

Here is a screenshot of the initial setup:

Manjot: Once Daniel had Orchestrator setup, he wanted to leverage it to help with the MySQL upgrade. We set out to create a plan that worked within his constraints and still kept best practices in mind.

First, we installed Percona Server 5.6 fresh on our dedicated backup slave. That first 5.6 slave was created with MyDumper to achieve forward compatibility and not have any legacy tablespaces. Since MyDumper was already installed with the Percona Backup Service that Life360 has, this was fairly easy to accomplish.

The MyDumper slave rebuild works in the following way:

To take a mydumper backup:

  1. Go to your desired backups directory
  2. Install mydumper (sudo apt-get install mydumper)
  3. mydumper -t 8 -L mydumper.log –compress

To restore:

  1. Make sure MyDumper is installed: sudo apt-get install mydumper
  2. Copy the MyDumper backups over to a backups dir
  3. Export your BACKUP_DIR as env var
  4. Run this to restore with MyLoader (from https://gist.github.com/Dnile/4658b338d4a101cbe2eeb5080ebddf8e):
    #!/usr/bin/env sh cd $BACKUP_DIR export DESTHOST=127.0.0.1 export BACKUP_DIR=/vol_mysql/backups mysqld --skip-grant-tables & for i in `ls -1 *-schema.dump.gz | cut -d'-' -f1`; do mysql -h $DESTHOST -e "CREATE DATABASE IF NOT EXISTS $i"; zcat $i-schema.dump.gz | mysql -h $DESTHOST $i; zcat $i-schema-post.dump.gz | mysql -h $DESTHOST $i; done /usr/bin/myloader --host=$DESTHOST --directory=$BACKUP_DIR --enable-binlog --threads=10 --queries-per-transaction=20 -v 3 chown -R mysql:mysql /var/lib/mysql/

Once the first 5.6 slave was caught up, we used Xtrabackup to backup 5.6 and then restored to each slave, cycling them out of the read slave pool one at a time.

Once all the slaves were upgraded, we created a new 5.6 master and had it replicate off our primary 5.5 master.

Then we moved all of the slaves to replicate off the new 5.6 master.

Life360 had long cron jobs that ran on the second 5.5 master. We moved the cron applications to write to the primary 5.5 master, and locked all tables. We then stopped replication on the second co-master. Daniel stopped MySQL and decommissioned it.

We then moved all application writes to the new 5.6 master. While Orchestrator can use external scripts to move IPs, we used a manual process here to change application DSNs and HAProxy configuration.

On the 5.5 master that remained, we used Orchestrator to set it to read only.

Daniel says this didn’t do a whole lot to get rid of connections that were still open on this server.

On the new master, we used the stop slave and reset slave buttons in the Orchestrator panel so it would no longer slave from the old master.

Once some of the thousands of connections had moved to the new master, we stopped MySQL on the 5.5 master, which took care of the rest and the application “gracefully” reconnected to the new 5.6 master.

There was some write downtime, as some connections did not drop off until they were forced to because php-fpm refused to let go. There is also always a high volume of transactions in this environment.

At this point our topology looks like this (ignore the globe icons for now):

But as always Daniel wanted MOAR. It was time for GTID. While we could have done this during the upgrade, Life360 wanted to manage risk and not make too many production changes at one time.

We followed Percona’s guide, Online GTID Deployment, but used Orchestrator to shuffle the old and new masters and toggle read_only on and off. This made our job a lot easier and faster, and saved us from any downtime.

The globes in the topology screenshot above show that the slaves are now using GTID replication.

Orchestrator makes upgrades and changes much easier than before, just use caution and understand what it is doing in the background.

 

Percona Live 2016: Closing Comments and Prize Giveaway

Fr, 2016-04-22 00:04

Well, that is it for Percona Live 2016! This year was bigger and better than last year and included more sponsors, speakers, technologies, and talks than ever before. Once again we’d like to thank everybody who participated, and the entire open source community in general: without your dedication and spirit, none of this would be possible.

At the prize ceremony, many of the exhibitors gave away a prize to the people who filled out and turned in a completed conference passport, as well as some gifts for those who completed surveys for the talks. Prizes ranged from $50 gift certificates, signed copies of Bill Nye’s book Unstoppable, an Amazon Echo, GoPro cameras, an Oculus Rift, a Playstation, and more. All the winners left happy (except maybe the guy who got the Mr. Spock socks, although I would have been pleased).

Thanks for coming, and we’ll see you in Amsterdam (and next year)!

Below is a video of the ceremony in full (it’s about 15 minutes long).

 

Percona Live 2016: MongoDB Security

Do, 2016-04-21 22:18

It’s almost time for the closing remarks and passport prize give away at Percona Live 2016, but still the talks keep coming. I was able to quickly pop into a lecture on MongoDB Security with Stephane Combaudon, MySQL Consultant at Slice Technologies.

Stephane went over some of the reported security issues with MongoDB and explained that MongoDB has good security features. Data security is a concern for most people, and recent reports in the news show that significant amounts of user details stored in MongoDB are easily accessible. This doesn’t mean that MongoDB can’t secure your data. As Stephane explained, MongoDB can be correctly secured – but some features are not enabled by default. In this session, we learned the main items that need to be checked to get a robust MongoDB deployment. Those items include:

  • Network security: Stopping people from connecting to your MongoDB instances
  • Operating system security: stopping people from connecting to MongoDB and taking control of your servers
  • User security: how to make sure that users can only interact with specific portions of the data

I had a chance to quickly speak with Stephane after his talk:

See the rest of the Percona Live 2016 schedule here.

Percona Live 2016: Sometimes “Less is More” for dash-boarding and graphing

Do, 2016-04-21 21:12

We’re starting to wind down here at Percona Live 2016, but there are still talks to see even this late in the conference! One of the talks I saw was Sometimes “Less is More” for dash-boarding and graphing with Michael Blue, Senior DBA at Big Fish Games.

In this talk, Michael discussed how monitoring specific metrics can be more help than all the metrics. In a monitor/measure everything environment, to get the big picture sometimes “Less is More.” There are good monitoring tools available for DBAs, with tons of metrics to measure, but at a larger scale it seems impossible to get that big picture view of your environment without spending much of your time going over all the metrics your graphing, sifting through emails of false positives alerts, and reading tons of your cron job outputs. In the talk, Michael explained the approaches he took at Big Fish to create better dashboards for all audiences without visual overload, which helped the DBAs find potential issues that were not caught via conventional monitoring. This session included:

This session included:

  • The basics of visualization
  • Picking metrics to measure at scale
  • How they leverage custom dashboards, Graphite, and the MySQL Enterprise Monitor

I had a chance  to speak with Michael after his talk:

 Check out more of the Percona Live 2016 schedule here.

Percona Live 2016: Percona Live Game Night!

Do, 2016-04-21 20:09

Wednesday night at Percona Live 2016 was reserved for fun, fun, fun! Once again, the Percona Live Game Night proved to be a popular and amazing event. There were more games this year than last, as well as food, drinks, and lots of friendly competition!

This year, besides the ever-crowd-pleasing Meltdown Challenge, there were Segway Races, pool, foosball, shuffleboard, Wii Boxing, Pac-Man Attack, a shootout gallery, darts, as well as virtual reality stations and a death-defying trampoline.

You can see Percona’s CEO Peter Zaitsev demonstrating how you use it, pro-level:

Below are some more photos of this outstanding night:

Coed boxing: guys, you need to improve your skills!

.

Some very intense foosball action!

.

This Pac-Man Attack reminds me of 1983!

.

Keep an eye on your wallets, gentlemen, I detect a hustle.

.

More trampoline.

.

For those who like less effort with their trampoline, virtual reality.

.

A little social lubrication.

.

Happy attendees (must have stopped at the previous picture).

.

Hmm, that guy looks a bit confused. Must be too much tech talk for one day!

.

I’d stay away from this table. Just saying.

.

More happy.

.

Thanks to everybody who came out and participated in an awesome night! We’ll see you all next year!

 

 

Percona Live 2016: MySQL Community Awards Ceremony

Do, 2016-04-21 19:23

We wrapped up Thursday morning’s Percona Live 2016 keynotes (April 21)  with The MySQL Community Award Ceremony. The MySQL Community Awards is a community-based initiative, with the goal of publicly recognizing contributors to the MySQL ecosystem. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based on past winners or their representatives, as well as known contributors. It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self-criticizing committee.

The categories can be found here.

We celebrated the members of the open source community, and some big open source contributors from the past year! The winners this year were:

Community Contributors of the Year:

  • Bill Karwin, Senior Database Architect and Software Engineer at SchoolMessenger
  • Domas Mituzas, Small Data Engineer at Facebook
  • Yoshinori Matsunobu, Data Engineer at Facebook

Application of the Year:

Corporate Contributor of the Year:

 

Congratulation to all the winners! You can watch the whole ceremony below (it’s about 15 minutes long).

Percona Live 2016: Day Three Keynote Talks

Do, 2016-04-21 18:52

We’re heading into the final day here at Percona Live 2016! People are looking a little tired, but still excited for some excellent talks today. Once again the day started off with two great keynote lectures. Read to the end for an important announcement!

Peter Zaitsev, CEO of Percona
Winning with Open Source Databases

Peter discussed what makes the open source community so vibrant and powerful, and why it is essential to preserve that spirit. Data is critical to the success of your business. You rely on your database and its supporting systems to power the applications that drive your business. These systems must be reliable, scalable, efficient – and increasingly, open source. With the complexity of today’s web applications and the databases, systems and hardware that support them, organizations must use the right open source tools and technology for each job – without getting locked into a proprietary solution. With Percona, customers are assured a choice in technology options that are completely open source (and include enterprise features). We help our customers find the right technology for their specific needs, rather than sell a one-size-fits-all product. Percona is a true open source partner that helps you optimize your database performance to better run your business.

Patrick McFadin, Chief Evangelist at DataStax

Take back the power in your cloud applications with Apache Cassandra

Patrick discussed how cloud applications can help you to develop the applications you need in your business, but also outline why the cloud isn’t a panacea for every business issue. Database engineers have had to support the crazy dreams of application developers since the beginning of the internet. Patrick says it’s time to take back the power! He believes that Apache Cassandra is the tool that can help you eliminate downtime or span your data around the world with ease. Deploying to the cloud isn’t always easy, but Cassandra might be able to give your application developers the best chance they can get and sleep easy at night.

 

Post MongoDB World New York Conference, June 30th, Hilton Mid-Town Manhattan

Peter also made an important announcement: Percona and ObjectRocket/Rackspace will be sponsoring a free post-MongoDB World Community Event! The event will take place on Thursday, June 30th at the Hilton Mid-Town Manhattan.

All are welcome.

Don’t miss out on this amazing opportunity to share ideas and get insights after MongoDB World.

Check out the rest of today’s Percona Live 2016 schedule here.

Percona Live 2016: What’s New in MySQL

Do, 2016-04-21 05:14

The second day of Percona Live 2016 is wrapping up, but I still wanted to get to one last lecture. Fortunately, there was a great one available: What’s New in MySQL with Geir Høydalsvik, Senior Software Development Director at Oracle, and Simon Mudd, DBA at booking.com.

MySQL 5.7 is an extremely exciting new version of the world’s most popular open source database that is 2x faster than MySQL 5.6, while also improving usability, manageability, and security. MySQL 5.7, now in GA, is the result of almost three years of development and brings improvements to all areas of the product. Some key enhancements include:

  • MySQL improved InnoDB scalability and temporary table performance, enabling faster online and bulk load operations, and more.
  • They’ve added native JSON support.
  • For increased availability and performance, they’ve included multi-source replication, multi-threaded slave enhancements, online GTIDs, and enhanced semi-sync replication.
  • They’ve added numerous new monitoring capabilities so the Performance Schema for better insights, reduced the footprint and overhead, and significantly improved ease of use with the new SYS Schema.
  • They are fulfilling “secure by default” requirements, and many new MySQL 5.7 features will help users keep their database secure.
  • They have rewritten large parts of the parser, optimizer, and cost model. This has improved maintainability, extendability, and performance.
  • GIS is completely new in MySQL 5.7, as is InnoDB spatial indexes, the use of Boost.Geometry, and increased completeness and standard compliance.

 I had a chance to talk with Geir and Simon after their lecture:

Check out the Percona Live 2016 schedule for more talks!

Percona Live 2016: MySQL and Docker Strategies

Do, 2016-04-21 01:19

The afternoon sessions at Percona Live 2016 are just as intriguing as the morning sessions. I’m trying to hit as many as I can, especially talks on interesting and upcoming technologies that are improving deployments and manageability. One of the talks I saw in this vein was MySQL and Docker Strategies, given by Patrick Galbraith, Senior Systems Engineer at Hewlett Packard, and Giuseppe Maxia, Quality Assurance Director at VMware.

Docker is a great new project for managing containers. Containers provide operating system resource isolation and allocation benefits as virtual machines, yet are more lightweight and allow you a lot of flexibility in how you can design your application and database architecture. This talk covered many of the useful things one can do with Docker and MySQL, such as:

  • Build ready-to-launch containers that are “batteries included” MySQL servers
  • Backup and restore MySQL databases using a container strategy that allows both containers and host systems access to the same data
  • Network containers across hosts
  • Implement container clustering solutions such as CoreOS, Kubernetes, and Mesos and how each of these solutions solves a similar requirement
  • Automate containerized MySQL using Ansible

Patrick and Giuseppe also provided summary details and a demonstration of their recent contribution to Kubernetes (a Galera cluster application), which showed how one can have a quickly deployable MySQL synchronous replication cluster in minutes and use it with other Kubernetes applications.

I had a chance to speak with Giuseppe after the talk:

 

Check out the Percona Live 2016 schedule for more talks!

Percona Live 2016: Indexing Strategies and Tips for MongoDB

Mi, 2016-04-20 22:57

The second day at Percona Live 2016 is moving along, and there is just too much to see to get to it all. Percona acquired Tokutek last year and with it a MongoDB platform. I was interested in learning more about MongoDB this year at Percona Live 2016, and I’m not disappointed! There is at least one MongoDB lecture ever hour, along with a MongoDB 101 track led by Percona experts who take attendees through the fundamentals of MongoDB tools and techniques.

One of the MongoDB lectures I attended was lead by Kimberly Wilkins, Principal Engineer/Databases at ObjectRocket by Rackspace. Her lecture was titled Indexing Strategies and Tips for MongoDB. In this presentation, Kimberly covered general indexing, options, and strategies. With the new indexing improvements and changes in MongoDB 3.2, there are more indexing options than ever before – including automated index builds and partial indexes. After this talk, I was able to walk away with real world examples of index use cases for running at scale and for high performance that have been tested with multiple high-level clients in small to very large MongoDB databases.

After the talk, I was able to chat with Kimberly for a minute:

See the rest of the Percona Live 2016 schedule here.

Percona Live 2016: Operational Buddhism — Building Reliable Services From Unreliable Components

Mi, 2016-04-20 20:55

It’s another packed day here at Percona Live 2016, with many, many database topics under discussion. Some technical, some strategical, and some operational. One such talk I sat in on was given by Ernie Souhrada, Database Engineer and Bit Wrangler at Pinterest. His talk was called Operational Buddhism: Building Reliable Services From Unreliable Components.

In it he discussed how the rise of utility computing has revolutionized much about the way organizations think about infrastructure and back-end serving systems, compared to the “olden days” of physical data centers. But success is still driven by meeting your SLAs. If services are up and sufficiently performant, you win. If not, you lose. In the traditional data center environment, fighting the uptime battle was typically driven by a philosophy Ernie calls “Operational Materialism.” The primary goal of OM is preventing failures at the infrastructure layer, and mechanisms for making this happen are plentiful and well-understood, many of which boil down to simply spending enough money to have at least N+1 of anything that might fail.

Ernie contends that in the cloud, Operational Materialism cannot succeed. Although the typical cloud provider tends to be holistically reliable, there are no guarantees that any individual virtual instance will not randomly or intermittently drop off the network or be terminated outright. Since we still need to keep our services up and running and meet our SLAs, we need a different mindset that accounts for the fundamentally opaque and ephemeral nature of the public cloud.

Ernie presented an alternative to OM, a worldview that he referred to as “Operational Buddhism.” Like traditional Buddhism, OB has Four Noble Truths:

  1. Cloud-based servers can fail at any time for any reason
  2. Trying to prevent this server failure is an endless source of suffering for DBAs and SREs alike
  3. Accepting the impermanence of individual servers, we can focus on designing systems that are failure-resilient, rather than failure-resistant
  4. We can escape the cycle of suffering and create a better experience for our customers, users, and colleagues.

To illustrate these concepts with concrete examples, he discussed how configuration management, automation, and service discovery help Pinterest to practice Operational Buddhism for both stateful (MySQL, HBase) and stateless (web) services. He also talked about some of the roads not taken, including the debate over Infrastructure-as-a-Service (IaaS) vs. Platform-as-a-Service (PaaS).

I was able to have a quick chat with Ernie after the talk:

 

See the rest of the Percona Live 2016 schedule here.

Percona Live 2016: Day Two Keynote Talks

Mi, 2016-04-20 20:23

We’ve started the second full day at Percona Live 2016, and again we had some pretty impressive and fascinating keynote speakers. This morning featured four keynote talks from some pretty heavy hitters in our industry:

Anurag Gupta, Vice President of Big Data Services, Amazon Web Services
AWS Big Data Services: Reimagining Big Data Processing in the Cloud

In this talk, we found out about the data challenges and trends that drove AWS to reimagine data processing and bet the future on new Big Data services that are innovating data infrastructure (from NoSQL databases to data warehouses). Anurag (and AWS) believe that one size doesn’t fit all, and multiple services are often necessary to address different challenges of Big Data. He provides an under-the-hood view of some of the most popular Big Data services at AWS, including:
  • Amazon DynamoDB – a NoSQL service
  • Amazon Redshift – a petabyte-scale data warehouse service
  • Amazon EMR – an elastic map-reduce service
  • Amazon Aurora – a massively scalable relational database service

He also discussed best practices in understanding customer desires and behavior from Big Data and how to achieve 1:1 customer experience at scale.

Data in the Cloud Keynote Panel: Cloudy with a chance of running out of disk space? Or Sunny times ahead?

Moderated by Matt Yonkovit, Percona’s Vice President of Professional Services, this panel discussion focused on the fact that larger and larger datasets are moving to the cloud, creating new challenges and opportunities in handling such workloads. As such, new technologies, revamped products, and a never ending stream of idea’s follow in the wake of this advance to keep pace. Even as these solutions attempt to improve the performance and manageability of cloud-based data, the question is are they enough? The panel brought our several areas that need to be addressed to keep ahead of the “data crisis,” including:

  • Compression
  • Visibility
  • Optimizing Storage
  • The trifecta of storage/analytics/processing
  • Security and privacy

Tomas Ulin, Vice President for the MySQL Engineering team, Oracle
MySQL: Combining SQL and NoSQL

In this talk, Tomas Ulin discussed how, now that MySQL 5.7 is GA, it’s delivered major improvements in performance, scalability and manageability. He explored how MySQL can deliver on the promises of NoSQL, while keeping all the proven benefits of SQL. This lecture allowed the audience to better understand the MySQL development priorities and what the future holds for the world’s most popular open source database.

Mark Callaghan, Software Engineer, Database Infrastructure, Facebook
MyRocks, MongoRocks and RocksDB

In this lecture, we learned about RocksDB, MyRocks, and MongoRocks. RocksDB is an embedded database engine. MyRocks and MongoRocks are RocksDB storage engines for MySQL and MongoDB. All of this is open-source. Facebook started these projects to get better performance, efficiency and compression with fast storage devices like flash-based SSD but they also work great with disks. Mark explained how and why MyRocks provides better performance, efficiency and compression using real and synthetic workloads. In one case, they got 2X better compression compared to compressed InnoDB for a production workload. Mark (and Facebook) expect MyRocks to become the web-scale storage engine for MySQL.

Those were the morning lectures today! And there is more to come! Check out our schedule here.

Percona Live 2016: Running MongoRocks in Production

Mi, 2016-04-20 06:38

It’s been a long first day at Percona Live 2016, filled with awesome insight and exciting topics. I was able to get to one more lecture before calling quits. For the final talk I saw today I listened to Igor Canadi, Software Engineer at Facebook, Inc., discuss Running MongoRocks in Production.

Facebook has been running MongoDB 3.0 with RocksDB storage engine (MongoRocks) at Parse since March of last year (2015). At this talk, they wanted to share some lessons learned about running MongoRocks in production. Igor was able to provide some interesting war stories and talk about performance optimization. Along with a little bit about RocksDB internals and which counters are most important to watch for.

RocksDB compares favorably to both the MMAP and WiredTiger storage engines when it comes to large write workloads.

The audience came away from the talk ready to get their feet wet with MongoRocks.

Below is a quick chat I had with Igor about RocksDB and MongoDB:

 

See the rest of the Percona Live schedule here.

 

Percona Live 2016: High Availability Using MySQL Group Replication

Mi, 2016-04-20 06:16

Percona Live 2016 had a great first day, with an impressive number of speakers and topics. I was able to attend a session in the afternoon with Luis Soares, Principal Software Engineer at Oracle, on High Availability Using MySQL Group Replication. MySQL Group Replication is a MySQL plugin under development that brings together group communication techniques and database replication, providing both high availability (HA) and a multi-master update everywhere replication solution.

At MySQL Group Replication’s  core is a set of group communication primitives that act as the building blocks to creating reliable, consistent and dependable messaging between the servers in the group. This allows the set of MySQL servers to coordinate themselves and act as a consistently replicated state machine. As a consequence, the group itself is fault-tolerant, and so is the service it provides (i.e., the MySQL database service). The plugin also provides multi-master update-anywhere characteristics with automatic conflict detection and handling.

In this discussion, we learned about the technical details of the MySQL Group Replication plugin, and discussed how this fits into the overall picture of the MySQL HA. For instance, how it can be deployed together with MySQL Router to automate load balancing and failover procedures. We also discovered the newest enhancements and how to leverage them when deploying and experimenting with this plugin.

Listen to a brief chat I had with Luis on MySQL Group Replication:

Check out the rest of the conference schedule here.

Percona Live 2016: A quick chat with Bill Nye, the Science Guy!

Mi, 2016-04-20 00:18

Percona Live is humming along, and we had quite a whirlwind keynote session this morning. Bill Nye the Science Guy gave an amazing talk, Bill Nye’s Objective – Change the World, on how the process of science and critical thinking can help us not only be positive about the challenges we face in our world today, but also help us to come up with the big ideas we need to solve them. He discussed many topics, from how his parents met, their involvement in science (his mother worked on the Enigma Code in World War 2!), working at Boeing as an engineer, his involvement with Carl Sagan, and how he has worked to help harness renewable energy through solar panels, a solar water heater, and skylights at his own home in Studio City.

Bill Nye is also the CEO of The Planetary Society. The Planetary Society, founded in 1980 by Carl Sagan, Bruce Murray, and Louis Friedman, works to inspire and involve the world’s public in space exploration through advocacy, projects, and education. Today, The Planetary Society is the largest and most influential public space organization group on Earth.

After the talks, I was able to quickly catch Bill Nye and ask him a few questions.

 

 

Percona Live 2016: Let Robots Manage your Schema (without destroying all humans)!

Di, 2016-04-19 23:55

We’re are rapidly moving through day one of the Percona Live Data Performance Conference, and I’m surrounded by incredibly smart people all discussing amazing database techniques and technology. The depth of solutions represented here, and the technical know-how needed to pull them off is astounding!

This afternoon I was able to catch Jenni Snyder, MySQL DBA  at Yelp deliver her talk on Let Robots Manage your Schema (without destroying all humans). While vaguely frightening, it was a fascinating talk on how automating schema changes helped Yelp’s development.

You’re probably already using automation to build your application, manage configuration, and alert you in case of emergencies. Jenni asks what’s keeping you from doing the same with your MySQL schema changes? For Yelp, the answer was “lots of things”. Today, Yelp uses Liquibase to manage their schema changes, pt-online-schema-change to execute them, and Jenkins to ensure that they’re run in all of their environments. During this session, she explained the history of MySQL schema management at Yelp, and how hard it was for both developers and DBAs.

Below is a video of her summarizing her team’s efforts and outcomes.

Check out the Percona Live schedule to see what is coming up next!

 

Percona Live 2016: Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums

Di, 2016-04-19 21:33

We’re moving along on the first day at Percona Live 2016, and I was able to attend a lecture from Intel’s Ken LeTourneau, Solutions Architect at Intel, on Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums. In this talk, Ken reviewed some benchmark testing he did using MySQL on various types of storage mediums. This talk looked at the performance of Percona Server for MySQL for Linux running on the same Intel system, but with three different storage configurations. We looked at and compared the performance of:

  1. a RAID of HDD,
  2. a RAID of SATA SSD, and
  3. a RAID of NVMe SSD

In the talk,  Ken covered the hardware and system configuration and then discuss results of TPC-C and TPC-H benchmarks, as well as the overall system costs including hardware and software, and cost per transaction/query based on overall costs and benchmark results.

I got a chance to speak with Ken after his talk, check it out below!

 

Day One of the Percona Live Data Performance Conference 2016 is off and running!

Di, 2016-04-19 20:35

Today was day one of the Percona Live Data Performance Conference! The day began with some excellent keynote speakers and exciting topics, and the packed room was eager to hear what our speakers had to say!

Peter Zaitsev, CEO, Percona
Percona Opening  Keynote
Peter kicked it off today by thanking the sponsors, the speakers, the Percona Live committee, and the attendees for contributing and participating in this year’s event. It has grown and changed quite a bit from its initial creation. Peter emphasized how this a gathering of members of a community, one that changes and adapts, and discusses and debates many different points of views and opinions. No longer is just a conference about MySQL, but now includes MongoDB, Cassandra, and many other solutions and products that are all a part of the open source community. The purpose of the conference is to provide open and diverse opinions, quality content, a technical focus, and useful and practical ideas and solutions.

Chad Jones, Chief Strategy Officer, Deep Information
Transcending database tuning problems: How machine learning helps DBAs play more ping pong

Next up was Chad Jones discussing how just as machine learning enables businesses to gain competitive advantage through predictive analytics, by looking deeper into the data stack we find the need for the same predictive capabilities for MySQL tuning. With over 10^13 possible tuning permutations, some requiring reboots or a rebuild, DBAs spend way too much time on MySQL tuning for a point-in-time situation that changes constantly. He demonstrated how unsupervised machine learning based on resource, workload and information modeling could predictively and continuously tune databases. DBAs can transcend the tuning game, saving precious time to work on important things, like improving your mad ping pong skills!

Bill Nye, The Planetary Society, CEO
Bill Nye’s Objective – Change the World
F
inally this morning, we were treated to an outstanding lecture from world-renown scientist and media personality Bill Nye the Science Guy. Bill spent his time discussing his life, how he came to love science, and the ability it brings to understand the world. His experiences as an engineer at Boeing helped him appreciate the value in investing time and money into excellent design strategies and processes. Through the power of critical thinking and science, we can embrace optimism in a world that has many touch challenges. Bill Nye fights to raise awareness of the value of science, critical thinking, and reason. He hopes that the data he brings will help inspire people everywhere to change the world!

Those were the morning lectures today! Such a great set of speakers, I can’t wait for tomorrow! Check out our schedule here.

 

Percona Server for MongoDB 3.2.4-1.0rc2 is now available

Di, 2016-04-19 20:20

Percona is pleased to announce the release of Percona Server for MongoDB 3.2.4-1.0rc2 on April 19, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. This release candidate is based on MongoDB 3.2.4, it extends MongoDB with MongoRocks and PerconaFT storage engines, as well as adding features like external authentication and audit logging. Percona Server for MongoDB requires no changes to MongoDB applications or code.

NOTE: The MongoRocks storage engine is still under development. There is currently no officially released version of MongoRocks that can be recommended for production. Percona Server for MongoDB 3.2.4-1.0rc2 includes MongoRocks 3.2.4, which is based on RocksDB 4.4.

This release includes all changes from MongoDB 3.2.4, and there are no additional improvements or new features on top of those upstream fixes.

NOTE: As of version 3.2, MongoDB uses WiredTiger as the default storage engine, instead of MMAPv1.

Percona Server for MongoDB 3.2.4-1.0rc2 release notes are available in the official documentation.