Dontcheff

HeatWave MySQL DB Systems in OCI

In DBA, MySQL, OCI on June 7, 2021 at 10:44

HeatWave is a distributed, scalable, shared-nothing, inmemory, columnar, query processing engine designed for fast execution of analytic queries. It is enabled when you add a HeatWave cluster to a MySQL DB System.

You can think of HeatWave as an easy way to run high performance analytics against the MySQL database.

When creating a MySQL DB System in Oracle Cloud Infrastructure, the options are now Standalone, High Availability and (the newest) HeatWave:

Here is a how the HeatWave architecture looks like:

A HeatWave cluster supports up to 64 nodes. The number you choose depends on the size of the database and the amount of compression that is achieved when loading the data into the HeatWave cluster. You cannot connect the app/session drectly to the HeatWave cluster.

Starting from scratch, choose a MySQL DB system from the OCI Databases menu:

And then start creating a MySQL DB system with the HeatWave option:

Once ready, adding additional clusters (when needed) is simple:

As of now, the only available shape is VM.Standard.E3, so you might get error messages when trying to add more than 4 clusters:

Here is what I got: “You have reached your Analytics Cluster service limit of 4 in this Availability Domain for MySQL.HeatWave.VM.Standard.E3. Please try launching in a different Availability Domain or Region, or try using a different shape. If you have reached all Service limits, please contact Oracle support to request a limit increase.”

Note that 1 node matches to ½ a TB of memory. The increase is linear.

You can rely also on Oracle to estimate the number of nodes:

Before loading data into the HeatWave cluster, the data/tables have to be prepared. Preparing tables involves modifying table definitions to exclude certain columns, define string column encodings, add data placement keys, and specify HeatWave (RAPID) as the secondary engine for the table – note that InnoDB is the primary engine. Loading a table into a HeatWave cluster requires executing an ALTER TABLE operation with the SECONDARY_LOAD keyword.

If a query accesses a table that is not loaded, the query is not offloaded to the HeatWave cluster for processing. Note that queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave cluster for accelerated processing. Results are returned to the MySQL DB System node and to the MySQL client or application that issued the query.

When a table is loaded, data is sliced horizontally and distributed among HeatWave nodes. After a table is loaded, changes to a table’s data on the MySQL DB System node are automatically propagated to the HeatWave nodes. No user action is required to keep data synchronized.

All the details can be found in the HeatWave documentation (it is short – 68 pages only). This is the best place to understand how to prepare & load the data and how to unload tables. The document is full of examples and lists and string functions and operators. Also, you can find a list of all functions, data types, variables, JOIN types, SQL modes, and other expressions and functionality that are not supported by HeatWave.

Unloading table is pretty straightforward:

mysql> ALTER TABLE sales SECONDARY_UNLOAD;

Here are some additional link if interested in more detail:

Licensing Types of the Oracle Database

In Cloud, Database options, Databases, DBA, New features, Oracle database on May 16, 2021 at 13:18

After being asked on daily basis all kinds of questions on Oracle Database Licensing, as time goes by, you sort of understand it. Sort of, because the Oracle Database Licensing Guide is 602 pages long and gets often updated. The latest one is from April 2021 – now it is mid-May.

Moreover, you have perhaps seen all Oracle certifications but if you search for one on licensing you will find what I did – there isn’t one.

What I am trying to do now, is to summarize Database Licensing in a short blog post – this might be helpful for many to at least understand the concept.

There are 3 types of licenses for the Oracle Database: Packs, Options and Features and 9 Oracle Database Offerings: Standard Edition 2, Enterprise Edition, Oracle Database Appliance, Exadata, Exadata Cloud Service and Cloud@Customer, Database Cloud Service Standard Edition, Database Cloud Service Enterprise Edition, Database Cloud Service Enterprise Edition – High Performance and Database Cloud Service Enterprise Edition – Extreme Performance (you can see their abbreviations in the table below).

  1. Packs: there are 5 different packs for the Oracle Database:

2. Options: there are 15 database options for the Oracle Database:

  • Oracle Active Data Guard
  • Oracle Advanced Compression
  • Oracle Advanced Security
  • Oracle Database In-Memory
  • Oracle Database Vault
  • Oracle Label Security
  • Oracle Machine Learning
  • Oracle Multitenant
  • Oracle On-Line Analytical Processing (OLAP)
  • Oracle Partitioning
  • Oracle RAC One Node
  • Oracle Real Application Clusters (Oracle RAC)
  • Oracle Real Application Testing
  • Oracle Spatial and Graph
  • Oracle TimesTen Application-Tier Database Cache

Here are the ones related to Consolidation, HA, Managability and Performance:

3. Features: there are 131 features that can be licensed with the Oracle Database out of which 105 are for EE and 123 are for Exadata. As you can see, there are 3 features available for Exadata, ExaCS and ExaC@C falling under the functional category of Autonomous:

If you would like to drill down in detail, use the Database Feature and Licensing tool which is available online without the need to register or have an Oracle account.

Moreover, the Oracle Enterprise Manager Licensing Manual is 366 pages, so there is more to read if you are done with the Database Licensing Manual.

You might think that is way too much for me, and perhaps it is, but the situation is very similar with other database vendors. Let us look at AWS and GCP for instance:

AWS have more than 10 database offering:

Amazon Aurora
Amazon RDS
Amazon Redshift
Amazon DynamoDB
Amazon ElastiCache
Amazon DocumentDB (with MongoDB compatibility)
Amazon Keyspaces (for Apache Cassandra)
Amazon Neptune
Amazon Timestream
Amazon Quantum Ledger Database (QLDB)
AWS Database Migration Service (DMS)

GCP have also more than 10 database offerings:

Relational: Bare Metal Solution for Oracle workloads
Cloud SQL: Managed MySQL, PostgreSQL and SQL Server
Cloud Spanner and BigQuery
Key value: Cloud Bigtable
Document: Firestore and Firebase Realtime Database
In-memory: Memorystore
NoSQL: MongoDB Atlas and managed offerings from open source partner network including MongoDB, Datastax, Redis Labs, and Neo4j

And, after all, Azure are not much behind:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Virtual Machines
Azure Database for PostgreSQL
Azure Database for MySQL
Azure Database for MariaDB
Azure Cosmos DB
Azure Cache for Redis
Azure Database Migration Service
Azure Managed Instance for Apache Cassandra

After all, being expert in database licensing in a skill of its own!

Applying one-off patches in the Cloud on Oracle Database 21c

In Cloud, DBA, OCI, Oracle database on April 27, 2021 at 08:37

Oracle have just released new fixes for the 21c version of database release: a security fix and a JDK bundle patch.

The recommendation is to apply these two patches mentioned below to your databases:

• 32640471 21C SECURITY FIXES FOR CPUAPR2021
• 32685286 JDK BUNDLE PATCH 21.0.0.0.210420

Most likely, you will first get an email from Oracle to let you know that the patches are already available:

How to apply the patch? The one-off patches (now they are call interim patches) can be applied via the Console, API or even manually. To apply an interim patch manually, you can use the Opatch utility. The detailed steps are provided in the Applying one-off patches on Oracle Database 21c documentation. The patches can be applied in any order.

Here is how simple and easy it is:

1. For the database on which you want to apply the patches, just click its name to display details and under Resources, click Updates:

2. Click on “Apply a one-off patch“:

3. Then, in the Apply one-off patch dialog, enter the patch numbers. Use a comma-separated list to enter more than one patch. I did apply them one after each other. Paste the patch number and then click Apply.

While the patch is being applied, the database’s status displays as Updating:

A work request is created for the patching operation allowing us to monitor the progress of the operation.

If the operation completes successfully, the database’s status changes to Available:

It is that simple!