Lots of rollbacks when mirroring traffic

Polyglot Projects ProxySQL
1

I am trying to mirror traffic between two RDS Aurora clusters with ProxySQL. I think it’s one of the most basic set ups.

ProxySQL Admin> select rule_id, active, destination_hostgroup, mirror_hostgroup, apply from runtime_mysql_query_rules;
+---------+--------+-----------------------+------------------+-------+
| rule_id | active | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+-----------------------+------------------+-------+
| 1       | 1      | 10                    | 20               | 1     |
+---------+--------+-----------------------+------------------+-------+
1 row in set (0.00 sec)

ProxySQL Admin>

It looks like it is working however, the digests worry me.

ProxySQL Admin> select hostgroup, digest, username, count_star, min_time, max_time from stats_mysql_query_digest;
+-----------+--------------------+----------+------------+----------+----------+
| hostgroup | digest             | username | count_star | min_time | max_time |
+-----------+--------------------+----------+------------+----------+----------+
******
| 10        | 0x42384B68212C12B7 | user   | 4015       | 481      | 620      |
******
+-----------+--------------------+----------+------------+----------+----------+
21 rows in set (0.00 sec)

All digests match up with a corresponding entry for hostgroup 10 (main cluster) and hostgroup 20 (mirrored cluster) except the digest above.

The digest text for this digest is ROLLBACK.

Should I be concerned? Is there anything I can do to work out what is happening?

2

As I understand your post, all queries are mirrored EXCEPT this ROLLBACK? Is there anything in the proxysql logs around this?

3

That is correct.

Looking at the logs two things stand out

2024-11-20 16:12:08 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (20,cluster_url,3306,16567): 1451, Cannot delete or update a parent row: a foreign key constraint fails (prod.locations, CONSTRAINT locations_ibfk_1 FOREIGN KEY (building_ref) REFERENCES building (uuid))

This is somewhat expected as the 20 hostgroup (mirrored) database didn’t start off as an exact replica of the main 10 hostgroup it was about 5 minutes behind so some records would be missing.

If the mirroring fails would that impact the original mysql query?

Also there are a lot of

2024-11-20 16:13:57 MySQL_Thread.cpp:4116:ProcessAllSessions_Healthy0(): [WARNING] Closing unhealthy client connection 10.124.85.94:43562

Again, is this some that should cause alarm?

There is nothing else standing out to me

4

That’s what I would expect. If the mirror receives any error, it simply aborts and does not send any remaining mirrored SQL. You get similar behavior in clustered solutions like GR and PXC. If one node has different data, or fails to apply a transaction, it self-aborts and forces a rebuild from another node. This is by design to keep the data consistent between nodes.

Unhealthy connection can be the result of mysql wait_timeout, or other network-related problem. I would not be focused on them.

5

Okay - so this would be a issue with the database and nothing that can be configured on ProxySQL?

Sorry, I’m not sure what you mean here.

Could the fact the mirror failed, cause the main database to rollback?

6

Your original post indicated that the ROLLBACK was successful to hostgroup 10 (main) but not to hg 20 (mirror). I would expect the ROLLBACK to not be mirrored to 20 if a previous query within the same transaction failed.

Are you saying that the ROLLBACK sent to 10 was a result of the failure in 20? That’s not how mirroring is supposed to work, but there could be a bug. ProxySQL is supposed to ignore results from the mirror.

7

I suppose I’m trying to work out what happening.

We have a very simple mirroring which is everything for destination_hostgroup 10 should be mirrored to host_group 20.

If anything fails on the mirror host group we want nothing to happen, just continue processing further MySQL queries. And under no circumstance should a query on mirroring fail result in a change in the destination_hostgroup.

This sounds like it should be how it works.

I am trying to work out if we are ready to go to production with ProxySQL but the number of rollbacks in the digest has me worried.

Is there much configuration or settings that we could have missed.

I just added hosts groups, mysql users and the one query rule.

8

Woah woah woah. Full Stop, right now. This is NOT how you setup replication in MySQL. Absolutely not. ProxySQL mirroring is NOT for creating a replicated environment.

You should be using MySQL’s native/built-in replication system that has existed since MySQL 3.23.

ProxySQL’s mirroring is a feature that allows you to test changes on a separate system, while not needing to modify queries or change application behavior. It is a feature for testing things, not for creating production-style replication architecture.

9

Just updated it, was a typo, I’m doing mirroring and not replication and am aware of the differences.

Thanks for pointing it out. We will be using it to test our production traffic against a new cluster.

10

Sorry to be back, but I just want to make sure I’m not doing anything stupid.

We are looking to mirror our prod traffic against a new cluster to monitor performance of the new cluster.

Does the config I put in the first post look correct, and this should have no impact on the traffic to the prod cluster. This will continue to be routed as is, it’s just proxysql will also send it to the mirror cluster but do nothing if it fails or if the clusters get out of sync? Just continue sending all requests received two both clusters?

11

The config looks correct to me. And it should have no impact on prod if there’s an issue on the mirror. You can test that by simply shutting down the mirror server, or putting the mirror in read-only. You’ll probably see error messages in proxysql logs, but those should not affect what is being sent to the primary destination hostgroup.

1 Like