Table size

SELECT table_name AS 表名, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS 表大小(GB), ROUND(DATA_FREE / 1024 / 1024 / 1024, 2) AS 碎片/未释放空间(GB) FROM information_schema.TABLES WHERE table_name = 'live_data_file_increment_4_4' AND table_schema = DATABASE();

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%alter%'

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'key_buffer_size'; SHOW VARIABLES LIKE 'max_connections'; -- 最大连接数（过高会导致线程内存累积） SHOW VARIABLES LIKE 'tmp_table_size'; -- 内存临时表上限

SELECT (@@read_buffer_size + @@sort_buffer_size + @@join_buffer_size) / (1024 * 1024) AS per_thread_mb;

SELECT (@@read_buffer_size + @@sort_buffer_size + @@join_buffer_size) / (1024 * 1024) AS per_thread_mb; SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 活跃线程数

-- 显示占用最高的模块（如InnoDB缓冲池、临时表）

SELECT event_name, current_alloc 
FROM sys.memory_global_by_current_bytes 
LIMIT 20;

select sys.format_bytes(SUM(current_alloc)) FROM sys.x$memory_global_by_current_bytes;

各个模块的分别占用的内存

SELECT SUBSTRING_INDEX(event_name,'/', 2 ) AS
       code_area, sys.format_bytes(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/', 2 )
       ORDER BY SUM(current_alloc) DESC;

监控InnoDB缓冲池利用率： SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';

SET GLOBAL innodb_buffer_pool_size =

连接占用的内存 https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653939777&idx=1&sn=da6b97b8d302b0b910fa52147e0f6854&chksm=bd3b722b8a4cfb3d3e974909558b805ca7200ddaeeefc0b8b04a5562543202a0df40b7d4365e#rd

pmap -x $(pgrep mysqld) | grep -i "anon" | sort -nrk3

查看 database size

selectname,FILE_SIZE/1024/1024/1024as  GB from information_schema.INNODB_TABLESPACES wherename='test/t1';

SELECT table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)', sum(truncate(DATA_FREE/1024/1024, 2)) as '碎片占用(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc;

查看 table size

SELECT table_name AS 表名, ROUND((DATA_LENGTH) / 1024 / 1024 / 1024, 2) AS 表大小(GB), ROUND((INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS 索引大小(GB), ROUND(DATA_FREE / 1024 / 1024 / 1024, 2) AS 碎片/未释放空间(GB) FROM information_schema.TABLES where table_schema = DATABASE();

查看 optimize table 事件

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

optimize table xx 后查看进度 SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

SELECT * FROM sys.innodb_lock_waits\G