New Features In MySQL 6.x

I thought I was clear in earlier blog postings about Performance Schema, but I’ll have to write more firmly.

1. Performance Schema source code is available:
https://code.launchpad.net/~marc.alff/mysql-server/mysql-6.0-perfschema

2. Performance Schema has been in development for less than one year.

3. All Performance Schema worklog task descriptions are public on forge.mysql.com.

I suppose it looks odd that many of the “6.x” features I’ve blogged about are now “5.4″ features. Sorry about the surprise. In mitigation I observe that this means some things are now scheduled a bit quicker, and some are a lot better than expected. Sometimes it’s hard to know till tests are in.

The current server 5.4 download is here:
http://dev.mysql.com/downloads/mysql/5.4.html.
It does not have all the features yet that we have announced as 5.4.

The features in the current 5.4 download are:

* Scalability, that is, it works faster if you add cores or processors.
A lot of the work for this happened outside the MySQL group per se, it’s lovely to have bunches of performance experts from elsewhere in Sun Microsystems, also known as Sun Classic, doing the tweaks for us. Tests show that 5.4 is faster than any variant, whether genuine MySQL or not.

There’s a slideshow, a blog from a Sun Classic employee, a blog from an old-time MySQL employee, and even a reference manual with sections “Changes in MySQL 5.4.0″ and “What Is New in MySQL 5.4″.

Large customers are trying out the beta with (as I understand) happiness. Tomorrow (May 21 2009) there will be a web seminar (”webinar”) on the subject, see http://www.mysql.com/news-and-events/web-seminars/display-343.html.

* DTrace. [sic -- with a capital T!]
The worklog task description “Improved DTrace support in server” (WL#4541) explains that this is about adding instrumentation in the SQL code for routines that aren’t system calls (system calls are handled without us having to add instrumentation). The WL#4541 description is out of date, and so is the manual at the time I’m writing this, so expect the list of instrumentation points to be better than what’s documented.

The features that are already pushed into 6.0, and therefore almost certain to be in 5.4, are:

* Things that make it go faster.
Well, this has been called a “performance release”, so the scalability improvements get coupled with reduced locking / mutex contention (WL#3262 WL#3561 WL#3726 WL#4284) , subquery semijoins and materializations (WL#1110 WL#2980 WL#3751 WL#3985), batch/range joining (WL#2475 WL#2771 WL#4424).

* Things which make programmers’ lives easier.
In this category I put SIGNAL and RESIGNAL (WL#2110 WL#2265), INFORMATION_SCHEMA addition (WL#2822 for thefirst time in public!, and the PARAMETERS view), replication additions, character-set additions (WL#4164 WL#3759) and XML additions (WL#1397).

The features that are not already pushed into 6.0, but which I personally expect have a good chance
of going to 5.4 (we’ll know by the end of June), are:
Performance Schema (WL#2360 WL#4678), Globalization (WL#1349 WL#3764 WL#3997 WL#4642), error messages (WL#751), number-to-string conversions (WL#2649), GIS (WL#1326 WL#2377 ), and partitioning (WL#4444 WL#4571).

There’s also a bit of refactoring work going on in mysys.
And there are bug fixes, including Bug#989.

I decided not to reference worklog tasks that are private, so the above list has a few gaps.

The Date

The goal is that 5.4 will be GA in December 2009. The date is earlier than we thought of for 6.0. I think it’ll probably make some people happy.

From the MySQL User Conference in Santa Clara.

Konstantin Osipov + Peter Gulutzan’s presentation, “New Foreign Keys in 6.1″, is now available as an odp (Open Office presentation slides) file here. Nobody stomped out in a rage. One attendee said it’s a great development, he’s been waiting for years to see multi-storage-engine foreign keys. Another asked whether the feature would be in Drizzle, and Peter didn’t have the answer at the time, but later he asked a Drizzle worker and heard it’s improbable.

Peter heard a sideshow presenter suggest that the MySQL / DB2 storage engine was a surprise. The person didn’t allow time for questions or comments, but here’s a belated note that we sent out the first press release two years ago, here.

Alexander Barkov + Peter Gulutzan’s BoF, about character sets, got only a few non-Sun attendees. The main concerns were not about new features, but about how to upgrade from old versions, or how to convert to UTF8. Peter made a foolish statement about stripping accents, for which apologies are in order, but it’s okay because nobody believed him at the time.

After the conference Peter, as if to remove all doubts re his foolishness, decided to walk around the adjacent communities of Milpitas and San Jose. Subsequent checking on Google Maps reveals that was a 25-mile stroll in the sun, so if you saw him later red-faced and limping, here’s hoping you didn’t get the wrong impression.

The rest of this blog post is the sheet that Konstantin Osipov and Peter Gulutzan used for the MySQL Camp demo, “MySQL 6.1 Test Drive”. Somebody was recording it; if you find the recording on the web, please send a comment. It looks better if you see the results, so come watch next time they demo.

Peter Gulutzan and Konstantin Osipov
Sun Microsystems

"This is a live nothing-behind-the-curtain demonstration
of features that actually work, in pre-alpha or alpha or
beta versions of MySQL including MySQL 6.0, 6.1, and future."

After each demonstration, there is a chance to ask
one or two questions. We will type more statements on request.

======================================================
Feature = Foreign keys / all storage engines (WL#148)
Demonstrator = Konstantin
Version = mysql-6.1-fk
Sample Statement text =
quit
/data1/mysql-6.1-fk.sh
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1
 (s1 INT UNIQUE NOT NULL)
 ENGINE=FALCON;                /* Falcon + foreign key! */
CREATE TABLE t2
 (s1 INT DEFAULT 5 REFERENCES t1(s1)/* Legal Syntax at last! */
 ON UPDATE SET DEFAULT)
 ENGINE=FALCON;
CREATE TRIGGER t2_au AFTER UPDATE ON t2
 FOR EACH ROW SET @a='trigger happened';
INSERT INTO t1 VALUES (1),(5);
INSERT INTO t2 VALUES (2);     /* Won't work! */
INSERT INTO t2 VALUES (1);
UPDATE t1 SET s1 = 2 WHERE s1 = 1; /* Cascades! */
SELECT *,@a FROM t2;           /* Shows cascading worked! */
=======================================================
Feature = Supplementary Unicode Characters (WL#1213)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t
  (s1 VARCHAR(5) CHARACTER SET UTF32); /* new character set! */
INSERT INTO t VALUES (0x000204d7);     /* rare cjk! */
INSERT INTO t VALUES (0x000100cc);     /* linear b! */
INSERT INTO t VALUES (0x00010400);     /* deseret! */
SELECT * FROM t;                       /* Visible with this font! */
=======================================================
Feature = Retrievable OUT parameters
Demonstrator = Konstantin
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1(OUT v1 INT, OUT v2 CHAR(32))
BEGIN
  SELECT 'procedure p1' as result;
  SET v1= 10;
  SET v2= 100;
END//
DELIMITER ;
PREPARE s1 FROM 'CALL p1(?, ?)';
EXECUTE s1 USING @u1, @u2;
SELECT @u1, @u2;
=======================================================
Feature = BACKUP and RESTORE
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
rm /data1/mysql-6.0/var/1
/data1/mysql-6.0.sh
DROP DATABASE d;
CREATE DATABASE d;
CREATE TABLE d.t (s1 INT);
INSERT INTO d.t VALUES (5);
BACKUP DATABASE d TO '1';             /* new statement! */
DROP DATABASE d;
SELECT * FROM d.t;                    /* now it's gone! */
RESTORE FROM '1';                     /* new statement! */
SELECT * FROM d.t;                    /* now it's back! */
=======================================================
Feature = EXECUTE IMMEDIATE (WL#2793)
Demonstrator = Konstantin
Version = 6.0-2793
Sample Statement text =
quit
/data1/mysql-6.0-2793.sh
DROP PROCEDURE IF EXISTS p1;
SET @@max_sp_recursion_depth = 5000;
EXECUTE IMMEDIATE 'SELECT 5';         /* literal! */
DELIMITER //
CREATE PROCEDURE p1 (name CHAR(255))
BEGIN                                 /* hara-kiri! */
  EXECUTE IMMEDIATE CONCAT('DROP PROCEDURE ', name);
  CALL p1(name);
END//
DELIMITER ;
CALL p1('p1');
=======================================================
Feature = Performance Schema
Demonstrator = Peter
Version = mysql-6.0-perf
Sample Statement text =
quit
/data1/mysql-6.0-perf.sh
SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT\G
SELECT event_name,count_star,sum_timer_wait,max_timer_wait
 FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
 WHERE COUNT_STAR > 0 ORDER BY sum_timer_wait DESC LIMIT 5;
=======================================================
Feature = SIGNAL + RESIGNAL (WL#2110, WL#2265)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p;
DROP TABLE IF EXISTS t;
SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='Oops'; /* customized error! */
DROP TABLE t;                                 /* default error! */
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  RESIGNAL SQLSTATE '22222';                  /* replaces default! */
  DROP TABLE t;                               /* activates handler! */
END//
DELIMITER ;
CALL p();
=======================================================
Feature = SHA2 (Bug#13174)
Demonstrator = Peter
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
SELECT sha2('a',256); /* Contributor = Bill Karwin */
=======================================================
Feature = Parameters view (WL#4301)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP FUNCTION IF EXISTS f;
CREATE FUNCTION f (parameter1 SMALLINT)
RETURNS SMALLINT
RETURN 5;
SELECT SPECIFIC_NAME AS RNAME, ORDINAL_POSITION AS POS,
PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE,
ROUTINE_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS;    /* 2 rows! */
=======================================================
Feature = Partition by string (WL#3352)
Demonstrator = Peter
Version = mysql-5.1-wl3352
Sample Statement text =
quit
/data1/mysql-5.1-wl3352.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR, b CHAR, c CHAR)
PARTITION BY RANGE COLUMN_LIST(a,b,c)
(PARTITION p0 VALUES LESS THAN (COLUMN_LIST('a','b','c')));
=======================================================
Feature = Less VARBINARY (WL#2649)
Demonstrator = Konstantin
Version = mysql-6.0-wl2649
Sample Statement text =
quit
/data1/mysql-6.0-wl2649.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT CONCAT(1);
SHOW CREATE TABLE t1;
=======================================================
Feature = WEIGHT_STRING function (WL#3716)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,WEIGHT_STRING(s1) FROM t;
DROP TABLE IF EXISTS t;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8
 COLLATE utf8_unicode_ci);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,HEX(WEIGHT_STRING(s1)) FROM t;