We remember when we first started auditing MySQL servers, there were very few tools available.  In one of our early big gigs, we were battling serious performance issues for a client.  At the time, tuning-primer.sh was about the only tool available that could be used to diagnose performance bottlenecks.  Fortunately, with a lot of manual interpolation of the raw data it presented, we were able to find the issue with the server and suggest how to resolve them.  For that we are very thankful.  It was a first step in analyzing MySQL status variables, minimizing the number of formulas to learn and calculate by hand.  Obviously doing it by hand takes forever!

Now fast-forward to today.  Unfortunately, not much has changed.  Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on.  Don’t get the wrong; those tools have …

Setting up a database backup is a primary task for database administrators and we see perl and shell scripts wrapped around few of the backup-tools in practice. With right tools…

The post Setup and configure MySQL backup using Holland and Xtrabackup first appeared on Change Is Inevitable.

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.

Make More Efficient Use of Your Time

Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.

Cross-Platform

Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The …

Talking with Percona Live attendees last year I heard a couple of common themes. First, people told me that there is a lot of great advanced content at Percona Live but there is not much for people just starting to learn the ropes with MySQL. Second, they would like us to find a way to make such basic content less expensive.

I’m pleased to say we’re able to accommodate both of these wishes this year at Percona Live! We have created a two-day intensive track called “MySQL 101” that runs April 15-16. MySQL 101 is designed for developers, system administrators and DBAs familiar with other databases but not with MySQL. And of course it’s ideal for anyone else who would like to expand their professional experience to include MySQL. The sessions are designed to lay a solid foundation on many aspects of MySQL development, design and …

Sometimes data sets are so large, a mysqldump to load a slave is just not practical.  With some of the systems we have administrated, we have had data so large it would have taken days to load the slave when it became out of sync with the master.  When this happens, we usually rely upon Percona’s XTRABackup utility which allows us to make a hot/online backup of the master to use for loading the slave.

In the old days we had to rely upon a third-party tool called ibbackup, or InnoDB Hot Backup utility to do this task. In many ways XTRABackup is a replacement for this tool and has in fact surpassed the ibbackup utility in features and function.

The most efficient way we have found to transfer that data to the slave is the use of the netcat utility.

We also use the screen command since we expect this could take quite some time and don’t want to take the chance that a network connection issue, or a dropped VPN, …

This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.

I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?

The testing environment

In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd
171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250 …

In my recent post, “TokuDB gotchas: slow INFORMATION_SCHEMA TABLES,” I saw a couple questions and tweets asking if we use TokuDB in production. Actually I mentioned it in that post and we also blogged about it in a couple of other recent posts:

• Getting to know TokuDB for MySQL
• Percona Server with TokuDB: Packing 15TB into local SSDs

So, yes, we are using Percona Server + TokuDB as a main storage engine in Percona Cloud Tools to store timeseries data.

And, …

We’ve recently received a number of questions on how to implement incremental MySQL backups alongside encryption with Percona XtraBackup. Some users thought it was not initially possible because with the default

--encrypt

options with XtraBackup, all files will be encrypted, but alas, that is not the case. This is where the option

--extra-lsn-dir

becomes useful, because it allows you to save LSN (Log Sequence Number) information to another directory and exclude it from encryption, allowing you to use the same information needed by incremental backups. Enough talk, let me show you.

Because you would want to usually script your backup and restore procedure, I’d use variables here as well to make you more familiar. First, I’d create 3 folders, where my backups will be stored, ‘full’ for full backups, ‘incr’ for …

I have recently moved to HP's Advanced Technology Group which is a new group in HP and as part of that I will be blogging a lot more about the Open Source things I and others in HP work on day to day.  I thought I would kick this off by talking about work that a colleague of mine, Patrick Crews, worked on several months ago.

For those who don't know Patrick, he is a great Devops Engineer and QA.  He will find new automated ways of breaking things that will torture applications (and the Engineers who write them). I don't know if I am proud or ashamed to say he has found many bugs in code that I have written by doing the software equivalent of beating it with a sledgehammer.

Every Devops Engineer worth his salt knows that backups are important, but one thing that is regularly forgotten about is to check whether the backups are good.  A colleague of mine …

For the same customer I am exploring ZFS for backups, the twin server is using regular LVM and XFS. On this twin, I have setup mylvmbackup for a more conservative backup approach. I quickly found some odd behaviors, the backup was taking much longer than what I was expecting. It is not the first time I saw that, but here it was obvious. So I recorded some metrics, bi from vmstat and percent of cow space used from lvs during a backup. Cow space is the Copy On Write buffer used by LVM to record the modified pages like they were at the beginning of the snapshot. Upon reads, LVM must scan the list to verify that there’s no newer version. Here’s the other details about the backup:

• Filesystem: 2TB, xfs
• Snapsize: 60GB
• Amount to backup: ~600GB
• Backup tool: mylvmbackup
• Compressor: pbzip2

…