0

I have a write-heavy application on a PostgreSQL 9.4 on CentOS 6 that I'm trying to tune.

Using iotop, I see that my session processers are doing all the heavy writing on disk when (to my understanding) only the checkpointer and writer processes should be doing it.

I thought that the sessions might be writing temp files due to lack of workmem but when I query the pg_stat_database, the columns "temp_files" and "temp_bytes" are zero. I'm trying to log this kind of usage via system parameter "log_temp_files" but nothing appears on the log files.

Can somebody help me? Are there any kind of data that the sessions write directly on disk besides temp files?

EDIT: Added an iotop example

19156 be/4 postgres    0.00 B/s  382.33 K/s  0.00 %  0.00 % postgres: checkpointer process
19167 be/4 postgres    0.00 B/s  146.96 K/s  0.00 %  3.03 % postgres: example example 10.18.14.1(59536) idle
19190 be/4 postgres    0.00 B/s  146.16 K/s  0.00 %  2.89 % postgres: example example 10.18.14.1(59551) idle
19164 be/4 postgres    0.00 B/s  144.17 K/s  0.00 %  2.20 % postgres: example example 10.18.14.1(59533) idle
19172 be/4 postgres    0.00 B/s  128.64 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59541) idle
19161 be/4 postgres    0.00 B/s  122.27 K/s  0.00 %  1.88 % postgres: example example 10.18.14.1(59525) idle
19174 be/4 postgres    0.00 B/s  119.08 K/s  0.00 %  2.08 % postgres: example example 10.18.14.1(59543) idle
19170 be/4 postgres    0.00 B/s  116.29 K/s  0.00 %  2.62 % postgres: example example 10.18.14.1(59539) idle
19169 be/4 postgres    0.00 B/s  115.50 K/s  0.00 %  1.99 % postgres: example example 10.18.14.1(59538) idle
 1304 be/4 root        0.00 B/s  115.50 K/s  0.00 %  0.92 % [flush-253:0]
19173 be/4 postgres    0.00 B/s  113.90 K/s  0.00 %  1.97 % postgres: example example 10.18.14.1(59542) SELECT
19175 be/4 postgres    0.00 B/s  113.50 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59544) idle
19476 be/4 postgres    0.00 B/s  111.91 K/s  0.00 %  1.91 % postgres: example example 10.18.14.1(59618) idle
19434 be/4 postgres    0.00 B/s  110.32 K/s  0.00 %  1.77 % postgres: example example 10.18.14.1(59606) DISCARD ALL
19168 be/4 postgres    0.00 B/s  101.16 K/s  0.00 %  1.82 % postgres: example example 10.18.14.1(59537) SELECT
19166 be/4 postgres    0.00 B/s   99.57 K/s  0.00 %  2.82 % postgres: example example 10.18.14.1(59535) idle
19171 be/4 postgres    0.00 B/s   98.77 K/s  0.00 %  1.78 % postgres: example example 10.18.14.1(59540) idle
19179 be/4 postgres    0.00 B/s   98.37 K/s  0.00 %  2.22 % postgres: example example 10.18.14.1(59547) idle
19163 be/4 postgres    0.00 B/s   96.38 K/s  0.00 %  1.72 % postgres: example example 10.18.14.1(59532) idle
19189 be/4 postgres    0.00 B/s   94.39 K/s  0.00 %  2.26 % postgres: example example 10.18.14.1(59550) idle

Thanks in advance.

1 Answer 1

1

Write-heavy operations like COPY or INSERT INTO ... SELECT FROM will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Even if your write heavy operations do not use a buffer access strategy:

  1. There is no reason for checkpointing to be doing most of the writing unless our checkpoint_segments are set too small.

  2. If your bgwriter is not aggressive enough, it might not get enough data written and so the user backends will do it themselves. You could try to make bgwriter more aggressive. But ever since version 8.3.20 or so, the bgwriter is not very useful anyway, and having the backends do writes themselves is rarely a problem anymore.

3
  • I'm asking because I just don't know what is causing the heavy I/O. All the checkpoints according to pg_stat_bgwriter are timed ones and the I/O that is showing on iotop for the checkpointer process is acceptable. I'm positive that none of the functions that are being called use COPY, so I'm trying to identify what is causing my sessions to write so heavly on disk.
    – Pivobispo
    Dec 7, 2015 at 21:11
  • If they are write heavy but don't use COPY, what do they use?
    – jjanes
    Dec 7, 2015 at 21:16
  • Mainly some functions with selects and DMLs. There are some postgis function calls too. The database receive a huge number geospatial information each second that needs to be processed and stored.
    – Pivobispo
    Dec 7, 2015 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.