PlanetMysql.ru — информация о СУБД MySQL

Schooner has a blog post showing that one node of their product beats 9 nodes of Clustrix’s in throughput. But this reduces everything to a single number, and that’s not everything that matters. If you’ve looked at Vadim’s white paper about Clustrix’s (paid-for) performance evaluation with Percona, you see there is a lot of detail about how consistent the throughput and response time are.

I’d love to see that level of details in any product comparison. A single number often isn’t enough to judge how good the performance is — fast is not the only thing that matters.

I have absolutely no doubts that a single node of Schooner’s product can run like a deer. It isn’t doing any cross-node communication, after all, so it had better be faster than something that blends multiple nodes together into a virtual “single database server.” And I think if the full story were told, it would be a great knock-down drag-out fight. Give us more details, Schooner!

Further Reading:

• High Performance MySQL, Second Edition: Query Performance Optimization
• An ongoing thread of blogs on MySQL performance
• Status update on High Performance MySQL
• High Performance MySQL Third Edition pre-order available
• Sessions of interest at the Percona Performance Conference

http://collaborate12.ioug.org

Double Down at COLLABORATE 12- The IOUG Forum with Two Ways to Save- and a Chance to Win! The user-driven Oracle event of the year is fast-approaching, and IOUG wants you to make youreducational experience a sure bet. Between hundreds of cutting-edge education sessions, workshops and legendary Oracle speakers, you’ll return from Las Vegas with valuable knowledge to transform into immediate results for your business. No need to go all in to attend- IOUG is sweetening the pot with ways for you to save big bucks and even pocket some cash while you’re at it. Register today for your chance to win a $200 American Express Gift Card!The deck is stacked at COLLABORATE 12 – The IOUG Forum in your favor. Register for the conference today with the priority code EM03 and be immediately entered for a chance to add a $200 AmEx Gift Card to your winnings for the week. Treat yourself to some fusion cuisine while in Vegas, catch a show on the strip, or just pocket the prize for a rainy day! Buy-in to the table at COLLABORATE 12-The IOUG Forum has never been more reasonable. Hotel rates are only going up from here on out, so book your room today to take advantage of the final day of ultra-reasonable rates!  How about adding more savings to the mix on your conference registration?Sign up for a seat at the table with IOUG before February 29 and be dealt a winning hand: save up to $500 on conference registration by booking ahead of the early bird, and we’ll even throw in a bonus, extra day of education($599 value)! Don’t miss your chance to to take advantage of these massive savings . Book your COLLABORATE 12 journey today! Guarantee your seat at the table today, and save by registering before the Early Bird Deadline! Registration rates start as low as $1,295* for members of IOUG. Resolve to give yourself a fantastic week of Oracle Education, and happy 2012! * Rate assumes hotel and group discount Boot Camp Information Housing Deep Dive Information IOUG Registration Benefits Exhibit at COLLABORATE 12 Session Schedule Want to sponsor a Deep Dive? Contact Jconlon@ioug.org for more information.

The first Central Virginia MySQL Meetup was a nice little howdy-do, and as a test flight, I think it showed that the bird can get off the ground quite nicely. So, with the generous help of our meeting host Meddius, we’re going to do it regularly on the 3rd Wednesday of every month. The next event is already scheduled — I will be talking about high availability options for MySQL.

I’m interested in having outside speakers. Anyone who’d like to come and present something MySQL-relevant, please feel free to email me, or contact me via the Meetup page with the “suggest a Meetup” feature. If you’re traveling from outside the area, the airport is CHO, and it’s about 30 minutes away. Amtrak is also very convenient. I’m happy to chauffeur you, and can help you find lodging too.

I will not try to steer overly much, but I hope that this turns into a group where people introduce themselves, mention job openings and other news of interest, and so on.

There are also a couple of related meetups nearby that I want to promote: NOVA MySQL at AOL’s headquarters led by Mike DelNegro, DevOps DC at CustomInk’s offices led by Nathen Harvey, and one I haven’t been to yet but hope to attend soon, Shenandoah Ruby Users Group in Harrisonburg near Rosetta Stone’s headquarters, led by John Paul Ashenfelter.

Further Reading:

• New Central Virginia MySQL Meetup
• Speaking at MySQL Meetup in Northern Virginia
• Free DevOpsDC meetup on Tuesday
• News on MySQL Cacti Templates
• An ongoing thread of blogs on MySQL performance

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.

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     | Pig    |         3 |
|       6 | hamster | Henry  |         1 |
|       7 | dog     | Honey  |         1 |
+---------+---------+--------+-----------+
7 rows in set (0.00 sec)

mysql> select * from owners;
+-----------+-------+
| owners_id | name  |
+-----------+-------+
|         1 | Susie |
|         2 | Sally |
|         3 | Sarah |
+-----------+-------+
3 rows in set (0.00 sec)

To find out who has which pets, we would join the two tables together like this:

mysql> select owners.name as owner, pets.name as pet, pets.animal
    -> from owners join pets on (pets.owners_id = owners.owners_id);
+-------+--------+---------+
| owner | pet    | animal  |
+-------+--------+---------+
| Sally | Rusty  | fox     |
| Sally | Fluffy | cat     |
| Sarah | Smudge | cat     |
| Sarah | Toffee | cat     |
| Sarah | Pig    | dog     |
| Susie | Henry  | hamster |
| Susie | Honey  | dog     |
+-------+--------+---------+
7 rows in set (0.00 sec)

The example above uses the ON keyword, but since the columns we use to join are called owners_id in both tables, then we can instead put in USING as a shorthand.

mysql> select owners.name as owner, pets.name as pet, pets.animal
    -> from owners join pets using (owners_id);
+-------+--------+---------+
| owner | pet    | animal  |
+-------+--------+---------+
| Sally | Rusty  | fox     |
| Sally | Fluffy | cat     |
| Sarah | Smudge | cat     |
| Sarah | Toffee | cat     |
| Sarah | Pig    | dog     |
| Susie | Henry  | hamster |
| Susie | Honey  | dog     |
+-------+--------+---------+
7 rows in set (0.00 sec)

OK so it's a super-simple tip but until you see the different approaches laid out side-by-side, it can be confusing. This USING trick is why you will often see fields named, for example, "user_id" when they are in the "users" table - then the shorthand can be used any time you join this user_id to any other user_id column.

Lorna is an independent web development consultant, writer and trainer, open source project lead and community evangelist. This post was originally published at LornaJane

Join me Wednesday for a free webinar on using the new Percona Toolkit v2.0.3 to verify replication integrity. If you’re not familiar with this topic, it’s one of those must-do things that no one officially tells you is necessary with MySQL. The new tools in Percona Toolkit 2.0.3 make it much easier and less complicated — and safer — than before. Well worth an hour of your time.

Further Reading:

• Free webinar on preventing MySQL downtime
• Free webinar on MySQL performance this Thursday
• How to check MySQL replication integrity continually
• Why I write Free Software
• A review of MySQL Replication by Russell Dyer

You can pre-order the new edition of High Performance MySQL now on Amazon or via O’Reilly’s website.

Eric Bergen called the second edition “the best MySQL book on the planet“. What will the third edition be called? The best in the solar system? This reminds me of a joke, supposed to be a true story: three pizza restaurants next to each other had signs saying “best pizza in town,” “best pizza in the world,” and “best pizza on this block.”

Further Reading:

• Pre-Order High Performance MySQL Second Edition
• High Performance MySQL Second Edition Schedule
• High Performance MySQL is going to press, again
• High Performance MySQL Second Edition goes to press!
• Are you sure you’re reading the second edition of High Performance MySQL?

As a MySQL  consultant, I spend a lot of time talking with people about their backups. More specifically, we talk a lot about recovery. I had an interesting incident myself, which illustrates some of the things that are bound to happen as time passes.

First, let me explain how I do my personal backups. I have a series of external hard drives, which are fully encrypted, as is my computer’s hard drive. I maintain a full mirror of my computer’s hard drive on these external hard drives. I occasionally switch the hard drives out, and carry one or more of them to a bank’s safe deposit box. I try to do this once a week, but sometimes it isn’t quite that often.

As a result, I have one hard drive located physically near my computer, which contains a very recent backup of all my work. I have at least one, usually 3 or more, other copies of my data in a slightly less fresh format, but durably stored in a bank.

While setting up a new computer recently, I somehow corrupted a GPG-encrypted file that I use quite often, and update frequently. (Perhaps a quantum bit flip or a solar flare — I don’t use ECC server-grade RAM, so this is actually possible/likely). As a result, I needed to get my most recent backup. I plugged in my external hard drive, and the drive physically failed. I spent some time doing diagnostics, and concluded that the drive really had failed. This reminded me that I had another hard drive, which I had set aside on a shelf couple of weeks ago, because it had also apparently failed. I pulled this drive off the shelf and ran diagnostics on it. It was also bad.

So I had lost my most recent copy of my file, as well as my most recent backup of it. it I could go to the bank and retrieve my previous backup of it, but that was a couple of weeks old, and I knew there were some changes that were not in that copy.

The happy ending to this story is that the corruption was only in the tail of the file, actually only in the last couple of bytes.  I was able to decrypt everything except the last block or so, and then I retrieved that  portion from my old backup. So  in the end, I did not lose any data, but it was an interesting exercise.

The most interesting thing about this is the probability of several failures happening together. I think it is a natural human tendency to underestimate the probability of several different kinds of failures, or even several identical failures, happening at the same time.  It quite commonly happens that hard drives fail at the same time, and we know that backups fail, and we know that  files are corrupted or deleted, and it’s not a matter of if, but when these things happen together. This is why I have several copies of my backups in different places.

I’m still glad that I do backups the way that I do, keeping my own backups instead of relying on some online backup provider. I have heard many horror stories about them, and witnessed a few myself.  I do not trust anyone else with my backups.

Further Reading:

• Progress on High Performance MySQL Backup and Recovery chapter
• How to use MySQL binlogs to undo a DROP statement
• The new hotness in open-core: InnoDB
• MySQL disaster recovery by promoting a slave
• A review of SecoBackup Amazon S3 backups for MySQL

I’ve created a Central Virginia MySQL Meetup group for those of you in the area. Our first event will be in a couple of weeks at Meddius’s headquarters. I’d like to meet and get to know more people in this area who use MySQL, so come out and we’ll have a good time together! Oh, and beer. And pizza. Of course.

Further Reading:

• Speaking at MySQL Meetup in Northern Virginia
• Free DevOpsDC meetup on Tuesday
• Recap of Portland OpenSQL Camp 2009
• MySQL Camp 2007
• Announcing OpenSQL Camp 2008

Here are some of the fake O'Reilly book covers I mentioned in a prior post.  These have been optimized for use as black & white Kindle screensaver wallpaper images.  If you haven't done so already, you can install a Kindle screensaver hack with a couple of downloads. 

Update: I've embedded a slideshow from PicasaWeb, but it requires Flash.  If you don't see it you can click on the links below to go directly to PicasaWeb.

• Kindle hacks: Installing a Kindle Screensaver, Kindle Screensaver Hack
• PicasaWeb: Infocom covers, Mad covers, Fake covers, Book covers