MySQL

Percona XtraDB Cluster 5.7.14-26.17 GA is now available

MySQL Performance Blog - Thu, 2016-09-29 14:05

This Percona XtraDB Cluster 5.7.14-26.17 GA release is dedicated to the memory of Federico Goncalvez, our colleague with Percona’s Uruguayan team until his tragic death on September 6, 2016.

Fede, we miss you.

Percona announces the first general availability (GA) release in the Percona XtraDB Cluster 5.7 series on September 29, 2016. Binaries are available from the downloads area or our software repositories.

The full release notes are available here.

Percona XtraDB Cluster 5.7.14-26.17 GA is based on the following:

For information about the changes and new features introduced in Percona Server 5.7, see Changed in Percona Server 5.7.

Percona XtraDB Cluster 5.7.14-26.17 GA New Features

This is a list of the most important features introduced in Percona XtraDB Cluster 5.7 compared to version 5.6:

  • PXC Strict Mode saves your workload from experimental and unsupported features.
  • Support for monitoring Galera Library instruments and other wsrep instruments as part of Performance Schema.
  • Support for encrypted tablespaces in Multi-Master Topology, which enables Percona XtraDB Cluster to wire encrypted tablespace to new booting node.
  • Compatibility with ProxySQL, including a quick configuration script.
  • Support for monitoring Percona XtraDB Cluster nodes using Percona Monitoring and Management
  • More stable and robust operation with MySQL and Percona Server version 5.7.14, as well as Galera 3.17 compatibility. Includes all upstream bug fixes, improved logging and more.
  • Simplified packaging for Percona XtraDB Cluster to a single package that installs everything it needs, including the Galera library.
  • Support for latest Percona XtraBackup with enhanced security checks.
Bug Fixes
  • Fixed crash when a local transaction (such as EXPLAIN or SHOW) is interrupted by a replicated transaction with higher priority (like ALTER that changes table structure and can thus affect the result of the local transaction).
  • Fixed DONOR node getting stuck in Joined state after successful SST.
  • Fixed error message when altering non-existent table with pxc-strict-mode enabled.
  • Fixed path to the directory in percona-xtradb-cluster-shared.conf.
  • Fixed setting of seqno in grastate.dat to -1 on clean shutdown.
  • Fixed failure of asynchronous TOI actions (like DROP) for non-primary nodes.
  • Fixed replacing of my.cnf during upgrade from 5.6 to 5.7.
Security Fixes
  • CVE-2016-6662
  • CVE-2016-6663
  • CVE-2016-6664

For more information, see this blog post.

Other Improvements
  • Added support of defaults-group-suffix for SST scripts.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Categories: MySQL

7 Fresh Bugs in MySQL 8.0

MySQL Performance Blog - Wed, 2016-09-28 18:19

This blog post will look at seven bugs in MySQL 8.0.

Friday afternoon is always ideal for a quick look at the early quality of MySQL 8.0! Last Friday, I did just that.

If you haven’t heard the news yet, MySQL 8.0 DMR is available for download on mysql.com!

Tools to the ready: pquery2, updated 8.0 compatible scripts in Percona-qa and some advanced regex to wade through the many cores generated by the test run. For those of you who know and use pquery-run.sh, this should mean a lot!

[09:41:50] [21492] ====== TRIAL #39308 ======

In other words, almost 40K trials and 21.5K core dumps (or other significant issues) detected! This run had been churning away on a server for a number of days. On to the bug logging fun!

After reducing test cases, and filtering duplicates, we have the following seven bugs logged in upstream;

  • Bug #83120 virtual void Field::store_timestamp(const timeval*): Assertion ‘false’ failed.
  • Bug #83118 handle_fatal_signal (sig=11) in replace_user_table
  • Bug #83117 Assertion MDL_checker::is_read_locked(m_thd, *object)’ failed.
  • Bug #83115 Assertion ‘maybe_null’ failed. handle_fatal_signal in tem_func_concat::val_str
  • Bug #83114 Assertion `strlen(db_name) <= (64*3) && strlen(table_name) <= (64*3)’ failed.
  • Bug #83113 SIGKILL myself on DROP TABLE
  • Bug #83112 handle_fatal_signal (sig=11) in sp_get_flags_for_command

My first impressions?

MySQL 8.0 DMR is a reasonably solid release for a first iteration.

It seems our friends at upstream are on an excellent path to making MySQL 8.0 another rock-solid release. Chapeau!

Categories: MySQL

Percona Live Europe featured talk with John De Goes — MongoDB Analytics & Dashboards

MySQL Performance Blog - Wed, 2016-09-28 15:46

Welcome to another Percona Live Europe featured talk with Percona Live Europe 2016: Amsterdam speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference. We’ll also discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live Europe registration bonus!

In this Percona Live Europe featured talk, we’ll meet John De Goes, CTO at SlamData Inc. His talk will be on MongoDB Analytics & Dashboards. SlamData is an open source analytics and reporting solution designed specifically for MongoDB. SlamData lets anyone build live analytics, charts, and dashboards on any type of data inside MongoDB. Because SlamData runs all queries inside the database, reports are always up to date and can scale to the largest MongoDB deployments.

I had a chance to speak with John and learn a bit more about analytics for businesses:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

John: I work as the CTO at SlamData, where we build open source software that helps companies understand complex data stored in modern databases.

I love my job. We’re solving a huge pain point for businesses, and we get to do that through open source. The technical challenges we’ve had to overcome (and have yet to overcome!) can be really difficult, but that’s what makes it so fun.

Percona: Your talk is called “MongoDB Analytics & Dashboards.” What are some of the more important data sets in MongoDB for people to monitor, and how does SlamData work to provide easy analysis of those data?

John: We use MongoDB to build data hubs, which pull together lots of different types of data into a single database. SlamData’s really useful for that case because you can explore many complex kinds of data, no matter how nested or irregular, then refine the data and build beautiful interactive reports and dashboards that can be used by non-technical people.

Developers also use MongoDB to build web and mobile applications, which tend to collect a lot of data about the product and the users. Normal analytics tools don’t work with MongoDB, because the data model is too complex. But we built SlamData to handle complex data, we make it very easy to do product and user analytics on top MongoDB databases.

Percona: Why are data dashboards important for businesses? How can businesses use the data to improve processes or realize revenues?

John: Analytics serve one of two major roles in today’s businesses. Firstly, tech companies use analytics to build product features. For example, if you built a marketing application for businesses, then your customers probably want their own analytics dashboard that’s embedded into the product. If you built your application on a modern database like MongoDB, then SlamData makes it very easy for developers to add these beautiful, interactive and secure dashboards into their applications.

Secondly, all kinds of companies store user profile, user event and product data inside databases. Businesses can use this information to better understand their customer makeup, what users are doing, how they are using internal or external applications, and how one data set relates to another. These insights help businesses improve business processes, such as allocating marketing spend or directing product development resources. This is the classic use case for Business Intelligence (BI) and reporting software, and SlamData really shines here, because you can just get to work on any kind of data, no matter how much or little structure it might have.

Percona: What is changing in how businesses use data that keeps you awake at night? What tools or features are you looking might you be thinking about to address these issues?

John: Businesses are moving away from traditional data warehousing. They don’t want to spend millions of dollars a year licensing a big clunky piece of technology that never has all the data, and which is always out of date. They want agile solutions that they can point to any source of data, no matter where it is, and no matter what structure it has, and begin answering the questions they have.

This is the single biggest shift in how companies want to consume analytics. The other two important ones are the democratization of analytics, which is a fancy way of saying business users want to do more and bottleneck less on IT, and the increasing use of web and mobile analytics solutions.

SlamData is built web- and mobile-first, and the core technology relies a lot on pushing computation down into data sources to minimize the need to involve IT. But nonetheless, it’s a new paradigm, and building this sort of technology that addresses the need for analytics agility is not easy. It’s taken us two and a half years to get this far, and we’re still barely halfway in.

Percona: What are you looking forward to the most at Percona Live Europe this year?

John: I’m from the USA, so I’m interested in seeing what tools and processes professionals in Europe are using to manage data requirements. I’ll be comparing notes. It’s going to be a blast!

You can read more about John and his thoughts on MongoDB analytics at his twitter handle.

Want to find out more about John, SlamData, and analytics? Register for Percona Live Europe 2016, and come see his talk MongoDB Analytics & Dashboards.

Use the code FeaturedTalk and receive €25 off the current registration price!

Percona Live Europe 2016: Amsterdam is the premier event for the diverse and active open source database community. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, and other open source databases. Percona live tackles subjects such as analytics, architecture and design, security, operations, scalability and performance. It also provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience. All of these people help you learn how to tackle your open source database challenges in a whole new way.

This conference has something for everyone!

Percona Live Europe 2016: Amsterdam is October 3-5 at the Mövenpick Hotel Amsterdam City Centre.

Amsterdam eWeek

Percona Live Europe 2016 is part of Amsterdam eWeek. Amsterdam eWeek provides a platform for national and international companies that focus on online marketing, media and technology and for business managers and entrepreneurs who use them, whether it comes to retail, healthcare, finance, game industry or media. Check it out!

Categories: MySQL

Using the super_read_only system variable

MySQL Performance Blog - Tue, 2016-09-27 18:06

This blog post will discuss how to use the MySQL super_read_only system variable.

It is well known that replica servers in a master/slave configuration, to avoid breaking replication due to duplicate keys, missing rows or other similar issues, should not receive write queries. It’s a good practice to set read_only=1 on slave servers to prevent any (accidental) writes. Servers acting as replicas will NOT be in read-only mode automatically by default.

Sadly, read_only has an historical issue: users with the SUPER privilege can override the setting and could still run DML queries. Since Percona Server 5.6.21 and MySQL 5.7.8, however, you can use the super_read_only feature to extend the read_only  option and apply it to users with SUPER privileges.

Both super_read_only and read_only  are disabled by default, and using super_read_only implies that read_only  is automatically ON as well. We’ll demonstrate how read_only and super_read only work:

mysql> SET GLOBAL read_only = 1; Query OK, 0 rows affected (0.00 sec)

As expected, with the read_only variable enabled, users without SUPER privilege won’t be able to INSERT values, and instead they will get an ERROR 1290 message:

mysql> SELECT @@global.read_only, @@global.super_read_only; +--------------------+--------------------------+ | @@global.read_only | @@global.super_read_only | +--------------------+--------------------------+ |                  1 |                        0 | +--------------------+--------------------------+ 1 row in set (0.01 sec) mysql> SHOW GRANTSG *************************** 1. row *************************** Grants for nosuper@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'nosuper'@'localhost' IDENTIFIED BY PASSWORD <secret> 1 row in set (0.00 sec) mysql> INSERT INTO test.example VALUES (1); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

However, users with SUPER privileges can INSERT values on the table:

mysql> SELECT @@global.read_only, @@global.super_read_only; +--------------------+--------------------------+ | @@global.read_only | @@global.super_read_only | +--------------------+--------------------------+ |                  1 |                        0 | +--------------------+--------------------------+ 1 row in set (0.01 sec) mysql> SHOW GRANTSG *************************** 1. row *************************** Grants for super@localhost: GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' IDENTIFIED BY PASSWORD '*3E26301B12AE2B8906D9F09785359751700930E8' 1 row in set (0.00 sec) mysql> INSERT INTO test.example VALUES (1); Query OK, 1 row affected (0.01 sec)

Now we will enable super_read_only and try to INSERT data again with both users:

mysql> SET GLOBAL super_read_only = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@global.read_only, @@global.super_read_only; +--------------------+--------------------------+ | @@global.read_only | @@global.super_read_only | +--------------------+--------------------------+ |                  1 |                        1 | +--------------------+--------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTSG *************************** 1. row *************************** Grants for super@localhost: GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' IDENTIFIED BY PASSWORD '*3E26301B12AE2B8906D9F09785359751700930E8' 1 row in set (0.00 sec) mysql> INSERT INTO test.example VALUES (1); ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

 

mysql> SELECT @@global.read_only, @@global.super_read_only; +--------------------+--------------------------+ | @@global.read_only | @@global.super_read_only | +--------------------+--------------------------+ |                  1 |                        1 | +--------------------+--------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTSG *************************** 1. row *************************** Grants for nosuper@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'nosuper'@'localhost' IDENTIFIED BY PASSWORD <secret> 1 row in set (0.00 sec) mysql> INSERT INTO test.example VALUES (1); ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

As you can see above, now even users with SUPER privileges can’t make updates or modify data. This is useful in replication to ensure that no updates are accepted from the clients, and are only accepted by the master.

When enabling the super_read_only system variable, please keep in mind the following implications:

  • Setting super_read_only ON implicitly forces read_only ON
  • Setting read_only OFF implicitly forces super_read_only OFF

There are some other implications for read_only that apply to super_read_only as well:

  • Operations on temporary tables are allowed no matter how these variables are set:
    • Updates performed by slave threads are permitted if the server is a replication slave. In replication setups, it can be useful to enable super_read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.
  • OPTIMIZE TABLE and ANALYZE TABLE operations are allowed as well, since the purpose of the read-only mode is to prevent changes to table structure or contents, but not to table metadata like index stats.
  • You will need to manually disable it when you promote a replica server to the role of master.

There are few bugs related to this variable that might be useful to take into consideration if you’re running on Percona Server 5.6:

For more information, please refer to this following documentation links:

Categories: MySQL

TokuDB and PerconaFT database file management part 1 of 2

MySQL Performance Blog - Tue, 2016-09-27 14:53

In this blog post, we’ll look at TokuDB and PerconaFT database file management.

The TokuDB/PerconaFT file set consists of many different files that all serve various purposes. These blog posts lists the different types of TokuDB and PerconaFT files, explains their purpose, shows their location and how to move them around.

Peter Zaitsev blogged on the same topic a few years ago. By the time you read back through Peter’s post and reach the end of this series, you should have some ideas to help you to manage your data set more efficiently.

TokuDB and PerconaFT files and file types:

  • tokudb.environment
    • This file is the root of the PerconaFT file set and contains various bits of metadata about the system, such as creation times, current file format versions, etc.
    • PerconaFT will create/expect this file in the directory specified by the MySQL datadir.
  • tokudb.rollback
    • Every transaction within PerconaFT maintains its own transaction rollback log. These logs are stored together within a single PerconaFT dictionary file and take up space within the PerconaFT cachetable (just like any other PerconaFT dictionary).
    • The transaction rollback logs will “undo” any changes made by a transaction if the transaction is explicitly rolled back, or rolled back via recovery as a result of an uncommitted transaction when a crash occurs.
    • PerconaFT will create/expect this file in the directory specified by the MySQL datadir.
  • tokudb.directory
    • PerconaFT maintains a mapping of a dictionary name (example: sbtest.sbtest1.main) to an internal file name (example: _sbtest_sbtest1_main_xx_x_xx.tokudb). This mapping is stored within this single PerconaFT dictionary file and takes up space within the PerconaFT cachetable just like any other PerconaFT dictionary.
    • PerconaFT will created/expect this file in the directory specified by the MySQL datadir.
  • Dictionary files
    • TokuDB dictionary (data) files store actual user data. For each MySQL table there will be:
      • One “status” dictionary that contains metadata about the table.
      • One “main” dictionary that stores the full primary key (an imaginary key is used if one was not explicitly specified) and full row data.
      • One “key” dictionary for each additional key/index on the table.
    • These are typically named: _<database>_<table>_<key>_<internal_txn_id>.tokudb
      PerconaFT creates/expects these files in the directory specified by tokudb_data_dir if set, otherwise the MySQL datadir is used.
  • Recovery log files
    • The PerconaFT recovery log records every operation that modifies a PerconaFT dictionary. Periodically, the system will take a snapshot of the system called a checkpoint. This checkpoint ensures that the modifications recorded within the PerconaFT recovery logs have been applied to the appropriate dictionary files up to a known point in time and synced to disk.
    • These files have a rolling naming convention, but use: log<log_file_number>.tokulog<log_file_format_version>
    • PerconaFT creates/expects these files in the directory specified by tokudb_log_dir if set, otherwise the MySQL datadir is used.
    • PeconaFT does not track what log files should or shouldn’t be present. Upon startup, it discovers the logs in the log dir, and replays them in order. If the wrong logs are present, the recovery aborts and possibly damages the dictionaries.
  • Temporary files
    • PerconaFT might need to create some temporary files in order to perform some operations. When the bulk loader is active, these temporary files might grow to be quite large.
    • As different operations start and finish, the files will come and go.
    • There are no temporary files left behind upon a clean shutdown,
    • PerconaFT creates/expects these files in the directory specified by tokudb_tmp_dir if set. If not, the tokudb_data_dir is used if set, otherwise the MySQL datadir is used.
  • Lock files
    • PerconaFT uses lock files to prevent multiple processes from accessing/writing to the files in the assorted PerconaFT functionality areas. Each lock file will be in the same directory as the file(s) that it is protecting. These empty files are only used as semaphores across processes. They are safe to delete/ignore as long as no server instances are currently running and using the data set.
    • __tokudb_lock_dont_delete_me_environment
    • __tokudb_lock_dont_delete_me_recovery
    • __tokudb_lock_dont_delete_me_logs
    • __tokudb_lock_dont_delete_me_data
    • __tokudb_lock_dont_delete_me_temp

PerconaFT is extremely pedantic about validating its data set. If a file goes missing or unfound, or seems to contain some nonsensical data, it will assert, abort or fail to start. It does this not to annoy you, but to try to protect you from doing any further damage to your data.

Look out for part 2 of this series for information on how to move your log, dictionary, and temp files around correctly.

Categories: MySQL

High Availability at Percona Live Europe 2016

MySQL Performance Blog - Mon, 2016-09-26 19:55

This blog will review some of the high availability topics featured at this year’s Percona Live Europe, Amsterdam conference.

The topic of high availability MySQL is always hot, because beyond just being available, you also want efficient database manageability. I’m sure you’ve all seen the video by Frederic Descamps talking about the launch of MySQL InnoDB Cluster (built with group replication, with management executed with the new MySQL Shell). MySQL 8.0 going GA will prove for an exciting time for the MySQL world (though all you early adopters should start trying it now, or right after Percona Live Europe Amsterdam!).

With that, I think that a must attend tutorial is MySQL Group Replication in a nutshell: hands-on tutorial by Frederic Descamps and Kenny Gryp. It competes with MySQL High Availability with Percona XtraDB Cluster 5.7 by  Alok Pathak, Peter Zaitsev and Krunal Bauskar (Percona XtraDB Cluster Team Lead), however, which I think will also be an interesting session (and you’re going to learn about Percona XtraDB Cluster 5.7 here).

The quality of the sessions this year are extremely high, making tutorial day hard to split up – another reason to bring a colleague to the conference to get the best spread! Remember, if you bring three or more in a group, you qualify for the group discounted rate at registration. Back to tutorial day, in the morning you have the choice of seeing me give Best Practices for MySQL High Availability or checkout the ProxySQL Tutorial by David Turner (Uber), Derek Downey (Pythian), and the author himself René Cannaò (Dropbox/ProxySQL). Frankly I think ProxySQL is the new hotness, so you definitely want to be there in the morning (and it will make a good follow on to the Percona XtraDB Cluster tutorial in the afternoon if you’re looking for a “track”).

On Day 1, there are plenty of talks, but my picks focused around high availability would be:

On Day 2, I’d probably check out the following:

So here’s another “track” like post about what’s coming in Amsterdam in about 2 weeks. There is of course, still time to register. Use the FeaturedTalk code to get a discount.

Amsterdam eWeek

Percona Live Europe 2016 is part of Amsterdam eWeek. Amsterdam eWeek provides a platform for national and international companies that focus on online marketing, media and technology and for business managers and entrepreneurs who use them, whether it comes to retail, healthcare, finance, game industry or media. Check it out!

Categories: MySQL

Webinar Wednesday, September 28: Percona Software News and Roadmap Update – Q3 2016

MySQL Performance Blog - Mon, 2016-09-26 19:27

Please join Percona founder and CEO Peter Zaitsev for a webinar Wednesday, September 28 at 11 am PDT (UTC-7) where he’ll discuss Percona Software News and Roadmap Update – Q3 2016.

Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

During this webinar Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register for the Percona Software News and Roadmap Update – Q3 2016 webinar here.

Peter Zaitsev, CEO

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 150 professionals in 20 plus countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. The Inc. 5000 added Percona to its list in 2013, 2014 and 2015. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group.

A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Data Performance Blog. He was also tapped as a contributor to Fortune and DZone, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of percona.com’s most popular downloads. Peter lives in North Carolina with his wife and two children. In his spare time, Peter enjoys travel and spending time outdoors.

Categories: MySQL

MariaDB Sessions at Percona Live Europe 2016

MySQL Performance Blog - Fri, 2016-09-23 17:06

If you’re going to Percona Live Europe Amsterdam 2016, and are specifically interested in MariaDB-related technologies, there are many talks for you to attend.

On Tutorial Monday, you will want to attend my tutorial The Complete MariaDB Server Tutorial. In those three hours, you’ll learn about all the differences that MariaDB Server has to offer in comparison to MySQL. Where there is feature parity, I’ll point out some syntax differences. This is a semi-hands-on tutorial as the feature scope would make fully hands-on impossible!

On Tuesday, my session picks include:
  • Common Table Expressions in MariaDB Server 10.2. You’ll get to learn from the very people implementing it, Sergei Petrunia (rockstar optimizer developer, and MyRocks hacker), and Galina Shalygina (a Google Summer of Code 2016 student who also helped develop it).
  • Percona XtraDB 5.7: Key Performance Algorithms. It is safe to presume that MariaDB Server 10.2 needs to include Percona XtraDB 5.7. Percona XtraDB has been the default storage engine for MariaDB Server since forever. In fact, MDEV-6113 suggests this will be available in the yet unreleased MariaDB Server 10.2.2. Learn why Percona XtraDB matters from Laurynas Biveinis, the Team Lead for Percona Server.
  • MySQL/MariaDB Parallel Replication: inventory, use-cases and limitations. Jean-François Gagné is the expert on parallel replication, not only from a usage and benchmarking perspective, but also from a feature request standpoint. I highly recommend attending this talk if you care about parallel replication. It will be packed!
On Wednesday, my session picks include:

So those are my Percona Live Europe picks that focus on the MariaDB ecosystem. Notably absent though is more MariaDB MaxScale content from the authors of the application. And what about MariaDB ColumnStore?

There is of course, still time to register. Use the FeaturedTalk code to get a discount.

Categories: MySQL

Identifying and Solving Database Performance Issues with PMM: Webinar Q&A

MySQL Performance Blog - Fri, 2016-09-23 14:16

In this blog, I will provide answers to the Q & A for the Identifying and Solving Database Performance Issues with PMM webinar.

First, I want to thank everybody for attending the September 15 webinar. The recording and slides for the webinar are available here. Thanks for so many good questions. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: PMM has some restrictions working with metrics from RDS instances (AWS)? Aurora for example?
Query analytics can be done only using performance_schema as a query source for RDS/Aurora. No slow log is possible at this moment, as it’s not in the file but the mysql.slow_log table. As for metrics, only MySQL-related ones can be collected, thus only MySQL graphs are available. No system metrics. However, we can look at what we can fetch from CloudWatch to close this gap.

Q: How many ports are needed for each MySQL client? This is related to how many ports need to be open on the firewall.
One metric service per instance requires one port (e.g., mysql:metrics, linux:metrics, mongodb:metrics). The MySQL query service (mysql:queries) does not require a port to open, as the agent connects to the server — unlike the server to client connection in the case of metric services. Usually, the diagram looks like this.

Q: Is it possible to add a customized item for additional monitoring on the client OS?
It is possible to enable additional options on the linux:metrics service (pmm-linux-metrics-42000, node_exporter process). However, it requires “hacking” the service file.

Q: Does PMM have any alerting feature built-in? Or, is there any way to feed alerts to other monitoring framework such as Nagios?
Currently, it doesn’t. But we have plans to add alerting functionality.

Q: Can pmm-client be delivered as an RPM instead of a tar ball?
It is delivered as packages, see the instructions. We recommend using system packages to simplify the upgrade process.

Q: You said it does SSL and Auth. I have 1.0.4 installed, but I do not know a way to do SSL or Auth with the install. My solution is to run it behind an nginx proxy.
Yes, 1.0.4 supports server SSL and HTTP basic authentication. The corresponding documentation is published now.

Q: If you change the Grafana username and password will it be persistent across reboots and upgrades of PMM?
Currently no, but we are working to make it possible very soon.

Q: In Percona cloud tools – we can sort the queries by the sum of the number of queries, or the max time taken by the queries. Is there a way to do that in PMM?
Unfortunately, there is no such possibility, but I have just filled an internal issue so it’s not forgotten.

Q: Can you show us how the explain query works in PMM?
Query Analytics web app calls the API, which asks the corresponding agent connected to the server from the client side to run EXPLAIN on MySQL in real time.

Q: Does PMM track deadlocks?
We can capture metric such as mysql_global_status_innodb_deadlocks but we do not currently graph it. Thanks for pointing out, we will consider adding it.

Q: Is it possible to set up alarm thresholds and notification?
Q: Is there any plan to add sending alerts by e-mail or SMS? This would be an excellent feature.
Currently, there is no alerts functionality. But we have plans for that.

Q: If we do not have a service manager installed like systemv or systemd, upstart , I am using Gentoo Linux on my database server , it has no service manager

Categories: MySQL

Percona Live Europe featured talk with Anthony Yeh — Launching Vitess: How to run YouTube’s MySQL sharding engine

MySQL Performance Blog - Thu, 2016-09-22 20:59

Welcome to another Percona Live Europe featured talk with Percona Live Europe 2016: Amsterdam speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference. We’ll also discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live Europe registration bonus!

In this Percona Live Europe featured talk, we’ll meet Anthony Yeh, Software Engineer, Google. His talk will be on Launching Vitess: How to run YouTube’s MySQL sharding engine. Vitess is YouTube’s solution for scaling MySQL horizontally through sharding, built as a general-purpose, open-source project. Now that Vitess 2.0 has reached general availability, they’re moving beyond “getting started” guides and working with users to develop and document best practices for launching Vitess in their own production environments.

I had a chance to speak with Anthony and learn a bit more about Vitess:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Anthony: Before joining YouTube as a software engineer, I worked on photonic integrated circuits as a graduate student researcher at U.C. Berkeley. So I guess you could say I took a rather circuitous path to the database field. My co-presenter Dan and I have that in common. If you see him at the conference, I recommend asking him about his story.

I don’t actually think of myself as being in database development though; that’s probably more Sugu‘s area. I treat Vitess as just another distributed system, and my job is to make it more automated, more reliable, and easier to administer. My favorite part of this job is when open-source contributors send us new features and plug-ins, and all I have to do is review them. Keep those pull requests coming!

Percona: Your talk is going to be on “Launching Vitess: How to run YouTube’s MySQL sharding engine.” How has Vitess moved from a YouTube fix to a viable enterprise data solution?

Anthony: I joined Vitess a little over two years ago, right when they decided to expand the team’s focus to include external usability as a key goal. The idea was to transform Vitess from a piece of YouTube infrastructure that happens to be open-source, into an open-source solution that YouTube happens to use.

At first, the biggest challenge was getting people to tell us what they needed to make Vitess work well in their environments. Attending Percona Live is a great way to keep a pulse on how the industry uses MySQL, and talk with exactly the people who can give us that feedback. Progress really picked up early this year when companies like Flipkart and Pixel Federation started not only trying out Vitess on their systems, but contributing back features, plug-ins, and connectors.

My half of the talk will summarize all the things we’ve learned from these early adopters about migrating to Vitess and running it in various environments. We also convinced one of our Site Reliability Engineers to give the second half of the talk, to share firsthand what it’s like to run Vitess in production.

Percona: What new features and fixes can people look forward to in the latest release?

Anthony: The biggest new feature in Vitess 2.0 is something that was codenamed “V3” (sorry about the naming confusion). In a nutshell, this completes the transition of all sharding logic from the app into Vitess: at first you had to give us a shard name, then you just had to tell us the sharding key value. Now you just send a regular query and we do the rest.

To make this possible, Vitess has to parse and analyze the query, for which it then builds a distributed execution plan. For queries served by a single shard, the plan collapses to a simple routing decision without extra processing. But for things like cross-shard joins, Vitess will generate new queries and combine results from multiple shards for you, in much the same way your app would otherwise do it.

Percona: Why is sharding beneficial to databases? Are there pros and cons to sharding?

Anthony: The main pro for sharding is horizontal scalability, the holy grail of distributed databases. It offers the promise of a magical knob that you simply turn up when you need more capacity. The biggest cons have usually been that it’s a lot of work to make your app handle sharding, and it multiplies the operational overhead as you add more and more database servers.

The goal of Vitess is to create a generalized solution to these problems, so we can all stop building one-off sharding layers within our apps, and replace a sea of management scripts with a holistic, self-healing distributed database.

Percona: Vitess is billed as being for web applications based in cloud and dedicated hardware infrastructures. Was it designed specifically for one or the other, and does it work better for certain environments?

Anthony: Vitess started out on dedicated YouTube hardware and later moved into Borg, which is Google’s internal precursor to Kubernetes. So we know from experience that it works in both types of environments. But like any distributed system, there are lots of benefits to running Vitess under some kind of cluster orchestration system. We provide sample configs to get you started on Kubernetes, but we would love to also have examples for other orchestration platforms like Mesos, Swarm, or Nomad, and we’d welcome contributions in this area.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Anthony: I hope to meet people who have ideas about how to make Vitess better, and I look forward to learning more about how others are solving similar problems.

You can read more about Anthony and Vitess on the Vitess blog.

Want to find out more about Anthony, Vitess, YouTube and and sharding? Register for Percona Live Europe 2016, and come see his talk Launching Vitess: How to run YouTube’s MySQL sharding engine.

Use the code FeaturedTalk and receive €25 off the current registration price!

Percona Live Europe 2016: Amsterdam is the premier event for the diverse and active open source database community. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, and other open source databases. Percona live tackles subjects such as analytics, architecture and design, security, operations, scalability and performance. It also provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience. All of these people help you learn how to tackle your open source database challenges in a whole new way.

This conference has something for everyone!

Percona Live Europe 2016: Amsterdam is October 3-5 at the Mövenpick Hotel Amsterdam City Centre.

Amsterdam eWeek

Percona Live Europe 2016 is part of Amsterdam eWeek. Amsterdam eWeek provides a platform for national and international companies that focus on online marketing, media and technology and for business managers and entrepreneurs who use them, whether it comes to retail, healthcare, finance, game industry or media. Check it out!

Categories: MySQL

Percona XtraDB Cluster 5.5.41-25.11.1 is now available

MySQL Performance Blog - Thu, 2016-09-22 17:56

Percona announces the new release of Percona XtraDB Cluster 5.5.41-25.11.1 (rev. 855) on September 22, 2016. Binaries are available from the downloads area or our software repositories.

Bugs Fixed:
  • Due to security reasons ld_preload libraries can now only be loaded from the system directories (/usr/lib64, /usr/lib) and the MySQL installation base directory. This fix also addresses issue with where limiting didn’t work correctly for relative paths. Bug fixed #1624247.
  • Fixed possible privilege escalation that could be used when running REPAIR TABLE on a MyISAM table. Bug fixed #1624397.
  • The general query log and slow query log cannot be written to files ending in .ini and .cnf anymore. Bug fixed #1624400.
  • Implemented restrictions on symlinked files (error_log, pid_file) that can’t be used with mysqld_safe. Bug fixed #1624449.

Other bugs fixed: #1553938.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Categories: MySQL

Sixth Annual Percona Live Open Source Database Conference 2017 Call for Speakers Now Open

MySQL Performance Blog - Thu, 2016-09-22 16:44

The Call for Speakers for Percona Live Open Source Database Conference 2017 is open and accepting proposals through Oct. 31, 2016.

The Percona Live Open Source Database Conference 2017 is the premier event for the diverse and active open source community, as well as businesses that develop and use open source software. Topics for the event will focus on three key areas – MySQL, MongoDB and Open Source Databases – and the conference sessions will feature a range of in-depth discussions and hands-on tutorials.

The 2017 conference will feature four formal tracks – Developer, Operations, Business/Case Studies, and Wildcard – that will explore a variety of new and trending topics, including big data, IoT, analytics, security, scalability and performance, architecture and design, operations and management and development. Speaker proposals are welcome on these topics as well as on a variety of related technologies, including MySQL, MongoDB, Amazon Web Services (AWS), OpenStack, Redis, Docker and many more. The conference will also feature sponsored talks.

Percona Live Open Source Database Conference 2017 will take place April 24-27, 2017 at The Hyatt Regency Santa Clara and Santa Clara Convention Center. Sponsorship opportunities are still available, and Super Saver Registration Discounts can be purchased through Nov. 13, 2016 at 11:30 p.m. PST.

Click here to see all the submission criteria, and to submit your talk.

Sponsorships

Sponsorship opportunities for Percona Live Open Source Database Conference 2017 are available and offer the opportunity to interact with more than 1,000 DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solution vendors, and entrepreneurs who typically attend the event.

Planning to Attend?

Super Saver Registration Discounts for Percona Live Open Source Database Conference 2017 are available through Nov. 13, 2016 at 11:30 p.m. PST.

Visit the Percona Live Open Source Database Conference 2017 website for more information about the conference. Interested community members can also register to receive email updates about Percona Live Open Source Database Conference 2017.

Categories: MySQL

Percona XtraDB Cluster 5.6.30-25.16.3 is now available

MySQL Performance Blog - Wed, 2016-09-21 18:22

Percona  announces the new release of Percona XtraDB Cluster 5.6 on September 21, 2016. Binaries are available from the downloads area or our software repositories.

Percona XtraDB Cluster 5.6.30-25.16.3 is now the current release, based on the following:

  • Percona Server 5.6.30-76.3
  • Galera Replication library 3.16
  • Codership wsrep API version 25
Bugs Fixed:
  • Limiting ld_preload libraries to be loaded from specific directories in mysqld_safe didn’t work correctly for relative paths. Bug fixed #1624247.
  • Fixed possible privilege escalation that could be used when running REPAIR TABLE on a MyISAM table. Bug fixed #1624397.
  • The general query log and slow query log cannot be written to files ending in .ini and .cnf anymore. Bug fixed #1624400.
  • Implemented restrictions on symlinked files (error_log, pid_file) that can’t be used with mysqld_safe. Bug fixed #1624449.

Other bugs fixed: #1553938.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Categories: MySQL

Percona Server 5.7.14-8 is now available

MySQL Performance Blog - Wed, 2016-09-21 18:11

Percona announces the GA release of Percona Server 5.7.14-8 on September 21, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.7.14, including all the bug fixes in it, Percona Server 5.7.14-8 is the current GA release in the Percona Server 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.14-8 milestone at Launchpad.

Bugs Fixed:
  • Limiting ld_preload libraries to be loaded from specific directories in mysqld_safe didn’t work correctly for relative paths. Bug fixed #1624247.
  • Fixed possible privilege escalation that could be used when running REPAIR TABLE on a MyISAM table. Bug fixed #1624397.
  • The general query log and slow query log cannot be written to files ending in .ini and .cnf anymore. Bug fixed #1624400.
  • Implemented restrictions on symlinked files (error_log, pid_file) that can’t be used with mysqld_safe. Bug fixed #1624449.

Other bugs fixed: #1553938.

The release notes for Percona Server 5.7.14-8 are available in the online documentation. Please report any bugs on the launchpad bug tracker .

Categories: MySQL

Percona Server 5.6.32-78.1 is now available

MySQL Performance Blog - Wed, 2016-09-21 18:04

Percona announces the release of Percona Server 5.6.32-78.1 on September 21st, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.6.32, including all the bug fixes in it, Percona Server 5.6.32-78.1 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.32-78.1 milestone on Launchpad.

Bugs Fixed:
  • Limiting ld_preload libraries to be loaded from specific directories in mysqld_safe didn’t work correctly for relative paths. Bug fixed #1624247.
  • Fixed possible privilege escalation that could be used when running REPAIR TABLE on a MyISAM table. Bug fixed #1624397.
  • The general query log and slow query log cannot be written to files ending in .ini and .cnf anymore. Bug fixed #1624400.
  • Implemented restrictions on symlinked files (error_log, pid_file) that can’t be used with mysqld_safe. Bug fixed #1624449.

Other bugs fixed: #1553938.

Release notes for Percona Server 5.6.32-78.1 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

Categories: MySQL

Percona Server 5.5.51-38.2 is now available

MySQL Performance Blog - Wed, 2016-09-21 17:58

Percona announces the release of Percona Server 5.5.51-38.2 on September 21, 2016. Based on MySQL 5.5.51, including all the bug fixes in it, Percona Server 5.5.51-38.2 is now the current stable release in the 5.5 series.

Percona Server is open-source and free. You can find release details of the release in the 5.5.51-38.2 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed:
  • Limiting ld_preload libraries to be loaded from specific directories in mysqld_safe didn’t work correctly for relative paths. Bug fixed #1624247.
  • Fixed possible privilege escalation that could be used when running REPAIR TABLE on a MyISAM table. Bug fixed #1624397.
  • The general query log and slow query log cannot be written to files ending in .ini and .cnf anymore. Bug fixed #1624400.
  • Implemented restrictions on symlinked files (error_log, pid_file) that can’t be used with mysqld_safe. Bug fixed #1624449.

Other bugs fixed: #1553938.

Find the release notes for Percona Server 5.5.51-38.2 in our online documentation. Report bugs on the launchpad bug tracker.

Categories: MySQL

Regular Expressions Tutorial

MySQL Performance Blog - Wed, 2016-09-21 13:48

This blog post highlights a video on how to use regular expressions.

It’s been a while since I did the MySQL QA and Bash Training Series. The 13 episodes were quite enjoyable to make, and a lot of people watched the video’s and provided great feedback.

In today’s new video, I’d like to briefly go over regular expressions. The session will cover the basics of regular expressions, and then some. I’ll follow up later with a more advanced regex session too.

Regular expressions are very versatile, and once you know how to use them – especially as a script developer or software coder – you will return to them again and again. Enjoy!

Presented by Roel Van de Paar. Full-screen viewing @ 720p resolution recommended

 

Categories: MySQL

Webinar Thursday September 22 – Black Friday and Cyber Monday: How to Avoid an E-Commerce Disaster

MySQL Performance Blog - Wed, 2016-09-21 13:11

Join Percona’s Sr. Technical Operations Architect, Tim Vaillancourt on Thursday, September 22, at 10 am PDT (UTC-7) for the webinar Black Friday and Cyber Monday: How to Avoid an E-Commerce Disaster. This webinar will provide some best practices to ensure the performance of your system under high-traffic conditions.

Can your retail site handle the traffic deluge on the busiest shopping day of the year?

Black Friday and Cyber Monday is mere months away. Major retailers have already begun stress-testing their e-commerce sites to make sure they can handle the load. Failure to accommodate the onslaught of post-Thanksgiving shoppers might result in both embarrassing headlines and millions of dollars in lost revenue. Our advice to retailers: September stress tests are essential to a glitch-free Black Friday.

This webinar will cover:

  • Tips to avoid bottlenecks in data-driven apps
  • Techniques to allow an app to grow and shrink for large events/launches
  • Solutions to alleviate load on an app’s database
  • Developing and testing scalable apps
  • Deployment strategies to avoid downtime
  • Creating lighter, faster user facing requests

For more ideas on how to optimize your E-commerce database, read Tim’s blog post here.

Please register here.

Timothy Vaillancourt, Senior Technical Operations Architect

Tim joined Percona in 2016 as Sr. Technical Operations Architect for MongoDB with a goal to make the operations of MongoDB as smooth as possible. With experience operating infrastructures in industries such as government, online marketing/publishing, SaaS and gaming, combined with experience tuning systems from the hard disk all the way up to the end-user, Tim has spent time in nearly every area of the modern IT stack with many lessons learned.

Tim is based in Amsterdam, NL and enjoys traveling, coding and music. Before Percona Tim was the Lead MySQL DBA of Electronic Arts’ DICE studios, helping some of the largest games in the world (“Battlefield” series, “Mirrors Edge” series, “Star Wars: Battlefront”) launch and operate smoothly while also leading the automation of MongoDB deployments for EA systems. Before the role of DBA at EA’s DICE studio, Tim served as a subject matter expert in NoSQL databases, queues and search on the Online Operations team at EA SPORTS. Before moving to the gaming industry, Tim served as a Database/Systems Admin operating a large MySQL-based SaaS infrastructure at AbeBooks/Amazon Inc.

Categories: MySQL

MongoDB point-in-time backups made easy

MySQL Performance Blog - Tue, 2016-09-20 23:03

In this blog post we’ll look at MongoDB point-in-time backups, and work with them.

Mongodump is the base logical backup tool included with MongoDB. It takes a full BSON copy of database/collections, and optionally includes a log of changes during the backup used to make it consistent to a point in time. Mongorestore is the tool used to restore logical backups created by Mongodump. I’ll use these tools in the steps in this article to restore backed-up data. This article assumes a mongodump-based backup that was taken consistently with oplog changes (by using the command flag “–oplog”), and the backup is being restored to a MongoDB instance.

In this example, a mongodump backup is gathered and restored for the base collection data, and separately the oplogs/changes necessary to restore the data to a particular point-in-time are collected and applied to this data.

Note: Percona developed a backup tool named mongodb_consistent_backup, which is a wrapper for ‘mongodump’ with added cluster-wide backup consistency. The backups created by mongodb_consistent_backup (in Dump/Mongodump mode) can be restored using the same steps as a regular “mongodump” backup.

Stages Stage 1: Get a Mongodump Backup Mongodump Command Flags –host/–port (and –user/–password)

Required, even if you’re using the default host/port (localhost:27017).  If authorization is enabled, add –user/–password flags also.

–oplog

Required for any replset member! Causes “mongodump” to capture the oplog change log during the backup for consistent to one point in time.

–gzip

Optional. For mongodump >= 3.2, enables inline compression on the backup files.

Steps
  1. Get a mongodump backup via (pick one):
    • Running “mongodump” with the correct flags/options to take a backup (w/oplog) of the data: $ mongodump --host localhost --port 27017 --oplog --gzip 2016-08-15T12:32:28.930+0200 writing wikipedia.pages to 2016-08-15T12:32:31.932+0200 [#########...............] wikipedia.pages 674/1700 (39.6%) 2016-08-15T12:32:34.931+0200 [####################....] wikipedia.pages 1436/1700 (84.5%) 2016-08-15T12:32:37.509+0200 [########################] wikipedia.pages 2119/1700 (124.6%) 2016-08-15T12:32:37.510+0200 done dumping wikipedia.pages (2119 documents) 2016-08-15T12:32:37.521+0200 writing captured oplog to 2016-08-15T12:32:37.931+0200 [##......................] .oplog 44/492 (8.9%) 2016-08-15T12:32:39.648+0200 [########################] .oplog 504/492 (102.4%) 2016-08-15T12:32:39.648+0200 dumped 504 oplog entries
    • Use the latest daily automatic backup, if it exists.
Stage 2: Restore the Backup Data Steps
  1. Locate the shard PRIMARY member.
  2. Triple check you’re restoring the right backup to the right shard/host!
  3. Restore a mongodump-based backup to the PRIMARY node using the steps in this article: Restore a Mongodump Backup.
  4. Check for errors.
  5. Check that all SECONDARY members are in sync with the PRIMARY.
Stage 3: Get Oplogs for Point-In-Time-Recovery

In this stage, we will gather the changes needed to roll the data forward from the time of backup to the time/oplog-position to which we would like to restore.

In this example below, let’s pretend someone accidentally deleted an entire collection at oplog timestamp: “Timestamp(1470923942, 3)” and we want to fix it. If we decrement the Timestamp increment (2nd number) of “Timestamp(1470923942, 3)” we will have the last change before the accidental command, which in this case is: “Timestamp(1470923942, 2)“. Using the timestamp, we can capture and replay the oplogs from when the backup occurred to just before the issue/error.

A start and end timestamp are required to get the oplog data. In all cases, this will need to be gathered manually, case-by-case.

Helper Script #!/bin/bash # # This tool will dump out a BSON file of MongoDB oplog changes based on a range of Timestamp() objects. # The captured oplog changes can be applied to a host using 'mongorestore --oplogReplay --dir /path/to/dump'. set -e TS_START=$1 TS_END=$2 MONGODUMP_EXTRA=$3 function usage_exit() { echo "Usage $0: [Start-BSON-Timestamp] [End-BSON-Timestamp] [Extra-Mongodump-Flags (in quotes for multiple)]" exit 1 } function check_bson_timestamp() { local TS=$1 echo "$TS" | grep -qP "^Timestamp(d+,sd+)$" if [ $? -gt 0 ]; then echo "ERROR: Both timestamp fields must be in BSON Timestamp format, eg: 'Timestamp(########, #)'!" usage_exit fi } if [ -z "$TS_START" ] || [ -z "$TS_END" ]; then usage_exit else check_bson_timestamp "$TS_START" check_bson_timestamp "$TS_END" fi MONGODUMP_QUERY='{ "ts" : { "$gte" : '$TS_START' }, "ts" : { "$lte" : '$TS_END' } }' MONGODUMP_FLAGS='--db=local --collection=oplog.rs' [ ! -z "$MONGODUMP_EXTRA" ] && MONGODUMP_FLAGS="$MONGODUMP_FLAGS $MONGODUMP_EXTRA" if [ -d dump ]; then echo "'dump' subdirectory already exists! Exiting!" exit 1 fi echo "# Dumping oplogs from '$TS_START' to '$TS_END'..." mkdir dump mongodump $MONGODUMP_FLAGS --query "$MONGODUMP_QUERY" --out - >dump/oplog.bson if [ -f dump/oplog.bson ]; then echo "# Done!" else echo "ERROR: Cannot find oplog.bson file! Exiting!" exit 1 fi

 

Script Usage: $ ./dump_oplog_range.sh Usage ./dump_oplog_range.sh: [Start-BSON-Timestamp] [End-BSON-Timestamp] [Extra-Mongodump-Flags (in quotes for multiple)]

 

Steps
  1. Find the PRIMARY member that contains the oplogs needed for the PITR restore.
  2. Determine the “end” Timestamp() needed to restore to. This oplog time should be before the problem occurred.
  3. Determine the “start” Timestamp() from right before the backup was taken.
    1. This timestamp doesn’t need to be exact, so something like a Timestamp() object equal-to “a few min before the backup started” is fine, but the more accurate you are, the fewer changes you’ll need to re-apply (which saves on restore time).
  4. Use the MongoToolsAndSnippets script: “get_oplog_range.sh (above in “Helper Script”) to dump the oplog time-ranges you need to restore to your chosen point-in-time. In this example I am gathering the oplog between two point-in-times (also passing in –username/–password flags in quotes the 3rd parameter):
    1. The starting timestamp: the BSON timestamp from before the mongodump backup in “Stage 2: Restore Collection Data” was taken, in this example. “Timestamp(1470923918, 0)” is a time a few seconds before my mongodump was taken (does not need to be exact).
    2. The end timestamp: the end BSON Timestamp to restore to, in this example. “Timestamp(1470923942, 2)” is the last oplog-change BEFORE the problem occurred.

    Example:

    $ wget -q https://raw.githubusercontent.com/percona/MongoToolsAndSnippets/master/rdba/dump_oplog_range.sh $ bash ./dump_oplog_range.sh 'Timestamp(1470923918, 0)' 'Timestamp(1470923942, 2)' '--username=secret --password=secret --host=mongo01.example.com --port=27024' # Dumping oplogs from 'Timestamp(1470923918, 0)' to 'Timestamp(1470923942, 2)'... 2016-08-12T13:11:17.676+0200&nbsp;&nbsp;&nbsp; writing local.oplog.rs to stdout 2016-08-12T13:11:18.120+0200&nbsp;&nbsp;&nbsp; dumped 22 documents # Done!

    Note: all additional mongodump flags (optional 3rd field) must be in quotes!

  5. Double check it worked by looking for the ‘oplog.bson‘ file and checking that the file has some data in it (168mb in the below example):

    $ ls -alh dump/oplog.bson -rw-rw-r--. 1 tim tim 168M Aug 12 13:11 dump/oplog.bson

     

Stage 4: Apply Oplogs for Point in Time Recovery (PITR)

In this stage, we apply the time-range-based oplogs gathered in Stage 3 to the restored data set to bring it from the time of the backup to a particular point in time before a problem occurred.

Mongorestore Command Flags –host/–port (and –user/–password)

Required, even if you’re using the default host/port (localhost:27017).  If authorization is enabled, add –user/–password flags also.

–oplogReplay

Required. This is needed to replay the oplogs in this step.

–dir

Required. The path to the mongodump data.

Steps
  1. Copy the “dump” directory containing only the “oplog.bson”. file (captured in Stage 3) to the host that needs the oplog changes applied (the restore host).
  2. Run “mongorestore” on the “dump” directory to replay the oplogs into the instance. Make sure the “dump” dir contains only “oplog.bson”! $ mongorestore --host localhost --port 27017 --oplogReplay --dir ./dump 2016-08-12T13:12:28.105+0200&nbsp;&nbsp;&nbsp; building a list of dbs and collections to restore from dump dir 2016-08-12T13:12:28.106+0200&nbsp;&nbsp;&nbsp; replaying oplog 2016-08-12T13:12:31.109+0200&nbsp;&nbsp;&nbsp; oplog&nbsp;&nbsp; 80.0 MB 2016-08-12T13:12:34.109+0200&nbsp;&nbsp;&nbsp; oplog&nbsp;&nbsp; 143.8 MB 2016-08-12T13:12:35.501+0200&nbsp;&nbsp;&nbsp; oplog&nbsp;&nbsp; 167.8 MB 2016-08-12T13:12:35.501+0200&nbsp;&nbsp;&nbsp; done
  3. Validate the data was restored with the customer or using any means possible (examples: .count() queries, some random .find() queries, etc.).
Categories: MySQL

Percona Live Europe featured talk with Marc Berhault — Inside CockroachDB’s Survivability Model

MySQL Performance Blog - Tue, 2016-09-20 16:41

Welcome to another Percona Live Europe featured talk with Percona Live Europe 2016: Amsterdam speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference. We’ll also discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live Europe registration bonus!

In this Percona Live Europe featured talk, we’ll meet Marc Berhault, Engineer at Cockroach Labs.His talk will be on Inside CockroachDB’s Survivability Model. This talk takes a deep dive into CockroachDB, a database whose “survive and thrive” model aims to bring the best aspects of Google’s next generation database, Spanner, to the rest of the world via open source.

I had a chance to speak with Marc and learn a bit more about these questions:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Marc: I started out as a Site Reliability Engineer managing Google’s storage infrastructure (GFS). Back in those days, keeping a cluster up and running mostly meant worrying about the masters.

I then switched to a developer role on Google’s next-generation storage system, which replaced the single write master with sharded metadata handlers. This increased the reliability of the entire system considerably, allowing for machine and network failures. SRE concerns gradually shifted away from machine reliability towards more interesting problems, such as multi-tenancy issues (quotas, provisioning, isolation) and larger scale failures.

After leaving Google, I found myself back in a world where one had to worry about a single machine all over again – at least when running your own infrastructure. I kept hearing the same story: a midsize company starts to grow out of its single-machine database and starts trimming the edges. This means moving tables to other hosts, shrinking schemas, etc., in order to avoid the dreaded “great sharding of the monolithic table,” often accompanied by its friends: cross-shard coordination layer and production complexity.

This was when I joined Cockroach Labs, a newly created startup with the goal of bringing a large-scale, transactional, strongly consistent database to the world at large. After contributing to various aspects of the projects, I switched my focus to production: adding monitoring, working on deployment, and of course rolling out our test clusters.

Percona: Your talk is called “Inside CockroachDB’s Survivability Model.” Define “survivability model”, and why it is important to database environments.

Marc: The survivability model in CockroachDB is centered around data redundancy. By default, all data is replicated three times (this is configurable) and is only considered written if a quorum exists. When a new node holding one of the copies of the data becomes unavailable, a node is picked and given a snapshot of the data.

This redundancy model has been widely used in distributed systems, but rarely with strongly consistent databases. CockroachDB’s approach provides strong consistency as well as transactions across the distributed data. We see this as a critical component of modern databases: allowing scalability while guaranteeing consistency.

Percona: What are the workloads and database environments that are best suited for a CockroachDB deployment? Do you see an expansion of the solution to encompass other scenarios?

Marc: CockroachDB is a beta product and is still in development. We expect to be out of beta by the end of 2016. Ideal workloads are those requiring strong consistency – those applications that manage critical data. However, strong consistency comes at a cost, usually directly proportional to latency between nodes and replication factor. This means that a widely distributed CockroachDB cluster (e.g., across multiple regions) will incur high write latencies, making it unsuitable for high-throughput operations, at least in the near term.

Percona: What is changing in the way businesses use databases that keeps you awake at night? How do you think CockroachDB is addressing those concerns?

Marc: In recent years, more and more businesses have been reaching the limits of what their single-machine databases can handle. This has forced many to implement their own transactional layers on top of disjoint databases, at the cost of longer development time and correctness.

CockroachDB attempts to find a solution to this problem by allowing a strongly consistent, transactional database to scale arbitrarily.

Percona: What are looking forward to the most at Percona Live Europe this year?

Marc: This will be my first time at a Percona Live conference, so I’m looking forward to hearing from other developers and learning what challenges other architects and DBAs are facing in their own work.

You can read more about Marc’s thoughts on CockroachDB at their blog.

Want to find out more about Marc, CoachroachDB and survivability? Register for Percona Live Europe 2016, and come see his talk Inside CockroachDB’s Survivability Model.

Use the code FeaturedTalk and receive €25 off the current registration price!

Percona Live Europe 2016: Amsterdam is the premier event for the diverse and active open source database community. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, and other open source databases. Percona live tackles subjects such as analytics, architecture and design, security, operations, scalability and performance. It also provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience. All of these people help you learn how to tackle your open source database challenges in a whole new way.

This conference has something for everyone!

Percona Live Europe 2016: Amsterdam is October 3-5 at the Mövenpick Hotel Amsterdam City Centre.

Amsterdam eWeek

Percona Live Europe 2016 is part of Amsterdam eWeek. Amsterdam eWeek provides a platform for national and international companies that focus on online marketing, media and technology and for business managers and entrepreneurs who use them, whether it comes to retail, healthcare, finance, game industry or media. Check it out!

Categories: MySQL
Syndicate content