MariaDB 5.3 has now three statistics counters for Multi Range Read optimization:

MariaDB [test]> show status like 'Handler_mrr%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Handler_mrr_extra_key_sorts   | 0     |
| Handler_mrr_extra_rowid_sorts | 0     |
| Handler_mrr_init              | 0     |
+-------------------------------+-------+
3 rows in set (0.08 sec)

I’ve just added the first two. The reason for having them is as follows: the point of MRR is to provide speedup over regular execution by doing reads in disk order. In order to make reads in disk order, MRR needs buffer space where it accumulates and sorts read requests. If there are too many read requests to fit into the buffer, MRR will make multiple accumulate-sort-read passes.

Doing multiple passes allows MRR to operate when having limited buffer space, but the speedup will be not as great as with one big disk-ordered read sweep. The purpose of Handler_mrr_extra_key_sorts and Handler_mrr_extra_rowid_sorts is to count the additional accumulate-sort-read passes, so you’re able to tell if you will benefit from increasing your @@mrr_buffer_size and @@join_buffer_size settings.

There are two counters, _extra_key_sorts and _extra_rowid_sorts, because MariaDB has two places where it will do sorting:

1. sort rowids before reading table records
2. sort key values before making a bunch of index lookups

MRR code will try to distribute buffer space between them in an optimal way. The decision is a guess based on the available statistics, and can be wrong. Having both counters will allow us to check how the guess will work in practice.

p.s. if you could not make any sense of anything above, try reading Multi Range Read page in our knowlegebase. We have just put there a hopefully-readable explanation of what MRR is.

You have until Jan. 31 to vote for your favorites tools and services, so, vote now !

Thanks again all folks for your keen interest and your involvement, it was a big surprise to see so many contributors

Follow this link to vote : http://www.mysqlplus.net/2012/01/05/vote-mysqlplus-community-awards-2011/

And come february the 1st for the final results…

 

 

We’ve released an updated version of the MySQL Configuration Wizard we announced at the end of last year. If you don’t remember that announcement, here’s the short version: this is a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

We’ve gotten really good feedback on this tool, including this nice mention on Stack Exchange:

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf’s were put together by highly trained mysql certified dba’s.

So what’s changed in the new version of the Configuration Wizard? Quite a few things. We’ve rolled out the first iteration of the account and profile features. Now you get a homepage with your configuration files, so you can manage them and return to them anytime you like.

From this page (click on the image for a fullsize view) you can do things like sharing configuration files and emailing them to yourself. The new release also adds features like downloading the configuration files so you don’t have to copy-paste them.

If you share a configuration file, then the URL can be loaded by anyone, even if they’re not logged in. It’s kind of like sending someone a link to a pastebin or something like that. Screenshot:

Another new feature is something I’ve wanted for a long time: the ability to generate a more strict, safer configuration file. There’s a new page in the Wizard that lets you set a lot of sanity/safety options to prevent common problems MySQL users run into because of too-permissive MySQL behaviors. These are the kinds of things that Drizzle fixes — and should be fixed by default in MySQL — but never will be because they might break applications that rely on the default behaviors. If you’re building an application from the ground up, now you can prevent bad things from getting a nose under the tent. Here’s a screenshot:

In addition to these things, we have added a number of other features you might not notice, which I won’t spend much time on — they’re things like an integrated feedback form at the left of the page and so on.

What’s next? Well, next I think we’re going to turn our attention to adding new tools, rather than improving this one. I have a list of tools that people have requested or suggested: a SQL formatter, a visual EXPLAIN tool, a configuration advisor, a query analysis tool, a way to register a server’s essential characteristics and then get advice when there’s a new release that might be beneficial for you, and so on. I have selected the next priorities, but I don’t want to spoil the surprise or promise something if it turns out to be harder than I think it will be. What ideas do you have? Let me know by leaving your feedback in the comments.

We hope this suite of free browser-based tools helps you become a more productive MySQL user and administrator!

What makes for a true statement?

We usually test statements using a WHERE clause:

SELECT * FROM world.City WHERE Population > 1000000

The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF():

SET @val := 7;
SELECT IF(@val > 2, 'Yes', 'No')

TRUE and FALSE

The two are keywords. They also map for the numerals 1 and 0, as follows:

mysql> SELECT TRUE, FALSE;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+

Like in the C programming language, a nonzero value evaluates to a true value. A zero evaluates to false. A NULL evaluates to... well, NULL. But aside from 3-valued logic, what's important in our case is that it is not true.

However, simple value comparison is incorrect:

mysql> SELECT @val, @val > 3, @val > 3 = TRUE as result;
+------+----------+--------+
| @val | @val > 3 | result |
+------+----------+--------+
|    7 |        1 |      1 |
+------+----------+--------+

mysql> SELECT @val, @val = TRUE as result;
+------+--------+
| @val | result |
+------+--------+
|    7 |      0 |
+------+--------+

To test for the truth value of an expression, the correct syntax is by using IS TRUE:

SELECT @val, @val IS TRUE as result;
+------+--------+
| @val | result |
+------+--------+
|    7 |      1 |
+------+--------+

Likewise, one may use IS FALSE to test for falsehood. However, if you wish to note NULL as a false value this does not work:

SELECT @empty, @empty IS TRUE, @empty IS FALSE;
+--------+----------------+-----------------+
| @empty | @empty IS TRUE | @empty IS FALSE |
+--------+----------------+-----------------+
| NULL   |              0 |               0 |
+--------+----------------+-----------------+

If you're unsure why, you should read more on three-valued logic in SQL. To solve the above, simply use IS NOT TRUE:

SELECT @empty, @empty IS NOT TRUE;
+--------+--------------------+
| @empty | @empty IS NOT TRUE |
+--------+--------------------+
| NULL   |                  1 |
+--------+--------------------+

In summary, use IS TRUE and IS NOT TRUE so as to normalize truth values into a 0, 1 value range, C style, including handling of NULLs.

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.

With Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is stored in the InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our example will be very straightforward: we’ll delete some rows from a table in order to recover the table later.

Most of these limitations are solved on Percona Server . More info about this in the conclusion section of this post. This post will be focus on how to recover a single tablespace using stock MySQL server.

First, you must meet certain prerequisites to be able to restore a ibd tablespace:

• The ibd file must be from a consistent backup with all insert buffer entries merged and have no uncommitted transactions in order to not be dependent of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server shutdown.
• You must not drop, truncate or alter the schema of the table after the backup has been taken.
• The variable innodb_file_per_table must be enabled.

Then, our first step is to get a consistent backup.

First we need to copy all the data to an output directory:

The –export option is the magic trick that will help us to get a consistent backup with complete independent ibd files without shutting down the service. In the second step the use of –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers.

# innobackupex --defaults-file=/etc/my.cnf --export /tmp/

Then apply the logs to get a consistent backup:

# innobackupex --defaults-file=/etc/my.cnf --apply-log --export /tmp/2012-01-22_14-13-20/

Now we’re going to delete some data from one table. In this case we’re going to delete the salary information from the user 10008:

mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

mysql> DELETE FROM salaries WHERE emp_no=10008;

The next step is where we are going to save a lot of time and some headaches Instead of recovering all the InnoDB data we are going to recover only the “salaries” table:

• Discard the tablespace of the salaries table:

mysql> ALTER TABLE salaries DISCARD TABLESPACE;

• Copy the salaries.ibd files from the backup to the database data directory:

# cp /tmp/2012-01-22_14-13-20/employees/salaries.ibd /var/lib/mysql/data/employees/

• Import the new tablespace:

mysql> ALTER TABLE salaries IMPORT TABLESPACE;

mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

The salary history from the user is back again!

Conclusion:

As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are some prerequisites to comply.

Percona Server relaxes a lot of limitations and is able to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was
“exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table_from_xtrabackup=1 should be enabled for such advanced import process to work. You can read more about this feature in Percona Server Documentation

In the next blog post I’ll explain how to do recovery using Percona Data Recovery toolkit.

The program for this year's MySQL conference is now published.

As regular readers will remember, I served on the program committee this year and was one of those who appealed for people to send in great proposals. I would now like to thank all of you that sent in proposals. On my quick count we had over 250 proposals, and if I look at my own ratings I'd say about 180 of them were really good, conference worthy talks (and this already excludes some pretty good talks). A related piece of trivia was that this might have been the first year ever that the deadline for the Call for Proposals wasn't extended, which possibly took some of you by surprise. We simply got so many good talks by the deadline, that there wasn't any need to.

read more

I am pleased to announce the schedule for Percona Live: MySQL Conference And Expo 2012 is now published. This is truly great selection of talks with something for MySQL Developers, DBAs, Managers, people just starting to use MySQL as well as looking for advanced topics. We have talks about running MySQL on extremely large scale in a Web as well as running MySQL In the Enterprise Environments. Some speakers have spent over decade pushing MySQL to its limits, others have in depth experience working on MySQL Code.

We have many talks which are covering Oracle MySQL, and forks such as MariaDB, Drizzle and Percona Server are well covered too. You will also have a chance to learn about commercial MySQL alternatives such as Clustrix and SchoonerSQL from our sponsors.

At the same time this is the conference for MySQL Community. We’re talking about other database systems only as it comes to migration to MySQL and about NoSQL technologies such as Memcached,Redis,Sphinx which are commonly used to supplement MySQL.

The space was very tight this year and competition was very tough. We had over 300 proposals for approximately 60 slots. As results committee had to make a lot of very tough choices and many great talks could not be accommodated.

We have a great Conference Committee this year who has done a great job getting the schedule together. I can’t thank them enough !

See you in April in Santa Clara for MySQL Conference and lets make this event an amazing success !

I spent last week at linux.conf.au in Ballarat, Victoria (that’s the Victoria in Australia, not wherever else there may be one) which is only a pleasant two hour drive from my home town of Melbourne (Australia, not Florida). I sent an email internally to our experts detailing bits of the conference that may interest them – and I thought that it may also interest our wider readers who are interested in all levels of the software stack.

For those that don’t know: linux.conf.au is one of (if not the) most awesome technical conference in the free software space. It consistently attracts a wide variety of very knowledgable speakers and a large number of attendees.

Every year it is put together by a (different) set of volunteers, and this means it also tours around the country (and sometimes even New Zealand). This year it was in Ballarat – a regional city a couple of hours drive out of Melbourne. One of the great things about LCA is that you are not always at the same hotel, in the same city stuck with the same two restaurants.

This year had a bit of an increased focus on privacy, security and basic freedoms and human rights. This is no doubt a reaction to the increased attacks on freedom of speech and the internet that have been going on in recent months.

That being said, there were a huge number of great talks on a variety of topics – everything from filesystem performance to open hardware, to repurposing existing hardware to upcoming challenges for the kernel to howto be a better sysadmin. In fact… for those who weren’t there and spend any of their life helping people admin machines – go and watch those talks.

linux.conf.au (for me) is one of the cannot-miss events in the year. It’s an opportunity to learn things that directly apply to my work, may apply in the future and most certainly will never apply but are rather cool anyway.

All the video from the conference are already up! This is an amazing effort from the (volunteer) AV team. I’ve included links to a selection of talks below that I especially think are worth watching:

Watch no matter what:

• Keynote – Karen Sandler
  This keynote was amazing. Go watch it. The organisers did a truly excellent job at selecting keynotes this year.
• Keynote – Jacob Appelbaum
  This is best described as a tour of internet freedom, the attacks on it and a tour of the modern surveillance state.
• UEFI and Linux: The future is here and it’s awful
  You will be depressed at some point in this talk – the news is not great for the future of even being able to easily boot free software on machines.
• Paul Fenwick’s Keynote
  A good quick introduction to hacking your brain. I’m sure many of you (like me) are interested in ways to hack our brains and our bodies to better serve us. This talk is merely an introduction. I also suggest you check out Anki if you want to improve your ability to remember things.
• Torturing OpenSSL
  This was certainly one of the most amazing talks I saw. A whole new interesting way of attacking SSL. Vary CPU voltage, extract private SSL keys! Wheee!
• The Kernel Report – Jonathan Corbet
  You can skip this only if you read every single mail on LKML, run your own analysis on the kernel source tree and publish an (at least) weekly publication on Linux.
  This is one of the few (err… only) talks that is repeatedly accepted into linux.conf.au. Why? Because Jon manages to compress a whole year of activity in the Linux world int oa single session that is incredibly informative.
• I Can’t Believe This Is Butter! A tour of BTRFS
  This is going to be the default filesystem in a number of places over the next few years, time to start learning! While it’s unlikely to be suitable for database workloads anywhere in the near future, I suspect we’ll see BTRFS as the root filesystem and XFS as the filesystem for the database server in the not too distant future.
• Mistakes Were Made
  This session explores a number of rather indispensable things for those in operations – but also leaks over into development. Learning from our mistakes can only make us better at doing our jobs.
• Hack Every

Truncated by Planet PHP, read more at the original (another 2772 bytes)

On the 25th of January at 10 am PST, I will present a webinar on preventing MySQL emergencies titled “Preventing Downtime in Production MySQL Servers”. The material I will present is based on in-depth research done by Percona across many production servers.  We analyzed more than 150 emergency cases and categorized our findings to help you learn ways to avoid production downtimes. Join us to learn more about why emergencies happen (it may be different than what you think) and what you can do to avoid them.

Sign up for the webinar now by visiting our webinar page [http://www.percona.com/webinars/2012-01-25-preventing-downtime-in-production-mysql-servers/].

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?

A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.

A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.

An incremental backup is a modification of a differential backup. An incremental backup records the changes since the last backup of any type, be it a differential or full backup. The advantages of incremental backups are similar to those of differential backups.

Here is an example that might explain this more clearly. Suppose that you take a full backup on Sunday. On Monday, you make a backup of all the changes since Sunday. This is a differential backup.

On Tuesday is when you begin to see the differences between the backup strategies. If you back up the changes since Sunday, then you have made a differential backup. If you back up the changes since Monday, it is an incremental backup.

Why would you choose an incremental versus a differential backup? That is a little bit out of scope for this blog post, because there are a lot of subtleties to consider. However, perhaps the biggest difference is in the way that you would restore a backup. Suppose that you need to restore your database on Friday. If you have taken differential backups all week long, you only need to restore Sunday, and then apply the changes that have happened since Sunday. If you have taken incremental backups, you must restore Sunday’s backup, and then apply changes repeatedly until you reach Friday. This can be more labor intensive, error-prone, and risky. It can also take longer.

Percona XtraBackup is capable of taking incremental backups, and when you specify the base backup as the previous incremental or full backup, you can easily make differential backups.