Improved Indices for MySQL Reference Manual
I continue to try and find new ways to make it easier for people to get into the right place in the documentation for the information you are looking for.
This week, I’ve added some experimental custom indexes. These use information from the ID mapping process that we use in lots of parts of the documentation to do some interesting stuff.
The indexes are an extension of the other areas where we already provide summaries (like the list of options/variables, or functions). These provide quick access into the definition of the function, but not a link to where that function might have been mentioned elsewhere in the documentation. With the indexes, we’ve extended that so that you can now see all of the places within the reference manual where we mention a specific function. This goes beyond the index (which relies on us adding specific tags), and just lists everywhere that we mention a specific option.
Currently we only provide the new indexes in the 5.1 manual, in all formats, including online, downloadable and PDFs. The new indexes are:
- Standard Index - this is a version of the standard index, but with some minor improvements and adding extended support for see also and deeper index links.
- C Function Index - indexes all of the C functions used in the
libmysqland API. - Command Index - index all of the commands (
mysql,mysqladminand so on). We are aware of a few aberrations in the index. - Function Index - an index of all the SQL functions we support.
- INFORMATION_SCHEMA Index - points to all of the uses of the
INFORMATION_SCHEMA - Transaction Isolation Level Index - pointers to all the places we quote different isolation levels.
- JOIN Types Index - examples of the different join types supported in SQL statements.
- Operator Index - SQL operators.
- Option Index - options, both for the server, and client commands.
- Privileges Index - all the different privileges supported through the
GRANTsyntax. - SQL Modes Index - where we quote different SQL modes.
- Status Variable Index - status variables
- Statement/Syntax Index - where we use or provide examples of different SQL syntax and statements.
- System Variable Index - pointers to all the different system variables.
I am of course inviting comments and input from people to find out how useful you guys find this information. Assuming nobody has any objections, we’ll start rolling it out to the other reference manuals and documents.
Are there any other types of information presented in an index that our readers would like to see?
If so, let me know!!
MySQL 5.1 in OpenSolaris
If you’ve attended just one of my recent talks, either at the UC, LOSUG or MySQL University, you should know that MySQL 5.1.30 will be in the next official drop of OpenSolaris.
In fact, you can find MySQL 5.1 in the current pre-release builds - I just download build 111 of the future 2009.06 release.
Key things about the new MySQL 5.1 in OpenSolaris:
- Contains the set of DTRACE probes that also exists in MySQL 5.4 (see DTrace Documentation)
- Like the 5.0, we have SMF integration, so you can start, stop, monitor and change some of the core configuration through SMF
- Directory layout is similar to 5.0, with a version specific directory (/usr/mysql/5.1), and the two can coexist if you want to handle a migration from 5.0 to 5.1
To install MySQL 5.1, use the pkg tool to perform the installation. We’ve split the components into three packages:
SUNWmysql51contains the server and client binaries, and associated scripts.SUNWmysql51libscontains the client libraries, which you’ll need for all external (i.e. MySQL) tools, like DBD::mysql for Perl connectivity)SUNWmysql51testcontains the MySQL test suite
To install:
$ pfexec pkg install SUNWmysql51
Once installed, you can start MySQL server through SMF:
$ pfexec svcadm enable mysql:version_51
You can set properties, like the data directory, by using svccfg:
$ svccfg
svc:> select mysql:version_51
svc:/application/database/mysql:version_51> setprop mysql/data=/data0/mysql
svc:/application/database/mysql:version_51> setprop mysql/enable_64bit=1
Any questions, please ask!
Quick Access to CommunityOne Deep Dives
So the CommunityOne conference is coming soon, and earlier this week I finished up the final part of the MySQL/DTrace presentation and preparation so I’m all ready to go.
Want to come along? Get quick access to the Deep Dives:
MySQL Docs Updates and Stats
We got an interesting question on the documentation list the other day, which basically asked if we provided a service that listed changes to specific pages in the manual.
While I like the idea of such a service, the mechanics of making such a service work are very difficult. To start with, and to re-iterate something I have to explain again and again:
the MySQL documentation is rebuilt up to 10 times *every* day
We don’t have set schedules for when we release changes. We don’t bundle changes up and then produce a new reference manual on a set day of the month, or week. If I make a change to the documentation right now, there is every chance that you would see that change in the live docs at http://dev.mysql.com/doc within 3 hours, and for the PDF format, possibly even within the hour.
Yep, it happens that quickly. And that happens every day of the year - even at weekends and holidays.
It’s also worth pointing out some of the statistics of the documentation to help explain why such a system might be impractical, but not impossible.
Our current mysqldoc repo, in existence since we made the move to DocBook, has just hit revision 14955, which means over the last 3 years and 7 months, we’ve made on average 11.45 commits every single day. On some days, we make many more than that, and sometimes those changes can be minor (like a typo correction), and other times they will be a huge reorganization or rewrite.
But there-in lies the other problem with any kind of monitoring of changes - our documentation is big and complex. Any ‘page watcher’ to notify you of changes would be limited by the fact that we split the reference manual into just over 2000 different HTML pages. And of course we do that for each manual (4.1, 5.0, 5.1, 5.1-maria, 5.4, and 6.0). For those keeping score, that’s about 12,000 different HTML pages for the reference manual alone.
If you want to talk printed pages (and why not), the Letter-sized PDF manuals for the main documents in English (reference manuals, GUI docs, the MySQL version reference (15 docs) constitutes about 17,300 pages of content.
We actually create 91 different documents from our English language repository, and 234 documents across all languages in up to 14 different output types (PDF, HTML, texinfo, txt, etc).
So to return to the original question, would a page monitoring service be possible?
Probably.
Would it be useful?
I’m in two minds about this. I can see the value of having a way to see changes, but if what you want is to be able to monitor changes in behavior, then most of the time this is documented in the changelog, and we have a number of different outputs available for viewing the changelog information in more useful ways.
Other, more minor changes, and in some case major rewrites wouldn’t feature in the changelog (because they don’t relate to a change in the product we are documenting). Whether you would find such changes useful is up to you. A rewrite normally means we’ve decided to change the content to make it clearer, or we’ve re-organized the information to make it flow better. Again, it doesn’t necessarily constitute a change to the product that wouldn’t otherwise be in the changelog.
Would it be practical?
Probably not - the sheer size of our documentation would mean that just providing the data on the changes would probably double the number of pages and information, and it wouldn’t always be clear whether the change were just a typo correction or a major rewrite. And tracking the changes if we change a page ID (which we do, from time to time), would it difficult to correctly identify changes across different pages that really had the same content.
Of course, I’m open to suggestion here, and we are doing things to further improve and enhance the content, and maybe this is something we will consider. But I’m open to input on whether this is needed.
Speaking at CommunityOne West
Sorry for the (relatively) short notice, but I will be talking at Sun’s CommunityOne conference in San Francisco on June 1st.
I’ll be talking about, and demonstrating, the DTrace probes we have put into MySQL in a joint presentation with Robert Lor who will be doing the same for Postgres.
CommunityOne West Badge
Our presentation is on the Monday afternoon.
Check out the CommunityOne West Conference Site for more details and registration.
Solaris 10 Boot Failure
OK, so this has now bitten me twice on a new install.
Basically, put Solaris 10u5 on certain machines, and it will work fine until you edit the /etc/vfstab and forget to add a terminating newline to one of your entries.
Upon reboot you will get something like this:
Error: svc:/system/filesystem/root:default failed to mount /boot (see 'svcs -x' for details)
[ system/filesystem/root:default failed fatally (see 'svcs -x' for details) ]
Requesting System Maintenance Mode
Console login service(s) cannot run
At the top of the output from svcs, you’ll see:
svc:/system/filesystem/root:default (root file system mount)
Reason: Start method exited with $SMF_EXIT_ERR_FATAL
see: http://sun.com/msg/SMF-8000-KS
see: /etc/svc/volatile/system-filesystem-root:default.log
Impact: 44 dependent services are not running. (use -v for list.)
The problem is that missing newline, which means the mount table is never parsed correctly.
To fix, enter your root password to get into admin mode. You’ll need to first remount the root fs as read/write:
# mount -orw,remount
And then add that offending missing line end:
# echo >>/etc/vfstab
Be careful with that second line; miss a > symbol and you’ll wipe out your vfstab altogether.
Now reboot, and things should be back to normal.
Using MySQL with the Dojo Toolkit
The presentation file for the Dojo toolkit presentation at the Users Conference is now available.
You can find it on the conference session page.
I’ll be uploading the example scripts that work to produce the examples I gave once I’m back in the office after the conference.
Presentations now on MySQL Conf Site Too
So I talked to the wonderful JB from O’Reilly who is responsible for all sorts of parts of the conference, and she’s now enabled my presentations on the MySQL Conference Presentations page.
While you’re there downloading mine, make sure you go and download some of the others.
In particular, you might want to try:
- DTrace presentation which Vince Carbone did (and which I co-presented to provide a live demo of DTrace)
- Ann and Kevin’s Falcon Presentation
- Andrey’s look at the new Connector/C++
- Sheeri and Patrick’s Metadata piece, especially if you are interested in what is going on inside MySQL when you work with different components. I wish I’d been able to see this live.
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.
UC2009: Scale Up, Scale Out and High Availability: Solutions and Combinations
My presentation slides for the Scale Up, Scale Out and High Availability tutorial here at the MySQL Users Conference are now available for download:
Scale Up, Scale Out and High Availability: Solutions and Combinations
These have been on the MySQL Conference website for days now, but for some reason they don’t seem to have been freed yet.
To those waiting I’m sorry for the delay in getting these uploaded.
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!
MySQL Option/Variable changes in the docs
In amongst the other changes I made last week to provide the Reverse Changelog, I’ve also added some additional output to the Options/Variable chapter.
Basically, for the OptVars Dynamic Docs, we track when each option/variable had a change, either in the value or when it was introduced, deprecated, etc. We can now output those changes for a given version, either an individual one (for example 5.1.30) or toplevel (5.1).
The new changes are documented here:
As always, comments, suggestions welcome.
MySQL University: MySQL and ZFS
I didn’t announce it last week, but I did a MySQL University presentation on using MySQL with the ZFS filesystem. ZFS is the main filesystem for Solaris/OpenSolaris and offers a number of different benefits for people using MySQL, including some improvements to the ease of use, management, and performance of the storage you use with your MySQL database.
You can get the presentation, and the recordings of the presentation, from the appropriate MySQL University page: MySQL and ZFS.
Selected MySQL Docs now at docs.sun.com
We’ve decided to make a number of our docs available through Sun’s docs.sun.com documentation website, in addition to our main documentation site at dev.mysql.com/doc.
The documentation uploaded there is only a small selection of the total documentation, and at the moment, it is limited to full documents, such as the reference manuals, MEM docs, NDBAPI Guide and the MySQL Workbench:
- MySQL Reference Manual 5.0 (English)
- MySQL Reference Manual 5.1 (English)
- MySQL Reference Manual 6.0 (English)
- MySQL Cluster API (NDBAPI) Reference (English)
- MySQL Workbench (English)
- MySQL Enterprise Monitor 2.0 (English)
- MySQL Enterprise Monitor 2.0 (Japanese)
The versions on docs.sun.com will be updated each day (unlike our versions, which are updated multiple times each day).
Reverse Changelog
One of the pain points of upgrading any software package is knowing which version to upgrade to when the time comes. With software that is frequently updated, like MySQL, choosing a version that provides new features and addresses issues you are aware of, without also being exposed to other issues is an added complexity.
This week, I added another new feature to our documentation, spurred on by Baron Schwarz’s idea for a ‘reverse changelog’.
As Baron noted, the idea is to report the bugs reported in a specific version, and then provide information about the version in which each bug was fixed, so that you can determine which version you need to choose when upgrading to avoid that bug.
You can see the output for 5.1: Bugs Reported and Fixed in 5.1.
That page contains every version of 5.1, a list of the bugs known to be reported against that specific version, the bug synopsis/description, and the version in which the bug was known to be fixed. The reported version information is extracted from our bugs database. The bug fixed version is extracted from the changelog information - another part of the dynamic changelog functionality is the ability to find out information like this super easy when generating changelog related output.
I’ve done a lot of work over the last couple of years to improve the quality and content of our changelog. Although the basic structure of the changelog hasn’t changed much on the outside, from the inside, I’ve simplified and expanded the content and information that we track, and made the documentation team’s work processes easier to handle too. About 20% of our time in the docs team is tied up in keeping the changelogs up to date with both the changelog content and any relevant changes to the manual, so keeping that time down enables us to focus on the content.
On the outside, for our users, we’ve been publishing the enhanced ‘key changes’ information for more than a year now. This uses tags that we add to the changelog entries when we write them, and allow us to pull out the ‘key changes’, that is bugs tagged with Important Change, Incompatible Change, etc. into a single view.
Hopefully both the reverse changelog and key changes will be useful to everybody - and I thank (and will thank) Baron for the inspiration for the idea, largely made possible through our dynamic changelog system.
For those curious about the dynamic changelog and what it can do, you might also want to check out the Open Bugs for 5.1 list which also uses the information provided by the dynamic changelog to tell you when known bugs are expected to be fixed.
If anybody has any further ideas about the sort of information they might want out of the changelog, please feel free to let me know. As a rough guide, pulling out information based on specific tags (for example, all the bugs affecting a particular storage engine, partitioning, or specific platform) are easy to produce, as are changes between specific versions, ranges, or combinations of the above.
MySQL University Needs You!!
MySQL University keeps rolling along. We’ve had some fantastic sessions just recently (not including my own, of course!), such as Lenz’s presentation of backing up MySQL with filesystem snapshots, Allan Packer’s presentation on the optimization of MySQL and, going back a little further, David Van Couvering and Petr Pisl’s talk on using PHP and MySQL within Netbeans.
Remember that all of these presentations can be viewed again online if you missed them first time round!
We’ve got some good topics coming up, but we ned more!!
Got some hot topic that you want to tell the world about?
Using MySQL in an interesting way?
Got a good MySQL scalability story that you want to tell?
Developed a great storage engine?
Any and all of these topics are welcome for discussion and presentation at MySQL University.
On the other hand, perhaps you’ve seen one of our existing presentations and would like an update or follow-on session that goes deeper, or that enables you to ask more questions.
Whatever it is you want to see on MySQL University, let me know here or through the contact form, and get these things scheduled in before the slots all disappear!
MySQL University: Developing MySQL on Solaris/OpenSolaris
We have a MySQL University session later today, featuring me and Trond Norbye (who works on Memcached development).
Ostensibly we are talking about some of the tools and environment details of developing on Solaris and OpenSolaris, but with a MySQL focus.
I’ve just updated the Forge page with the requisite information and a copy of the slides if you need them. Here’s the abstract:
Developing MySQL on Solaris requires you to install a suitable compiler and other tools, but you may be surprised to know that most of the material is there already, or easy to install if it’s not. But even more so, there is a huge wealth of information that you can get about your application while it’s running, both with and without using more traditional debugging methods. We’ll cover setting up a suitable environment, where to find the things you need, and and how to make use of the process monitoring tools and debugging environment to get the best out of development on Solaris generally, and MySQL specifically.
Please attend if you have any interest in developing on Solaris/OpenSolaris and MySQL. Trond an I will also be on-hand to answer any questions you might have.
Problems with the CHM Refman builds
As some of you have noticed (and reported) we are having trouble with the CHM builds we do of the various MySQL Reference Manuals.
This is something that we are looking into, but the reasons and issues behind it are complex. One of the basic reasons if the sheer complexity and size of the documents are now building - there are thousands of files in a standard HTML build, constituting more than 2,500 pages of material.
Unfortunately, the final part of the build process, using Microsoft’s own HTML Help Compiler is something we have no control over, and Microsoft no longer support the product. Sometimes it produces a perfectly fine CHM from the source material. Sometimes, using the exact same material, it produces a build that is corrupt in some way.
The CHM building process is automatic - I improved it a couple of years ago to be automated as much as possible using a Windows Powershell script that performs nearly all of the work and verifies the build process. But Microsoft’s tool will report a successful build even if the build actually produces a corrupt CHM file.
Identifying a corrupt build is difficult; some of the CHM builds open fine on Windows but not in Linux CHM readers, some the other way round, some that don’t work in anything but which reported a completely fine build.
We are looking at different ways of addressing the issues, including creating simplified output (removing some of the more esoteric stuff), and releasing CHMs less often that have been manually checked.
I’ll let you know the progress, but until then, we’re going to be highlighting the CHM build issue on the download page.
My Sessions at UC2009
I’m speaking at the User Conference this year, with a half-day tutorial and three further sessions. The running theme is performance, both in terms of the performance of your queries, and in terms of scaling up.
Scale Up, Scale Out, and High Availability: Solutions and Combinations
This is the big tutorial. It’s difficult to resolve what I’ll be talking about into a few sentences, but think about all of the different technologies available here - replication, partitions, sharding, DRBD, memcached - I’ll be talking about all of them, and more importantly combinations of the different solutions and where the potential performance gains and pitfalls are. I’ll also be using the opportunity to demonstrate some of the more obscure combinations that you can use to provide the environment you need.
How I used Query Analysis to Speed Up my Applications
For query analysis, I’ll start with some of the basic methods available to us for performance monitoring, including EXPLAIN and DTrace, before I look at the query analysis provided by MySQL Enterprise Monitor. As an advisor to the group I’ve been looking at it for a while and used it on my own sites to identify a range of different query problems.
Improving performance by running MySQL multiple times
It isn’t talked about much, but there are times when running a single instance of MySQL doesn’t get you either the performance or environment that you need to support your applications. In this presentation I’m going to look at some of the benefits, from simply running multiple instances, to using solutions like VMware, Xen, LDOMs, BSD Jails, and Solaris Containers.
Using MySQL with the Dojo Toolkit
The final presentation is something a little more fun. The Dojo Toolkit is a JavaScript kit for developing AJAX applications. There are some really fun things you can do with Dojo, but getting the best combination and cool and efficient with MySQL is an art. We’ll look at two quick examples; the first is a browsable interface to large quantities of data. The other is dynamic graphing using MySQL as the backend.
If, within the bounds of any of these presentations there is something you would like covered, please let me know.