Objectives
Our objective is to split the read and write database query of a Magento Community edition so that we can have the writer database only serve the write traffic and the reader database handles the read traffic. This will allow us to load-balance the bulk read traffic and be able to handle more traffic with the exact same database instance size. Also, most of the read traffic will be served from the database proxy caching.
Here, we will be using AWS RDS to host the database with 1 writer and multiple reader instances. However, for our testing we will only use 1 writer and 1 reader instance. Application is hosted on AWS EC2 with auto-scaling configuration. The database proxy used is the Heimdalldata SQL proxy. It is capable of handling proxy for MySQL, PostgreSQL and SQL server.
Requirements
- AWS EC2 with auto-scaling config to host the Magento application
- AWS RDS aurora to host the database
- Heimdalldata to act as the MySQL proxy
- AWS Elasticache to cache the database queries
Let’s start the setup process.
We already have the Magento application and database hosted on AWS EC2 and AWS RDS aurora, so we will not cover the setup for Magento application and database. We will only be covering the Heimdalldata setup and integration with Magento2 in this tutorial.
If you have not yet done Magento application setup, please review this document for Magento 2 application and database setup