jintropin

Xtrabackup for MySQL, and issues with streaming mode

March 5th, 2011

Yes, it has been quite some time since I blogged, work has been very busy lately.

Currently we have a number of various backup strategies that our partners may use, one of which has been hot backups via xtrabackup (or innobackup/MySQL Enterprise Backup – with a license fee of course).

At this time we have one person dedicated to maintaining the backups, which includes rewriting innobackupex to handle extras and also write wrapper scripts around the original one.

This morning he contacted me because he was running into problems with xtrabackup 1.5 and streaming. No, not the usual performance issues etc, but rather a few random .MYI files were missing and the xtrabackup_checkpoint and xtrabackup_logfiles were missing.

What was interesting was the the MYI,frm and MYD files missing was random – it was mainly static but would change every now and then when his script ran.
At first glance without reviewing his script, it seemed like something was going on with xtrabackup streaming mode, since the help_*.* files contained the checkpoints info, so it looked like it streamed it to the wrong place..

However, after I was informed about it, I decided to look at the script this person had written, and I realized that he did something like..
innobackupex –backup ……. –stream=tar /path 1>/file.tar 2>/logfile.

After testing, its clear that xtrabackup does not like that. You are not able to separate stdout and stderr like this, since it breaks the application. By changing 1>/file.tar 2>/logfile to just >/file.tar it all worked well.

Why does innobackupex write things to STDERR that should be sent to the tar stream? I do not know, but I hope that someone here can help out.
Either way, this is a reminder for all of you that want to use it – you cannot suppress output, since that will break the stream mode.

Have a great spring!

Time for an update

June 9th, 2010

Sorry all I have been very busy transitioning into my new position for the last couple of months.

I want to just give a small note – it was very good to meet a bunch of you at the conference back in April!

However, on to more serious business – I currently work for Electronic Arts, and at this point we are actually looking for Sr. MySQL DBA’s. So if you or someone you know is interested, feel free to email me directly at nwesterlund – at – ea.com . We will of course treat everything confidentially.

On a different note – the InnoDB plugin rocks in performance compared to the built-in version! I have seen test results (from my own test) ranging from 15% to 70% performance improvement! Good job there!

Forward – into the future

January 28th, 2010

Looking back at the short-term, it wasn’t long since MySQL was bought by Sun, and yesterday the deal with Oracle was finalized.

I want to take this time to thank everyone involved in MySQL and the surrounding community for their hard work, and express my wishes that you look forward to the future and what it might hold, I am sure it will be great.

I think that if done correctly, we as a community can benefit from these changes in the long run, so let’s look forward rather than backward.

On a sidenote, mysql.com is quite slow today, perhaps as a result of high load due to masses of people wanting to check out the news. However, sun.com has now been redirected to Oracle.com.

Turns out however that things such as the documentation is still missing, hopefully it will return soon. As an example for what I mean, take a look at the documentation and you will notice that it is all empty.

So, to everyone, a happy 2010 and best wishes to Oracle.

Log Buffer #173: a Carnival of the Vanities for DBAs

December 18th, 2009

Time keeps on moving and we’re now only one week from Christmas, when people spend time with their families and loved ones. But, that is in a week, today it is time for a new edition of Log Buffer, where we catch up on database blogs from across the world, starting with SQL Server.

SQL Server
Over at Less Than Dot Ted Krueger brings up the question of the good, the bad and the ugly of database design where he says “In my career I have seen the ugly and then the really ugly but I found on this particular implementation it could get even uglier.”

Over at Carpe Datum the question is if wizards are evil or not where we are encouraged to fix the wizards.

Ever wondered about how much space is available in tempdb? The friendly people over at the Customer Advisory Team monitor free space in tempdb where they remind us that “Usually, unused transaction log space is reclaimed in tempdb is when the transaction log runs 70% full or if a user initiated CHECKPOINT command is run.”

Oracle
Now, let’s jump right ahead and take a look at what has been going on in the Oracle world this week. Starting off with Kerry Osborne’s Oracle Blog where we get a post on how to track changes to database parameters although it hits some limits with diskspace, as Kerry states “So that’s 174G for AWR data for 7 years with 3 nodes and one hour snapshots!”

Sticking to AWR, Doug’s Oracle Blog explains how you should never underestimate AWR, and what his favourite oracle blog this year was. This ties up nicely together with the previous post by Kerry.

Ever questioned just how good you are? How would you become an expert? Ever won DBA of the year awards? Husnu Sensoy has a post which he titled How to become an Oracle expert?, in which he explains the love behind his job and how to find – and become – a master yourself.

Let’s take a look at queries, specifically how to change an outerjoin query to one without as Gerwin explains in his blog Home of General Approach Performance Profiling

Over at DBA Kevlar Kellyn gives us a overview of the problems encountered by ORA-14097: column type of size mismatch. That’s one more bullet that the Kevlar managed to survive.

MySQL
Now let’s take a look at the MySQL world. There’s really been two big things going on this week, one concerning Oracle/Sun and the EC (Including how that affects MySQL and all forks) and the other being that MySQL 5.5 is now released. Let’s dive right into it, shall we?

First of all, there will be a MySQL Conference in April as usual, although not co-arranged with Sun.

Giuseppe provides a nice tutorial on getting started with MySQL 5.5 on his blog, The Data Charmer, basically it boils down to the fact that the new release model is now in full swing.

Now, since 5.5 was released Roland Bouman gives us a quick look at the SIGNAL syntax in his blogpost on how to validate mysql data entry. SIGNAL was introduced with the latest 5.5 milestone release (m2), and is an ANSI/ISO standard.

Innodb_io_capacity might be very tempting to tune, but take a look at what Jay says about in a post where he reveals his results of testing the parameter.

Over at the indian wind along the telegraph lines Kurt is trying to explain and set some facts straight on the response after Monty issued his call for action. Both of these posts has stirred up quite a lot of comments and emotions across the community.

Sheeri over at The Pythian Group has voiced her opinion on Oracle buying Sun. She also goes on saying “When others spread the fear, uncertainty and doubt that Oracle will somehow kill MySQL, I consider the source.”. And this is something that I personally agree with and share the same idea as Sheeri does.

That is all for this week folks, and I wish you a Merry Christmas in advance, and have a safe holiday!

IO benchmarking for MySQL, part 1

December 4th, 2009

A while ago I started a project which will be heavily IO-bound on the MySQL Server, the testmachine allocated for this had a DAS with 15 disks (although I only used 14) connected via external SAS (standard 3Gb/s half-duplex or 6Gb/s full-duplex on two ports).

I used sysbench for the tests, both fileio and oltp, although these results will be based on the fileio results. The setup with the disks in RAID10 (7 raid 1 sets, then striping over them) and later RAID50 (2 raid 5 sets with 7 disks each, then striping over that), the latter yielding better results.

Let’s take a look at 1,2,4,8,16 and 128 concurrent clients, with different IO schedulers, although using XFS.
The config for the raid controller was write-back, cached access, advanced readahead. 512MB battery backed cache on the controller.

Also, I tested both sequential reading (SEQRD in the graphs) and random read/write (RNDRW in the graphs)

Random Read-Write

Random Read-Write

Sequential Reads

Sequential Reads

So, in random access they were all quite bad, especially considering that I had expected better performance, but they were at least very close. However, sequential reading was a field where noop did a much better job, as all the others started dropping off after 128 clients, but I reached a peak around 384 concurrent clients with noop (and a throughput of ~518Mb/sec)

Now, in August Vadim had a post about ec2/ebs IO benchmarking, and the results that you will see below are better, but still not good enough in my mind, since I know that IO access on ec2/ebs is quite slow.

Anyway, let’s take a look at req/s and response time in ms, with 8 threads doing random read-write to keep the benchmark comparable with Vadim’s.

Req and ms

Req and ms

The latency here stayed pretty constant, and what is interesting is that the latency for each test was almost identical to each other as I scaled up the amount of threads as well.

One thing worth noting, was that with bonnie++, I would get similar results for one thread as I got with sysbench in the reading test, however, it turns out that linuxmesses around with device management even though it’s better off left to the controller in this case.

Take a look at this graph, which shows how much improvement you can get by using blockdev to change setra on linux even though that the raid controller should be responsible for that:

tweaking

tweaking

In the next part, I will focus on finding the limits for each scheduler as well as see what is possible to get out of this array, but I am still waiting for additional hardware to arrive in order to do so.

As for which scheduler I recommend? For the workloads I tried with the oltp test, noop had the best results. These results will be published in the next part as well.

New challenges and new location

November 20th, 2009

Hello again.

After I left Pythian I was looking at a couple of other places to begin working with, but finally settled on staying in the mediterranean region, however with a small relocation.

Thus, this post is now long overdue, but I am working for a spanish company called Tuenti, which is the largest social network in spain. I am quite excited and have now settled down in Madrid, so I will be using this website in order to blog about challenges, problems, solutions and everything that comes with running a large scale deployment of MySQL.

Stay tuned for the first technical post, it’s on its way already!

Nick Westerlund: Narak iktar tard!

September 14th, 2009

On the 23rd of June 2008, I wrote a note saying that I had just joined Pythian. Today I am posting a similar, but different, note saying that as of the last of September, I will no longer be employed by Pythian, the time has come to look for new challenges. Although I am sad to leave, I do look forward to the future and what it may hold for me.

I wanted to take this moment to thank Pythian for having me, for having such great co-workers whom I count myself lucky to have worked with. I also want to give a special thanks to Augusto for taking care of me when I first joined, and showing me around how the company works. I must thank Paul as well—he is an exceptional person to work with, and I’ve come to value his opinion and expertise very highly.

I will still be around. I will be working with MySQL, and I will be active (probably even more than currently) in blogging, in conferences, and in the MySQL community—so you have not seen the last of me. It’s been a great time so far, and I intend to make even more of it in the future, to involve myself much more than I have done so far.

As a closing note, I want to address a concern I had in that very first blogpost, and that was, if it’s cold in Canada in July—and I can tell you that it is! I had to wear a jacket (even during the day!). I also fondly remember some days talking to the team from here in Malta, and someone reported a temperature -23C, and I look outside and it’s +19, so I guess that comparing Ottawa with Malta may not be the easiest, nor the most fair, comparison. It’s like poutine versus pastizzi!

Thanks, and we will be in touch. I hope to see you all around the world and to cross paths again! As we say in Maltese, Narak iktar tard!

Want to spend a weekend in Germany talking about Databases?

July 6th, 2009

If so, you should check out OpenSQLCamp 2009, European Edition. November last year, the home of OpenSQL Camp was Charlottesville, VA, but now it is time to have something a bit more local. OpenSQL Camp will take place Saturday 22nd and Sunday 23rd of August, in St. Augustin, Germany, so it could do for a nice August getaway to Germany.

It’s not really the biggest of cities, but then again, that is part of the charm, going to some small city and learning more about databases.
In case you do happen to be curious, feel free to check out the list of proposed sessions, although it is not complete, it does give a overview of what to expect.

I for one am looking forward to a European event, as it seems like most things is based in the US, and that means travel time will be less and no timezone difference, which I think will be great. In addition, the opportunity to meet with friends, share knowledge and listen to some very interesting talks makes for a very promising weekend.

I hope to see you all there!

Pythian’s Sessions at the MySQL Conference

May 6th, 2009

Augusto Bott and myself had a wonderful time at April’s MySQL Conference in Santa Clara, California, and also at the coinciding MySQL Camp and Percona Performance Conference.

We presented two sessions at the conference, and we wanted to share the slides with you. The first one is called Proactive Operational Measures.

The second one is called 8 Simple Rules to Design Secure Applications.

Augusto and I promised that we would make the slides available online, so we’re sorry for the slight delay in getting them published. Enjoy!

Log Buffer #138: A Carnival of the Vanities for DBAs

March 6th, 2009

Welcome to the 138th edition of Log Buffer, the weekly review of database blogs. If you aren’t aware of who I am, my name is Nick and I am a Senior DBA at The Pythian Group. This is my second run at hosting Log Buffer, and I hope you will enjoy it as much as I did creating it.

As we see winter giving way to summer, I thought I would start with Informix.
Over at Informix-technology Fernando talks about FUD for thought where he talks about the future of Informix vs. DB2.

Sticking with IBM, I thought that we should take a look at what Bryan Smith says in this post about call for feedback, where he discuss the need for feedback on the Data Studio administration console.

I don’t know about you, but I’m tired of Informix. Let’s switch to DB2, and Henrik Loeser where he discuss the DB2 Information Center and does still mention Informix as well as DB2.

Let’s do some general SQL, and watch Stephane Farroult talking about rewriting sql queries in 9 minutes for performance.

Having covered IBM, let’s switch to Oracle and Eric Emrick, where he talks about database continuity for a while, and then let’s go over to Pythian’s Don Seiler and his talk on how redundancy isn’t good for retention policies, in his blog RMAN Redundancy is not a Viable Policy.

Now let’s continue in the Oracle swamp for a few moments while we take some time to think about what Tanel Poder has to say about Oracle’s diagnostic events and KSD debugs.

As a MySQL DBA we’ve had issues with hierarchical data. But this isn’t MySQL, it’s about what Tyler Muth can do without resorting to PL/SQL loops, in this post: Hierarchical Query to Unordered List. Now, Pete Finnigan talks about CPUs and security in his blog, IOUG Critical Patch Update Survey Results Are Out.

So, switching back to performance (sorry, I follow an internal preference here, mixing it up a bit), Randol Geist examines Basic SQL statement performance, where he discuss how to perform a performance diagnostic.

Let’s switch over to MySQL, starting with a blog called Looking at MySQL from an IDS perspective: Introduction which covers how MySQL works and its strengths and weaknesses. Following in the same series of blogposts, Looking at MySQL from an IDS perspective: The MySQL Editions shows the differences between each MySQL edition—very good info for anyone thinking about using MySQL.

Over at MySQL Performance Blog Baron Schwartz discuss about the truth about filesort, in his blogpost What does Using filesort mean in MySQL? .

On When Pet Projects Bite Back, there’s Jonathan’s discussion on how best to move your production systems without encountering downtime.

Matt, over at Big DBA Head discusses the 5 minute DBA, and the need for monitoring, and he gives some information on how to do just that. Quite interesting.

Almost last—an emergency. Arjen asks if you have a fork stuck in your head, and discusses what you’d do if you did. But, he also explains why he likes to get at the “why” of your MySQL issue instead of provide an easy answer, which is very common in the MySQL world—everything depends.

To close, I think this blog by Cary Millsap is my favorite blog of the week, where Cary talks about Maths as well as educational techniques. Very worthwhile reading. The blog is called Dad, do I really need math?.

I hope you all enjoyed this week’s Log Buffer, and maybe if Dave thought I did a good job, I will get the chance of writing another one in the future. Until then, take care of your databases.