
Создание базы и настройка
~~~~~~~~~~~~~~~~~~~~~~~~~


=> create database db9;
CREATE DATABASE

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

=> alter system reset all;
ALTER SYSTEM

=> alter system set bgwriter_lru_maxpages = 0;
ALTER SYSTEM

=> alter system set checkpoint_timeout = '30s';
ALTER SYSTEM

=> alter system set log_checkpoints = on;
ALTER SYSTEM

=> alter system set log_line_prefix = '%t ';
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 db9
You are now connected to database "db9" as user "postgres".

Таблицы для теста:

pgbench -i db9 
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.11 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

Статистика
~~~~~~~~~~


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


Нагрузка
~~~~~~~~


=> select pg_current_xlog_location() as start_lsn \gset

pgbench -T 180 -P 10 db9 
starting vacuum...end.
progress: 10.0 s, 884.9 tps, lat 1.128 ms stddev 0.473
progress: 20.0 s, 900.6 tps, lat 1.108 ms stddev 0.496
progress: 30.0 s, 903.6 tps, lat 1.105 ms stddev 0.515
progress: 40.0 s, 908.5 tps, lat 1.099 ms stddev 0.531
progress: 50.0 s, 895.3 tps, lat 1.115 ms stddev 0.659
progress: 60.0 s, 918.5 tps, lat 1.087 ms stddev 0.514
progress: 70.0 s, 837.0 tps, lat 1.193 ms stddev 0.727
progress: 80.0 s, 875.6 tps, lat 1.140 ms stddev 0.501
progress: 90.0 s, 909.3 tps, lat 1.098 ms stddev 0.458
progress: 100.0 s, 882.3 tps, lat 1.132 ms stddev 0.890
progress: 110.0 s, 843.0 tps, lat 1.184 ms stddev 0.738
progress: 120.0 s, 881.2 tps, lat 1.133 ms stddev 0.574
progress: 130.0 s, 902.2 tps, lat 1.107 ms stddev 1.022
progress: 140.0 s, 938.3 tps, lat 1.064 ms stddev 0.447
progress: 150.0 s, 930.4 tps, lat 1.073 ms stddev 0.570
progress: 160.0 s, 927.9 tps, lat 1.076 ms stddev 0.416
progress: 170.0 s, 902.0 tps, lat 1.107 ms stddev 0.561
progress: 180.0 s, 855.5 tps, lat 1.167 ms stddev 0.621
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 160962
latency average: 1.116 ms
latency stddev: 0.614 ms
tps = 894.231108 (including connections establishing)
tps = 894.243905 (excluding connections establishing)

=> select pg_current_xlog_location() as end_lsn \gset

Объем журнала
~~~~~~~~~~~~~


=> select pg_size_pretty(:'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn);
 pg_size_pretty 
----------------
 149 MB
(1 row)


Выполнение контрольной точки
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Средний объем генерируемых журнальных записей в минуту:

=> select pg_size_pretty(((:'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn)/3)::numeric(20));
 pg_size_pretty 
----------------
 50 MB
(1 row)


Максимальный размер журнала:

=> select pg_size_pretty(setting::numeric*16*1024*1024) from pg_settings where name='max_wal_size';
 pg_size_pretty 
----------------
 1024 MB
(1 row)


Если генерируемый объем не превышает максимальный, то основная часть контрольных точек будет выполнена по расписанию.

Проверим статистику:

=> select checkpoints_timed, checkpoints_req from pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req 
-------------------+-----------------
                 6 |               0
(1 row)


Журнал сообщений сервера
~~~~~~~~~~~~~~~~~~~~~~~~


tail -n 20 /home/postgres/logfile 
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
2016-07-02 16:31:30 MSK LOG:  database system was shut down at 2016-07-02 16:31:29 MSK
2016-07-02 16:31:30 MSK LOG:  MultiXact member wraparound protections are now enabled
2016-07-02 16:31:30 MSK LOG:  database system is ready to accept connections
2016-07-02 16:31:30 MSK LOG:  autovacuum launcher started
2016-07-02 16:32:00 MSK LOG:  checkpoint starting: time
2016-07-02 16:32:15 MSK LOG:  checkpoint complete: wrote 2056 buffers (12.5%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=14.866 s, sync=0.055 s, total=14.939 s; sync files=44, longest=0.022 s, average=0.001 s; distance=24709 kB, estimate=24709 kB
2016-07-02 16:32:30 MSK LOG:  checkpoint starting: time
2016-07-02 16:32:45 MSK LOG:  checkpoint complete: wrote 2233 buffers (13.6%); 0 transaction log file(s) added, 1 removed, 0 recycled; write=14.965 s, sync=0.028 s, total=15.007 s; sync files=19, longest=0.013 s, average=0.001 s; distance=24948 kB, estimate=24948 kB
2016-07-02 16:33:00 MSK LOG:  checkpoint starting: time
2016-07-02 16:33:15 MSK LOG:  checkpoint complete: wrote 2024 buffers (12.4%); 0 transaction log file(s) added, 1 removed, 1 recycled; write=14.957 s, sync=0.023 s, total=14.995 s; sync files=6, longest=0.009 s, average=0.003 s; distance=24743 kB, estimate=24928 kB
2016-07-02 16:33:30 MSK LOG:  checkpoint starting: time
2016-07-02 16:33:45 MSK LOG:  checkpoint complete: wrote 2215 buffers (13.5%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=14.867 s, sync=0.025 s, total=14.907 s; sync files=16, longest=0.012 s, average=0.001 s; distance=23638 kB, estimate=24799 kB
2016-07-02 16:34:00 MSK LOG:  checkpoint starting: time
2016-07-02 16:34:15 MSK LOG:  checkpoint complete: wrote 1978 buffers (12.1%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=14.961 s, sync=0.047 s, total=15.015 s; sync files=5, longest=0.030 s, average=0.009 s; distance=23673 kB, estimate=24686 kB
2016-07-02 16:34:30 MSK LOG:  checkpoint starting: time


Время записи на диск - следствие установки параметра checkpoint_completion_target = 0.5 (15 секунд в нашем случае).

Восстанавливаем настройки
~~~~~~~~~~~~~~~~~~~~~~~~~


=> alter system reset all;
ALTER SYSTEM

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

=> \q
