I get several emails each week that are from folks who have the basics mechanics of being a DBA but are looking to learn how to manage data. They can administer a database server but want to know how to get more out of that data. I now have an excellent book for them to refer.

My copy of Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL arrived last week. I need to disclose that I was originally hired by MySQL to replace one of this books authors, Roland Bouman. He told me a year ago that he was working on this book. And I have found that he is always investigating something interesting.

Now to buy a pair of those cheap terabyte disk drives and load up some data.

This book will end up being one of those tomes that DBAs will have dog-eared, stuffed with Post-Ittm notes, …

Just a quick update that the new MySQL Enterprise Monitor has reached RC readiness and is set for official launch in the next few weeks. This is version 2.1 and features:

• Enhanced Query Analyzer with correlation graphs so you can highlight and drill into spikes in key system resources to see the queries that were running at the same time
• Query specific execution graphs so you can track the "normal" behavior of your queries over time
• GUI support for Query Analyzer EXPLAIN generation
  
• Live feeds for MySQL product updates and alerts
• Live feeds for your open support issues
• New Advisor Rules and Graphs
• and some other things...

Here's a screenshot of the Query Analyzer and correlation graphs...

…

Partitioning, new with MySQL 5.1, has complicated interactions with queries and indexes.  If one isn’t careful it is easy to degrade performance.   For example, select queries that go with that grain (queries where partition elimination occurs) can be much quicker, but select queries that go against that grain can be much slower.   Queries that go against the grain must query each partition, so for a table with 12 partitions,  one query against that  table can result in 12 queries, one against each of the partitions.  An example of this would be a query against a month partitioned table that is looking to see how much activity a product had in the past 12 months. 

The ideal partitioning scheme would be a system where all queries only needs to access data from one partition.  This describes enterprise software deployed as a service where multiple enterprise tenants all …

<rant>

Oh dear. I was hoping that by now this would be a non-issue. It seems that many of us (including the majority of my clients) are late to the party.

People…it’s now September of  2009. Next month starts the last quarter of 2009.  If you have a 64-bit chip in your server for the LOVE OF GOD please run a 64-bit operating system. And easily 90% of the servers shipping have 64-bit procs. I don’t know of any shipping dedicated servers that are running 32-bit procs but I suppose there are some left somewhere in the universe.

Do you know how difficult and costly it can be to upgrade your server to a 64-bit OS? Costly in terms of money and downtime (which may as well be money). All because you didn’t realize when you bought the server that you would ever use more than four gigs of RAM. Or you thought you might have some trouble getting your application to work with the 64-bit libraries or whatever. …

Intalio acquires Jetty. Red Hat updates JBoss platform. $12m funding for Medsphere. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Intalio acquired Webtide, developer of Jetty application server.

# Red Hat delivered JBoss Enterprise Application Platform 5.0, as well as JBoss Operations Network (ON) 2.3 and launched Catalyst partner program.

# Medsphere raised $12m to support ongoing development and expansion in open source health IT.

This is a bit of a side-step from directly MySQL related information .. but I found myself installing Snow Leopard on my MacBook Pro today (which I use for pretty much everything) and every-time I do I have to try and remember all the applications I use. And I thought I would share them as I've found it useful you can pick up on things other people use. So here it is!

I have marked the applications which cost money with a green dollar symbol ($) and hyperlinked all the project names for convenience.

• 
  
• iLife 2009 - iTunes, iPhoto, iMovie $
  
• Built-in Mac Applications - Mail, Safari, Terminal
  
• iWork 2009 - Office Suite - Pages, Numbers, Keynote $
  
• …

Following requests received during earlier MySQL Cluster webinars, a new (and as always, free) webinar has been scheduled which focuses on MySQL Cluster Replication. The webinar is scheduled for Thursday 10 September and you can register at http://www.mysql.com/news-and-events/web-seminars/display-415.html

I’ll be on-line during the webinar, answering questions.

Details….

MySQL Cluster: Geographic Replication Deep-Dive

Thursday, September 10, 2009

MySQL Cluster has been deployed into some of the most demanding web, telecoms and enterprise / government workloads, supporting 99.999% availability with real time performance and linear write scalability.

Tune into this webinar where you can hear from the Director of MySQL Server Engineering provide a detailed “deep dive” …

So, it turned out fixing the Workbench crash on Snow Leopard wasn’t very hard. Took a little time to find out what was causing the crash but, once that was found, the fix was quick.

The next releases of both branches — MySQL Workbench 5.1.18 this week and 5.2.3 alpha later on — will have proper support for Mac OS X 10.6

For whoever might be interested, the bug was caused by what seems to be a slight change in how object/NSView copies are handed in Cocoa. NSCollectionView items are populated by copying a “prototype” object. It seems that somehow, duplication of that object included the fields of child objects as well in Leopard, but not in Snow Leopard. The object copies were incomplete and when they were accessed, it ended up attempting to create a C++ string out of nil.

I got to meet, and share a meal with a most interesting Darren Cassar at FRoSCon/OpenSQLCamp, who’s the mastermind behind SecuRich. Some sparse notes, while we await his slides. I think there’s some great potential here, and SecuRich is exciting and should be given some more love.

Designed to work with Sybase and MySQL in mind (because he’s hacking on migrating Sybase to MySQL).

How often do we audit user privileges and access levels? How often do we forget temporary elevated privileges?

What you have in MySQL today: Authentication against ‘username’@'hostname’, and the password is hashed by PASSWORD() function. There is wide range of privileges, and the granting of privileges is controlled.

What are limitations in MySQL today: Password limits are not available (password size …

I am using Oprofile to look into the performance of a Mysql Storage engine. Upon reading further into the oprofile docs I find:

"OProfile is oriented to finding problems with CPU-limited processes. OProfile does not identify processes that are asleep because they are waiting on locks or for some other event to occur (for example an I/O device to finish an operation)."

That is kind of a drag since the results I am expecting to see include the largest bottlenecks for IO and waiting for locks.

From my experience with gprof I seem to remember the same thing: CPU profiling primarily.

Does any one know of a good off the shelf tool that can integrate CPU profiling with IO? I don't even expect to see anything out there that can include time waiting for locks because it is not generic.