Skip to content

Instantly share code, notes, and snippets.

@MarkLeith

MarkLeith/memory_by_instrument

Created October 17, 2016 14:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MarkLeith/167dbac6b16c4e5bbbffce0e9fd0db72 to your computer and use it in GitHub Desktop.
Save MarkLeith/167dbac6b16c4e5bbbffce0e9fd0db72 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
memory_by_instrument
-- Copyright (c) 2016, 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
DROP FUNCTION IF EXISTS sys.ps_is_instrument_enabled;
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION sys.ps_is_instrument_enabled (
in_instrument varchar(128)
)
RETURNS enum('YES', 'NO')
COMMENT '
Description
-----------
Determines whether an instrument is enabled within the Performance Schema.
Parameters
-----------
in_instrument VARCHAR(128):
The name of the instrument to check.
Returns
-----------
ENUM(\'YES\', \'NO\')
Example
-----------
mysql> SELECT sys.ps_is_instrument_enabled(\'memory/innodb/mem0mem\');
+-------------------------------------------------------+
| sys.ps_is_instrument_enabled(\'memory/innodb/mem0mem\') |
+-------------------------------------------------------+
| YES |
+-------------------------------------------------------+
1 row in set (0.00 sec)
'
SQL SECURITY INVOKER
DETERMINISTIC
READS SQL DATA
BEGIN
IF (in_instrument IS NULL) THEN
SIGNAL SQLSTATE '02200'
SET MESSAGE_TEXT = 'sys.ps_is_instrument_enabled: in_instrument input variable should not be NULL',
MYSQL_ERRNO = 1138;
END IF;
RETURN (SELECT enabled FROM performance_schema.setup_instruments WHERE name = in_instrument);
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS sys.memory_by_instrument;
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE sys.memory_by_instrument (
IN in_instrument VARCHAR(128)
)
COMMENT '
Description
-----------
Dumps global, per user, per host and per thread summaries for a specific memory instrument.
Parameters
-----------
in_instrument VARCHAR(128):
The memory instrument name to generate the report for.
Configuration Options
----------------------
None.
Example
--------
mysql> call sys.memory_by_instrument(\'memory/innodb/mem0mem\');
+----------------------------+
| Global Summary |
+----------------------------+
| 2016-10-17 13:33:26.869851 |
+----------------------------+
1 row in set (0.00 sec)
+------------+------------+-------------+-------------+------------+------------+
| curr_count | curr_alloc | count_alloc | total_alloc | count_free | total_free |
+------------+------------+-------------+-------------+------------+------------+
| 14624 | 23.27 MiB | 301336 | 1.65 GiB | 286712 | 1.63 GiB |
+------------+------------+-------------+-------------+------------+------------+
1 row in set (0.01 sec)
+----------------------------+
| Per User Summary |
+----------------------------+
| 2016-10-17 13:33:26.874145 |
+----------------------------+
1 row in set (0.01 sec)
+-------------------+------------+---------------+-------------+-------------+------------+------------+
| user | curr_count | curr_alloc | count_alloc | total_alloc | count_free | total_free |
+-------------------+------------+---------------+-------------+-------------+------------+------------+
| mem | 13791 | 23.01 MiB | 204943 | 1.41 GiB | 191152 | 1.39 GiB |
| mysqld_background | 925 | 470.43 KiB | 90433 | 201.28 MiB | 89508 | 200.82 MiB |
| msandbox | -40 | -95584 bytes | 2384 | 26.02 MiB | 2424 | 26.11 MiB |
| root | -52 | -115336 bytes | 3576 | 16.83 MiB | 3628 | 16.94 MiB |
+-------------------+------------+---------------+-------------+-------------+------------+------------+
4 rows in set (0.02 sec)
+----------------------------+
| Per Host Summary |
+----------------------------+
| 2016-10-17 13:33:26.883231 |
+----------------------------+
1 row in set (0.02 sec)
+-------------------+------------+------------+-------------+-------------+------------+------------+
| host | curr_count | curr_alloc | count_alloc | total_alloc | count_free | total_free |
+-------------------+------------+------------+-------------+-------------+------------+------------+
| localhost | 13699 | 22.81 MiB | 210903 | 1.46 GiB | 197204 | 1.43 GiB |
| mysqld_background | 925 | 470.43 KiB | 90433 | 201.28 MiB | 89508 | 200.82 MiB |
+-------------------+------------+------------+-------------+-------------+------------+------------+
2 rows in set (0.03 sec)
+----------------------------+
| Per Thread Summary |
+----------------------------+
| 2016-10-17 13:33:26.887664 |
+----------------------------+
1 row in set (0.03 sec)
+---------------------------------+-------+------------+--------------+-------------+-------------+------------+------------+
| user | db | curr_count | curr_alloc | count_alloc | total_alloc | count_free | total_free |
+---------------------------------+-------+------------+--------------+-------------+-------------+------------+------------+
| sql/main | NULL | 925 | 470.43 KiB | 3734 | 2.11 MiB | 2809 | 1.65 MiB |
| mem | mem | 1 | 240 bytes | 3 | 960 bytes | 2 | 720 bytes |
| sql/thread_timer_notifier | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_ibuf_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_log_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_read_thread | NULL | 0 | 0 bytes | 6167 | 14.95 MiB | 6167 | 14.95 MiB |
| innodb/io_read_thread | NULL | 0 | 0 bytes | 1403 | 3.98 MiB | 1403 | 3.98 MiB |
| innodb/io_read_thread | NULL | 0 | 0 bytes | 4196 | 11.18 MiB | 4196 | 11.18 MiB |
| innodb/io_read_thread | NULL | 0 | 0 bytes | 886 | 2.46 MiB | 886 | 2.46 MiB |
| innodb/io_write_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_write_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_write_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/io_write_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/page_cleaner_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_error_monitor_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_lock_timeout_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_monitor_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_master_thread | NULL | 0 | 0 bytes | 13262 | 17.37 MiB | 13262 | 17.37 MiB |
| innodb/srv_purge_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_worker_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_worker_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/srv_worker_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/buf_dump_thread | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| innodb/dict_stats_thread | NULL | 0 | 0 bytes | 60785 | 149.23 MiB | 60785 | 149.23 MiB |
| sql/signal_handler | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| sql/compress_gtid_table | NULL | 0 | 0 bytes | 0 | 0 bytes | 0 | 0 bytes |
| root | mysql | -3 | -6248 bytes | 436 | 3.05 MiB | 439 | 3.05 MiB |
| msandbox | sys | -16 | -58188 bytes | 2124 | 23.01 MiB | 2140 | 23.07 MiB |
| root | mysql | -34 | -80864 bytes | 783 | 1.20 MiB | 817 | 1.28 MiB |
+---------------------------------+-------+------------+--------------+-------------+-------------+------------+------------+
29 rows in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE v_msg TEXT;
IF (in_instrument IS NULL) THEN
SET v_msg = 'sys.memory_by_instrument: in_instrument input variable should not be NULL';
SIGNAL SQLSTATE '02200'
SET MESSAGE_TEXT = v_msg,
MYSQL_ERRNO = 1138;
END IF;
IF in_instrument NOT LIKE 'memory/%' THEN
SET v_msg = CONCAT('sys.memory_by_instrument: ', in_instrument, ' is not a memory/ instrument');
SIGNAL SQLSTATE '02300'
SET MESSAGE_TEXT = v_msg,
MYSQL_ERRNO = 6001;
END IF;
IF NOT (sys.ps_is_instrument_enabled(in_instrument) <=> 'YES') THEN
SET v_msg = CONCAT('sys.memory_by_instrument: ', in_instrument, ' instrument not enabled');
SIGNAL SQLSTATE '02300'
SET MESSAGE_TEXT = v_msg,
MYSQL_ERRNO = 6002;
END IF;
SELECT NOW(6) AS "Global Summary";
SELECT current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = in_instrument;
SELECT NOW(6) AS "Per User Summary";
SELECT IFNULL(user, 'mysqld_background') AS user,
current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.memory_summary_by_user_by_event_name
WHERE event_name = in_instrument
ORDER BY current_number_of_bytes_used DESC;
SELECT NOW(6) AS "Per Host Summary";
SELECT IFNULL(host, 'mysqld_background') AS host,
current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.memory_summary_by_host_by_event_name
WHERE event_name = in_instrument
ORDER BY current_number_of_bytes_used DESC;
SELECT NOW(6) AS "Per Thread Summary";
SELECT if(processlist_user is null,
substring_index(t.name, '/', -2),
processlist_user) AS user,
t.processlist_db AS db,
m.current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.threads t
JOIN performance_schema.memory_summary_by_thread_by_event_name m using (thread_id)
WHERE event_name = in_instrument
ORDER BY current_number_of_bytes_used DESC;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment