Personal blog of Yzmir Ramirez

In this blog post, we’ll look at using hints to analyze queries.

There are a lot of things that you can do wrong when writing a query, which means that there a lot of things that you can do to make it better. From my personal experience there are two things you should review first:

1. The table join order
2. Which index is being used

Why only those two? Because many other alternatives that are more expensive, and at the end query optimization is a cost-effectiveness analysis. This is why we must start with the simplest fixes. We can control this with the hints “straight_join” and “force index”. These allow us to execute the query with the plan that we would like to test.

Join Order

In a query where we use multiple tables or subqueries, we have some particular fields that we are going to use to join the tables. Those fields could be the Primary Key of the table, the first part of a secondary index, neither or both. But before we analyze possible scenarios, table structure or indexes, we need to establish what is the best order for that query to join the tables.

When we talked about join order and the several tables to join, one possible scenario is that a table is using a primary key to join a table, and another field to join to other tables. For instance:

select
  table_a.id, table_b.value1, table_c.value1
from
  table_a join
  table_b on table_a.id = table_b.id join
  table_c on table_b.id_c = table_c.id
where
  table_a.value1=10;

We get this explain:

+----+-------------+---------+--------+----------------+---------+---------+------------------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys  | key     | key_len | ref                                | rows | Extra       |
+----+-------------+---------+--------+----------------+---------+---------+------------------------------------+------+-------------+
|  1 | SIMPLE      | table_a | ref    | PRIMARY,value1 | value1  | 5       | const                              |    1 | Using index |
|  1 | SIMPLE      | table_b | eq_ref | PRIMARY        | PRIMARY | 4       | bp_query_optimization.table_a.id   |    1 | Using where |
|  1 | SIMPLE      | table_c | eq_ref | PRIMARY        | PRIMARY | 4       | bp_query_optimization.table_b.id_c |    1 | NULL        |
+----+-------------+---------+--------+----------------+---------+---------+------------------------------------+------+-------------+

It is filtering by value1 on table_a, which joins with table_b with the primary key, and table_c uses the value of id_c which it gets from table_b.

But we can change the table order and use straight_join:

select straight_join
  table_a.id, table_b.value1, table_c.value1
from
  table_c join
  table_b on table_b.id_c = table_c.id join
  table_a on table_a.id = table_b.id
where
  table_a.value1=10;

The query is semantically the same, but now we get this explain:

+----+-------------+---------+--------+----------------+---------+---------+----------------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys  | key     | key_len | ref                              | rows | Extra       |
+----+-------------+---------+--------+----------------+---------+---------+----------------------------------+------+-------------+
|  1 | SIMPLE      | table_c | ALL    | PRIMARY        | NULL    | NULL    | NULL                             |    1 | NULL        |
|  1 | SIMPLE      | table_b | ref    | PRIMARY,id_c   | id_c    | 5       | bp_query_optimization.table_c.id |    1 | NULL        |
|  1 | SIMPLE      | table_a | eq_ref | PRIMARY,value1 | PRIMARY | 4       | bp_query_optimization.table_b.id |    1 | Using where |
+----+-------------+---------+--------+----------------+---------+---------+----------------------------------+------+-------------+

In this case, we are performing a full table scan over table_c, which then joins with table_b using index over id_c to finally join table_a using the primary key.

Sometimes the optimizer chooses the incorrect join order because of bad statistics. I found myself reviewing the first query with the second explain plan, where the only thing that I did to find the query problem was to add “STRAIGHT_JOIN” to the query.

Taking into account that the optimizer could fail on this task, we found a practical way to force it to do what we want (change the join order).

It is also useful to find out when an index is missing. For example:

SELECT costs.id as cost_id, spac_types.id as spac_type_id
FROM
spac_types INNER JOIN
costs_spac_types ON costs_spac_types.spac_type_id = spac_types.id INNER JOIN
costs ON costs.id = costs_spac_types.cost_id
WHERE spac_types.place_id = 131;

The explain plan shows:

+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+-----------------------------------+-------+-------------+
| id | select_type | table            | type  | possible_keys                                       | key                                                | key_len | ref                               | rows  | Extra       |
+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+-----------------------------------+-------+-------------+
|  1 | SIMPLE      | costs_spac_types | index  | index_costs_spac_types_on_cost_id_and_spac_type_id | index_costs_spac_types_on_cost_id_and_spac_type_id | 8       | NULL                              | 86408 | Using index |
|  1 | SIMPLE      | spac_types       | eq_ref | PRIMARY,index_spac_types_on_place_id_and_spac_type | PRIMARY                                            | 4       | pms.costs_spac_types.spac_type_id |     1 | Using where |
|  1 | SIMPLE      | costs            | eq_ref | PRIMARY                                            | PRIMARY                                            | 4       | pms.costs_spac_types.cost_id      |     1 | Using index |
+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+-----------------------------------+-------+-------------+

It is starting with costs_spac_types and then using the clustered index for the next two tables. The explain doesn’t look bad!

However, it was taking longer than this:

SELECT STRAIGHT_JOIN costs.id as cost_id, spac_types.id as spac_type_id
FROM
spac_types INNER JOIN
costs_spac_types ON costs_spac_types.spac_type_id = spac_types.id INNER JOIN
costs ON costs.id = costs_spac_types.cost_id
WHERE spac_types.place_id = 131;

0.17 sec versus 0.09 sec. This is the explain plan:

+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+------------------------------+-------+-----------------------------------------------------------------+
| id | select_type | table            | type   | possible_keys                                      | key                                                | key_len | ref                          | rows  | Extra                                                           |
+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+------------------------------+-------+-----------------------------------------------------------------+
|  1 | SIMPLE      | spac_types       | ref    | PRIMARY,index_spac_types_on_place_id_and_spac_type | index_spac_types_on_place_id_and_spac_type         | 4      | const                         |    13 | Using index                                                     |
|  1 | SIMPLE      | costs_spac_types | index  | index_costs_spac_types_on_cost_id_and_spac_type_id | index_costs_spac_types_on_cost_id_and_spac_type_id | 8      | NULL                          | 86408 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | costs            | eq_ref | PRIMARY                                            | PRIMARY                                            | 4      | pms.costs_spac_types.cost_id  |     1 | Using index                                                     |
+----+-------------+------------------+--------+----------------------------------------------------+----------------------------------------------------+---------+------------------------------+-------+-----------------------------------------------------------------+

Reviewing the table structure:

CREATE TABLE costs_spac_types (
  id int(11) NOT NULL AUTO_INCREMENT,
  cost_id int(11) NOT NULL,
  spac_type_id int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY index_costs_spac_types_on_cost_id_and_spac_type_id (cost_id,spac_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=172742 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I saw that the unique index was over cost_id and then spac_type_id. After adding this index:

ALTER TABLE costs_spac_types ADD UNIQUE KEY (spac_type_id,cost_id);

Now, the explain plan without STRIGHT_JOIN is:

+----+-------------+------------------+--------+-----------------------------------------------------------------+--------------------------------------------+---------+------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                                                   | key                                        | key_len | ref                          | rows | Extra       |
+----+-------------+------------------+--------+-----------------------------------------------------------------+--------------------------------------------+---------+------------------------------+------+-------------+
|  1 | SIMPLE      | spac_types       | ref    | PRIMARY,index_spac_types_on_place_id_and_spac_type              | index_spac_types_on_place_id_and_spac_type | 4      | const                         |   13 | Using index |
|  1 | SIMPLE      | costs_spac_types | ref    | index_costs_spac_types_on_cost_id_and_spac_type_id,spac_type_id | spac_type_id                               | 4      | pms.spac_types.id             |   38 | Using index |
|  1 | SIMPLE      | costs            | eq_ref | PRIMARY                                                         | PRIMARY                                    | 4      | pms.costs_spac_types.cost_id  |    1 | Using index |
+----+-------------+------------------+--------+-----------------------------------------------------------------+--------------------------------------------+---------+------------------------------+------+-------------+

Which is much better, as it is scanning fewer rows and the query time is just 0.01 seconds.

Indexes

The optimizer has the choice of using a clustered index, a secondary index, a partial secondary index or no index at all, which means that it uses the clustered index.

Sometimes the optimizer ignores the use of an index because it thinks reading the rows directly is faster than an index lookup:

mysql> explain select * from table_c where id=1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | table_c | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
mysql> explain select * from table_c where value1=1;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table_c | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

In both cases, we are reading directly from the clustered index.

Then, we have secondary indexes that are partially used or/and that are partially useful for the query. This means that we are going to scan the index and then we are going to lookup in the clustered index. YES! TWO STRUCTURES WILL BE USED! We usually don’t realize any of this, but this is like an extra join between the secondary index and the clustered index.

Finally, the covering index, which is simple to identify as “Using index” in the extra column:

mysql> explain select value1 from table_a where value1=1;
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | table_a | ref  | value1        | value1 | 5       | const |    1 | Using index |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------------+

Index Analysis

As I told you before, this is a cost-effectiveness analysis from the point of view of query performance. Most of the time it is faster to use covering indexes than secondary indexes, and finally the clustered index. However, usually covering indexes are more expensive for writes, as you need more fields to cover the query needs. So we are going to use a secondary index that also uses the clustered index. If the amount of rows is not large and it is selecting most of the rows, however, it could be even faster to perform a full table scan. Another thing to take into account is that the amount of indexes affects the write rate.

Let’s do an analysis. This is a common query:

mysql> explain select * from table_index_analisis_1 t1, table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                             | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL                            |   64 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | bp_query_optimization.t2.value1 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+

It is using all the fields of each table.

This is more restrictive:

mysql> explain select t1.id, t1.value1, t1.value2, t2.value2 from table_index_analisis_1 t1, table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                             | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL                            |   64 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | bp_query_optimization.t2.value1 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+

But it is performing a full table scan over t2, and then is using t2.value1 to lookup on t1 using the clustered index.

Let’s add an index on table_index_analisis_2 over value1:

mysql> alter table table_index_analisis_2 add key (value1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

The explain shows that it is not being used, not even when we force it:

mysql> explain select * from table_index_analisis_1 t1, table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                             | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | value1        | NULL    | NULL    | NULL                            |   64 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | bp_query_optimization.t2.value1 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
mysql> explain select * from table_index_analisis_1 t1, table_index_analisis_2 t2 force key (value1) where t1.id = t2.value1;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                             | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | value1        | NULL    | NULL    | NULL                            |   64 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | bp_query_optimization.t2.value1 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------+------+-------------+

This is because the optimizer considers performing a full table scan better than using a part of the index.

Now we are going to add an index over value1 and value2:

mysql> alter table table_index_analisis_2 add key (value1,value2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select t1.id, t1.value1, t1.value2, t2.value2 from table_index_analisis_1 t1, table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys   | key      | key_len | ref                             | rows | Extra                    |
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+
|  1 | SIMPLE      | t2    | index  | value1,value1_2 | value1_2 | 10      | NULL                            |   64 | Using where; Using index |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY         | PRIMARY  | 4       | bp_query_optimization.t2.value1 |    1 | NULL                     |
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+

We can see that now it is using the index, and in the extra column says “Using index” — which means that it is not using the clustered index.

Finally, we are going to add an index over table_index_analisis_1, in the best way that it is going to be used for this query:

mysql> alter table table_index_analisis_1 add key (id,value1,value2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select t1.id, t1.value1, t1.value2, t2.value2 from table_index_analisis_1 t1, table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys   | key      | key_len | ref                             | rows | Extra                    |
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+
|  1 | SIMPLE      | t2    | index  | value1,value1_2 | value1_2 | 10      | NULL                            |   64 | Using where; Using index |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,id      | PRIMARY  | 4       | bp_query_optimization.t2.value1 |    1 | NULL                     |
+----+-------------+-------+--------+-----------------+----------+---------+---------------------------------+------+--------------------------+
2 rows in set (0.00 sec)

However, it is not selected by the optimizer. That is why we need to force it:

mysql> explain select t1.id, t1.value1, t1.value2, t2.value2 from table_index_analisis_1 t1 force index(id), table_index_analisis_2 t2 where t1.id = t2.value1;
+----+-------------+-------+-------+-----------------+----------+---------+---------------------------------+------+--------------------------+
| id | select_type | table | type  | possible_keys   | key      | key_len | ref                             | rows | Extra                    |
+----+-------------+-------+-------+-----------------+----------+---------+---------------------------------+------+--------------------------+
|  1 | SIMPLE      | t2    | index | value1,value1_2 | value1_2 | 10      | NULL                            |   64 | Using where; Using index |
|  1 | SIMPLE      | t1    | ref   | id              | id       | 4       | bp_query_optimization.t2.value1 |    1 | Using index              |
+----+-------------+-------+-------+-----------------+----------+---------+---------------------------------+------+--------------------------+
2 rows in set (0.00 sec)

Now, we are just using the secondary index in both cases.

Conclusions

There are many more hints to analyze queries we could review, like handler used, table design, etc. However, in my opinion, it is useful to focus on these at the beginning of the analysis.

I will also like to point out that using hints is not a long-term solution! Hints should be used just in the analysis phase.

The post Using Hints to Analyze Queries appeared first on Percona Database Performance Blog.

Please join Percona’s Architect, Tibi Köröcz as he presents Percona XtraDB Cluster 5.7 Tutorial on Wednesday, April 18, 2018, at 7:00 am PDT (UTC-7) / 10:00 am EDT (UTC-4).

Never used Percona XtraDB Cluster before? Come join this 45-minute tutorial where we will introduce you to the concepts of a fully functional Percona XtraDB Cluster.

In this tutorial, we will show you how you can install Percona XtraDB Cluster with ProxySQL, and monitor it with Percona Monitoring and Management (PMM).

We will also cover topics like bootstrap, IST, SST, Certification, common-failure situations and online schema changes.

Register for the webinar now.

Tibor Köröcz, Senior Consultant

Tibi joined Percona in 2015 as a Consultant. Before joining Percona, among many other things, he worked at the world’s largest car hire booking service as a Senior Database Engineer. He enjoys trying and working with the latest technologies and applications that can help or work with MySQL. In his spare time, he likes to spend time with his friends, travel around the world and play ultimate frisbee.

The post Webinar Wednesday, April 18, 2018: Percona XtraDB Cluster 5.7 Tutorial appeared first on Percona Database Performance Blog.

Due to continuous development and improvement, Percona Server for MySQL incorporates a number of improvements related to binary log handling and replication. This results in replication specifics, distinguishing it from MySQL Server.

Temporary tables and mixed logging format

Summary of the fix:

As soon as some statement involving temporary tables was met when using a mixed binlog format, MySQL switched to row-based logging for all statements until the end of the session (or until all temporary tables used in the session were dropped). This is inconvenient when you have long-lasting connections, including replication-related ones. Percona Server for MySQL fixes the situation by switching between statement-based and row-based logging when necessary.

Details:

The new mixed binary logging format, supported by Percona Server for MySQL, means that the server runs in statement-based logging by default, but switches to row-based logging when replication would be unpredictable. For example, in the case of a nondeterministic SQL statement that could cause data divergence if reproduced on a slave server. The switch is done when matching any condition from a long list, and one of these conditions is the use of temporary tables.

Temporary tables are never logged using row-based format, but any statement that touches a temporary table is logged in row mode. This way, we intercept all the side effects that temporary tables can produce on non-temporary ones.

There is no need to use the row logging format for any other statements, solely because of the temp table presence. However, MySQL undertook such an excessive precaution: once some statement with a temporary table had appeared and the row-based logging was used, MySQL was logging unconditionally put all subsequent statements in row format.

Percona Server for MySQL has implemented more accurate behavior. Instead of switching to row-based logging until the last temporary table is closed, the usual rules of row vs. statement format apply, and we don’t consider the presence of currently opened temporary tables. This change was introduced with the fix of bug #151 (upstream #72475).

Temporary table drops and binloging on GTID-enabled server

Summary of the fix:

MySQL logs DROP statements for all temporary tables regardless of the logging mode under which these tables were created. This produces binlog writes and errand GTIDs on slaves with row and mixed logging. Percona Server for MySQL fixes this by tracking the binlog format at temporary table create time and uses it to decide whether a DROP should be logged or not.

Details:

Even with read_only mode enabled, the server permits some operations, including ones with temporary tables. With the previous fix, temporary table operations are not binlogged in row- or mixed-mode. But MySQL server doesn’t track what the logging mode was when a temporary table was created, and therefore unconditionally logs DROP statements for all temporary tables. These DROP statements receive IF EXISTS addition, which is intended to make them harmless.

Percona Server for MySQL has fixed this with the bug fixes #964, upstream #83003, and upstream #85258. Moreover, with all the binlogging fixes discussed so far nothing involving temporary tables is logged to the binary log in row or mixed format. There is no need to consider CREATE/DROP TEMPORARY TABLE unsafe for use in stored functions, triggers and multi-statement transactions in row/mixed format. Therefore, we introduced an additional fix to mark the creation and drop of temporary tables as unsafe inside transactions in statement-based replication only (the fixed bug is #1816, while the correspondent upstream one is #89467 and it is still open).

Safety of statements with a LIMIT clause

Summary of the fix:

MySQL Server considers all UPDATE/DELETE/INSERT ... SELECT statements with the LIMIT clause unsafe, no matter if they are really producing non-deterministic results or not. Percona Server for MySQL is more accurate because it acknowledges such instructions as safe when they include ORDER BY PK or WHERE condition.

Details:

MySQL Server treats UPDATE/DELETE/INSERT ... SELECT statements with the LIMIT clause as unsafe, considering that they produce an unpredictable number of rows. But some such statements can still produce an absolutely predictable result. One such deterministic case takes place when a statement with the LIMIT clause has an ORDER BY PK or WHERE condition.

The patch, making updates and deletes with a limit to be supposed as safe if they have an ORDER BY pk_column clause, was initially provided on the upstream bug report and incorporated later into Percona Server for MySQL with additional improvements. Bug fixed #44 (upstream #42415).

Performance improvements

There are also two modifications in Percona Server related to multi-source replication that improve performance on slaves.

The first improvement is about relay log position, which was always updated in multi-source replications setups regardless of whether the committed transaction has already been executed or not. Percona Server omits relay log position updates for the already logged GTIDs.

These unconditional relay log position updates caused additional fsync operations in the case of relay-log-info-repository=TABLE. With the higher number of channels transmitting such duplicate (already executed) transactions, the situation became proportionally worse. The problem was solved in Percona Server 5.7.18-14.  Bug fixed  #1786 (upstream #85141).

The second improvement decreases the load on slave nodes configured to update the master status and connection information only on log file rotation. MySQL additionally updated this information in the case of multi-source replication when a slave had to skip the already executed GTID event. This behavior was the cause of substantially higher write loads on slaves and lower replication throughput.

The configuration with master_info_repository=TABLE and sync_master_info=0  makes the slave update the master status and connection information in this table on log file rotation and not after each sync_master_info event, but it didn’t work on multi-source replication setups. Heartbeats sent to the slave to skip GTID events that it had already executed previously were evaluated as relay log rotation events and reacted with mysql.slave_master_info table sync. This inaccuracy could produce a huge (up to five times on some setups) increase in write load on the slave, before this problem was fixed in Percona Server for MySQL 5.7.20-19. Bug fixed  #1812 (upstream #85158).

Current status of fixes

The three issues related to temporary tables that were fixed in Percona Server 5.5 and contributed upstream, and the final fixes of the bugs #72475, #83003, and #85258, have landed into MySQL Server 8.0.4.

The post Binlog and Replication Improvements in Percona Server for MySQL appeared first on Percona Database Performance Blog.

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Percona Live is just over a week away — there’s an awesome keynote lineup, and you really should register. Also don’t forget to save the date as Percona Live goes to Frankfurt, Germany November 5-7 2018! Prost!

In acquisitions, we have seen MariaDB acquire MammothDB and Idera acquire Webyog.

Some interesting Amazon notes: Amazon Aurora Continues its Torrid Growth, More than Doubling the Number of Active Customers in the Last Year (not sure I’d describe it as torrid but this is great for MySQL and PostgreSQL), comes with a handful of customer mentions. In addition, there have already been 65,000 database migrations on AWS. For context, in late November 2017, it was 40,000 database migrations.

Releases

• Zstandard v1.3.4 – you can now tune compression parameters to go from near lz4 speeds, to near lzma ratios. Should this now be your go to compression format? Relevant from a MyRocks standpoint.
• DBdeployer – now a GA, at version 1. Goodbye MySQL Sandbox. Thanks for the awesome work Giuseppe Maxia! As he says, over 6,000+ tests performed.
• MariaDB 10.2.14 with MyRocks being Gamma (RC), bugs fixed. MariaDB 10.1.32 with bugs fixed. I guess both releases also have a new plugin, called DISKS, so the INFORMATION_SCHEMA now has a DISKS table.
• ProxySQL 1.4.8 just keeps getting better, with improvements even for ClickHouse backends.
• Percona Monitoring & Management (PMM) 1.9.1 now with faster loading of the index page with gzip/HTTP2, improvements for deployments on Amazon AWS, and something useful for Percona customers: the Percona Snapshot Server.
• Percona XtraBackup 2.4.10 with bug fixes, based on MySQL 5.7.19.
• Database Flow – self-describing, “Database Flow is an open source self-hosted SQL client, GraphQL server, and charting application that works with your database.”

Link List

• Amazon DynamoDB Continuous Backups and Point-In-Time Recovery (PITR) – continuous backup and restore with DynamoDB
• Managing db schema changes without downtime – from the co-founder of Discourse.
• All AWS Services GDPR ready
• What a Difference a Decade Makes – Stephen O’Grady on open source database adoption.
• 10 Steps to Develop an Incident Response Plan You’ll ACTUALLY Use
• Incident Post-Mortem and Security Advisory: Data Exposure After travis-ci.com Outage – be careful of those database queries
• PostgreSQL Begins Landing LLVM JIT Support For Faster Performance – incidentally have you also seen the work done by Vitesse Data?
• Monitoring PostgreSQL VACUUM processes
• textql – Execute SQL against structured text like CSV or TSV
• CockroachDB 2.0 Performance Makes Significant Strides
• GraphQL-to-MongoDB, or how I learned to stop worrying and love generated query APIs
• How Kustomer uses MongoDB and AWS to help fill in the gaps in the customer journey – I really enjoy reading these case studies that MongoDB are doing lately
• Enable your Devs to do Ops
• How to Speed-Up MongoDB Regex Queries by a Factor of up-to 10
• Building a Highly Available Web Application with Web Processing and Storing Capabilities Using MongoDB and Elk Stack

Upcoming appearances

• Percona Live 2018 – Santa Clara, California, USA – April 23-25 2018
• rootconf.in 2018 – Bangalore, India – May 10-11 2018
• Open Source Data Centre Conference – Berlin, Germany – June 12-13 2018
• DataOps Barcelona – Barcelona, Spain – June 21-22 2018
• OSCON – Portland, Oregon, USA – July 16-19 2018

Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

 

The post This Week in Data with Colin Charles 35: Percona Live 18 final countdown and a roundup of recent news appeared first on Percona Database Performance Blog.

In this blog post, I’ll look at point-in-time recovery (PITR) options for MySQL, MariaDB and Percona Server for MySQL.

It is a common good practice to extend data safety by having additional measures apart from regular data backups, such as delayed slaves and binary log backups. These two options provide the ability to restore the data to any given point in time, or just revert from some bad accidents. These methods have their limitations of course: delayed slaves only help if a deadly mistake is noticed fast enough, while full point-in-time recovery (PITR) requires the last full backup and binary logs (and therefore usually takes a lot of time).

How to reverse from disaster faster

Alibaba engineers and the MariaDB team implemented an interesting feature in their version of the mysqlbinlog tool: the --flashback option. Based on ROW-based DML events, it can transform the binary log and reverse purposes. That means it can help undo given row changes extremely fast. For instance, it can change DELETE events to INSERTs and vice versa, and it will swap WHERE and SET parts of the UPDATE events. This simple idea can dramatically speed up recovery from certain types of mistakes or disasters.

The question is whether it works with non-MariaDB variants. To verify that, I tested this feature with the latest available Percona Server for MySQL 5.7 (which is fully compatible with upstream MySQL).

master [localhost] {msandbox} ((none)) > select @@version,@@version_comment;
+---------------+--------------------------------------------------------+
| @@version     | @@version_comment                                      |
+---------------+--------------------------------------------------------+
| 5.7.21-20-log | Percona Server (GPL), Release 20, Revision ed217b06ca3 |
+---------------+--------------------------------------------------------+
1 row in set (0.00 sec)

First, let’s simulate one possible deadly scenario: a forgotten WHERE in DELETE statement:

master [localhost] {msandbox} ((none)) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 200      |
+----------+
1 row in set (0.00 sec)
master [localhost] {msandbox} ((none)) > delete from test.sbtest1;
Query OK, 200 rows affected (0.04 sec)
slave1 [localhost] {msandbox} ((none)) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec

So, our data is lost on both the master and slave!

Let’s start by downloading the latest MariaDB server 10.2.x package, which I’m hoping has a mysqlbinlog tool that works with MySQL 5.7, and unpack it to some custom location:

$ dpkg -x mariadb-server-10.2_10.2.13+maria~wheezy_amd64.deb /opt/maria/
$ /opt/maria/usr/bin/mysqlbinlog --help|grep flash
-B, --flashback Flashback feature can rollback you committed data to a

It has the function we are looking for. Now, we have to find the culprit transaction or set of transactions we want to revert. A simplified example may look like this:

$ mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 > mysql-bin.000002.sql
$ less mysql-bin.000002.sql

By searching through the decoded binary log, we are looking for transactions that have wiped out the table test.sbtest1. It looks like this (as the table had 200 rows, it is pretty long, so I’ve pasting only the beginning and the end):

BEGIN
/*!*/;
# at 291
#180314 15:30:34 server id 1  end_log_pos 348 CRC32 0x06cd193e  Table_map: `test`.`sbtest1` mapped to number 111
# at 348
#180314 15:30:34 server id 1  end_log_pos 8510 CRC32 0x064634c5         Delete_rows: table id 111
...
### DELETE FROM `test`.`sbtest1`
### WHERE
###   @1=200
###   @2=101
###   @3='26157116088-21551255803-13077038767-89418462090-07321921109-99464656338-95996554805-68102077806-88247356874-53904987561'
###   @4='51157774706-69740598871-18633441857-39587481216-98251863874'
# at 38323
#180314 15:30:34 server id 1  end_log_pos 38354 CRC32 0x6dbb7127        Xid = 97
COMMIT/*!*/;

It is very important to take the proper start and stop positions. We need the ones exactly after BEGIN and before the final COMMIT. Then, let’s test if the tool produces the reverse statements as expected. First, decode the rows to the .sql file:

$ /opt/maria/usr/bin/mysqlbinlog --flashback -v --base64-output=DECODE-ROWS --start-position=291 --stop-position=38323 mysql-bin.000002 > mysql-bin.000002_flash.sql

Inside, we find 200 of those. Looks good:

### INSERT INTO `test`.`sbtest1`
### SET
### @1=200
...

Since we verified the positions are correct, we can prepare a binary log file:

$ /opt/maria/usr/bin/mysqlbinlog --flashback --start-position=291 --stop-position=38323 mysql-bin.000002 > mysql-bin.000002_flash.bin

and load it back to our master:

master [localhost] {msandbox} (test) > source mysql-bin.000002_flash.bin
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 200      |
+----------+
1 row in set (0.00 sec)

and double check they restored on slaves:

slave1 [localhost] {msandbox} (test) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 200      |
+----------+
1 row in set (0.00 sec)

GTID problem

MariaDB has a completely different GTID implementation from MySQL and Percona Server. You can expect problems when decoding incompatible GTID enabled binary logs with MariaDB. As MariaDB’s mysqlbinlog does not support –start/stop-gtid options (even for its own implementation), we have to take the usual positions anyway. From a GTID-enabled binary log, for example, delete can look like this:

# at 2300
#180315 9:37:31 server id 1 end_log_pos 2365 CRC32 0x09e4d815 GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '00020996-1111-1111-1111-111111111111:2'/*!*/;
# at 2365
#180315 9:37:31 server id 1 end_log_pos 2433 CRC32 0xac62a20d Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1521103051/*!*/;
BEGIN
/*!*/;
# at 2433
#180315 9:37:31 server id 1 end_log_pos 2490 CRC32 0x275601d6 Table_map: `test`.`sbtest1` mapped to number 108
# at 2490
#180315 9:37:31 server id 1 end_log_pos 10652 CRC32 0xe369e169 Delete_rows: table id 108
...
# at 42355
#180315 9:37:31 server id 1 end_log_pos 42386 CRC32 0xe01ff558 Xid = 31
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

The tool seems to work, and transforms the delete transaction to a sequence of INSERTs. However, the server rejects it when we try to load it on a GTID-enabled master:

master [localhost] {msandbox} ((none)) > source mysql-bin.000003.flash
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
master [localhost] {msandbox} ((none)) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

Unfortunately, the solution here is either to disable GTID mode for the recovery time (which is surely tricky in replicated clusters), or try to add GTID-related information to the resulting binary log with the

--flashback option

. In my case, adding these lines worked (I used the next free available GTID sequence):

$ diff -u mysql-bin.000003.flash mysql-bin.000003.flash.gtid
--- mysql-bin.000003.flash 2018-03-15 10:20:20.080487998 +0100
+++ mysql-bin.000003.flash.gtid 2018-03-15 10:25:02.909953620 +0100
@@ -4,6 +4,10 @@
DELIMITER /*!*/;
#180315 9:32:51 server id 1 end_log_pos 123 CRC32 0x941b189a Start: binlog v 4, server v 5.7.21-20-log created 180315 9:32:51 at startup
ROLLBACK/*!*/;
+# at 154
+#180315 9:37:05 server id 1 end_log_pos 219 CRC32 0x69e4ce26 GTID last_committed=0 sequence_number=1 rbr_only=yes
+/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
+SET @@SESSION.GTID_NEXT= '00020996-1111-1111-1111-111111111111:5'/*!*/;
BINLOG '
sy+qWg8BAAAAdwAAAHsAAAAAAAQANS43LjIxLTIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACzL6paEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
@@ -724,6 +728,7 @@
'/*!*/;
COMMIT
/*!*/;
+SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

master [localhost] {msandbox} ((none)) > source mysql-bin.000003.flash.gtid
(...)
master [localhost] {msandbox} ((none)) > select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 200      |
+----------+
1 row in set (0.00 sec

Limitations

Obviously, flashback cannot help after DROP/TRUNCATE or other DDL commands. These are not transactional, and affected rows are never recorded in the binary log. It doesn’t work with encrypted or compressed binary logs either. But most importantly, to produce complete events that can reverse bad transactions, the binary format must be ROW. The row image also must be FULL:

master [localhost] {msandbox} ((none)) > select @@binlog_format,@@binlog_row_image;
+-----------------+--------------------+
| @@binlog_format | @@binlog_row_image |
+-----------------+--------------------+
| ROW             | FULL               |
+-----------------+--------------------+
1 row in set (0.00 sec)

If these conditions are not met (or if you’re dealing with a too-complicated GTID issue), you will have to follow the standard point-in-time recovery procedure.

The post Flashback: Another Take on Point-In-Time Recovery (PITR) in MySQL/MariaDB/Percona Server appeared first on Percona Database Performance Blog.

Percona announces the release of Percona Monitoring and Management 1.9.1. PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM 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.

This release contains bug fixes only and supersedes Percona Monitoring and Management 1.9.0. This release effectively solves the problem in QAN when the Count column actually displayed the number of queries per minute, not per second, as the user would expect. The following screenshot demonstrates the problem. The value of the Count column for the TOTAL row is 649.38 QPS (queries per second). The total number 38.96 k (38960) is only sixty times greater than the reported value of QPS. Thus, queries were counted for each minute within the selected time range of Last 1 hour.

Query Analytics in PMM version 1.9.0.

The corrected version of QAN in PMM 1.9.1 shows that queries are now counted per second. The total number of queries is 60 * 60 greater than the value of QPS, as should be expected for the chosen time range.

Query Analytics in PMM version 1.9.1.

Bug fixes

• PMM-2364: QPS are wrong in QAN

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management 1.9.1 Is Now Available appeared first on Percona Database Performance Blog.

In this blog post, we demonstrate a new feature in ProxySQL Admin: support for multiple clusters.

In a previous blog post, Ramesh and Roel introduced a new tool that helps configured Percona XtraDB Cluster nodes into ProxySQL. However, at that time it only worked for a single cluster per ProxySQL Admin configuration. Starting from ProxySQL 1.4.6, which comes with an improved ProxySQL Admin tool (proxysql-admin), our tool now supports configuring multiple Percona XtraDB Cluster clusters with ease (PSQLADM-32).

Pre-requisites

• Cluster name (wsrep_cluster_name) should be unique.
• proxysql-admin.cnf configuration differences:
  • ProxySQL READ/WRITE hostgroup should be different for each cluster.
  • Application user should be different for each cluster.
• Host priority feature support only one cluster at a time.

Configuring /etc/proxysql-admin.cnf

As mentioned above, the CLUSTER_APP_USERNAME and the WRITE/READ_HOSTGROUP should be different for each cluster. Wsrep_cluster_name should also be unique for each cluster.

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| wsrep_cluster_name | cluster1 |
+--------------------+----------+
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| wsrep_cluster_name | cluster2 |
+--------------------+----------+

Sample configuration of /etc/proxysql-admin.cnf for cluster1:

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME='admin'
export PROXYSQL_PASSWORD='admin'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'
# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='root'
export CLUSTER_PASSWORD='sekret'
export CLUSTER_HOSTNAME='10.0.3.41'
export CLUSTER_PORT='3306'
# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='monit0r'
# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='cluster1_user'
export CLUSTER_APP_PASSWORD='c1_passw0rd'
# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID='10'
export READ_HOSTGROUP_ID='11'
# ProxySQL read/write configuration mode.
export MODE="singlewrite"
# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf

Sample configuration of /etc/proxysql-admin.cnf for cluster2

# proxysql admin interface credentials.
export PROXYSQL_DATADIR='/var/lib/proxysql'
export PROXYSQL_USERNAME='admin'
export PROXYSQL_PASSWORD='admin'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'
# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME='root'
export CLUSTER_PASSWORD='sekret'
export CLUSTER_HOSTNAME='10.0.3.173'
export CLUSTER_PORT='3306'
# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='monit0r'
# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='cluster2_user'
export CLUSTER_APP_PASSWORD='c2_passw0rd'
# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID='20'
export READ_HOSTGROUP_ID='21'
# ProxySQL read/write configuration mode.
export MODE="loadbal"
# ProxySQL Cluster Node Priority File
export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf

Setting up Percona XtraDB Cluster nodes in ProxySQL

I would add that you have the option to use a single proxysql-admin.cnf file, and just edit the file where changes are appropriate. You could also use two different files to configure ProxySQL. In my example, I used two files with the contents as seen above:

[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster1.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
Configuring ProxySQL monitoring user..
ProxySQL monitor username as per command line/config-file is monitor
User 'monitor'@'10.%' has been added with USAGE privilege
Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
Percona XtraDB Cluster application username as per command line/config-file is cluster1_user
Percona XtraDB Cluster application user 'cluster1_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges
Adding the Percona XtraDB Cluster server nodes to ProxySQL
Configuring singlewrite mode with the following nodes designated as priority order:
Write node info
+-----------+--------------+------+---------+---------+
| hostname  | hostgroup_id | port | weight  | comment |
+-----------+--------------+------+---------+---------+
| 10.0.3.41 | 10           | 3306 | 1000000 | WRITE   |
+-----------+--------------+------+---------+---------+
ProxySQL configuration completed!
ProxySQL has been successfully configured to use with Percona XtraDB Cluster
You can use the following login credentials to connect your application through ProxySQL
mysql --user=cluster1_user -p  --host=localhost --port=6033 --protocol=tcp

[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster2.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is loadbal
Host priority file (/var/lib/proxysql/host_priority.conf) is already present. Would you like to replace with the new file [y/n] ? n
Host priority file is not deleted. Please make sure you have properly configured /var/lib/proxysql/host_priority.conf
Configuring ProxySQL monitoring user..
ProxySQL monitor username as per command line/config-file is monitor
User 'monitor'@'10.%' has been added with USAGE privilege
Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
Percona XtraDB Cluster application username as per command line/config-file is cluster2_user
Percona XtraDB Cluster application user 'cluster2_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges
Adding the Percona XtraDB Cluster server nodes to ProxySQL
ProxySQL configuration completed!
ProxySQL has been successfully configured to use with Percona XtraDB Cluster
You can use the following login credentials to connect your application through ProxySQL
mysql --user=cluster2_user -p  --host=localhost --port=6033 --protocol=tcp

Inspect ProxySQL tables

Login to ProxySQL to confirm that the setup is correct:

[root@proxysql_multi-pxc ~]# mysql -uadmin -p -P6032 -h127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 33893
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select * from mysql_users;
+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username      | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| cluster1_user | *448C417D62616B779E789F3BD72AA3DE9C319EA3 | 1      | 0       | 10                |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
| cluster2_user | *AB1E96267D16A9F26A201282F9ED80B50244B770 | 1      | 0       | 20                |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from mysql_servers;
+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+
| hostgroup_id | hostname   | port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment   |
+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+
| 11           | 10.0.3.81  | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ      |
| 10           | 10.0.3.41  | 3306 | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0              | WRITE     |
| 11           | 10.0.3.232 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ      |
| 20           | 10.0.3.173 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READWRITE |
| 20           | 10.0.3.78  | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READWRITE |
| 20           | 10.0.3.141 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READWRITE |
+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+
6 rows in set (0.00 sec)
mysql> select * from scheduler;
+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+
| id | active | interval_ms | filename                         | arg1 | arg2 | arg3 | arg4 | arg5                                                 | comment  |
+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+
| 6  | 1      | 3000        | /usr/bin/proxysql_galera_checker | 10   | 11   | 1    | 1    | /var/lib/proxysql/cluster1_proxysql_galera_check.log | cluster1 |
| 7  | 1      | 3000        | /usr/bin/proxysql_galera_checker | 20   | 20   | 0    | 1    | /var/lib/proxysql/cluster2_proxysql_galera_check.log | cluster2 |
+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+
2 rows in set (0.00 sec)
mysql> select * from mysql_query_rules;
+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username      | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest        | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 7       | 1      | cluster1_user | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE | NULL          | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
| 8       | 1      | cluster1_user | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 11                    | NULL      | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL | 1     | NULL    |
+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.00 sec)
mysql> exit
Bye

It’s as easy as that! We hope you continue to enjoy using ProxySQL Admin!

The post ProxySQL Admin Support for Multiple Clusters appeared first on Percona Database Performance Blog.

We’ve posted the Percona Live 2018 keynote addresses for the seventh annual Percona Live Open Source Database Conference 2018, taking place April 23-25, 2018 at the Santa Clara Convention Center in Santa Clara, CA. 

This year’s keynotes explore topics ranging from how cloud and open source database adoption accelerates business growth, to leading-edge emerging technologies, to the importance of MySQL 8.0, to the growing popularity of PostgreSQL.

We’re excited by the great lineup of speakers, including our friends at Alibaba Cloud, Grafana, Microsoft, Oracle, Upwork and VividCortex, the innovative leaders on the Cool Technologies panel, and Brendan Gregg from Netflix, who will discuss how to get the most out of your database on a Linux OS, using his experiences at Netflix to highlight examples.  

With the theme of “Championing Open Source Databases,” the conference will feature multiple tracks, including MySQL, MongoDB, Cloud, PostgreSQL, Containers and Automation, Monitoring and Ops, and Database Security. Once again, Percona will be offering a low-cost database 101 track for beginning users who want to learn how to use and operate open source databases.

The Percona Live 2018 keynotes include:

Tuesday, April 24, 2018

• Open Source for the Modern Business – Peter Zaitsev of Percona will discuss how open source database adoption continues to grow in enterprise organizations, the expectations and definitions of what constitutes success continue to change. A single technology for everything is no longer an option; welcome to the polyglot world. The talk will include several compelling open source projects and trends of interest to the open source database community and will be followed by a round of lightning talks taking a closer look at some of those projects.
• Cool Technologies Showcase – Four industry leaders will introduce key emerging industry developments. Andy Pavlo of Carnegie Mellon University will discuss the requirements for enabling autonomous database optimizations. Nikolay Samokhvalov of PostgreSQL.org will discuss new PostgreSQL tools. Sugu Sougoumarane of PlanetScale Data will explore how Vitess became a high-performance, scalable and available MySQL clustering cloud solution in line with today’s NewSQL storage systems. Shuhao Wu of Shopify explains how to use Ghostferry as a data migration tool for incompatible cloud platforms.
• State of the Dolphin 8.0 – Tomas Ulin of Oracle will discuss the focus, strategy, investments and innovations that are evolving MySQL to power next-generation web, mobile, cloud and embedded applications – and why MySQL 8.0 is the most significant MySQL release in its history.
• Linux Performance 2018 – Brendan Gregg of Netflix will summarize recent performance features to help users get the most out of their Linux systems, whether they are databases or application servers. Topics include the KPTI patches for Meltdown, eBPF for performance observability, Kyber for disk I/O scheduling, BBR for TCP congestion control, and more.

Wednesday, April 25, 2018

• Panel Discussion: Database Evolution in the Cloud – An expert panel of industry leaders, including Lixun Peng of Alibaba, Sunil Kamath of Microsoft, and Baron Schwartz of VividCortex, will discuss the rapid changes occurring with databases deployed in the cloud and what that means for the future of databases, management and monitoring and the role of the DBA and developer.
• Future Perfect: The New Shape of the Data Tier – Baron Schwartz of VividCortex will discuss the impact of macro trends such as cloud computing, microservices, containerization, and serverless applications. He will explore where these trends are headed, touching on topics such as whether we are about to see basic administrative tasks become more automated, the role of open source and free software, and whether databases as we know them today are headed for extinction.
• MongoDB at Upwork – Scott Simpson of Upwork, the largest freelancing website for connecting clients and freelancers, will discuss how MongoDB is used at Upwork, how the company chose the database, and how Percona helps make the company successful.

We will also present the Percona Live 2018 Community Awards and Lightning Talks on Monday, April 23, 2018, during the Opening Night Reception. Don’t miss the first day of tutorials and Opening Night Reception!

Register for the conference on the Percona Live Open Source Database Conference 2018 website.

Sponsorships

Limited Sponsorship opportunities for Percona Live 2018 Open Source Database Conference are still available, and offer the opportunity to interact with the DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solution vendors, and entrepreneurs who typically attend the event. Contact live@percona.com for sponsorship details.

• Diamond Sponsors – Percona, VividCortex
• Platinum – Alibaba Cloud, Microsoft
• Gold Sponsors – Facebook, Grafana
• Bronze Sponsors – Altinity, BlazingDB, Box, Dynimize, ObjectRocket, Pingcap, Shannon Systems, SolarWinds, TimescaleDB, TwinDB, Yelp
• Contributing Sponsors – cPanel, Github, Google Cloud, NaviCat
• Media Sponsors – Database Trends & Applications, Datanami, EnterpriseTech, HPCWire, ODBMS.org, Packt

The post Calling All Polyglots: Percona Live 2018 Keynote Schedule Now Available! appeared first on Percona Database Performance Blog.

In this blog post, we’ll look at options for migrating database charsets to utf8mb4.

Migrating charsets, in my opinion, is one of the most tedious tasks in a DBA’s life. There are so many things involved that can screw up our data, making it work is always hard. Sometimes what seems like a trivial task can become a nightmare very easily, and keeps us working for longer than expected.

I’ve recently worked on a case that challenged me with lots of tests due to some existing schema designs that made InnoDB suffer. I’ve decided to write this post to put together some definitive guide to enact charset conversion with minimal downtime and pain.

• First disclosure: I can’t emphasize enough that you need to always backup your data. If something goes wrong, you can always roll things back by keeping a healthy set of backups.
• Second disclosure: A backup can’t be considered a good backup until you test it, so I can’t emphasize enough that running regular backups and also performing regular restore tests is a must-to-do task for being in the safe side.
• Third and last disclosure: I’m not pretending to present the best or only way to do this exercise. This is the way I consider easiest and painless to perform a charset conversion with minimal downtime.

My approach involves at least one slave for failover and logical/physical backup operations to make sure that data is loaded properly using the right charset.

In this case, we are moving from latin1 (default until MySQL 8.0.0) to utf8mb4 (new default from 8.0.1). In this post, Lefred refers to this change and some safety checks for upgrading. For our change, an important thing to consider: Latin1 charset stores one byte per character, while utf8mb4 can store up to four bytes per character. This change definitely impacts the disk usage, but also makes us hit some limits that I describe later in the plan.

So let’s put out hands in action. First, let’s create a slave using a fresh (non-locking) backup. Remember that these operations are designed to minimize downtime and reduce any potential impact on our production server.

If you already have a slave that can act as a master replacement then you can skip this section. In our source server, configure binlog_format and flush logs to start with fresh binary logs:

set global binlog_format=MIXED;
flush logs;

Start a streaming backup using Percona Xtrabackup through netcat in the destination server:

nc -l 9999 | cat - > /dest/folder/backup.tar

and in our source server:

innobackupex --stream=tar ./ | nc dest_server_ip 9999

Once the backup is done, untar and restore the backup. Then set up the slave:

tar -xif /dest/folder/backup.tar
innobackupex --apply-log /dest/folder/
/etc/init.d/mysql stop
rm -rf /var/lib/mysql/
mv /dest/folder/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
/etc/init.d/mysql start
cat /var/lib/mysql/xtrabackup_binlog_info
change master to master_host='master_host', master_user='master_user, master_password='master_password', master_log_file='file_printed_in_xtrabackup_binlog_info', master_log_pos=pos_printed_in_xtrabackup_binlog_info;
start slave;

Now that we have the slave ready, we prepare our dataset by running two mysqldump processes so we have data and schemas in separate files. You can also run this operation using MyDumper or mysqlpump, but I will keep it easy:

STOP SLAVE;
SHOW SLAVE STATUS;

Write down this output, as it may be needed later:

mysqldump --skip-set-charset --no-data --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');"` > schema.sql
mysqldump --skip-set-charset -n -t --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');"` > data.sql

Notice that I’m passing a command as an argument to –databases to dump all databases but mysql, performance_schema and information_schema (hack stolen from this post, with credit to Ronald Bradford).  It is very important to keep the replication stopped, as we will resume replication after fully converting our charset.

Now we have to convert our data to utf8mb4. This is easy as we just need to touch the schema.sql file by running few commands:

sed -e "s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8mb4/g" schema.sql
sed -e "s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci /" schema.sql
sed -e "s/SET character_set_client = utf8/SET character_set_client = utf8mb4/" schema.sql

Can this be a one-liner? Yes, but I’m not a good basher.

Now we are ready to restore our data using new encoding:

mysql -e "set global innodb_large_prefix=1;"
mysql < schema.sql
mysql < data.sql

Notice I’ve enabled the variable innodb_large_prefix. This is important because InnoDB limits index prefixes to 768 bytes by default. If you have an index based in a varchar(255) data type, you will get an error because the new charset exceeds this limit (up to four bytes per character goes beyond 1000 bytes) unless you limit the index prefix. To avoid issues during data load, we enable this variable to extend the limit to 3072 bytes.

Finally, let’s configure our server and restart it to make sure to set new defaults properly. In the my.cnf file, add:

[client]
default-character-set=utf8mb4
[mysqld]
skip-slave-start
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_large_prefix=1

Let’s resume replication after the restart, and make sure everything is ok:

START SLAVE;
SHOW SLAVE STATUS;

Ok, at this point we should be fine and our data should be already converted to utf8mb4. So far so good. The next step is to failover applications to use the new server, and rebuild the old server using a fresh backup using xtrabackup as described above.

There are few things we need to consider now before converting this slave into master:

1. Make sure you properly configured applications. Charset and collation values can be set as session level, so if you set your connection driver to another charset then you may end up mixing things in your data.
2. Make sure the new slave is powerful enough to handle traffic from the master.
3. Test everything before failing over production applications. Going from Latin1 to utf8mb4 should be straightforward, as utf8mb4 includes all the characters in Latin1. But let’s face it, things can go wrong and we are trying to avoid surprises.
4. Last but not least, all procedures were done in a relatively small/medium sized dataset (around 600G). But this conversion (done via logical backups) is more difficult when talking about big databases (i.e., in the order of TBs). In these cases, the procedure helps but might not be good enough due to time restrictions (imagine loading a 1TB table from a logical dump — it take ages). If you happen to face such a conversion, here is a short, high-level plan:

• • Convert only smaller tables in the slave (i.e., those smaller than 500MB) following same procedure. Make sure to exclude big tables from the dump using the –ignore-tables parameter in mysqldump.
  • Convert bigger tables via alter table, as follows:

    ALTER TABLE big_table MODIFY latin1_column varbinary(250);
    ALTER TABLE big_table MODIFY latin1_column varchar(250) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

  • Once everything is finished, you can resume replication. Notice you can do dump/conversion/restore in parallel with the altering of bigger tables, which should reduce the time required for conversion.

It’s important to understand why we need the double conversion from latin1 to varbinary to utf8mb4. This post from Marco Tusa largely explains this.

Conclusion

I wrote this guide from my experience working with these type of projects. If you Google a bit, you’ll find a lot of resources that make this work, along with different solutions. What I’ve tried to present here is a guide to help you deal with these projects. Normally, we have to perform these changes in existing datasets that sometimes are big enough to prevent any work getting done via ALTER TABLE commands. Hopefully, you find this useful!

The post Migrating Database Charsets to utf8mb4: A Story from the Trenches appeared first on Percona Database Performance Blog.

Percona’s Principal Support Engineer, Sveta Smirnova presents the webinar MySQL Test Framework for Troubleshooting on April 12, 2018, at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4).

MySQL Test Framework (MTR) provides a unit test suite for MySQL. MySQL Server developers and contributors write the tests in the framework, and use them to ensure the build is working correctly.

I found that this isn’t the only thing that makes MTR useful. I regularly use it in my support job to help customers and verify bug reports.

With MySQL Test Framework I can:

• Create a complicated environment in a single step, and re-use it later
• Test the same scenario on dozens of MySQL/Percona/MariaDB server versions with a single command
• Test concurrent scenarios
• Test errors and return codes
• Work with results, external commands and stored routines

Everything can be done with a single script that can be reused on any machine, any time, with any MySQL/Percona/MariaDB Server version.

In this webinar, I will show my way of working with MySQL Test Framework. I hope you will love it as I do!

Register for the webinar now.

Sveta Smirnova, Principal Technical Services Engineer

Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns that can quickly solve typical issues and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona, Sveta worked as Support Engineer in MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

The post Webinar Thursday, April 12, 2018: MySQL Test Framework for Troubleshooting appeared first on Percona Database Performance Blog.