It's a well-know fact that InnoDB secondary keys contain both user defined columns and the primary key columns. For example, if a table has PRIMARY KEY(pk) and secondary key k1(f1), then index k1 is internally stored as k1(f1,pk).

Prior to version 5.6.9, the MySQL optimizer could only partially use these extended primary key columns: they could be used for sorting and to provide index only access. Starting from MySQL 5.6.9, the optimizer makes full use of the extended columns. This means that 'ref' access, range access, MIN/MAX optimizations, index_merge, loose index scan etc all works as if you had created the index with all primary key columns in all secondary keys. The new feature is turned on and off by optimizer switch 'use_index_extensions' and is on by default.

Consider we have following table:

CREATE TABLE t1
(
  f1 INT NOT NULL DEFAULT '0',
  f2 INT NOT NULL DEFAULT …

I would like to thank you again for all positive feedbacks I got on MyXplain.
This project has just begun but I am glad he has already affected many of you, it is a very pleasant surprise for us.

We are already working on very exciting features that I hope will emerge in the first quarter of 2013, stay tuned!

Now, if you would like to contribute to MyXplain, several options are available :

Of course you can propose a link, a slide or a book via the box available on website.
Feel free to leave comments on the options that you have some experience or expertise.

To go further, you can also provide a link to your blog or website and even make a donation, Christmas is coming soon, enjoy!
Only $10 can allow MyXplain to run for 1 more …

How easy is it to identify and debug Percona XtraDB Cluster replication problem ?

If you are using PXC, you may have already seen in your datadirectory several log files starting with GRA_

Those files correspond to a replication failure. That means the slave thread was not able to apply one transaction. For each of those file, a corresponding warning or error message is present in the mysql error log file.

Those error can also be false positive like a bad DDL statement (DROP a table that doesn’t exists for example) and therefore nothing to worry about. However it’s always recommended to understand what’s is happening.

As the GRA files contain binlog events in ROW format representing the failed transaction this post explains how to proceed.

The first step to be able to analyze your GRA files is to add a binlog header to the file.
You can download one here : …

Every time I visit my family for the holidays, as the date approaches, I find myself filled with dread. It’s nothing sinister, my family’s great, and the season is nice. The reason is simple:

I hate packing.

In fact, I hate both kinds of packing: trip packing, and bit packing. Let me tell you a story about bit packing.

Bit packing

If you’ve ever browsed around the available type attributes in GCC, you may have noticed the entry for “packed”. It seems straightforward enough, and if you’re trying to cram a lot of data in a system (like we do), it can be pretty attractive.

There are plenty …

We are really getting into the Christmas spirit this year, so we are offering 25% flat discount on all your purchases in the run-up to the big day. Time to go shopping again and buy yourself a gift, and make super savings of the year.

It’s a limited period offer, so head right away to the Online Shop and apply the coupon code save25.

Cheers,
Team Webyog

Tweet

The post Christmas Offer – 25% flat discount on all products! appeared first on Webyog Blog.

There have been several reports (1,2,3) describing Percona’s stance regarding the MariaDB Foundation that are not totally accurate so I though it would be worth it to describe where we stand on this and related matters.

First, let me say the creation of theMariaDB Foundation is a good thing for the MariaDB Community and I’m very pleased to be in the group of those select people Monty chose to brief and seek feedback from before it was announced publicly. A Foundation is a form of governance for many mature open source projects, such as …

A week or two ago I heard second hand about someone saying "they can't use Galera because they use SAN for everything". Well, just so there is no doubt: You can perfectly well use Galera together with SAN. Galera is quite agnostic about the disks used.

In fact, Galera might be a great option together with SAN. Often SAN means slightly worse latency for disk writes, in particular when used with iSCSI. If the network path to the SAN is poor, it might mean a big performance hit, actually. Galera is in fact a good fit here, because the default/recommended setting for a Galera cluster is to relax the durability settings for InnoDB (innodb_flush_log_at_trx_commit), since durability is primarily guaranteed by the syncrhonous nature of the replication. (Same philosophy as is applied in the architecture for MySQL NDB Cluster.) This reduced stress on the disk can actually improve your experience with a SAN. A case in point is …

We’re always testing the latest versions of MySQL with most of the environments to make sure that we can find the critical issues before it goes to production. This wasn’t different with the 5.6 MySQL neither. We already started to play with this version in the summer. The first news were very promising. The performance gain is significant. However we have run into couple of errors. One of them was the

mysql_install_db problem with replicating environment

[root@hostname ~]# mysql_install_db --user=mysql --datadir=/mysql/data/

------------------------------------------------------------------------------------
Installing MySQL system tables...
121217 10:02:20 InnoDB: The InnoDB memory heap is disabled
121217 10:02:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121217 10:02:20 InnoDB: Compressed tables use zlib 1.2.3
121217 10:02:20 InnoDB: Using Linux native AIO
121217 10:02:20 InnoDB: CPU supports crc32 instructions
121217 10:02:20 …

It is time for christmas presents: some sharding support and cache locality optimizations are coming with PECL/mysqlnd_ms 1.5. PECL/mysqlnd_ms is a plugin for the mysqlnd library. The plugin adds replication and load balancing support to any PHP MySQL API (mysql, mysqli, PDO_MySQL) if compiled to use the mysqlnd library.

As a MySQL user you can choose between a wide variety of clustering solutions to scale-out. Your options range from eventual consistent solutions to strong consistent ones, from built-in (MySQL Replication, MySQL Cluster) to third party or home-grown. PECL/mysqlnd_ms is a client side load balancer that aims to serve all. …

The old MySQL.com logins will go away on Wednesday, December 19th and will be replaced by the Oracle Single Sign On (SSO).

This Oracle SSO will replace the old MySQL.Com login starting December 20th.

So what do you do? Go to Oracle.com and login. If your account was migrated you will be able to login. But the crimson invalid login message will require you to set up a new account. Some may be wary of providing more personal information. But this is being done to bring the MySQL.com web sites into compliance with our corporate rules and provide a better flow for those of you using more than one Oracle product.

Q: Will this affect voting statistics on Plant.MySQL.Com?
A: Probably. The run away leaders have been Giuseppe Maxia and …