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

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) …

The MySQL Development Team is happy to announce the first GA release of InnoDB Cluster–our integrated, native, full stack HA solution for MySQL. You can see highlights of the changes and improvements made since the RC release here, and you can download the GA packages from our MySQL APT (Ubuntu, Debian) and YUM (Redhat, OEL, Fedora) repositories or from dev.mysql.com.…

We carefully listened to the feedback we got from the last release candidate and incorporated some of the suggested changes. On top of that, we have fixed several bugs to reach the desired maturity state.

Here are the highlights of this release!…

MySQL 8.0.1 introduces two new features which allow you to better manage situations where you have tables with hot row contention.  This issue frequently presents itself in scenarios such as worker threads all accessing the same tables trying to find new work, and ecommerce websites trying to keep accurate inventory counts.…

[…] Replication filters per channel is now implemented in MySQL starting from version 8.0.1. Check this post for more […]

LikeLike

MySQL 8.0 introduces Common Table Expressions (CTE). My colleague Guilhem has written several blog posts on how to use CTEs , and you can also read about it in the MySQL 8.0 Reference Manual. In this blog post, I will focus on how using a CTE instead of a view or a derived table can improve performance.…

[…] year ago, I wrote a post about replication filters in MySQL Multi Source Replication and whether we can set the filters per […]

LikeLike

[…] year ago, I wrote a post about replication filters in MySQL Multi Source Replication and whether we can set the filters per […]

LikeLike