MySql数据库中查看资源使用情况

MySql数据库中查看资源使用情况

df -h --查看linux磁盘使用的情况

设置临时表内存空间
一般是在my.ini文件中设置
使用命令设置 set global tmp_table_size= 67108864

show variables like '%tmp%';   -- 查看设置大小
SELECT 536870912/1024/1024
set global tmp_table_size=64*1024*1024

而不是：
set global tmp_table_size=64M

---

select
table_schema as '数据库' 
,sum(table_rows) as '记录数' 
,sum(truncate(data_length/1024/1024, 2))/1024  as '数据容量(G)' 
,sum(truncate(index_length/1024/1024, 2))/1024  as '索引容量(G)'
from information_schema.tables
where table_schema='prodrai_report'; 

select * from (
select
create_time
,table_schema as '数据库'
,table_name as '表名'
,table_rows as '记录数'
,truncate(data_length/1024/1024, 2)/1024 as '数据容量(G)'
,truncate(index_length/1024/1024, 2)/1024 as '索引容量(G)'
from information_schema.tables
order by data_length desc, index_length desc
)a 
where 数据库 ='prodrai_report'
and `数据容量(G)` > 5
and  date_format (create_time,'%Y%m%d')  > '202110'