Dontcheff

Archive for June, 2021|Monthly archive page

DBAs: 20 years after

In DBA, Oracle database on June 28, 2021 at 16:37

Oracle 9i was released 20 years ago. Oracle Real Application Clusters (RAC) and Oracle XML DB were cool new things except for a few OPS DBAs who even stopped playing ping-pong if you know what I mean.

Now, Oracle 21c is available from the public cloud and and several other database brands are competing shoulder to shoulder with the Oracle database.

However, DBAs still go their separate own ways, just like at the end of the book “20 years after” by Alexandre Dumas.

Some are still focused mostly into on-premises database work, some are very cloud oriented and some are positioning themselves into the golden middle. Just like the Three Musketeers.

The DBA profession still stays the same: solid, desired by IT experts and for good or for bad – still arguably the most complex job in IT. Complex in both cloud and off-cloud.

In a recent study, the DBA profession is #7 in Best Technology Jobs, #19 in Best STEM Jobs and #55 in the top 100 Best Jobs:

According to the study, Database Administrators made a median salary of $93,750 in 2019. The best-paid 25 percent made $120,880 that year, while the lowest-paid 25 percent made $68,340:

As we all know, it is still not all about the money… So, let us look at the rest.

Upward Mobility, meaning opportunities for advancements and salary are “Above Average”, same is the Stress Level, meaning work environment and complexities of the job’s responsibilities. But the Flexibility which means an alternative working schedule and work life balance is rated as “Average”.

I personally think that all 3 categories above are well rated, however there is another category called Job Security which totally depends on the DBA – it cannot be generalized.

It is worth reading Tim Hall’s What Employers Want : A Series of Posts and Learning New Things : A Series of Posts.

If you would like to know what will change, what new skills are required and how to work in hybrid environment, check The Cloud and Database Administration by Craig S. Mullins.

The Next Generation of DBAs is well described in Cloud DBA: The Next Generation of Database Administrator?

A DBA should be nowadays master of at least few database brands. There are several list and websites pointing towards the top/best databases but imho every database has its use cases and clients. Here are few:

Top 15 databases to use in 2021

Top 25 Best Database Management Software in 2021

6 Best Databases To Use In 2021

Best database software in 2021

And this one is from today, June 28th, 2021: Top 30 Most Popular Database Management Software: Complete List

Let me point out at the end that the Database field is rapidly changing – cloud native databases, NewSQL, etc. so DBAs are even more and more important. The application architecture is getting more diverse because of cloud and the newly emerging databases plus the transformation of traditional databases – think of Oracle Autonomous Database for instance.

As a DBA, there is always a database you like and prefer more than another one but once you are comfortable with the work you are doing, either on-prem or in the cloud – enjoy, learn new things and look into the future remembering you have chosen one of the best professions in the world!

Advertisement

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: