ProxySQL with AWS AuroraIn this post, we'll look at how to implement ProxySQL with AWS Aurora. Recently, there have been a few discussions and customer requests that focused on AWS Aurora and how to make the various architectures and solutions more flexible. Flexible how, you may ask? Well, there are the usual expectations:

  • How do you improve resource utilization?
  • How I can filter (or block) things?
  • Can I shard with Aurora?
  • What is the best way to implement query caching?
  • … and more.

The inclusion of ProxySQL solves many of the points above. We in Consulting design the solutions for our customers by applying the different functionalities to better match customers needs. Whenever we deal with Aurora, we've had to exclude ProxySQL because of some limitations in the software. Now however, ProxySQL 2.0 supports Aurora, and it does it amazingly. This article will show you how to implement ProxySQL with AWS Aurora. The the next article Leveraging AWS Aurora performance will show you WHY.

The Problem

ProxySQL has two different ways to deal with backend servers. One is using replication mechanisms, like standard Async replication and Group Replication. The other is to use the scheduler, as in the case of Percona XtraDB Cluster, MariaDB Cluster, etc. While we can use the scheduler as a solution for Aurora, it is not as immediate and well-integrated as the embedded support for replication, given that we normally opted not to use it in this specific case (Aurora). But what WAS the problem with Aurora? An Aurora cluster bases its definition of Writer vs. Readers using the innodb_read_only variable. So, where is the problem? Well actually no problem at all, just that ProxySQL up to version 2 for replication only supported the generic variable READ_ONLY. As such, it was not able to correctly identify the Writer/Readers set.

The Solution

In October 2017, this issue was opened (https://github.com/sysown/proxysql/issues/1195 )and the result was, as usual, a quite simple and flexible solution.

“Brainstorming, a possible solution could be to add another column in mysql_replication_hostgroups to specify what needs to be checked, either read_only or innodb_read_only, or even super_read_only”

This lead to the ProxySQL team delivering (“commit fe2f16d6df15252f0107a6a224dad7b1efdb13f6”):

Added support for innodb_read_only and super_read_only 

MYHGM_MYSQL_REPLICATION_HOSTGROUPS "CREATE TABLE mysql_replication_hostgroups 
(writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY , 
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0) , 
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only' , 
comment VARCHAR NOT NULL DEFAULT '' , UNIQUE (reader_hostgroup))"

Which in short means they added a new column to the mysql_replication_hostgroup table. ProxySQL continues to behave exactly the same and manages the servers and the replication groups as usual. No need for scripts or other crazy stuff.

Implementation

Here we are, the HOW TO part. The first thing to keep in mind is that when you implement a new Aurora cluster, you should always consider having at least two instances in the same AZ and another instance in a remote AZ. To implement ProxySQL, you should refer directly to


the instances, NOT to the cluster entry-point. To be clear, you must take this for each instance:

The information is available in the web-admin interface, under the instance or using the command: 

aws rds describe-db-instances

And filter the result for:

"Endpoint": {
                "Port": 3306,
                "Address": "proxysqltestdb.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com"
            },

To run ProxySQL with RDS in general, you need to install it on an intermediate server or on the application box. Once you decide which one fits your setup better, you must download or git clone ProxySQL v2.0+. DO NOT use v1.4.x, as it does not contain these new features and will not work as expected. Once you have all the Aurora instances up, it is time to configure ProxySQL.

Below is an example of all the commands used during the installation:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25





GRANT usage, replication client ON *.* TO monitor@'%' IDENTIFIED BY 'monitor';
 
DELETE FROM mysql_servers WHERE hostgroup_id IN (70,71);
DELETE FROM mysql_replication_hostgroups WHERE writer_hostgroup=70;
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('proxysqltestdb.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com',70,3306,1000,2000);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('proxysqltestdb.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com',71,3306,1000,2000);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('proxysqltestdb2.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com',71,3306,1000,2000);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('proxysqltestdb-eu-central-1b.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com',71,3306,1,2000);
 
INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment,check_type) VALUES (70,71,'aws-aurora','innodb_read_only');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
 
DELETE FROM mysql_query_rules WHERE rule_id IN (50,51,52);
INSERT INTO mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(50,6033,'m8_test',70,0,3,'.',1);
INSERT INTO mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(51,6033,'m8_test',70,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(52,6033,'m8_test',71,1,3,'^SELECT.*$',1);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
 
DELETE FROM mysql_users WHERE username='m8_test';
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) VALUES ('m8_test','test',1,70,'mysql',1);
LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;
 
UPDATE global_variables SET variable_value="67108864" WHERE variable_name='mysql-max_allowed_packet';
UPDATE global_variables SET Variable_Value=0  WHERE Variable_name='mysql-hostgroup_manager_verbose'; 
LOAD mysql VARIABLES TO run;save mysql VARIABLES TO disk;
















 

The above will give you a ready-to-go ProxySQL setup that supports Aurora cluster, performing all the usual operations ProxySQL does, including proper W/R split and more for a user named 'm8_test'. The key is in passing the value 'innodb_read_only' for the column check_type in the table mysql_replication_hostgroups. To check the status of your ProxySQL, you can use this command (which gives you a snapshot of what is going to happen):


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32





 watch -n 1 'mysql --defaults-file=~/.my.cnf -h 127.0.0.1 -P 6032 -t -e "select b.weight, c.* from stats_mysql_connection_pool c left JOIN runtime_mysql_servers b ON  c.hostgroup=b.hostgroup_id and c.srv_host=b.hostname and c.srv_port = b.port where hostgroup in( 50,52,70,71) order by hostgroup,srv_host desc;" -e " select srv_host,command,avg(time_ms), count(ThreadID) from stats_mysql_processlist group by srv_host,command;" -e "select * from stats_mysql_users;";mysql  --defaults-file=~/.my.cnf -h 127.0.0.1 -P 6032  -t -e "select * from stats_mysql_global "|egrep -i  "(mirror|memory|stmt|processor)"' 
+--------+-----------+--------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| weight | hostgroup | srv_host                                                                 | srv_port | STATUS | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+--------+-----------+--------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1000   | 70        | proxysqltestdb.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com               | 3306     | ONLINE | 0        | 0        | 0         | 0       | 0           | 0       | 0                 | 0               | 0               | 5491       |
| 1000   | 71        | proxysqltestdb2.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com              | 3306     | ONLINE | 0        | 5        | 5         | 0       | 5           | 73      | 0                 | 5483            | 28442           | 881        | 
| 1000   | 71        | proxysqltestdb.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com               | 3306     | ONLINE | 0        | 5        | 5         | 0       | 5           | 82      | 0                 | 6203            | 32217           | 5491       | 
| 1     | 71        | proxysqltestdb-eu-central-1b.c7wzm8xxmrze.eu-central-1.rds.amazonaws.com | 3306     | ONLINE | 0        | 0        | 0         | 0       | 0           | 0       | 0                 | 0               | 0               | 1593       |
+--------+-----------+--------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
+----------+----------------------+--------------------------+
| username | frontend_connections | frontend_max_connections |
+----------+----------------------+--------------------------+
| m8_test  | 0                    | 10000                    |
+----------+----------------------+--------------------------+
| Query_Processor_time_nsec    | 0              |
| Com_backend_stmt_prepare     | 0              |
| Com_backend_stmt_execute     | 0              |
| Com_backend_stmt_close       | 0              |
| Com_frontend_stmt_prepare    | 0              |
| Com_frontend_stmt_execute    | 0              |
| Com_frontend_stmt_close      | 0              |
| Mirror_concurrency           | 0              |
| Mirror_queue_length          | 0              |
| SQLite3_memory_bytes         | 2652288        |
| ConnPool_memory_bytes        | 712720         |
| Stmt_Client_Active_Total     | 0              |
| Stmt_Client_Active_Unique    | 0              |
| Stmt_Server_Active_Total     | 0              |
| Stmt_Server_Active_Unique    | 0              |
| Stmt_Max_Stmt_id             | 1              |
| Stmt_Cached                  | 0              |
| Query_Cache_Memory_bytes     | 0              |
















 

At this point, you can connect your application and see how ProxySQL allows you to perform much better than the native cluster entry point. This will be expanded in the next article: Leverage AWS Aurora performance.

Conclusions

I had my first issue with the native Aurora connector a long time ago, but I had nothing to replace it. ProxySQL is a very good alternative to standard cluster access, with more options/controls and it also allows us to perform close-to-application caching, which is much more efficient than the remote MySQL one (http://www.proxysql.com/blog/scaling-with-proxysql-query-cache). In the next article I will illustrate how, in a simple setup, ProxySQL can help in achieving better results than using the default Aurora cluster endpoint.

Or Fun in using MySQL8 with ProxySQL and MysqlJ connector

 

I am recently working on testing MySQL8 and try the several solution attach to it,like ProxySQL but not only.

After I had setup the set of servers, and configured ProxySQL to redirect the incoming connection from my user m8_test to my MySQL8 setup, I had turn on my Java test application ... and with my surprise I received an error:

Caused by:

1

com.mysql.cj.core.exceptions.CJException: Unknown system variable 'query_cache_size'

 

Well ok MySQL8 doesn't have Query cache, but why I got this error?

I did point the application to MySQL8 directly and it worked fine.

 

Just to be sure this is something restricted to the Java connector, I did a test with a perl, and I was able to access and write my MySQL8 servers from ProxySQL without problem.

 

So the issue is restricted to MySQLJ and ProxySQL. Not the first time I have issue with that connector, and not the first time I see ProxySQL not 100% compatible. But this was weird.

I downloaded the latest MYSQLJ connector and put the source in my development environment.

 

Then I started to dig in to the issue.

MySQL Connector send a "SHOW VARIABLES" and then parse the result to "configure" the connector accordingly.

In the class MysqlaSession.loadServerVariables() is the method that will decide what variables should be included and what not.

The process is a bit rude and basic, with a series of IF condition checking the Server Version.

Finally at line 1044 I found the why the connector was failing:

1
2
3
4
5
6
7
8
9
10
11
12
13

if (versionMeetsMinimum(8, 0, 3)) {
 
queryBuf.append(", @@have_query_cache AS have_query_cache");
 
} else {
 
queryBuf.append(", @@query_cache_size AS query_cache_size");
 
queryBuf.append(", @@query_cache_type AS query_cache_type");
 
}


So if Version is at least 8.0.3 check for the variable have_query_cache, otherwise read query_cache_size and type.

Here we go, ProxySQL by default in version 1.4.6 declare itself as:

1

Server version:	5.5.30 (ProxySQL)

 

One of the good things in ProxySQL is that most of the important settings can be dynamically change, including the Server Version.

This is it, ProxySQL can impersonate whichever MySQL just modifying the Server Version variable.

 

Given that I did:

1
2
3
4
5

update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;


At this point I had run my java app again, and all was running fine.

While there I tested several different scenarion and mostly worked as expected.

 

But once I set ProxySQL to impersonate a MySQL 5.5, yes right as 5.5 not as 5.5.x

Just to see if the connector was reading the version correctly. And with no big surprise... it was not.

 

Why? Bcause MySQL Connector once opened the channel with the server, reads some of the parameters directly from the connection, one of them is the Server Version.

The Server Version is parse in the class ServerVersion.parseVersion() method, and here the connector expect to find the server version following the standard major.sub.subminor (5.5.30) if this is not declare exactly like that, then the connector will just set the Server Version to 0.0.0. With the side effect that nothing will work correctly afterwards.

 

Conclusion

This short blog post was to share a simple issue I had and his resolution using the flexibility in ProxySQL to modify the declared MySQL Server Version.

Still, attention must be made given the MySQLJ is not flexible and standard (major.sub.subminor) must be used.

ProxySQL_firewall_smallNot long ago we had an internal discussion about security and how to enforce a stricter set of rules to prevent malicious acts, and block other undesired queries.

ProxySQL comes up as a possible tool that could help us in achieving what we were looking for. Last year I had written how to use ProxySQL to stop a single query.

 

That approach may be good for few queries and as temporary solution. But what can we do when we really want to use ProxySQL as an SQL-based firewall? And more importantly, how to do it right?

 

First of all, let us define what “right” can be in this context.

For right I mean an approach that will allow us to have rules matching as specific as possible, and impacting the production system as least as possible.

To make this clearer, let us assume I have 3 schemas:

Shakila

World

Windmills

 

I want to have my firewall block/allow SQL access independently by each schema, user, eventually by source, and so on.

There are a few case where this is not realistic, like in SaaS setups where each schema represents a customer. In this case, the application will have exactly the same kind of SQL just pointing to different schemas depending the customer.

Using ProxySQL

Anyhow… ProxySQL allows you to manage query firewalling in a very simple and efficient way using the query rules.

In the mysql_query_rules table we can define a lot of important things and one of this is, to set our SQL firewall.

 

How?

Let us take a look to the mysql_query_rules table:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT,
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT,
    replace_pattern VARCHAR,
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED,
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED,
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)
 

 

We can define rules around almost everything: connection source, port, destination IP/Port, user, schema, SQL text, and any combination of them.

 

Given we may have a quite large set of queries to manage, I prefer to logically create “areas” around which add the rules to manage SQL access.

For instance, I may decide to allow a specific set of SELECTs to my schema windmills but nothing more.

Given that, I allocate the set of rule ids from 100 to 1100 to my schema, and add my rules in 3 groups.

  1. The exception that will bypass the firewall
  2. The blocking rule(s) [the firewall]
  3. The managing rules (post processing like sharding and so on)

There is a simple thing to keep in mind when you design rules for firewalling.
Do you need post processing of the query or not?
In the case you DON’T need post processing, the rule can simply apply and exit the QueryProcessor. That is probably the most common scenario and read/write split can be define in the exception rules assigning to the rule the desired HostGroup.

While if you need post-processing the rule MUST have apply=0 and the FLAGOUT must be define. That will allow you to have additional actions once the query is beyond the firewall.

An example is in case of sharding, where you need to process the sharding key/comment or whatever.

 

I will use the simple Firewall scenario, given this is the topic of the current article.

The rules

Let us start with the easy one, set 2, the blocking rule:

1
2
3

insert into mysql_query_rules (rule_id,username,schemaname,match_digest,error_msg,active,apply) 
values(1000,'pxc_test','windmills','.',
'You cannot pass.....I am a servant of the Secret Fire, wielder of the flame of Anor,. You cannot pass.',1, 1);

 

In this query rule, I had defined the following:

  • User connecting
  • Schema name
  • Any query
  • Message to report
  • Rule_id 

That rule will block ANY query that will try to access the schema windmills from application user pxc_test.

 

Now in set 1, I will add all the rules I want let pass, will report here one only but all can be found in github here.

1

insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,
schemaname,active,retries,apply,flagout,match_digest) 
values(101,6033,'pxc_test',52,'windmills',1,3,1,1000,
'SELECT wmillAUTOINC\.id,wmillAUTOINC\.millid,wmillAUTOINC\.location 
FROM wmillAUTOINC WHERE wmillAUTOINC\.millid=.* and wmillAUTOINC\.active=.*');

 

That is quite simple and straightforward but there is an important element that you must note.
In this rule, apply must have value of =1 always, to allow the query rule to bypass without further delay the firewall.

(Side Note:  if you need post-processing, the flagout needs to have a value (like flagout=1000) and apply must be =0. That will allow the query to jump to the set 3, the managing rules.)

 

This is it, ProxySQL will go to the managing rules as soon as it finds a matching rule that will allow the application to access my database/schema, or it will exit if apply=1.

A graph can help to understand better:

Screen Shot 2018-01-03 at 1.37.04 AM

 

 

Rule set 3 will have the standard query rules to manage what to do with the incoming connection like sharding or redirecting SELECT FOR UPDATE and so on:

1

insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,
destination_hostgroup,active,retries,match_digest,apply,flagin) 
values(1040,6033,'windmills','pxc_test',50,1,3,'^SELECT.*FOR UPDATE',1,1000);

 

Please note the presence of the flagin which matches the flagout above.

 

Setting rules, sometimes thousands of them can be very confusing. It is very important to plan correctly what should be in as excluding rule and what not. Do not rush, take your time and identify the queries you need to manage carefully.

 

Once more proxySQL can help us. Querying the table stats_mysql_query_digest will tell us exactly what queries were sent to ProxySQL, ie:

1

(admin@127.0.0.1) [main]>select hostgroup,schemaname,digest,digest_text,count_star 
from stats_mysql_query_digest where schemaname='windmills' order by count_star desc;

 

 

The above query shows us all the queries hitting the windmills schema. From there we can decide which queries we want to pass and which not.

1
2
3
4
5
6
7
8
9

 
>select hostgroup,schemaname,digest,digest_text,count_star 
from stats_mysql_query_digest where schemaname='windmills' order by count_star desc  limit 1\G
 
*************************** 1. row ***************************
hostgroup: 50
schemaname: windmills
digest: 0x18CA8FF2C9C53276
digest_text: SHOW GLOBAL STATUS
count_star: 141

 

Once we have our set done (check on github for an example), we are ready to check how our firewall works.


By default, I suggest you to keep all the exceptions (in set 1) with active=0, just to test the firewall.

 

For instance, my application will generate the following exception:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
 You cannot pass.....I am a servant of the Secret Fire, wielder of the flame of Anor,. You cannot pass.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
	at net.tc.stresstool.statistics.providers.MySQLStatus.getStatus(MySQLStatus.java:48)
	at net.tc.stresstool.statistics.providers.MySQLSuper.collectStatistics(MySQLSuper.java:92)
	at net.tc.stresstool.statistics.StatCollector.collectStatistics(StatCollector.java:258)
	at net.tc.stresstool.StressTool.<init>(StressTool.java:198)
	at net.tc.stresstool.StressTool.main(StressTool.java:282)
 

 

Activating the rules, will instead allow your application to work as usual.

 

What the impact will be?

First,let us define the baseline, running the application without any rule blocking but only the r/w split (set 3).

 

Queries/sec global

queries_routed_baseline

Using two application servers:

Server A: Total Execution time = 213

Server B: Total Execution time = 209

 

 

Queries/sec per server

queries_routed_baseline_per_server

As we can see queries are almost equally distributed.

 

QueryProcessor time taken/Query processed total

QP_cost_baseline

 

All queries are processed by QueryProcessor in ~148ms AVG (total)

QueryProcessor efficiency per query.

QP_efficency_baseline

 

Single query cost is in nanoseconds avg 10 us.

 

Use match_digest

Once defined the baseline we can go ahead and activate all the rules using the match_digest.
Run the same tests again and… :

 

Queries/sec global

queries_routed_match

 

Using two application servers:

Server A: Total Execution time = 207

Server B: Total Execution time = 204

 

First of all, we can notice that the execution time did not increase. This is mainly because we have CPU cycles to use in the ProxySQL box.

 

Queries/sec per server

queries_routed_match_per_server

 

 

Here we have a bit of unbalance. We will investigate that in a separate session, but all in all, time/effort sounds ok.

 

QueryProcessor time taken/Query processed total

QP_cost_match

Here we have the first thing to notice. Comparing this to the baseline we had defined, we can see that using the rules as match_digest had significantly increase the execution time to 458ms.

 

QueryProcessor efficiency per query.

QP_efficency_match

 

Notice that also if we are in the range of nanoseconds, the cost of processing the query is now 3 times that of the baseline. Not too much but if you add a stone to another stone and another stone and another stone … you end up building a huge wall.

 

So, what to do? Up to now we had seen that managing the firewall with ProxySQL is easy and it can be set at very detailed level, but the cost may not be what we expect it to be.

 

What can be done? Use DIGEST instead.

The secret is to not use match_digest, which implies interpretation of the string, but to use the DIGEST of the query, which is calculated ahead and remains constant for that query.

 

Let us see what will be if we run the same load using DIGEST in the MYSQL_QUERY_RULES table.

 

Queries/sec global

queries_routed_digest

Using two application servers:

Server A: Total Execution time = 213

Server B: Total Execution time = 209

No, this is  not an issue with cut and paste. I had more or less the same execution time as if without rules, at the seconds; different millisecond though.

 

Queries/sec per server

queries_routed_digest_per_server

Again, here some unbalance, but minor thing.

 

QueryProcessor time taken/Query processed total

QP_cost_digest

 

And we go down as we should to 61ms as execution of all queries. Note that we improve the efficiency of the Query Processor from 148ms AVG to 61ms AVG.

Why? Because our rules using the DIGEST also have the instructions for read/write split, so request can exit the Query Processor with all the information required at this stage; more efficient.

 

QueryProcessor efficiency per query.

QP_efficency_digest

Finally using the DIGEST, the cost for query drops to 4us which is … LOW!

 

That’s it! ProxySQL using the DIGEST field from mysql_query_rules, will perform much better, given that it will not need to analyze the whole SQL string with regular expression, but it will just match the DIGEST.

Conclusions

ProxySQL can be effectively used as an SQL firewall, but some best practices should be taken in to consideration.

First of all, try to use specific rules, and be specific on what should be filtered/allowed. Use filter by schema or user or IP/port or combination of them.

Always try to avoid match_digest and use digest instead. That will allow ProxySQL to bypass the call to the regularExp lib and it will be by far more efficient.

Use stats_mysql_query_digest to identify the correct DIGEST.

 

Regarding this, it would be nice to have an GUI interface that will allow us to manage these rules; that would make the usage of the ProxySQL much easier, and the maintenance/creation of rule_chains friendlier.

More Articles …

  1. ProxySQL PXC Single Writer Mode and auto failover, re-bootstrap
  2. How ProxySQL deal with schema (and schemaname)
  3. How ProxySQL deal with schema (and schemaname) Long story
  4. Sweet and sour can become bitter
  5. Group-Replication, sweet & sour
  6. ProxySQL and Mirroring what about it?
  7. InnoDB Page Merging and Page Splitting
  8. Setup ProxySQL as High Available (and not a SPOF)
  9. ProxySQL – Percona Cluster (Galera) integration
  10. ProxySQL and MHA integration

Path

  2. Home

Latest conferences

We have 3816 guests and one member online

login

Remember Me