Shulou(Shulou.com)06/01 Report--

ProxySQL read-write separation

Query routing is one of the core features of proxysql.

Read / write separation is probably one of the most commonly used query routes, while the other most commonly used query route is fragmentation.

First, use different ports for read-write separation

If you use an agent like HAProxy, you can configure it to listen on two ports: one as the writer and the second as the reader. People are often asked how to configure proxysql using the same method and query routes based on incoming ports.

The following is an example of how to implement query routing based on incoming ports, running the following command on the Admin of proxysql.

Assume that the master and slave servers have been configured in the correct host group:

MySQL writes in host group 10

MySQL reading in host group 20

If you use Galera or group replication, you can use a similar method. The steps are as follows

1. Configure proxysql to listen on two ports and restart: mysql-interfaces is one of the few variables that cannot be changed in runtime and need to be restarted

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402'

# # save it on disk and restart proxysql

SAVE MYSQL VARIABLES TO DISK

PROXYSQL RESTART

two。 Add routes based on incoming ports

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)

VALUES (1), (2) (2)

LOAD MYSQL QUERY RULES TO RUNTIME

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

It's done! Now all queries to port 6401 will be sent to the MySQL server in host group 10, and all queries to port 6402 will be sent to one of the MySQL servers in host group 20.

Read / write separation restrictions based on incoming ports

In the previous paragraph, I wrote that people often ask how to configure proxysql to use incoming port-based routing.

Although this is sometimes an effective method, in my opinion, it has a big drawback: applications need built-in read / write separation to distinguish between read and write.

(here are the benefits of using ProxySQL balabala....)

But this is not the case in the actual production environment. Typically, the application connection string is configured with only one link (read-write-insensitive), and this link is the MySQL host. If proxysql is used, all traffic on a single port can be accepted and traffic can be analyzed to perform read / write separation based on the query type.

This is very convenient because it does not require any application changes.

However, its main advantage is not the ability to route traffic without changing the application. The main advantage is that DBA now has the tools to control the traffic sent to the database. DBA wakes up in the middle of the night because the DB server is overloaded and does not choose to change the application configuration without a developer, and he now has the option to control traffic (that is, which MySQL servers DBA can send through ProxySQL control statements, very good).

Second, read / write separation based on regular expressions.

In this paragraph, an example of how to use regular expressions to perform read / write separation is shown.

First, you should delete the query rule you created earlier:

DELETE FROM mysql_query_rules

Then, create basic rules for read / write:

UPDATE mysql_users SET default_hostgroup=10; # by default, all goes to HG10

LOAD MYSQL USERS TO RUNTIME

SAVE MYSQL USERS TO DISK; # if you want this change to be permanent

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1 SELECT.*FOR UPDATE$',10,1)

(2, SELECT',20,1, 1)

LOAD MYSQL QUERY RULES TO RUNTIME

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

Now the routing will work as follows:

1. All SELECT FOR UPDATE will be sent to HG10

two。 All other SELECT will be sent to HG20

3. Everything else will be sent to HG10 (default)

Note that I (the author) don't think the above method is a good read-write separation method.

I (the author) often use this example to describe how to configure rules, but it is often misunderstood as a method of configuring read / write separation (as it seems to be the case with online blogs).

Do not use the above example in production (from the author's emphasis, that is, do not simply use the above two rules as the read-write separation configuration of the production environment. On the contrary, it is said here that whoever likes to use it in this way (many blogs on the Internet are also written in this way, using ^ SELECT.*FOR UPDATE$ and ^ SELECT to achieve the separation of reading and writing.)

In the next paragraph, I (the author) will show you a better approach (the right way to use ProxySQL for read-write separation).

Now, let's delete all the rules:

DELETE FROM mysql_query_rules

LOAD MYSQL QUERY RULES TO RUNTIME

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

Third, use regular expressions and abstracts for read / write separation

A configuration process for effectively setting read / write separation is as follows:

1. Configure proxysql to send all traffic to only one MySQL node, the primary node (including write and read)

two。 Find out which SELECT consumes the most performance in stats_mysql_query_digest

3. Determine which statements that consume the most performance should be moved to the read node

4. Configure mysql_query_rules (create rules) to send only the most performance-consuming SELECT statements to read nodes

So the idea is simple: send only the SELECT statements you want to send to the slave library or read node, not any SELECT statements. This is the correct way to use ProxySQL.

Use stats_mysql_query_digest to find the query statements that consume the most performance

Here are some examples of how to identify potential queries that can be sent to the reader.

Because proxysql exports all the metrics in the table, you can create complex queries to collect information.

These results are based on a very busy proxysql instance that has been running for several months and has so far processed about 100 billion queries.

1. Find the first five queries based on the total execution time:

Admin > SELECT digest,SUBSTR (digest_text,0,25), count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5

+-- +

| | digest | SUBSTR (digest_text,0,25) | count_star | sum_time | |

+-- +

| | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 | |

| | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 | |

| | 0x38BE36BDFFDBE638 | SELECT instance.name as | 59343662 | 1096236803754 | |

| | 0xB4233552504E43B8 | SELECT ir.type as type, | 1362897166 | 488971769571 | |

| | 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293 | 475253770301 | |

+-- +

5 rows in set (0.01 sec)

two。 Find the first five queries according to count:

Admin > SELECT digest,SUBSTR (digest_text,0,25), count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5

+-- +

| | digest | SUBSTR (digest_text,0,25) | count_star | sum_time | |

+-- +

| | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 | |

| | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 | |

| | 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609 | |

| | 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867 | |

| | 0xB4233552504E43B8 | SELECT ir.type as type, | 1362906755 | 488974931108 | |

+-- +

5 rows in set (0.00 sec)

3. Find the first five queries based on the maximum execution time:

Admin > SELECT digest,SUBSTR (digest_text,0,25), count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5

+-+

| | digest | SUBSTR (digest_text,0,25) | count_star | sum_time | avg_time | min_time | max_time |

+-+

| | 0x36CE5295726DB5B4 | SELECT COUNT (*) as total | 146390 | 185951894994 | 1270249 | 445 | 237344243 |

| | 0xDA8C56B5644C0822 | SELECT COUNT (*) as total | 44130 | 24842335265 | 562935 | 494 | 231395575 |

| | 0x8C1B0405E1AAB9DB | SELECT COUNT (*) as total | 1194 | 1356742749 | 1136300 | 216677507 |

| | 0x6C03197B4A2C34BE | Select *, DateDiff (Date_ | 4796 | 748804483 | 156131 | 607 | 197881845 |

| | 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid | 592196 | 40209254260 | 67898 | 416 | 118055372 | |

+-+

5 rows in set (0.01 sec)

This particular result shows that the maximum execution time of some queries is very high, while the minimum execution time is very small, and the average speed is quite slow.

For example, the average execution time of a query using the summary 0x36CE5295726DB5B4 is 1.27 seconds, the minimum execution time is 0.4ms, and the maximum execution time is 237.34 seconds. It may be necessary to study why the execution time is uneven.

4. Find the first five queries sorted by total execution time, with a minimum execution time of at least 1 millisecond:

Admin > SELECT digest,SUBSTR (digest_text,0,20), count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5

+-+

| | digest | SUBSTR (digest_text,0,20) | count_star | sum_time | avg_time | min_time | max_time |

+-+

| | 0x9EED412C6E63E477 | SELECT a.id as acco | 961733 | 24115349801 | 25074 | 10994 | 7046628 | |

| | 0x8DDD43A9EA37750D | Select (Coalesce ((| 107069 | 3156179256 | 29477 | 1069 | 24600674 |) |

| | 0x9EED412C6E63E477 | SELECT a.id as acco | 91996 | 1883354396 | 20472 | 10095 | 497877 | |

| | 0x08B23A268C35C08E | SELECT id as reward | 49401 | 244088592 | 4940 | 1237 | 1483791 | |

| | 0x437C846F935344F8 | SELECT Distinct I. e | 163873101 | × × 26 | 1383 | 7905811 | |

+-+

5 rows in set (0.01 sec)

5. Find the first five queries sorted by total execution time, with an average execution time of at least 1 second. Also displays the percentage of total execution time:

Admin > SELECT digest,SUBSTR (digest_text,0,25), count_star,sum_time,sum_time/count_star avg_time, ROUND (sum_time*100.00/ (SELECT SUM (sum_time) FROM stats_mysql_query_digest), 3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5

+-- +

| | digest | SUBSTR (digest_text,0,25) | count_star | sum_time | avg_time | pct |

+-- +

| | 0x36CE5295726DB5B4 | SELECT COUNT (*) as total | 146390 | 185951894994 | 1270249 | 2.11 |

| | 0xD38895B4F4D2A4B3 | SELECT instance.name as | 9783 | 12409642528 | 1268490 | 0.141 | |

| | 0x8C1B0405E1AAB9DB | SELECT COUNT (*) as total | 1194 | 1356742749 | 1136300 | 0.015 | |

+-- +

3 rows in set (0.00 sec)

6. Find the first five queries sorted by total execution time, with an average execution time of at least 15 milliseconds. Also displays the percentage of total execution time:

Admin > SELECT digest,SUBSTR (digest_text,0,25), count_star,sum_time,sum_time/count_star avg_time, ROUND (sum_time*100.00/ (SELECT SUM (sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'), 3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE' SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5

+-- +

| | digest | SUBSTR (digest_text,0,25) | count_star | sum_time | avg_time | pct |

+-- +

| | 0x38BE36BDFFDBE638 | SELECT instance.name as | 59360371 | 1096562204931 | 18472 | 13.006 | |

| | 0x36CE5295726DB5B4 | SELECT COUNT (*) as total | 146390 | 185951894994 | 1270249 | 2.205 | |

| | 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid | 592281 | 40215136635 | 67898 | 0.477 | |

| | 0xDA8C56B5644C0822 | SELECT COUNT (*) as total | 44130 | 24842335265 | 562935 | 0.295 | |

| | 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768 | 24116011513 | 25074 | 0.286 | |

+-- +

5 rows in set (0.00 sec)

Do all of these queries need to be executed on master? If the average execution time of a query is more than 1 second, the answer is likely to be no.

For some applications, even queries with an average execution time of 15ms may become dependent queries.

For example, after checking with the application owner, we can decide that the query that uses the summary 0x38BE36BDFFDBE638 can be sent to the slave library:

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)

VALUES

(0x38BE36BDFFDBE638)

Again, after checking this output:

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, ROUND (sum_time*100.00/ (SELECT SUM (sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'), 3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE' SELECT COUNT%' ORDER BY sum_time DESC

We agree that all queries that begin with SELECT COUNT (*) can be sent to the slave library:

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1 SELECT COUNT 1 (\ *\)', 20 ~ 1)

Finally, load each rule into runtime:

LOAD MYSQL QUERY RULES TO RUNTIME

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

Proxysql is very effective for selective query routing.

While for some applications it is acceptable to send all SELECT to the read / slave library and all others to the write / master library, for many other applications / workloads, the situation is not so simple.

DBA should be able to configure proxysql with complex rules, sending only queries that do not need to be executed on the master server to the slave server without making any changes to the application.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.