If you are going to run e-commerce with good sales on Magento 2 Community Edition (CE) read the post carefully as it will save your money. If you are already experiencing these issues it will help you to resolve them.
What is the problem with Magento 2 Community Edition architecture and database replication?
2 years ago we started working on a few big enterprises Magento 2 projects. Naturally for large e-commerce needs our customers chose the most powerful dedicated servers with 32 CPUs and 64Gb RAM.
With this configuration, all pages, even the most complex, are processed fast, and with another our solution Magento 2 Crawler, Magento 2 Community Edition performed exceptionally well demonstrating a stable growth.
After a while, e-commerce teams (marketers, SEO-specialists, content writers and others) worked actively and the stores continued to expand.
At some point, when traffic reached 5000 visitors per day, the dedicated Servers became highly overloaded (another article will explain how easy it is to jump to a scalable distributed architecture).
For that exact point, we started investigating why the servers were overloaded and how to resolve that right at those points quick and fast as businesses started losing conversion rates and income as a result.
We have discovered two major problems that were causing a loss of conversion:
- Add to Cart & Checkout functions slowness
- MySQL errors: “PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock”
When some deep analysis was conducted we have found a root of these problems.
Overloaded database server
The first problem with a slowness on critically important less-cached e-commerce functions like Add to Cart, Checkout, and others was caused by the overloaded database server. SQL queries were running 10~20 times slower, as a result, customers thought something is not right and were leaving sites.
1213 Deadlock
The second problem with MySQL transactions (1213 Deadlock) was happening because of actively using Magento 2 admin by e-commerce teams (adding/editing products, categories, cms, etc). And was caused by the first problem.
What happens when the servers are overloaded
Normally, the SQL operations in Magento 2 Backend are performed very quickly, and the probability of the external keys’ crossing is close to 0%. However, when the servers are overloaded, the following happens:
- Large Frontend SELECT queries overload SQL server
- A single backend transaction runs, locks some keys and waits, as the server is not able to process transaction fast
- Large SELECTs from frontend keep running and server is still 100% overloaded
- Another backend transaction runs and locks crossed foreign keys
- As a result, we have a deadlock, that locks the keys and keeps tables locked until some timeout happens and both transactions die
- This all is happening in a loop and as a result, in admin backend, we have constant not finished operations and errors. Naturally, the front site slows down too.
As a quick solution was offered to run another dedicated server for the database only. This allowed more resources for MySQL server and resolved problems temporarily until more traffic came in and more activity on backend happened.
Eventually, the business hits a glass ceiling and the growth stops until serious action is taken.
Skynix team conducted deep research and detected that this problem might be resolved only with a database replication when in architecture will be 1 master database server for quick write operations and one or a number of slave database servers for large and slow [read only] operations.
However, Magento 2 CE owners were left without this opportunity. When Magento 1 and Magento 2 EE supported this feature out of the box, Magento 2 CE does not.
And it has appeared that it is almost impossible to code any module/extension that will make Magento 2 CE to support database replication, because of a specifically designed core architecture.
Accelerating your website by Magento 2 CE database replication
Skynix team put a lot of effort to find some compromise-solution for Magento 2 CE.
The source code of the solution is licensed as open source, so welcome to use it for free!
For github fans: https://github.com/skynixukraine/Magento-2—MySQL-Cluster/releases/tag/1.0.0
For Bitbucket professionals: https://bitbucket.skynix.co/projects/M2E/repos/magento-2—mysql-cluster/browse
Briefly: this solution forwards all SELECT requests from frontend to slave server(s).
All write requests and all requests from admin backend keep coming to the master database.
As a result, Add To Cart, Checkout and Admin backend work fast, and slow frontend can be easily fixed by adding new slave servers.
Installation & configuration process of the solution is quick and easy, though if you have any problems don’t hesitate to contact us.
UPDATE: We’re sad to inform that this module is not supported by the latest versions of Magento and requires an upgrade.
