Buy Percona ServicesBuy Now!
Subscribe to Latest MySQL Performance Blog posts feed
Updated: 2 hours 35 min ago

Performance improvements in Percona XtraDB Cluster 5.7.17-29.20

April 19, 2017 - 6:50am

In our latest release of Percona XtraDB Cluster, we’ve introduced major performance improvements to the MySQLwrite-set replication layer. In this post, we want to show what these improvements look like.

For the test, we used the sysbench OLTP_RW, UPDATE_KEY and UPDATE_NOKEY workloads with 100 tables, 4mln rows each, which gives about 100GB of datasize. In all the tests we use a three-node setup, connected via a 10GB network, with the sysbench load directed to the one primary node.

In the first chart, we show improvements comparing to the previous version (5.7.16):

The main improvements come from concurrent workloads, under multiple threads.

The previous chart is for cases using enabled binary logs, but in some situations we will have deployments without binary logs enabled (Percona XtraDB Cluster does not require them). The latest release significantly improves performance for this case as well.

Here is a chart showing throughput without binary logs:

Where does Percona XtraDB Cluster place in comparison with similar technologies? To find out, we’ll compare this release with MySQL 5.7.17 Group Replication and with the recently released MariaDB 10.2.5 RC.

For MySQL 5.7.17 Group Replication, I’ve reviewed two cases: “durable” with sync_binlog=1, and “relaxed durability” with sync_binlog=0.

Also for MySQL 5.7.17 Group Replication, we want to review two cases with different flow_control settings. The first setting is flow_control=25000 (the default setting). It provides better performance, but with the drawbacks that non-primary nodes will fall behind significantly and MySQL Group Replication does not provide a way to protect from reading stale data. So with a default flow_control=25000, we risk reading very outdated data. We also tested MySQL Group Replication with flow_control=1000 to minimize stale data on non-primary nodes.

A note on the Flow Control topic: it is worth mentioning that we also changed the flow_control default for Percona XtraDB Cluster. The default value is 100 instead of 16 (as in version 5.7.16).

Comparison chart with sync_binlog=1 (for MySQL Group Replication):

Comparison chart with sync_binlog=0 (for MySQL Group Replication):

So there are couple conclusions we can make out of these charts.

  1. The new version of Percona XtraDB Cluster performs on the level with MySQL Group Replication
  2. flow_control for MySQl Group Replication really makes a difference for performance, and default flow_control=25000 is better (with the risk of a lot of outdated data on non-primary nodes)

The reference our benchmark files and config files are here.

Percona XtraDB Cluster 5.7.17-29.20 is now available

April 19, 2017 - 6:37am

Percona announces the release of Percona XtraDB Cluster 5.7.17-29.20 on April 19, 2017. Binaries are available from the downloads section or our software repositories.

NOTE: You can also run Docker containers from the images in the Docker Hub repository.

Percona XtraDB Cluster 5.7.17-29.20 is now the current release, based on the following:

All Percona software is open-source and free.

Performance Improvements

This release is focused on performance and scalability with increasing workload threads. Tests show up to 10 times increase in performance.

Fixed Bugs

  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • Improved SST and IST log messages for better readability and unification.
  • Excluded the garbd node from flow control calculations.
  • Added extra checks to verify that SSL files (certificate, certificate authority, and key) are compatible before openning connection.
  • Improved parallelism for better scaling with multiple threads.
  • Added validations for DISCARD TABLESPACE and IMPORT TABLESPACE in PXC Strict Mode to prevent data inconsistency.
  • Added the wsrep_flow_control_status variable to indicate if node is in flow control (paused).
  • PXC-766: Added the wsrep_ist_receive_status variable to show progress during an IST.
  • Allowed CREATE TABLE ... AS SELECT (CTAS) statements with temporary tables (CREATE TEMPORARY TABLE ... AS SELECT) in PXC Strict Mode. For more information, see 1666899.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-783: Improved the wsrep stage framework.
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-795: Set --parallel=4 as default option for wsrep_sst_xtrabackup-v2 to run four threads with XtraBackup.
  • PXC-797: Blocked wsrep_desync toggling while node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • PXC-805: Inherited upstream fix to avoid using deprecated variables, such as INFORMATION_SCHEMA.SESSION_VARIABLE. For more information, see 1676401.
  • PXC-811: Changed default values for the following variables:
    • fc_limit from 16 to 100
    • send_window from 4 to 10
    • user_send_window from 2 to 4
  • Moved wsrep settings into a separate configuration file (/etc/my.cnf.d/wsrep.cnf).
  • Fixed mysqladmin shutdown to correctly stop the server on systems using systemd.
  • Fixed several packaging and dependency issues.

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!

Percona XtraBackup 2.4.7 is Now Available

April 18, 2017 - 12:19pm

Percona announces the GA release of Percona XtraBackup 2.4.7 on April 18, 2017. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New features:
  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:
  • Backups were missing *.isl files for general tablespace. Bug fixed #1658692.
  • In 5.7, MySQL changed default checksum algorithm to crc32, while xtrabackup was using innodb. This caused xtrabackup to perform extra checksum calculations which were not needed. Bug fixed #1664405.
  • For system tablespaces consisting of multiple files xtrabackup updated LSN only in first file. This caused MySQL versions lower than 5.7 to fail on startup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

Release notes with all the bugfixes for Percona XtraBackup 2.4.7 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Percona XtraBackup 2.3.8 is Now Available

April 18, 2017 - 11:36am

Percona announces the release of Percona XtraBackup 2.3.8 on April 18, 2017. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series.

New Features
  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:
  • xtrabackup would not create fresh InnoDB redo logs when preparing incremental backup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

Release notes with all the bugfixes for Percona XtraBackup 2.3.8 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

M17 Conference Observations on the Future of MariaDB

April 18, 2017 - 10:52am

In this blog post, I’ll discuss some of my thoughts about the future of MariaDB after attending the M17 Conference.

Let me start with full disclosure: I’m the CEO of Percona, and we compete with the MariaDB Corporation in providing Support for MariaDB and other services. I probably have some biases!

Last week I attended the MariaDB Developers UnConference and the M17 Conference, which provided great insights into MariaDB’s positioning as a project and as a business. Below are some of my thoughts as I attended various sessions at the conference:

Breaking away from their MySQL past. Michael Howard’s (MariaDB CEO) keynote focused on breaking away from the past and embracing the future. In this case, the “past” means proprietary databases. But I think MariaDB is also trying to break away from their past of being a MySQL variant, and focus on becoming completely independent technology. If I didn’t know their history, I wouldn’t recognize how much codebase MariaDB shares with MySQL – and how much MariaDB innovation Oracle still drives.

MySQL compatibility is no longer the primary goal. In its first version, MariaDB 5.1 was truly compatible with MySQL (it had relatively few differences). By contrast, MariaDB 10.2 has different replication, JSON support and a very different optimizer. With MariaDB 10.3, more changes are planned for InnoDB on disk format, and no plans exist to remove .frm files and use the MySQL 8 Data Dictionary. With these features, another level of MySQL compatibility is removed. The MariaDB knowledgebase states: “For all practical purposes, MariaDB is a binary drop in replacement for the same MySQL version.” The argument can still be made that this is true for MySQL 5.7 (as long as your application does not use some of the new features), but this does not seem to be the case for MySQL 8.

The idea seems to be that since MariaDB has replaced MySQL in many (most?) Linux distributions, and many people use MariaDB when they think they are using MySQL, compatibility is not that big of a deal anymore.

Embracing contributions and keeping the development process open is a big focus of MariaDB. Facebook, Google, Alibaba and Tencent have contributed to MariaDB, along with many independent smaller companies and independent developers (Percona among them). This is different from the MySQL team at Oracle, who have provided some contributions, but not nearly to the extent that MariaDB has. An open source model is a double-edged sword – while it gives you more features, it also makes it harder to maintain a consistent user experience and consistent quality of code and documentation. It will be interesting to see how MariaDB deals with these challenges.

Oracle compatibility. MariaDB strives to be the open source database that is the most compatible with Oracle, and therefore the easiest to migrate to. I have heard people compare MariaDB’s desire for Oracle compatibility to EDB Postgres – only with the advantage of being open source as opposed to proprietary software.  For MariaDB 10.3 (alpha), they are developing support for Oracle PL/SQL syntax for stored procedures to be able to migrate applications with little, if any, changes. They are also developing support for SEQUENCE and other Oracle features, including a special sql_mode=ORACLE to maximize compatibility.

BSL as a key for success. When it comes to business source licensing (BSL), I couldn’t quite resolve the conflict I found in MariaDB’s messaging. On the one hand, MariaDB promotes open source as a great way to escape vendor lock-in (which we at Percona completely agree with). But on the other hand, Michael Howard stated that BSL software (“Eventual Open Source”) is absolutely critical for MariaDB’s commercial success. Is the assumption here that if vendor lock-in is not forever, it is not really lock-in? Currently, only MariaDB MaxScale is BSL, but it sounds like we should expect more of their software to follow this model.

Note. As MariaDB Server and MariaDB Columnstore use a lot of Oracle’s GPL code, these will most likely remain GPL.

I enjoyed attending both conferences. I had a chance to meet with many old friends and past colleagues, as well as get a much better feel for where MariaDB is going and when it is appropriate to advise its usage.

Experimental Build of MyRocks with Percona Server for MySQL

April 17, 2017 - 5:07pm

We have been working on bringing out a build of MyRocks with Percona Server for MySQL.

MyRocks is a RocksDB-based storage engine. You can find more information about MyRocks here.

While there is still a lot of work to do, I want to share an experimental build of Percona Server for MySQL with MyRocks, which you can use to evaluate and test this engine

(WARNING: in NO WAY is this build supposed to be for production usage! Consider this ALPHA quality.)

The tar.gz binaries are available from our TESTING area. To start Percona Server for MySQL with the MyRocks engine, use following line in my.cnf:

plugin-load=rocksdb=ha_rocksdb.so;rocksdb_cfstats=ha_rocksdb.so;rocksdb_dbstats=ha_rocksdb.so;rocksdb_perf_context=ha_rocksdb.so;rocksdb_perf_context_global=ha_rocksdb.so;rocksdb_cf_options=ha_rocksdb.so;rocksdb_compaction_stats=ha_rocksdb.so;rocksdb_global_info=ha_rocksdb.so;rocksdb_ddl=ha_rocksdb.so;rocksdb_index_file_map=ha_rocksdb.so;rocksdb_locks=ha_rocksdb.so;rocksdb_trx=ha_rocksdb.so

Later we will provide experimental RPM and DEB packages from our testing repositories. Please let us know how MyRocks is working for you!

The mysqlpump Utility

April 17, 2017 - 10:45am

In this blog, we’ll look at the mysqlpump utility.

mysqlpump is a utility that performs logical backups (which means backing up your data as SQL statements instead of a raw copy of data files). It was added in MySQL Server version 5.7.8, and can be used to dump a database or a set of databases to a file and then loaded on another SQL server (not necessarily a MySQL server).

Its usage is similar to mysqldump, but it includes a new set of features. Many of the options are the same, but it was written from scratch to avoid being limited to mysqldump compatibility.

The Main Features Include:
  • To make the dump process faster, it allows parallel processing of databases and objects within databases.
  • There are more options to customize your dumps and choose which databases and objects to dump (tables, stored programs, user accounts), using the --include-* and --exclude-* parameters.
  • User accounts can be dumped now as CREATE USER and GRANT statements, instead of inserting directly to the MySQL system database.
  • Information between the client and the server can be compressed using the --compress option. This feature is very useful for remote backups, as it saves bandwidth and transfer time. You can also compress the output file using--compress-output, which supports ZLIB and LZ4 compression algorithms.
  • It has an estimated progress indicator. This is really useful to check the current status of the dump process. You can see the total amount of rows dumped and the number of databases completed. It also reports an estimate of the total time to complete the dump.
  • Creation of secondary indexes for InnoDB tables happens after data load for shorter load times.
Exclude/Include:

This feature provides more control over customizing your dumps, and filter the data that you need. Using this feature, you can be more selective with the data you want to dump (databases, tables, triggers, events, routines, users) and save file size, process time and transferring time while copying/moving the file to another host.

Keep in mind that there are some options that are mutually exclusive: e.g., if you use the --all-databases option, the --exclude-databases  parameter won’t take effect. By default, mysqlpump will not dump the following databases unless you specify them using the --include-databases option: INFORMATION_SCHEMA, performance_schema, ndbinfo  and sys.

Values for these options need to be declared by comma-separated listing. Using a “%” as a value for any of the exclude/include options acts as a wildcard. For example, you can dump all databases starting with “t” and “p” by adding the option --include-databases=t%,p%  to the command line.

For users, routines, triggers and events, mysqlpump has --include-* and --exclude-* options with similar usage. Some specific notes:

  • Triggers are dumped by default, but you can also filter them using the --include-triggers/--exclude-triggers options
  • Routines and events are not dumped by default, and need to be specified in the command line with --routines and --events, or the corresponding --include and --exclude options
  • Keep in mind that if a stored procedure and a function have the same name, then include/exclude applies to both
Parallel Processing:

This feature allows you to process several databases, and tables within the databases, in parallel. By default, mysqlpump uses one processing queue with two threads. You can increase the number of threads for this default queue with --default-parallelism. Unless you create additional queues, all the databases and/or tables you elect to dump go through the default queue.

To create additional queues you can use the --parallel-schemas option, which takes two parameters: the number of threads for the queue and the sub-set of databases this queue processes.  As an example, you could run:

mysqlpump --include-databases=a,b,c,d,e,f,g,h --default-parallelism=3 --parallel-schemas=4:a,b

so that schemas c, d, e, f, g and h are processed by the default queue (which uses three threads), and then tables from schemas a and b are processed by a separate queue (that uses four threads). Database names should be included as a comma-separated list:

$ mysqlpump --parallel-schemas=4:example1,example2,example3 --parallel-schemas=3:example4,example5 > examples.sql Dump progress: 0/1 tables, 250/261184 rows Dump progress: 24/30 tables, 1204891/17893833 rows Dump progress: 29/30 tables, 1755611/17893833 rows Dump progress: 29/30 tables, 2309111/17893833 rows ... Dump completed in 42424 milliseconds

User Accounts:

User accounts can be dumped using this tool. Here’s a comparison of our Percona Tool pt-show-grants versus mysqlpump to check their differences.

By default, mysqlpump doesn’t dump user account definitions (even while dumping the MySQL database). To include user accounts on the dump, you must specify the --users option.

Here’s an example on how use mysqlpump to get only user accounts dumped to a file:

$ mysqlpump --exclude-databases=% --exclude-triggers=% --users -- Dump created by MySQL dump utility, version: 5.7.8-rc, linux-glibc2.5 (x86_64) -- Dump start time: Thu Aug 27 17:10:10 2015 -- Server version: 5.7.8 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_TIME_ZONE=@@TIME_ZONE; SET TIME_ZONE='+00:00'; SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8; CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%'; CREATE USER 'msandbox_ro'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'127.%'; CREATE USER 'msandbox_rw'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'127.%'; CREATE USER 'rsandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%'; CREATE USER 'furrywall'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*AB8D50A9E3B8D1F3ACE85C54736B5BF472B44539' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK; GRANT USAGE ON *.* TO 'furrywall'@'localhost'; CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost'; CREATE USER 'msandbox_ro'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'localhost'; CREATE USER 'msandbox_rw'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'localhost'; CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; CREATE USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6E543F385210D9BD42A4FDB4BB23FD2C31C95462' REQUIRE NONE PASSWORD EXPIRE INTERVAL 30 DAY ACCOUNT UNLOCK; GRANT USAGE ON *.* TO 'testuser'@'localhost'; SET TIME_ZONE=@OLD_TIME_ZONE; SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT; SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS; SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- Dump end time: Thu Aug 27 17:10:10 2015 Dump completed in 823 milliseconds

As you can see, above the tool makes sure the session uses known values for timezone and character sets. This won’t affect users, it’s part of the dump process to ensure correctness while restoring on the destination.

Comparing it with pt-show-grants from Percona Toolkit, we can see that mysqlpump dumps the CREATE USER  information as well. The statements produced by mysqlpump are the right thing to run to recreate users (and should be the preferred method), especially because of the sql_mode NO_AUTO_CREATE_USERS. If enabled, it renders pt-show-grants useless.

Here’s an example of pt-show-grants usage:

$ pt-show-grants --host 127.0.0.1 --port 5708 --user msandbox --ask-pass Enter password: -- Grants dumped by pt-show-grants -- Dumped from server 127.0.0.1 via TCP/IP, MySQL 5.7.8-rc at 2015-08-27 17:06:52 -- Grants for 'furrywall'@'localhost' GRANT USAGE ON *.* TO 'furrywall'@'localhost'; -- Grants for 'msandbox'@'127.%' GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%'; -- Grants for 'msandbox'@'localhost' GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost'; -- Grants for 'msandbox_ro'@'127.%' GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'127.%'; -- Grants for 'msandbox_ro'@'localhost' GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'localhost'; -- Grants for 'msandbox_rw'@'127.%' GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'127.%'; -- Grants for 'msandbox_rw'@'localhost' GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'localhost'; -- Grants for 'root'@'localhost' GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION; -- Grants for 'rsandbox'@'127.%' GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%'; -- Grants for 'testuser'@'localhost' GRANT USAGE ON *.* TO 'testuser'@'localhost';

Some Miscellaneous Notes:
  • One of the differences with mysqldump is that mysqlpump adds CREATE DATABASE statements to the dump by default, unless specified with the --no-create-db option.
    • There’s an important difference on the dump process that is closely related: it includes the database name while adding the CREATE TABLE statement. This causes a problem when trying to use the tool to create a duplicate.

Run Percona Server on Bash on Windows on Ubuntu

April 14, 2017 - 1:54pm

In this post, I’ll explain how to run Percona Server for MySQL and Percona Server for MongoDB on Bash on Windows on Ubuntu.

We are getting a good number of questions about whether Percona Server (for both MySQL and MongoDB) is available for Windows for evaluation or development purposes. I want to provide a guide to how to get it running.

In comments to the post Running Percona XtraBackup on Windows … in Docker, Peter Laursen recommend Bash on Ubuntu on Windows. That hadn’t occurred to me before, so the credit goes to Peter Laursen.

As of that older post, it appears that Percona XtraBackup was not working right in Bash on Ubuntu on Windows. But in my latest test on Windows 10 Creators Edition, the problem seems resolved.

But you can get Percona Server for MySQL Percona Server for and MongoDB)  running in Bash on Windows on Ubuntu right away. It is quite easy and does not require extra work. Probably the biggest step is to get Bash on Ubuntu on Windows enabled by itself. A manual on how to do this is here:

https://www.howtogeek.com/249966/how-to-install-and-use-the-linux-bash-shell-on-windows-10/

After this, follow the steps to install Percona Server for MySQL or Percona Server for MongoDB from our Ubuntu repositories:

After this, you can start the server as you would in a regular Linux environment.

TokuDB Hotbackup and Replication

April 14, 2017 - 11:10am

TokuDB Hotbackup is a solution that allows you to do backups on the fly. It works as a library that intercepts certain system calls that duplicate data written to already copied parts of files, so that at the end of the backup process the copied files contain the same content as the original files. There are several blog posts describing how TokuDB Hot Backup works in details:

Replication often uses backups replication to create slaves. For this purpose, we need to know the last executed GTID or binary log position both for the slave and master configurations.

You can obtain the corresponding information with SHOW MASTER/SLAVE STATUS. But if we request this information after the backup, the corresponding binlog position or executed GTID can be changed and the backed-up data won’t correspond to the master/slave state.

It’s possible to lock tables with FLUSH TABLE WITH READ LOCK, or use more smart locks like LOCK TABLES/BINLOG FOR BACKUP. See this  blog post for details: Introducing backup locks in Percona Server.

But the larger question is when can we use the corresponding locks? We could lock binlog or some table before getting a backup, and then release after it’s done. But if the data being backed-up is big enough the backup itself can take time — and all that time the data is locked for changes.

There must be a more suitable solution. Let’s recall how TokuDB Hotbackup works: it uses a special library that intercepts some system calls and it has an API. This API includes such commands as “start capturing” and “stop capturing”. “Capturing” means that if some part of a file gets copied to a backup location, and this part is changed (by the MySQL server in our case), then these changes are also applied to the backup location. TokuDB Hotbackup plugin uses the API, starts capturing, make copies and stops capturing.

There is no need to lock the binlog during the copy process, because when the binlog is flushed the changes are copied to backup by a “capturing” mechanism. After everything has been copied, and with the capturing still running, this is a good time for LOCK BINLOG FOR BACKUP execution. After this statement is executed, the binlog is flushed, the flushed changes are captured and applied to a backup location, and any queries that could change the binlog position or executed GTID are blocked. After this we can stop capturing, get the binlog position or the last executed GTID and unlock the binlog.

This is how it’s implemented in TokuDB Hotbackup. After a backup is taken, there are “tokubackup_slave_info” and “tokubackup_binlog_info” files in the backup directory that contain the corresponding information about the slave and master (in human-readable format). You can use this information to start a new slave from the master or slave. 5.7 supports a multisource replication, and “tokubackup_slave_info” contains information for all replication channels.

There could be a happy ending here, but the devil is in the details. For example, there are several binary logging formats: RBR, SBR, MBR (for details see https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html). In the case of SBR or MBR, a binary log event can contain statements that produce temporary tables on the slave side, and the result of further statements can depend on the content of the temporary tables.

Usually, temporary tables are created in a separate directory that is out of a MySQL data directory, and aren’t backed up. That is why if we create a backup when temporary tables produced by binary log events exist, and then try to restore the backup, the temporary tables aren’t restored (as they were not backed up). If we try to restore the replication from the point saved during the backup, and after this point the binary log contains events that use the content of non-restored temporary tables, the data will be inconsistent.

That is why the so-called “safe-slave” mode is implemented in TokuDB Hotbackup. The same mode is implemented in Percona XtraBackup, and its name was also inherited from Percona XtraBackup. In this mode, along with LOCK BINLOG FOR BACKUP statement execution, the slave SQL thread is stopped. After this it is checked to see if temporary tables produced by slave SQL thread exist or not. If yes, then the slave SQL thread is restarted until there are no temporary tables (or the certain timeout is reached).

For those purposes, we introduced the following new system variables:

  • --tokudb-backup-safe-slave – turn on/off safe-slave mode
  • --tokudb-backup-safe-slave-timeout – maximum amount of time in seconds to wait until temp tables disappear

Note for the case of multisource replication, the simplified version of this feature is implemented. So if there are several replication channels, and for some of them the SQL thread is started while for others the thread is stopped, the TokuDB Hotbackup does not restore the channels state. It just restarts the SQL threads for all channels, and after the backup SQL threads for all channels will be started.

You can’t use this option for group-replication.

This could be the happy ending, but… well, you know, it’s complicated. Apart from replication features in the MySQL server, there are a lot of engine-specific features. One of them is how frequently the recovery log is synchronized.

For TokuDB, there are two system variables you can use to tune recovery log synchronization frequency: tokudb_commit_sync and tokudb_fsync_log_period. By playing with these variables, you can establish some tradeoff between durability and performance. Here is a good blogpost about these parameters: High Insertion Rates into a TokuDB Table with Durable Transactions.

But let’s imagine we have a certain recovery log synchronization period, and the backup finished somewhere in the middle of this period. It’s OK if we use the backup just to restore some data, because the data is restored using recovery and rollback logs during the server’s start.

But if we also want to start the slave using the information stored in the “tokubackup_slave_info” and “tokubackup_binlog_info” files, there might be a situation where after the recovery stage the data in the database is behind the replication position stored during backup. See this bug description https://bugs.launchpad.net/percona-server/+bug/1533174 for details. Now, when capturing is still active, TokuDB forcibly synchronizes the recovery log when the copy process is finished.

So this could be the happy ending but… Well, actually I hope this is a happy ending! At least we’ve implemented the general functionality for creating slaves using TokuDB Hotbackup. However, as “ the show must go on”, or “the spice must flow” etc., we are happy to discuss any concerns, ideas, proposals and bug reports.

Thanks to all who took part in development: George O. Lorch III, Sergey Glushchenko, Shahriyar Rzaev, Alexey Kopytov (as the author of “LOCK BINLOG FOR BACKUP” feature) and other people from Percona team and community.

TokuDB Troubleshooting: Q & A

April 13, 2017 - 10:08am

In this blog, we will provide answers to the Q & A for the TokuDB Troubleshooting webinar.

First, we want to thank everybody for attending the March 22, 2017 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that we were unable to answer during the webinar:

Q: Is it possible to load specific tables or data from the backup?

A: Do you mean the backup created by TokuBackup? No, this is not possible. You have to restore the full backup to a temporary instance, then perform a logical dump/reload of the specific tables you want to restore.

Q: Since it is not recoverable when corruption happens, we have a risk of losing data. Is incremental backup the only option?

A: TokuBackup currently does not support incremental backup options. You can only create a kind of incremental backup if you copy and store the binary logs on a separate server, and then apply them on top of a previous full backup.

Percona Live Featured Session with Wei Hu – AliSQL: Breakthrough for the Future

April 13, 2017 - 8:31am

Welcome to another post in the series of Percona Live featured session blogs! In these blogs, we’ll highlight some of the session speakers that will be at this year’s Percona Live conference. We’ll also discuss how these sessions can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured session, we’ll meet Wei Hu, Staff Engineer at Alibaba. His session (along with co-presenter Dengcheng He, Senior Staff Engineer at Alibaba) is AliSQL: Breakthrough for the Future. AliSQL is a MySQL branch maintained by the Alibaba Database Team. AliSQL has made many improvements over the last year in its efforts to make it a high-performance, high-availability and low-maintenance storage engine option.

I had a chance to speak with Wei about AliSQL:

Percona: How did you get into database technology? What do you love about it?

Wei: I worked on an RDBMS storage engine project in graduate school, where I spent years studying database theory, and experienced the charm of database systems.

Before joining the Alibaba Group, I worked for Netease. I developed another storage engine called TNT (Transactional/Non-Transactional) for MySQL 5.1 and 5.5. During this project, I had the opportunity to learn about and gain a deep understanding of the MySQL source code. Last year I joined the Alibaba group. Alibaba’s E-Commerce business has extreme RDBMS demands, and my work here is making AliSQL faster, safer and more efficient.

Percona: Your talk is called AliSQL: Breakthrough for the Future. What is AliSQL, and what workloads could benefit from it?

Wei: Last year, we joined Percona Live for the very first time. We brought the world AliSQL. AliSQL is a fork of MySQL(based on the community version) tailored for Alibaba’s business characteristics and requirements. AliSQL is focused on extreme performance and data consistency.  As many people know, AliSQL supports the world’s largest throughput of OLTP system. This has been demonstrated in the Singles’ Day shopping festival. Compared to the community version, AliSQL can offer high throughput, high concurrency and low latency at the same time.

Last Percona Live, We share many of the improvements we made, including Column Compression, Double Read Buffer, SQL Firewall and so on. This year we’re bringing the world a brand new AliSQL.

Firstly, we developed the new “Hot SKU” feature. We were not satisfied with AliSQL’s previous performance (5,000 single key updates per second). We developed a new Group update algorithm to improve throughputs to 100,000 single key updates per second. Panic buying is no longer an annoying problem in our e-commerce scenario.

Secondly, based on the InnoDB memcache plugin, AliSQL developed X-KV, a new powerful Key-Value interface. X-KV implements a new protocol with more operation and data type support. Our customers used X-KV as a memory cache, and save the use of hundreds of machines in a production environment.

In addition, based on AliSQL we have developed X-Cluster. X-Cluster uses X-Paxos (Alibaba’s consensus library) to replicate data among instances. It supports high availability and high reliability. X-Cluster has better performance compared to Group Replication. Our benchmarking shows that X-Cluster has five times the throughput of Group Replication (for MySQL 5.7.17) in our high latency network. Furthermore, X-Cluster has many customization features for Alibaba’s production environment, such as leader election priority, LogType instance (low cost), etc.

Percona: How does the latest version of AliSQL make DBAs’ work easier?

Wei: With new “Hot SKU” feature, DBAs do not need to scale out instances for panic buying. With AliSQL X-KV, DBAs do not need to care about schema changes anymore. With AliSQL X-Cluster, DBAs don’t need to worry about data inconsistency problems. All the data transfer systems for AliSQL can use X-Paxos SDK to communicate with X-Cluster. DBAs do not need to set the log position. All is handled by X-Cluster itself.

Percona: What do you want attendees to take away from your session? Why should they attend?

Wei: In my session, I will share the AliSQL HOT SKU, X-KV and X-Cluster internals. Other developers can gain insights and spark new ideas from the talk.

Percona: What are you most looking forward to at Percona Live 2017?

Wei: I am looking forward to chatting with MySQL developers from all over the world, and making more friends.

Register for Percona Live Data Performance Conference 2017, and see Wei and Dengcheng present AliSQL: Breakthrough for the Future. Use the code FeaturedTalk and receive $100 off the current registration price!

Percona Live Data Performance Conference 2017 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community, as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 24-27, 2017 at the Hyatt Regency Santa Clara and the Santa Clara Convention Center.

At Percona Live 2017: Many Technical MariaDB Server Sessions!

April 12, 2017 - 12:56pm

At Percona, we support MariaDB Server (in addition to MySQL, Percona Server for MySQL, MongoDB and Percona Server for MongoDB, of course!), and that is reflected in the good, high-quality technical content about MariaDB Server at the Percona Live Open Source Database Conference 2017 in Santa Clara.

MariaDB is a fork of MySQL, developed and maintained by some of the original creators of MySQL (most notably, Michael “Monty” Widenius). At Percona Live, learn about how MariaDB promises to be a drop-in replacement for MySQL – with better performance and more flexibility – in our MariaDB track. These sessions tackle subjects on analytics, architecture and design, security, operations, scalability and performance for MariaDB.

If you’re hungry for good MariaDB Server content, this is what you’ll probably want to attend:

Monday (tutorial day)

  • Come to the MyRocks Deep Dive Tutorial by Yoshinori Matsunobu. Percona Server for MySQL and MariaDB Server will include the new storage engine in production. You should attend this tutorial if you want to learn how to use it.

Tuesday

Wednesday

Thursday

So there is plenty of MariaDB Server related content to fill you up while attending Percona Live. Use the code SeeMeSpeak to get 10% off your tickets. What are you waiting for, register now!

ProxySQL Rules: Applying and Chaining the Rules

April 12, 2017 - 11:55am

In this post, I am going to show you how you can minimize the performance impact of ProxySQL rules by using some finesse.

Apply Test

In my previous post, we could see the effect of the rules on ProxySQL performance. As we could also see, the “apply” option does not help with 1000 tables. Are we sure about this? Let’s consider: if we know 90% of our traffic won’t match any rules, it doesn’t matter if we have 10 or 500 rules – it has to check all of them. And this is going to have a serious effect on performance. How can we avoid that?

Let’s insert rule number ONE, which matches all queries, like this:

insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest,apply) values('testuser_rw',600,1,3,'(from|into|update|into table) sbtest([1-9]d{3,}|[1-9][0-9][1-9])b',1);

This rule matches all queries where table names > sbtest100. But again, this logic also can be applied on “userids” or any other keys. We just have to know our application and our query distribution.

With this rule, the 90% of the queries have to check only one rule (the first one):

Now we have 101 rules, but the performance is almost the same as when we had only ten rules! As we can see, creating the rules based on our query distribution has a huge impact!

But what if we don’t know which queries are the busiest, or every query has the same amount of hits? Can we do anything? Yes, we can.

Chaining

In my previous post, I mentioned the “flagIN”, “flagOUT” options. With these options we can chain the rules. But why is that good for us?

If we have 100 rules and 100 tables, even with applying, on average ProxySQL has to check 50 rules. But if we write rules like these:

insert into mysql_query_rules (flagin,flagout,username,active,retries,match_digest,apply) VALUES (0,1000,'testuser_rw',1,3,'(from|into|update|into table) sbtest.b',0), (0,1100,'testuser_rw',1,3,'(from|into|update|into table) sbtest1.b',0), (0,1200,'testuser_rw',1,3,'(from|into|update|into table) sbtest2.b',0), (0,1300,'testuser_rw',1,3,'(from|into|update|into table) sbtest3.b',0), (0,1400,'testuser_rw',1,3,'(from|into|update|into table) sbtest4.b',0), (0,1500,'testuser_rw',1,3,'(from|into|update|into table) sbtest5.b',0), (0,1600,'testuser_rw',1,3,'(from|into|update|into table) sbtest6.b',0), (0,1700,'testuser_rw',1,3,'(from|into|update|into table) sbtest7.b',0), (0,1800,'testuser_rw',1,3,'(from|into|update|into table) sbtest8.b',0), (0,1900,'testuser_rw',1,3,'(from|into|update|into table) sbtest9.b',0); insert into mysql_query_rules (flagin,destination_hostgroup,active,match_digest,apply) VALUES (1100,600,1,'(from|into|update|into table) sbtest11b',1), (1100,600,1,'(from|into|update|into table) sbtest12b',1), (1100,600,1,'(from|into|update|into table) sbtest13b',1), (1100,600,1,'(from|into|update|into table) sbtest14b',1), (1100,600,1,'(from|into|update|into table) sbtest15b',1), (1100,600,1,'(from|into|update|into table) sbtest16b',1), (1100,600,1,'(from|into|update|into table) sbtest17b',1), (1100,600,1,'(from|into|update|into table) sbtest18b',1), (1100,600,1,'(from|into|update|into table) sbtest19b',1); ...

We are going to have more than 100 rules, but first we match on the first digit after the second and then go on. With this approach ProxySQL has to only check 15 rules on average.

Let’s see the results:

As we can see, even with more rules, chaining is way faster than without chaining.

Tips Hits

ProxySQL keeps statistics about a rule’s hits. When you add a rule you can see how many queries it applied to:

select * from stats_mysql_query_rules; +---------+------+ | rule_id | hits | +---------+------+ | 2 | 6860 | | 3 | 6440 | | 4 | 6880 | | 5 | 6610 | | 6 | 6850 | | 7 | 7130 | | 8 | 6620 | | 9 | 7300 | | 10 | 6750 | | 11 | 7050 | | 12 | 7280 | | 13 | 6780 | | 14 | 6670 | ...

Query_Processor_time_nsec

ProxySQL does not record how much time it spends on a rule (not yet, anyway: https://github.com/sysown/proxysql/issues/966), but it has a global stat:

select * from stats_mysql_global where Variable_name="Query_Processor_time_nsec"; +---------------------------+----------------+ | Variable_Name | Variable_Value | +---------------------------+----------------+ | Query_Processor_time_nsec | 3184114671740 | +---------------------------+----------------+

You can monitor this statistic, and if you see a huge increase after you added a rule, you might want to review it again.

Conclusion

ProxySQL can handle many rules, and of course they have some costs. But if you design your rules based on your workload and your query distribution, you can minimize this cost a lot.

Correct Index Choices for Equality + LIKE Query Optimization

April 11, 2017 - 12:51pm

As part of our support services, we do a lot of query optimization. This is where most performance gains come from. Here’s an example of the work we do.

Some days ago a customer arrived with the following table:

CREATE TABLE `infamous_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL DEFAULT '0', `email` varchar(200) NOT NULL DEFAULT '', `msg_type` varchar(255) NOT NULL DEFAULT '', `t2send` int(11) NOT NULL DEFAULT '0', `flag` char(1) NOT NULL DEFAULT '', `sent` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `f` (`flag`), KEY `email` (`email`), KEY `msg_type` (`msg_type`(5)), KEY `t_msg` (`t2send`,`msg_type`(5)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

And a query that looked like this:

SELECT COUNT(*) FROM `infamous_table` WHERE `t2send` > 1234 AND `msg_type` LIKE 'prefix%';

The table had an index t_msg that wasn’t helping at all: the EXPLAIN for our 1000000 rows test table looked like this:

id: 1 select_type: SIMPLE table: infamous_table type: range possible_keys: t_msg key: t_msg key_len: 4 ref: NULL rows: 107478 Extra: Using where

You can see the index is the on that was expected: “t_msg”. But the key_len is 4. This indicates that the INT part was used, but that the msg_type(5) part was ignored. This resulted examining 100k+ rows. If you have MySQL 5.6, you can see it more clearly with EXPLAIN FORMAT=JSON under used_key_parts:

EXPLAIN: { "query_block": { "select_id": 1, "table": { "table_name": "infamous_table", "access_type": "range", "possible_keys": [ "t_msg" ], "key": "t_msg", "used_key_parts": [ "t2send" ], "key_length": "4", "rows": 107478, "filtered": 100, "index_condition": "(`test`.`infamous_table`.`t2send` > 1234)", "attached_condition": "(`test`.`infamous_table`.`msg_type` like 'prefix%')" } } }

The customer had multi-valued strings like “PREFIX:INT:OTHER-STRING” stored in the columnmsg_type, and that made it impossible to convert it to an enum or similar field type that allowed changing the LIKE for an equity.

So the solution was rather simple: just like for point and range queries over numeric values, you must define the index with the ranged field as the rightmost part. This means the correct index would have looked like msg_type(5),t2send. The EXPLAIN for the new index provided the customer with some happiness:

id: 1 select_type: SIMPLE table: infamous_table type: range possible_keys: t_msg,better_multicolumn_index key: better_multicolumn_index key_len: 11 ref: NULL rows: 4716 Extra: Using where

You can see the key_len is now what we would have expected: four bytes for the INT and another seven bytes for the VARCHAR (five for our chosen prefix + two for prefix length). More importantly, you can notice the rows count decreased by approximately 22 times.

We used pt-online-schema on the customer’s environment to apply ALTER to avoid downtime. This made it an easy and painless solution, and the query effectively executed in under 1/20 of the time! So, all fine and dandy? Well, almost. We did a further test, and the query looked like this:

SELECT COUNT(*) FROM `infamous_table` WHERE `t2send` > 1234 AND `msg_type` LIKE 'abc%';

So where’s the difference? The length of the string used for the LIKE condition is shorter than the prefix length we choose for the VARCHAR part of the index (the customer intended to look-up strings with only three chars, so we needed to check this). This query also scanned 100k rows, and EXPLAIN showed the key_len was 4, meaning the VARCHAR part was being ignored once again.

This means the index prefix needed to be shorter. We ALTERed the table and made the prefix four characters long, counting on the fact that the multi-valued strings were using “:” to separate the values, so we suggested the customer include the colon in the look-up string for the shortest strings. In this case,  'abc%' would be 'abc:%' (which is also four characters long).

As a final optimization, we suggested dropping old indexes that were covered by the new better_multicolumn_index, and that were most likely created by the customer while testing optimization.

Conclusion

Just like in point-and-range queries, the right order for multi-column indexes is putting the ranged part last. Equally important is that the length of the string prefix needs to match the length of the shortest string you intend to look-up. Just remember, you can’t make this prefix too short or you’ll lose specificity and the query will end up scanning rows unnecessarily.

Webinar Wednesday 4/12: Tuning MongoDB Consistency

April 11, 2017 - 9:54am

Please join Percona’s Senior Technical Operations Architect Tim Vaillancourt as he presents Tuning MongoDB Consistency on April 12, 2017 at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Register Now  Welcome to part two of Percona’s tuning series. In our previous webinar, we mentioned some of the best practices for MongoDB tuning. What if you still need better performance after following the tuning advice in the first webinar? Part two takes a closer look at some of the some of the other options to consider when tuning queries.

In this webinar, we will cover:

  • Consistency, atomicity and isolation in MongoDB
  • Replica set rollbacks, and the risks to your data
  • Integrity vs. scalability tradeoffs to consider during development
  • Using read concerns and write concerns to tune your application data consistency
  • When to use Read Preference, and the tradeoffs of doing so
  • Tuning your MongoDB deployment and server configuration for data integrity/consistency
  • Performing cluster-wide consistent backups

By the end of the webinar you will have a better understanding of how to use MongoDB’s features to achieve a required balance of consistency and scalability.

Register for the webinar 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.

ProxySQL Rules: Do I Have Too Many?

April 10, 2017 - 4:52pm

In this blog post we are going to take a closer look at ProxySQL rules. How do they work, and how big is the performance impact of having many rules?

I would like to say thank you to Renè, who was willing to answer all my questions during my tests.

Overview

ProxySQL is heavily based on the query rules. We can set up ProxySQL without rules based only on the host groups, but if we want read/write splitting or sharding (or anything else) we need rules.

ProxySQL knows the SQL protocol and language, so we can easily create rules based on username, schema name and even on the query itself. We can write regular expressions that match the query digest. Let me show you an example:

insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('Testuser',601,1,3,'^SELECT');

This rule matches all the queries starting with “SELECT”, and sends them to host group 601.

After version 1.3.1, the default regex engine was RE2. Starting after version 1.4, the default regex engine will be PCRE.

I would like to highlight three options that can have a bigger impact on your rules than you think: flagIN, flagOUT, apply.

With regards to the manual:

. . .these allow us to create “chains of rules” that get applied one after the other. An input flag value is set to 0, and only rules with flagIN=0 are considered at the beginning. When a matching rule is found for a specific query, flagOUT is evaluated and if NOT NULL the query will be flagged with the specified flag in flagOUT. If flagOUT differs from flagIN, the query will exit the current chain and enters a new chain of rules having flagIN as the new input flag. If flagOUT matches flagIN, the query will be re-evaluated again against the first rule with said flagIN. This happens until there are no more matching rules, or apply is set to 1 (which means this is the last rule to be applied)

You might not be sure what this means, but I will show you later.

As you can see, adding a rule is easy and we can add hundreds of rules, But is there any performance impact?

Test Case

We can write rules based on any part of the query (for example, “userid” or some “sharding key”). In these tests I wrote the rules based on table names because I can easily generate tables with “sysbench”, and run queries against these tables.

I created 1000 tables using sysbench, and I am going to test them with a direct MySQL connection, ProxySQL without rules, with ten rules and with 100 rules.

Time to do some tests to see if adding 100 or more rules have any effect on the performance?

I used two c4.4xlarge instances with SSDs, and I am going to share the steps so anybody can repeat my test and share/compare the results. NodeA is running MySQL 5.7.17 server, and NodeB is running “ProxySQL 1.3.4: and sysbench. During the test I increased the sysbench threads in the following steps:1,2,4,8,12,16,20,24.

I tried to use the simplest ProxySQL configuration as possible:

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('10.10.10.243',600,3306,1000,0); INSERT INTO mysql_replication_hostgroups VALUES (600,'',''); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK; insert into mysql_users (username,password,active,default_hostgroup,default_schema) values ('testuser_rw','Testpass1.',1,600,'test'); LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK; 

Only one server, one host group. I tried to measure the impact the rules had, so in all the test I sent the queries to the same host group. I only changed the rules (and some ProxySQL settings, as I will explain later).

As I mentioned, I am going to filter based on table names. Here are the 100 rules that I used:

insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('testuser_rw',600,1,3,'(from|into|update|into table) sbtest1b'); insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('testuser_rw',600,1,3,'(from|into|update|into table) sbtest2b'); ... insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('testuser_rw',600,1,3,'(from|into|update|into table) sbtest100b');First Test

First I ran tests with a direct MySQL connection, ProxySQL without rules, ProxySQL with ten rules and ProxySQL with 100 rules.

ProxySQL itself has an impact on the performance, but there is a big difference between 10 and 100 rules. So adding more and more rules can have a negative effect on the performance.

That’s all? Can we do anything to speed things up? I used the default ProxySQL settings. Let’s have a look what can we tune.

Increasing the Number of Threads

Let’s go step by step. First we can increase the thread number inside ProxySQL (the default is 4). We will increase it to 8:

UPDATE global_variables SET variable_value='8' WHERE variable_name='mysql-threads'; SAVE MYSQL VARIABLES TO DISK;

ProxySQL has to be restarted after this changes.

With this simple changes, we can improve the performance. As we can see, the difference is getting larger and larger as we increase the number of the sysbench threads.

Compiling

By compiling our own package, we can gain some extra performance. It is not clear why, so we opened a ticket for further investigation:

I removed some of the columns because the graph got to busy.

ProxySQL 1.4

In ProxySQL 1.4 (which is not GA yet), we can change between the regex engines. However, even using the same engine (RE2) is faster in 1.4:

Apply

As I mentioned, ProxySQL has a few important parameters like “apply”. With apply, if the query matches a rule it won’t check the remaining rules. In an ideal world, if you have 100 rules and 100 queries in random order which match only one rule, you only have to check 50 rules on average.

The new rules:

insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest,apply) values('testuser_rw',600,1,3,'(from|into|update|into table) sbtest1b',1);

As you can see it didn’t help at all. But why? Because in this test we have 1000 tables, and we are running queries on all of the tables. This means 90% the queries have to check all the rules anyway. Let’s make a test with 100 tables to see if the “apply” helps or not:

As we can see, with 100 tables we get a much better performance. But of course this is not a valid solution because we can’t just drop tables, “userids” or “sharding keys”. In the next post I will show you how to use “apply” in a more effective way.

Conclusion

So far, ProxySQL 1.4 with the PCRE engine and eight threads gives us the best performance with 100 rules and 1000 tables. As we can see, both the number of the rules and the query distribution matter. Both impact the performance. In my next blog post, I will show you how you can add some logic into your rules so that, even if you have more rules, you will get better performance.

Non-Deterministic Order for SELECT with LIMIT

April 7, 2017 - 12:26pm

In this blog, we’ll look at how queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).

Short story:

Do not rely on the order of your rows if your query does not use ORDER BY. Even with ORDER BY, rows with the same values can be sorted differently. To fix this issue, always add ORDER BY ... ID when you have LIMIT N.

Long story:

While playing with MariaDB ColumnStore and Yandex ClickHouse, I came across a very simple case. In MariaDB ColumnStore and Yandex ClickHouse, the simple query (which I used for testing) select * from <table> where ... limit 10  returns results in a non-deterministic order.

This is totally expected. SELECT * from <table> WHERE ... LIMIT 10 means “give me any ten rows, and as there is no order they can be anything that matches the WHERE condition.” What we used to get in vanilla MySQL + InnoDB, however, is different: SELECT * from <table> WHERE ... LIMIT 10 gives us the rows sorted by primary key. Even with MyISAM in MySQL, if the data doesn’t change, the results are repeatable:

mysql> select * from City where CountryCode = 'USA' limit 10; +------+--------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+--------------+-------------+--------------+------------+ | 3793 | New York | USA | New York | 8008278 | | 3794 | Los Angeles | USA | California | 3694820 | | 3795 | Chicago | USA | Illinois | 2896016 | | 3796 | Houston | USA | Texas | 1953631 | | 3797 | Philadelphia | USA | Pennsylvania | 1517550 | | 3798 | Phoenix | USA | Arizona | 1321045 | | 3799 | San Diego | USA | California | 1223400 | | 3800 | Dallas | USA | Texas | 1188580 | | 3801 | San Antonio | USA | Texas | 1144646 | | 3802 | Detroit | USA | Michigan | 951270 | +------+--------------+-------------+--------------+------------+ 10 rows in set (0.01 sec) mysql> select * from City where CountryCode = 'USA' limit 10; +------+--------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+--------------+-------------+--------------+------------+ | 3793 | New York | USA | New York | 8008278 | | 3794 | Los Angeles | USA | California | 3694820 | | 3795 | Chicago | USA | Illinois | 2896016 | | 3796 | Houston | USA | Texas | 1953631 | | 3797 | Philadelphia | USA | Pennsylvania | 1517550 | | 3798 | Phoenix | USA | Arizona | 1321045 | | 3799 | San Diego | USA | California | 1223400 | | 3800 | Dallas | USA | Texas | 1188580 | | 3801 | San Antonio | USA | Texas | 1144646 | | 3802 | Detroit | USA | Michigan | 951270 | +------+--------------+-------------+--------------+------------+ 10 rows in set (0.00 sec)

The results are ordered by ID here. In most cases, when the data doesn’t change and the query is the same, the order of results will be deterministic: open the file, read ten lines from the beginning, close the file. (When using indexes it can be different if different indexes are selected. For the same query, the database will probably select the same index if the data is static.)

But this is still not guaranteed. Here’s why: imagine we now introduce parallelism, split our table into ten pieces and run ten threads. Each will work on its own piece. Then, unless we specifically wait on each thread to finish and order the results, it will give us a random order of results. Let’s simulate this in a bash script:

for y in {2000..2010} do sql="select YearD, count(*), sum(ArrDelayMinutes) from ontime where yeard=$y and carrier='DL' limit 1" mysql -Nb ontime -e "$sql" & done wait

The script’s purpose is to perform aggregation faster by taking advantage of multiple CPU cores on the server in parallel. It opens ten connections to MySQL and returns results as they arrive:

$ ./parallel_test.sh 2009 428007 5003632 2007 475889 5915443 2008 451931 5839658 2006 506086 6219275 2003 660617 5917398 2004 687638 8384465 2002 728758 7381821 2005 658302 8143431 2010 732973 9169167 2001 835236 8339276 2000 908029 11105058 $ ./parallel_test.sh 2009 428007 5003632 2008 451931 5839658 2007 475889 5915443 2006 506086 6219275 2005 658302 8143431 2003 660617 5917398 2004 687638 8384465 2002 728758 7381821 2010 732973 9169167 2001 835236 8339276 2000 908029 11105058

In this case, the faster queries arrive first and are on top, with the slower on the bottom. If the network was involved (think about different nodes in a cluster connected via a network), then the response time from each node can be much more random due to non-deterministic network latency.

In the case of MariaDB ColumnStore or Yandex Clickhouse, where scans are performed in parallel, the order of the results can also be non-deterministic. An example for ClickHouse:

:) select * from wikistat where project = 'en' limit 1; SELECT * FROM wikistat WHERE project = 'en' LIMIT 1 ┌───────date─┬────────────────time─┬─project─┬─subproject─┬─path─────┬─hits─┬──size─┐ │ 2008-07-11 │ 2008-07-11 14:00:00 │ en │ │ Retainer │ 14 │ 96857 │ └────────────┴─────────────────────┴─────────┴────────────┴──────────┴──────┴───────┘ 1 rows in set. Elapsed: 0.031 sec. Processed 2.03 million rows, 41.40 MB (65.44 million rows/s., 1.33 GB/s.) :) select * from wikistat where project = 'en' limit 1; SELECT * FROM wikistat WHERE project = 'en' LIMIT 1 ┌───────date─┬────────────────time─┬─project─┬─subproject─┬─path─────────┬─hits─┬───size─┐ │ 2008-12-15 │ 2008-12-15 14:00:00 │ en │ │ Graeme_Obree │ 18 │ 354504 │ └────────────┴─────────────────────┴─────────┴────────────┴──────────────┴──────┴────────┘ 1 rows in set. Elapsed: 0.023 sec. Processed 1.90 million rows, 68.19 MB (84.22 million rows/s., 3.02 GB/s.)

An example for ColumnStore:

MariaDB [wikistat]> select * from wikistat limit 1 date: 2008-01-18 time: 2008-01-18 06:00:00 project: en subproject: NULL path: Doctor_Who:_Original_Television_Soundtrack hits: 2 size: 2 1 row in set (1.63 sec) MariaDB [wikistat]> select * from wikistat limit 1 date: 2008-01-31 time: 2008-01-31 10:00:00 project: de subproject: NULL path: Haramaki hits: 1 size: 1 1 row in set (1.58 sec)

In another case (bug#72076) we use ORDER BY, but the rows being sorted are the same. MySQL 5.7 contains the “ORDER BY” + LIMIT optimization:

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

Conclusion
In systems that involve parallel processing, queries like select * from table where ... limit N can return rows in a random order (even if the data doesn’t change between the calls). This is due to the async nature of the parallel calls: whoever serves results faster wins. In MySQL, you run select * from table limit 1 three times and get the same data in the same order (especially if the table data doesn’t change), but the response time will be slightly different. In a massively parallel system, the difference in the response times can cause the rows to be ordered differently.

To fix: always add ORDER BY ... ID  when you have LIMIT N.

Percona Server for MongoDB 3.4.3-1.3 is Now Available

April 7, 2017 - 9:57am

Percona announces the release of Percona Server for MongoDB 3.4.3-1.3 on April 6, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB 3.4.3-1.3

Percona Server for MongoDB 3.4.3-1.3 is an enhanced, open source, fully compatible, highly-scalable, zero-maintenance downtime database supporting the MongoDB v3.4 protocol and drivers. It extends MongoDB with Percona Memory Engine and MongoRocks storage engine, as well as several enterprise-grade features:

Percona Server for MongoDB requires no changes to MongoDB applications or code.

This release candidate is based on MongoDB 3.4.3 and includes the following additional changes:

  • #PSMDB-123: Fixed Hot Backup to create proper subdirectories in the destination directory.
  • #PSMDB-126: Added index and collection names to duplicate key error message.

Percona Server for MongoDB 3.4.3-1.3 release notes are available in the official documentation.

Percona Live Featured Session with Ilya Kosmodemiansky: Linux IO internals for Database Administrators

April 6, 2017 - 1:57pm

IWelcome to another post in the series of Percona Live featured session blogs! In these blogs, we’ll highlight some of the session speakers that will be at this year’s Percona Live conference. We’ll also discuss how these sessions can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured session, we’ll meet Ilya Kosmodemiansky, CEO and Consultant of Data Egret. His session is Linux IO Internals for Database Administrators. Input/output performance problems are an everyday agenda item for DBAs since the beginning of databases. Data volume grows rapidly, and you need to get your data quickly from the disk – and more importantly to the disk. This talk covers how Linus IO works, how database pages travel from the disk level to the database shared memory and back, and what mechanisms exist to help control the exchange.

NOTE: As of this interview, Ilya’s company has rebranded from PostgreSQL Consulting to Data Egret. You can read about this change here.
I had a chance to speak with Ilya about Linux IO Internals for Database Administrators:

Percona: How did you get into database technology? What do you love about it?

Ilya: I am actually a biologist by training, so my first programming experience was in basic bioinformatics rather than general programming. That was a long time ago. Then I started trying myself in pure informatics and found it really exciting. I then worked as an Oracle, DB2 and Postgres DB. Today, my main focus is Postgres.

Dealing with open source technology is very different, and I enjoy the sense of community it brings.

Databases are the bread and butter of any business, and are crucial to its success. I find it exciting to be able to support the different types of businesses we are working with, and really enjoy solving problems and troubleshooting complex issues. This is what makes my day-to-day really enjoyable and keeps me on my toes.

Percona: Your talk is called Linux IO internals for Database Administrators. Why are Linux IO internals important for DBAs?

Ilya: Databases are really a part of a larger ecosystem, they heavily rely on operating system’s internal mechanisms, hardware, etc. To be an expert DBA and have a full control over your database, you should have a deeper understanding of how this system works. This knowledge also helps you to tackle different situations and avoid problems where possible.

After you reach a certain level of database optimization, you need to scrutinize your system on a deeper level. This is the only way you can ensure its optimal function.

Percona: What value does understanding how the IO internals work add to their ability to do their jobs?

Ilya: I would say it’s similar to driving vs. troubleshooting a car. To drive a car, you only need to know how you change gears, adjust the mirrors, add fuel and have a good driving technique. But if your car breaks down you need to understand what happens under the hood, and how its different components work on a deeper level. It not only makes you a better and more confident driver, but it also helps you get the best out of your car.

The same thing is true with databases. If you know how they really work, you will be able to better optimize their performance. As a DBA, you are in a sense an F1 mechanic. You need to know how they work to be able to do a good job, efficiently and fast.

Percona: What do you want attendees to take away from your session? Why should they attend?

Ilya: I would like my audience to really start and see the bigger picture, while at the same time not forgetting the importance of detail. It’s always easy to rely on a checklist, and an average DBA always looks for one. My talk is going to disappointment them: there is no ultimate checklist that finds all the possible failures and fixes. You really need to have a deeper understanding of database internals. Only that knowledge allows you to quickly make the right decision in critical situations. Having this knowledge will also allow you to be the judge of what to optimize and what to improve, so that you can get most out of your system.

Percona: What are you most looking forward to at Percona Live 2017?

Ilya: This is going to be my third conference, and it always attracts fantastic speakers and a great audience. I am looking forward to learning a lot about broader technologies I would normally have no chance to look at, making new friends and hanging out with old ones. Being part of the open source community is like having an extended family, and I find events such as Percona Live contribute to its strength by bringing together different communities.

For the first time, we will have PostgreSQL community booth at Percona Live this year. I think it’s a fantastic opportunity that will allow the two communities to get together and provide a fertile ground for new discussions, collaborations and mutual technology improvements.

For more information on Linux IO internals, or PostgreSQL in general, see Ilya and Data Egret’s various social handles:

Register for Percona Live Data Performance Conference 2017, and see Ilya present his session on Linux IO Internals for Database Administrators. Use the code FeaturedTalk and receive $100 off the current registration price!

Percona Live Data Performance Conference 2017 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community, as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 24-27, 2017 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

Dealing with MySQL Error Code 1215: “Cannot add foreign key constraint”

April 6, 2017 - 11:11am

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen. This blog post is a compendium of the most common reasons why you can get ERROR 1215, how to diagnose your case to find which one is affecting you and potential solutions for adding the foreign key.

(Note: be careful when applying the proposed solutions, as many involve ALTERing the parent table and that can take a long time blocking the table, depending on your table size, MySQL version and the specific ALTER operation being applied; In many cases using pt-online-schema-change will be likely a good idea).

So, onto the list:

1) The table or index the constraint refers to does not exist yet (usual when loading dumps).

How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means tables are being created in wrong order.
How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Example:

mysql> CREATE TABLE child ( -> id INT(10) NOT NULL PRIMARY KEY, -> parent_id INT(10), -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) -> ) ENGINE INNODB; ERROR 1215 (HY000): Cannot add foreign key constraint # We check for the parent table and is not there. mysql> SHOW TABLES LIKE 'par%'; Empty set (0.00 sec) # We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost): mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; Query OK, 0 rows affected (0.00 sec) # And now we re-attempt to create the child table mysql> CREATE TABLE child ( -> id INT(10) NOT NULL PRIMARY KEY,drop table child; -> parent_id INT(10), -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) -> ) ENGINE INNODB; Query OK, 0 rows affected (0.01 sec)

2) The table or index in the constraint references misuses quotes.

How to diagnose: Inspect each FOREIGN KEY declaration and make sure you either have no quotes around object qualifiers, or that you have quotes around the table and a SEPARATE pair of quotes around the column name.
How to fix: Either don’t quote anything, or quote the table and the column separately.
Example:

# wrong; single pair of backticks wraps both table and column ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`; # correct; one pair for each part ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`); # also correct; no backticks anywhere ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id); # also correct; backticks on either object (in case it’s a keyword) ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);

3) The local key, foreign table or column in the constraint references have a typo:

How to diagnose: Run SHOW TABLES and SHOW COLUMNS and compare strings with those in your REFERENCES declaration.
How to fix: Fix the typo once you find it.
Example:

# wrong; Parent table name is ‘parent’ ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id); # correct ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);

4) The column the constraint refers to is not of the same type or width as the foreign column:

How to diagnose: Use SHOW CREATE TABLE parent to check that the local column and the referenced column both have same data type and width.
How to fix: Edit your DDL statement such that the column definition in the child table matches that of the parent table.
Example:

# wrong; id column in parent is INT(10) CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id BIGINT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ) ENGINE INNODB; # correct; id column matches definition of parent table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ) ENGINE INNODB;

5) The foreign object is not a KEY of any kind

How to diagnose: Use SHOW CREATE TABLE parent to check that if the REFERENCES part points to a column, it is not indexed in any way.
How to fix: Make the column a KEY, UNIQUE KEY or PRIMARY KEY on the parent.
Example:

# wrong; column_1 is not indexed in our example table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB; # correct; we first add an index and then re-attempt creation of child table ALTER TABLE parent ADD INDEX column_1_idx(column_1); # and then re-attempt creation of child table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB;

6) The foreign key is a multi-column PK or UK, where the referenced column is not the leftmost one

How to diagnose: Do a SHOW CREATE TABLE parent to check if the REFERENCES part points to a column that is present in some multi-column index(es), but is not the leftmost one in its definition.
How to fix: Add an index on the parent table where the referenced column is the leftmost (or only) column.
Example:

# wrong; column_3 only appears as the second part of an index on parent table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB; # correct; create a new index for the referenced column ALTER TABLE parent ADD INDEX column_3_idx (column_3); # then re-attempt creation of child CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB;

7) Different charsets/collations among the two table/columns

How to diagnose: Run SHOW CREATE TABLE parent and compare that the child column (and table) CHARACTER SET and COLLATE parts match those of the parent table.
How to fix: Modify the child table DDL so that it matches the character set and collation of the parent table/column (or ALTER the parent table to match the child’s wanted definition.
Example:

# wrong; the parent table uses utf8/utf8_bin for charset/collation CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB; # correct; edited DDL so COLLATE matches parent definition CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB;

8) The parent table is not using InnoDB

How to diagnose: Run SHOW CREATE TABLE parent and verify if ENGINE=INNODB or not.
How to fix: ALTER the parent table to change the engine to InnoDB.
Example:

# wrong; the parent table in this example is MyISAM: CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY ) ENGINE MyISAM; # correct: we modify the parent’s engine ALTER TABLE parent ENGINE=INNODB;

9) Using syntax shorthands to reference the foreign key

How to diagnose: Check if the REFERENCES part only mentions the table name. As explained by ex-colleague Bill Karwin in http://stackoverflow.com/questions/41045234/mysql-error-1215-cannot-add-foreign-key-constraint, MySQL doesn’t support this shortcut (even though this is valid SQL).
How to fix: Edit the child table DDL so that it specifies both the table and the column.
Example:

# wrong; only parent table name is specified in REFERENCES CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent ) ENGINE INNODB; # correct; both the table and column are in the REFERENCES definition CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent(column_2) ) ENGINE INNODB;

10) The parent table is partitioned

How to diagnose: Run SHOW CREATE TABLE parent and find out if it’s partitioned or not.
How to fix: Removing the partitioning (i.e., merging all partitions back into a single table) is the only way to get it working.
Example:

# wrong: the parent table we see below is using PARTITIONs CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY ) ENGINE INNODB PARTITION BY HASH(id) PARTITIONS 6; #correct: ALTER parent table to remove partitioning ALTER TABLE parent REMOVE PARTITIONING;

11) Referenced column is a generated virtual column (this is only possible with 5.7 and newer)

How to diagnose: Run SHOW CREATE TABLE parent and verify that the referenced column is not a virtual column.
How to fix: CREATE or ALTER the parent table so that the column will be stored and not generated.
Example:

# wrong; this parent table has a generated virtual column CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY, column_1 INT(10) NOT NULL, column_2 INT(10) NOT NULL, column_virt INT(10) AS (column_1 + column_2) NOT NULL, KEY column_virt_idx (column_virt) ) ENGINE INNODB; # correct: make the column STORED so it can be used as a foreign key ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL; # And now the child table can be created pointing to column_virt CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_virt INT(10) NOT NULL, FOREIGN KEY (parent_virt) REFERENCES parent(column_virt) ) ENGINE INNODB;

12) Using SET DEFAULT for a constraint action

How to diagnose: Check your child table DDL and see if any of your constraint actions (ON DELETE, ON UPDATE) try to use SET DEFAULT
How to fix: Remove or modify actions that use SET DEFAULT from the child table CREATE or ALTER statement.
Example:

# wrong; the constraint action uses SET DEFAULT CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT ) ENGINE INNODB; # correct; there's no alternative to SET DEFAULT, removing or picking other is the corrective measure CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE INNODB;

I realize many of the solutions are not what you might desire, but these are limitations in MySQL that must be overcome on the application side for the time being. I do hope the list above gets shorter by the time 8.0 is released!

If you know other ways MySQL can fail with ERROR 1215, let us know in the comments!

More information regarding Foreign Key restrictions can be found here: https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html.

Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.