Iotop + PostgreSQL

Sometimes it is useful to look at the current I/O load on a machine.
For these purposes, you can use the iotop utility.

installation [Centos 7]

yum -y install iotop

For instance, maybe you only want global disk usage data. The following command accomplishes this:

sudo iotop -botqqqk --iter=60 >> /tmp/io.txt

Next, using a regular expression in Linux, we will obtain a certain set of data for the operating system user postgres, which we will then load into the database using a utility COPY

grep postgres: /tmp/io.txt | sed -r 's/^(\S+)\s+(\S+)\s+\S+\s+\S+\s+(\S+) K\/s\s+(\S+).+(postgres:.*)$/\1\t\2\t\3\t\4\t\5/' > res.txt

Then we will create a table in the database:

CREATE UNLOGGED TABLE iotop_log (
ts time NOT NULL,
pid int NOT NULL,
read_kb numeric NOT NULL,
write_kb numeric NOT NULL,
details text NOT NULL
);

Load the data

\copy iotop_log FROM 'res.txt' WITH (FORMAT text);

Prepare for querying:

VACUUM ANALYZE iotop_log;

Execute the SQL:

WITH base AS (
   SELECT *,
          CASE WHEN details LIKE 'postgres: autovacuum worker%' THEN 'autovacuum'
               WHEN details LIKE 'postgres: walwriter%' THEN 'walwriter'
               WHEN details LIKE 'postgres: background writer%' THEN 'bgwriter'
               WHEN details LIKE 'postgres: logger' THEN 'logger'
               WHEN details LIKE 'postgres: stats collector' THEN 'stats collector'
               WHEN details LIKE 'postgres: checkpointer' THEN 'checkpointer'
               ELSE 'other'
          END AS detail_type
     FROM iotop_log
), totals AS (
SELECT detail_type,
       SUM(read_kb) AS read_kb, SUM(write_kb) AS write_kb
  FROM base
 GROUP BY detail_type
)
SELECT *,
       trunc(100.0 * read_kb / SUM(read_kb) OVER (), 2) AS "read %total" ,
       trunc(100.0 * write_kb / SUM(write_kb) OVER (), 2) AS "write %total",
       trunc(read_kb / write_kb, 4) AS RW_ratio,
       pg_size_pretty(read_kb*1024.0) AS read_hr,
       pg_size_pretty(write_kb*1024.0) AS write_hr
  FROM totals
 ORDER BY write_kb DESC;

Let's look at some results

 detail_type     |  read_kb   |  write_kb  | read %total | write %total | rw_ratio |   read_hr   | write_hr |
-----------------+------------+------------+-------------+--------------+----------+-------------+----------
 other           | 1363304.90 | 8835663.36 |      100.00 |        99.81 |   0.1542 | 1331 MB     | 8629 MB  |
 stats collector |       0.00 |    9207.64 |        0.00 |         0.10 |   0.0000 | 0.000 bytes | 9208 kB  |
 walwriter       |       0.00 |    5159.32 |        0.00 |         0.05 |   0.0000 | 0.000 bytes | 5159 kB  |
 logger          |       0.00 |    1426.85 |        0.00 |         0.01 |   0.0000 | 0.000 bytes | 1427 kB  |
 autovacuum      |       0.00 |     326.90 |        0.00 |         0.00 |   0.0000 | 0.000 bytes | 327 kB   |
 bgwriter        |       0.00 |      53.37 |        0.00 |         0.00 |   0.0000 | 0.000 bytes | 53 kB    |
(6 rows)