diff options
Diffstat (limited to 'scripts/sys_schema/views/p_s/host_summary.sql')
-rw-r--r-- | scripts/sys_schema/views/p_s/host_summary.sql | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/scripts/sys_schema/views/p_s/host_summary.sql b/scripts/sys_schema/views/p_s/host_summary.sql new file mode 100644 index 00000000..080100a4 --- /dev/null +++ b/scripts/sys_schema/views/p_s/host_summary.sql @@ -0,0 +1,60 @@ +-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved. +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; version 2 of the License. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +-- +-- View: host_summary +-- +-- Summarizes statement activity, file IO and connections by host. +-- +-- When the host found is NULL, it is assumed to be a "background" thread. +-- +-- mysql> select * from host_summary; +-- +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+ +-- | host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | +-- +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+ +-- | hal1 | 2924 | 00:03:59.53 | 81.92 ms | 82 | 54702 | 55.61 s | 1 | 1 | 1 | +-- +------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+ +-- + +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'mariadb.sys'@'localhost' + SQL SECURITY INVOKER +VIEW host_summary ( + host, + statements, + statement_latency, + statement_avg_latency, + table_scans, + file_ios, + file_io_latency, + current_connections, + total_connections, + unique_users +) AS +SELECT IF(accounts.host IS NULL, 'background', accounts.host) AS host, + SUM(stmt.total) AS statements, + sys.format_time(SUM(stmt.total_latency)) AS statement_latency, + sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency, + SUM(stmt.full_scans) AS table_scans, + SUM(io.ios) AS file_ios, + sys.format_time(SUM(io.io_latency)) AS file_io_latency, + SUM(accounts.current_connections) AS current_connections, + SUM(accounts.total_connections) AS total_connections, + COUNT(DISTINCT accounts.user) AS unique_users + FROM performance_schema.accounts + LEFT JOIN sys.x$host_summary_by_statement_latency AS stmt ON accounts.host = stmt.host + LEFT JOIN sys.x$host_summary_by_file_io AS io ON accounts.host = io.host + GROUP BY IF(accounts.host IS NULL, 'background', accounts.host); |