4 Steps to Configure Magento 2 Split Database

• Split Database and its Working in Magento 2'

• Key Benefits of Split Database Solution

• Split Database Configuration in Magento 2

• Magento 2 Split Database Extensions and its Features

• Best Practices for Magento 2 Split Database

• FAQs

• Summary

Split Database and its Working in Magento 2'

A split database solution involves dividing a single large database into smaller, more manageable parts.” Each part handles different types of data and tasks.

• After splitting, Magento 2 typically has three main types of databases. Here is an overview:

  • The main database handles most of the standard site data except for what is covered by the other two databases. It includes product details, categories, and static information like Magento CMS pages.

  • The checkout database is specialized to handle everything related to the checkout process. It deals with customer sessions, cart data, and anything related to processing purchases. By isolating checkout data, Magento can ensure that the checkout process runs smoothly. This is especially true during high-traffic times.

  • After a purchase is made, order details are stored in the order database. This includes order histories, payment details, and shipping information. Having a separate database for orders allows for faster order processing and provides easy access to order histories.

• Magento provides the ability to set up any number of slave databases for each master. These slaves act as read replicas and can be used for load-balancing read queries, reporting, analytics, etc.

• Magento uses the ResourceConnections class to provide a unified MySQL database connection to the application, regardless of the split database configuration being used.

• For routing queries, Magento implements the Command Query Responsibility Segregation (CQRS) pattern. This pattern separates operations that write queries from operations that just read queries.

• The CQRS pattern handles the logic of routing write queries like checkout submissions, order placements, etc., to the respective checkout or sales databases. It routes read queries for product browsing, search, etc., to the main application database.

Key Benefits of Split Database Solution

1. Performance Improvement

• When databases are split, each can be optimized for specific tasks, leading to faster processing of data.

• For example, searching for products can be quicker because the system only has to look through the product information. Splitting the database reduces the load on any single database and prevents Magento slowdowns during heavy usage times, like during a sale.

2. Easier Maintenance

• When data is organized into separate databases, it's easier to pinpoint the source of issues and fix them without affecting other parts of the system.

• Maintaining, updating, or backing up smaller databases can be quicker and less risky as changes are localized to specific areas.

3. Scalability

• As your Magento store grows, so does the data it handles. Split databases allow you to scale up more smoothly by adding resources where needed without overhauling the entire database system.

• The admin can move databases to different servers if one type of data needs more resources, helping manage growth more efficiently.

4. Flexible Database Architecture

• With split databases, Magento 2 provides flexibility in how the Magento database tier is architected and scaled. Each database can be hosted on the same server or distributed across multiple servers/hosts.

• Databases can be replicated with master-slave setups for load balancing and high availability. Cloud-based database services can be leveraged for specific needs like checkout or Magento OMS.

5. Data Isolation and Analysis

• Splitting databases logically isolates different types of data, making it easier for the developer to manage, back up, and analyze each area.

• For example, the sales/OMS database can be replicated as read-only for running Magento analytics or reporting without impacting transactional operations. Backup strategies can be customized per database type based on priorities.

Split Database Configuration in Magento 2

Step 1: Setup three MySQL master databases

1. Create three separate MySQL master databases on any host:

   • A master database for checkout/quote tables like quote, quote_item, quote_address, etc. This stores all cart and checkout-related data.

   • One master database for sales/order management (OMS) tables like sales_order, sales_order_item, sales_invoice, etc. This stores all order processing and sales data.

   • A master database for the remainder of the Magento application tables, such as Magento catalog data, customer data, configurations, etc.

2. You can name these databases anything like magento_quote, magento_sales, and magento.

Step 2: Configure database details in the env.php file

1. Add the database connection details to the env.php file located at <magento_root>/app/etc/env.php.
2. Specify the database hostname, name, username, and password for each of the three master databases:

'db' => [ 'connection' => [ 'default' => [ 'host' => 'localhost', 'dbname' => 'magento', 'username' => 'root', 'password' => 'password' ], 'checkout' => [ 'host' => 'localhost', 'dbname' => 'magento_quote', 'username' => 'quote_user', 'password' => 'quote_password' ], 'sales' => [ 'host' => 'localhost', 'dbname' => 'magento_sales', 'username' => 'sales_user', 'password' => 'sales_password' ] ] ]

Step 3: Run SQL Scripts to Setup Schemas and Move Tables

1. Open the Magento CLI commands to enter the following code:

bin/magento setup:db-schema:split-quote -- host="checkout_host" --dbname="magento_quote" -- username="quote_user" --password="quote_pass"

bin/magento setup:db-schema:split-sales --host="sales_host" - -dbname="magento_sales" --username="sales_user" --password="sales_pass"

2. It will move the relevant tables to the new databases based on the connection details provided.

Step 4: Test and Verify Implementation

1. Add some products to the cart and start checkout to ensure the quote/checkout tables like quote and quote_address are being written correctly.

2. Complete an order and verify that sales tables like sales_order and sales_invoice appear in the sales database.

3. Browse the product catalog and verify that all other core Magento operations are unaffected.

Magento 2 Split Database Extensions and its Features

Extension Name	Key Features
Webkul Split Database Marketplace Add-On	- Allows splitting the master database into two: One for Magento and one for the Webkul Marketplace extension tables. - Provides CLI commands to split the databases. - Improves Magento 2 website performance and scalability by dividing the load between databases. - Supports read/write operations across multiple databases.
Firebear Split Database Solution	- Implements the native Magento split database architecture with three master databases. Supports replication with any number of slave databases. - Uses ResourceConnections class for unified database connection. - Implements CQRS pattern to route read/write queries.
Sparsh Split Order Extension	- Allows splitting orders into separate order IDs for products from different vendors. - Enables separate invoices and shipments for each order ID. - Splits shipping charges, tax, and discounts based on order split. - Customers can view and track split orders separately.

Best Practices for Magento 2 Split Database

1. Plan Your Database Architecture

• Assess which data is accessed most frequently and may cause bottlenecks, such as customer sessions, orders, and product data. Separate these into different databases to optimize performance.

• Design your database architecture with scalability in mind, considering future business growth and ensuring the architecture can handle increased data volumes and traffic.

2. Proper Hardware and Resources

• Allocate dedicated hardware resources to each database to prevent resource competition. Each database should have its CPU, RAM, and storage.

• Implement redundancy and failover mechanisms like MySQL Cluster or master-slave replication to keep the system running smoothly even in the event of hardware or software failures.

3. Optimize Database Configuration

• Analyze and implement indexes on database tables that are frequently queried. This reduces the time taken to retrieve data from large tables.

• External caching systems like Magento Redis or Memcached can be used to store frequently accessed data, reducing the load on the database.

• Adjust MySQL settings such as max_connections and innodb_buffer_pool_size to optimize database performance, allowing it to handle more simultaneous connections effectively.

4. Security Best Practices

• Implement strict access controls, ensuring that only authorized personnel and applications have access to the databases. Use network security measures such as firewalls to restrict access.

• Encrypt sensitive data in transit using SSL/TLS and at rest using AES-256. Regularly audit database access logs, configurations, and security settings to identify and mitigate potential security risks.

5. Data Migration and Synchronization

• Ensure that data remains consistent across all databases during migration. Use transactional support and atomic operations where necessary.

• If an atomic operation is interrupted by a failure or a system crash, changes will not partially apply. Employ synchronization tools like MySQL replication to maintain data consistency across master and replica databases.

6. Monitoring and Maintenance

• Implement monitoring solutions to continuously track database performance, resource usage, and system health. Tools like Nagios, New Relic, or Prometheus can provide real-time insights.

• Perform database maintenance tasks like defragmenting databases, updating statistics, and performing integrity checks. Set up an alerting system to notify admins of potential issues like high load, slow queries, or connectivity problems.

7. Testing and Quality Assurance

• Load testing should be conducted by simulating both normal and peak loading conditions to ensure the database can handle traffic surges without performance hitches.

• Perform thorough regression testing to ensure that new updates and configurations do not affect existing functionalities.

8. Documentation and Training

• Maintain documentation of your database architecture, configuration procedures, and maintenance schedules.

• Provide regular training sessions for your technical teams to educate them on database setup and performance optimization techniques.

FAQs

1. What is the purpose of using a split database in Magento 2?

It enhances store performance by managing different data types, especially when handling high traffic and large order volumes.

2. How does splitting the database benefit cloud-based Magento 2 stores?

It improves performance and scalability and facilitates easier database management in the cloud.

3. What are the first steps to install a split database setup in Magento 2?

Start by setting up three MySQL master databases and configuring details in the env.php file to install the system.

4. Can I consolidate multiple databases back to a single database in Magento?

Yes, merging multiple databases back to a single database is possible, but it requires careful execution.

5. What PHP configurations are essential for optimizing a split database setup?

Adjust max_execution_time and memory_limit in PHP settings to support the split database system effectively.

Summary

Magento 2 split databases is an effective strategy to increase performance and website efficiency for a better user experience. In this tutorial, we explained how to split databases, as well as its benefits and best practices. Here is a quick recap:

• Targeted Database Roles: Magento 2 segregates databases to streamline site operations.

• Simplified Scalability: Separate databases enable easier scaling and maintenance, allowing for resource allocation.

• Cloud Compatibility: The flexible architecture supports both single-server and multi-cloud environments.

• Focused Data Management: Logical data segmentation aids in more efficient backups, security measures, and analytical processes.

• Strategic Best Practices: Effective planning and resource dedication are key to leveraging the full potential of split databases.

Explore managed Magento hosting plans to boost store efficiency and performance by splitting databases.