开发转兼职DBA（七）：不是SQL的锅——从操作系统层面排查数据库问题

开发转兼职DBA七不是SQL的锅——从操作系统层面排查数据库问题数据库慢了第一反应是查SQL、查执行计划。但有时候SQL没问题索引也在走数据库配置也没改——问题在操作系统层。这篇讲两个真实案例Tomcat权限污染导致系统假死和磁盘IO瓶颈拖慢整个数据库。文章目录开发转兼职DBA七不是SQL的锅——从操作系统层面排查数据库问题案例一系统挂了结果不是数据库的问题现象排查过程修复为什么会这样教训案例2数据库慢了结果是磁盘的问题现象跳出数据库看操作系统解决加大内存为什么加大内存能解决磁盘问题为什么加大内存能解决磁盘问题两层缓存这个案例里发生了什么另一种思路不扩内存把SGA加大一句话总结排查思路逐层往下追几个常用命令速查CPU相关内存相关磁盘IO相关网络相关文件权限相关系列终章总结案例一系统挂了结果不是数据库的问题现象某天上午用户打电话来系统打不开了。登录检查数据库正常监听正常SQL能跑应用服务器ping得通但浏览器访问页面一直转圈最终超时第一反应数据库是不是锁了查了一下没有阻塞会话没有异常等待。数据库好好的。那问题在哪排查过程第一步看应用日志。tail-200f/opt/tomcat/logs/catalina.out报错java.io.FileNotFoundException: /opt/tomcat/logs/localhost.2024-01-15.log (Permission denied) java.io.IOException: Unable to create directory /opt/tomcat/work/Catalina/localhost/_写不进去日志文件。第二步查文件权限。ls-la/opt/tomcat/logs/-rw-r----- 1 root root 12345 Jan 15 09:00 localhost.2024-01-15.log drwxr-x--- 2 root root 4096 Jan 15 09:00 Catalina/所有者全是root。第三步查Tomcat是用什么用户启动的。psaux|greptomcattomcat 12345 2.3 15.4 2345678 654321 ? Sl 09:00 1:23 /usr/bin/java -jar ...现在是用tomcat用户跑的。但日志文件属于root。第四步问了一圈真相大白。前一天晚上有人用root账号手动重启了Tomcatsu- /opt/tomcat/bin/startup.shTomcat以root身份启动。运行过程中创建的日志文件、临时文件、编译后的JSP class文件——全部属于root。第二天早上自动化的systemd服务以tomcat用户重启了Tomcat。新进程想写日志文件——没有权限。想编译JSP——work目录属于root写不进去。应用看起来挂了但数据库完全正常。修复# 停掉Tomcatsystemctl stop tomcat# 把所有文件归属改回tomcat用户chown-Rtomcat:tomcat /opt/tomcat/# 重启systemctl start tomcat一行chown问题解决。为什么会这样Linux的权限模型很简单-rw-r----- 1 tomcat tomcat 12345 Jan 15 09:00 catalina.out │├──┤├──┤├──┤ ├──┤ ├──┤ │ │ │ │ │ └── 组名 │ │ │ │ └── 所有者 │ │ │ └── 其他用户的权限--- 无权限 │ │ └── 同组用户的权限r-- 只读 │ └── 所有者的权限rw- 读写 └── 文件类型- 普通文件d 目录进程创建新文件时文件的所有者是进程的运行用户。root启动的进程创建的文件属于root。之后tomcat用户想去写这些文件——权限不够。教训永远不要用root启动应用服务——不仅权限会乱安全风险也大用systemd管理服务在unit文件里指定用户[Service] Usertomcat Grouptomcat排查系统挂了不要只盯着数据库——应用层的问题比数据库层更常见案例2数据库慢了结果是磁盘的问题现象某大库Oracle数据库整体变慢。不是某一条SQL慢是所有SQL都慢。第一反应是不是锁表了查v$session没有阻塞。查AWR报告发现db file sequential read的等待时间比平时高了几倍。db file sequential read是单块读等待——Oracle从数据文件读一个块到缓冲区的等待。这个等待变长要么是读的块变多了要么是每次读的时间变长了。查执行计划跟以前一样没变。查索引没变。查数据量没有暴增。不是SQL的问题。跳出数据库看操作系统第一步看CPU。top%Cpu(s): 3.0 us, 1.5 sy, 0.0 ni, 55.0 id, 38.0 wa, 0.0 hi, 2.5 si关键指标ususer用户态CPU占用3%——不高sysystem内核态CPU占用1.5%——不高ididle空闲55%——一半多的CPU时间在空闲waiowait38%——严重不正常waiowait是CPU在等磁盘IO完成的时间占比。正常应该接近0。38%意味着CPU超过三分之一的时间在等磁盘。idle只有55%不是CPU忙是CPU在等磁盘。第二步看磁盘IO。iostat-x15Device rrqm/s wrqm/s r/s w/s rMB/s wMB/s await %util sda 0.0 12.0 350.0 200.0 2.8 1.6 35.8 98.5关键指标r/s, w/s每秒读写次数加起来550次/秒——很多await平均每次IO等待时间35.8ms——很高%util磁盘利用率98.5%——磁盘已经饱和35ms的await意味着什么Oracle一次单块读需要35ms。一个查询需要读100个块光等磁盘就要3.5秒。而且这不是个别慢查询的问题——550次/秒的IO请求量磁盘98.5%利用率磁盘已经被打满了。第三步看内存。free-gtotal used free shared buff/cache available Mem: 128 95 2 1 31 30 Swap: 8 1 7128G总内存Oracle的SGA占了约100G剩余留给操作系统的只有不到30G。其中buff/cache约31G——看起来不小但这是一个大库数据文件几百GB甚至上TB31G的page cache远远覆盖不了热点数据。128G总内存 ├── Oracle SGA: ~100GBuffer Cache Shared Pool 其他 ├── 进程系统: ~2G ├── page cache: ~31G └── 空闲: ~2G根因找到了大库数据量大128G内存里Oracle自己吃了100G留给操作系统page cache的只有30G缓存不住热点数据大量读请求直接打到磁盘磁盘被打满。解决加大内存服务器内存从128G扩到264G。Oracle的SGA配置没动还是100多G。多出来的136G全部留给操作系统。扩完之后top里看到最明显的变化idle从55%升到85%——CPU空闲时间多了wa从38%降到5%以下——CPU不用等磁盘了free -g的变化total used free shared buff/cache available Mem: 264 97 80 1 87 164page cache从31G涨到87G。Oracle的Buffer Cache没变但操作系统层多出了56G的缓存。Oracle的Buffer Cache没命中的数据块现在大概率在page cache里能找到——不用访问磁盘了。iostat的变化磁盘%util从98%降到30%左右await从35ms降到个位数。数据库整体性能恢复。不是改了任何SQL、加了任何索引、调了任何数据库参数——就是加了内存。为什么加大内存能解决磁盘问题为什么加大内存能解决磁盘问题这个问题的本质不是磁盘太慢是内存不够大缓存不住热点数据。但缓存这个词太笼统。内存分配给数据库之后实际上是两层缓存在配合工作很多人只看到了一层。两层缓存Oracle读一个数据块 ↓ 第一层Oracle自己的 Buffer CacheSGA里配的 db_cache_size ↓ 命中 → 直接返回 ↓ 未命中 第二层Linux的 page cache操作系统的 buff/cache ↓ 命中 → 从内存读不需要访问磁盘 ↓ 未命中 第三层磁盘 → 真正的物理IO慢第一层Oracle的Buffer Cache。这是Oracle自己管理的内存区域配多大由sga_target或db_cache_size决定。Oracle把频繁访问的数据块缓存在这里内部用LRU算法管理淘汰。Buffer Cache命中率就是第五篇讲的那个指标——低于90%说明不够用。第二层Linux的page cache。Oracle的数据文件.dbf也是操作系统管理的文件。Oracle向操作系统发起read调用时Linux先查page cache——如果这个数据块最近被读过还在内存里直接返回不访问磁盘。这两层缓存的关系Oracle的Buffer Cache是Oracle内部申请的内存通过sga_target分配。这部分内存被Oracle进程锁定Linux不会回收。Linux的page cache是操作系统自动管理的空闲内存。Linux会把没人用的内存全部拿来缓存磁盘数据。应用不用的内存越多page cache越大磁盘IO越少。内存就这么多给了Oracle就不能给Linux的page cache反过来也一样。所以内存规划的核心问题是Oracle的Buffer Cache和操作系统的page cache怎么分这个案例里发生了什么128G总内存。Oracle的SGA配了约100G其中Buffer Cache是大头操作系统和进程占用几G剩下的约30G被Linux自动用作page cache。128G总内存 ├── Oracle SGA: ~100GBuffer Cache占大部分 ├── 进程系统: ~3G └── page cache: ~31G大库的数据文件几百GB100G的Buffer Cache加上31G的page cache总共约130G的缓存。看似很多但一个跑了几年的大库热点数据分散在大量表和索引上130G的缓存覆盖不了。大量读请求穿透两层缓存直接打到磁盘。扩到264G之后264G总内存 ├── Oracle SGA: ~100G没变 ├── 进程系统: ~3G ├── page cache: ~87G大涨 └── 空闲: ~80GLinux会逐渐回收用作page cacheOracle的SGA没变Buffer Cache还是100G左右。但操作系统的page cache从31G涨到87G。Oracle的Buffer Cache没命中的数据块在page cache里命中的概率大大增加——很多请求不用访问磁盘了。磁盘压力骤降。iowait从38%降到5%以下idle从55%升到85%。另一种思路不扩内存把SGA加大既然缓存不够把Oracle的Buffer Cache从100G加大到150G是不是也能解决能缓解但有个问题Oracle的Buffer Cache只缓存Oracle自己的数据块。操作系统上的其他进程RMAN备份、日志收集、文件传输的磁盘IO不受Oracle Buffer Cache保护还是直接打磁盘。如果这些操作也在产生大量IOOracle加大Buffer Cache的作用有限。而操作系统的page cache是全局的——所有进程的文件IO都受益。Oracle的数据块、备份程序读的文件、日志文件的写入都能利用page cache减少磁盘IO。所以实际经验中不要把所有内存都分配给Oracle的SGA要留足够的内存给操作系统做page cache。一般建议Oracle SGA占总内存的40%~60%剩下的留给操作系统。这个案例里128G内存给Oracle 100GSGA占了78%——比例偏高了留给操作系统的太少。扩到264G后SGA只占38%操作系统拿到160Gpage cache充足磁盘压力自然下来了。这不是数据库调优能解决的问题。执行计划再优化索引再加该从磁盘读的块还是要读。瓶颈在磁盘IO解法在内存规划和两层缓存的配合。一句话总结数据库慢了不一定是SQL的问题。先看iostat和free确认瓶颈在哪一层。排查思路逐层往下追两个案例放在一起抽象出一个通用的排查链路用户说系统慢了/挂了 ↓ 第一层应用层 ├── 应用日志有没有报错Tomcat权限问题就是在这一层发现的 ├── 接口响应时间是多少 └── 是所有功能都慢还是某个功能慢 ↓ 第二层数据库层 ├── 有没有锁阻塞v$session的blocking_session ├── 有没有慢SQLv$sql按elapsed_time排序 ├── 等待事件是什么v$system_event └── 执行计划有没有变化 ↓ 第三层操作系统层 ├── CPU够不够top的us/sy/wa ├── 内存够不够free的available和buff/cache ├── 磁盘IO是不是瓶颈iostat的await和%util └── 网络通不通ping/telnet/netstat ↓ 第四层基础设施层 ├── 磁盘是不是快满了df -h ├── 存储后端有没有问题SAN/NAS/云盘 └── 有没有硬件故障dmesg | grep error大部分开发者到第二层就停了——查SQL、加索引、调参数。但如果问题在第三层、第四层在数据库里怎么折腾都没用。几个常用命令速查CPU相关# 整体CPU使用情况top# 每个CPU核心的使用情况mpstat-PALL15# 查哪个进程吃CPUpsaux--sort-%cpu|head-20重点关注%waiowait持续5%说明磁盘是瓶颈。内存相关# 内存使用概况free-m# 进程内存排序psaux--sort-%mem|head-20# 详细内存映射cat/proc/meminfo重点关注available真正可用的内存包含可回收的缓存、Swap usedswap用了多少持续增长说明物理内存不够。磁盘IO相关# 磁盘IO统计每秒刷新共5次iostat-x15# 查看哪个进程在疯狂读写磁盘iotop# 磁盘空间df-h# 目录大小du-sh/opt/*重点关注%util持续80%说明磁盘饱和await持续10msSSD1ms说明IO延迟高。网络相关# 查看网络连接状态netstat-tlnp# 测试数据库端口是否通telnet192.168.1.1001521# 查看网络流量sar-nDEV15# DNS解析nslookupdbserver文件权限相关# 查看权限ls-la/opt/tomcat/logs/# 递归修改所有者chown-Rtomcat:tomcat /opt/tomcat/# 修改权限chmod755/opt/tomcat/bin/startup.sh# 查看某用户对文件的权限su- tomcat-ctest -w /opt/tomcat/logs/catalina.out echo writable || echo not writable系列终章总结七篇文章一条线一只会写SQL ↓ 查询慢了 二学会看执行计划加索引 ↓ 索引也救不了 三数据库起不来了逼着理解WAL和redo ↓ undo也坏了 四逼着理解MVCC和undo ↓ 不能老出事才救 五学参数、内存、监控、备份 ↓ 换了个项目数据库换成了MySQL 六发现原理都一样 ↓ 数据库没问题系统还是慢 七跳出数据库从操作系统层排查从一个只会写SELECT * FROM的开发者到能看执行计划优化SQL到理解WAL和MVCC的底层原理到能配参数做监控写备份脚本到跨数据库触类旁通到能跳出数据库从操作系统层面定位问题。这就是开发转兼职DBA的完整路径。不是因为我想学是因为小团队没有专职DBA出了事就是我扛。每次事故逼出一段认知每段认知沉淀成经验最终串成一条完整的知识链。标签#DBA #运维 #Linux #iostat #top #free #权限 #磁盘IO #page cache #故障排查