Some extensions to *note 'SHOW': show. statements accompany the implementation of 'INFORMATION_SCHEMA':
*note 'SHOW': show. can be used to get information about the structure of 'INFORMATION_SCHEMA' itself.
Several *note 'SHOW': show. statements accept a 'WHERE' clause that provides more flexibility in specifying which rows to display.
The 'IS_UPDATABLE' flag may be unreliable if a view depends on one or more other views, and one of these underlying views is updated. Regardless of the 'IS_UPDATABLE' value, the server keeps track of the updatability of a view and correctly rejects data change operations to views that are not updatable. If the 'IS_UPDATABLE' value for a view has become inaccurate to due to changes to underlying views, the value can be updated by deleting and recreating the view.
'INFORMATION_SCHEMA' is an information database, so its name is included in the output from note 'SHOW DATABASES': show-databases. Similarly, note 'SHOW TABLES': show-tables. can be used with 'INFORMATION_SCHEMA' to obtain a list of its tables:
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+note 'SHOW COLUMNS': show-columns. and note 'DESCRIBE': describe. can display information about the columns in individual 'INFORMATION_SCHEMA' tables.
*note 'SHOW': show. statements that accept a 'LIKE' clause to limit the rows displayed also permit a 'WHERE' clause that specifies more general conditions that selected rows must satisfy:
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLESThe 'WHERE' clause, if present, is evaluated against the column names displayed by the note 'SHOW': show. statement. For example, the note 'SHOW CHARACTER SET': show-character-set. statement produces these output columns:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...To use a 'WHERE' clause with *note 'SHOW CHARACTER SET': show-character-set, you would refer to those column names. As an example, the following statement displays information about character sets for which the default collation contains the string ''japanese'':
mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+This statement displays the multibyte character sets:
mysql> SHOW CHARACTER SET WHERE Maxlen > 1;
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+File: manual.info.tmp, Node: performance-schema, Next: sys-schema, Prev: information-schema, Up: Top
25 MySQL Performance Schema ***************************
Menu:
performance-schema-restrictions:: Restrictions on Performance Schema
The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema has these characteristics:
The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented using the *note 'PERFORMANCE_SCHEMA': performance-schema. storage engine and the 'performance_schema' database. The Performance Schema focuses primarily on performance data. This differs from 'INFORMATION_SCHEMA', which serves for inspection of metadata.
The Performance Schema monitors server events. An 'event' is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.
Performance Schema events are distinct from events written to the server's binary log (which describe data modifications) and Event Scheduler events (which are a type of stored program).
Performance Schema events are specific to a given instance of the MySQL Server. Performance Schema tables are considered local to the server, and changes to them are not replicated or written to the binary log.
Current events are available, as well as event histories and summaries. This enables you to determine how many times instrumented activities were performed and how much time they took. Event information is available to show the activities of specific threads, or activity associated with particular objects such as a mutex or file.
The *note 'PERFORMANCE_SCHEMA': performance-schema. storage engine collects event data using 'instrumentation points' in server source code.
Collected events are stored in tables in the 'performance_schema' database. These tables can be queried using *note 'SELECT': select. statements like other tables.
Performance Schema configuration can be modified dynamically by updating tables in the 'performance_schema' database through SQL statements. Configuration changes affect data collection immediately.
Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.
Monitoring is available on all platforms supported by MySQL.
Some limitations might apply: The types of timers might vary per platform. Instruments that apply to storage engines might not be implemented for all storage engines. Instrumentation of each third-party engine is the responsibility of the engine maintainer. See also *note performance-schema-restrictions::.
Data collection is implemented by modifying the server source code to add instrumentation. There are no separate threads associated with the Performance Schema, unlike other features such as replication or the Event Scheduler.
The Performance Schema is intended to provide access to useful information about server execution while having minimal impact on server performance. The implementation follows these design goals:
Activating the Performance Schema causes no changes in server behavior. For example, it does not cause thread scheduling to change, and it does not cause query execution plans (as shown by *note 'EXPLAIN': explain.) to change.
Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.
The parser is unchanged. There are no new keywords or statements.
Execution of server code proceeds normally even if the Performance Schema fails internally.
When there is a choice between performing processing during event collection initially or during event retrieval later, priority is given to making collection faster. This is because collection is ongoing whereas retrieval is on demand and might never happen at all.
It is easy to add new instrumentation points.
Instrumentation is versioned. If the instrumentation implementation changes, previously instrumented code continues to work. This benefits developers of third-party plugins because it is not necessary to upgrade each plugin to stay synchronized with the latest Performance Schema changes.
Note:
The MySQL 'sys' schema is a set of objects that provides convenient access to data collected by the Performance Schema. The 'sys' schema is installed by default. For usage instructions, see *note sys-schema::.
File: manual.info.tmp, Node: performance-schema-quick-start, Next: performance-schema-build-configuration, Prev: performance-schema, Up: performance-schema