For open-source relational databases, Oracle MySQL is still one of the most popular today.

And few may realise the differences of Oracle MySQL that are not present on other open-source technology. eg Postgres.

Also the Oracle MySQL Database Service is available with HeatWave as an in-memory query accelerator for OLTP and OLAP queries.

But why consider Oracle MySQL in the first place, if you are already familiar with another open-source relational database?

It’s helpful to examine some of the standard features of Oracle MySQL that make it stand out in the open-source crowd.

Storage Engines

For those familiar with other database technology, the Oracle MySQL storage engines may be unfamiliar to you.

Essentially, for each table you create, you decide it’s operational profile by specifying a storage engine. There are several storage engines to choose from, but the two main storage engines in Oracle MySQL are:

• InnoDB (transactional storage engine).
• MyISAM (non-transactional storage engine).

InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases.

The differences between the storage engines are as below.

1. InnoDB Storage Engine

InnoDB is the storage engine that MySQL customers typically use for transactions where reliability and concurrency are important.

This storage engine has the following attributes:

— InnoDB is a multi-version storage engine. It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback.

— Each table is physically organized to optimize queries based on the primary key.

— Uses row-level locking to support simultaneous write access by multiple sessions and runs queries as nonlocking consistent reads by default, in the style of Oracle.

If the server unexpectedly exits because of a hardware or software issue, regardless of what was happening in the database at the time, you don’t need to do anything special after restarting the database.

2. MyISAM Storage Engine

As the MyISAM storage engine is non-transactional, it performs best for readonly, read-mostly, or single-user applications due to the locking model used.

MyISAM has only table level locking (multiple readers with a single writer).

Note — unlike the Oracle Database, by default, MySQL (and other open-source relational databases) will start the session for each new connection with autocommit enabled.

Indexes

As for Oracle database, MySQL supports the use of range, list, and hash partitioning.

Also for the InnoDB storage engine:

— Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key.

— InnoDB indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

— InnoDB can we have b-tree indexes and the Adaptive Hash Index feature on the same table.

— InnoDB can have hashed (or keyed) partitioned tables, and the indexes will follow the same partitioning.

Descending indexes, and invisible indexes are also supported.

Data Access Paths

For the execution of queries the optimisation is advanced. Oracle MySQL is able to perform the following advanced optimisations for queries:

• index skip scans.
• partition pruning.

The optimisation of DML is quite sophisticated in Oracle MySQL, and not too dissimilar to the Oracle database.

Online DDL

Just like the Oracle database, MySQL really shines for online DDL.

Many DDL operations on tables and indexes (CREATE, ALTER, and DROP statements) can be performed online.

• Index Operations
• Primary Key Operations
• Column Operations
• Generated Column Operations
• Foreign Key Operations
• Table Operations
• Tablespace Operations
• Partitioning Operations

Other open-source relational databases don’t come close to Oracle MySQL in the breadth of online DDL operations.

Physical backup

Oracle MySQL is able to do physical backups. Backups are managed by the MySQL Enterprise Backup utility.

Full Backups

MySQL Enterprise Backup does a hot backup of all tables that use the InnoDB storage engine.

— Backups can be performed while the MySQL server is not running.

— If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup. MySQL Enterprise Backup does this locking automatically for tables that require it.

For tables using MyISAM or other non-InnoDB storage engines, it does a “warm” backup, where the database continues to run, but those tables cannot be modified while being backed up.

Incremental backups

Incremental backups are really just backups of the logs created by transactions.

Incremental backups are made possible by enabling the server’s binary log, which the server uses to record data changes.

Maintenance

Unlike in the Oracle database, where there are practically no maintenance tasks, there are some tasks required for MySQL.

Capacity Planning for the MyISM Storage Engine

— To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run myisamchk in recovery mode.

— It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur.

Also truncating a file-per-table tablespace is very fast and can free up disk space for the operating system to reuse.

Stats Gathering

In Oracle MySQL, table statistics are done manually, but there may be situations where an autostats gather will be performed.

Paul Guerin is an international consultant that specialises in Oracle database. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.