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'