When troubleshooting database performance on Linux systems, traditional tools like iostat, iotop, and dstat only provide device-level statistics. For MSSQL or other database administrators coming from Windows environments where Resource Monitor offers file-level I/O insights, this can be frustrating.

The Linux kernel actually provides powerful capabilities for file-level I/O monitoring through:

# Kernel I/O accounting
echo 1 > /proc/sys/vm/block_dump

# View block I/O operations
dmesg | grep -i block

However, this method is crude and requires parsing kernel messages. More sophisticated tools have been developed to leverage kernel features like inotify and ftrace.

Here are the most effective tools currently available:

1. fatrace - File Activity Trace

# Install on CentOS
sudo yum install fatrace

# Monitor all file activity
sudo fatrace

# Filter for specific processes
sudo fatrace -c $(pgrep mysqld)

2. iotop with Custom Scripting

While iotop doesn't show files directly, we can combine it with lsof:

#!/bin/bash
while true; do
    iotop -n 1 -b | awk '$12 ~ /^[DWR]/ {print $1,$12}' | \
    while read pid op; do
        echo "PID $pid ($op):"
        ls -lh /proc/$pid/fd | grep -v memfd:
    done
    sleep 2
done

3. SystemTap Scripts

For deeper analysis, SystemTap provides kernel-level tracing:

# File operations by process
probe kernel.function("vfs_read"),
      kernel.function("vfs_write")
{
    printf("%s %s %s\n", execname(), pid(), filename)
}

For database servers, consider these specialized approaches:

MySQL Performance Schema

-- Enable file I/O instrumentation
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%file%';

-- Query file I/O stats
SELECT * FROM performance_schema.file_summary_by_instance;

PostgreSQL pg_stat_io

-- View I/O statistics by relation
SELECT * FROM pg_statio_user_tables;

-- Backend I/O operations
SELECT * FROM pg_stat_activity JOIN pg_stat_io 
ON pg_stat_activity.pid = pg_stat_io.pid;

For enterprise environments, consider these solutions:

• eBPF-based tools like bpftrace for low-overhead tracing
• Commercial APM solutions with file I/O capabilities
• Custom Prometheus exporters using inotify watches

The key is combining these tools with your specific database's instrumentation for complete visibility into file I/O patterns.

---

When diagnosing performance bottlenecks on database servers, understanding which specific files are experiencing heavy I/O activity is crucial. While Windows provides Resource Monitor for this purpose, Linux requires specialized tools.

Standard Linux tools like iostat, iotop, and dstat provide system-wide or process-level I/O statistics but lack file-level granularity. For database administrators, this makes identifying problematic tablespaces or log files challenging.

While iotop doesn't show per-file stats, it's useful for identifying I/O-heavy processes:

sudo iotop -oP

This shows processes actually performing I/O at the moment.

The fatrace tool (File Activity Trace) can monitor which files are being accessed:

sudo apt-get install fatrace  # Debian/Ubuntu
sudo yum install fatrace      # CentOS/RHEL

# Monitor all file activity:
sudo fatrace

# Filter for specific process:
sudo fatrace -c $(pgrep mongod)

For more targeted analysis, combine lsof and strace:

# Find open files for a process
sudo lsof -p $(pgrep mysqld)

# Then trace I/O operations:
sudo strace -p $(pgrep mysqld) -e trace=file

For deeper visibility, SystemTap can track file I/O at the kernel level. Create a script (io_monitor.stp):

global reads, writes

probe vfs.read.return {
    reads[pid(),execname(),filename] <<< $return
}

probe vfs.write.return {
    writes[pid(),execname(),filename] <<< $return
}

probe timer.s(5) {
    println("\nReads:")
    foreach([pid,exec,file] in reads-) {
        printf("%d %s %s: %d\n", pid, exec, file, @count(reads[pid,exec,file]))
    }
    
    println("\nWrites:")
    foreach([pid,exec,file] in writes-) {
        printf("%d %s %s: %d\n", pid, exec, file, @count(writes[pid,exec,file]))
    }
    
    clear(reads)
    clear(writes)
}

Run with: sudo stap io_monitor.stp

Modern Linux kernels support eBPF for efficient tracing. Tools like bcc provide file I/O monitoring:

# Trace file opens
sudo opensnoop-bpfcc

# Trace read operations
sudo readsnoop-bpfcc

# Trace write operations
sudo writesnoop-bpfcc

For database servers, consider:

1. MySQL: Enable the Performance Schema with performance_schema_instrument='wait/io/file/%=ON'
2. PostgreSQL: Use pg_stat_statements extension combined with pg_stat_activity
3. MongoDB: Analyze the profiler output and db.currentOp() results

For long-term monitoring, configure auditd rules:

# Monitor file opens in /var/lib/mysql
sudo auditctl -w /var/lib/mysql -p rwa -k mysql_io

# View logs:
sudo ausearch -k mysql_io | aureport -f -i