The latest from the MySQL community

Query routing is one of the core features of ProxySQL.
Read/write split is perhaps one of the most common query routing use, while the other most commonly used query routing implementation is for sharding.
In this blog post I will show some example on how to implement read/write split.

Read/write split using different ports

If you are using a proxy like HAProxy you can configure it to listen onto two ports: one as an endpoint for writes, while the second port as an endpoint for reads.
People has often asked how to configure ProxySQL using the same approach, and routing queries based on incoming port.

Here is an example on how to implement query routing based on incoming port, running the follow on ProxySQL's Admin. I will assume you already have master and slaves configured in the right hostgroups: MySQL writer in hostgroup 10, and MySQL readers in hostgroup 20. A similar approach will apply if you are …

Query routing is one of the core features of ProxySQL.
Read/write split is perhaps one of the most common query routing use, while the other most commonly used query routing implementation is for sharding.
In this blog post I will show some example on how to implement read/write split.

Read/write split using different ports

If you are using a proxy like HAProxy you can configure it to listen onto two ports: one as an endpoint for writes, while the second port as an endpoint for reads.
People has often asked how to configure ProxySQL using the same approach, and routing queries based on incoming port.

Here is an example on how to implement query routing based on incoming port, running the follow on ProxySQL's Admin. I will assume you already have master and slaves configured in the right hostgroups: MySQL writer in hostgroup 10, and MySQL readers in hostgroup 20. A similar approach will apply if you are …

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

Apply Test

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

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

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

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

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

Overview

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

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

insert into mysql_query_rules …

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index creation, …

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index …

Error messageError 1045 (28000): Access denied for user ‘user’@’host’ (using password: YES)Debugging Summary

• Check for typo error: username or password. 
• Check the host name and compare it with mysql.user table host name. 
• Check user exists or not. 
• Check whether host contains IP address or host name.

There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue. 

Possible reasons:

• Case 1: …

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

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

ERROR 1215 (HY000): Cannot add foreign key constraint

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

Overview

In this post I will explain how to transfer a partitioned table using transportable tablespaces on MySQL 5.6. Note that the operation is not officially supported until MySQL 5.7, as the discard tablespace operation will fail if it is run against a partitioned table on MySQL 5.6.

The workaround is transferring each individual partition as if it was a standalone table. Continue reading to learn more about the detailed procedure.

Steps

1. Create the same table structure on the destination server:

[root@mysqlsandbox ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-80.0 Percona Server (GPL), Release 80.0, Revision f113994f31

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. …

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

The Percona Monitoring and Management MariaDB Dashboard builds on the efforts of the MariaDB development team to instrument the Aria Storage Engine Status Variables related to Aria Pagecache and Aria Transaction Log activity, the tracking of Index Condition Pushdown (ICP), InnoDB Online DDL when using ALTER TABLE ... ALGORITHM=INPLACE, InnoDB Deadlocks …