Deploy Magento on MDS & HeatWave

Magento is an Open-Source e-commerce platform written in PHP using multiple other PHP frameworks such as Laminas and Symphony. Magento source code is distributed under Open Software License v3.0.

Deploying Magento is not always easy as it often requires registration. However the source code is also availble on GitHub.

Magento supports MySQL 8.0 since version 2.4.0 (July 28th 2020).

In Magento’s documentation, there is a warning about GTID support:

This is not anymore a problem since MySQL 8.0.13 ! Also MDS uses always the latest version of MySQL.

Requirements

To deploy Magento on Oracle Cloud Infrastructure (OCI) using MySQL Database Service (MDS) and HeatWave you need:

• an Internet Connection
• an OCI account (you can get a free trial on https://www.oracle.com/mysql)

And… nothing else 😉

Once ready, the OCI dashboard is similar to this:

Deployment

The easiest was to deploy a full stack architecture on OCI is to use Resource Manager and a Stack. The complexity will be handled by the stack itself and Terraform will deploy automatically all required resources.

You can get a stack to deploy the Magento architecture with all the required resources on my GitHub repository: https://github.com/lefred/oci-magento-mds

The zip file is what you need if you want to deploy it manually:

If you want to deploy everything automatically, you just need to click on the button

If you click on it, you will launch the Stack Creation wizard and you need to first accept the Oracle Terms of Use:

Once accepted, all information will be pre-filled and you will need to just fill some mandatory variables:

Architectures

With this stack, you have to possibility to deploy different architectures. It’s up to you to decide which one will match your needs.

Architecture 1 – default

The default architecture is this one:

As you can see there is only one Magento Webserver (compute instance) using one MySQL Database Service instance and one OpenDistro for ElasticSearch compute instance.

MDS must always be on a private subnet.

Architecture 2 – multiple webservers

With this deployment, one single Database and OpenDistroES are working as backend for multiple Magento Webservers. This is common when you want to have a load-balancer in front of your Magento serving the same site.

Architecture 3 – multiple isolated sites

As you can see, in this third and final architecture, you can deploy multiple Magento completely isolated. They each use their own MDS instance and their own OpenDistroES instance.

You can deploy on of these architecture simply from the Stack Creation screen:

It’s also possible to use an existing infrastructure in case you already have a VCN, an Internet Gateway, subnets, security lists, etc… and you want to use them.

When you choose to deploy multiple Webservers, you need to fill this form too:

Deploy !

When everything is filled as you want, you can directly deploy all the resources:

Now, the apply job will run for some time (approximately 10 to 15 minutes). And you can follow all the steps in the log console:

Important information will also be printed at the end. You can also retrieve those output variables from the menu on the left:

Created Resources

You can also verify what has been created for you like the instances for example:

Magento

We can copy the public IP of the Magento Webserver in our browser and we will see the Magento’s empty home page:

If you are familiar with Magento, you should know that now, you require the Magento Admin URI to access the administration dashboard. This URI can be found in the Stack’s log output:

If you add the URI to the public IP in your browser, you will reach the sign in form:

Cron Job

You will then see that the cron jobs are not enabled:

To enable the cron jobs, you will need to access the Webserver in SSH.

SSH to Compute Instances

In OCI, to access in ssh to a compute instance in the public subnet, we need the IP but also a key. Usually, when you deploy manually your resources, you are requested to paste, upload or generate such key. Resource Manager generated one for us.

We need to retrieve that key and save it in our computer. As usual we can find it in Stack’s outputs:

We will paste it into a file called oci.key and use it:

[fred@imac ~/keys] $ chmod 600 oci.key 
[fred@imac ~/keys] $ ssh -i oci.key opc@130.xx.xx.xxx
The authenticity of host '130.xx.xx.xxx (130.xx.xx.xxx)' can't be established.
ECDSA key fingerprint is SHA256:+gVvfYsXMfqoUEHuw6myhIfm9ov748jN+Vf20zr573o.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '130.xx.xx.xxx' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Mon Mar 15 11:38:21 2021 from 132.xxx.xxx.xxx
[opc@magentoserver1 ~]$

We can now enable to cron job using the magento command line utility:

[opc@magentoserver1 ~]$ cd /var/www/html
[opc@magentoserver1 html]$ sudo -u apache bin/magento cron:install
Crontab has been generated and saved

The magento utility can also be used to retrieve the admin URI:

[opc@magentoserver1 html]$ sudo -u apache bin/magento info:adminuriAdmin URI: /admin_1s35px

Magento Sample Data

As I’m not a Magento developer, I will add some sample data. I will use Magento2’s official sample data using git:

[opc@magentoserver1 ~]$ sudo dnf -y install git
...
[opc@magentoserver1 ~]$ cd /var/www/html/
[opc@magentoserver1 html]$ sudo -u apache git \
  clone https://github.com/magento/magento2-sample-data
...
[opc@magentoserver1 html]$ sudo -u apache php -f \
  magento2-sample-data/dev/tools/build-sample-data.php \
  -- --ce-source="/var/www/html/"
All symlinks you can see at files:
/var/www/html/magento2-sample-data/dev/tools/exclude.log

It’s recommended to also increase the max memory allowed for PHP processes:

[opc@magentoserver1 html]$ sudo sed -i \
  's/memory_limit\s*=.*/memory_limit=512M/g' /etc/php.ini

[opc@magentoserver1 html]$ sudo systemctl reload httpd

When done, we can finish the sample data’s installation:

[opc@magentoserver1 html]$ sudo -u apache bin/magento setup:upgrade
...
block_html: 1
full_page: 1
Nothing to import.
[opc@magentoserver1 html]$ sudo -u apache bin/magento cache:flush

Let’s verify is something has changed on the site (we use again the public IP in the browser):

MySQL HeatWave

HeatWave is a flagship technology that is only available in MDS and that considerably accelerates queries that are too long or too complex.

The first requirement to use HeatWave is to have a MDS Instance Shape compatible with it.

I recommend to always use a HeatWave compatible shape for MDS even if you don’t plan to directly use HeatWave. In this example, the shape used is MySQL.HeatWave.VM.Standard.E3. You can directly see that the shape is compatible with HeatWave.

If you have such shape, you can immediately enable HeatWave on it (if not you will have to deploy a new instance):

And you follow the wizard.

As soon as the HeatWave nodes are ready, you will see them as active in OCI’s dashboard:

Using MySQL HeatWave

Now from the Magento Compute Instance, we can use MySQL Shell to connect to our MDS instance and verify if HeatWave is ready:

SQL> SHOW GLOBAL STATUS LIKE 'rapid_plugin_bootstrapped';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| rapid_plugin_bootstrapped | YES   |
+---------------------------+-------+
1 row in set (0.0018 sec)

It seems it’s all correct. We also have status variables:

SQL> show status like 'rapid%' ;
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| rapid_change_propagation_status | ON         |
| rapid_cluster_ready_number      | 2          |
| rapid_cluster_status            | ON         |
| rapid_core_count                | 16         |
| rapid_heap_usage                | 67109005   |
| rapid_load_progress             | 100.000000 |
| rapid_net_authentication        | ON         |
| rapid_plugin_bootstrapped       | YES        |
| rapid_preload_stats_status      | Available  |
| rapid_query_offload_count       | 0          |
| rapid_service_status            | ONLINE     |
+---------------------------------+------------+

And Performance_Schema tables:

SQL> show tables like 'rpd%';
+-------------------------------------+
| Tables_in_performance_schema (rpd%) |
+-------------------------------------+
| rpd_column_id                       |
| rpd_columns                         |
| rpd_exec_stats                      |
| rpd_nodes                           |
| rpd_preload_stats                   |
| rpd_query_stats                     |
| rpd_table_id                        |
| rpd_tables                          |
+-------------------------------------+

Choosing tables to be used with HeatWave

It’s necessary to tell to MySQL which tables can be used for HeatWave. This means which tables have their data off-loaded in HeatWave’s in-memory cluster.

For this Magento site, I will use the catalog tables.

This is an example using catalog_product_index_price:

SQL> ALTER TABLE catalog_product_index_price SECONDARY_ENGINE = RAPID;
SQL> ALTER TABLE catalog_product_index_price SECONDARY_LOAD;

I used a script to perform those statements on all catalog% tables.

It’s time to surf a bit on our Magento Shop and then verify is some queries used HeatWave.

To perform this operation, we can use the rapid_query_offload_count status vatiable in MySQL:

SQL> SHOW STATUS LIKE 'rapid_query_offload%' ;
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| rapid_query_offload_count | 3     |
+---------------------------+-------+
1 row in set (0.0015 sec)

We can see that 3 queries used HeatWave already. And of course, it’s possible to see which ones using Performance_Schema table called rpd_query.stats:

SQL> SELECT query_text, JSON_PRETTY(QEXEC_TEXT) 
     FROM performance_schema.rpd_query_stats\G
 ************************** 1. row ***************************
             query_text: SELECT MAX(count) AS `count` FROM (
                           SELECT count(value_table.value_id) AS `count` 
                         FROM `catalog_product_entity_varchar` AS `value_table` 
                         GROUP BY `entity_id`,
    `store_id`) AS `max_value`
json_pretty(QEXEC_TEXT): {
  "timings": {
    "queryEndTime": "2021-03-17 09:26:29.208009",
    "queryStartTime": "2021-03-17 09:26:29.016203",
    "joinOrderStartTime": "2021-03-17 09:26:29.016116"
  },
...

With HeatWave in MDS we can also compare those queries. Let’s have a look:

SQL> SELECT MAX(count) AS `count` FROM (SELECT count(value_table.value_id) 
     AS `count` FROM `catalog_product_entity_varchar` AS `value_table` 
     GROUP BY `entity_id`, `store_id`) AS `max_value`;
+-------+
| count |
+-------+
|    10 |
+-------+
1 row in set (0.0507 sec)

SQL> SET SESSION use_secondary_engine=OFF;

SQL> SELECT MAX(count) AS `count` FROM (SELECT count(value_table.value_id) 
     AS `count` FROM `catalog_product_entity_varchar` AS `value_table` 
     GROUP BY `entity_id`, `store_id`) AS `max_value`;
+-------+
| count |
+-------+
|    10 |
+-------+
1 row in set (2.3843 sec)

As you can see, there is already a lot of difference for such a small query !

Maintenance

For some operations, like reindexing everything, Magento runs some DDLs that are not supported while HeatWave is active.

So if you encounter errors similar to this one:

SQLSTATE[HY000]: General error: 3890 DDLs on a table with a secondary engine 
defined are not allowed., query was:
   TRUNCATE TABLE `catalog_category_product_index_store1_replica`
Product Categories index process unknown error

You only need to disable HeatWave for the time of the maintenance:

SQL> ALTER TABLE catalog_product_index_store1 SECONDARY_ENGINE NULL;

And the maintenance can happen this time without error:

$ sudo -u apache bin/magento indexer:reindex catalog_product_price
Product Price index has been rebuilt successfully in 00:14:03

When finished, you can enable back HeatWave:

SQL> ALTER TABLE catalog_product_index_store1 SECONDARY_ENGINE = RAPID;
SQL> ALTER TABLE catalog_product_index_store1 SECONDARY_LOAD;

As you can see, it’s very easy to deploy Magento on OCI using Resource Manager and you can see that Magento benefits immediately from HeatWave.

You can also check this post on video: