
БУФЕРНЫЙ КЭШ
~~~~~~~~~~~~

Создадим отдельную базу данных:

        => create database db7;
        CREATE DATABASE

        => \c db7
        You are now connected to database "db7" as user "postgres".

И таблицу в ней:

        => create table test(t text);
        CREATE TABLE

        => insert into test select 'line '||s.id from generate_series(1,500) as s(id);
        INSERT 0 500

        => vacuum analyze test;
        VACUUM

.......................................................................

Уменьшим временно размер буферного кэша, чтобы упростить наблюдение за ним.

        => alter system set shared_buffers=512;
        ALTER SYSTEM

        => \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

.......................................................................

Проверим размер кэша:

        psql 

        => \c db7
        You are now connected to database "db7" as user "postgres".

        => select setting, unit from pg_settings where name = 'shared_buffers';
         setting | unit 
        ---------+------
         512     | 8kB
        (1 row)
        

.......................................................................

Содержимое буферного кэша можно посмотреть с помощью расширения:

        => create extension pg_buffercache;
        CREATE EXTENSION

.......................................................................

Создадим для удобства представление:

        => create view pg_buffercache_v as
        => select bufferid,
        =>        (select c.relname
        =>         from   pg_class c
        =>         where  pg_relation_filenode(c.oid) = b.relfilenode
        =>        ) relname,
        =>        case relforknumber
        =>          when 0 then 'main'
        =>          when 1 then 'fsm'
        =>          when 2 then 'vm'
        =>        end relfork,
        =>        relblocknumber,
        =>        isdirty,
        =>        usagecount
        => from   pg_buffercache b
        => where  b.reldatabase in (0,(select oid from pg_database where datname=current_database()))
        => and    b.usagecount is not null;
        CREATE VIEW

Условие на базу данных необходимо, так как в буферном кэше содержатся данные
всего кластера. Расшифровать мы можем только информацию из той БД, к которой
подключены. Глобальные объекты считаются принадлежащими БД с нулевым oid.

.......................................................................

        => select * from pg_buffercache_v;
         bufferid |                 relname                 | relfork | relblocknumber | isdirty | usagecount 
        ----------+-----------------------------------------+---------+----------------+---------+------------
                1 | pg_database                             | main    |              0 | f       |          5
                2 | pg_authid                               | main    |              0 | f       |          5
               55 | pg_database_oid_index                   | main    |              0 | f       |          3
               56 | pg_database_oid_index                   | main    |              1 | f       |          3
               57 | pg_db_role_setting_databaseid_rol_index | main    |              0 | f       |          5
               58 | pg_authid_rolname_index                 | main    |              0 | f       |          2
               59 | pg_authid_rolname_index                 | main    |              1 | f       |          2
               60 | pg_authid_oid_index                     | main    |              0 | f       |          2
               61 | pg_authid_oid_index                     | main    |              1 | f       |          2
               62 | pg_database_datname_index               | main    |              0 | f       |          2
               63 | pg_database_datname_index               | main    |              1 | f       |          4
               64 | pg_class                                | main    |              0 | t       |          5
               65 | pg_class                                | main    |              1 | f       |          5
               66 | pg_class                                | main    |              2 | f       |          5
               67 | pg_attribute                            | main    |              0 | f       |          5
               68 | pg_attribute                            | main    |              1 | f       |          5
               69 | pg_attribute                            | main    |              2 | f       |          5
               70 | pg_attribute                            | main    |              3 | f       |          5
               71 | pg_attribute                            | main    |              4 | f       |          5
               72 | pg_attribute                            | main    |              5 | f       |          5
               73 | pg_attribute                            | main    |              6 | f       |          5
               74 | pg_attribute                            | main    |              7 | f       |          5
               75 | pg_attribute                            | main    |              8 | f       |          5
               76 | pg_attribute                            | main    |              9 | f       |          5
               77 | pg_attribute                            | main    |             10 | f       |          5
               78 | pg_attribute                            | main    |             11 | f       |          5
               79 | pg_attribute                            | main    |             12 | f       |          5
               80 | pg_attribute                            | main    |             13 | f       |          5
               81 | pg_attribute                            | main    |             14 | f       |          5
               82 | pg_attribute                            | main    |             15 | f       |          5
               83 | pg_attribute                            | main    |             16 | f       |          5
               84 | pg_attribute                            | main    |             17 | f       |          5
               85 | pg_class                                | main    |              3 | f       |          5
               86 | pg_class                                | main    |              4 | f       |          5
               87 | pg_class                                | main    |              5 | f       |          4
               88 | pg_class                                | main    |              6 | f       |          5
               89 | pg_index                                | main    |              0 | f       |          5
               90 | pg_am                                   | main    |              0 | f       |          1
               91 | pg_opclass                              | main    |              0 | f       |          5
               92 | pg_amproc                               | main    |              0 | f       |          5
               93 | pg_amproc                               | main    |              1 | f       |          2
               94 | pg_amproc                               | main    |              2 | f       |          2
               95 | pg_amproc                               | main    |              3 | f       |          2
               96 | pg_index                                | main    |              1 | f       |          5
               97 | pg_attribute                            | main    |             18 | f       |          5
               98 | pg_opclass_oid_index                    | main    |              0 | f       |          1
               99 | pg_opclass_oid_index                    | main    |              1 | f       |          5
              100 | pg_amproc_fam_proc_index                | main    |              0 | f       |          1
              101 | pg_amproc_fam_proc_index                | main    |              3 | f       |          5
              102 | pg_amproc_fam_proc_index                | main    |              1 | f       |          5
              103 | pg_class_oid_index                      | main    |              0 | f       |          1
              104 | pg_class_oid_index                      | main    |              1 | t       |          5
              105 | pg_attribute_relid_attnum_index         | main    |              0 | f       |          1
              106 | pg_attribute_relid_attnum_index         | main    |              3 | f       |          5
              107 | pg_attribute_relid_attnum_index         | main    |              1 | f       |          5
              108 | pg_attribute_relid_attnum_index         | main    |              2 | f       |          5
              109 | pg_index_indexrelid_index               | main    |              0 | f       |          1
              110 | pg_index_indexrelid_index               | main    |              1 | f       |          5
              111 | pg_class                                | main    |              7 | f       |          5
              112 | pg_attribute_relid_attnum_index         | main    |              4 | f       |          5
              113 | pg_attribute                            | main    |             19 | f       |          5
              114 | pg_index                                | main    |              2 | f       |          5
              115 | pg_attribute_relid_attnum_index         | main    |              5 | f       |          5
              116 | pg_namespace_nspname_index              | main    |              0 | f       |          1
              117 | pg_namespace_nspname_index              | main    |              1 | f       |          3
              118 | pg_namespace                            | main    |              0 | f       |          3
              119 | pg_class_relname_nsp_index              | main    |              0 | f       |          1
              120 | pg_class_relname_nsp_index              | main    |              3 | f       |          5
              121 | pg_class_relname_nsp_index              | main    |              4 | f       |          1
              122 | pg_attribute                            | main    |             21 | f       |          4
              123 | pg_attribute                            | main    |             22 | f       |          1
              124 | pg_rewrite_rel_rulename_index           | main    |              0 | f       |          1
              125 | pg_rewrite_rel_rulename_index           | main    |              1 | t       |          5
              126 | pg_rewrite                              | main    |              1 | f       |          1
              127 | pg_rewrite                              | main    |              2 | f       |          1
              128 | pg_operator_oprname_l_r_n_index         | main    |              0 | f       |          1
              129 | pg_operator_oprname_l_r_n_index         | main    |              3 | f       |          4
              130 | pg_operator_oprname_l_r_n_index         | main    |              2 | f       |          4
              131 | pg_operator                             | main    |              0 | f       |          4
              132 | pg_operator_oid_index                   | main    |              0 | f       |          1
              133 | pg_operator_oid_index                   | main    |              3 | f       |          4
              134 | pg_operator_oid_index                   | main    |              1 | f       |          4
              135 | pg_type_oid_index                       | main    |              0 | f       |          1
              136 | pg_type_oid_index                       | main    |              1 | t       |          5
              137 | pg_type                                 | main    |              0 | f       |          5
              138 | pg_proc_oid_index                       | main    |              0 | f       |          1
              139 | pg_proc_oid_index                       | main    |              3 | f       |          5
              140 | pg_proc_oid_index                       | main    |              1 | f       |          4
              141 | pg_proc                                 | main    |              0 | f       |          2
              142 | pg_proc_oid_index                       | main    |              4 | f       |          1
              143 | pg_proc                                 | main    |             38 | t       |          5
              144 | pg_amop_opr_fam_index                   | main    |              0 | f       |          1
              145 | pg_amop_opr_fam_index                   | main    |              3 | f       |          3
              146 | pg_amop_opr_fam_index                   | main    |              1 | f       |          3
              147 | pg_amop                                 | main    |              0 | f       |          5
              148 | pg_amop                                 | main    |              2 | f       |          2
              149 | pg_amop                                 | main    |              1 | f       |          3
              150 | pg_amop                                 | main    |              3 | f       |          3
              151 | pg_type                                 | main    |              1 | f       |          2
              152 | pg_event_trigger_evtname_index          | main    |              0 | f       |          1
              153 | pg_extension_name_index                 | main    |              0 | f       |          1
              154 | pg_extension_name_index                 | main    |              1 | t       |          2
              155 | pg_namespace_oid_index                  | main    |              0 | f       |          1
              156 | pg_namespace_oid_index                  | main    |              1 | f       |          1
              157 | pg_index_indrelid_index                 | main    |              0 | f       |          1
              158 | pg_index_indrelid_index                 | main    |              1 | f       |          5
              159 | pg_extension_oid_index                  | main    |              0 | f       |          1
              160 | pg_extension_oid_index                  | main    |              1 | t       |          2
              161 | pg_extension                            | fsm     |              0 | f       |          1
              162 | pg_extension                            | fsm     |              1 | f       |          1
              163 | pg_extension                            | fsm     |              2 | f       |          1
              164 | pg_extension                            | main    |              0 | t       |          1
              165 | pg_extension                            | vm      |              0 | t       |          1
              166 | pg_shdepend_reference_index             | main    |              0 | f       |          1
              167 | pg_shdepend_reference_index             | main    |              1 | f       |          4
              168 | pg_shdepend                             | main    |              0 | f       |          4
              169 | pg_depend_reference_index               | main    |              0 | f       |          1
              170 | pg_depend_reference_index               | main    |              3 | f       |          5
              171 | pg_depend_reference_index               | main    |             22 | t       |          5
              172 | pg_depend                               | main    |             40 | t       |          5
              173 | pg_depend                               | fsm     |              0 | f       |          1
              174 | pg_depend                               | fsm     |              1 | f       |          1
              175 | pg_depend                               | fsm     |              2 | t       |          2
              176 | pg_depend_depender_index                | main    |              0 | f       |          1
              177 | pg_depend_depender_index                | main    |              3 | f       |          5
              178 | pg_depend_depender_index                | main    |             34 | t       |          5
              179 | pg_description_o_c_o_index              | main    |              0 | f       |          1
              180 | pg_description_o_c_o_index              | main    |              3 | f       |          2
              181 | pg_description_o_c_o_index              | main    |             19 | t       |          2
              182 | pg_description                          | fsm     |              0 | f       |          1
              183 | pg_description                          | fsm     |              1 | f       |          1
              184 | pg_description                          | fsm     |              2 | f       |          1
              185 | pg_description                          | main    |             30 | t       |          1
              186 | pg_description                          | vm      |              0 | t       |          1
              187 | pg_language_name_index                  | main    |              0 | f       |          1
              188 | pg_language_name_index                  | main    |              1 | f       |          1
              189 | pg_language                             | main    |              0 | f       |          2
              190 | pg_type_typname_nsp_index               | main    |              0 | f       |          1
              191 | pg_type_typname_nsp_index               | main    |              3 | f       |          5
              192 | pg_type_typname_nsp_index               | main    |              4 | t       |          5
              193 | pg_type                                 | main    |              3 | f       |          2
              194 | pg_proc_proname_args_nsp_index          | main    |              0 | f       |          1
              195 | pg_proc_proname_args_nsp_index          | main    |              3 | f       |          5
              196 | pg_proc_proname_args_nsp_index          | main    |             16 | t       |          3
              197 | pg_default_acl_role_nsp_obj_index       | main    |              0 | f       |          5
              198 | pg_proc_oid_index                       | main    |              8 | t       |          4
              199 | pg_proc                                 | fsm     |              0 | f       |          1
              200 | pg_proc                                 | fsm     |              1 | f       |          1
              201 | pg_proc                                 | fsm     |              2 | f       |          1
              202 | pg_proc                                 | vm      |              0 | t       |          1
              203 | pg_depend_depender_index                | main    |             26 | t       |          5
              204 | pg_depend                               | main    |             22 | f       |          1
              205 | pg_depend_reference_index               | main    |              1 | f       |          5
              206 | pg_depend                               | main    |             20 | f       |          5
              207 | pg_transform_type_lang_index            | main    |              0 | f       |          1
              208 | pg_depend_reference_index               | main    |             21 | t       |          4
              209 | pg_language_oid_index                   | main    |              0 | f       |          1
              210 | pg_language_oid_index                   | main    |              1 | f       |          1
              211 | pg_type_typname_nsp_index               | main    |              1 | t       |          5
              212 | pg_class_relname_nsp_index              | main    |              1 | t       |          5
              213 | pg_type                                 | fsm     |              0 | f       |          1
              214 | pg_type                                 | fsm     |              1 | f       |          1
              215 | pg_type                                 | fsm     |              2 | f       |          1
              216 | pg_type                                 | main    |              7 | t       |          4
              217 | pg_type                                 | vm      |              0 | t       |          1
              218 | pg_depend_reference_index               | main    |             13 | f       |          5
              219 | pg_depend                               | main    |             14 | f       |          5
              220 | pg_depend_reference_index               | main    |             37 | t       |          5
              221 | pg_depend_reference_index               | main    |              6 | f       |          5
              222 | pg_depend                               | main    |              4 | f       |          5
              223 | pg_depend_reference_index               | main    |             17 | f       |          2
              224 | pg_depend_reference_index               | main    |             33 | t       |          4
              225 | pg_class                                | fsm     |              0 | f       |          1
              226 | pg_class                                | fsm     |              1 | f       |          1
              227 | pg_class                                | fsm     |              2 | f       |          1
              228 | pg_class_tblspc_relfilenode_index       | main    |              0 | f       |          1
              229 | pg_class_tblspc_relfilenode_index       | main    |              1 | t       |          2
              230 | pg_attribute                            | fsm     |              0 | f       |          1
              231 | pg_attribute                            | fsm     |              1 | f       |          1
              232 | pg_attribute                            | fsm     |              2 | f       |          1
              233 | pg_attribute                            | main    |             42 | t       |          5
              234 | pg_attribute_relid_attnam_index         | main    |              0 | f       |          1
              235 | pg_attribute_relid_attnam_index         | main    |              3 | f       |          5
              236 | pg_attribute_relid_attnam_index         | main    |             11 | t       |          5
              237 | pg_attribute_relid_attnum_index         | main    |              8 | t       |          5
              238 | pg_depend                               | main    |             19 | f       |          5
              239 | pg_depend_depender_index                | main    |             33 | t       |          3
              240 | pg_rewrite_oid_index                    | main    |              0 | f       |          1
              241 | pg_rewrite_oid_index                    | main    |              1 | t       |          4
              242 | pg_rewrite                              | fsm     |              0 | f       |          1
              243 | pg_rewrite                              | fsm     |              1 | f       |          1
              244 | pg_rewrite                              | fsm     |              2 | f       |          1
              245 | pg_rewrite                              | main    |             11 | t       |          5
              246 | pg_rewrite                              | vm      |              0 | t       |          1
              247 | pg_depend                               | main    |             50 | t       |          5
              248 | pg_depend                               | vm      |              0 | t       |          1
              249 | pg_depend_reference_index               | main    |             31 | t       |          5
              250 | pg_proc_proname_args_nsp_index          | main    |             17 | f       |          1
              251 | pg_proc                                 | main    |             45 | f       |          2
              252 | pg_type                                 | main    |              2 | f       |          1
              253 | pg_cast_source_target_index             | main    |              0 | f       |          1
              254 | pg_cast_source_target_index             | main    |              1 | f       |          5
              255 | pg_cast                                 | main    |              0 | f       |          4
              256 | pg_proc_oid_index                       | main    |              6 | f       |          1
              257 | pg_operator                             | main    |              2 | f       |          2
              258 | pg_proc                                 | main    |              3 | f       |          1
              259 | pg_operator                             | main    |              1 | f       |          2
              260 | pg_proc                                 | main    |              2 | f       |          1
              261 | pg_proc_proname_args_nsp_index          | main    |              5 | f       |          1
              262 | pg_proc                                 | main    |             12 | f       |          2
              263 | pg_proc_oid_index                       | main    |              2 | f       |          1
              264 | pg_toast_2618_index                     | main    |              0 | f       |          1
              265 | pg_toast_2618_index                     | main    |              1 | t       |          4
              266 | pg_toast_2618                           | fsm     |              0 | f       |          1
              267 | pg_toast_2618                           | fsm     |              1 | f       |          1
              268 | pg_toast_2618                           | fsm     |              2 | f       |          1
              269 | pg_toast_2618                           | main    |             45 | t       |          3
              270 | pg_toast_2618                           | vm      |              0 | t       |          1
              271 | pg_depend_reference_index               | main    |             12 | f       |          1
              272 | pg_depend                               | main    |              0 | f       |          4
              273 | pg_depend_reference_index               | main    |             34 | f       |          1
              274 | pg_depend                               | main    |             23 | f       |          2
              275 | pg_depend_reference_index               | main    |             19 | f       |          2
              276 | pg_depend                               | main    |             24 | f       |          1
              277 | pg_statistic_relid_att_inh_index        | main    |              0 | f       |          1
              278 | pg_statistic_relid_att_inh_index        | main    |              1 | f       |          2
              279 | pg_statistic                            | main    |              3 | f       |          1
              280 | pg_tablespace_oid_index                 | main    |              0 | f       |          1
              281 | pg_tablespace_oid_index                 | main    |              1 | f       |          2
              282 | pg_tablespace                           | main    |              0 | f       |          2
              283 | pg_statistic                            | main    |             11 | f       |          1
              284 | pg_amop_fam_strat_index                 | main    |              0 | f       |          1
              285 | pg_amop_fam_strat_index                 | main    |              3 | f       |          2
              286 | pg_amop_fam_strat_index                 | main    |              1 | f       |          2
              287 | pg_opclass_am_name_nsp_index            | main    |              0 | f       |          1
              288 | pg_opclass_am_name_nsp_index            | main    |              1 | f       |          1
              289 | pg_opclass                              | main    |              1 | f       |          5
        (237 rows)
        

.......................................................................

Используя это расширение, можно наблюдать за состоянием кэша с разных сторон.
Кроме информации о том, как представлена в кэше та или иная страница,
можно, например, посмотреть распределение буферов по их "популярности":

        => select usagecount,
        =>        count(*)
        => from   pg_buffercache
        => group by usagecount
        => order by usagecount;
         usagecount | count 
        ------------+-------
                  1 |    94
                  2 |    42
                  3 |     8
                  4 |    16
                  5 |   139
                    |   213
        (6 rows)
        

.......................................................................

Посмотрим на расширение для прогрева кэша:

        => create extension pg_prewarm;
        CREATE EXTENSION

.......................................................................

Например, сейчас в кэше нет таблицы test (мы перезагружали сервер):

        => select * from pg_buffercache_v where relname = 'test';
         bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
        ----------+---------+---------+----------------+---------+------------
        (0 rows)
        

.......................................................................

В простейшем виде указанная таблица считывается в буферный кэш.

        => select pg_prewarm('test');
         pg_prewarm 
        ------------
                  3
        (1 row)
        

        => select * from pg_buffercache_v where relname = 'test';
         bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
        ----------+---------+---------+----------------+---------+------------
              303 | test    | main    |              0 | f       |          1
              304 | test    | main    |              1 | f       |          1
              305 | test    | main    |              2 | f       |          1
        (3 rows)
        

Расширение pg_prewarm может также использоваться для прогрева кэша
операционной системы.

.......................................................................

Изменим одну строку таблицы (происходит удаление и вставка):

        => update test set t = 'changed' where t = 'line 1';
        UPDATE 1

        => select * from pg_buffercache_v where relname = 'test';
         bufferid | relname | relfork | relblocknumber | isdirty | usagecount 
        ----------+---------+---------+----------------+---------+------------
              303 | test    | main    |              0 | t       |          2
              304 | test    | main    |              1 | f       |          2
              305 | test    | main    |              2 | t       |          3
              307 | test    | vm      |              0 | t       |          1
              308 | test    | fsm     |              0 | f       |          1
              309 | test    | fsm     |              1 | f       |          1
              310 | test    | fsm     |              2 | f       |          1
        (7 rows)
        

.......................................................................

Следует учитывать, что расширение pg_buffercache накладывает блокировки
на время доступа к кэшу, поэтому не следует использовать его слишком часто
на продуктивной среде.

.......................................................................

ПРОЦЕСС ФОНОВОЙ ЗАПИСИ
~~~~~~~~~~~~~~~~~~~~~~

Работа фонового процесса записи отражается в собираемой статистике;
за ней можно наблюдать с помощью представления pg_stat_bgwriter.
Часть полей этого представления относится и к процессу checkpointer.
Пока мы не будем обращать на них внимание.

        => select buffers_clean, maxwritten_clean, buffers_backend from pg_stat_bgwriter;
         buffers_clean | maxwritten_clean | buffers_backend 
        ---------------+------------------+-----------------
                 40943 |              360 |          745963
        (1 row)
        

* buffers_clean    - количество буферов, записанных на диск процессом writer,
* maxwritten_clean - количество остановок по достижению bgwriter_lru_maxpages,
* buffers_backend  - количество буферов, записанных серверными процессами.

.......................................................................

Дадим нагрузку на базу данных.
Для этого удобно воспользоваться расширением pgbench. Оно позволяет прогонять
тест TPC-B или любой пользовательский сценарий.

Сначала надо создать таблицы для теста:

pgbench -i db7 
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.14 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

.......................................................................

Для чистоты эксперимента выполним контрольную точку, чтобы сбросить все
грязные буферы на диск, и сбросим статистику.

        => checkpoint;
        CHECKPOINT

        => select pg_stat_reset_shared('bgwriter');
         pg_stat_reset_shared 
        ----------------------
         
        (1 row)
        

.......................................................................

Теперь можно запускать сам тест.
Установим длительность 30 секунд и скорость 100 транзакций в секунду.


pgbench -T 30 -R 100 -P 1 db7 
starting vacuum...end.
progress: 1.0 s, 96.0 tps, lat 2.036 ms stddev 1.077, lag 0.380 ms
progress: 2.0 s, 106.0 tps, lat 2.304 ms stddev 1.500, lag 0.389 ms
progress: 3.0 s, 95.0 tps, lat 2.203 ms stddev 0.925, lag 0.319 ms
progress: 4.0 s, 107.0 tps, lat 2.046 ms stddev 0.746, lag 0.286 ms
progress: 5.0 s, 97.0 tps, lat 3.376 ms stddev 5.831, lag 1.348 ms
progress: 6.0 s, 96.0 tps, lat 2.473 ms stddev 0.828, lag 0.437 ms
progress: 7.0 s, 101.1 tps, lat 2.464 ms stddev 1.280, lag 0.516 ms
progress: 8.0 s, 96.0 tps, lat 1.952 ms stddev 0.886, lag 0.218 ms
progress: 9.0 s, 97.0 tps, lat 2.586 ms stddev 1.691, lag 0.547 ms
progress: 10.0 s, 112.0 tps, lat 2.310 ms stddev 2.934, lag 0.489 ms
progress: 11.0 s, 111.0 tps, lat 1.817 ms stddev 0.478, lag 0.318 ms
progress: 12.0 s, 89.0 tps, lat 1.854 ms stddev 0.464, lag 0.252 ms
progress: 13.0 s, 104.0 tps, lat 2.033 ms stddev 0.713, lag 0.347 ms
progress: 14.0 s, 96.0 tps, lat 1.660 ms stddev 0.411, lag 0.211 ms
progress: 15.0 s, 94.0 tps, lat 2.128 ms stddev 2.278, lag 0.319 ms
progress: 16.0 s, 106.0 tps, lat 4.631 ms stddev 8.197, lag 2.130 ms
progress: 17.0 s, 82.0 tps, lat 1.789 ms stddev 0.473, lag 0.217 ms
progress: 18.0 s, 117.0 tps, lat 1.828 ms stddev 0.472, lag 0.291 ms
progress: 19.0 s, 110.0 tps, lat 1.799 ms stddev 0.519, lag 0.302 ms
progress: 20.0 s, 99.0 tps, lat 1.709 ms stddev 0.323, lag 0.232 ms
progress: 21.0 s, 103.0 tps, lat 2.364 ms stddev 3.690, lag 0.643 ms
progress: 22.0 s, 92.0 tps, lat 1.671 ms stddev 0.313, lag 0.200 ms
progress: 23.0 s, 82.0 tps, lat 1.754 ms stddev 0.429, lag 0.252 ms
progress: 24.0 s, 107.0 tps, lat 1.770 ms stddev 0.371, lag 0.295 ms
progress: 25.0 s, 115.0 tps, lat 1.943 ms stddev 0.645, lag 0.372 ms
progress: 26.0 s, 93.9 tps, lat 1.883 ms stddev 0.890, lag 0.298 ms
progress: 27.0 s, 96.1 tps, lat 1.609 ms stddev 0.336, lag 0.212 ms
progress: 28.0 s, 110.0 tps, lat 1.718 ms stddev 0.461, lag 0.274 ms
progress: 29.0 s, 74.0 tps, lat 1.623 ms stddev 0.357, lag 0.173 ms
progress: 30.0 s, 82.0 tps, lat 1.718 ms stddev 0.398, lag 0.241 ms
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 2966
latency average: 2.114 ms
latency stddev: 2.312 ms
rate limit schedule lag: avg 0.425 (max 40.150) ms
tps = 98.866278 (including connections establishing)
tps = 98.876063 (excluding connections establishing)

.......................................................................

        => select buffers_clean, maxwritten_clean, buffers_backend from pg_stat_bgwriter;
         buffers_clean | maxwritten_clean | buffers_backend 
        ---------------+------------------+-----------------
                  2846 |                2 |             173
        (1 row)
        

По полученной информации видно, что основную работу выполнял процесс writer,
хотя и серверным процессам пришлось немного поработать.

.......................................................................

Конец демонстрации.

.......................................................................

        => alter system reset all;
        ALTER SYSTEM

        => \q

pg_ctl restart -w -m fast -l /home/postgres/logfile 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
