Move to Italy



Support Wikipedia

Login Form

Some reasoning around mirroring in MySQL
Written by Marco Tusa   

mirror1In the last year we had heard a lot of talking around ProxySQL and mirroring features. Frankly speaking the need to have a “mirroring” tool is something that comes up over and over. Question is “why so”?

The answer is that Mirroring is not an easy task, aside simplistic approaches, if we think carefully about it, we can easily realize that mirroring is not just pushing data from A to B.

When you look at yourself in in the mirror your image is not partial, you don’t miss an eye or part of the nose. What you see is the reflection of the whole face. If you smile or move your head, you will see the same behavior in the mirror, and if not, you will look for a poltergeist.

This is it, mirroring is not only reporting the image back, but also the movements, in some way the behavior.

If you smile a specific set of muscle will contract and other release to allow you that specific expression. A mirror will not do that, but it will mimic your face reflecting the light and showing what your body is doing in full.

Well that in Database transactions is not going to work so easily. We cannot easily mirror server A on server B, unless we will successful replicate B as a copy of A (easy?), and B also receiving exactly the same load in the same moment/time. Also if we succeed there initially, there are various non deterministic factor that will bring the two to diverge in the behavior, like network package transmission.

In datastore we have some level of Mirroring, but that is mainly passive mirroring. A volume is created and maintained as an exact copy of another, all independent behavior is suppressed, data is pass from A to B as it is, reads may happen.

In short, talking of mirroring for database behavior in real production environments, it is not only misleading but conceptually wrong, because we will always have a broken mirror image, like a face reflected without eyes and moving after or before we do. Who wants to have a mirror drive containing different data of its master?

But here the great things. Because the point is that we may not need mirroring at all. We may need something else, something actually more realistic and useful.

In the last years talking with customer and colleagues, what I heard over and over was the need to have of a tool that can allow to replicate, mimic the load of server A on server B.

This is slightly different from a Mirroring functionality, and another good news is that, this is actually achievable. Probably no tool will be able to replicate with 100% accuracy the load performed on a server to another but it would “good enough” to cover most of the cases.

What at then end we are talking about is to use a traffic/Load dispatcher. The tool does not pretend to mirror or mimic the original behavior, but it will use the original load to replicate it on the destination.

Just to mention Percona Playback was one of the tools that was going in that direction.

Anyhow, in my opinion there are 3 main cases where this “good enough” make sense:

  • Functional tests (Like to check if a query behave the same on server A and B)
  • Load comparison tests (to identify which server between A and B is managing the traffic better)
  • Empiric scalability test (Using traffic from A to multiply it and progressively Load B until saturation)

Also in this “simplified” approach we still have a lot to consider and be concerned.

For instance, in our point 1, also for a simple SELECT, we will need to be sure that collecting or redirecting the SELECT itself will not modify the behavior in A.

Secondly, we will receive a dataset from A, the application behavior will, eventually, adjust in regards to the received data.

But what about B? If the server B will send back the data set faster, should it wait for A? Or if for any reason (optimizer) we receive a different data set? Should we consider the differentiation as optimization or an error?

The easiest solution is to ignore B as whole but that may raise some consistency concern when performing full CRUD operations.

Another issue, and this is more related to point 2-3, what about transactions and data consistency?

Let say I have a transaction applying N different queries (again full CRUD model), should we ensure to have the same execution order in B as in A?

Should we respect the transaction isolation, the data validation against UK/FK, rollbacks and commit?

In my opinion the answer is YES, at least for whatever may change the data, we must ensure consistency.

Select can be “skip” but update/delete/insert cannot, and not to be too strict but for simple basic data consistency.

I think that at this point I had made my opinion clear.

Thanks to everybody.

Last Updated on Tuesday, 23 May 2017 13:12
ProxySQL and Mirroring what about it?
Written by Marco Tusa   



1. Overview

I love ProxySQL, I think it is a great component for expanding architecture flexibility and HA, but not all what shine is gold.

Let me make clear that I only want to set the expectations right, and avoid to sell carbon for gold. Carbon has it's own use, gold has another. 


First of all let me clarify what is mirroring for me (and hope most of you).

Then we need to cover the basic of how ProxySQL manage the (I cannot say mirroring) traffic dispatch.

ProxySQL receive a connection from the application, and through it we can have a simple SELECT or a more complex transaction. ProxySQL will get each query, pass it to the QueryProcessor, process the query, identify if the query is mirrored, duplicate the whole mysql session ProxySQL internal object and associate it to a mirror queue, which refer to a mirror threads pool.

If the pool is free (has an available active slot in the concurrent active threads set) then the query is processed right away, if not it will stay in the queue. If the queue is full, the query is lost.

Whatever is returned from the query goes to /dev/null, as such no result set is passed back to client.

The whole process is not free for a server, actually if you will check the CPU utilization you will see that the “mirroring” in ProxySQL will actually double the CPU utilization. Meaning that also the traffic on server A will be impacted because resource contention.

Summarizing ProxySQL will:

  • Send the query for execution in different order
  • Completely ignore any transaction isolation
  • Have different number of query executed on B respect to A
  • Add significant load on the server resources

Comparing this with the point and expectations I mention in the reasoning at the end of this article, it is quite clear to me that at the moment we cannot consider ProxySQL as a valid mechanism to duplicate consistent load from server A to server B.


Personally, I don’t think that ProxySQL development Team (Rene’ :D), should waste his time on fixing this part, there are so many other things to cover and improve on ProxySQL.

After having work extensively with ProxySQL and have done deep QA on mirroring, I think that either we keep it as basic blind traffic dispatcher or a full re-conceptualization is required.

2. Setup

But once we have clarified that, we can still see ProxySQL “traffic dispatch” (cannot say mirroring really) as a very interesting feature, that may result useful in many ways, especially because it is so easy to setup.

The following is the result of tests I had performed, which should help in setting correct expectations.

Tests were simple, load data in a PXC cluster and use ProxySQL to replicate the load on a MySQL master-slave environment.

Machines for MySQL/PXC where VM with CentOS 7, 4 CPU 3 GB RAM, attached storage.

Machine for ProxySQL VM CentOS 7, 8 CPU 8GB RAM.

Why I choose to give ProxySQL such higher  volume of resources?

I knew in advance I may need to play a bit with a couple of settings requiring more memory and CPU cycles and I want to be sure I don’t get any problem from ProxySQL in relation to CPU and Memory.

The application that I was using to add load is a Java application I develop to perform my tests. App is https://github.com/Tusamarco/blogs/blob/master/stresstool_base_app.tar.gz, the whole set I had used to do the test is here

I had used 4 different tables,

| Tables_in_mirror |
| mirtabAUTOINC    |
| mirtabMID        |
| mirtabMIDPart    |
| mirtabMIDUUID    |


For full table definition see here


3. ProxySQL setup

Ok so let start.

First setup ProxySQL

DELETE FROM mysql_servers WHERE hostgroup_id IN (500,501,700,701);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',500,3306,60000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',501,3306,100,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',700,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('',701,3306,1,400);
DELETE FROM mysql_users WHERE username='load_RW';
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) VALUES ('load_RW','test',1,500,'test',1);
DELETE FROM mysql_query_rules WHERE rule_id=202;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,apply) VALUES(202,'load_RW',500,700,1,3,1);


4. Tests results

Now the action. 

4.1. Test1

The first test is mainly a simple functional test during which I insert records using 1 single thread in PXC and in MySQL.

No surprise here I have 3000 loops and at the end I have 3000 records on both platforms.

To have a baseline we can see that ProxySQL CPU utilization is quite low



AT the same time the number of "questions" against PXC and MySQL very similar:





The other two metrics we want to keep an eye on are Mirror_concurrency and Mirror_queue_length this two refer respectively to mysql-mirror_max_concurrency and mysql-mirror_max_queue_length.

The two new variables and metrics were introduced in ProxySQL 1.4.0 with the intent to control and manage the load ProxySQL generate internally related to the mirroring feature.



In this case as you can see we have a max of 3 concurrent connections and 0 queue entry, all good.

Now that we have a baseline, and that we know at functional level "it works" let see what happen increasing the load.


4.2. Test 2.

Scope of the test was to identify how ProxySQL will behave with standard configuration and increasing load.

It comes up that as soon as ProxySQL has a little bit more load, it will start to loose some query along the way.

Executing 3000 loop for 40 threads, insert only will result in 120,000 rows inserted in all the 4 tables in PXC but the table in the Secondary (mirrored) platform will only have a variable number between 101,359 and 104,072. Showing consistent lost of data.

Reviewing the insight and comparing the connections running in PXC and the secondary we can see that, as expected, the PXC number of connections is scaling serving the number of incoming requests, while the connections on the Secondary are limited by the default value of mysql-mirror_max_concurrency=16.



Is also interesting to notice that to process the queue of transaction existing in ProxySQL the connection on the Secondary persist longer than the connection in PXC.



As we can see above the queue as an evident bell curve reaching the 6K entries which is quite below the mysql-mirror_max_queue_length limit (32K). Yet the queries were drop by ProxySQL, which indicate the queue is not really enough to accommodate the pending work.



CPU wise ProxySQL as expected take a bit more cycles, but nothing crazy and the overhead for the simple mirroring queue processing can be see when the main load stops around 12:47.


Another interesting graph to keep an eye on is the one describing the executed command inside PXC and the Secondary:






As you can see the traffic on the Secondary was significantly less 669 average than PXC 1.17K. Then it spikes when the main load on the PXC node terminates.

In short it is quite clear that ProxySQL is not able here to scale following the traffic existing in PXC and actually loosing significant amount of data on the Secondary.

Doubling the load in the Test3 show the same behavior, having ProxySQL reach his limit for the traffic duplication.


But can this be optimized?

Of course yes, this is what the mysql-mirror_max_concurrency is for, lets see what is going to happen if I increase the value from 16 to 100 just to make it crazy high.


4.3. Test 4

2 app node writing.

I am jumping the description of test 3 because is mainly the same of Test 2 with more load.


The first thing that is coming to the attention is that both PXC and secondary report same number of rows in the tables (240,000). That is a first good win.


Second the number of running connections:


Lines now are much closer and the queue just drop to few entries.


Commands executed in PXC:


And commands executed in the Secondary:


Average execution report the same value, and very similar trend.


Finally, what was the CPU cost and effect?






As expected some difference in the CPU usage distribution exists, but the trend is consistent between the two nodes and with the operations.


The ProxySQL CPU utilization is definitely higher than before:


But absolutely manageable, and still reflecting the initial distribution.


Finally what about CRUD now?
So far I had only tested the insert operation, but what happen if we run a full CRUD set of tests?


4.4. Test 7 Crud

First of all, let us review the executed commands, in PXC


And Secondary


While in appearance we have very similar workload, selects aside the behavior will significantly diverge.
This because in the Secondary the different operations are not encapsulated by the transaction and executed as they are received.  We can see significant difference in update and delete operations between the two.


Also the threads in execution will show a different picture between the two platform.





It appears quite clear that PXC is constantly having more running threads and more connections.

Never the less both platform process similar total number of questions.





Both having an average around 1.17K/second questions.

This is also another indication of how much the behavior is impacted when we have concurrent operation but no respect of the isolation or execution order.

Different behavior that is also clear reviewing the CPU utilization.






5. Conclusions

To close this long article, I want to go back to the start.

We cannot consider the mirror function in ProxySQL as a real mirroring, but more a traffic redirection.


Use ProxySQL with this approach, is still partially effective in testing the load and the effect it may have on a secondary platform.
As we know data consistency is not guarantee in this scenario, and Selects, Updates and Delete are affected by this given the different data-set and result-set they will manage.

Given that, the server behaviors will change between original and mirror, if not in the quantity in the quality.

I am convinced that when we need a tool able to test our production load on a different or new platform, we will do better to look to something else, like Query Playback recently reviewed and significantly patch by DropBox (https://github.com/Percona-Lab/query-playback).

At the end ProxySQL is already a cool tool and if it doesn't cover this, well I can live with that, I am interested to have it working as it should and does in many other functionalities.


6. Acknowledgments

As usual to Rene' who had work on fixing and introducing new functionalities associated to the mirror, like the queue and concurrency control.

To the Percona team who develop PMM, all the graphs here (except 3) comes from PMM, and some of them are my customization.

Last Updated on Friday, 26 May 2017 10:04
More Articles...

Page 1 of 23

Connecting from

Your IP:


Who's Online

We have 154 guests online