MCslp Coalface

UC2009: How I Used Query Analysis to Speed up My Applications

The presentation slides for my Query Analysis talk here at the users conference are now available:

How I Used Query Analysis to Speed up My Applications

I’ll sort out the scripts and bits and pieces I use once I get back to the office next week, for those people waiting for that material.

Tags: mysqlconf09, query analysis

UC2009: On the Starting Blocks

Despite an annoying 3 hour flight delay from Heathrow (and no, I wasn’t connecting there, I was leaving from there), I’m here in San Francisco and Santa Clara again ready for the MySQL User Conference 2009.

I obviously have my four presentations to get through, and there will be plenty of other stuff going on at the conference both in terms of other presentations, the show floor, the booths, and the Birds of a Feather sessions (which are terrific fun).

But mostly, I see the UC2009 as the best opportunity to meet up with both other MySQL/Sun people, our customers, and the community at large and talk about the thing we are all passionate about – MySQL and the technologies surrounding it.

And I don’t care if that sounds goofy. Nothing beats talking to face to face with like minded individuals, and one thing that MySQL – and Sun – seems to engender is an extreme passion about the products we deliver that I don’t always see in other products or companies. That means the talk is always interesting and intelligent, and, when it needs to be, good fun.

To anybody attending, welcome to the fun and enjoy the experience. To those not coming, you really are missing something.

MySQL Conf 2009 Preview: Scalability and HA Tutorial

Like most people, and with just over a week to go before the conference, I’m putting the finishing touchs on my various presentations.

First up for me, on Monday afternoon, is my tutorial: Scale Up, Scale Out, and High Availability: Solutions and Combinations.

What will be doing?

Very simply: Looking at every potential solution for maximum scalability and availability for your database environment.

If you are attending, be prepared to:

• Expand your mind as we think about scaling up.
• Expand your horizons as we think about scaling out.
• Divide and conquer, as we think about high-availability.

We’re not not hands on in this session – but I will expect you to be brains on!

Diarizing – what goes around, comes around

OK, so I’ve just written about my love of Moleskines and it occurs to me that today’s bloggers (myself included) are just continuing a long tradition for some people to share their experiences.

I write my memories in my Moleskines, not on a day to day basis, but whenever anything significant is happening in my life, or when I’m traveling about the world for business or pleasure, and for me that’s a vital part of me recording my memories, despite my otherwise excellent recollection skills.

Hundreds of years ago Samuel Pepys and many others did the same thing – diarized what was going on in their lives and the world around them, and it became part of the content that records our world history. I know not every blog is that interesting (my own including), but as the title says, what goes around, comes around – we are just carrying on the tradition of those great diarists that came before us.

I love my Moleskine

Not remotely related to computing at all, but I’ve just been updating my diaries, and I use Moleskine. I go everywhere with a Moleskine of some description (they’ve recently released some really tiny notebooks, which make it easier to carry your life around with you).

Despite having computers, organization tools, and email, there is something decidedly comforting about writing, by hand, into a physical notebook.

Of course, you write in pencil so that the contents don’t smudge, and somehow that just feels even better.

MySQL on Solaris Best Practices Presentation

A couple of weeks ago I was at the MySQL European Customer Conference in London, where I was presenting my talk on deploying MySQL on Solaris best practices. You can download a copy of the presentation here: MySQL on Solaris Best Practices.

I cover both choosing the best release version, using tricks like mtmalloc (the threaded malloc library) before moving on to UFS and ZFS tricks, using DTrace and MySQL Cluster and Sun Cluster.

BBC Quiz: Old computers

The BBC has a great quiz on old computers, most of which I’m happy to admit that I have actually owned.

Despite this I only 7 out of 10, but I still think that’s a respectable score.

MySQL Documentation Myths

There are a few myths surrounding the MySQL documentation and how it works, and I thought I’d try and dispel some of those myths if I can. If you have any more questions or misunderstandings you want clarified, let me know.

Myth:

MySQL Documentation is written by the developers.

Reality

MySQL Documentation is written by a dedicated team of writers with help and input from the developers. There are four main writers, Paul DuBois, Tony Bedford, Jon Stephens, and MC Brown (me!), plus our Team Lead, Stefan Hinz.

All the documentation staff are employed full time for the sole purpose of writing documentation. Sure, some of us get involved in other things too, but that’s basically the nature of the job. Some of us simply cannot help ourselves.

Myth

Docs team members are just writers and have no technical expertise.

Reality

It’s tempting to come back with a rude response to this one, but it is a comment I heard from someone at a conference. The reality is that all of us have some technical background, unsurprisingly often with MySQL. Some of us have expertise elsewhere too. Speaking only for myself, go look at MCslp.com for more info. If you want details, feel free to ask, but know that this myth is definitely busted.

Myth

The documentation is updated very rarely.

Reality

Our main tree, mysqldoc, is publicly available, and if you want to go view the commits to that tree, please feel free. It doesn’t take much to see that we commit to that tree all day, and every time we change something, the documentation gets rebuilt. How frequently? Well, on a typical day we will generate 10-15 new versions of each reference manual. It’s actually difficult to rebuild more frequently than that due to the sheer size of the documentation.

If you want to check the build date of the documentation, check the intro/preface of each document. The build and build date information is included there.

Myth

The MySQL documentation is small and unused.

Reality

You’d be amazed how many people need to be told RTFM, but a surprising number of people who criticize the MySQL documentation have actually never read it, or, they looked at it years ago and haven’t bothered to look recently because they couldn’t find what they were looking for before.

The reality is that our documentation is over 2000 pages per reference manual, which means over 10,000 pages now just for MySQL. There are hundreds more pages on the GUI tools, Workbench, Cluster/NDBAPI, and the Enterprise Monitor.

As to the popularity, the hits to the online pages of our manual exceed the hits to every other section of the MySQL website by a significant factor. For the downloadable formats we get an average of 200,000 downloads in all the various formats each month, with occasional spikes up to 800,000. For the online manuals, the documentation pages make up about 45% of all the traffic on mysql.com. Or to say it another way, we account for almost half of all the web traffic that MySQL receives, including downloads.

In short, we have no shortage of interested readers.

Myth

Docs team don’t read comments

Reality

Actually, we all get an email each time you post a comment and all of us will read it, determine whether it is suitable, useful, or (occasionally) spam, and either ignore it, delete it, or comment on it accordingly. Often that will happen within minutes of your leaving the comment. If there’s a non-standard reply to your comment, you’ll get that too.

Now, we are aware that the comments system has it’s faults. For one, we have one comment system for all the different versions of the manual, which means comments can be confusing and even misleading. We’re fixing that. We’re also trying to address the problem that some comments are really tips, while others are just plain comments and observations.

Any other comments or criticisms, let us know. We may not be unaware of the problem, but if we know your pain we can do something about it.

Myth

Docs team don’t accept bugs or corrections

Reality

You can report a bug or correction to us using the standard bugs.mysql.com, or drop us an email to docs@mysql.com.

Myth

Docs are ‘closed source’

Reality

The docs are not closed source – you can download the DocBook XML and the files and tools required to build them (well, beyond the XML parsers, Perl, and other bits and pieces). You can get hold of the repos (via SVN), on the Tech Resources page.

That said, we don’t allow anybody to commit changes, but see the response above for information on how to provide changes and fixes. Again this is something we are working to improve on.

Myth

MySQL Documentation is not distributable

Reality

This mostly comes out of the fuss around Debian dropping the man pages from their MySQL distributions You can see the description why here: MySQL Documentation and Debian/Ubuntu.

The short answer is that it is a mis-understanding in our license for the documentation, which is not released under the same license as MySQL. You can provide documentation if you provide MySQL, but not on it’s own. The reason for this is that our documentation is updated so regularly that we want to ensure that we only get genuine, up to date, versions of our documentation out there. Trust me, do a search for MySQL and some term and you will find versions of the manual that are months, or even years out of date, which is no help to anybody.

It’s about trying to make our documentation readable and usable and not misleading.

OK that’s enough myths busted today, but if you hear any more, or just have additional questions, feel free to ask.

DimDim and MySQL University

Stop the press! My boss, Stefan Hinz, has just started blogging, with his first post here: Using NetBeans with MySQL.

So who is he? Well, Stefan is the guy that keeps the rest of us in the docs team in check and makes sure we do what we’re asked, when we’re are asked and that all of the machinery, legalities and management tasks happen in the background. Without him we really couldn’t function as effectively as we do.

It’s wonderful to see some other Docs team members getting in on the act (to be fair to the rest of the team, Jon is also a blogger). We are all writers, you would think the blogging would come as a natural extension.

Behind the tease is the simple fact that the improved system for MySQL University I was talking about is getting a trial run this week.

We’ve been trying out Dimdim for web conferencing with David van Couvering and I have to say I’m pretty impressed.

We chatted, we tried out screen-sharing, presentations and the whiteboard functionality and it all worked really nicely.

We’re going to be using it for the MySQL University session this week, Using MySQL with NetBeans. Space will be limited, but feel free to join us if you can.

How to analyze memory leaks on Windows

We use valgrind to find memory leaks in MySQL on Linux. The tool is a convenient, and often enlightening way of finding out where the real and potential problems are location.

On Windows, you dont have valgrind, but Microsoft do provide a free native debugging tool, called the user-mode dump heap (UMDH) tool. This performs a similar function to valgrind to determine memory leaks.

Vladislav Vaintroub, who works on the Falcon team and is one of our resident Windows experts provides the following how-to for using UMDH:

1. Download and install debugging tools for Windows from here
   MS Debugging Tools
   Install 64 bit version if you’re on 64 bit Windows and 32 bit version
   otherwise.

2. Change the PATH environment variable to include bin directory of Debugging tools.
   On my system, I added
   C:\Program Files\Debugging Tools for Windows 64-bit to the PATH.

3. Instruct OS to collect allocation stack for mysqld with gflags -i
mysqld.exe +ust.
   On Vista and later, this should be done in “elevated” command prompt,
   it requires admin privileges.

   Now collect the leak information. The mode of operation is that: take the
   heap snapshot once, and after some load take it once again. Compare
   snapshots and output leak info.

4. Preparation : setup debug symbol path.
   In the command prompt window, do

   set _NT_SYMBOL_PATH= srv*C:\websymbols*http://msdl.microsoft.com/download/symbols;G:\bzr\mysql-6.0\sql\Debug

   Adjust second path component for your needs, it should include directory
   where mysqld.exe is.

5. Start mysqld and run it for some minutes

6. Take first heap snapshot

   umdh -p:6768 -f:dump1

   Where -p: actually, PID of my mysqld was 6768.

7. Let mysqld run for another some minutes

8. Take second heap snapshot

   umdh -p:6768 -f:dump2

9. Compare snapshots

   umdh -v dump1 dump2 > dump.compare.txt

10. Examine the result output file. It is human readable, but all numbers are
    in hex, to scare everyone except geeks.

11. gflags -i mysqld.exe -ust

    Instruct OS not to collect mysqld user mode stacks for allocations
    anymore.

These are 10 steps and it sounds like much work, but in reality it takes 15
minutes first time you do it and 5 minutes next time.

Additional information is given in Microsoft KB article about UMDH
KB 268343.

Tags: debugging, MySQL, Windows

Book: Intellectual Property and Open Source – the solution to IANAL

I’m reading Intellectual Property and Open Source by Van Lindberg at the moment, and despite being about a relatively dry topic, I must admit that it’s a fascinating read.

Van Lindberg introduces the book by talking about the comments that end up on Slashdot.org, almost certainly prefixed by the expression IANAL (I Am Not A Lawyer) where people defend, discuss, and rip people up about the legalities of open source and the various licenses. Van Lindberg also talks about how he spends much of his time translating the contents of various legal documents into engineer speak and back again.

Despite being a proponent and long time user of free software and open source for the best part of my working life, I’ll admit to being completely ignorant of many of the issues. This isn’t through lack of interest, but I’d rather leave those discussions and decisions to people who know, and it’s clear that Van Lindberg not only knows the subject, but he also knows how to make it interesting to those of us who actually have to work within the confines of rules and regulations.

I’m still reading and learning a lot of the ins and outs of copyright, company agreements, and individual licenses and details. There’s a lot of material and detail included here.

I’ll have a full review when I’ve finished. Until then, if you have even a passing interest in the various licensing, legal and IP issues with open source, check out the book for a proper read.

MySQL University – quick survey

MySQL University has been running for the last 18 months, and we’ve covered a wide range of topics, from the internals of MySQL right up to Amazon’s EC2, using MySQL in the Solaris/OpenSolaris Webstack and a description of the forthcoming MySQL Online Backup.

Personally, I think they’re great. Obviously many times I am scribe and am there for the sessions, but I listen to lots of the sessions anyway, and I’m yet to be disappointed by the content. What’s really great is that in all the cases the person you are listening to is probably the person that either developed, or helped drive development of the particular function, or, in the case of some of the external tools (EC2, for example), these guys are expert in it. The experience is not quite as thrilling as attending the MySQL User Conference, but the content is just the same.

The problem is that despite all the work we do to get the presenters, interesting topics, and promotion of the upcoming sessions, we don’t always get as many attendees as we want or expect.

So, I’m wondering why this should be the case. We know that the current presentation system is not ideal (and we’re working on that), but I’m interested to hear people’s opinions on MySQL University. If you want to help shape the future of MySQL University, then comment here, and either answer the questions below, or make up your own.

• Have you attended any MySQL University sessions. How many?
• How would you rate the sessions generally? A simple good or bad will do
• If you haven’t attended any sessions, or don’t regularly attend them, why not?
• Have you ever looked at/listened to the past sessions that provide on MySQL Forge?

Please, I’m interested to hear.

Tags: MySQL

General Purpose vs Specialized systems

Yesterday at Computerworld I talked about the X4150 and how it fits into a general purpose piece of hardware that can easily be redeployed and reused.

Interestingly I’ve just found this item in my blog post queue from way back in 2006, Jonathan Schwartz: The Rise of the General Purpose System, where Jonathan pretty much spells out that general purpose was the way to go. Obviously the X4150 is an embodiment of that belief.

Wanted: GUI developer for MySQL Enterprise Monitor

One of the great things about working on great products is that you get to meet such intelligent and interesting people. I can apply that to everybody that I work with, but there are some teams where not only are they working on great products, they are also all great people just to spend time with.

I have had the good fortune of working with the MySQL Enterprise Monitor team as an advisor, and more recently in writing the documentation, for the last 18 months. We’ve had some great fun at meetings in Amsterdam, Heidelberg, Santa Cruz and recently Riga. In the meeting rooms we are professional, but fun. But in the evenings we’ve gone out and just had plain good fun. Unless you’ve experienced a full day, or even week, of non-stop meetings for 12 hours a day you have no idea how important it is to kick back in the evenings. That, as a team, we are still able to have a good time at the end of each day while still being in the same room together is a good indication of how well we all get on.

Why am I telling you this? Because there is an opportunity to come and work for MySQL Enterprise Monitor team as a GUI developer, and if you are going to join us, you need to be as much fun at the dinner table as you are in the meeting room.

We are looking for talented people, obviously, and you are going to need to be good both with the design and the programming aspects of the GUi development. We use frameworks like Hibernate and Spring, and build our interfaces using DHTML and AJAX. If you know about MySQL and scale-up/out environments, that would be even better.

If it sounds like a good fit for you, and you just happen to want to have a good time to boot, apply here.

What they don’t tell you on the official pages is how much you will be expected to also get on with the rest of the team. It’s really important, because when we have those meetings we’ll be looking to the new guy to provide us with entertainment and interesting stories, at least until the next guy comes along.

Automatic example generator

I wonder whether there is a market for creating an automated example generator for different projects.

I’m just doing a piece on XML schemas and would love to be able to create a ready to run sample of SOAP and WSDL XML files without having to actually create the example by hand. Just a quick ‘give me this function, these arguments’ and have it generate it.

Years I go I thought about this for things like contacts and book projects.

More MySQL UC 08 Videos

Hopefully you can’t get enough of the UC08 videos (and thanks to Sheeri for the link with the full Jonathan keynote video), so Zack has managed to get some most posted.

This morning, we learned what it meant to be a pirate in terms of patents, copyright and now politics with the Pirate Party. Don’t let the scary name put you off – these guys are about making all of us consumers (of software, video, audio, books, etc.) more in control of information. Please support these guys by visiting Piratpartiet.se.

Next we had the Scalability Panel with representatives from Facebook, Fotolog, Sun, YouTube, Flickr and Wikipedia talking about the problems (and some of the solutions) they have taken to approach the bane of any web company.

One option for scalability of course is just not to cover the problem yourself. Skip the issues of server rooms and use Amazon’s various services (EC2, S3, etc). If you need convincing, try Werner Vogels’ talk.

Yesterday, in a short but sweet visit to the podium, Rich Green talked about MySQL and Sun and it will all work. If you have concerns about the integration, this is a good place to get the situation from the guy who will make sure it doesn’t go wrong. And of course Rich will answer to Jonathan.

Marten talked about the significance of Storage engines and the significance of your data.

For a full list, try Zack’s profile page.

Tags: MySQL, mysql08, mysqluc08

MySQL UC 08 Keynote Videos

I’m pleased to say that I was able to see these in the flesh, but if you aren’t lucky enough to be here (or just want to watch them again), Zack has posted up videos on YouTube of the opening keynote presentations:

• MySQL Conference Keynote 2008 – Marten Mickos
• MySQL Conference Keynote 2008 – Jonathan Schwartz
• MySQL Conference Keynote 2008 – Werner Vogels

Sadly these are only snippets, but if you like what you see, make sure to book your place for next year’s conference early!

Tags: MySQL, mysql08, mysqluc08

Cheffy 2.0 beta now out!!

I’m pleased to announce that we have release the new version of Cheffy, what we’ve been calling internally, Cheffy 2.0.

The new version comes with a number of new features, some of which I’ll be detaileding over the coming weeks, but as a headline:

• New layout and structure
• New Inspire Me section for quick access to selected recipes
• Your own personal Kitchen area where you can manage your preferences, your cookbooks, recipes, shopping list, and connect with other users.
• Add your own recipes – you can now add a recipe, have the nutrition automatically calculated, and then share that recipe with others.
• RSS feeds for your cookbook, recipes, and Inspire me and automated searches
• Improved searching and nutrition information

And there’s more to come – please start using the site and let us know what you think, or if you find any problems.

New VoiceXML/XQuery Demo

I’ve got a new VoiceXML/XQuery article coming out, and IBM have asked that a demo of the service is live.

The service is an interface RSS reader – you get to choose the topic and the feed (currently only four static feeds are provided), then it will read out the feed content.

You can try out the demo by calling:

• Skype: +99000936 9991260725
• US (freephone): (800) 289-5570, then using PIN 9991260725

Occasionally the hosting times out, in which case, please contact me and I’ll check it out and restart or reboot the service.

Comparing 32-bit/64-bit MySQL on OpenSolaris

I’ve been working with the folks working on OpenSolaris for a few months now providing advice and input on getting MySQL and the connectors (C/ODBC and C/J) installed as a standard component. Having got the basics in, the team are now looking at adding both 32-bit and 64-bit packages.

The question raised at the end of last week was whether OpenSolaris should enable 64-bit builds by default in 64-bit installations, and whether there was a noticeable performance difference that would make this worthwhile.

I did some initial tests on Friday which showed that there was a small increase (10-15%) of the packaged 64-bit installations over 32-bit under x86 using snv_81. Tests were executed using the included sql-bench tool, and this was a single execution run of each package for 5.0.56. Transactions are missing because I hadn’t enabled transactions in the tests.

There are some significant differences there (like the 55% increase on SELECT speeds, for example), but a single execution is never a good test. Also, it’s unclear whether the differences are between the compilations, the platform or just pure coincidence. This requires further investigation.

As a coincidence, Krish Shankar posted these notes on using SunStudio 11 and SunStudio 12 and the right compiler flags to get the best optimization.

I decided to do 10-pass iterations of sql-bench and compare both 32-bit and 64-bit standard builds, the 32-bit standard builds against Krish’s optimizations, and finally 32-bit and 64-bit optimized builds.

Some notes on all the tests:

• All builds are 5.0.56
• All tests are run on SunOS 5.11, snv_81
• Tests are executed on the same OS and machine running in 64-bit. The SPARC tests are on an UltraSPARC IIIi@1.28GHz Workstation with 1GB RAM; x86 are on a Dell T105, Opteron 1212 with 4GB RAM. Of course we’re not comparing machine speed, just 32-bit binaries over 64-bit.
• All results are in seconds; lower values mean faster performance.
• In all tests I’m using the built-in defaults (i.e. no my.cnf anywhere) so as to simulate a standardized installation.

Let’s first look at x86 and the 32-bit standard and 32-bit optimized builds:

The standard build uses gcc instead of SunStudio, but I don’t get the same performance increases that Krish saw – in fact, I see reductions in performance, not improvements at all. I’m going to rebuild and retest, because I’m convinced there’s a problem here with the builds that I’m not otherwise seeing. I certainly don’t expect to get results that show a 27% reduction in insert speed. That said, a 10% big-table increase is interesting. I’ll redo these builds and find out if the slow down is as marked as it here.

Here’s the comparison for standard builds between 32-bit and 64-bit standard builds on x86:

There are some incredible differences here – more than 50% increase in SELECT, and 30% for the big-tables test show that there is some advantage to having the 64-bit builds on x86 enabled.

Unfortunately I’ve had problems with the 64-bit optimized builds on my machine, so I haven’t completed optimized test comparisons.

On SPARC, Sun Studio is used as the default compiler, and the standard 32-bit and 64-bit show little difference:

Overall, a pretty insignificant difference here.

Now let’s compare the standard and optimized builds using Krish’s flags on SPARC:

The tests here show little significant difference between the standard and the optimized builds, although 6-7% would probably be enough to prefer an optimized build if you wanted to build your own.

Now let’s compare the optimized, Sun Studio 12 builds running in 32-bit and 64-bit:

The differences are virtually non-existent, and taking the reductions and increases in performance overall, there’s probably little difference.

The overall impression is that on x86 the improvement of 64-bit over 32-bit is significant enough that it’s probably a good idea to make 64-bit the default. On SPARC, the difference in the optimized builds is so slight that for compatibility reasons alone, 32-bit would probably make a better default.

I’ll probably be re-running these tests over the next week or so (particularly the x86 so I can get a true comparison of the 64-bit optimized improvements), and I’ll try the T1000 which I upgraded to snv_81 over the weekend, but I think indications are good enough to make a reasonable recommendation of 64-bit over 32-bit.