Changes in AliSQL 5.6.32 (2017 05 04)

AliSQL 5.6.32 Release Notes (2017-05-04)

Abstract

Here are the AliSQL Youth Day Release Notes:

1. Add Column Dynamically

A new InnoDB record format named COMFORT was desiged to support adding column dynamically. It will be no longer needed to copy all rows when launched ALTER TABLE ADD COLUMN statement if the table has ROW_FORMAT=COMFORT option.

2. Thread Pool

Thread pool is designed to cope with enormous connection requests by sharing limited server thread. Compared to one-thread-per-connection strategy, it can save a lot of context switch CPU time.
We port this functionality from MariaDB.

Functionality Added or Changed

1. Add Column Dynamically

Description:

The original adding column will copy all rows, it will consume very long time. Now the new row_format COMFORT for InnoDB can add column dynamically.

The InnoDB COMFORT record format such as:
[lens | n_nulls | n_fields | extra | id...]

1. extra info_bits will occupy 1 bit to flag COMFORT.
2. n_fields will occupy 1 or 2 bytes to save the column count.

then ADD COLUMN statement will only change the dictionary and cache.

Limition:
The columns can only added at last, and nullable, non-default value.

Parameters:
no

Usage:

CREATE TABLE test(
  id number,
  col1 varchar(255)
)ENGINE= InnoDB ROW_FORMAT= COMFORT;

ALTER TABLE test ADD COLUMN col2 int;

2. Thread Pool

Description:
With one-thread-per-connection strategy MySQL server will create a dedicated thread to service every connection. It will be inefficient if enormous connection requests.
Pool-of-thread strategy only create shared and limited threads to service all connections，and keep the throughput stable no matter how many connection requests.

Parameters:

1. thread_pool_min_threads

   System Variable Name	thread_pool_min_threads
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, 256]
   Default	1
   Description

   Minimum number of threads in the threadpool .

2. thread_pool_idle_timeout

   System Variable Name	thread_pool_idle_timeout
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, UINT_MAX]
   Default	60
   Description

   Timeout in seconds for idle thread, worker thread will shutdown if timeout

3. thread_pool_oversubscribe

   System Variable Name	thread_pool_oversubscribe
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, 1000]
   Default	3
   Description

   How many additional active worker threads in a group are allowed

4. thread_pool_size

   System Variable Name	thread_pool_size
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, 128]
   Default	Number of processors
   Description

   The number of threads that can use the CPU at the same time

5. thread_pool_stall_limit

   System Variable Name	thread_pool_stall_limit
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[1, UINT_MAX]
   Default	10
   Description

   The number of milliseconds before a running thread is considered stalled When this limit is reached thread pool will wake up or create another thread.

6. thread_pool_high_prio_tickets

   System Variable Name	thread_pool_high_prio_tickets
   Variable Scope	global
   Dynamic Variable	YES
   Permitted Values	[0, UINT_MAX]
   Default	UINT_MAX
   Description

   Number of tickets to enter high priority event queue for each transaction

7. thread_pool_high_prio_mode

   System Variable Name	thread_pool_high_prio_mode
   Variable Scope	session
   Dynamic Variable	YES
   Permitted Values	[transactions, statements, none]
   Default	transactions
   Description

   High priority queue mode: one of 'transactions', 'statements', 'none'

Usage:
no