Jun
01
2018
--

This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road

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

Shortly after the last dispatch, I jetted off for a spot of vacation (which really meant I was checking out the hype behind Blockchain with a database developer lens at the Blockchain Week NYC), and then some customer visits in Seoul, which explains the short hiatus. Here’s to making this more regular as the summer approaches.

I am about to embark on a fairly long trip, covering a few upcoming appearances: Lisbon for the Percona Engineering meeting, SouthEastLinuxFest in Charlotte, the Open Source Data Centre Conference in Berlin and then the DataOps Barcelona event. I have some discount codes: 50% discount for OSDC with the code OSDC_FOR_FRIENDS, and 50% discount for DataOps Barcelona with the code dataopsbcn50. Expect this column to reflect my travels over the next few weeks.

There has been a lot of news on the MariaDB front: MariaDB 10.3.7 went stable/GA! You might have noticed more fanfare around the release name MariaDB TX 3.0, but the reality is you can still get this download from your usual MariaDB Foundation site. It is worth noting that the MariaDB Foundation 2017 financials have also been released. Some may have noticed a couple months back there was a press release titled Report “State of the Open-Source DBMS Market, 2018” by Gartner Includes Pricing Comparison With MariaDB. This led to a Gartner report on the State of the Open-Source DBMS Market, 2018; although the report has since been pulled. Hopefully we see it surface again.

In the meantime, please do try out MariaDB 10.3.7 and it would be great to hear feedback. I also have an upcoming Percona webinar on MariaDB Server 10.3 on June 26 2018 — when the sign up link appears, I will be sure to include it here.

Well written, and something worth discussing: Should Red Hat Buy or Build a Database?. The Twitter discussion is also worth looking at.

Releases

Link List

Upcoming appearances

Feedback

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

The post This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road appeared first on Percona Database Performance Blog.

May
25
2018
--

Webinar Tues, 5/29: MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together

Please join Percona’s Principal Architect Alex Rubin as he presents MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together to give you a highly available cluster database environment on Tuesday, May 29th at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).

 

In this webinar, Alex will discuss how to deploy a highly available MySQL database environment on Kubernetes/Openshift using Percona XtraDB Cluster (PXC) together with MySQL Proxy to implement read/write splitting.

If you have never used Kubernetes and Openshift, or never used PXC / MySQL Proxy, Alex will do a quick introduction to these technologies. There will also be a demo where Alex sets up a PXC cluster with ProxySQL in Openshift Origin and tries to break it.

By the end of this webinar you will have a better understanding of:

  • How to deploy Percona XtraDB Cluster with ProxySQL for HA solutions
  • How to leverage Kubernetes/Openshift in your environments
  • How to troubleshoot performance issues

Register for the webinar

Alexander Rubin, Principal Consultant

Alexander RubinAlexander joined Percona in 2013. Alexander worked with MySQL since 2000 as DBA and Application Developer. Before joining Percona he was doing MySQL consulting as a principal consultant for over 7 years (started with MySQL AB in 2006, then Sun Microsystems and then Oracle). He helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. Alexander also helped customers design Big Data stores with Apache Hadoop and related technologies.

The post Webinar Tues, 5/29: MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together appeared first on Percona Database Performance Blog.

May
22
2018
--

ProxySQL 1.4.8 and Updated proxysql-admin Tool Now in the Percona Repository

ProxySQL for Connection Pooling

ProxySQL 1.4.5ProxySQL 1.4.8, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 1.4.8 source and binary packages available at https://percona.com/downloads/proxysql include ProxySQL Admin – a tool, developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 1.4.8 are available as well: https://hub.docker.com/r/percona/proxysql/. You can download the original ProxySQL from https://github.com/sysown/proxysql/releases.

This release fixes the following bugs in ProxySQL Admin:

Usability improvement:

  • PR #84: Now proxysql-status tool dumps host_priority and proxysql-admin.cnf. Also output format was changed.

Other improvements and bug fixes:

  • PR #66: --syncusers option now makes ProxySQL-admin to update the user’s password in ProxySQL database if there is any password difference between ProxySQL user and MySQL user.
  • PSQLADM-45: it was unclear from the help screen, that --config-file option requires an argument.
  • PSQLADM-48${PROXYSQL_DATADIR}/${CLUSTER_NAME}_mode file was not created at ProxySQL-admin upgrade (1.4.5 or before to 1.4.6 onwards).
  • PSQLADM-52: The  proxysql_galera_checker script was not checking empty query rules.
  • PSQLADM-54: proxysql_node_monitor did not change OFFLINE_HARD status properly for the coming back online nodes.

ProxySQL is available under OpenSource license GPLv3.

The post ProxySQL 1.4.8 and Updated proxysql-admin Tool Now in the Percona Repository appeared first on Percona Database Performance Blog.

May
09
2018
--

Does the Version Number Matter?

ProxySQL

ProxySQLYes, it does! In this blog post, I am going to share my recent experiences with ProxySQL and how important the database software version number can be.

Migration

I was working on a migration to Percona XtraDB Cluster (PXC) with ProxySQL, fortunately on a staging environment first so we could catch any issues (like this one).

We installed Percona XtraDB Cluster and ProxySQL on the staging environment and repointed the staging application to ProxySQL. At first, everything looked great. We were able to do some application tests and everything looked good. I advised the customer to do more testing to make sure everything works well.

Something is wrong, but what?

A few days later the customer noticed that their application was not working properly.

We started investigating. Everything seemed well-configured, and the only thing we could see in the application log was the following:

2018-04-20 11:28:31,169 [ default-threads - 42] ERROR Error in lifecycle management : org.hibernate.StaleStateException : Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1 {it.tasgroup.monetica.gt.lifecycle.LifeCycle:line 103} (method: error)
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:85)
at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:70)

Based on this error I still did not know what is wrong. Were some of the queries failing because of PXC, ProxySQL or some other settings?

We redirected the application to one of the nodes from PXC, and everything worked fine. We tried HAproxy as well, and everything worked again. We knew something was happening around ProxySQL which is causing the problem. But we still could not find the problem. Every query went through ProxySQL without any issue.

Debug log is our savior

The customer finally enabled the application debug logging so we could see which query was failing:

delete from TABLENAME where ID='11' and Timestamp ='2018-04-20 16:15:03';

I was confused at first: this is a kind of simple query, what could be wrong? Let’s investigate it on the cluster. When I tried to select the data on the cluster, it gave me back zero results. That’s OK, maybe the row was already deleted?

For this investigation, the slow query logging was enabled and long_query_time set to 0 to log all the queries. I checked the slow query log looking for queries like this. What I found helped me realize what the problem was:

delete from TABLENAME where ID=10 and Timestamp ='2018-04-20 11:17:22.35';
delete from TABLENAME where ID=24 and Timestamp ='2018-04-20 11:17:31.602';
delete from TABLENAME where ID=43 and Timestamp ='2018-04-20 11:18:13.2';
delete from TABLENAME where ID=22 and Timestamp ='2018-04-20 11:11:02.854';
delete from TABLENAME where ID=11 and Timestamp ='2018-04-20 11:21:57';
delete from TABLENAME where ID=64 and Timestamp ='2018-04-20 11:18:34';
delete from TABLENAME where ID=47 and Timestamp ='2018-04-20 10:38:35';
delete from TABLENAME where ID=23 and Timestamp ='2018-04-20 11:30:03';

I hope you see the difference! The first four lines have fractional seconds! At that time, the application was pointed to the cluster directly. So ProxySQL cut off the fractional seconds? That would be a nasty bug.

I checked the application log again with the debug information, and I could see the application does not even use the fractional seconds in the queries when it points to ProxySQL. This is why the query was failing (does not delete any rows), because in the table all the rows had fractional seconds but the queries were not using them.

So why does the application not use fractional seconds with ProxySQL?

First of all, fractional seconds were introduced in MySQL 5.6.4. The application is a Java-based application with Jboss and Hibernate. I knew ProxySQL reports MySQL 5.5. Maybe the application/connector reads the version number and makes decisions based on that?

It was quite easy to test this theory by just changing the version number in ProxySQL like this:

update global_variables set variable_value="5.7.21" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

The application had to be restarted (probably it was caching the previous settings) but after that everything was working as expected.

But be careful, now it will report 5.7.21 for all the hostgroups. What if you have multiple hostgroups with different MySQL versions? It would be nice if you could define this for every hostgroup.

Conclusion

The solution was very easy, but finding the source of the problem took a long time. If you are planning to use ProxySQL, I would always recommend changing the mysql-server_version to match to the underlying MySQL server version number because who knows which connector or application checks the version and makes a decision based on that.

There is another example here where Marco Tusa had a very similar problem with a Java connector.

The post Does the Version Number Matter? appeared first on Percona Database Performance Blog.

May
03
2018
--

Q&A: “Percona XtraDB Cluster 5.7 and ProxySQL for Your High Availability Needs” Webinar

High Availability

High AvailabilityOn March 22, 2018, we held a webinar on how Percona XtraDB cluster 5.7 (PXC) and ProxySQL can help achieve your database clustering high availability needs. Firstly, thanks to all the attendees for taking time to attend the webinar and we are sure you had a webinar experience. We tried answering some of your high availability questions during the call but due to time restrictions if we missed some of the questions then this blog will help clarify them.

Q. You say the replication to servers is virtually synchronous, if there is any latency, does ProxySQL detect this and select a node accordingly?

A. PXC nodes are virtually synchronous, which effectively means while the apply/commit of a transaction may be in progress on one node, other nodes may have completed applying it. There is no direct way for ProxySQL to know about this, but it could be traced by looking at wsrep_last_applied and wsrep_last_committed. Also, if a user expects to always fetch updated data, then a wsrep_sync_wait configuration can be used.

Q. Hello, do you suggest geoReplication / wan clustering for an e-commerce website? Let ‘s say www.domain.it served by an Italian pxc cluster and www.domain.us served by a US PXC cluster?

A. Geo-distributed PXC is already in use by a lot of customers, and is meant to exactly serve the use-case you have pointed out. An important aspect of geo-distributed clustering (that often gets missed) is to configure timeout and window setting to accommodate network latency and segment settings. There is also a separate webinar on this topic and you can surely get in touch with us to find out more details on how to configure it correctly.

Q. Can we add a read-only node with PXC?

A. You can simply mark the selected nodes as super_read_only (or read_only). Replication continues as normal but direct traffic is blocked.

Q. Does the ProxySQL impact performance?

A. Using all of ProxySQL’s features gives you a huge performance improvement. Here is the sample use case.

Q. I have not had “excellent” results with Drupal. (e.g., clearing cache sometimes causes corruption, although i ensure all tables do have a primary key). Any advice on its suitability? I am currently using proxySQL with a single percona (non-cluster) 5.7 but would like to try again with PXC if advisable.

A. Not sure what exact problem you faced, but you may want to check this variable and articles around it wsrep_drupal_282555_workaround.

Q. Another question (to queue up as you are able to answer if possible): do you recommend SSL between ProxySQL — and specifically, what are the performance impacts, especially if there’s some latency between proxySQL and master percona db for writes?

A. We recommend SSL for security reasons, but it depends on the individual setup. Currently, ProxySQL does not support SSL from frontends. This feature is only available since 2.0. https://github.com/sysown/proxysql/wiki/SSL-Support

Q1. Can i put two PXC clusters in master-slave replication mode with automatic failover?

Q2. How can i setup two PXC clusters in master-slave replication model with automatic failover?

A. You can have async master-slave replication link among two PXC clusters, but automatic failover of the node (if the acting master from cluster-1 fails then another active nodes of the cluster takes over as master) is currently not supported.

Q. Can the replication be done from ProxySQL level, so that if one node goes down in slave PXC, another node in PXC will take over the slave role?

A. This feature is not supported through ProxySQL. You can monitor replication lag through ProxySQL.

Q. Suppose if i have 5 node cluster in DC1 & DC2, how can we make transaction successful as soon as nodes in DC1 are committed rather than waiting for certification from nodes in DC2?

A. Given the transaction is executed on the local node and during commit (as a pre-commit stage) it is replicated (replication action doesn’t include certification and commit) to the other nodes of the cluster. Once replicated each node can parallelly certify, apply and commit the transaction. So this effectively means a transaction doesn’t need to be certified on all the nodes of the cluster before communicating commit success to end-user. Once the transaction is replicated, originating node can complete the local commit and communicate success to the application.

Q. Hi, thank you for the webinar is ProxySQL support HA, is it a single point of failure?

A. ProxySQL supports Native Clustering, thereby forming a ProxySQL cluster (vs. a single ProxySQL node) and in turn helps avoid a single point failure.

Q.  What is a good setup you will recommend, make proxySQL on some other server/vm or on the same as one of PXC nodes?

A. We would recommend installing ProxySQL on an independent node (or share with other applications). We don’t recommend installing ProxySQL on a PXC node. If the node hosting PXC and ProxySQL goes down (network or power failure), even though the cluster is working, the application will still lose connectivity as the ProxySQL gateway goes down as well.

Q. Let’s say we have three nodes, good quorum, what happened when one node goes down for maintenance what happens to the quorum since only two nodes now?

A. Two nodes can still form the quorum and continue servicing the workload.

Q If the transaction is not committed to all the nodes then will the cluster remains locked for read too?

A. No. The transaction commit is independent of a read action. “transaction commit” can continue in the background and the user can continue to read from the cluster node. If a user has configured wsrep_sync_wait, which effectively means wait for a transaction to get committed to fetch updated data only, then the read may wait for transaction commit to complete.

Q. Is there a way to do partitioning over data? To not have 100% replicate in each master?

A. PXC/Galera, being a multi-master solution, doesn’t recommend unsync data nodes. As an end-user you can still achieve it by setting wsrep_on=off -> execute a workload (this will not be replicated on the cluster) -> wsrep_on=on (all action post this point will again follow replication). This can lead to data inconsistency, though,  and shutdown of the cluster if the workload or action are not properly segregated – so not-recommended.

Q. Are changes done by triggers rollbackable?

A. Yes, they are.

Q. Does ProxySQL prevent “mysql server gone away” in mostly idle daemons?

A. ProxySQL Monitor Module regularly probes the backend nodes and marks the node as OFFLINE in the ProxySQL database if MySQL server is down.

Q. Can proxysql cache rules use regexes?

A. We can use regex with ProxySQL query rules. Go here for more info.

Q. Can PMM be used in Digitalocean droplets?

A. Yes.

Q. In regards to PXC, how much delay is introduced when data is written since it has to appear on all nodes?

A. When a user initiates a transaction on given node (let’s call it an originating node), then it is first applied (not committed) on the said node and a binary write-set is created. This write-set is then replicated on other nodes of the cluster. Once the replication is successful, each node can independently certify, apply and commit the transaction. Since originating node has already applied the transaction, it just needs to certify and commit the transaction. But it is interesting to note that the apply stage on the other replicated node is fast too, given that the transaction is now packed in a database optimized apply format. In short, there would be no delay (or marginal delay). Delay could be higher if the transaction is a huge transaction, as the apply stage could take time. That is one of the reasons Galera doesn’t recommend huge transactions.

Q. How does PXC (Percona XtraDB Cluster) allow DDL (schema changes) on one server with DML on the same table on another server? (This can break MySQL Master-Master replication)?

A. PXC executes DDL using the TOI (total order isolated) protocol. In short, while DDL is executing it takes complete control of the node (no other parallel DML or DDL is allowed). DDL executes at the same position on all then does.

Q. Can ProxySQL split read-write queries based on stored procedure names (patterns)? e.g. sp_write vs sp_read?

A. ProxySQL read/write split is based on mysql_query_rules and hostgroups. For more info.

Q. Can we use ProxySQL with a single node for the query caching feature? Especially since query cache will be discontinued in MySQL 8?

A. If you configure Query Cache properly, you can cache queries for a single node. 

Q. Must binary logging be enabled for ProxySQL / PXC to work?

A. PXC replicates write-sets. While binary logging is not needed, PXC still needs these write-sets that are generated using binary logging module so PXC can then enable emulation based bin logs for a generation of these write-sets (persistence to disk is not needed). If disk space is not a constraint, we recommend you enable binary logging.

Q. Please define Galera and Percona, as well as the relationship between the two?

A. Galera is replication technology owned and developed by Codership and distributed under GPL license. Percona has adopted the said technology along with its Percona Server for MySQL and build PXC. Percona continues to refresh updates made to Galera and related wsrep-plugin on a regular basis. At the same time, Percona also continues to refresh from Percona-Server for MySQL for related enhancement and bug fixes.

Q. Is the ProxySql Admin tool the script/tool that you mentioned would autodetect your existing PXC or there’s a different script? Trying to know if you need to have the PXC and ProxySQL installed at the same time?

With ProxySQL, do we need to wait for active threads on the PXC to drain before shutting down the PXC?

A. ProxySQL Admin (proxysql-admin) script helps you configure your PXC nodes to ProxySQL database. PXC and ProxySQL should be up and running to initiate proxysql-admin script. For more info.

If you trigger PXC node shutdown proxysql_galera_checker script marks the node as offline in the ProxySQL DB, and new connections aren’t redirected to the offline node.

Q. 1) HAProxy and ProxySQL: which one has the better performance when the number of Clusters is large? Up to 30 Clusters?
2) What´s the better tool to monitoring a large number of clusters and nodes?

A. For PXC, we strongly recommend ProxySQL as it is closely integrated with PXC. HAProxy works with PXC as well, and before ProxySQL we had customers using it. For a quick comparison, you can take a look at following article.

Q. When the PXC settings have a maximum connection how does ProxySQL allow for much more than the standard connections?

A. ProxySQL terminates the connection with a connection timeout error.

FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_insert.lua:61: SQL error, errno = 9001, state = 'HY000': Max connect timeout reached while reaching hostgroup 10 after 10012ms

__________________________________________________________________________________

Once again, thanks for your questions and queries. If you still have more questions or need clarification, you can log them at the percona-xtradb-cluster forum. We would also like to know what else you expect from Percona XtraDB Cluster in upcoming releases.

The post Q&A: “Percona XtraDB Cluster 5.7 and ProxySQL for Your High Availability Needs” Webinar appeared first on Percona Database Performance Blog.

May
02
2018
--

ProxySQL Query Rewrite Use Case

ProxySQL Query Rewrite

ProxySQL Query RewriteIn this blog post, I’m going to revisit the ProxySQL Query Rewrite feature. You may have seen me talking about possible use case scenarios in the past few conferences, but the reason I’m starting with this is that query rewriting was the original intention for building ProxySQL.

Why would you need to rewrite a query?

  • You’ve identified a query that’s causing bottleneck or slowness
  • A special operation requires query routing
  • You cannot modify application code

So here we have a case of a bad query hitting the backend database. You as a DBA have identified the query as causing severe slowdown, which could lead to a site-wide outage. This query needs to be optimized, and you have asked the developer to correct this bad query. Their answer isn’t really what you expected. You can rewrite some queries to have the same data result by choosing a different optimizer path. In cases where an application was written in ORM – such as Hibernate or similar – it is not easy to quickly make a code change.

The query rewrite feature of ProxySQL makes this possible (until the application can be modified).

How do we rewrite a query? There are two ways to accomplish this with ProxySQL.

Query rewrite is just a match_pattern + replace_pattern activity, whereas match_digest is only used for matching a query, not rewriting it. Logically, match_digest serves the same purpose of username, schemaname, proxy_addr, etc. It only matches the query.

These two different mechanisms offers ways to optimize query matching operation efficiently depending on the query type (such as DML operation versus SELECT query). Please note that if your intention is to rewrite queries, the rule must match the original query by using match_pattern. Query rules are processed by using rule_id field and only applied if active = 1.

Here’s how we can demonstrate match_digest in our test lab:

mysql> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+----+-----------+------------+-----------------------------------+
| hg | sum_time  | count_star | digest_text                       |
+----+-----------+------------+-----------------------------------+
| 0  | 243549572 | 85710      | SELECT c FROM sbtest10 WHERE id=? |
| 0  | 146324255 | 42856      | COMMIT                            |
| 0  | 126643488 | 44310      | SELECT c FROM sbtest7 WHERE id=?  |
| 0  | 126517140 | 42927      | BEGIN                             |
| 0  | 123797307 | 43820      | SELECT c FROM sbtest1 WHERE id=?  |
| 0  | 123345775 | 43460      | SELECT c FROM sbtest6 WHERE id=?  |
| 0  | 122121030 | 43010      | SELECT c FROM sbtest9 WHERE id=?  |
| 0  | 121245265 | 42400      | SELECT c FROM sbtest8 WHERE id=?  |
| 0  | 120554811 | 42520      | SELECT c FROM sbtest3 WHERE id=?  |
| 0  | 119244143 | 42070      | SELECT c FROM sbtest5 WHERE id=?  |
+----+-----------+------------+-----------------------------------+
10 rows in set (0.00 sec)
mysql> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest, match_pattern,replace_pattern,apply) VALUES (10,1,'root','SELECT.*WHERE id=?','sbtest2','sbtest10',1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 0    | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2       | sbtest10        | NULL      | 1     |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 593  | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2       | sbtest10        | NULL      | 1     |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)

We can also monitor Query Rules activity live using the ProxyTop utility:

To reset ProxySQL’s statistics for query rules, use following steps:

mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Here’s a match_pattern example:

mysql> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 5;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 0  | 98753983 | 16292      | BEGIN                            |
| 0  | 84613532 | 16232      | COMMIT                           |
| 1  | 49327292 | 16556      | SELECT c FROM sbtest3 WHERE id=? |
| 1  | 49027118 | 16706      | SELECT c FROM sbtest2 WHERE id=? |
| 1  | 48095847 | 16396      | SELECT c FROM sbtest4 WHERE id=? |
+----+----------+------------+----------------------------------+
5 rows in set (0.01 sec)
mysql> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (20,1,'root','DISTINCT(.*)ORDER BY c','DISTINCT1',1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 0    | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2                | sbtest10        | NULL      | 1     |
| 0    | 20      | NULL   | 1      | root     | NULL               | DISTINCT(.*)ORDER BY c | DISTINCT1      | NULL      | 1     |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.01 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 9994 | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2                | sbtest10        | NULL      | 1     |
| 6487 | 20      | NULL   | 1      | root     | NULL               | DISTINCT(.*)ORDER BY c | DISTINCT1      | NULL      | 1     |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql>  LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

The key in query ruling for a rewrite is the order of the apply field:

  • apply = 1 means don’t evaluate any other rules if there’s a match already.
  • apply = 0 means evaluate the next rules in the chain.

As we can see in the test below, all queries matching with rule_id = 10 or rule_id = 20 have hits. In reality, all rules in runtime_mysql_query_rules are active. If we want to disable a rule that is in the mysql_query_rules table, set active = 0:

mysql> update mysql_query_rules set apply = 1 where rule_id in (10);
Query OK, 1 row affected (0.00 sec)
mysql> update mysql_query_rules set apply = 0 where rule_id in (20);
Query OK, 1 row affected (0.00 sec)
mysql>  LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 0    | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2                | sbtest10        | NULL      | 1     |
| 0    | 20      | NULL   | 1      | root     | NULL               | DISTINCT(.*)ORDER BY c | DISTINCT1      | NULL      | 0     |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| hits  | rule_id | digest | active | username | match_digest       | match_pattern          | replace_pattern | flagIN | apply |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| 10195 | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2                | sbtest10        | 0      | 1     |
| 6599  | 20      | NULL   | 1      | root     | NULL               | DISTINCT(.*)ORDER BY c | DISTINCT1      | 0      | 0     |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
2 rows in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| hits  | rule_id | digest | active | username | match_digest       | match_pattern          | replace_pattern | flagIN | apply |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| 20217 | 5       | NULL   | 1      | root     | NULL               | DISTINCT(.*)ORDER BY c | DISTINCT1      | 0      | 1     |
| 27020 | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2                | sbtest10        | 0      | 0     |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
2 rows in set (0.00 sec)
mysql> update mysql_query_rules set active = 0 where rule_id = 5;
Query OK, 1 row affected (0.00 sec)
mysql>  LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 0    | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2       | sbtest10        | NULL      | 0     |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | digest | active | username | match_digest       | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 4224 | 10      | NULL   | 1      | root     | SELECT.*WHERE id=? | sbtest2       | sbtest10        | NULL      | 0     |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.01 sec)

Additionally, ProxySQL can help to identify bad queries. Login to the admin module and follow these steps:

Find the most time-consuming queries:

mysql> SELECT SUM(sum_time), SUM(count_star), digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM(sum_time) DESC LIMIT 3G
*************************** 1. row ***************************
  SUM(sum_time): 95053795
SUM(count_star): 13164
    digest_text: BEGIN
*************************** 2. row ***************************
  SUM(sum_time): 85094367
SUM(count_star): 13130
    digest_text: COMMIT
*************************** 3. row ***************************
  SUM(sum_time): 52110099
SUM(count_star): 13806
    digest_text: SELECT c FROM sbtest3 WHERE id=?
3 rows in set (0.00 sec)

Find highest average execution time:

mysql> SELECT SUM(sum_time), SUM(count_star), SUM(sum_time)/SUM(count_star) avg,  digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM(sum_time)/SUM(count_star) DESC limit 1;
+---------------+-----------------+--------+--------------------------------+
| SUM(sum_time) | SUM(count_star) | avg    | digest_text                    |
+---------------+-----------------+--------+--------------------------------+
| 972162        | 1               | 972162 | CREATE INDEX k_5 ON sbtest5(k) |
+---------------+-----------------+--------+--------------------------------+
1 row in set (0.00 sec)

The above information can also be gathered from information_schema.events_statements_summary_by_digest, but I prefer the ProxySQL admin interface. Also, you can run the slow query log analysis by running a detailed pt-query-digest on your system to identify slow queries. You can also use PMM’s QAN.

Conclusion

I’ve found the best documentation on ProxySQL query rewrite is at IBM’s site, where they explain query rewrite fundamentals with examples. It’s worth a read. I’m not going to get into the details of these techniques here, but if you find more relevant resources, please post them in the comments section.

A few of the possible query optimization techniques:

  • Operation merging
  • Operation movement
  • Predicate translation

At the time of this blog post, ProxySQL has also announced a new fast schema routing algorithm to support thousands of shards.

There may be other cases where you want to divert traffic to another table. Think of a table hitting the maximum integer value, and you want to keep inserts going into a new table while you alter the old one to correct the issue. In the mean time, all selects can still point to the old table to continue operation.

As of MySQL 5.7.6, Oracle also offers query rewrite as a plugin, and you can find the documentation here. The biggest disadvantage of using Oracle’s built-in solution is the rewrite rule sits with the server it is implemented on. That’s where ProxySQL has a bigger advantage: it sits between the application and database server, so the rule applies to the entire topology, not just for a single host.

As you can see, ProxySQL query rewrite is a great way to solve some real operational issues and make you a hero to the team and project. To become a rock star, you might want to consider Percona Training on ProxySQL. The training will provide the knowledge to set up a ProxySQL environment with best practices, understand when and how to change the configuration, and maintain it to ensure increasing your uptime SLAs. Contact us for more details at info@percona.com.

References:

https://www.percona.com/blog/2017/04/10/proxysql-rules-do-i-have-too-many/

http://www.proxysql.com/blog/query-rewrite-with-proxysql-use-case-scenario

https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#query-rewrite

https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005293.html

The post ProxySQL Query Rewrite Use Case appeared first on Percona Database Performance Blog.

Apr
17
2018
--

Webinar Wednesday, April 18, 2018: Percona XtraDB Cluster 5.7 Tutorial

Percona XtraDB Cluster Tutorial

Percona XtraDB Cluster 5.7 TutorialPlease 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.

Percona XtraDB ClusterTibor 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.

Apr
16
2018
--

ProxySQL 1.4.7 and Updated proxysql-admin Tool Now in the Percona Repository

ProxySQL Admin

ProxySQL 1.4.5ProxySQL 1.4.7, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 1.4.7 source and binary packages available at https://percona.com/downloads/proxysql include ProxySQL Admin – a tool, developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 1.4.7 are available as well: https://hub.docker.com/r/percona/proxysql/. You can download the original ProxySQL from https://github.com/sysown/proxysql/releases.

This release fixes the following bugs in ProxySQL Admin:

Usability improvements:

  • Added proxysql-status  tool to dump ProxySQL configuration and statistics.

Bug fixes:

  • PSQLADM-2: ProxySQL galera checker script didn’t check if another instance of itself is already running. While running more then one copy of proxysql_galera_checker in the same runtime environment at the same time is still not supported, the introduced fix is able to prevent duplicate script execution in most cases.
  • PSQLADM-40: ProxySQL scheduler generated a lot of proxysql_galera_checker  and  proxysql_node_monitor processes in case of wrong ProxySQL credentials in proxysql-admin.cnf file.
  • PSQLADM-41: Timeout error handling was improved with clear messages.
  • PSQLADM-42: An inconsistency of the date format in ProxySQL and scripts was fixed.
  • PSQLADM-43: proxysql_galera_checker didn’t take into account the possibility of special characters presence in mysql-monitor_password.
  • PSQLADM-44: proxysql_galera_checker generated unclear errors in the proxysql.log file if wrong credentials where passed.
  • PSQLADM-46: proxysql_node_monitor script incorrectly split the hostname and the port number in URLs containing hyphen character.

ProxySQL is available under OpenSource license GPLv3.

The post ProxySQL 1.4.7 and Updated proxysql-admin Tool Now in the Percona Repository appeared first on Percona Database Performance Blog.

Apr
13
2018
--

This Week in Data with Colin Charles 35: Percona Live 18 final countdown and a roundup of recent news

Colin Charles

Colin CharlesJoin 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

Link List

Upcoming appearances

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.

Apr
11
2018
--

ProxySQL Admin Support for Multiple Clusters

ProxySQL Admin

ProxySQL AdminIn 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.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com