Asynchronous mysql replication, which fork to choose?

Question:

Geographic distribution of mysql database (master-master) is required.

I read a lot about the comparison of forks for synchronous replication, and I realized that the choice is more likely between percona and mariadb, since mariadb is not developed by a commercial organization and requires disabling selinux, with all other ~ equal reviews, I would be the first to try to implement percona.

But for a geographically distributed replication solution, asynchronous is needed, I think so, and on this topic I can not find a good comparison. I suspect that modified innodb repositories on forks will still give better performance and stability.

I'm more concerned about performance with data consistency. I already have a little experience in implementing asynchronous oracle mysql master-master with GTID, I configured it for experimental purposes on a project with an attendance of ~ 600 uniques per day, deploying 2 database containers on one virtual machine. There are few visitors, but CMS 1C Bitrix has a frequently changing table with user sessions and every 2-4 days I receive a letter that this table contains 1032, 1062. For this table, the consistency does not matter, ok, but what will happen on the project with ~ 40t and, for example, tables of web forms xs.

Please share your experience in implementing asynchronous mysql. The story about synchronous is also welcome. 🙂

Answer:

Typically, tables and user sessions are excluded from replication.

Quote :

Almost any project contains non-critical or recoverable data. Including – in the database.

In our case, such data was sessions. What was wrong with everything being replicated?

In addition to SELECTs, session tables have many write operations ( INSERT , UPDATE , DELETE ). This means that we are giving extra load to the slave base. The kind of stress that can be avoided.

In addition, with a sufficiently large value of query_cache_size we are faced with the fact that active work with these tables and their participation in replication leads to the fact that many threads "hang" in the "waiting for query cache lock" state (seen in SHOW PROCESSLIST ). Further, this is fraught with increased CPU load and general performance degradation.

Excluding this data from replication completely solved the problem. "

Scroll to Top