如何监控数据库的临时空间使用？

wen IT资讯 2026-06-03 234

本文目录导读：

如何监控数据库的临时空间使用？

Oracle 数据库（临时表空间）
SQL Server 数据库（tempdb）
MySQL 数据库（临时表空间或临时目录）
PostgreSQL 数据库（临时文件）
通用监控与告警方案（推荐）
常见问题与应急处理

监控数据库临时空间（如临时表空间、tempdb等）的使用情况，是DBA的日常工作之一，因为临时空间耗尽往往会导致查询失败、事务回滚甚至数据库服务中断。

不同数据库的监控方式差异较大，以下为您整理了主要数据库（Oracle、SQL Server、MySQL、PostgreSQL） 的监控方法及通用最佳实践。

Oracle 数据库（临时表空间）

Oracle 的临时空间用于排序、哈希连接、全局临时表等操作。

查看当前使用率

-- 查看临时表空间的总大小和已使用大小
SELECT d.tablespace_name  "表空间名",
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 2) "总大小(MB)",
       ROUND(NVL(t.bytes / 1024 / 1024, 0), 2) "已使用(MB)",
       ROUND((NVL(t.bytes, 0) / NVL(a.bytes, 1)) * 100, 2) "使用率(%)"
FROM dba_temp_files d,
     (SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_free_space GROUP BY tablespace_name) t
WHERE d.tablespace_name = t.tablespace_name(+);

查看当前正在使用临时空间的会话

-- 找出占用临时空间最多的会话（需要诊断时使用）
SELECT s.sid, s.serial#, s.username, s.sql_id,
       ROUND(SUM(u.blocks * t.block_size / 1024 / 1024), 2) "临时空间(MB)"
FROM v$sort_usage  u
JOIN v$session    s ON u.session_addr = s.saddr
JOIN dba_tablespaces t ON u.tablespace = t.tablespace_name
GROUP BY s.sid, s.serial#, s.username, s.sql_id
ORDER BY 5 DESC;

设置告警阈值

-- 设置临时表空间使用率超过80%时告警
ALTER TABLESPACE temp ADD DATAFILE '/u01/oradata/temp02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;
-- 并配置OEM或第三方监控工具的阈值

SQL Server 数据库（tempdb）

SQL Server 的 tempdb 是全局共享的,压力通常比较大。

查看当前使用量

-- 查看tempdb数据文件大小和剩余空间
SELECT 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB, 
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

动态监控（实时）

-- 使用DMV查看tempdb空间消耗排名
SELECT 
    session_id,
    request_id,
    database_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

常用性能计数器（PerfMon）

SQL Server: Databases > Data File(s) Size (KB) （针对tempdb）
SQL Server: Databases > Log File(s) Size (KB) （针对tempdb）

关键注意事项

不要对tempdb执行SHRINKFILE（除非紧急释放空间，否则会导致严重性能问题）。
建议为tempdb配置多个等大数据文件（等于CPU核心数，避免闩争用）。

MySQL 数据库（临时表空间或临时目录）

MySQL 的临时空间分为内部临时表（如排序、分组）和外部临时文件（由 tmpdir 参数指定）。

查看临时表空间文件大小

MySQL 8.0+ 默认使用 ibtmp1（共享临时表空间）。

-- 查看临时表空间配置和当前大小
SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
-- 查看实际文件大小（在操作系统层面）
-- ls -lh /var/lib/mysql/ibtmp1

监控当前创建的临时表数量

-- 查看全局状态（累计值）
SHOW GLOBAL STATUS LIKE '%tmp%';
-- 重点关注：
-- Created_tmp_tables: 创建的临时表总数
-- Created_tmp_disk_tables: 在磁盘上创建的临时表数（高则可能是临时空间不足或查询优化问题）
-- 计算磁盘临时表比例（理想状态应低于10%）
SELECT @@global.Created_tmp_disk_tables / @@global.Created_tmp_tables * 100;

查看当前活跃的临时文件（Linux）

# 查看MySQL的tmpdir目录中当前的临时文件
ls -lh /tmp | grep -E '^#sql|^MYD' | wc -l
# 或使用lsof查看哪个会话在写临时文件
lsof | grep tmp | grep delet

关键配置项

[mysqld]
# 增大临时表空间大小（默认12MB自动扩展）
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
# 临时目录（建议放在SSD或独立磁盘）
tmpdir = /data/mysql_tmp

PostgreSQL 数据库（临时文件）

PG 没有专门的临时表空间，临时对象存放在普通表空间中，或系统默认 pg_default。

查看临时文件总大小

PG 将排序、哈希等操作的溢出数据写入 pgsql_tmp 目录下的临时文件。

-- 查看当前会话创建的临时文件大小
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres');

实时监控临时目录

# 查看临时目录文件（通常在PG数据目录下的 base/pgsql_tmp）
du -sh $PGDATA/base/pgsql_tmp/*
# 查看哪个进程在使用
lsof $PGDATA/base/pgsql_tmp

关键参数调整

# 增大work_mem可以减少磁盘临时文件（每个排序操作分配的内存）
work_mem = 64MB
# 增大temp_buffers（临时表缓冲区）
temp_buffers = 64MB

通用监控与告警方案（推荐）

无论使用哪种数据库,建议采用以下层次化的监控策略：

层次	工具/方法	说明
OS层面	`df -h`（Linux） `磁盘监控`（Nagios/Zabbix）	监控存放临时文件的磁盘分区（如 `/tmp` `/var/lib/mysql`）剩余空间
数据库内部	上述SQL脚本	定时采集使用率（5分钟一次）
第三方监控	Prometheus + Grafana Datadog SolarWinds	可视化历史曲线，设置告警阈值（如使用率>80%）
实时告警	钉钉/企微/邮件	当使用率超过90%或达到绝对大小限制时，立即通知DBA

常见问题与应急处理

问题现象	可能原因	紧急操作
临时空间持续增长	长事务、未优化的SQL（大量排序/哈希）	找到并kill占用最大的会话（Oracle: `v$sort_usage`；SQL Server: `sys.dm_exec_requests`）
临时空间无法释放	事务未提交、全局临时表数据未清理	检查是否有未提交的显式事务（`SELECT * FROM sys.dm_tran_active_transactions`）
临时表空间自动扩展失败	磁盘空间满、达到maxsize限制	清理磁盘日志、增加maxsize、添加数据文件