I get a lot of questions about Percona XtraDB Cluster 5.6 (PXC 5.6), specifically about whether such and such MySQL 5.6 Community Edition feature is in PXC 5.6. The short answer is: yes, all features in community MySQL 5.6 are in Percona Server 5.6 and, in turn, are in PXC 5.6. Whether or not the new feature is useful in 5.6 really depends on how useful it is in general with Galera.
I thought it would be useful to highlight a few features and try to show them working:
Innodb Fulltext Indexes
Yes, FTS works in Innodb in 5.6, so why wouldn’t it work in PXC 5.6? To test this I used the Sakila database , which contains a single table with FULLTEXT. In the sakila-schema.sql file, it is still designated a MyISAM table:
1 2 3 4 5 6 7 |
CREATE TABLE film_text ( film_id SMALLINT NOT NULL, title VARCHAR(255) NOT NULL, description TEXT, PRIMARY KEY (film_id), FULLTEXT KEY idx_title_description (title,description) )ENGINE=MyISAM DEFAULT CHARSET=utf8; |
I edited that file to change MyISAM to Innodb, loaded the schema and data into my 3 node cluster:
1 2 |
[root@node1 sakila-db]# mysql < sakila-schema.sql [root@node1 sakila-db]# mysql < sakila-data.sql |
and it works seamlessly:
1 2 3 4 5 6 7 8 9 10 11 |
node1 mysql> select title, description, match( title, description) against ('action saga' in natural language mode) as score from sakila.film_text order by score desc limit 5; +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ | title | description | score | +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ | FACTORY DRAGON | A Action-Packed Saga of a Teacher And a Frisbee who must Escape a Lumberjack in The Sahara Desert | 3.0801234245300293 | | HIGHBALL POTTER | A Action-Packed Saga of a Husband And a Dog who must Redeem a Database Administrator in The Sahara Desert | 3.0801234245300293 | | MATRIX SNOWMAN | A Action-Packed Saga of a Womanizer And a Woman who must Overcome a Student in California | 3.0801234245300293 | | REEF SALUTE | A Action-Packed Saga of a Teacher And a Lumberjack who must Battle a Dentist in A Baloon | 3.0801234245300293 | | SHANE DARKNESS | A Action-Packed Saga of a Moose And a Lumberjack who must Find a Woman in Berlin | 3.0801234245300293 | +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ 5 rows in set (0.00 sec) |
Sure enough, I can run this query on any node and it works fine:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
node3 mysql> select title, description, match( title, description) against ('action saga' in natural language mode) as score from sakila.film_text order by score desc limit 5; +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ | title | description | score | +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ | FACTORY DRAGON | A Action-Packed Saga of a Teacher And a Frisbee who must Escape a Lumberjack in The Sahara Desert | 3.0801234245300293 | | HIGHBALL POTTER | A Action-Packed Saga of a Husband And a Dog who must Redeem a Database Administrator in The Sahara Desert | 3.0801234245300293 | | MATRIX SNOWMAN | A Action-Packed Saga of a Womanizer And a Woman who must Overcome a Student in California | 3.0801234245300293 | | REEF SALUTE | A Action-Packed Saga of a Teacher And a Lumberjack who must Battle a Dentist in A Baloon | 3.0801234245300293 | | SHANE DARKNESS | A Action-Packed Saga of a Moose And a Lumberjack who must Find a Woman in Berlin | 3.0801234245300293 | +-----------------+-----------------------------------------------------------------------------------------------------------+--------------------+ 5 rows in set (0.05 sec) node3 mysql> show create table sakila.film_textG *************************** 1. row *************************** Table: film_text Create Table: CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
There might be a few caveats and differences from how FTS works in Innodb vs MyISAM, but it is there.
Minimal replication images
Galera relies heavily on RBR events, but until 5.6 those were entire row copies, even if you only changed a single column in the table. In 5.6 you can change this to send only the updated data using the variable binlog_row_image=minimal.
Using a simple sysbench update test for 1 minute, I can determine the baseline size of the replicated data:
1 2 3 4 5 6 7 8 9 |