Performance Schema (PS) has been the subject of many, many recent discussions, presentations, and articles.  After its release in MySQL 5.7, PS has become the main actor for people who want to take the further steps in MySQL monitoring. At the same time, it has become clear that Oracle intends to make PS powerful with so many features and new instrumentation that old-style monitoring will begin to look like obsolete tools from the Stone Age.

This article will explain PS and provide guidance on what needs to be done in order to use it effectively.

What I am not going to do is to dig into specific performance issues or address polemics about what PS is and what, in a Utopian vision, it should be. I have seen too many presentations, articles and comments like this and they are not productive, nor are they in line with my target which is: keep people informed on how to do things EASILY.

For the scope of this …

Oracle 12c introduces new catalog features including CDB_ dictionary views (which include a CON_ID column) superseding the DBA_ views that most DBA sqlplus scripts are based upon.

However, existing DBA sqlplus scripts can easily be modified using just a few simple sqlplus techniques to be compatible with 11g, as well as all types of 12c databases including legacy and container databases.

The following simple SQL and sqlplus techniques can be used to make a “universal script” that is compatible with all versions.

Illustrating the Issue

Let’s say for sake of example that we have a simple 10g/11g monitoring script that’s checking the amount of freespace in each tablespace by querying the DBA_TABLESPACE_USAGE_METRICS view.

On our 10g or 11g database the following query gives the necessary information:

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select …

The fsfreeze command, is used to suspend and resume access to a file system. This allows consistent snapshots to be taken of the filesystem. fsfreeze supports Ext3/4, ReiserFS, JFS and XFS.

A filesystem can be frozen using following command:

# /sbin/fsfreeze -f /data

Now if you are writing to this filesystem, the process/command will be stuck. For example, following command will be stuck in D (UNINTERUPTEBLE_SLEEP) state:

# echo “testing” > /data/file

Only after the filesystem is unfreezed using the following command, can it continue:

# /sbin/fsfreeze -u /data

As per the fsfreeze main page, “fsfreeze is unnecessary for device-mapper devices. The device-mapper (and LVM) automatically freezes filesystem on the device when a snapshot creation is requested.”

fsfreeze is provided by the util-linux package in RHEL systems. Along with userspace support, fsfreeze also …

First of all a disclaimer: I don’t work for Oracle nor do I speak for them. I believe this information to be correct, but for licensing questions, Oracle themselves have the final word.

With that out of the way, followers of this blog may have seen some of the results from my testing of actual CPU capacity with public clouds like Amazon Web Services, Microsoft Azure, and Google Compute Engine. In each of these cases, a CPU “core” was actually measured to be equivalent to an x86 HyperThread, or half a physical core. So when provisioning public cloud resources, it’s important to include twice as many CPU cores as the equivalent …

As a MySQL DBA I already know the data changes that happen on my system. I have logs for that.

However, it’s a common problem that several years into the life of an application, the current developers won’t know where in the codebase queries come from. It’s often hard for them to find the location in the code if queries are formed dynamically; the pattern I show them to optimize doesn’t match anything in the code.

I stumbled on a trick a couple years ago that has been invaluable in tracking down these problematic queries: query comments.

Here’s an example:

When a query generally shows up in a slow query log, it might look something like this:

# Time: 150217 10:26:01
# User@Host: comments[comments] @ localhost []  Id:    13
# Query_time: 0.000231  Lock_time: 0.000108 Rows_sent: 3  Rows_examined: 3
SET timestamp=1424186761;
select * from cars;

That logging shows me who executed the query …

Some of you know that I have published how to install Oracle VM Manager (OVMM) on a Dom0 host since Oracle released the Oracle VM 3. I have described why you possibly may want to do it in my very first post. Just want to mention here that it should be used for sandbox configurations only. You can find the previous post on how to install 3.1.1 OVMM version under Dom0 here. This time I talk about 3.2.2 version.

NOTE: At the time of writing ORACLE VM 3.2.3 SERVER (Patch 16410428) and ORACLE VM 3.2.3 MANAGER (Patch 16410417) became available. I didn’t have time to install the latest versions yet. However I do not expect that installation are significantly …

Friends,

I want to remind you to let us know if you would like to come for the dinner at Pedro’s on Tuesday April, 23.

Actually, ALL of you should come!

Pedro’s is asking us to have a better idea of the number of participants, so please go to this page “MySQL Community Dinner at Pedro’s 2013” and subscribe.

We want to have all of you enjoy the evening with us. Subscribe & come!

Once again, Pythian is organizing an event that by now may be considered a tradition: the MySQL community dinner at Pedro’s! This dinner is open to all MySQL community members as many of you will be in town for the MySQL Conference that week.

Here are the details:

What: The MySQL community pay-your-own-way dinner
When: Tuesday April, 23 – Meet us at 6:30 pm in the lobby of the Hyatt Santa Clara, or at 7 pm at Pedro’s (You are welcome to show up later too!)
Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054
How: Comment on this blog post to add your name to the list of probable attendees

Pedro’s can handle large groups of people, but we would like to have an …

I am very excited and thrilled to use the latest release of MySQL 5.6 in production. This is probably the most notable and innovative release in many years, if not ever.

During the last year, we had the chance to work with many new features and test the fixes to old issues. To be honest, I was expecting to have MySQL 5.6 GA before now, and I even wagered with my colleague Francisco that it would be out before the end of 2012. It was nothing special, just a beer in the Santa Clara Hyatt lounge. Unfortunately for me, MySQL 5.6 is now in GA and given that it happened in 2013, I lost the bet and now have to pay for that beer. But I have also lost the full list of things that we saw as relevant, interesting, or really innovative for MySQL.

So I took a step back, took some time, and reviewed what Oracle delivered in this new MySQL release.

Short premise

Oracle developer teams did great work for …

This is just a small bug-fix release of the plugin. It has actually been quietly released for a while now. If you have downloaded the plugin recently, you have the latest version. To be sure, check the version in the Console, or you can see it in the file name.

There are two bugs fixed:
1. Deployment on an OMS hosted on Solaris didn’t work. (And I suspect it could be the same for Agents on Solaris.)
2. Changing thresholds on the metrics caused the error “Modification of Target Monitoring Settings has Failed”. Also, applying monitoring template was failing for the same reason.

If you didn’t know that you could setup threshold and customized the thresholds that are set out of the box with the plugin, then you are not fully using Enterprise Manager. You can do that in the MySQL target menu — Monitoring -> Metric and Collection Settings. When alert or warning conditions are met, Oracle EM can raise …