When is an in-memory operation a BAD idea?

Recently I've learned a little more about how MySQL uses implicit in-memory temp tables that I felt it would be worth sharing.

A little background that perhaps many of you may wish to skip...

MySQL when handling many kinds of queries will implicitly create a temp table. This table will start off in-memory and if it exceeds a certain size (controlled by tmp_table_size and max_heap_table_size) it will be converted to an on-disk table in the location(s) defined by the tmpdir server variable.

This was not new to me and it may not be new to you, but I urge you to read on for the interesting part...

When MySQL creates this temporary table in memory, it will use fixed width rows. I assume this is done because in many cases it is easier/faster to allocate and manage memory this way, rather than measuring the size needed for each row in the temp table and then allocating the memory needed, MySQL just allocates <max_row_size> for each row and it's done.

What this means is that the maximum possible space that could be consumed by any one row is the amount of space allocated and consumed for all rows.

Consider, if you will, a VARCHAR(1024) field using the UTF8 character set. Given that a UTF8 character can be represented by up to three bytes (in MySQL), it means that the maximum theoretical size for storage of 1024 such characters becomes 3072 bytes (3K).

Suddenly your generous and forward-thinking schema design becomes your enemy. If such a field only contains simple words like "cat" and "dog" you will need 3K of memory to be allocated in your in-memory temp table regardless.

As you can imagine, a few such fields existing in your implicit temp table, combined with a high number of rows can cause the space needed for this to spiral out of control very quickly!

Now, to add insult to injury, when MySQL decides that your enormous implicit temp table is too big for memory, based on tmp_table_size / max_heap_table_size, it maintains the very same fixed width row format as it copies the table to disk and continues appending rows to it there.

In practise, I have seen this cause 2.3G of data balloon out to 43G -- this is an increase by a factor of over 18!

So how to avoid it?

It really depends on the situation, but I would suggest that if you know a query is going to need such a temp table that you split the query into multiple steps and employ the use of a pivot table.

The pivot table would be an on-disk MyISAM table (TEMPORARY or not - your choice) that you use to explicitly perform the work done by MySQL when performing the implicit temp table step. The benefit here is that when you define this table, you can use variable-width fields and only consume the space needed.

Depending on your system and environment, you could be a little sneaky and even consider defining your MySQL tmpdir as tmpfs (memory) -- this way you get the benefit of the speed of memory as well as only allocating the space you need for each row, rather than the maximum theoretical size.

In the case that I found, it makes a lot more sense to just materialize the temp table efficiently on disk than to be exposed to the risk that a fixed-width table could run amok.

Hopefully this is useful to some of you out there!

Cheers,
Lachlan

Note: Edited per Don McArthur for correctness. utf8 in MySQL only supports the Basic Multilingual Plane subset of utf8, meaning that it may consume only up to 3 bytes per character, not 4 as in the full utf8 spec.

Xtrabackup Manager - Updates and MySQL Conference Observations..

After talking to a number of people at the MySQL Conf last week, it seems there is a pretty high level of interest in a tool like Xtrabackup Manager. This is great news!

I also got a chance to discuss with some folks about what their needs might be and how they would use such a tool. Hopefully I can make sure that those needs are met as I'm developing things.

The other day I finally committed the xbm-conftool contribution. You can now manage the configuration of your hosts in your favourite CSV editor and then import it into the DB.

I have also now started work on making sure that Xtrabackup Manager will run on Nexenta. If you're not aware, Nexenta is a Solaris kernel based system with a Debian userland -- basically OpenSolaris with apt-get.

The main reason for this is that I really like the idea of using a ZFS based system to run as my backup host. It means I can have the filesystem do compression behind the scenes, which saves on disk usage, but I don't have to worry about it in the user space -- This makes it easier to manage backups because I don't have to worry about compressing and uncompressing stuff all the time. This simplifies operations like applying incremental deltas into full backups.

So far the main aspects of the Xtrabackup Manager code seem to "just work" on Nexenta which is promising, but more testing is needed. I've had to make a small change in the way flushing to the crontab is done, since it seems the crontab command in Nexenta does not support installing a file in the crontab of another user.

I've been side-lined with some other work tasks this week, but I'm hoping to get back to Xtrabackup Manager soon.

Lachlan

Xtrabackup Manager - Local Restores, ConfTools and Re-factoring!

Things have been moving along well in the world of Xtrabackup Manager.

In the last week I managed to fix a some bugs and overcome a number of implementation issues. Most notably the internals have been re-factored significantly and now make use of PHP Exceptions.

You probably don't care about the re-factoring all that much if all you want to do is use the tool, but rest assured that it makes development easier, which in turn is going to be better for users!

Aside from the refactoring and probably more interesting -- I added the functionality to be able to perform a local restore any backup snapshot.

If you are using the standard "rolling incremental" backup method, then this means Xtrabackup Manager would first take a FULL backup of your target MySQL host and following that it would take incrementals.

With Xtrabackup Manager you have the ability to set a snapshot retention policy and it is based on the count of snapshots to retain. For example, if you have scheduled backups to be at 11PM daily with a snapshot retention of 7, then you will, at most, keep snapshots for 7 days.

Once Xtrabackup Manager successfully takes the 8th backup, it will collapse the oldest set of incremental deltas by applying/merging them into the full backup snapshot.

Using the new local restore tool you can restore any snapshot with a simple command like:

shell> xbm-restore -s 17 -l /path/to/restore/to

This will restore backup snapshot ID 17 to local path /path/to/restore/to

It works by first copying the full snapshot for the relevant host -- we call this the SEED -- and then periodically applying each set of incremental deltas needed to effectively "roll forward" to the snapshot that you specified in the command.

So far this seems to work fairly well.

In addition to the local restore tool, I have received a patch to aid in managing your host configurations - it allows you to export everything to CSVs that can be more easily edited in something like Excel or OpenOffice, make a bunch of changes and then reimport over the top.

It is important to note that this is just one of _many_ ways that one will be able to manage their Xtrabackup Manager configuration.

I am still looking to add a nifty web interface in the future.... which leads me into reminding everyone and anyone that I am looking for contributors for the project!

MySQL, Linux, PHP and Web/UI experienced folks would be greatly appreciated!

Check out the project on Google Code for more info:

  http://code.google.com/p/xtrabackup-manager/

Cheers!
Lachlan

Question: What do you use to capture and analyse MySQL processlist?

I have recently been evaluating MONyog and one of the key things that I was hoping that it would provide for me was an easy way to answer the all important question for troubleshooting...

"What was happening at the time?"

If I see some query that is normally fast took far too long or perhaps replication fell behind significantly for a while -- I will always ask myself "What was happening at the time?"

I'd check out stuff like SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS and some system level things like iostat and vmstat or mpstat, etc.

I saw that MONyog has the ability to do things like periodically sniff SHOW PROCESSLIST, or even use MySQL Proxy for query analysis purposes.

This seems to capture how often queries run, whether they used an index, how long they took, etc. but the data is not available to be seen in a time-based snapshot style format.

I know that MySQL Enterprise Monitor that Oracle have on offer as a part of the MySQL Enterprise offerings sort of has this kind of feature -- they have a spiffy way to click and drag a time portion of a graph and then be taken to Query Analyzer to see what was happening during the window.

This of course is still not quite a substitute for the full SHOW FULL PROCESSLIST output -- so I open the question to you, oh MySQL blog-o-sphere...

What do you use to capture Processlist and InnoDB Status info so that you can refer back to it?

Do you just use some cron and have it periodically write to a file? .. or is there some other nifty tool out there that I haven't heard of?

Lachlan

Xtrabackup Manager - License changed from GPLv3 to GPLv2

Hi Folks,

I changed the license on Xtrabackup Manager from GPLv3 to GPLv2 today. This should make it more compatible with the MySQL Ecosystem.

If anyone has any objections, advice, comments -- please let them fly :)

Meanwhile, testing and work continue...

Cheers,
Lachlan

Xtrabackup Manager - A new home and some more progress...

Hi Folks,

The Xtrabackup Manager project now has a new home on Google Code. Including tasks/to-dos, etc.

You can find the project here:

  http://code.google.com/p/xtrabackup-manager/

If you haven't heard about it yet, the project aims to provide a nice backup management wrapper to the popular "xtrabackup" tool from Percona.

At the moment you can set it up and it will automagically detect when to take a FULL backup (first) and then when to take incremental backups following that. It will even collapse your old incrementals into your FULL backup (seed) based on your snapshot retention policy.

So functionality is coming along nicely!...

I have also started work on some documentation -- it is still a work in progress, but shortly there will be enough there for the more experimental among you to try it out.

I'll make another post once that is ready.

Interesting in contributing? Please reach out to me and let me know. I'm interested in finding someone who may like to develop a Web Front-end for it in PHP.

Until then, I will keep plugging away as I find the time.

Cheers,
Lachlan

OSS Project hosting woes of a first-timer

Being a long time OSS Project user/supporter but not having setup or developed any of my own projects previously, I'm having to navigate unchartered territory with regards to how and where to setup my projects.

At first I looked at launchpad.net because I'd seen a bunch of other projects using it, however, when I discovered they didn't have many of the supporting features I wanted - mainly wiki, I decided to look elsewhere.

It was then I checked out good ol' sourceforge.net (apparently I'm still living in the past, because someone commented to me the other day something like "Oh.. you mean sourceforget?") So I guess even geeks have stuff that's "in" and stuff that's "out" ;-)

Anywho.. I liked what I saw on SF.net, it seemed fairly easy to setup and had a lot of features that seemed like they would be useful for a project. So I setup the project on sourceforge(t).net and continued my work.

About 2 revision commits in I started thinking the project was getting to a point that it could be used - so of course I needed to make some quick and dirty documentation about how to use and set it up.

It was then that I came to realise my mistake -- I was apparently so blinded by the various _other_ features on SF.net, that I neglected to notice that they don't have a wiki space provided. Sure, I could set one up in the web hosting space, but I want to spend my time on actually working on the project, not managing the infrastructure around it. (It's a nice way of saying I'm lazy..)

So now I'm thinking about Google Code and Github.

I went to Github and went to check out some of the project hosting pages. Honestly, I think I touched git a long time ago in a galaxy far far away and any information other than it's name has long since evacuated my brain. 

The first thing I did was try to browse through some of the existing projects -- How does it feel to browse? Can I find the things I would want to find easily?

The answer was "No." most of the time I couldn't tell if I was in a project or a branch or a fork or whatever and I struggled to find an easy link for wiki or documentation or even downloads. Github might be a great tool if I knew git and knew what I was doing, but if I imagine a DBA like myself coming to the page to find my tool and use it, they would likely feel an equal level of confusion. They don't necessarily want the source code or to contribute, they want to download and use it.

So for now Github is out.

Then came Google Code - It seemed like a breath of fresh air. I could see clear links to Source, Downloads, Wiki and Issues ( bugs ). Yes, this would do just fine. The problem then became that when I logged in with my gmail account, I saw that it was not my display name that came showing up against the project, but my gmail account ID. 

Ewww... 

Call me vain, but I like putting my real name against things that I do in the community. I don't want people to have to learn that "arzkl123@gmail.com" is actually Lachlan Mulcahy. (That's not my gmail by the way -- I'm attempting to avoid spam here). 

A quick google search and it seems this issue is something people have complained about since 2008 sometime. I guess it is not going to get changed anytime soon, so I'll just go any make myself a gmail account with my real name in it more visibly and use that.

It is a bit of a pain, but out of the available options, it seems like the best choice.

Now I'll hop off the soapbox and get back to work :)

Cheers,

Lachlan