Quite often we need to perform a so-called “MySQL instance audit”. This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. You can use SHOW STATUS and check the list of system variables, but this way can hardly be called DBA-friendly.

Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I’d like to give a brief overview of MySQLTuner.

So, what can MySQLTuner do? Quoting the documentation: “MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.”

It’s not magic — they don’t use any “hidden” or unknown MySQL features to provide the report. What they do is use SHOW STATUS metrics and provide a user-friendly report, interpreting data this or that way. What makes such tools really valuable is the way they interpret that data.

(more…)

If you have created a new Oracle 11g database using DBCA and opted to use by default 11g’s enhanced security settings or, at least, the audit setting, then you risk the unlimited growth of the SYSAUS tablespace that hosts the audit trail table SYS.AUD$. I realized that while reviewing the slides of my presentation on 11g’s new features, a few of which covered security enhancements.

During my presentation at the TOUG meeting later that day, I mentioned that concern, and Mohamed El-Shafie from Oracle quickly noticed that there is no auto-purge. I promised to have another look at the maintenance tasks in 11g to confirm that, and indeed, the audit trail is not purged automatically when auditing is enabled by default.

Here is a quick remedy — scheduling an audit trail maintenance job.

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

create or replace procedure purge_audit_trail (days in number) as
  purge_date date;
begin
  purge_date := trunc(sysdate-days);
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' started');
  delete from aud$ where ntimestamp# < purge_date;
  commit;
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' has completed');
end;
/

Then let’s schedule a new maintenance job using Job Scheduler, which appeared first in 10g. Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, and we will use that to run the purge.

(more…)

I have recently stumbled upon V$SESSION_CONNECT_INFO view and discovered that it provides interesting information about client-side software and settings.

Using this view in Oracle 11g you can simplify collecting some statistics about database clients. Here is what can be extracted:

• Version of client libraries
• Type of OCI library used (standard OCI, different instant clients and etc.) but no JDBC support it seems
• Client characterset (new in 11g – doesn’t work for pre-11.1 clients and for JDBC thin)
• Authentication type (username+password, OS based, proxy and etc.)

How many times have you been in the situation when you need to know certain attributes of your clients to evaluate impact of a coming change of a bug you just hit? Unless the environment is very simplistic or just recently setup, there is usually no easy way to discover every client driver used to connect. This view can workaround absence of standard policies and documentation, procedure violations and you can confidently determine which client versions are where. You can even setup monitoring and pro-actively generate an alert when violations are detected which would be my preferred way.

If you decide to use this feature, you might want to create an AFTER LOGON trigger – sampling of could be not enough to catch short-living sessions.

I haven’t used this view myself and I’m writing this on the plane as I’ve just come across it in the documentation and though that this rather unknown feature would be useful on the blog.

It’d be nice to have that info available in audit views but I couldn’t find it in DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL. DBA_AUDIT_TRAIL has just column COMMENT_TEXT where we can see authentication type for session records.

Oh… which plane am I on? That deserves a separate blog post. Stay tuned!

PS: Oh… I’ve seen a ghost of Dave Ensor now on the plane… It’s probably not such a good idea to read Oracle manuals on the plane - it was just a man with very similar face profile. Yeah, I better switch to a movie then!

PPS: Actually, it took me few days to post this entry. Shame on me but better sooner then later… Oh… I mean better later than never!

If this post seems a bit like an “advertorial”, please believe me — it’s not. Well, at least it’s not an advertisement for Pythian in any way. What it is, however, is a post about a longstanding business partner of Pythian’s who run a very useful service I think more of you should know about. Rest assured there is nothing “in it” for me or for Pythian for writing this. (Doug, I look forward to hearing you out on whether this post was appropriate or not!)

As background, supply management as a discipline has grown leaps and bounds for managing the quality and cost of parts. I was reminded of this today as I was reading this outstanding analysis by the Boston Consulting Group of Toyota’s supply-management process and its related successes.

Supply management for services is a different matter altogether, and has not really achieved this level of discipline. I will save a blog posting on that subject for another day, as it turns out that the Pythian model actually enables some more advanced supply management methods to apply to infrastructure-management services. But as I’ve already said, this article isn’t about Pythian.

It turns out that there is a way to apply sophisticated supply management techniques to your database licensing costs in quite an efficient, turnkey manner, and that way is simply to outsource this work to Miro. I recommend this to Pythian customers all the time.

(more…)