Magento 2: Trying to build Split Database Master and Slave for Magento2 CE (Part 1)
What is the problem with Magento 2 Community Edition architecture and database replication?
Over a year ago, I started on a few big enterprise Magento 2 projects. With the most powerful dedicated servers, Magento 2 Community Edition performed exceptionally well demonstrating stable growth.
After a while, e-commerce teams (marketers, SEO specialists, data-team, and others) worked actively and the stores continued to expand. The project becomes huge with over 100 stores over the world. The lowest traffic reached 5000 and the most it can be 30 000 visitors per day, the dedicated Servers became highly overloaded.
At this point, we started investigating why the servers were overloaded and how to resolve that right at those points quickly and fast as businesses started losing conversion rates and income as a result.
We discovered 2 major problems:
- Add to cart and Checkout features are very slow
- MySql Deadlock (SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)
Overloaded database on less-cached e-commerce functions.
Magento 2 is the most powerful but also heavy CMS. Add to Cart, Checkout, and other non-cached functions become slow caused by the overloaded database server. SQL queries were running 10~20 times slower, as a result, customers left because they thought something is not right.
MySql Deadlock
1213 Deadlock is a MySql transactions problem. Most of it was from Magento 2 Admin (adding/editing products, categories, cms, etc) and Magento 2 Cron jobs. Normally, the SQL operations in Magento 2 Backend are performed very quickly but the first problem brings us to this.
How did Deadlock happen?
- Frontend large queries overload SQL server
- Backend (Cronjob) transaction runs so locks and waits from the server. Queries can’t go fast because of FE queries.
- FE keeps sending large SELECT queries.
- Another Backend (Cronjob) new transaction runs and all older be crossed.
- So, after some timeout happens and both transactions were killed. And we got an error notification of Deadlock and transactions were not run completely.
So, Backend took an SQL error and Frontend kept slow. This happens as an infinite loop. The solution to this can be easy running another dedicated server for the MySql only, the more resources added, the more fast server runs. But resources are limited, widening it is a temporary solution until more traffic came in and more activity on the backend happened.
We kept researching and digging deeper, we discovered that the deadlock problem might be resolved only with a replication database.
Master node was used for quick write operations and Slave node for large and slow [read only] operations. This architecture was supported in Magento 1 and Magento 2 Enterprise Edition. However, Magento 2 Community Edition does not.
Master-Slave database for Magento 2 CE
And it has appeared that it is almost impossible to code any module/extension that will make Magento 2 CE support database replication, because of a specifically designed core architecture.
We don't know if it can be or not, but let take a look.
Magento 2 has a class is “ Magento\Framework\DB\Adapter\Pdo\Mysql”, which handles the connection to the database and that connection to query via PDO. Why we do not add a new connection for read-only purposes? We can add methods to detect the query is writing or reading and route them into the right database node that was configured. That is logic level, it may contain advantages and disadvantages. We will detail it in the next article, please follow.
The second compromise solution is ProxySql. MySql 8.0 is a required Magento 2.4.x system. Query cache was disabled by default in version 5.6, but it was removed in 8.0, ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for traffic mirroring, among other use cases.
It was just a case and idea, we will find out it works or not in the next article (Source code will be provided here).
