Changes in AliSQL 5.6.32 (2017 02 14)

AliSQL 5.6.32 Release Notes (2017-02-14)

Abstract

AliSQL Valentine Release opened three important features:

1. Sequence engine

Sequence as a new object type that supplied simple user interface to use, as below:

create sequence s
       start with 1
       minvalue 1
       maxvalue 9999999
       increment by 1
       cache 20
       cycle;
select nextval for s;

SEQUENCE is designed as a MySQL engine, implemented most handler interface. But SEQUENCE is only logical engine, as it used InnoDB or other real storage engine as the based table engine, so user can use 'SHOW CREATE TABLE/SEQUENCE' command to display the actual sequence table. Almost all external tools are unneccessary to change， such as mysqldump or XtraBackup.

Details seen Sequence Document. AliSQL-Sequence-Doc_C .

2. Semisync optimization

Semisync plugin is changed to buildin statically, and improved the performance.
Add [after_sync, after_commit] options to decide when to wait for ACK.
Remove the LOCK_log lock within dump thread, used end_position lock instead.

3. Persistent InnoDB AUTO_INCREMENT value

Reuse the InnoDB CLUSTER INDEX root page PAGE_MAX_TRX_ID position to save auto_increment value, and the operation is protected by redo log.

Functionality Added or Changed

1. SEQUENCE ENGINE

Description:

SEQUENCE engine as an embedded logical engine, it mainly used to generate unique number, it is the middle layer engine that use InnoDB or other storage engine as the based table engine, All query on sequence table will be changed into the operation on based table. the cache or other sequence value management is decided by SEQUENCE engine handler.

According to the setting which is defined by CREATE SEQUENCE ... or CREATE SEQUENCE TABLE... + INSERT VALUES, user can query the nextval or currval from sequence. In order to distinguish the normal SELECT statement, we supply new Syntax SELECT NEXTVAL FOR SEQUENCE:

1. SELECT NEXTVAL FROM SEQUENCE  will return the based table record directly.
2. SELECT NEXTVAL FOR SEQUENCE  will return the iteratored record.

Syntax: CREATE SEQUENCE SYNTAX:

    CREATE SEQUENCE [IF NOT EXISTS] schema.sequence_name
       [START WITH <constant>]
       [MINVALUE <constant>]
       [MAXVALUE <constant>]
       [INCREMENT BY <constant>]
       [CACHE <constant> | NOCACHE]
       [CYCLE | NOCYCLE]
      ;

    OR:
      CREATE SEQUENCE schema.sequence_name (
      `currval` bigint(21) NOT NULL COMMENT 'current value',
      `nextval` bigint(21) NOT NULL COMMENT 'next value',
      `minvalue` bigint(21) NOT NULL COMMENT 'min value',
      `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
      `start` bigint(21) NOT NULL COMMENT 'start value',
      `increment` bigint(21) NOT NULL COMMENT 'increment value',
      `cache` bigint(21) NOT NULL COMMENT 'cache size',
      `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
      `round` bigint(21) NOT NULL COMMENT 'already how many round'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    INSERT INTO schema.sequence_name VALUES(0,0,1,9223372036854775807,1,1,10000,1,0);
    COMMIT;

Strongly recommend the first CREATE SEQUENCE syntax.

SHOW SYNTAX:

      SHOW CREATE SEQUENCE schema.sequence_name;
      SHOW CREATE TABLE schema.sequence_name;

QUERY SYNTAX:

      SELECT [nextval | currval | *] FOR schema.sequence_name;
      SELECT [nextval | currval | *] FROM schema.sequence_name;

Parameters: no

Usage:

      create sequence s;
      create table t(id int);
      select nextval for s;
      insert into t select nextval for s;

2. Optimize performance of semisync

Description:

1. Make semisync buildin to completely remove overhead of plugin lock
2. Remove LOCK_log requirement from dump thread
3. Now user threads can wait for ACK before innodb commit
   add rpl_semi_sync_master_wait_point to control this behavior(AFTER_COMMIT/AFTER_SYNC)
4. Add a new ACK thread to handle ACK from slave
5. The IO thread flushs master info only when ACK is needed
   add rpl_semi_sync_slave_delay_master to control this behavior
6. Fix bug#70669, if sync_binlog = 1 ,then notify dump thread after fsync of binlog file

Parameters:

1. rpl_semi_sync_master_wait_point

   System Variable Name	rpl_semi_sync_master_wait_point
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[after_sync, after_commit]
   Default	after_commit
   Description	Watting for slave ack before/after commit trx

Usage: no

3. Persistent AUTO_INCREMENT value for InnoDB table

Description:

InnoDB auto_increment value is not persistent and can lead to some values reused, as described by official bug #199.

This patch fix the problem by writing the last used AUTO_INCREMENT to the root page of the clustered index, using the PAGE_MAX_TRX_ID field, which is unused for root page.

Two InnoDB variables are introduced to control this persistent behaviour:

• innodb_autoinc_persistent This persistent behaviour is enabled or not.

• innodb_autoinc_persistent_interval The interval of persist max auto_increment value.

Parameters:

1. innodb_autoinc_persistent

   System Variable Name	innodb_autoinc_persistent
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[ON, OFF]
   Default	OFF
   Description	This persistent behaviour is enabled or not

2. innodb_autoinc_persistent_interval

   System Variable Name	innodb_autoinc_persistent
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, 10000]
   Default	1
   Description	The interval of persist max auto_increment value

Usage:

no