<a href="#_Toc458365216">24 MySQL sys架構... 1</a>
<a href="#_Toc458365217">24.1 sys架構的前提條件... 1</a>
<a href="#_Toc458365218">24.2 使用sys架構... 2</a>
<a href="#_Toc458365219">24.3 sys架構進度報告... 3</a>
<a href="#_Toc458365220">24.4 sys架構的對象... 3</a>
<a href="#_Toc458365221">24.4.1所有sys下的對象... 3</a>
<a href="#_Toc458365222">24.4.2 sys架構的表和觸發器... 8</a>
<a href="#_Toc458365223">24.4.2.1 sys_config. 8</a>
<a href="#_Toc458365224">24.4.3 性能架構視圖... 10</a>
<a href="#_Toc458365225">24.4.4 sys架構存儲過程... 13</a>
<a href="#_Toc458365226">24.4.5 sys架構存儲函數... 14</a>
在開始使用sys架構之前,有一些前提條件,sys需要mysql 5.6或者更高版本。因為sys是performance_schema的替代方案,performance_schema必須啟動,sys才能夠使用。
為了完全通路sys,需要有以下權限:
· Sys表和視圖的select權限。
· Sys存儲過程和函數的exec權限。
· Sys_config表的insert,update權限。
· 另外執行存儲過程需要一些其他的權限,可以看存儲過程的描述。
其他權限:
· Sys架構對象通路的任何performance_Schema的表的select權限,和sys架構對象更新的任何表的update權限。
· Information_schema.innodb_buffer_page的process表。
特定的performance_Schema消費者和記錄點要啟動:
· 所有的等待記錄點
· 所有stage記錄點
· 所有statement記錄點
· Xxx_current和xxx_history_long消費者相關的所有事件。
你可以使用sys下面的存儲過程啟動所有這些選項:
CALL sys.ps_setup_enable_instrument('wait');
CALL sys.ps_setup_enable_instrument('stage');
CALL sys.ps_setup_enable_instrument('statement');
CALL sys.ps_setup_enable_consumer('current');
CALL sys.ps_setup_enable_consumer('history_long');
注意點:
對于很多sys的使用,預設的性能架構資料收集就能夠滿足,啟動所有的記錄點和消費者會對性能有一點影響,是以最好隻啟動你要的配置。通過這個函數也可以傳回預設配置:
CALL
sys.ps_setup_reset_to_default(TRUE);
檢視sys版本和mysql版本:
mysql> USE sys;
Database changed
mysql> SELECT * FROM version;
+-------------+-----------------+
| sys_version |
mysql_version |
|
1.5.0 | 5.7.9-debug-log |
Sys架構下包含了很多視圖合計了性能架構的表。很多這些視圖成對出現的,比如一個成員的名字和另外一個一樣,隻是加了x$的字首。比如host_summary_by_file_io有個名字一樣的x$host_summary_by_file_io,2個顯示的機關不同。
mysql> SELECT * FROM
host_summary_by_file_io;
+------------+-------+------------+
host | ios | io_latency |
| localhost
| 67570 | 5.38 s |
| background
| 3468 | 4.18 s |
x$host_summary_by_file_io;
+------------+-------+---------------+
host | ios |
io_latency |
| 67574 | 5380678125144 |
| 3474 | 4758696829416 |
沒有x$字首的表更容易讀。帶x$和不帶x$顯示的數值是一樣大的用來用具擷取和處理這些資料。
可以使用show 語句或者information_schema的查詢擷取對象的定義比如:
mysql> SHOW CREATE VIEW session;
mysql> SHOW CREATE FUNCTION format_bytes;
mysqldump,mysqlpump預設不導出sys架構,導出sys需要顯示設定:
mysqldump
--databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql
導入sys結構:
mysql <
sys_dump.sql
Mysql 5.7.9,在sys下提供了長運作事務的進度報告:
Processlist
session
x$processlist
x$session
假設請求的記錄點和消費者已經啟動了,這些視圖的progress列顯示了完成的百分比。
stage進度報告要啟動events_stages_current消費者,還有一些需要啟動的記錄點資訊:
stage/sql/Copying
to tmp table
stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
stage/innodb/alter table (log apply table)
stage/innodb/alter table (merge sort)
stage/innodb/alter table (read PK 和 internal sort)
stage/innodb/buffer pool load
對于stage不支援履歷和完成工作報告,如果請求記錄點和消費者沒有啟動,progress列為null。
Table 24.1 sys Schema
Tables 和 Triggers
Table or
Trigger Name
Description
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-sys-config.html">sys_config</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-sys-config-insert-set-user.html">sys_config_insert_set_user</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-sys-config-update-set-user.html">sys_config_update_set_user</a>
View Name
Statement
activity, file I/O, 和
connections, grouped by host
File I/O,
grouped by host
grouped by host 和
event type
stages, grouped by host
statistics, grouped by host
Statements
executed, grouped by host 和
statement
InnoDB buffer information, grouped by schema
InnoDB buffer information, grouped by schema 和 table
InnoDB lock information
I/O consumers,
grouped by thread
Global I/O
consumers, grouped by file 和
bytes
latency
consumers, grouped by bytes
consumers, grouped by latency
Most recent
I/O, grouped by file 和
thread
Memory use,
grouped by user
grouped by allocation type
Total memory
use
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-metrics.html">metrics</a>
Server metrics
information
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-check-lost-instrumentation.html">ps_check_lost_instrumentation</a>
Variables that
have lost instruments
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-auto-increment-columns.html">schema_auto_increment_columns</a>
AUTO_INCREMENT column information
Index
statistics
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-overview.html">schema_object_overview</a>
Types of
objects within each schema
<a title="24.4.3.27 The schema_redundant_indexes and x$schema_flattened_keys Views" href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-redundant-indexes.html">schema_redundant_indexes</a>
Duplicate or
redundant indexes
Sessions
waiting for metadata locks
Table
statistics, including InnoDB buffer pool statistics
Tables being
accessed with full scans
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-unused-indexes.html">schema_unused_indexes</a>
Indexes not in
active use
information for user sessions
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-session-ssl-status.html">session_ssl_status</a>
Connection SSL
aggregate statistics
Statements that
have produced errors or warnings
have done full table scans
Statements with
highest average runtime
performed sorts
used temporary tables
User statement 和 connection
activity
grouped by user 和
event
Stage events,
statistics, grouped by user
executed, grouped by user 和
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version.html">version</a>
versions
Wait class
average latency, grouped by event class
total latency, grouped by event class
Wait events,
grouped by event
<a title="24.4.3.38 The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views" href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-runtimes-in-95th-percentile.html">x$ps_digest_95th_percentile_by_avg_us</a>
Helper view for
95th-percentile views
<a title="24.4.3.38 The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views" href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-runtimes-in-95th-percentile.html">x$ps_digest_avg_latency_distribution</a>
<a title="24.4.3.29 The schema_table_statistics and x$schema_table_statistics Views" href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics.html">x$ps_schema_table_statistics_io</a>
table-statistics views
<a title="24.4.3.27 The schema_redundant_indexes and x$schema_flattened_keys Views" href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-redundant-indexes.html">x$schema_flattened_keys</a>
Helper view
Procedure Name
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-create-synonym-db.html">create_synonym_db()</a>
Create synonym
for schema
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-diagnostics.html">diagnostics()</a>
Collect system
diagnostic information
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-execute-prepared-stmt.html">execute_prepared_stmt()</a>
Execute
prepared statement
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-background-threads.html">ps_setup_disable_background_threads()</a>
Disable
background thread instrumentation
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-consumer.html">ps_setup_disable_consumer()</a>
consumers
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-instrument.html">ps_setup_disable_instrument()</a>
instruments
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-thread.html">ps_setup_disable_thread()</a>
instrumentation for thread
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-background-threads.html">ps_setup_enable_background_threads()</a>
Enable
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-consumer.html">ps_setup_enable_consumer()</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-instrument.html">ps_setup_enable_instrument()</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-thread.html">ps_setup_enable_thread()</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reload-saved.html">ps_setup_reload_saved()</a>
Reload saved
Performance Schema configuration
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reset-to-default.html">ps_setup_reset_to_default()</a>
Reset saved
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-save.html">ps_setup_save()</a>
Save
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled.html">ps_setup_show_disabled()</a>
Display
disabled Performance Schema configuration
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled-consumers.html">ps_setup_show_disabled_consumers()</a>
disabled Performance Schema consumers
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled-instruments.html">ps_setup_show_disabled_instruments()</a>
disabled Performance Schema instruments
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled.html">ps_setup_show_enabled()</a>
Display enabled
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled-consumers.html">ps_setup_show_enabled_consumers()</a>
Performance Schema consumers
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled-instruments.html">ps_setup_show_enabled_instruments()</a>
Performance Schema instruments
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-statement-avg-latency-histogram.html">ps_statement_avg_latency_histogram()</a>
statement latency histogram
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-trace-statement-digest.html">ps_trace_statement_digest()</a>
Trace
Performance Schema instrumentation for digest
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-trace-thread.html">ps_trace_thread()</a>
Dump
Performance Schema data for thread
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-truncate-all-tables.html">ps_truncate_all_tables()</a>
Truncate
Performance Schema summary tables
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statement-performance-analyzer.html">statement_performance_analyzer()</a>
Report of
statements running on server
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html">table_exists()</a>
Whether a table
exists
Function Name
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-extract-schema-from-file-name.html">extract_schema_from_file_name()</a>
Extract schema
name from file path name
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-extract-table-from-file-name.html">extract_table_from_file_name()</a>
Extract table
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-bytes.html">format_bytes()</a>
Convert byte
value to value with units
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-path.html">format_path()</a>
Replace data 和 temp-file
directories in path name with symbolic values
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-statement.html">format_statement()</a>
Truncate long
statement to fixed length
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-time.html">format_time()</a>
Convert
picoseconds value to value with units
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-list-add.html">list_add()</a>
Add item to
list
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-list-drop.html">list_drop()</a>
Remove item
from list
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-account-enabled.html">ps_is_account_enabled()</a>
Check whether
account instrumentation is enabled
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-consumer-enabled.html">ps_is_consumer_enabled()</a>
consumer is enabled
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-enabled.html">ps_is_instrument_default_enabled()</a>
instrument is enabled
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-timed.html">ps_is_instrument_default_timed()</a>
instrument is timed
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-thread-instrumented.html">ps_is_thread_instrumented()</a>
thread is instrumented
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-account.html">ps_thread_account()</a>
Return account
for thread ID
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-id.html">ps_thread_id()</a>
Return thread
ID for connection ID
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-stack.html">ps_thread_stack()</a>
Return event
information for thread ID
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-trx-info.html">ps_thread_trx_info()</a>
Return
transaction information for thread ID
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-quote-identifier.html">quote_identifier()</a>
Return string
as quoted identifier
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-sys-get-config.html">sys_get_config()</a>
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-major.html">version_major()</a>
MySQL server
major version number
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-minor.html">version_minor()</a>
minor version number
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-patch.html">version_patch()</a>
patch release version number
Sys_config表列:
·
Variable:配置選項
value:選項的值
set_time:最近一次修改時間。
set_by:最近一次修改使用者
為了最小化直接讀取sys_config表的影響,sys架構下的函數用來檢查使用者定義的變量和相關的名字,這個名字使用變量以@sys.為字首。如果目前會話有使用者定義的變量部位null,那麼就優先使用變量上的長度。否則就讀取表上的值:
mysql> SET @stmt = 'SELECT variable, value,
set_time, set_by FROM sys_config';
mysql> SELECT format_statement(@stmt);
+----------------------------------------------------------+
format_statement(@stmt)
| SELECT
variable, value, set_time, set_by FROM sys_config |
mysql> SET @sys.statement_truncate_len = 32;
+-----------------------------------+
format_statement(@stmt)
| SELECT variabl
... ROM sys_config |
之後會話中的會繼續使用變量的32,而不是使用表裡面的64。
為了停止變量的使用可以使用以下語句,取消或者關閉目前會話:
mysql> SET @sys.statement_truncate_len =
NULL;
變量可以在會話結束前生效,如果sys_config在會話中被修改,這個修改不會展現在會話上,除非會話結束。
Sys_config和變量的配置值:
diagnostics.all_i_s_tables, @sys.diagnostics.all_i_s_tables
如果選項為on,diagnostics()過程允許對information_schema.tables表進行掃描。如果表很多這個操作花費就比較大。
diagnostics.include_raw,@sys. diagnostics.include_raw
如果選項為on,diagnostics()過程包含了metrics視圖輸出的原生資料。預設為off。
ps_thread_trx_info.max_length,@sys. ps_thread_trx_info.max_length
ps_thread_trx_info()函數輸出的最大json的長度。
statement_performance_analyzer.limit,@sys.statement_performance_analyzer.limit
視圖傳回的最大行數,編譯沒有限制。最大為100.
statement_performance_analyzer.view,@sys.statement_performance_analyzer.view
statement_performance_analyzer()過程使用到的視圖和查詢。如果選項值包含了空間就表示是一個查詢,否則必須是events_statements_summary_by_digest表上的視圖。如果上面的limit>0就不能有limit子句。預設我null。
statement_truncate_len,
@sys.statement_truncate_len
format_statement()函數傳回的最大語句長度,長的語句會被截斷,預設64.
其他選項可以通過sys_config表添加。比如diagnostics(),execute_prepared_stmt()的調試選項,但是不是sys_config的預設有的選項:
mysql> INSERT INTO sys_config (variable,
value) VALUES('debug', 'ON');
修改debug資訊:
mysql> UPDATE sys_config SET value = 'OFF'
WHERE variable = 'debug';
mysql> SET @sys.debug = NULL;
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary.html">host_summary 和 x$host_summary Views</a>
視圖顯示了語句活動,檔案io和連接配接資訊,由host分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-file-io.html">host_summary_by_file_io 和 x$host_summary_by_file_io Views</a>
視圖總計了檔案io,由host分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-file-io-type.html">host_summary_by_file_io_type 和 x$host_summary_by_file_io_type Views</a>
視圖總計了檔案io,由host和event類型分組。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-stages.html">host_summary_by_stages 和 x$host_summary_by_stages Views</a>
總計語句stage,由host分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-statement-latency.html">host_summary_by_statement_latency 和 x$host_summary_by_statement_latency Views</a>
總計語句的統計資訊,由host分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-statement-type.html">host_summary_by_statement_type 和 x$host_summary_by_statement_type Views</a>
總計語句的執行,由host和語句類型分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-innodb-buffer-stats-by-schema.html">innodb_buffer_stats_by_schema 和 x$innodb_buffer_stats_by_schema Views</a>
統計information_schema.innodb_buffer_page,由schema分組,object_schema為對象的schema,如果為innodb表屬于innodb system。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-innodb-buffer-stats-by-table.html">innodb_buffer_stats_by_table 和 x$innodb_buffer_stats_by_table Views</a>
統計information_schema.innodb_buffer_page,由表名分組。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html">innodb_lock_waits 和 x$innodb_lock_waits Views</a>
總計了innodb鎖等待。列如下:
wait_started:等待開始事件。
wait_age:等待鎖的時間長度。
wait_age_secs:等待了多少秒。
locked_table:被鎖定的表。
locked_index:被鎖的索引
locked_type:鎖等待類型
waiting_trx_started:等待事務的開始事件。
waiting_trx_age:等待事務等待時間。
waiting_trx_rows_locked:等待事務鎖定的行鎖個數。
……
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-io-by-thread-by-latency.html">io_by_thread_by_latency 和 x$io_by_thread_by_latency Views</a>
總計了IO消費者顯示了線程的IO等待。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-bytes.html">io_global_by_file_by_bytes 和 x$io_global_by_file_by_bytes Views</a>
總計了IO消費者顯示每個檔案的讀寫量,由檔案分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-latency.html">io_global_by_file_by_latency 和 x$io_global_by_file_by_latency Views</a>
總結io消費者顯示io次數和延遲事件,由檔案分組
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-bytes.html">io_global_by_wait_by_bytes 和 x$io_global_by_wait_by_bytes Views</a>
每個event的總io位元組。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-latency.html">io_global_by_wait_by_latency 和 x$io_global_by_wait_by_latency Views</a>
每個event的總io次數和io等待時間
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-latest-file-io.html">latest_file_io 和 x$latest_file_io Views</a>
總計活動的檔案IO,由檔案和線程分組。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-memory-by-host-by-current-bytes.html">memory_by_host_by_current_bytes 和 x$memory_by_host_by_current_bytes Views</a>
總計host使用的總記憶體
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-memory-by-thread-by-current-bytes.html">memory_by_thread_by_current_bytes 和 x$memory_by_thread_by_current_bytes Views</a>
線程的記憶體使用
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-memory-by-user-by-current-bytes.html">memory_by_user_by_current_bytes 和 x$memory_by_user_by_current_bytes Views</a>
總計使用者使用的記憶體
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-memory-global-by-current-bytes.html">memory_global_by_current_bytes 和 x$memory_global_by_current_bytes Views</a>
每個配置設定類型配置設定的記憶體
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-memory-global-total.html">memory_global_total 和 x$memory_global_total Views</a>
服務的總記憶體使用
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-metrics.html">metrics View</a>
視圖總計mysql服務的名額,顯示變量名,變量值,類型和他們的啟動情況。視圖在mysql 5.7.9被添加,視圖主要包含資訊:
o
全局的狀态變量,來至于global_status表。
來自information_schema.global_status.Innodb名額
目前和所有記憶體配置設定
目前時間
有一些在global_status和innodb_status中有重複的名額,metrics視圖會進行消除。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-processlist.html">processlist 和 x$processlist Views</a>
比show processlist傳回的資訊更加詳細
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-check-lost-instrumentation.html">ps_check_lost_instrumentation View</a>
傳回丢失的性能架構記錄點,顯示是否性能架構可以跟蹤所有資料。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-auto-increment-columns.html">schema_auto_increment_columns View</a>
視圖顯示了有auto_increment的列,并且提供了有用的資訊。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-index-statistics.html">schema_index_statistics 和 x$schema_index_statistics Views</a>
視圖提供的所有統計資訊
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-overview.html">schema_object_overview View</a>
schema下對象統計
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-redundant-indexes.html">schema_redundant_indexes 和 x$schema_flattened_keys Views</a>
顯示了備援的索引
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html">schema_table_lock_waits 和 x$schema_table_lock_waits Views</a>
顯示了哪些會話被中繼資料鎖鎖定,什麼鎖定了它們
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics.html">schema_table_statistics 和 x$schema_table_statistics Views</a>
表操作的統計,io和延遲的統計
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics-with-buffer.html">schema_table_statistics_with_buffer 和 x$schema_table_statistics_with_buffer Views</a>
表操作的統計,io和延遲的統計,和記憶體的配置設定
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-tables-with-full-table-scans.html">schema_tables_with_full_table_scans 和 x$schema_tables_with_full_table_scans Views</a>
顯示了哪些表被表掃描通路
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-schema-unused-indexes.html">schema_unused_indexes View</a>
未使用過的索引
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-session.html">session 和 x$session Views</a>
和processlist相似但是不顯示背景程序
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-session-ssl-status.html">session_ssl_status View</a>
對于每個連接配接顯示SSL版本,chipher和count
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statement-analysis.html">statement_analysis 和 x$statement_analysis Views</a>
顯示了語句的執行情況,執行次數,響應行數,延遲等
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-errors-or-warnings.html">statements_with_errors_or_warnings 和 x$statements_with_errors_or_warnings Views</a>
語句的錯誤或者警告
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-full-table-scans.html">statements_with_full_table_scans 和 x$statements_with_full_table_scans Views</a>
用到了表掃描的語句
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-runtimes-in-95th-percentile.html">statements_with_runtimes_in_95th_percentile和 x$statements_with_runtimes_in_95th_percentile Views</a>
runtimes在95%以内的語句
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-sorting.html">statements_with_sorting 和 x$statements_with_sorting Views</a>
執行了排序的語句
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html">statements_with_temp_tables 和 x$statements_with_temp_tables Views</a>
使用了臨時表的語句
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary.html">user_summary 和 x$user_summary Views</a>
使用者總計資訊,包含語句,檔案io,連接配接
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io.html">user_summary_by_file_io 和 x$user_summary_by_file_io Views</a>
使用者檔案io總計
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io-type.html">user_summary_by_file_io_type 和 x$user_summary_by_file_io_type Views</a>
使用者檔案io類型總計
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-stages.html">user_summary_by_stages 和 x$user_summary_by_stages Views</a>
使用者stage事件總計
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-latency.html">user_summary_by_statement_latency 和 x$user_summary_by_statement_latency Views</a>
使用者在執行語句上的延遲
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-type.html">user_summary_by_statement_type 和 x$user_summary_by_statement_type Views</a>
使用者在語句類型上的延遲
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version.html">version View</a>
版本
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-wait-classes-global-by-avg-latency.html">wait_classes_global_by_avg_latency 和 x$wait_classes_global_by_avg_latency Views</a>
等待類型延遲彙總,由平均延遲排序
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-wait-classes-global-by-latency.html">wait_classes_global_by_latency 和 x$wait_classes_global_by_latency Views</a>
等待類型延遲彙總,由總延遲排序
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-waits-by-host-by-latency.html">waits_by_host_by_latency 和 x$waits_by_host_by_latency Views</a>
host,等待事件延遲彙總
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-waits-by-user-by-latency.html">waits_by_user_by_latency 和 x$waits_by_user_by_latency Views</a>
使用者等待事件延遲
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-waits-global-by-latency.html">waits_global_by_latency 和 x$waits_global_by_latency Views</a>
等待事件延遲。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-create-synonym-db.html">The create_synonym_db() Procedure</a>
建立一個别名資料庫,指向被引用的資料庫,使用視圖指向被引用資料庫的表
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-diagnostics.html">The diagnostics() Procedure</a>
目前伺服器的狀态,包含了mysql服務版本資訊,系統變量,innodb狀态,processlist,記憶體使用資訊,performance_schema狀态,和一些狀态資訊。輸出到diag.out檔案
以已準備好的語句,來執行。準備好的語句在執行完成後被釋放,是以并不能重用。可以用來執行的動态語句。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-background-threads.html">The ps_setup_disable_background_threads() Procedure</a>
禁止背景程序的所有性能架構記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-consumer.html">The ps_setup_disable_consumer() Procedure</a>
禁用某個消費者
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-instrument.html">The ps_setup_disable_instrument() Procedure</a>
禁用記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-disable-thread.html">The ps_setup_disable_thread() Procedure</a>
根據連接配接id,禁用某個線程
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-background-threads.html">The ps_setup_enable_background_threads() Procedure</a>
啟動背景線程的性能架構記錄
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-consumer.html">The ps_setup_enable_consumer() Procedure</a>
啟動某個性能架構消費者
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-instrument.html">The ps_setup_enable_instrument() Procedure</a>
啟動某個性能架構記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-enable-thread.html">The ps_setup_enable_thread() Procedure</a>
啟動某個連接配接id對應線程的記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reload-saved.html">The ps_setup_reload_saved() Procedure</a>
重新加載性能架構配置,使用之前ps_setup_save儲存的配置
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reset-to-default.html">The ps_setup_reset_to_default() Procedure</a>
重置到預設配置
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-save.html">The ps_setup_save() Procedure</a>
儲存性能架構配置,這樣可以根據調試要求先調整,然後恢複
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled.html">The ps_setup_show_disabled() Procedure</a>
顯示目前所有禁用的配置
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled-consumers.html">The ps_setup_show_disabled_consumers() Procedure</a>
顯示所有禁用的消費者
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-disabled-instruments.html">The ps_setup_show_disabled_instruments() Procedure</a>
顯示禁用的記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled.html">The ps_setup_show_enabled() Procedure</a>
顯示啟動的性能架構配置
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled-consumers.html">The ps_setup_show_enabled_consumers() Procedure</a>
顯示啟動的消費者
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-show-enabled-instruments.html">The ps_setup_show_enabled_instruments() Procedure</a>
顯示啟動的記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-statement-avg-latency-histogram.html">The ps_statement_avg_latency_histogram() Procedure</a>
直方圖顯示語句的平均延遲
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-trace-statement-digest.html">The ps_trace_statement_digest() Procedure</a>
跟蹤指定語句digest的所有性能記錄點。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-trace-thread.html">The ps_trace_thread() Procedure</a>
指定連接配接id的所有性能記錄點
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-truncate-all-tables.html">The ps_truncate_all_tables() Procedure</a>
清空所有性能架構summary表
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-statement-performance-analyzer.html">The statement_performance_analyzer() Procedure</a>
建立語句在服務端上運作的報表
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html">The table_exists() Procedure</a>
判斷是否存在表,視圖,臨時表
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-extract-schema-from-file-name.html">The extract_schema_from_file_name() Function</a>
根據檔案路徑擷取對應的資料庫名
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-extract-table-from-file-name.html">The extract_table_from_file_name() Function</a>
根據檔案路徑擷取表名
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-bytes.html">The format_bytes() Function</a>
給位元組數,轉化為可讀的格式
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-path.html">The format_path() Function</a>
檔案路徑格式化
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-statement.html">The format_statement() Function</a>
格式化語句輸出,輸出長度和statement_truncate_len配置有關。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-format-time.html">The format_time() Function</a>
時間格式化
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-list-add.html">The list_add() Function</a>
增加一個由逗号隔開的隊列中。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-list-drop.html">The list_drop() Function</a>
從逗号隔開的隊列的隊列中删除一個元素
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-account-enabled.html">The ps_is_account_enabled() Function</a>
判斷賬号是否啟動
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-consumer-enabled.html">The ps_is_consumer_enabled() Function</a>
判斷消費者是否啟動
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-enabled.html">The ps_is_instrument_default_enabled() Function</a>
判斷記錄點是否預設啟動
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-instrument-default-timed.html">The ps_is_instrument_default_timed() Function</a>
給定記錄點預設是否是被計時的。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-is-thread-instrumented.html">The ps_is_thread_instrumented() Function</a>
判斷連接配接id對應的性能架構記錄點啟動
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-account.html">The ps_thread_account() Function</a>
給定連接配接id,判斷線程啟動的使用者。
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-id.html">The ps_thread_id() Function</a>
給定連接配接id傳回線程id
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-stack.html">The ps_thread_stack() Function</a>
給定線程id,傳回json格式的語句,stages,events的stack
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-ps-thread-trx-info.html">The ps_thread_trx_info() Function</a>
傳回線程id的事務和已經執行的語句
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-quote-identifier.html">The quote_identifier() Function</a>
引用分隔符
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-sys-get-config.html">The sys_get_config() Function</a>
擷取sys_config表的資料
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-major.html">The version_major() Function</a>
mysql主版本
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-minor.html">The version_minor() Function</a>
mysql次版本
<a href="http://dev.mysql.com/doc/refman/5.7/en/sys-version-patch.html">The version_patch() Function</a>
mysql更新檔号