Buy Percona ServicesBuy Now!
Subscribe to Latest MySQL Performance Blog posts feed
Updated: 1 hour 50 min ago

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.

Evaluation of PMP Profiling Tools

April 5, 2017 - 12:12pm

In this blog post, we’ll look at some of the available PMP profiling tools.

While debugging or analyzing issues with Percona Server for MySQL, we often need a quick understanding of what’s happening on the server. Percona experts frequently use the pt-pmp tool from Percona Toolkit (inspired by http://poormansprofiler.org).

The pt-pmp tool collects application stack traces GDB and then post-processes them. From this you get a condensed, ordered list of the stack traces. The list helps you understand where the application spent most of the time: either running something or waiting for something.

Getting a profile with pt-pmp is handy, but it has a cost: it’s quite intrusive. In order to get stack traces, GDB has to attach to each thread of your application, which results in interruptions. Under high loads, these stops can be quite significant (up to 15-30-60 secs). This means that the pt-pmp approach is not really usable in production.

Below I’ll describe how to reduce GDB overhead, and also what other tools can be used instead of GDB to get stack traces.

  • GDB
    By default, the symbol resolution process in GDB is very slow. As a result, getting stack traces with GDB is quite intrusive (especially under high loads).There are two options available that can help notably reduce GDB tracing overhead:

      1. Use readnever patch. RHEL and other distros based on it include GDB with the readnever patch applied. This patch allows you to avoid unnecessary symbol resolving with the --readnever option. As a result you get  up to 10 times better speed.
      2. Use gdb_index. This feature was added to address symbol resolving issue by creating and embedding a special index into the binaries. This index is quite compact: I’ve created and embedded gdb_index for Percona server binary (it increases the size around 7-8MB). The addition of the gdb_index speeds up obtaining stack traces/resolving symbols two to three times.

    # to check if index already exists: readelf -S | grep gdb_index # to generate index: gdb -batch mysqld -ex "save gdb-index /tmp" -ex "quit" # to embed index: objcopy --add-section .gdb_index=tmp/mysqld.gdb-index --set-section-flags .gdb_index=readonly mysqld mysqld

  • eu-stack (elfutils)
    The eu-stack from the elfutils package prints the stack for each thread in a process or core file.Symbol resolving also is not very optimized in eu-stack. By default, if you run it under load it will take even more time than GDB. But eu-stack allows you to skip resolving completely, so it can get stack frames quickly and then resolve them without any impact on the workload later.
  • Quickstack
    Quickstack is a tool from Facebook that gets stack traces with minimal overheads.

Now let’s compare all the above profilers. We will measure the amount of time it needs to take all the stack traces from Percona Server for MySQL under a high load (sysbench OLTP_RW with 512 threads).

The results show that eu-stack (without resolving) got all stack traces in less than a second, and that Quickstack and GDB (with the readnever patch) got very close results. For other profilers, the time was around two to five times higher. This is quite unacceptable for profiling (especially in production).

There is one more note regarding the pt-pmp tool. The current version only supports GDB as the profiler. However, there is a development version of this tool that supports GDB, Quickstack, eu-stack and eu-stack with offline symbol resolving. It also allows you to look at stack traces for specific threads (tids). So for instance, in the case of Percona Server for MySQL, we can analyze just the purge, cleaner or IO threads.

Below are the command lines used in testing:

# gdb & gdb+gdb_index time gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p `pidof mysqld` > /dev/null # gdb+readnever time gdb --readnever -ex "set pagination 0" -ex "thread apply all bt" -batch -p `pidof mysqld` > /dev/null # eu-stack time eu-stack -s -m -p `pidof mysqld` > /dev/null # eu-stack without resolving time eu-stack -q -p `pidof mysqld` > /dev/null # quickstack - 1 sample time quickstack -c 1 -p `pidof mysqld` > /dev/null # quickstack - 1000 samples time quickstack -c 1000 -p `pidof mysqld` > /dev/null

Percona Server for MySQL 5.7.17-13 is Now Available

April 5, 2017 - 8:53am

Percona announces the GA release of Percona Server for MySQL 5.7.17-13 on April 5, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

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

Bugs Fixed:
  • MyRocks storage engine detection implemented in mysqldump in Percona Server 5.6.17-12 was using a deprecated INFORMATION_SCHEMA.SESSION_VARIABLES table, causing mysqldump failures on servers running with the show_compatibility_56 variable set to OFF. Bug fixed #1676401.

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

Percona Live Webinar Thursday, April 6, 2017: Best Practices Migrating to Open Source Databases

April 4, 2017 - 9:45am

Please join Percona’s CEO and Founder, Peter Zaitsev on April 6th, 2017 at 8:00 am PDT / 11:00 am EDT (UTC-7) as he presents Best Practices Migrating to Open Source Databases.

Register Now

This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.

In this webinar, we will look at how to address such concerns to help get a migration commitment. We’ll cover picking the right project, selecting the right team to manage migration, and developing the right migration path to maximize migration success (from a technical and organizational standpoint).

Register for the webinar here.

Peter Zaitsev, Co-Founder and CEO, Percona

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. Percona was named to the Inc. 5000 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. Fortune and DZone tapped him as a contributor, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of percona.com’s most popular downloads.

Percona Live Featured Session: Using SelectStar to Monitor and Tune Your Databases

April 4, 2017 - 9:18am

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 the folks at SelectStar, a database monitoring and management tool company. SelectStar will be a sponsor at Percona Live this year.

I recently came across the SelectStar database monitoring product. There are a number of monitoring products on the market (with the evolution of various SaaS and on-premises solutions), but SelectStar piqued my interest for a few reasons. I had a chance to speak with Cameron Jones, Principal Product Manager at SelectStar about their tool:

Percona: What are the challenges that lead to developing SelectStar?

Cameron: One of the challenges that we’ve found in the database monitoring and management sector comes from the dilution of the database market – and not in a bad way. Traditional, closed source database solutions continue to be used across the board (especially by large enterprises), but open source options like MySQL, MongoDB, PostgreSQL and Elasticsearch continue to gain traction as organizations seek solutions that meet their demand for agility and flexibility.

From a database monitoring perspective, this adds some challenges. Traditional solutions are focused on monitoring RDBMS and are really great at it, while newer solutions may only focus on one piece of the puzzle (NoSQL or cloud only, for example).

Percona: How does SelectStar compare to other monitoring and management tools?

Cameron: SelectStar covers a wide array of open and closed source database solutions and is easy to setup. This makes it ideal for enterprises that have a lot going on. Here is the matrix of supported products from our website:

Database Types Key Metrics Monitored by SelectStar Big Data

  • Hadoop
  • Cassandra
  • Ops Counters – Inserts, Queries, etc.
  • Network Traffic
  • Asserts
  • Locks
  • Memory Usage
Cloud

  • Amazon Aurora
  • Amazon Dynamo
  • Amazon RDS
  • Microsoft Azure
  • Queries
  • Memory Usage
  • Network
  • CPU Balance
  • IOPS
NoSQL

  • MongoDB
  • Ops Counters – Inserts, Queries, etc.
  • Network Traffic
  • Asserts
  • Locks
  • Memory Usage
Open Source

  • PostgreSQL
  • MongoDB
  • MySQL
  • MariaDB
  • Average Query Execution Time
  • Query Executions
  • Memory Usage
  • Wait Time
Traditional RDBMS

  • IBM DB2
  • MS SQL Server
  • Oracle
  • Average Query Execution Time
  • Query Executions
  • Memory Usage
  • Wait Time


In addition to monitoring key metrics for different database types, one of the key differences with SelectStar came from its comprehensive alerts and recommendations system.

The alerts and recommendations are designed to ensure you have an immediate understanding of key issues – and where they are coming from. MonYOG is great at this for MySQL, but lacks on other aspects. With SelectStar, you can pinpoint the exact database instance that may be causing the issue; or go further up the chain and see if it’s an issue impacting several database instances at the host level.

Recommendations are often tied to alerts – if you have a red alert, there’s going to be a recommendation tied to it on how you can improve. However, the recommendations pop up even if your database is completely healthy – ensuring that you have visibility into how you can improve your configuration before you actually have an issue impacting performance.

With insight into key metrics, alerts and recommendations, you can fine tune your database performance. In addition, it gives you the opportunity to become more proactive with your database monitoring.

Percona: Is configuring SelectStar difficult?

Cameron: SelectStar is easy to set up – in fact, most customers are up and running in 20 minutes.

Simply head over to the website – selectstar.io – and log in. From there, you’ll be greeted by a welcome screen where you can easily click through and configure a database.

To configure a database, you select your type:

And from there, set up your collector by inputting some key information.

And that’s it! As soon as it’s configured, the collector will start gathering information and data is populated within 20 minutes.

Percona: How does SelectStar work?

Cameron: Using agentless collectors, SelectStar gathers data from both your on-premises and AWS platforms so that you can have insight into all of your database instances.

The collector is basically an independent machine within your infrastructure that pulls data from your database. It is low impact so that it doesn’t impact performance. This is a different approach from all of the other monitoring tools.

Router Metrics (Shown Above)

Mongo relationship tree displaying router, databases, replica set, shards and nodes. (Shown Above)

Percona: Any final thoughts? What are you looking forward to at Percona Live?

Cameron: If you’re in the market for a new database monitoring solution, SelectStar is worth looking at because it covers a breadth of databases with the depth into key metrics, alerts and notifications that optimize performance across your databases. We have a free trial, so you have an easy option to try it. We’re looking forward to meeting with as much of the community as possible, getting feedback and hearing about people’s monitoring needs.

Register for Percona Live Data Performance Conference 2017, and meet the creators of SelectStar. You can find them at selectstar.io. 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.

New MariaDB Dashboard in Percona Monitoring and Management Metrics Monitor

April 3, 2017 - 2:56pm

In honor of the upcoming MariaDB M17 conference in New York City on April 11-12, we have enhanced Percona Monitoring and Management (PMM) Metrics Monitor with a new MariaDB Dashboard and multiple new graphs!

The Percona Monitoring and Management MariaDB Dashboard builds on the efforts of the MariaDB development team to instrument the Aria Storage Engine Status Variables related to Aria Pagecache and Aria Transaction Log activity, the tracking of Index Condition Pushdown (ICP), InnoDB Online DDL when using ALTER TABLE ... ALGORITHM=INPLACE, InnoDB Deadlocks Detected, and finally InnoDB Defragmentation. This new dashboard is available in Percona Monitoring and Management release 1.1.2. Download it now using our docker, VirtualBox or Amazon AMI installation options!

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MariaDB® and MongoDB® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL, MariaDB® and MongoDB servers to ensure that your data works as efficiently as possible.

Aria Pagecache Reads/Writes

MariaDB 5.1 introduced the Aria Storage Engine, which is MariaDB’s MyISAM replacement. Originally known as the Maria storage engine, they renamed it in late 2010 in order to avoid confusion with the overall MariaDB project name. The Aria Pagecache Status Variables graph plots the count of disk block reads and writes, which occur when the data isn’t already in the Aria Pagecache. We also plot the reads and writes from the Aria Page Cache, which count the reads/writes that did not incur a disk lookup (as the data was previously fetched and available from the Aria pagecache):

Aria Pagecache Blocks

Aria reads and writes to the pagecache in order to cache data in RAM and avoid or delay activity related to disk. Overall, this translates into faster database query response times:

  • Aria_pagecache_blocks_not_flushed: The number of dirty blocks in the Aria pagecache.
  • Aria_pagecache_blocks_unused: Free blocks in the Aria pagecache.
  • Aria_pagecache_blocks_used: Blocks used in the Aria pagecache.

Aria Pagecache Total Blocks is calculated using Aria System Variables and the following formula:aria_pagecache_buffer_size / aria_block_size:

Aria Transaction Log Syncs

As Aria strives to be a fully ACID- and MVCC-compliant storage engine, an important factor is support for transactions. A transaction is the unit of work in a database that defines how to implement the four properties of Atomicity, Consistency, Isolation, and Durability (ACID). This graph tracks the rate at which Aria fsyncs the Aria Transaction Log to disk. You can think of this as the “write penalty” for running a transactional storage engine:

InnoDB Online DDL

MySQL 5.6 released the concept of an in-place DDL operation via ALTER TABLE ... ALGORITHM=INPLACE, which in some cases avoided performing a table copy and thus didn’t block INSERT/UPDATE/DELETE. MariaDB implemented three measures to track ongoing InnoDB Online DDL operations, which we plot via the following three status variables:

  • Innodb_onlineddl_pct_progress: Shows the progress of the in-place alter table. It might not be accurate, as in-place alter is highly dependent on the disk and buffer pool status
  • Innodb_onlineddl_rowlog_pct_used: Shows row log buffer usage in 5-digit integers (10000 means 100.00%)
  • Innodb_onlineddl_rowlog_rows: Number of rows stored in the row log buffer

For more information, please see the MariaDB blog post Monitoring progress and temporal memory usage of Online DDL in InnoDB.

InnoDB Defragmentation

MariaDB merged the Facebook/Kakao defragmentation patch for defragmenting InnoDB tablespaces into their 10.1 release. Your MariaDB instance needs to have started with innodb_defragment=1 and your tables need to be in innodb_file_per_table=1 for this to work. We plot the following three status variables:

  • Innodb_defragment_compression_failures: Number of defragment re-compression failures
  • Innodb_defragment_failures: Number of defragment failures
  • Innodb_defragment_count: Number of defragment operations

Index Condition Pushdown

Oracle introduced this in MySQL 5.6. From the manual:

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

Essentially, the closer that ICP Attempts are to ICP Matches, the better!

InnoDB Deadlocks Detected (MariaDB 10.1 Only)

Ever since MySQL implemented a transactional storage engine there have been deadlocks. Deadlocks are conditions where different transactions are unable to proceed because each holds a lock that the other needs. In MariaDB 10.1, there is a Status variable that counts the occurrences of deadlocks since the server startup. Previously, you had to instrument your application to get an accurate count of deadlocks, because otherwise you could miss occurrences if your polling interval wasn’t configured frequent enough (even using pt-deadlock-logger). Unfortunately, this Status variable doesn’t appear to be present in the MariaDB 10.2.4 build I tested:

Again, please download Percona Monitoring and Management 1.1.2 to take advantage of the new MariaDB Dashboard and new graphs!  For installation instructions, see the Deployment Guide.

You can see the MariaDB Dashboard and new graphs in action at the PMM Demo site. If you feel the graphs need any tweaking or if I’ve missed anything, leave a note on the blog. You can also write me directly (I look forward to your comments): michael.coburn@percona.com.

To start: on the ICP graph, should we have a line that defines the percentage of successful ICP matches vs. attempts?

Percona Monitoring and Management 1.1.2 is Now Available

April 3, 2017 - 10:12am

Percona announces the release of Percona Monitoring and Management 1.1.2 on April 3, 2017.

For installation instructions, see the Deployment Guide.

This release includes several new dashboards in Metrics Monitor, updated versions of software components used in PMM Server, and a number of small bug fixes.

Thank You to the Community!

We would like to mention some of the key contributors in this release, and thank the community for continued support of PMM:

New Dashboards and Graphs

This release includes the following new dashboards:

  • MariaDB dashboard includes three new graphs for the Aria storage engine. There will be a detailed blog post about monitoring possibilities with these new graphs:

The new MariaDB dashboard also includes three new graphs for monitoring InnoDB within MariaDB. We are planning to move them into one of the existing InnoDB dashboards in the next PMM release:

  • The InnoDB Defragmentation graph shows how OPTIMIZE TABLE impacts defragmentation on tables when running MariaDB with innodb_file_per_table=1 and innodb_defragment=1.

  • The InnoDB Online DDL graph includes metrics related to online DDL operations when using ALTER TABLE ... ALGORITHM=INPLACE in MariaDB.

  • The InnoDB Deadlocks Detected graph currently works only with MariaDB 10.1. We are planning to add support for MariaDB 10.2, Percona Server, and MySQL in the next PMM release.

  • The Index Condition Pushdown graph shows how InnoDB leverages the Index Condition Pushdown (ICP) routines. Currently this graph works only with MariaDB, but we are planning to add support for Percona Server and MySQL in the next PMM release.

Updated Software

PMM is based on several third-party open-source software components. We ensure that PMM includes the latest versions of these components in every release, making it the most secure, stable and feature-rich database monitoring platform possible. Here are some highlights of changes in the latest releases:

  • Grafana 4.2 (from 4.1.1)
    • HipChat integration
    • Templating improvements
    • Alerting enhancements
  • Consul 0.7.5 (from 0.7.3)
    • Bug fix for serious server panic
  • Prometheus 1.5.2 (from 1.5.1)
    • Prometheus binaries are built with Go1.7.5
    • Fixed two panic conditions and one series corruption bug
  • Orchestrator 2.0.3 (from 2.0.1)
    • GTID improvements
    • Logging enhancements
    • Improved timing resolution and faster discoveries
Other Changes in PMM Server
  • Migrated the PMM Server docker container to use CentOS 7 as the base operating system.
  • Changed the entry point so that supervisor is PID 1.
  • PMM-633: Set the following default values in my.cnf:
    [mysqld] # Default MySQL Settings innodb_buffer_pool_size=128M innodb_log_file_size=5M innodb_flush_log_at_trx_commit=1 innodb_file_per_table=1 innodb_flush_method=O_DIRECT # Disable Query Cache by default query_cache_size=0 query_cache_type=0
  • PMM-676: Added descriptions for graphs in Disk Performance and Galera dashboards.
Changes in PMM Client
  • Fixed pmm-admin remove --all to clear all saved credentials.
  • Several fixes to mongodb_exporter including PMM-629 and PMM-642.
  • PMM-504: Added ability to change the name of a client with running services: $ sudo pmm-admin config --client-name new_name --force

    WARNING: Some Metrics Monitor data may be lost when renaming a running client.

About Percona Monitoring and Management

Percona Monitoring and Management is an open-source platform for managing and monitoring MySQL and MongoDB performance. Percona developed it in collaboration with experts in the field of managed database services, support and consulting.

PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

A live demo of PMM is available at pmmdemo.percona.com.

Please provide your feedback and questions on the PMM forum.

If you would like to report a bug or submit a feature request, use the PMM project in JIRA.

Visit Percona Store


General Inquiries

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