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!

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.

Using static disks in Parallels for performance

Using a static disk, or even just multiple disks, within Parallels can make a big difference to performance. This is particularly true with Windows virtual machines within OS X; I’ve managed to change the boot time from about 30 seconds to under 20 just by changing to a static disk for VM. The default disk in Parallels is an expanding type – this saves disk space, because Parallels automatically adds to the size of the disk as you need it, but it also means that Parallels has to manage the allocated disk space, adding to the file used. Not only does the management imply a small overhead, there is a much larger chance of the file being fragmented. A more annoying effect is that the constant use of the expanding disk with virtual memory under Windows, means that size of the disk may increase just because you opened a large application once. You can get round this by creating a statically-sized disk, and then setting the virtual memory within your virtual host, to use this statically sized disk. To do this:

  1. Shutdown your virtual machine – you cannot do this with a machine in the paused stated, because you are effectively adding new hardware to the machine.
  2. Click Edit to edit the configuration for the virtual machine.
  3. Click Add, and select a new hard disk
  4. Unclick the Expanding checkbox and set the size; probably 1-2GB is fine, but keep in mind you will lose this amount of disk space permanently, even if your VM doesn’t use it all.
  5. Save your configuration.
  6. Start up your VM and configure the new drive.

For Windows:

  1. Log in as a user with Administrator privileges.
  2. Right click on My Computerand choose Manage.
  3. Choose Local Disk Management.
  4. Create a new partition/volume.
  5. Once the new disk is ready to use, right click on My Computer again, and choose Properties.
  6. Click the Advanced tab.
  7. Click Settings under Performance.
  8. Click the Advanced tab.
  9. Click Change under Virtual Memory.
  10. Reconfigure the VM settings, creating the new settings for the new drive (I recommend a lower value of 50MB and an upper value 2-10MB below the maximum size of the disk. Windows will use the minimum and dynamically increase it’s usage up until the maximum.
  11. Remove the VM configuration for the original system/expanding disk.

You should be all set.It’s probably a good idea to run the Parallels Compressor and reduce the size of your disk now that you are no longer using the disk for virtual memory. For Linux, Solaris and other Unix variants you might want to run, the process is of course slightly different. For some environments, there are other benefits, but I’ll cover that in a separate post.

Parallels Update

Parallels for Mac has been updated, but its the stuff beyond the highlight elements that I find most interesting.The headline elements are:

  • Support for the new Mac Pro (and up to 3.5GB RAM
  • Support for Mac OS X 10.5 (Leopard)
  • Experimental support for Vista

That’s all great, and I’m just installing Vista beta 2 on the iMac as I type this on the MBP. However, for me the key elements are:

  • Solaris guest OS no longer hangs after suspend/resume – this was a really annoying issue that begged me no end. Although my iMac 17″ stays on full time, my MBP is set to sleep after an hour, but leaving it alone while I’m updating Solaris 10 or running tests would mean that I’d have to force a reboot and sometimes start again.
  • Solaris doesn’t work with more than one virtual disk fix – this was particularly annoying, as I’ve been playing with ZFS, and having multiple, virtual hard disks to toy with would have been much better than playing with partitions.

So far, the new version seems great. Being able to play with ZFS (even with expanding disks) is fantastic. I’ve been too busy to let the machine sleep and trigger the freezing problem.