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:

  1. Contains the set of DTRACE probes that also exists in MySQL 5.4 (see DTrace Documentation)
  2. Like the 5.0, we have SMF integration, so you can start, stop, monitor and change some of the core configuration through SMF
  3. 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:

  1. SUNWmysql51 contains the server and client binaries, and associated scripts.
  2. SUNWmysql51libs contains the client libraries, which you’ll need for all external (i.e. MySQL) tools, like DBD::mysql for Perl connectivity)
  3. SUNWmysql51test contains 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!

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.

Media_httpcoalfacemcs_kjoba

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 ModeConsole login service(s) cannot runAt 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_FATALsee: http://sun.com/msg/SMF-8000-KSsee: /etc/svc/volatile/system-filesystem-root:default.logImpact: 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,remountAnd then add that offending missing line end: # echo >>/etc/vfstabBe 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.

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.

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.

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 CombinationsThis 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 ApplicationsFor 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 timesIt 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 ToolkitThe 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.

DTrace in MySQL: Documentation and a MySQL University Session

DTrace has been something that I’ve been trying to get into the MySQL server for more than a year now.After a combination of my own patches and working with Mikael Ronstrom and Alexey Kopytov we finally have a suite of probes in MySQL 6.0.8. Better still, after a short hiatus while I was busy working on a million-and-one other things, the documentation for those probes is now available: Tracing mysqld with DTrace. The documentation is comparatively light and deep all at the same time. It’s lightweight from the perspective that I’ve added very little detail on the mechanics of DTrace itself, since there is no need to replicate the excellent guides that Sun already provide on the topic. At the same time, I’ve tried to provide at least one (and sometimes two) D script examples for each of the groups of probes in the 6.0.8 release. So what next for MySQL DTrace probes? Well, the next version of the probes has already been worked on. I’ve been testing them for a month or so, and due to a problem with the probes on SPARC I couldn’t approve the patch, but I managed to resolve that last week. The new patch extends the probes to enable a more detailed look at certain operations, and it enables us to expand the probes to be placed anywhere within the server, including individual engine-level row operations. If you want a demonstration of DTrace in MySQL, some of the things you can monitor without using the user probes we’ve added, and those new probes I just mentioned, then you will want to attend the MySQL University session this Thursday (12th Feb), at 14:00UTC where I’ll be doing all of the above. As a side note, because I know there are people interested, last week I also finished the patch for these probes to go into the MySQL 5.1.30 version that we will be putting into OpenSolaris. Sunanda is working on getting that release out there as I type this.

LOSUG Presentation Slides Now Available

My presentation at LOSUG on tuesday went down like a house on fire – I think it would be safe to say that the phrase for the evening was ‘It’s a cache!’. For that to make sense, you need to look at the slides, which are now available here. Attendance was great, but it seems the last minute change of day meant that some people missed the session. We had 151 people register, and about 80 turned up on the night.

LOSUG January: MySQL/DTrace and Memcached

Next Tuesday (27th Jan), I’ll be speaking at the London OpenSolaris User Group again. For those that follow the LOSUG meetings, we normally the third thursday of the month, but due to the overwhelming popularity of the event this month (more 100 registrations so far) we have had to push the event back to the last Tuesday of the month. This month, I’ll be talking about the DTrace probes that we have added into MySQL and demonstrating their use and functionality. Along the way I’ll also cover some of the internals of MySQL and how it works (and how they relate to the DTrace probes we’ve added), how to use the probes to analyze and diagnose your queries, and how I’ve already used the DTrace probes to provide information up to the Query Analysis functionality within Enterprise Monitor. After we’ve looked at performance monitoring and optimization with DTrace, I’ll then demonstrate how to get a little more performance out of your application using MySQL by taking advantage of the memory cache offered by Memcached. As before, I’ll provide a link to the finished presentation once I’ve demonstrated everything to the LOSUG folks on tuesday. If you happen to be in London, and don’t already have plans of some sort, please feel free to come along – food and drink will be provided after the session and I’ll be around for as long as I can for any questions.