Day 0 (tutorials), post 2. (I’m still working on the versioning thing — bear with me.)

I snuck into the 5.1 new features session halfway through but definitely well worth it. Lots of new features coming out in 5.1 though Brian Aker admits some of them are not quite production-ready yet, so don’t go deploying it just yet.

Some of the features are:

• partitioning — range, list, hash, key, even composite. And its for all MySQL storage engines.
• faster multi-threaded loads of data instead of only having a single thread (for import / load data infile)
• events / event scheduler (like jobs/job scheduler)
• more system tables: the general log and slow query log are now system tables and you can run regular queries on them.
• the slow query log can be turned on or off on the fly
• row-based replication
• new storage engines
• more near-online operations (some alter tables, drop / create indexes but not all)

I had a good discussion with the MySQL certification team at lunch, we will go into more details when we meet Wednesday evening — I was invited to be on the certification technical advisory board earlier.

(more…)

It was a nice enough flight from Ottawa to Chicago to San Jose, if a little long. Security (or the baggage handlers) completely trashed our boxes of information folders, but I think we managed to salvage some… so if you’re looking for information on Pythian, come see us early.

The weather here is just like Ottawa. It’s probably just the few weeks of the year when it’s like that, but it does feel nice.

I have had my usual two-dozen cups of coffee, so I am ready to dive in to the first day of conferencing.

The first tutorial is on Scaling and High Availability Architectures. The room is packed beyond capacity, which should give you an idea of the interest in this topic.

Jeremy and Eric are doing a great job. I have gone over the handout material they have given out, and it’s very detailed, covering pretty much everything related to this, from a single shared hosting server to a bunch of standalone database servers, to data partitioning and replication.

Perhaps its a testament to the wide variety of clients we see at Pythian at different stages of the game, or perhaps to the fact that there’s just so much one can do a with a database engine, that I haven’t seen anything new or earth-shattering yet, though it’s all good solid information for someone looking to scale their database.
I’m still looking for that perfect solution where I can scale a database just like I can scale webservers behind a load balancer. If there is a DBA heaven, that’s what it would be. But until then we have to do it all manually, all the way from newborn to adult mature systems, as Jeremy put it so elegantly.

I’ll be attending the tutorial on Wikipedia internals next, and I will write about that too. There’s a huge amount of data on Wikipedia, so I’m guessing data partitioning is already in place, as is a lot of replication and caching. But thats for later!

–Raj.

I will be out of the office all next week, so expect more blog posts from me! That’s not a joke — both Yannick Gratton and I will be at the MySQL Conference in Santa Clara, taking in all the material we can, processing it, and spewing out what I hope will be some useful blogs. Watch this space!

If you’re going to be at the conference, do drop by and say hi. If you don’t know how to find me, drop in to Ballroom B at 3:35pm on April 24 when I’m doing a presentation on “Real World MySQL Tuning” — some quick and dirty tips to get the most out of an out-of-the-box MySQL install. And if you’re not already registered, there are details and links to the conference website below. There’s still time — be there!

<shameless plug>
Meet our marketing guy Martin too… he will be presenting more information about The Pythian Group right after my presentation, same place.
</shameless plug>

Cheers!
–Raj.

The MySQL Conference Conference & Expo 2007

The conference is taking place in both the Santa Clara Convention Center
and the Hyatt Regency Hotel.

Convention Center
5001 Great America Parkway
Santa Clara, CA 95054

Hyatt Regency Hotel
5101 Great America Parkway
Santa Clara, CA 95054
Phone: (408) 200-1234

If you have found rman backups slow in Oracle 10.2.0.2, you should check out note 375386.1 on metalink. It seems the CBO isn’t all its made out to be, and you have to force rman to use the RULE hint instead to make your rman backup run faster. (And you thought RULE was deprecated, didn’t you!)

A few interesting things come out of that Oracle note.

1. Tracing an rman session: connect to rman and set a 10046 event:

   rman target / log rman.log trace rman.trc debug
   RMAN> sql "alter session set events ' '10046 trace name context forever, level
   12' '"
   

   Note the two single quotes before 10046 and two single quotes and a double quote after level 12

2. Setting rman to use the RULE hint instead of CBO: add the following to your rman script:

   sql "alter session set optimizer_mode=RULE";
   

3. In spite of what Oracle says, there really is no end of the road for the RULE hint just yet.

The underlying bug# is 5247609 if you’re interested.

Of late, Oracle has had the tendency to automate a lot of the usual DBA tasks.

I didn’t complain on autoextensible datafiles, I just had to make sure my disk had enough space to handle the maximum potential space requirement. I also didn’t complain too loudly when a file extended to a size that triggered an Oracle bug and caused the database to crash irrecoverably. It was 8i after all, and things have improved since. Or have they?

I was mildly amused by automatic PGA and for the most part it worked well.
The default dba_scheduler job to refresh statistics interfered with my own job to gather statistics, and the schedule was the worst possible for the db I first saw it on, after an upgrade from 9i to 10.1 — but that was a minor irritant and quickly resolved.

However, I have to draw the line at automatic SGA management. (more…)

Today is SysAdmin day, don’t you know. Right up until last year, DBAs were included in this global movement to celebrate systems administrators. The Internet Wayback Machine gladly shows us how the site looked last year, clearly including DBAs right in the home page.

This year? No mention of us!

Craig Mullins, at DBAZine, says that they include us: “And before anyone questions this, the celebration includes DBAs (it says so right on the web site),” he writes. Well Craig, not this year they don’t.

Which kind of makes sense. DBAs have a unique role in an organization, and it was unfair to lump them with the common SysAdmin anyway :)

So DBAs will now have their own day. I have registered ‘dbaday.com‘ and am looking for ideas for which day to call our own. The current leading candidate is August 23, given that its Codd’s birthday. Feel free to lobby for other days in the comments below, we’ll make a decision soon and I’m trying to get Paul to approve a budget for a DBA of the year prize.

–Raj.