Configuring the MySQL Database Plug-In for Oracle Enterprise Manager 12c

I have blogged in the past about MySQL Enterprise Monitor 3.0 and I was quite happy at the very beginning, but after a while I have to admit that I was missing many of the Oracle Enterprise Manager 12c features.

In particular, MEM 3.0 does not have a usable database. In MEM all the tables are crypted and it is not possible to list, for example, all the targets monitored, nor it is possible via API or REST web services because MEM 3.0 lacks these features.

What makes EM12c a GREAT product comparing to MEM, are many features like blackouts, a usable command line interface (emcli), integrated reporting, scheduler, automatic groups… the list would be just huge.

Luckily, Oracle has officially released a MySQL plugin for EM12c, provided that the EM is at least in version 12.1.0.4.

So I’ve upgraded (a while ago) my customer’s EM12c to 12.1.0.5. and decided to try the plugin.

The first step is to download the last version of plugin for MySQL.

I can verify that you have the last version by going to

Setup -> Extensibility -> Self-Update -> Plugins:

2015-08-19 13_54_00-Plug-ins - Oracle Enterprise Manager

The agent has been downloaded, but in order to make it available on the targets, I first need to deploy it on the management servers (2 OMSes in my case):

2015-08-19 13_54_24-Plug-ins - Oracle Enterprise Manager

Check the plugin name and version:

2015-08-19 13_54_52-Deploy Plug-ins on Management Servers

Verify the prerequisites check (here I have one column per OMS):
2015-08-19 13_55_26-Deploy Plug-ins on Management Servers

Specify the credentials for the Management Repository:
2015-08-19 13_55_39-Deploy Plug-ins on Management Servers
Execute the deploy:

2015-08-19 13_55_52-Deploy Plug-ins on Management Servers     If everything went OK, I’m able to check the status of the deployment:

2015-08-19 13_56_02-Deploy Plug-ins on Management Servers

2015-08-19 13_58_01-Plug-ins - Oracle Enterprise Manager

Now I can see that the plugin is correctly deployed on the OMSes, I can do the same for the agents:

2015-08-19 13_58_42-Plug-ins - Oracle Enterprise Manager

2015-08-19 13_58_57-Plug-ins - Oracle Enterprise ManagerI must select one by one the agents that run on the hosts where I have MySQL running. I may select all agents as well, but it’s better to be neat…

2015-08-19 13_59_35-Plug-ins - Oracle Enterprise Manageragain, there are prerequisite checks and confirmations:

2015-08-19 13_59_48-Plug-ins - Oracle Enterprise Manager

2015-08-19 13_59_54-Plug-ins - Oracle Enterprise ManagerThe plugin deployment went well:

2015-08-19 14_00_02-Plug-ins - Oracle Enterprise Manager

2015-08-19 14_01_25-Plug-ins - Oracle Enterprise ManagerNow I can run the target discovery on the agent:

2015-08-19 14_01_49-Setup Discovery - Oracle Enterprise Manager

But the discovery does not find my MySQL targets. What went wrong?

2015-08-19 14_02_19-Setup Discovery - Oracle Enterprise ManagerEach agent has a default list of “discovery modules”  used for the discovery, but by default the MySQL one is not enabled after I install the plugin:

2015-08-19 14_02_50-Discovery Modules_ slv1314p - Oracle Enterprise Managerso it is necessary to activate it and deactivate the discovery modules I do not need:

2015-08-19 14_03_17-Discovery Modules_ slv1314p - Oracle Enterprise ManagerTada! at the next discovery, I have my target available:

2015-08-19 14_03_48-Setup Discovery - Oracle Enterprise ManagerThe target name is automatically set to hostname:mysqlport:

2015-08-19 14_04_01-Auto Discovery Results - Oracle Enterprise Manageras all discovered targets, I need to promote it to have it available for monitoring with EM12c:

2015-08-19 14_22_07-Promote Discovered Target - Oracle Enterprise ManagerThe target is available, now I can use most of the EM12c features to monitor my MySQL environment.

2015-08-19 14_22_56-All Targets - Oracle Enterprise ManagerHTH

Ludovico

First steps with MySQL Enterprise Monitor 3.0

During the MySQL Connect @ Open World, Oracle has announced the release of MySQL Enterprise Monitor 3.0.

I’ve had the chance to propose a POC on MEM 3.0 to a customer currently relying on MEM 2.3, so we set up a VM to make some tests on it. I want to share my very first experience with it.

The binaries

The binaries you can download from edelivery belong to the “base” 3.0.2 version. But the release 3.0.3 is available since November 1st: in order to get it you must download two patchsets from the My Oracle Support. (Patches & Updates).

mem3_patch_search

  • Patch 17721950: MySQL Enterprise Monitor Service Manager 3.0.3 for Linux x86 (64-bit)
  • Patch 17721960: MySQL Enterprise Monitor Agent 3.0.3 for Linux x86 (64-bit)

So, as it is for Oracle RDBMS, now the new versions are released as patchsets that can be installed from scratch.

 

The installation

The installation of the new product is as straightforward as it was for the previous release. You can still choose between using an existent MySQL database or a new one created my the installer. The installation steps have not changed at all, so I won’t list them here.

If you’re new to MEM installations, just refer to the official documentation.

 

New vest

MEM3 comes with a new graphic vest, definitely more “Oracle style” and eye-candy. Globally I have to say that the experience is really improved. The overview page resumes well the overall availability, workload and alerts.

mem3_overview

mem3_treeThe new graph browser allows to choose the correspondent object from the left tree-view pane. You can see from the screenshot that now the agent collects data from the system and regroup the items differently, e.g. the filesystems are now separated objects belonging to the server.

New agent behavior

The new MySQL Enterprise Agent brings some benefits comparing to the old one. Now a single agent installation can monitor all database instances on the server and actively discover new ones.
When a new instance is discovered is not monitored by default: an event is raised so you can create an handle that sends you an email that reminds to enable the monitoring for that instance.

mem3_auto_discovery

Other remarks

The Query Analyzer is now active by default for instances that have the performance_schema enabled.

Many new graphs are displayed by default providing great information on database statistics trends.

The Event Handling is simpler to manage IMO, but the only choices are still sending an email and/or an SNMP trap. I regret that it’s still impossible to take actions like hooking shell scripts or send WS calls.

The other functionalities are still present and basically unchanged.

Conclusion

Oracle have done a very good job in restyling everything. The new Enterprise Monitor dashboard is really responsive and I haven’t found bugs so far. But I have to say that there is still a lack of features that would make it really “enterprise ready”, such as a plugin mechanism that would allow to interact directly with common event management softwares like EM12c, Tivoli, Remedy or CA SM without relying on SNMP only. Also, it would be great to run actions directly when an event raises.

The POC however has been a complete success and we’ll start next week with the definitive migration! 🙂

Oracle Database 12c: MySQL C API implementation: a double-edge sword?

One of the new features of Oracle 12c is the new is the MySQL C API implementation for Oracle, so that all applications and tools built on this API can use transparently a MySQL or an Oracle database as backend.

API Reference for Oracle MySQL Client Library Driver


Oracle says that this will facilitate the migration from MySQL to Oracle, but I ask myself: Won’t be attractive for many developers to start developing applications with the MySQL API rather than with the Oracle libraries? This can potentially permit new applications to be migrated quickly in the opposite direction… (Oracle -> MySQL).

Time will tell.

Ludo

MySQL 5.6 Certification exams? Not yet (1+1=3)

(cc) Vincenzo Visciano http://www.flickr.com/photos/liberodicrederci/

(cc) Vincenzo Visciano

I’ve taken my MySQL 5.0 OCP certification in 2010. A little late because this certification has been around for longtime, but surprisingly, it’s still the only version for MySQL OCP certification.

The 5.0 branch started in 2003 and it went GA in 2005:

“MySQL 5.0.0, a new version of the popular Open Source/Free SoftwareDatabase Management System, has been released.”

Michael Widenius, December 24 2003

 

“It is my pleasure to announce the production release of MySQL 5.0, which is hereby GA (Generally Available).”

Kaj Arnö, October 24th 2005

If we make a comparison, it’s like if the certification release for Oracle was the 10gR1.

Yesterday I’ve received an email from Oracle University claiming new courses on MySQL 5.6 to add certifications to the resume:

“The MySQL for Database Administrators course is for DBAs and other database professionals who want to install and configure MySQL Server, set up replication and security, perform database backups and performance tuning, and protect MySQL databases. This course has been updated to cover commercial features of MySQL as well as enhanced replication capabilities in MySQL 5.6.”

Oracle University, May 20th 2013

 

I’ve started thinking that a new certification path on 5.6 was available, but on the Oracle site I see that the certification is always the same. They have just added a sentence in the preparation notes:

“This exam has been validated through MySQL 5.5. The recommended course is based on MySQL 5.6. Candidates wishing to use this course to prepare may wish to consult product documentation to ensure they understand the differences between MySQL 5.5 and MySQL 5.6”

I hope we’ll see something new soon!

Ludovico

JBoss Portal and MySQL scalability: What The…???

I found several queries running on a MySQL 5.0 database like this one:

This query is related to JBoss Portal and does a full scan on table JBP_OBJECT_NODE.

It has bad performances (>0.8 sec) with just a few records:

mysql> select count(*) from JBP_OBJECT_NODE;
+———-+
| count(*) |
+———-+
|    33461 |
+———-+

If I rewrite the query using an inner join (à la Oracle, please forgive me) instead of a subquery I get an index scan:

With 30k records the execution time falls down from 0.8 secs to 0.01 secs…
That’s NOT all! I found this open bug:

https://jira.jboss.org/jira/browse/JBPORTAL-2040

With many users registered in, the JBoss Portal Admin console tooks over a minute to show a single page…

I don’t like portals…