diff options
| author | Varun Gupta <varun.gupta@mariadb.com> | 2019-02-13 11:22:16 +0530 |
|---|---|---|
| committer | Varun Gupta <varun.gupta@mariadb.com> | 2019-02-13 11:52:36 +0530 |
| commit | be8709eb7bdf2a68a1c04fd8ab368113f5f39b63 (patch) | |
| tree | 35585804a654ec75089a92e90d546f73e7f60b68 /mysql-test/suite/funcs_1 | |
| parent | 6b979416e0e4eac0a036ca5f2b81b748a3d2e680 (diff) | |
| download | mariadb-git-be8709eb7bdf2a68a1c04fd8ab368113f5f39b63.tar.gz | |
MDEV-6111 Optimizer Trace
This task involves the implementation for the optimizer trace.
This feature produces a trace for any SELECT/UPDATE/DELETE/,
which contains information about decisions taken by the optimizer during
the optimization phase (choice of table access method, various costs,
transformations, etc). This feature would help to tell why some decisions were
taken by the optimizer and why some were rejected.
Trace is session-local, controlled by the @@optimizer_trace variable.
To enable optimizer trace we need to write:
set @@optimizer_trace variable= 'enabled=on';
To display the trace one can run:
SELECT trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
This task also involves:
MDEV-18489: Limit the memory used by the optimizer trace
introduces a switch optimizer_trace_max_mem_size which limits
the memory used by the optimizer trace. This was implemented by
Sergei Petrunia.
Diffstat (limited to 'mysql-test/suite/funcs_1')
4 files changed, 116 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index dfaa5d75137..386b0f07f98 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -205,6 +205,10 @@ def information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA 10 NULL YES varc def information_schema KEY_COLUMN_USAGE TABLE_CATALOG 4 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_NAME 6 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_SCHEMA 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES 4 0 NO tinyint NULL NULL 3 0 NULL NULL NULL tinyint(1) select NEVER NULL +def information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 3 0 NO int NULL NULL 10 0 NULL NULL NULL int(20) select NEVER NULL +def information_schema OPTIMIZER_TRACE QUERY 1 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select NEVER NULL +def information_schema OPTIMIZER_TRACE TRACE 2 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select NEVER NULL def information_schema PARAMETERS CHARACTER_MAXIMUM_LENGTH 8 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL def information_schema PARAMETERS CHARACTER_OCTET_LENGTH 9 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL def information_schema PARAMETERS CHARACTER_SET_NAME 13 NULL YES varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL @@ -743,6 +747,10 @@ NULL information_schema KEY_COLUMN_USAGE POSITION_IN_UNIQUE_CONSTRAINT bigint NU 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_COLUMN_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +1.0000 information_schema OPTIMIZER_TRACE QUERY longtext 4294967295 4294967295 utf8 utf8_general_ci longtext +1.0000 information_schema OPTIMIZER_TRACE TRACE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext +NULL information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE int NULL NULL NULL NULL int(20) +NULL information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES tinyint NULL NULL NULL NULL tinyint(1) 3.0000 information_schema PARAMETERS SPECIFIC_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) 3.0000 information_schema PARAMETERS SPECIFIC_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema PARAMETERS SPECIFIC_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index 96fb1e286c0..97346c4648d 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -205,6 +205,10 @@ def information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA 10 NULL YES varc def information_schema KEY_COLUMN_USAGE TABLE_CATALOG 4 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_NAME 6 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema KEY_COLUMN_USAGE TABLE_SCHEMA 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES 4 0 NO tinyint NULL NULL 3 0 NULL NULL NULL tinyint(1) NEVER NULL +def information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE 3 0 NO int NULL NULL 10 0 NULL NULL NULL int(20) NEVER NULL +def information_schema OPTIMIZER_TRACE QUERY 1 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext NEVER NULL +def information_schema OPTIMIZER_TRACE TRACE 2 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext NEVER NULL def information_schema PARAMETERS CHARACTER_MAXIMUM_LENGTH 8 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL def information_schema PARAMETERS CHARACTER_OCTET_LENGTH 9 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL def information_schema PARAMETERS CHARACTER_SET_NAME 13 NULL YES varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL @@ -743,6 +747,10 @@ NULL information_schema KEY_COLUMN_USAGE POSITION_IN_UNIQUE_CONSTRAINT bigint NU 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema KEY_COLUMN_USAGE REFERENCED_COLUMN_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +1.0000 information_schema OPTIMIZER_TRACE QUERY longtext 4294967295 4294967295 utf8 utf8_general_ci longtext +1.0000 information_schema OPTIMIZER_TRACE TRACE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext +NULL information_schema OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE int NULL NULL NULL NULL int(20) +NULL information_schema OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES tinyint NULL NULL NULL NULL tinyint(1) 3.0000 information_schema PARAMETERS SPECIFIC_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) 3.0000 information_schema PARAMETERS SPECIFIC_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema PARAMETERS SPECIFIC_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index 5fee1e0050a..9af3aa860a0 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -489,6 +489,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_TRACE +TABLE_TYPE SYSTEM VIEW +ENGINE MYISAM_OR_MARIA +VERSION 11 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA @@ -1530,6 +1555,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_TRACE +TABLE_TYPE SYSTEM VIEW +ENGINE MYISAM_OR_MARIA +VERSION 11 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index 5fee1e0050a..9af3aa860a0 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -489,6 +489,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_TRACE +TABLE_TYPE SYSTEM VIEW +ENGINE MYISAM_OR_MARIA +VERSION 11 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA @@ -1530,6 +1555,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME OPTIMIZER_TRACE +TABLE_TYPE SYSTEM VIEW +ENGINE MYISAM_OR_MARIA +VERSION 11 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS TABLE_TYPE SYSTEM VIEW ENGINE MYISAM_OR_MARIA |