Copyright

The textbook, practical assignments, presentations (hereinafter referred to as documents) are intended for educational purposes.

The documents are protected by copyright and intellectual property laws.

You may copy and print documents for personal use for self-study purposes, as well as for training in training centers and educational institutions authorized by Tantor Labs LLC. Training centers and educational institutions authorized by Tantor Labs LLC may create training courses based on the documents and use the documents in training programs with the written permission of Tantor Labs LLC.

You may not use the documents for training employees or others without permission from Tantor Labs LLC. You may not license or commercially use the documents in whole or in part without permission from Tantor Labs LLC.

For non-commercial use (presentations, reports, articles, books) of information from documents (text, images, commands), keep a link to the documents.

The text of the documents cannot be changed in any way.

The information contained in the documents may be changed without prior notice and we do not guarantee its accuracy. If you find errors, copyright infringement, please inform us about it.

Disclaimer of liability for the content of the document, products and services of third parties:

Tantor Labs, LLC and its affiliates are not responsible for and expressly disclaim any warranties of any kind, including loss of income, whether direct or indirect, special or incidental, arising from the use of the document. Tantor Labs, LLC and its affiliates are not responsible for any losses, costs or damages arising from the use of the information contained in the document or the use of third-party links, products or services.

Copyright © 2025, Tantor Labs LLC

Created by : Oleg Ivanov

Practice for Chapter 1

Part 1. Standard pgbench test

Virtual machine image for the course: https://disk.yandex.ru/d/APErrktFq-Gamg

1) When running the practices, the instance will be restarted many times. Create a file with a short name to conveniently restart the instance:

astra@tantor:~$ su -

Password: root

root@tantor:~# echo "systemctl restart tantor-se-server-16" > /usr/local/bin/restart

root@tantor:~# chmod 755 /usr/local/bin/restart

root@tantor:~# <ctrl+d>

logout

astra@tantor:~$ sudo restart 

astra @ tantor :~$

You created a batch file and restarted the instance using it.

2) Switch to the postgres operating system user :

astra@tantor:~$ su - postgres

Password: postgres

postgres@tantor:~$

3) The pgbench utility comes with postgres and uses tables for built-in tests.

Create tables for p g bench tests :

postgres@tantor:~$ pgbench -i

dropping old tables...

NOTICE: table "pgbench_accounts" does not exist, skipping

NOTICE: table "pgbench_branches" does not exist, skipping

NOTICE: table "pgbench_history" does not exist, skipping

NOTICE: table "pgbench_tellers" does not exist, skipping

creating tables...

generating data (client-side)...

100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s)

vacuuming...

creating primary keys...

done in 0.43 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.28 s, vacuum 0.07 s, primary keys 0.07 s).

pgbench_accounts table has 100 thousand rows, the other tables have 1, 0 and 10 rows.

4) Run the test by default for 30 seconds with output of intermediate results at 5 second intervals:

postgres@tantor:~$ pgbench -T 30 -P 5

starting vacuum...end.

progress: 5.0 s, 547.2 tps, lat 1.818 ms stddev 0.475, 0 failed

progress: 10.0 s, 551.6 tps, lat 1.805 ms stddev 0.230, 0 failed

progress: 15.0 s, 545.8 tps, lat 1.824 ms stddev 0.255, 0 failed

progress: 20.0 s, 554.2 tps, lat 1.797 ms stddev 0.204, 0 failed

progress: 25.0 s, 532.6 tps, lat 1.870 ms stddev 0.298, 0 failed

progress: 30.0 s, 502.6 tps, lat 1.982 ms stddev 2.024, 0 failed

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 1

query mode: simple

Number of clients: 1

Number of threads: 1

Maximum number of tries: 1

duration: 30 s

number of transactions actually processed: 16171

number of failed transactions: 0 (0.000%)

latency average = 1.847 ms

stddev latency = 0.849 ms

initial connection time = 3.973 ms

tps = 539 .088500 (without initial connection time)

The example shows the result: 539 transactions per second ( tps )

5) Check how the database horizon retention affects the TPS of the standard test. Run the test by specifying a large time interval:

postgres @ tantor :~$ pgbench - T 10000 - P 15

6) Launch the second terminal. In the terminal, run psl and start the transaction:

astra@tantor:~$ psql

postgres=# begin;

BEGIN

postgres =*#

7) In the window with the running test, pay attention to the tps indicator:

progress: 15.0 s, 541.5 tps, lat 1.838 ms stddev 0.242, 0 failed

progress: 30.0 s, 554.7 tps, lat 1.795 ms stddev 0.255, 0 failed

progress: 45.0 s, 545.9 tps, lat 1.824 ms stddev 0.279, 0 failed

8) Open a transaction by calling a function that returns the transaction number:

postgres=*# select pg_current_xact_id();

pg_current_xact_id

--------------------

974640

(1 row)

postgres =*#

9) Check that in the window with the test tps will gradually start to decrease:

progress: 60.0 s, 484.6 tps, lat 2.055 ms stddev 0.603, 0 failed

progress: 75.0 s, 395.7 tps, lat 2.517 ms stddev 0.751, 0 failed

progress: 90.0 s, 349.1 tps, lat 2.853 ms stddev 0.793, 0 failed

progress: 105.0 s, 319.5 tps, lat 3.118 ms stddev 0.912, 0 failed

progress: 120.0 s, 308.1 tps, lat 3.237 ms stddev 0.942, 0 failed

progress: 135.0 s, 339.8 tps, lat 2.935 ms stddev 0.507, 0 failed

progress: 150.0 s, 306.3 tps, lat 3.257 ms stddev 0.478, 0 failed

progress: 165.0 s, 277.6 tps, lat 3.595 ms stddev 0.706, 0 failed

progress: 180.0 s, 264.2 tps, lat 3.777 ms stddev 0.673, 0 failed

In three minutes, tps will decrease by 1.5-2 times. In an hour, tps will decrease by 100 times. We will not wait for an hour, an hour-long test can be launched at lunchtime.

10) Run a query showing the PIDs of active processes and the duration of their transactions:

select age(backend_xmin), extract(epoch from (clock_timestamp()-xact_start)) secs, pid, datname database, state from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc;

age | secs | pid | database | state

--------+------------+--------+----------+---------------------

175455 | 1425.651346 | 255554 | postgres | idle in transaction

1 | 0.001878 | 255547 | postgres | active

      1 | 0.001213 | 255626 | postgres | active

This is a query that allows you to monitor the horizon of databases. The query returns the number of transactions that have occurred since the transaction was started by the server process to which the row belongs; the duration of the transaction, in the first line the longest transaction and the horizon hold time; the pid of the process, the name of the database in which the transaction is running; the state of the transaction. If the state of the transaction is " idle in transaction " this means that the transaction is idle and waiting for a command from the client. An idle open transaction is undesirable and should not exist in a well-written application.

In the example, 1425 seconds have passed and during this time the tps time in the test window has decreased by 7 times:

progress: 1425.0 s, 71.6 tps, lat 13.964 ms stddev 1.215, 0 failed

11) Complete the transaction holding the horizon in the second terminal window:

postgres=*# rollback;

ROLLBACK

postgres=#

12) repeat the request in the third terminal window:

postgres=# select age(backend_xmin), extract(epoch from (clock_timestamp()-xact_start)) secs, pid, datname database, state from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc;

age | secs | pid | database | state

----------+----------+--------+----------+--------

1 | 0.001050 | 255547 | postgres | active

1 | 0.001280 | 255626 | postgres | active

Two transactions This transactions pgbench test . Their "age" in the number of transactions is 1. They do not hold the horizon.

13) Look in the first terminal window with the test that the tps will return to the original quite quickly:

progress: 1905.0 s, 61.3 tps, lat 16.302 ms stddev 1.201, 0 failed

progress: 1920.0 s, 60.4 tps, lat 16.549 ms stddev 1.762, 0 failed

progress: 1935.0 s, 159.5 tps, lat 6.266 ms stddev 6.035, 0 failed

progress: 1950.0 s, 389.1 tps, lat 2.561 ms stddev 0.586, 0 failed

progress: 1965.0 s, 535.5 tps, lat 1.860 ms stddev 0.347, 0 failed

progress: 1980.0 s, 522.1 tps, lat 1.908 ms stddev 0.327, 0 failed

You have observed that holding the horizon in the database significantly reduces the number of transactions that the DBMS can service. A single query holds the horizon for the duration of its execution. Long queries should be moved to replicas.

the default transaction isolation level ( READ COMMITTED ) , a transaction begins to hold a horizon as soon as it is assigned a real transaction number ( xid ). The pg_current_xact_id () function was used to obtain the real number . The real number will be assigned when any command that changes data is executed. For example: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP. Transactions at the repeatable isolation level read hold the horizon from the moment any command is executed, including SELECT, and hold the horizon until the transaction is completed:

postgres=# begin transaction isolation level repeatable read;

BEGIN

postgres =*# select 1;

?column?

----------

1

(1 row)

Part 2. Using pgbench with your own script

1) Create a table for the test by running the following commands in psql:

postgres=# drop table if exists t;

create table t(pk bigserial, c1 text default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

insert into t select *, 'a' from generate_series(1, 100000);

alter table t add constraint pk primary key (pk);

DROP TABLE

CREATE TABLE

INSERT 0 100000

ALTER TABLE

2) Create three scripts (batch files):

postgres=# \q

postgres@tantor:~$ echo "select count(*) from t;" > count1.sql

echo "select count(1) from t;" > count2.sql

echo "select count(pk) from t;" > count3.sql

postgres @ tantor :~$

3) Run three tests in turn with the created scripts:

postgres@tantor:~$ pgbench -T 30 -P 5 -f count 1 .sql

pgbench (16.2)

starting vacuum...end.

progress: 5.0 s, 75.8 tps, lat 13.144 ms stddev 1.410, 0 failed

progress: 10.0 s, 74.2 tps, lat 13.495 ms stddev 1.454, 0 failed

progress: 15.0 s, 75.0 tps, lat 13.304 ms stddev 1.047, 0 failed

progress: 20.0 s, 75.2 tps, lat 13.313 ms stddev 1.267, 0 failed

progress: 25.0 s, 75.6 tps, lat 13.205 ms stddev 1.030, 0 failed

progress: 30.0 s, 76.2 tps, lat 13.125 ms stddev 0.896, 0 failed

transaction type: count1.sql

scaling factor: 1

query mode: simple

Number of clients: 1

Number of threads: 1

Maximum number of tries: 1

duration: 30 s

number of transactions actually processed: 2261

number of failed transactions: 0 (0.000%)

latency average = 13.263 ms

stddev latency = 1.207 ms

initial connection time = 3.788 ms

tps = 75.339419 (without initial connection time)

postgres@tantor:~$ pgbench -T 30 -P 5 -f count 2 .sql

pgbench (16.2)

starting vacuum...end.

progress: 5.0 s, 69.4 tps, lat 14.386 ms stddev 0.891, 0 failed

progress: 10.0 s, 67.8 tps, lat 14.707 ms stddev 1.009, 0 failed

progress: 15.0 s, 68.4 tps, lat 14.633 ms stddev 1.208, 0 failed

progress: 20.0 s, 68.4 tps, lat 14.615 ms stddev 0.909, 0 failed

progress: 25.0 s, 68.4 tps, lat 14.596 ms stddev 1.010, 0 failed

progress: 30.0 s, 67.0 tps, lat 14.940 ms stddev 0.720, 0 failed

transaction type: count2.sql

scaling factor: 1

query mode: simple

Number of clients: 1

Number of threads: 1

Maximum number of tries: 1

duration: 30 s

number of transactions actually processed: 2048

number of failed transactions: 0 (0.000%)

latency average = 14.645 ms

stddev latency = 0.983 ms

initial connection time = 4.314 ms

tps = 68.242454 (without initial connection time)

postgres@tantor:~$ pgbench -T 30 -P 5 -f count 3 .sql

pgbench (16.2)

starting vacuum...end.

progress: 5.0 s, 56.2 tps, lat 17.758 ms stddev 0.983, 0 failed

progress: 10.0 s, 55.2 tps, lat 18.095 ms stddev 0.698, 0 failed

progress: 15.0 s, 55.6 tps, lat 17.968 ms stddev 0.768, 0 failed

progress: 20.0 s, 55.4 tps, lat 18.016 ms stddev 0.765, 0 failed

progress: 25.0 s, 55.8 tps, lat 17.954 ms stddev 0.930, 0 failed

progress: 30.0 s, 55.0 tps, lat 18.125 ms stddev 1.161, 0 failed

transaction type: count3.sql

scaling factor: 1

query mode: simple

Number of clients: 1

Number of threads: 1

Maximum number of tries: 1

duration: 30 s

number of transactions actually processed: 1667

number of failed transactions: 0 (0.000%)

latency average = 17.985 ms

stddev latency = 0.906 ms

initial connection time = 4.129 ms

tps = 55.572129 (without initial connection time)

postgres@tantor:~$

4) Analyze the test result: determine which team works faster

5) Run the commands in psql :

postgres =# \ timing 

Timing is on.

postgres=# select count(pk) from t;

count

--------

100000

(1 row)

Time: 18.249 ms

postgres=# select count(1) from t;

count

--------

100000

(1 row)

Time: 15.223 ms

postgres=# select count(*) from t;

count  

--------

100000

(1 row )

Time : 14.535 ms

Does the execution time of the commands match the pgbench results? Yes, it does.

Does the command execution time match the latency value? Yes, it does.

latency average measured by pgbench with accuracy corresponding to latency stddev .

6) turn off measurement time :

postgres=# \timing

Timing is off.

7) Run the commands:

postgres=# explain analyze select count(pk) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1791.00.. 1791.01 rows=1 width=8) (actual time=817.468.. 817.485 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=8) (actual time=0.013.. 403.349 rows=100000 loops=>

Planning Time: 0.047 ms

Execution Time: 817.602 ms

(4 rows)

postgres=# explain analyze select count(1) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1791.00.. 1791.01 rows=1 width=8) (actual time=800.453.. 800.470 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=0) (actual time=0.015.. 399.821 rows=100000 loops=>

Planning Time: 0.053 ms

Execution Time: 800.589 ms

(4 rows)

postgres=# explain analyze select count(*) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1791.00..1791.01 rows=1 width=8) (actual time=804.634.. 804.650 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=0) (actual time=0.013.. 401.192 rows=100000 loops=>

Planning Time: 0.047 ms

Execution Time: 804.767 ms

(4 rows)

postgres=# explain analyze select count(*) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1791.00.. 1791.01 rows=1 width=8) (actual time=813.472.. 813.489 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=0) (actual time=0.015.. 410.861 rows=100000 loops=>

Planning Time: 0.054 ms

Execution Time: 813.610 ms

(4 rows)

explain commands correct? analyze command execution time without explain analyze ? No, it does not match. Command execution time is ~15 ms , command execution time with explain analyze in this example is ~800 ms or less ~300 ms (depending on the type of time source).

explain analyze with default options showed that count(*) took longer to execute than count (1), which is at odds with previous tests. The overhead of timing significantly exceeds the execution time of the commands.

The last two results of the same command 804.767 ms and 813.610 ms differ due to delays in accessing buffers in the buffer cache 401.192 and 410.861 .

The execution time of the Aggregate node also diverges from the results of pgbench and the execution of commands without explain analyze:

817.485 - 403.349 = 414.136

800.470 - 399.821 = 400.649

804.650 - 401.192 = 403.458

813.489 - 410.861 = 402.628

The difference in the results is that count (*) is slower than count (1).

8) Run the commands, disabling the time measurement in the explain command:

postgres=# explain (analyze, buffers, timing off ) select count(pk) from t;

QUERY PLAN

------------------------------------------------------------------------

Aggregate (cost=1791.00..1791.01 rows=1 width=8) (actual rows=1 loops=1)

Buffers: shared hit=541

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=8) (actual rows=100000 loops=1)

Buffers: shared hit=541

Planning:

Buffers: shared hit=65

Planning Time: 0.431 ms

Execution Time: 21.388 ms

(8 rows)

postgres=# explain (analyze, buffers, timing off ) select count(1) from t;

QUERY PLAN

---------------------------------------------------------------------------

Aggregate (cost=1791.00..1791.01 rows=1 width=8) (actual rows=1 loops=1)

Buffers: shared hit=541

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=0) (actual rows=100000 loops=1)

Buffers: shared hit=541

Planning Time: 0.059 ms

Execution Time: 18.952 ms

(6 rows)

postgres=# explain (analyze, buffers, timing off ) select count(*) from t;

QUERY PLAN

-------------------------------------------------------------------------

Aggregate (cost=1791.00..1791.01 rows=1 width=8) (actual rows=1 loops=1)

Buffers: shared hit=541

-> Seq Scan on t (cost=0.00..1541.00 rows=100000 width=0) (actual rows=100000 loops=1)

Buffers: shared hit=541

Planning:

Buffers: shared hit=3

Planning Time: 0.058 ms

Execution Time: 16.357 ms

(8 rows )

With the timing off option, the results of the explain command match the results of pgbench tests and query executions without explain .

The buffers parameter does not affect the result, it illustrates that all table blocks were in the buffer cache ( shared hit =541 ) when executing all three commands.

explain command has a settings option that will output a string with the values of parameters that:

a ) affect the scheduler

b ) have values other than the default values.

If such parameters are missing, the settings parameter has no effect on the output of the explain command.

The features of time measurement, time source settings, and measuring its speed will be discussed in the following chapters of the course.

What parameters "affect the scheduler"? This is declared when adding a configuration parameter by PostgreSQL developers or extension library creators. Example :

postgres=# select pg_settings_get_flags('work_mem');

pg_settings_get_flags

-----------------------

{EXPLAIN}

(1 row)

If the results contain , it means that the configuration parameter is declared as “affecting the scheduler”. There are about 60 such parameters.

Configuration parameters can have the following flags:

EXPLAI N (approximately 60 parameters) parameters with this flag are included in EXPLAIN (SETTINGS) commands;

NO_SHOW_AL L (there are no parameters with this flag) are excluded from SHOW ALL commands;

NO_RESET (3 parameters) do not support RESET commands;

NO_RESET_AL L (3 parameters) are excluded from RESET ALL commands;

NOT _ IN _ SAMPLE (50 parameters) are not automatically included in the postgresql . conf . sample file from which the initdb utility creates the cluster parameter file

RUNTIME_COMPUTED (5 parameters) parameter values are not set, but are calculated when the instance starts.

The list of parameters with flags can be obtained using the command:

postgres=# select name, pg_settings_get_flags(name) from pg_settings where array_length(pg_settings_get_flags(name),1)>0;

List calculated parameters :

postgres=# select name, context, pg_settings_get_flags(name) from pg_settings where pg_settings_get_flags(name)::text like '%RUNTIME%';

name | context | pg_settings_get_flags

----------------------------------+----------+--------------------------------

data_checksums | internal | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}

data_directory_mode | internal | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}

shared_memory_size | internal | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}

shared_memory_size_in_huge_pages | internal | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}

wal_segment_size | internal | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}

(5 rows )

List of parameters that are not reset to their original value:

postgres=# select name, context, pg_settings_get_flags(name) from pg_settings where pg_settings_get_flags(name)::text like '%RESET%';

name | context | pg_settings_get_flags

------------------------+---------+---------------------------------------

transaction_deferrable | user | {NO_RESET,NO_RESET_ALL,NOT_IN_SAMPLE}

transaction_isolation | user | {NO_RESET,NO_RESET_ALL,NOT_IN_SAMPLE}

transaction_read_only | user | {NO_RESET,NO_RESET_ALL,NOT_IN_SAMPLE}

(3 rows)

9 ) Run the command generated from the plpgsql test : https://gist.github.com/lukaseder/2611212b23ba40d5f828c69b79214a0e/​​​​​​​​​​​​​​​​​ 

used in the article https :// blog . jooq . org / whats - faster - count - or - count 1/ :

DO $$

DECLARE

v_ts TIMESTAMP;

v_repeat CONSTANT INT := 100;

rec RECORD;

run INT[];

stmt INT[];

elapsed DECIMAL[];

min_elapsed DECIMAL;

i INT := 1;

BEGIN

FOR r IN 1..5 LOOP

v_ts := clock_timestamp();

FOR i IN 1..v_repeat LOOP

FOR rec IN (

        select count(*) from t

) LOOP

NULL;

END LOOP;

END LOOP;

run[i] := r;

stmt[i] := 1;

elapsed[i] := (EXTRACT(EPOCH FROM CAST(clock_timestamp() AS TIMESTAMP)) - EXTRACT(EPOCH FROM v_ts));

i := i + 1;

v_ts := clock_timestamp();

FOR i IN 1..v_repeat LOOP

FOR rec IN (

        select count(1) from t

) LOOP

NULL;

END LOOP;

END LOOP;

run[i] := r;

stmt[i] := 2;

elapsed[i] := (EXTRACT(EPOCH FROM CAST(clock_timestamp() AS TIMESTAMP)) - EXTRACT(EPOCH FROM v_ts));

i := i + 1;

v_ts := clock_timestamp();

FOR i IN 1..v_repeat LOOP

FOR rec IN (

        select count(pk) from t

) LOOP

NULL;

END LOOP;

END LOOP;

run[i] := r;

stmt[i] := 3;

elapsed[i] := (EXTRACT(EPOCH FROM CAST(clock_timestamp() AS TIMESTAMP)) - EXTRACT(EPOCH FROM v_ts));

i := i + 1;

END LOOP;

SELECT min(t.elapsed)

INTO min_elapsed

FROM unnest(elapsed) AS t(elapsed);

FOR i IN 1..array_length(run, 1) LOOP

RAISE INFO 'RUN %, Statement %: %', run[i], stmt[i], CAST(elapsed[i] / min_elapsed AS DECIMAL(10, 5));

END LOOP;

END$$;

INFO: RUN 1, Statement 1: 1.00764

INFO: RUN 1, Statement 2: 1.12819

INFO: RUN 1, Statement 3: 1.38278

INFO: RUN 2, Statement 1: 1.01305

INFO: RUN 2, Statement 2: 1.12725

INFO: RUN 2, Statement 3: 1.37496

INFO: RUN 3, Statement 1: 1.00552

INFO: RUN 3, Statement 2: 1.13360

INFO: RUN 3, Statement 3: 1.38406

INFO: RUN 4, Statement 1: 1.00269

INFO: RUN 4, Statement 2: 1.13924

INFO: RUN 4, Statement 3: 1.38148

INFO: RUN 5, Statement 1: 1.00000

INFO: RUN 5, Statement 2: 1.14320

INFO: RUN 5, Statement 3: 1.41113

DO

The result matches the results of the pgbench test.

This was an example of using a plpgsql program to test the execution time of three commands. Using pgbench is simpler and gives more information: the execution time of commands ( latency ) and the standard deviation of execution time.

Part 3. Using the sysbench utility

1) Create the tables that sysbench uses by running the command:

postgres@tantor:~$ sysbench --db-driver=pgsql --pgsql-port=5432 --pgsql-db=postgres --pgsql-user=postgres --pgsql-password=postgres --tables=1 --table_size=100000 /usr/share/sysbench/oltp_read_only.lua prepare

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

One table sbtest1 with 100 thousand rows was created.

Sysbench test files are written in lua . The / usr / share / sysbench / directory contains standard tests. Run a read - only test named oltp_read_only.lua :

postgres@tantor:~$ sysbench --db-driver=pgsql --pgsql-port=5432 --pgsql-db=postgres --pgsql-user=postgres --pgsql-password=postgres --threads=10 --time=15 --report-interval=5 /usr/share/sysbench/oltp_read_only.lua run

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with the following options:

Number of threads: 10

Report intermediate results every 5 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 5s ] thds: 10 tps: 584.03 qps: 9364.62 (r/w/o: 8194.56/0.00/1170.05) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 10 tps: 592.77 qps: 9480.12 (r/w/o: 8294.58/0.00/1185.54) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00

[ 15s ] thds: 10 tps: 477.21 qps: 7632.70 (r/w/o: 6678.48/0.00/954.21) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

read: 115934

write: 0

other: 16562

total: 132496

 transactions:                       8281   (550.22 per sec.)

queries: 132496 (8803.46 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 15.0492s

total number of events:              8281

Latency (ms):

min: 3.70

avg: 18.12

max: 178.98

95th percentile: 30.81

sum: 150092.73

Threads fairness:

events (avg/stddev): 828.1000/63.26

execution time (avg/stddev): 15.0093/0.01

Number of transactions per second 550.22 .

The other indicators are not convenient. The number of transactions 8281 and events : depend on the duration of the test. Other indicators are difficult to analyze. For example, Latency 95th​ percentile more closely matches reality than avg , which is hardly meaningful, since measurements that deviate greatly from the average ( 178.98 ) should have been excluded from the calculation . Delays can be related to the scheduler or operating system activity. In the example, 10 threads were used, the virtual machine has fewer cores and delays are related to the operating system scheduler. The authors of the utility should have excluded measurements taken immediately after starting the utility from the calculation.

Part 4. Using HammerDB

1) Launch terminal as user astra and install HammerDB:

astra @tantor:~$ wget https://github.com/TPC-Council/HammerDB/releases/download/v4.12/HammerDB-4.12-Linux-x64-installer.run

- 'HammerDB-4.12-Linux-x64-installer.run' saved [12665959/12665959]

astra@tantor:~$ chmod +x HammerDB-4.12-Linux-x64-installer.run 

astra@tantor:~$ ./HammerDB-4.12-Linux-x64-installer.run

Install to default directory /home/astra/HammerDB-4.12

After installation, the utility will start. The utility can be started again with the command:

astra@tantor:~$ cd /home/astra/HammerDB-4.12

astra @ tantor :~/ HammerDB -4.12$ ./ hammerdb &

2) After launching the utility, click on PostgreSQL in its window . In the window that appears, " Benchmark Options " leave the values PostgreSQL and TPROC - C . Click OK in the window. In the confirmation window, also click OK.

3) Open the menu PostgreSQL->TPROC-C->Schema and click on Options, go to the Settings tab and set Number of warehouses = 2. The default value is 1. For 10 warehouses, 1GB is required. Set the number of Virtual Users to build schema to twice the number of processor cores minus one, for example 7 for 4 cores. Click OK, the setting will be saved. In real testing, a significantly larger number of warehouses will be required. However, a small number of warehouses will allow simulating contention for locks between sessions.

on Build in the PostgreSQL -> TPROC - C -> Schema menu . A window will appear warning that the tpcc database will be created and tables with data for the TPC-C test will be created in it.

The creation time is several minutes. You can see the load on the processor while the tables are being created using the top command. To show the load on the cores, press 1. To exit the top utility, press the q key.

astra@tantor:~/HammerDB-4.12$ top

In the top utility window , you can see that the load on four cores is close to 100%. USR/SYS ratio: % Cpu 0 89.9us + 7.7 sy .

TPCC message will indicate that the tables have been created. SCHEMA COMPLETE in the Virtual window User 1.

5) Click on the red square on the utility toolbar. Select V menu PostgreSQL->TPROC-C-> Driver Script -> Options. In the window that opens, go to the Settings tab and specify Minutes of Rampup Time 0. This is the "warm-up" time, that is, the gradual increase in the load on the DBMS. We have installed a small number of clients that will not load the DBMS and the delay is not needed. Minutes of Test Duration 5, this is the duration of the test if the test is not stopped earlier.

6) B In the PostgreSQL->TPROC-C-> Virtual User -> Options window , set User Delay(ms)=0, Repeat Delay(ms)=0. For real testing, it is recommended to set the number of users 10 times less than the number of warehouses.

7) Before running the test, you need to start the Transaction Counter to monitor tpm. To do this, click on PostgreSQL -> TPROC - C -> Transactions -> Counter in the menu. The transaction counter will start.

7) Click on PostgreSQL->TPROC-C->Virtual User -> Create and then on PostgreSQL->TPROC-C->Virtual User -> Run

Click on the Transaction Counter tab. A window will appear with a test metric called tpm .

In the example tpm=113604. The tpm metric graph fluctuates, this is not an inaccuracy, but a consequence of the dynamic load corresponding to the test rules. The decrease in tpm by tens of percent occurs due to the retention of the database horizon by autoanalysis. A few minutes after starting the test, tpm can decrease by a third . This occurs due to the launch of autoanalysis on the order_line table and lasting up to two minutes . The tpm reduction is also affected by the execution of the checkpoint.

This is an example where periodic statistics collection is not only useless, but harmful. If the statistics data does not change after recollections, there is no point in "updating" the statistics.

postgres =# \c tpcc

You are now connected to database "tpcc" as user "postgres".

tpcc=# select extract(epoch from (clock_timestamp()-xact_start)) secs, pid, datname database, state, query from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc limit 1 \gx

-[ RECORD 1 ]------------------------------------------

secs | 118 .896428

pid | 296618

database | tpcc

state | active

query | autovacuum: VACUUM ANALYZE public.order_line

In the absence of load, table analysis takes (on a DBMS with default configuration parameters) 27 seconds :

tpcc=# VACUUM (ANALYZE, verbose) public.order_line;

INFO: vacuuming "tpcc.public.order_line"

INFO: finished vacuuming "tpcc.public.order_line": index scans: 1

pages: 0 removed, 186118 remain, 166506 scanned (89.46% of total)

tuples: 446332 removed, 15377120 remain, 0 are dead but not yet removable, oldest xmin: 10827114

removable cutoff: 10827114, which was 0 XIDs old when operation ended

frozen: 79603 pages from table (42.77% of total) had 1825159 tuples frozen

index scan needed: 51544 pages from table (27.69% of total) had 597993 dead item identifiers removed

index "order_line_i1": pages: 81640 in total, 0 newly deleted, 0 currently deleted, 0 reusable

avg read rate: 83.907 MB/s, avg write rate: 48.399 MB/s

buffer usage: 170193 hits, 296131 misses, 170812 dirtied

WAL usage: 384641 records, 164101 full page images, 1262224255 bytes

system usage: CPU: user: 6.17 s, system: 4.05 s, elapsed: 27.57 s

INFO: analyzing "public.order_line"

INFO: "order_line": scanned 30000 of 186118 pages, containing 2492677 live rows and 0 dead rows; 30000 rows in sample, 15464402 estimated total rows

VACUUM

tpm dropped from 101000 to 63588 due to autoanalysis holding the database horizon.

8) You don’t need to wait for the test to stop by time. Stop the test by clicking on the left red square (Destroy Virtual users) on the toolbar. On the right red square ( Stop Transaction Counter ) you don't need to press, otherwise the transaction counter will stop. In the PostgreSQL->TPROC-C-> Virtual User -> Options window , set User Delay(ms)=500, Repeat Delay(ms)=500. These parameters do not affect tpm much , they affect the fluctuations of the tpm graph . Tpm is affected by the number of clients (sessions with the DBMS), called Virtual Users.

Changing parameters in Virtual User -> Options , click on Virtual User -> Run and going to the Transaction Counter tab you can see how tpm changes. The maximum tpm is achieved when the number of clients is equal to the number of processor cores.

9) After testing is complete, close the HammerDB window and delete the tpcc database , which will grow several times after a short period of testing. The test puts rows in the tables and they grow.

After the test is completed, in the Virtual window User 1 will be shown the test result:

IN example result tpms (PostgreSQL TPM)=72606 and NOPM (New Orders per minute)=31648.

Over time, tpcm will decrease with repeated tests. Because of this, the test is not suitable for use in checking how cluster configuration changes will affect performance, since tpm is not only unstable, but also decreases over time. For the stability of the result, you will have to delete the tpcc database and create it again. The test can be used for long-term one-time testing for comparison with other DBMS of the same or different type. When testing, all parameters must be the same, especially the size of the database (number of warehouses), test duration, number of clients.

The TPC-C test is convenient because when it is run, you can analyze the operation of the instance using available tools (extensions, accessing views with statistics) and identify the most effective tools and metrics. This is a consequence of more complex commands than simple pgbench load tests TPC - B .

The HammerDB graphical application is convenient for such purposes.

postgres=# \c tpcc

You are now connected to database "tpcc" as user "postgres".

tpcc=# select pg_size_pretty(pg_database_size('tpcc'));

pg_size_pretty

----------------

 2682 MB

(1 row)

After creating the tables, the database took up 1GB.

tpcc=# vacuum full;

VACUUM

tpcc=# select pg_size_pretty(pg_database_size('tpcc'));

pg_size_pretty

----------------

2331 MB

(1 row)

postgres=# drop database tpcc;

DROP DATABASE

postgres=#

Part 5. Using the Go App - TPC

1) Install go - tpc :

postgres @ tantor :~$ mkdir gotpc

mkdir: cannot create directory 'gotpc': File exists

postgres@tantor:~$ cd gotpc

postgres@tantor:~/gotpc$ wget https://raw.githubusercontent.com/pingcap/go-tpc/master/install.sh

- 'install.sh' saved [2020/2020]

postgres@tantor:~/gotpc$ chmod +x install.sh

postgres@tantor:~/gotpc$ ./install.sh

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0

100 4984k 100 4984k 0 0 4370k 0 0:00:01 0:00:01 --:--:-- 23.2M

Detected shell: bash

Shell profile: /var/lib/postgresql/.bash_profile

/var/lib/postgresql/.bash_profile has been modified to to add go-tpc to PATH

open a new terminal or source /var/lib/postgresql/.bash_profile to use it

Installed path: /var/lib/postgresql/.go-tpc/bin/go-tpc

==============================================================

Have a try: go-tpc tpcc

==============================================================

postgres @ tantor :~/ gotpc $

2) Go to the utility directory and run the utility with the tpcc option prepare , which will create the gotpcc database and will create objects for the TPC-C type test in this database :

postgres@tantor:~/gotpc$ cd $HOME/.go-tpc/bin

postgres@tantor:~/.go-tpc/bin$ ./go-tpc tpcc prepare -d postgres -U postgres -p 'postgres' -D gotpcc -H 127.0.0.1 -P 5432 --conn-params sslmode=disable

maxprocs: Leaving GOMAXPROCS=4: CPU quota undefined

creating table warehouse

creating a district table

creating customer table

creating index idx_customer

creating table history

creating index idx_h_w_id

creating index idx_h_c_w_id

creating table new_order

creating table orders

creating index idx_order

creating table order_line

creating stock table

creating table item

load to item

load to warehouse in warehouse 1

Load stock in warehouse 1

load to district in warehouse 1

load to warehouse in warehouse 2

Loading stock in warehouse 2

load to district in warehouse 2

load to warehouse in warehouse 3

Load stock in warehouse 3

load to district in warehouse 3

load to warehouse in warehouse 4

Loading stock in warehouse 4

load to district in warehouse 4

load to warehouse in warehouse 5

Load stock in warehouse 5

load to district in warehouse 5

load to warehouse in warehouse 6

Load stock in warehouse 6

load to district in warehouse 6

load to warehouse in warehouse 7

Loading stock in warehouse 7

load to district in warehouse 7

load to warehouse in warehouse 8

Load stock in warehouse 8

load to district in warehouse 8

load to warehouse in warehouse 9

Load stock in warehouse 9

load to district in warehouse 9

load to warehouse in warehouse 10

Load stock in warehouse 10

load to district in warehouse 10

load to customer in warehouse 1 district 1

load to history in warehouse 1 district 1

..

For each test type of this utility, it is better to create tables in a separate database, since some tables have overlapping names. There are no local connections in the utility driver, the connection must be via a network interface, so all connection parameters are specified .

By default, the utility creates 10 warehouses. They are created within 4 minutes. The number of warehouses can be changed by specifying the parameter --warehouses 4 .

3) After about 3-4 minutes you will see the lines " begin to check warehouse 1 at condition 3.3.2.4 " in the utility log. The utility has started checking the data after loading. The check takes a long time and is pointless. It is needed for those DBMS that lose data, PostgreSQL does not lose data. Click on keyboard < ctrl+c > to interrupt check :

load to new_order in warehouse 10 district 10

load to order_line in warehouse 10 district 10

begin to check warehouse 1 at condition 3.3.2.4

begin to check warehouse 1 at condition 3.3.2.5

begin to check warehouse 1 at condition 3.3.2.6

begin to check warehouse 1 at condition 3.3.2.7

begin to check warehouse 1 at condition 3.3.2.8

begin to check warehouse 1 at condition 3.3.2.9

begin to check warehouse 1 at condition 3.3.2.10

begin to check warehouse 1 at condition 3.3.2.10

^C

Got signal [interrupt] to exit.

check prepare failed, err check warehouse 1 at condition 3.3.2.10 failed exec SELECT count(*)

FROM ( SELECT c.c_id, c.c_d_id, c.c_w_id, c.c_balance c1,

(SELECT sum(ol_amount) FROM orders, order_line

WHERE OL_W_ID=O_W_ID

AND OL_D_ID = O_D_ID

AND OL_O_ID = O_ID

AND OL_DELIVERY_D IS NOT NULL

AND O_W_ID=?

AND O_D_ID=c.C_D_ID

AND O_C_ID=c.C_ID) sm, (SELECT sum(h_amount) from history

WHERE H_C_W_ID=?

AND H_C_D_ID=c.C_D_ID

AND H_C_ID=c.C_ID) smh

FROM customer c

WHERE c.c_w_id = ? ) t

WHERE c1<>sm-smh failed pq: canceling statement due to user request

Finished

The size of the database created by the utility is 1GB :

postgres=# select pg_size_pretty(pg_database_size('gotpcc'));

pg_size_pretty

----------------

 1028 MB

(1 row)

4) Launch tpcc test :

postgres@tantor:~/.go-tpc/bin$ ./go-tpc tpcc run -d postgres -U postgres -p 'postgres' -D gotpcc -H 127.0.0.1 -P 5432 --conn-params sslmode=disable 
maxprocs: Leaving GOMAXPROCS=4: CPU quota undefined
[Current] DELIVERY - Takes(s): 9.9, Count: 61, TPM: 369.4, Sum(ms): 879.3, Avg(ms): 14.4, 50th(ms): 14.2, 90th(ms): 16.8, 95th(ms): 17.8, 99th(ms): 21.0,
99.9th(ms): 27.3, Max(ms): 27.3 [Current] NEW_ORDER - Takes(s): 9.9, Count: 714, TPM: 4310.5, Sum(ms): 5931.1, Avg(ms): 8.3, 50th(ms): 8.4, 90th(ms): 11.0, 95th(ms): 12.6, 99th(ms): 17.8, 99.9th(ms): 26.2, Max(ms): 29.4 [Current] ORDER_STATUS - Takes(s): 9.9, Count: 61, TPM: 369.7, Sum(ms): 104.9, Avg(ms): 1.7, 50th(ms): 2.1, 90th(ms): 2.1, 95th(ms): 2.6, 99th(ms): 3.1, 99.9th(ms): 5.2, Max(ms): 5.2 [Current] PAYMENT - Takes(s): 10.0, Count: 697, TPM: 4197.6, Sum(ms): 2448.8, Avg(ms): 3.5, 50th(ms): 3.7, 90th(ms): 4.2, 95th(ms): 4.7, 99th(ms): 7.9, 99.9th(ms): 8.4, Max(ms): 13.1 [Current] STOCK_LEVEL - Takes(s): 9.9, Count: 65, TPM: 395.9, Sum(ms): 277.1, Avg(ms): 4.3, 50th(ms): 3.7, 90th(ms): 4.7, 95th(ms): 5.8, 99th(ms): 24.1, 99.9th(ms): 41.9, Max(ms): 41.9

Current TPM statistics will be displayed on the console.

5) After about a minute, press < ctrl + c > on the keyboard to interrupt the test. The utility will display statistics with the tpmC result :

^ C

Got signal [ interrupt ] to exit .

Finished

[Summary] DELIVERY - Takes(s): 182.8, Count: 1329, TPM: 436.3, Sum(ms): 17163.3, Avg(ms): 12.9, 50th(ms): 12.6, 90th(ms): 14.7, 95th(ms): 16.8, 99th(ms): 22.0, 99.9th(ms): 29.4, Max(ms): 30.4

[Summary] NEW_ORDER - Takes(s): 182.8, Count: 14194, TPM: 4659.0, Sum(ms): 106451.6, Avg(ms): 7.5, 50th(ms): 7.3, 90th(ms): 9.4, 95th(ms): 10.5, 99th(ms): 15.7, 99.9th(ms): 22.0, Max(ms): 41.9

[Summary] ORDER_STATUS - Takes(s): 182.8, Count: 1288, TPM: 422.9, Sum(ms): 2306.0, Avg(ms): 1.8, 50th(ms): 2.1, 90th(ms): 2.6, 95th(ms): 2.6, 99th(ms): 4.2, 99.9th(ms): 8.9, Max(ms): 10.0

[Summary] PAYMENT - Takes(s): 182.8, Count: 13431, TPM: 4407.9, Sum(ms): 46553.2, Avg(ms): 3.5, 50th(ms): 3.7, 90th(ms): 4.2, 95th(ms): 4.7, 99th(ms): 7.9, 99.9th(ms): 14.2, Max(ms): 19.9

[Summary] STOCK_LEVEL - Takes(s): 182.7, Count: 1262, TPM: 414.4, Sum(ms): 3707.7, Avg(ms): 2.9, 50th(ms): 3.1, 90th(ms): 3.7, 95th(ms): 4.2, 99th(ms): 8.9, 99.9th(ms): 24.1, Max(ms): 41.9

tpmC: 4659.0 , tpmTotal: 10340.5, efficiency: 3622.8%

6) Delete the database created by the utility:

postgres@tantor:~/.go-tpc/bin$ psql - with "drop database gotpcc;"

DROP DATABASE

7) Unlike HammerDB, the go-tpc utility does not work at full speed, but follows the TPC-C test rules, working with delays and gradually increasing the load. The utility has two more tests: TPC-H and the tpc utility's own test - ch (CH-benCHmark mixed load from TPC-C and TPC-H tests). The test is suitable for DBMSs that are used for general purposes with mixed load. PostgreSQL is rarely used with mixed load, since physical replicas are used for long (analytical) queries.

The TPC-H benchmark is for data warehouses and consists of read commands only. The TPC-H benchmark result is more stable, allowing for comparison. The downside is that the size of the official benchmark databases is quite large.

Database creation takes approximately 10 minutes. Database size is 1660MB.

The following steps of this practice are optional. The commands and their results are provided for reference only. You can use these commands as a reference or if you want to consolidate your skills in using the utility.

postgres@tantor:~/.go-tpc/bin$ ./go-tpc tpch prepare -d postgres -U postgres -p 'postgres' -D gotpch -H 127.0.0.1 -P 5432 --conn-params sslmode=disable

maxprocs: Leaving GOMAXPROCS=4: CPU quota undefined

creating a nation

creating region

creating part

creating supplier

creating parts supp

creating a customer

creating orders

creating lineitem

generating nation table

generate nation table done

generating region table

generate region table done

generating customers table

generate customers table done

generating suppliers table

generate suppliers table done

generating part/partsupplier tables

generate part/partsupplier tables done

generating orders/lineitem tables

generate orders/lineitem tables done

Finished

Launch TPC-H test :

postgres@tantor:~/.go-tpc/bin$ ./go-tpc tpch run -d postgres -U postgres -p 'postgres' -D gotpch -H 127.0.0.1 -P 5432 --conn-params sslmode=disable

maxprocs: Leaving GOMAXPROCS=4: CPU quota undefined

[Current] Q1: 6.21s

[Current] Q2: 0.91s

[Current] Q3: 1.31s

[Current] Q4: 0.44s

[Current] Q5: 0.44s

[Current] Q10: 1.24s

[Current] Q11: 0.30s

[Current] Q12: 1.17s

[Current] Q13: 1.04s

[Current] Q14: 1.17s

[Current] Q6: 0.77s

[Current] Q7: 0.64s

[Current] Q8: 0.97s

[Current] Q9: 3.05s

[Current] Q15: 0.84s

[Current] Q16: 0.57s

[Current] Q17: 1199.91s

[Current] Q18: 5.27s

[Current] Q19: 1.04s

The Q17 query in the test is long, it takes 3 hours ( 11999.91 s ). The utility shows the maximum value of 1199.91s for long queries, there is a software error in the utility.

Example monitoring query Q17:

postgres=# select extract(epoch from (clock_timestamp()-xact_start)) secs , pid, datname database, state, query from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc limit 1 \gx

-[ RECORD 1 ]----------------------------------------------

secs | 6434 .701066

pid | 316066

database | gotpch

state | active

query | +

| /*PLACEHOLDER*/ select +

| sum(l_extendedprice) / 7.0 as avg_yearly+

| from +

| lineitem, +

| part +

| where +

| p_partkey = l_partkey +

| and p_brand = 'Brand#43' +

| and p_container = 'LG PACK' +

| and l_quantity < ( +

| select +

| 0.2 * avg(l_quantity) +

| from +

| lineitem +

| where +

| l_partkey = p_partkey +

         | ); +

|

The query contains a correlated subquery. Correlated subqueries are heavy for all DBMS. For example , on specialized under OLAP DBMS clickhouse queries TPC-H Q17 and Q20 tests are not work "As of October 2024, the query does not work out-of-the box due to correlated subqueries . Corresponding issue: https://github.com/ClickHouse/ClickHouse/issues/6697" ( https://clickhouse.com/docs/en/getting-started/example-datasets/tpch ).

Query Q20 runs on PostgreSQL with default parameters for 7.6 hours, query Q17 runs for about 4 hours . Long queries are interesting for query execution optimization: finding out the reasons for long execution . It is worth moving on to query optimization after setting up the PostgreSQL instance. Plan execution this request :

postgres=# \c gotpch

You are now connected to database "gotpch" as user "postgres".

gotpch=# explain select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#43' and p_container = 'LG PACK' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey);

QUERY PLAN

-----------------------------------------------------------------------

Aggregate (cost=2204613.84..2204613.86 rows=1 width=32)

-> Hash Join (cost=6625.16..2204608.92 rows=1970 width=8)

Hash Cond: (lineitem.l_partkey = part.p_partkey)

Join Filter: (lineitem.l_quantity < ( SubPlan 1 ))

-> Seq Scan on lineitem (cost=0.00..184745.33 rows=6001233 width=21)

-> Hash (cost=6622.70..6622.70 rows=197 width=8)

-> Gather (cost=1000.00..6622.70 rows=197 width=8)

Workers Planned: 2

-> Parallel Seq Scan on part (cost=0.00..5603.00 rows=82 width=8)

Filter:((p_brand = 'Brand#43'::bpchar) AND (p_container = 'LG PACK'::bpchar))

      SubPlan 1

-> Aggregate (cost=199748.49..199748.51 rows=1 width=32)

-> Seq Scan on lineitem lineitem_1 (cost=0.00..199748.41 rows=31 width=5)

Filter: (l_partkey = part.p_partkey )

JIT:

Functions: 24

Options: Inlining true, Optimization true, Expressions true, Deforming true

(17 rows )

There are 6 million and 200 thousand rows in the tables, which is not so much:

gotpch=# select count(*) from lineitem;

count

---------

6001215

(1 row)

gotpch=# select count(*) from part;

count  

--------

200000

(1 row )

A long query needs to be interrupted by typing < ctrl + c > on the keyboard and then deleting the gotpch database :

postgres@tantor:~/.go-tpc/bin$ psql - with "drop database gotpch;"

The go-tpc application contains three tests. To create a database for the third test (mixed), the " ch " parameter is used instead of " tpcc " and " tpch ":

go-tpc ch prepare -d postgres -U postgres -p 'postgres' -D gotpcch -H 127.0.0.1 -P 5432 --conn-params sslmode=disable

Practice for Chapter 2

Part 1. Launching a Huge Pages Instance

1) Find the list of PostgreSQL instances:

postgres @ tantor :~$ ps - ef | grep / postgres

postgres 1423 1 0 Nov27 ? 00:00:06 /opt/tantor/db/16/bin/postgres -D /var/lib/postgresql/tantor-se-16/data

postgres 1432 1 0 Nov27 ? 00:00:03 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf

In the example there are two instances: Tantor DBMS and PostgreSQL Astralinux .

2) See how much memory the PostgreSQL instance is using and the size of huge pages:

postgres@tantor:~$ cat /proc/1432/status | grep VmPeak

VmPeak: 222268 kB

postgres@tantor:~$ cat /proc/meminfo | grep Huge

AnonHugePages: 2048 kB

ShmemHugePages: 0 kB

FileHugePages: 0 kB

HugePages_Total: 0

HugePages_Free: 0

HugePages_Rsvd: 0

HugePages_Surp: 0

Hugepagesize: 2048 kB

Hugetlb: 0 kB

Maximum memory consumption per instance: 222268 kB / 2048 kB = 109 pages of 2 MB ( 2048 ). Huge pages can be used by the shared pool (shared_buffers parameter ) and parallel processes ( min_dynamic_shared_memory parameter ) . Forks can also be used for other memory structures. The peak memory consumption on a loaded instance shows the memory usage of all structures.

- zero value in the AnonHugePages : line indicates that some process has explicitly requested the use of THP via the madvise () system call . PostgreSQL does not use such a system call.

Run the free command:

postgres @ tantor :~$ free

               total used free shared buff/cache available

Mem : 4020796 1137792 764984 158692 2556828 2883004

Swap : 0 0 0

3) Stop the instance and check how many hugepages it can allocate maximum according to its configuration parameters:

postgres@tantor:~$ sudo systemctl stop postgresql

postgres@tantor:~$ /usr/lib/postgresql/15/bin/postgres -c config_file=/etc/postgresql/15/main/postgresql.conf -D /var/lib/postgresql/15/main -C shared_memory_size_in_huge_pages

70

4) Allocate memory for 100 pages and check that they are allocated:

postgres@tantor:~$ sudo sysctl -w vm.nr_hugepages=100

vm.nr_hugepages = 100

postgres@tantor:~$ cat /proc/meminfo | grep Huge

AnonHugePages: 0 kB

ShmemHugePages: 0 kB

FileHugePages: 0 kB

HugePages_Total: 100

HugePages_Free: 27

HugePages_Rsvd: 1

HugePages_Surp: 0

Hugepagesize: 2048 kB

Hugetlb: 204800 kB

The value HugePages_Free : = 27 indicates that there are not enough free pages for a shared pool of 128MB. If you have more than 70 pages , then this amount is enough. If there are less than 80, then allocate memory for 300 pages and check that there are more than 80 free pages :

 postgres@tantor:~$ sudo sysctl -w vm.nr_hugepages=300

vm.nr_hugepages = 300

postgres@tantor:~$ cat /proc/meminfo | grep Huge

AnonHugePages: 0 kB

ShmemHugePages: 0 kB

FileHugePages: 0 kB

HugePages_Total: 218

HugePages_Free: 145

HugePages_Rsvd: 1

HugePages_Surp: 0

Hugepagesize: 2048 kB

Hugetlb: 446464 kB

5) Run the free command:

postgres @ tantor :~$ free

               total used free shared buff/cache available

Mem : 4020796 1331220 571396 158696 2556992 2689576

Swap : 0 0 0

The output of the free command shows the following: used increased by 193,428 bytes, free decreased by 193,588 bytes, available decreased by 193,428 bytes.

6) Restart postgresql service :

postgres@tantor:~$ sudo systemctl restart postgresql

postgres@tantor:~$ cat /proc/meminfo | grep Huge

AnonHugePages: 0 kB

ShmemHugePages: 0 kB

FileHugePages: 0 kB

HugePages_Total: 218

HugePages_Free: 126

HugePages_Rsvd: 73

HugePages_Surp: 0

Hugepagesize: 2048 kB

Hugetlb: 446464 kB

7) Get a list of processes that are using 2MB huge pages:

postgres@tantor:~$ sudo grep "KernelPageSize: 2048 kB" /proc/[[:digit:]]*/smaps | awk {'print $1'} | cut -d "/" -f3 | sort | uniq

112647

112649

112650

112652

112653

112654

8) Compare with the process numbers ( PID ) of the Astralinux instance PostgreSQL :

postgres@tantor:~$ ps -ef | grep 15/

postgres 112647 1 0 17:55 ? 00:00:00 /usr/lib/postgresql/15/bin/ postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf

postgres 112648 112647 0 17:55 ? 00:00:00 postgres: 15/main: logger

postgres 112649 112647 0 17:55 ? 00:00:00 postgres: 15/main: checkpointer

postgres 112650 112647 0 17:55 ? 00:00:00 postgres: 15/main: background writer

postgres 112652 112647 0 17:55 ? 00:00:00 postgres: 15/main: walwriter

postgres 112653 112647 0 17:55 ? 00:00:00 postgres: 15/main: autovacuum launcher

postgres 112654 112647 0 17:55 ? 00:00:00 postgres: 15/main: logical replication launcher

Numbers processes match . Instance uses huge pages .

The fact that the instance has started using huge pages will also be indicated by an increase in the HugePages _ Rsvd value or a decrease in HugePages _ Free . Due to the inconvenience of checking by these operating system metrics whether the pages are reserved by the instance or are already in use by the instance or other operating system processes, in version 17 of PostgreSQL the huge_pages_status parameter was added , which shows whether huge pages are used by the instance or not.

the huge_pages = on parameter . With this parameter value, the instance will not start unless it allocates huge pages.

Huge pages are reserved for use by parallel processes only at instance startup and only by the min_dynamic_shared_memory parameter . If huge pages are not reserved or there are not enough of them, parallel processes will allocate and use regular pages.

9) See the size and type of huge pages that the postgres process is using :

postgres@tantor:~$ cat /proc/ 112647 /smaps_rollup | grep tlb

Shared_Hugetlb: 12288 kB

Private_Hugetlb: 0 kB

postgres process uses shared huge pages.

Part 2. Changing the oom_score value 

1) Run the command:

postgres@tantor:~$ for PID in $(pgrep "postgres"); do awk '/Pss/ {PSS+=$2} END { getline cmd < "/proc/'$PID'/cmdline"; sub("\0", " ", cmd); getline oom < "/proc/'$PID'/oom_score"; printf "%.0f -%s-> %s (PID %s) \n", PSS, oom, cmd, '$PID'}' /proc/$PID/smaps; done|sort -n -r

28579 - 0 -> /opt/tantor/db/16/bin/postgres -D/var/lib/postgresql/tantor-se-16/data (PID 112360)

5629 -668->postgres: walwriter (PID 112364)

5166 - 70 -> /usr/lib/postgresql/15/bin/postgres -D/var/lib/postgresql/15/main-cconfig_file=/etc/postgresql/15/main/postgresql.conf (PID 112647)

3207 -668->postgres: autovacuum launcher (PID 112365)

2945 -668->postgres: 15/main: autovacuum launcher (PID 112653)

2933 -668-> postgres: autoprewarm leader (PID 112366)

2685 -668-> postgres: 15/main: logical replication launcher (PID 112654)

2644 -668->postgres: checkpointer (PID 112361)

2618 -668-> postgres: logical replication launcher (PID 112368)

2366 -668-> postgres: pg_wait_sampling collector (PID 112367)

2157 -668->postgres: background writer (PID 112362)

1497 -667->postgres: 15/main: logger (PID 112648)

1479 -667->postgres: 15/main: checkpointer (PID 112649)

1468 -667->postgres: 15/main: walwriter (PID 112652)

1438 -667->postgres: 15/main: background writer (PID 112650)

command shows the value of oom_score PostgreSQ L instance processes .

The main process of the Tantor DBMS instance has oom_score = 0 . Astralinux PostgreSQL instance oom_score= 70 .

The oom_score reduction was done by editing the service file. See content file :

postgres@tantor:~$ cat /usr/lib/systemd/system/tantor-se-server-16.service

[Unit]

Description=Tantor Special Edition database server 16

Documentation=https://www.postgresql.org/docs/16/static/

After=syslog.target

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

LimitNOFILE=infinity

LimitNOFILESoft=infinity

# Where to send early-startup messages from the server (before the logging options of postgresql.conf take effect)

# This is normally controlled by the global default set by systemd

# StandardOutput=syslog

# Disable OOM kill on the postmaster

OOMScoreAdjust=-1000

# ... but allow it still to be effective for child processes

# (note that these settings are ignored by Postgres releases before 9.5)

Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj

Environment=PG_OOM_ADJUST_VALUE=0

# Maximum number of seconds pg_ctl will wait for postgres to start. Note that PGSTARTTIMEOUT should be less than

# TimeoutSec value.

Environment=PGSTARTTIMEOUT=270

Environment=PGDATA=/var/lib/postgresql/tantor-se-16/data

ExecStartPre=/opt/tantor/db/16/bin/postgresql-check-db-dir ${PGDATA}

ExecStart=/opt/tantor/db/16/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT}

ExecStop=/opt/tantor/db/16/bin/pg_ctl stop -D ${PGDATA} -s -m fast

ExecReload=/opt/tantor/db/16/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down. Ideally, the timeout for starting

# PostgreSQL server should be handled more nicely by pg_ctl in ExecStart, so keep its timeout smaller than this

# value.

TimeoutSec=300

[Install]

WantedBy=multi-user.target

The file says that OOM kill is disabled for the postmaster process, but works for other instance processes. If you need to reduce the oom_score value , you can change the PG_OOM_ADJUST_VALUE parameter value in this file .

3) This point is optional, you can skip it. Reduce oom_score by 200. To do this, edit the service file and set the value PG _ OOM _ ADJUST _ VALUE = -300 :

postgres@tantor:~$ su -

Password: root 

root@tantor:~# cd /usr/lib/systemd/system/

root@tantor:~# mcedit tantor-se-server-16.service

edit and then save the file with F 2 ENTER close the editor with F 10

root@tantor:/usr/lib/systemd/system# cat tantor-se-server-16.service | grep OOM

# Disable OOM kill on the postmaster

OOMScoreAdjust=-1000

Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj

Environment= PG_OOM_ADJUST_VALUE=-300

root@tantor:/usr/lib/systemd/system# systemctl daemon-reload

root@tantor:/usr/lib/systemd/system# restart

root@tantor:/usr/lib/systemd/system# for PID in $(pgrep "postgres"); do awk '/Pss/ {PSS+=$2} END { getline cmd < "/proc/'$PID'/cmdline"; sub("\0", " ", cmd); getline oom < "/proc/'$PID'/oom_score"; printf "%.0f -%s-> %s (PID %s) \n", PSS, oom, cmd, '$PID'}' /proc/$PID/smaps; done|sort -n -r

33392 -0-> /opt/tantor/db/16/bin/postgres -D/var/lib/postgresql/tantor-se-16/data (PID 119419)

4993 -70-> /usr/lib/postgresql/15/bin/postgres -D/var/lib/postgresql/15/main-cconfig_file=/etc/postgresql/15/main/postgresql.conf (PID 112647)

3232 - 468 -> postgres: autovacuum launcher (PID 119424)

2865 -668->postgres: 15/main: autovacuum launcher (PID 112653)

2770 - 468 -> postgres: logical replication launcher (PID 119427)

2648 -668-> postgres: 15/main: logical replication launcher (PID 112654)

2533 - 468 -> postgres: background writer (PID 119421)

2523 - 468 -> postgres: pg_wait_sampling collector (PID 119426)

2019 -667->postgres: 15/main: checkpointer (PID 112649)

1777 - 468 -> postgres: autoprewarm leader (PID 119425)

1562 - 468 -> postgres: checkpointer (PID 119420)

1517 -667->postgres: 15/main: walwriter (PID 112652)

1480 -667->postgres: 15/main: background writer (PID 112650)

1475 -667->postgres: 15/main: logger (PID 112648)

1463 - 468 -> postgres: walwriter (PID 119423)

All processes of the tantor instance oom_score decreased by 200. After editing the service file, the configuration was reread using the systemctl command daemon - reload and restarted the instance.

4) Check that ksm is disabled:

postgres @ tantor :~$ cat / proc / vmstat | grep ksm

ksm_swpin_copy 0

cow_ksm 0

postgres@tantor:~$ cat /sys/kernel/mm/ksm/run

0

postgres@tantor:~$ cat /sys/kernel/mm/ksm/pages_scanned

0

This check is done just in case. ksm is useful and can be enabled if the operating system runs virtual machines from a single virtual machine image file.

5) Check the values of the parameters that affect the allocation of memory larger than the existing one:

postgres@tantor:~$ sudo sysctl -a | grep vm.over

vm.overcommit_kbytes = 0

vm.overcommit_memory = 0

vm.overcommit_ratio = 50

postgres@tantor:~$ sudo sysctl -a | grep vm.swap

vm.swappiness = 60

Since the swap partition is disabled in the virtual machine, vm . overcommit _ memory must be zero. Since swapping is disabled, the value of the vm . swappiness parameter doesn't matter.

Part 3. Unloading long lines with the pg_dump utility

1) Create a database named ela and a table with the following commands:

postgres@tantor:~$ psql

postgres=# create database ela;

CREATE DATABASE

postgres=# \c ela

You are now connected to database "ela" as user "postgres".NOTICE: table "t2" does not exist, skipping

postgres=# drop table if exists t2;

create table t2(c1 text, c2 text);

insert into t2 (c1) VALUES (repeat('a', 1024*1024*512));

update t2 set c2 = c1;

\q

DROP TABLE

CREATE TABLE

INSERT 0 1

UPDATE 1

2) Stop the postgresql instance

postgres@tantor:~$ sudo systemctl stop postgresql

3) Set the number of huge pages to 0:

postgres@tantor:~$ sudo sysctl -w vm.nr_hugepages=0

vm.nr_hugepages = 0

4) Try it unload base data ela utility pg_dump :

postgres@tantor:~$ pg_dump -c -C -d ela -f ela.sql 

pg_dump: error: Dumping the contents of table "t2" failed: PQgetResult() failed.

pg_dump: detail: Error message from server: ERROR: out of memory

DETAIL: Cannot enlarge string buffer containing 536870913 bytes by 536870912 more bytes.

pg_dump: detail: Command was: COPY public.t2 (c1, c2) TO stdout;

An error occurred that the contents of the table could not be unloaded due to the memory size for the string being exceeded. buffer . The maximum buffer size is 1GB.

5) Starting with version 16.2, the Tantor DBMS has a parameter enable_large_allocations , which increases the size of the string buffer to 2GB. The parameter can also be set in pg_dump utility sessions using the utility parameter. Dump the ela database using this parameter:

postgres @ tantor :~$ time pg_dump - c - d​​ ela - f ela . sql -- enable - large - allocations

real 0m12.556s

user 0m1.614s

sys 0m0.699s

ela database download took 12.556 seconds.

6) A file of 1GB size was created:

postgres@tantor:~$ ls -al ela.sql 

-rw-r--r-- 1 postgres postgres 1073742755 ela.sql

Part 4. Out of Memory

1) In the terminal window, check how much memory is free:

postgres@tantor:~$ free

total used free shared buff/cache available

Mem: 4020792 993520 2888632 185788 546988 3027272

Swap: 0 0 0

Free 2.8GB , available 3GB

2) Run the psql utility:

postgres @ tantor :~$ psql - d ela

Type "help" for help.

ela=# \c ela

3) In another terminal, look at the list of PostgreSQL instance processes:

root@tantor:~$ for PID in $(pgrep "postgres"); do awk '/Pss/ {PSS+=$2} END { getline cmd < "/proc/'$PID'/cmdline"; sub("\0", " ", cmd); getline oom < "/proc/'$PID'/oom_score"; printf "%.0f -%s-> %s (PID %s) \n", PSS, oom, cmd, '$PID'}' /proc/$PID/smaps; done|sort -n -r

3039132 -922->postgres: postgres ela [local] COPY (PID 12518)

28619 -0-> /opt/tantor/db/16/bin/postgres -D /var/lib/postgresql/tantor-se-16/data (PID 12499)

5502 -668->postgres: walwriter (PID 12503)

3031 -668->postgres: autovacuum launcher (PID 12504)

2753 -668->postgres: checkpointer (PID 12500)

2507 -668-> postgres: logical replication launcher (PID 12507)

2499 -668-> postgres: pg_wait_sampling collector (PID 12506)

2069 -668->postgres: background writer (PID 12501)

1697 -668-> postgres: autoprewarm leader (PID 12505)

The first line contains the number of the server process connected to the ela database .

3) In the psql window , execute the script obtained by the pg_dump utility :

ela=# \i ela.sql

SET

CREATE TABLE

ALTER TABLE

psql:ela.sql:44: server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

psql:ela.sql:44: error: connection to server was lost

The connection was lost.

4) Look at the latest messages from the Linux kernel:

postgres@tantor:~$ sudo dmesg | tail -n 120

[16387.760734] Mem-Info:

[16387.760737] active_anon:342614 inactive_anon:609810 isolated_anon:0

active_file:22 inactive_file:71 isolated_file:0

unevictable:0 dirty:8 writeback:0

slab_reclaimable:5664 slab_unreclaimable:13043

mapped:15337 shmem:46435 pagetables:5001

sec_pagetables:0 bounce:0

kernel_misc_reclaimable:0

free:20934 free_pcp:1 free_cma:0

[16387.760743] Node 0 active_anon:1370456kB inactive_anon:2439240kB active_file:88kB inactive_file:284kB unevictable:0kB isolated(anon):0kB isolated(file):0kB mapped:61348kB dirty:32kB writeback:0kB shmem:185740kB shmem_thp:0kB shmem_pmdmapped:0kB anon_thp:0kB writeback_tmp:0kB kernel_stack:5696kB pagetables:20004kB sec_pagetables:0kB all_unreclaimable? no

[16387.760749] Node 0 DMA free:14848kB boost:0kB min:260kB low:324kB high:388kB reserved_highatomic:0KB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB writepending:0kB present:15992kB managed:15360kB mlocked:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB

[16387.760756] lowmem_reserve[]: 0 3385 3825 3825 3825

[16387.760770] Node 0 DMA32 free:61272kB boost:0kB min:59580kB low:74472kB high:89364kB reserved_highatomic:0KB active_anon:1077032kB inactive_anon:2365224kB active_file:0kB inactive_file:0kB unevictable:0kB writepending:32kB present:3653568kB managed:3555048kB mlocked:0kB bounce:0kB free_pcp:4kB local_pcp:0kB free_cma:0kB

[16387.760778] lowmem_reserve[]: 0 0 439 439 439

[16387.760791] Node 0 Normal free:7616kB boost:0kB min:7736kB low:9668kB high:11600kB reserved_highatomic:0KB active_anon:105944kB inactive_anon:261496kB active_file:416kB inactive_file:0kB unevictable:0kB writepending:0kB present:524288kB managed:450384kB mlocked:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB

[16387.760798] lowmem_reserve[]: 0 0 0 0 0

[16387.760811] Node 0 DMA: 0*4kB 0*8kB 0*16kB 0*32kB 0*64kB 0*128kB 0*256kB 1*512kB (U) 0*1024kB 1*2048kB (M) 3*4096kB (M) = 14848kB

[16387.760846] Node 0 DMA32: 849*4kB (UME) 615*8kB (UME) 229*16kB (UME) 278*32kB (UME) 177*64kB (UE) 67*128kB (UME) 34*256kB (UME) 15*512kB (UME) 1*1024kB (U) 0*2048kB 1*4096kB (M) = 62284kB

[16387.760893] Node 0 Normal: 230*4kB (UME) 153*8kB (UME) 107*16kB (UME) 71*32kB (UME) 25*64kB (UME) 5*128kB (M) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 8368kB

[16387.760934] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB

[16387.760937] 46530 total pagecache pages

[16387.760939] 0 pages in swap cache

[16387.760941] Free swap = 0kB

[16387.760943] Total swap = 0kB

[16387.760946] 1048462 pages RAM

[16387.760948] 0 pages HighMem/MovableOnly

[16387.760950] 43264 pages reserved

[16387.760952] 0 pages hwpoisoned

[16387.760954] Tasks state (memory values in pages ):

[16387.760956] [ pid ] uid tgid total_vm      rss pgtables_bytes swapents oom_score_adj name

[16387.760961] [270] 0 270 13053 288 118784 0 -250 systemd-journal

[16387.760966] [299] 0 299 8116 608 86016 0 -1000 systemd-udevd

[16387.760971] [ 426] 0 426 3359 66 49152 0 -1000 auditd

[16387.760975] [451] 0 451 59920 269 102400 0 0 accounts-daemon

[16387.760978] [452] 0 452 658 32 45056 0 0 acpid

[16387.760982] [455] 109 455 2246 96 61440 0 0 avahi-daemon

[16387.760986] [456] 0 456 1814 64 53248 0 0 cron

[16387.760989] [457] 100 457 2605 448 61440 0 -900 dbus-daemon

[16387.760993] [461] 109 461 2202 98 57344 0 0 avahi-daemon

[16387.760996] [467] 995 467 60688 895 110592 0 0 polkitd

[16387.761000] [473] 0 473 6953 256 81920 0 0 systemd-logind

[16387.761003] [562] 0 562 67078 832 163840 0 0 NetworkManager

[16387.761007] [578] 0 578 4923 256 77824 0 0 wpa_supplicant

[16387.761010] [585] 0 585 80121 589 122880 0 0 ModemManager

[16387.761013] [703] 114 703 423624 1552 208896 0 0 docker-registry

[16387.761017] [ 721] 0 721 340017 3498 299008 0 -999 containerd

[16387.761020] [ 786] 0 786 4954 448 81920 0 -1000 sshd

[16387.761024] [865] 0 865 620 96 45056 0 0 fly-getexe

[16387.761027] [891] 0 891 1630 64 49152 0 0 agetty

[16387.761031] [ 1744] 0 1744 9731 4013 118784 0 0 astra-event-dia

[16387.761034] [ 1768] 0 1768 384560 7030 425984 0 -500 dockerd

[16387.761038] [1774] 0 1774 345665 5100 466944 0 0 syslog-ng

[16387.761041] [5571] 0 5571 3670 192 73728 0 0 fly-dm

[16387.761044] [5578] 102 5578 100486 24100 581632 0 0 Xorg

[16387.761047] [5587] 0 5587 106865 136 106496 0 0 VBoxService

[16387.761051] [5642] 102 5642 5567 512 81920 0 100 systemd

[16387.761054] [5645] 102 5645 28019 1385 102400 0 100 (sd-pam)

[16387.761058] [5670] 0 5670 6821 400 102400 0 0 fly-dm

[16387.761061] [5682] 1000 5682 5610 576 81920 0 100 systemd

[16387.761064] [5683] 1000 5683 28019 1385 102400 0 100 (sd-pam)

[16387.761068] [5698] 1000 5698 13047 4141 147456 0 0 fly-wm

[16387.761071] [5758] 1000 5758 2317 256 57344 0 200 dbus-daemon

[16387.761074] [5791] 1000 5791 60058 832 192512 0 200 kglobalaccel5

[16387.761078] [5798] 1000 5798 4628 98 57344 0 0 VBoxClient

[16387.761081] [5799] 1000 5799 37695 130 69632 0 0 VBoxClient

[16387.761085] [5811] 1000 5811 4628 130 61440 0 0 VBoxClient

[16387.761088] [5812] 1000 5812 37687 98 73728 0 0 VBoxClient

[16387.761091] [5817] 1000 5817 4628 130 57344 0 0 VBoxClient

[16387.761095] [5819] 1000 5819 37816 66 69632 0 0 VBoxClient

[16387.761098] [5822] 1000 5822 4628 98 57344 0 0 VBoxClient

[16387.761101] [5823] 1000 5823 37748 194 73728 0 0 VBoxClient

[16387.761104] [5837] 0 5837 58952 256 98304 0 0 upowerd

[16387.761108] [5844] 1000 5844 2867 202 49152 0 0 ssh-agent

[16387.761111] [5870] 1000 5870 103544 1216 258048 0 0 polkit-kde-auth

[16387.761115] [5871] 1000 5871 125625 1600 290816 0 0 org_kde_powerde

[16387.761118] [5873] 1000 5873 67186318 736 196608 0 0 baloo_file

[16387.761121] [5876] 1000 5876 188404 3439 364544 0 0 fly-notificatio

[16387.761124] [5877] 1000 5877 39425 576 163840 0 0 astra-event-wat

[16387.761128] [5880] 1000 5880 36132 384 139264 0 0 fly-cups-watch

[16387.761131] [5881] 1000 5881 63043 1056 208896 0 0 kscreend

[16387.761135] [5882] 1000 5882 67439028 7809 712704 0 0 fly-start-menu

[16387.761138] [5883] 1000 5883 67294 1184 241664 0 0 fly-shutdown-sc

[16387.761142] [5886] 1000 5886 69346 1391 262144 0 0 fly-touchpadd

[16387.761145] [5892] 1000 5892 77765 288 102400 0 0 at-spi-bus-laun

[16387.761148] [5896] 1000 5896 58767 160 94208 0 0 agent

[16387.761152] [5900] 1000 5900 90195 1472 282624 0 0 fly-notify-osd-

[16387.761155] [5911] 1000 5911 393645 8394 720896 0 0 fly-sound-apple

[16387.761158] [5916] 1000 5916 87538 1600 266240 0 0 fly-reflex-serv

[16387.761171] [5921] 1000 5921 2208 160 61440 0 0 dbus-daemon

[16387.761176] [5922] 1000 5922 14788 3917 163840 0 0 applet.py

[16387.761179] [5929] 1000 5929 4633 256 73728 0 0 compton

[16387.761182] [5944] 1000 5944 123154 1376 278528 0 200 kactivitymanage

[16387.761186] [5974] 1000 5974 106825 418 106496 0 0 pulseaudio

[16387.761190] [5976] 0 5976 99686 565 139264 0 0 udisksd

[16387.761193] [5985] 1000 5985 60389 384 106496 0 0 gsettings-helpe

[16387.761196] [6002] 1000 6002 41071 224 86016 0 0 at-spi2-registr

[16387.761200] [6003] 1000 6003 161984 4706 495616 0 0 nm-applet

[16387.761203] [6017] 1000 6017 59383 832 188416 0 200 kscreen_backend

[16387.761207] [ 6067] 1000 6067 59604 224 102400 0 200 gvfsd

[16387.761211] [6450] 1000 6450 154277 5493 385024 0 0 fly-term

[16387.761214] [6459] 1000 6459 2225 416 57344 0 0 bash

[16387.761218] [6835] 1000 6835 5043 320 81920 0 0 su

[16387.761221] [6836] 113 6836 2322 448 53248 0 0 bash

[16387.761225] [8280] 0 8280 8723 1216 102400 0 0 cupsd

[16387.761229] [10858] 1000 10858 154156 5320 376832 0 0 fly-term

[16387.761232] [10865] 1000 10865 2225 384 57344 0 0 bash

[16387.761236] [12288] 113 12288 66375 8928 229376 0 -900 postgres

[16387.761239] [12289] 113 12289 19967 637 122880 0 0 postgres

[16387.761243] [12290] 113 12290 66442 1313 155648 0 0 postgres

[16387.761246] [12291] 113 12291 66409 1185 151552 0 0 postgres

[16387.761249] [12295] 113 12295 66375 1729 143360 0 0 postgres

[16387.761252] [12296] 113 12296 66878 993 172032 0 0 postgres

[16387.761256] [12297] 113 12297 66854 865 159744 0 0 postgres

[16387.761260] [12499] 113 12499 57491 4352 184320 0 -1000 postgres

[16387.761263] [12500] 113 12500 57528 1104 151552 0 0 postgres

[16387.761267] [12501] 113 12501 57524 1008 147456 0 0 postgres

[16387.761271] [12503] 113 12503 57524 1744 143360 0 0 postgres

[16387.761274] [12504] 113 12504 57923 1008 163840 0 0 postgres

[16387.761277] [12505] 113 12505 57524 742 147456 0 0 postgres

[16387.761280] [12506] 113 12506 57635 880 143360 0 0 postgres

[16387.761283] [12507] 113 12507 57891 912 151552 0 0 postgres

[16387.761287] [12517] 113 12517 6495 448 94208 0 0 psql

[16387.761290] [ 12518 ] 113 12518 1630962   789553 6533120 0 0 postgres

[16387.761293] [12540] 1000 12540 5043 256 86016 0 0 su

[16387.761297] [12541] 113 12541 2221 384 57344 0 0 bash

[16387.761300] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null), cpuset=tantor-se-server-16.service,mems_allowed=0,global_oom, task_memcg=/system.slice/tantor-se-server-16.service,task=postgres,pid=12518, uid=113

[16387.761329] Out of memory: Killed process 12518 ( postgres ) total-vm: 6523848kB , anon-rss: 3151300kB , file-rss:0kB, shmem-rss:6912kB, UID:113 pgtables:6380kB oom_score_adj:0

The OOM kill process stopped the server process that was running the script.

When OOM-kill was triggered, the oom_score_adj value did not play any role, since there were no memory consumers in the operating system except for PostgreSQL processes .

The OOM kill process shows the virtual memory size total - vm : 6523848 kB . 6523848 kB /4 kB (page size) = 1630962 (in pages) is the memory size of the server process at the time it was stopped.

Memory allocation in diagnostic messages is shown in 4K pages, except for the pgtables_bytes column :

[16387.760954] Tasks state (memory values in pages ):

[16387.760956] [ pid ] uid tgid total_vm     rss pgtables_bytes swapents oom_score_adj name

[16387.761290] [ 12518 ] 113 12518 1630962  789553 6533120 0 0 postgres

===== ==== note ==========

The server process read a file into its local memory, which was 3GB in size .

The virtual machine has 4GB and no swap. According to the free utility , the available memory ( available ) was 3027272 k ibi . At the same time, the operating system allocated 6523848 kB of virtual memory . If the value vm.overcommit_memory =2 with vm . overcommit_ratio =50, then the process would be able to allocate only 1.5GB of memory and would be denied an attempt to allocate more, despite the fact that the available memory was 3GB. In this case , the OOM-kill process would not manifest itself. Example :

postgres=# \i ela.sql

SET

CREATE TABLE

ALTER TABLE

psql:ela.sql:44: ERROR: out of memory

DETAIL: Failed on request of size 2147483646 in memory context "COPY".

CONTEXT: COPY t2, line 1

vm.overcommit_memory= 2 on a virtual machine without swap enabled , or increase:

root@tantor:~# sysctl -w vm.overcommit_ratio=100

otherwise you will not be able to give any command :

root@tantor:~# free

total used free shared buff/cache available

Mem: 4020796 948788 1958532 188700 1533332 3072008

Swap: 0 0 0

root@tantor:~# sysctl -w vm.overcommit_memory=2

vm.overcommit_memory = 2

root@tantor:~# free

-bash: fork: Cannot allocate memory

root@tantor:~# reboot

-bash: fork: Cannot allocate memory

After enabling swap in the next part of the practice, you can check that with vm . overcommit _ memory =2 OOM kill does not work, instead of working, the process allocating memory is given an error.

=====================

5) View the list of PostgreSQL processes:

postgres @ tantor :~$ for PID in $( pgrep " postgres "); do awk '/ Pss /{ PSS +=$2} END { getline cmd < "/ proc /'$ PID '/ cmdline "; sub ("\0", "", cmd ); getline oom < "/ proc / '$ PID '/ oom_score " ; printf "%.0 f -% s -> % s ( PID % s ) \ n ", PSS , oom , cmd , '$ PID '}' / proc / $ PID / smaps ; done | sort - n - r

The list is empty, or only Astralinux instance processes are running PostgreSQL . After an OOM kill stopped the server process , the instance was restarted according to the value of the restart_after_crash configuration parameter :

postgres=# select name, setting, context, max_val, min_val from pg_settings where name ~ 'restart';

name | setting | context | max_val | min_val

---------------------+---------+---------+---------+---------

restart_after_crash | on | sighup | |

(1 row )

By default, this option is enabled, and after a server process crashes, the postgres process will crash all child processes (equivalent to an immediate stop without a checkpoint) and restart the processes (equivalent to a " crash recovery " - starting the instance after a crash). If you disable this option, all processes in the instance will be forcibly stopped, including the postgres process. The state of the cluster after stopping will be the same as after a forced stop with the pg_ctl stop -m immediate command :

postgres@tantor:~$ pg_controldata | grep state

Database cluster state: in production

6) Launch instance tantor:

postgres@tantor:~$ sudo systemctl start tantor-se-server-16

If the instance does not start, you can wait until it stops or issue the command pg_ctl​​ stop - m immediate and repeat the sudo command systemctl start tantor - se - server -16

7) This point can be omitted. A simple query can cause a lack of memory and stop psql :

postgres@tantor:~$ psql

postgres=# select repeat('a', 100000000) from generate_series(1, 100);

Killed

postgres@tantor:~$ sudo dmesg | tail -5

[ 1002.311864] [ 6421] 113 6421 747298 718592 5861376 0 0 psql

[ 1002.311867] [ 6422] 113 6422 139618 75066 770048 0 0 postgres

[ 1002.311870] oom-kill :constraint=CONSTRAINT_NONE,nodemask=(null), cpuset=containerd.service,mems_allowed=0,global_oom,task_memcg=/user.slice/user-1000.slice/session-3.scope, task=psql,pid= 6421 ,uid=113

[ 1002.311894] Out of memory: Killed process 6421 ( psql ) total-vm:2989192kB, anon-rss:2874368kB, file-rss:0kB, shmem-rss:0kB, UID:113 pgtables:5724kB oom_score_adj:0

If vm.overcommit_memory=2 then the result will be:

postgres=# select repeat('a', 100000000) from generate_series(1, 100);

out of memory for query result

set vm.overcommit_memory=2 only after setting vm . overcommit_ratio = 100 or enabling swap, otherwise the virtual machine will immediately run out of memory and you will not be able to issue any commands.

OOM kill usually kills the psql process, but it can also kill instance processes, causing the instance to crash:

postgres=# select repeat('a', 1000000000) from generate_series(1, 1000);

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

The connection to the server was lost. Attempting reset: Failed.

!?>

[1211447.321264] [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score _adj name

[1211447.321658] [524388] 113 524388 756951 205344 4251648 313792 0 psql

[1211447.321661] [524389] 113 524389   808432 716332 6062080 17383 0 postgres

[1211447.321664] oom-kill :constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=user.slice,mems_allowed=0, global_oom,task_memcg=/system.slice/tantor-se-server-16.service,task=postgres,pid=524389,uid=113

[1211447.321735] Out of memory: Killed process 524389 (postgres) total-vm:3233728kB, anon-rss:2863536kB, file-rss:1664kB, shmem-rss:128kB, UID:113 pgtables:5920kB oom_score_adj:0

In this case, the instance may not start and it will need to be started with the command:

sudo systemctl start tantor-se-server-16

Part 5. Enabling swapping

1) Add a 2GB swap file and enable swap:

root@tantor:~# dd if=/dev/zero of=/swap_file bs=1M count=2048

2048+0 records in

2048+0 records out

2147483648 bytes (2.1 GB, 2.0 GiB) copied, 22.3827 s, 95.9 MB/s

root@tantor:~# chmod 600 /swap_file

root@tantor:~# mkswap /swap_file

Setting up swapspace version 1, size = 2 GiB (2147479552 bytes)

no label, UUID=4b56ea3b-ac66-46ef-801b-2aa473f27ef6

root@tantor:~# swapon /swap_file

root@tantor:~# free -m

total used free shared buff/cache available

Mem : 3926 881 672 19 2680 3044

Swap : 2047 0 2047

Swapping will not be enabled automatically when you reboot the virtual machine.

2) Do it script dump :

postgres=# \timing

Timing is on.

ela=# \i ela.sql

...

SET

CREATE TABLE

ALTER TABLE

COPY 1

Time: 18,979.619 ms (00:18.980)

The COPY command took 18 seconds to load the string.

3) In the same psql window in which the loading script was executed, look at the value of the enable_large_allocations parameter :

ela=# show enable_large_allocations ;

enable_large_allocations

--------------------------

 on

(1 row)

The parameter was enabled when executing the ela.sql file with the command:

SET enable_large_allocations TO on; from this file .

By default, the option is disabled.

4) In another terminal window, look at the beginning of the ela.sql file :

postgres@tantor:~$ head -n 20 ela.sql

--

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET transaction_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

SET enable_large_allocations TO on;

Meaning parameter enable_large_allocations was unloaded utility pg_dump .

5) look at the characteristics of the parameter enable_large_allocations :

ela=# select * from pg_settings where name like '%large%' \gx

-[ RECORD 1 ]---+--------------------------------------------

name | enable_large_allocations

setting | on

unit |

category | Resource Usage/Memory

short_desc | Sets whether to use large memory buffers - greater than 1 Gb, up to 2 Gb

extra_desc |

context | superuser

vartype | bool

source | session

min_val |

max_val |

enumvals |

boot_val | off

reset_val | off

sourcefile |

sourceline |

pending_restart | f​​

The parameter can be set at different levels, including the session level.

The parameter increases the size of the string buffer from 1GB to 2GB. Strings up to 2GB in size can be unloaded. Strings larger than 2GB will not be unloaded.

Part 6. Page cache

1) Open or switch to a terminal with root privileges :

postgres@tantor:~$ su -

Password: root

2) Run the commands:

root@tantor:~# cat /proc/meminfo | grep Cached

Cached: 1367440 kB

SwapCached: 49328 kB

root@tantor:~# free

total used free shared buff/cache available

Mem : 4020792 605576 2250740 36796 1452148 3415216

Swap : 2097148 508696 1588452

By default, the free command displays values in kibibytes (bytes divided by 1024 1024). To display in kilobytes, use the --kylo or --si option (bytes divided by 1000). The difference noticeable , in second discharge left (3 6 820 3 7 703):

root@tantor:~# free

total used free shared buff/cache available

Mem: 4020792 575192 3493796 3 6 820 215152 3445600

Swap: 2097148 507160 1589988

root@tantor:~# free --si

total used free shared buff/cache available

Mem: 4117291 589750 3576889 3 7 703 220323 3527540

Swap: 2147479 519331 1628147

root@tantor:~# free --bytes

total used free shared buff/cache available

Mem: 4117291008 588890112 3577745408 3 7 703 680 220340224 3528400896

Swap: 2147479552 519331840 1628147712

Cached is included in buff / cache , the difference (taking into account different units of measurement) is the value of buff .

3) Make clean page cache blocks free:

root@tantor:~# echo 1 > /proc/sys/vm/drop_caches

root@tantor:~# cat /proc/meminfo | grep Cached

Cached: 156684 kB

SwapCached: 49332 kB

root@tantor:~# free

total used free shared buff/cache available

Mem : 4020792 536520 3530520 36800 184272 3484272

Swap : 2097148 508696 1588452

Values Cached and buff / cache decreased by 1.2GB. Value free increased.

4) Look at the number of pages of different sizes:

root@tantor:~# cat /proc/buddyinfo

Node 0, zone DMA 0 0 0 0 0 0 0 1 0 1 3

Node 0, zone DMA32 1670 1466 868 707 734 503 294 166 111 111 648

Node 0, zone Normal 444 283 262 98 31 10 3 39 32 2 1

Node 0 - physical processor number. Zones:

 zone DMA    - virtual memory with an offset from zero to 16 MB

 zone DMA 32 - from 16MB to 4GB

 zone Normal - from 4GB to 2^48 (2 to the power of 48)

Each zone is divided into parts of the memory address space of size ( 4096 bytes *2^ n ):

4Kb 8Kb 16Kb 32Kb 64Kb 128Kb 256Kb 512Kb 1Mb 2Mb 4Mb

5) Look at the page set fragmentation index:

root@tantor:~# cat /sys/kernel/debug/extfrag/extfrag_index

Node 0, zone DMA -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000

Node 0, zone DMA32 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000

Node 0, zone Normal -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 0.964 0.982 0.991 0.996

Defragmentation will start automatically at decrease free memory below vm.min_free_kbytes=67584 or meanings vm.watermark_scale_factor . The default value is vm.watermark_scale_factor=10 , which means 0.1% of the free physical memory. These values are too small and defragmentation is launched as a last resort. Recommendations for setting values are given in the theoretical part of the chapter.

6) Run defragmentation manually:

root@tantor:~# echo 1 > /proc/sys/vm/compact_memory

7) Check the defragmentation result (how the fragmentation index has changed):

root@tantor:~# cat /sys/kernel/debug/extfrag/extfrag_index

Node 0, zone DMA -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000

Node 0, zone DMA32 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000

Node 0, zone Normal -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 -1.000 0.991

8) Look how many dirty 4 KB pages are in the linux page cache:

root@tantor:~# cat /proc/vmstat | grep dirty

nr_dirty 126

nr_dirty_threshold 176446

nr_dirty_background_threshold 88115​​​​​​

9) Run the sync command, which flushes dirty pages to disk:

root@tantor:~# sync

root@tantor:~# cat /proc/vmstat | grep dirty

nr_dirty 0

nr_dirty_threshold 174962

nr_dirty_background_threshold 87374

root@tantor:~# cat /proc/vmstat | grep dirty

nr_dirty 2

nr_dirty_threshold 175071

nr_dirty_background_threshold 87428

The sync command flushes all dirty pages and nr_dirty becomes 0 at some point, but when processes are running, the pages constantly become dirty and the value increases almost immediately .

10) Look at the values of the parameters related to working with dirty pages:

root@tantor:~# sysctl -a | grep dirty

vm.dirty_background_bytes = 0

vm.dirty_background_ratio = 10

vm.dirty_bytes = 0

vm.dirty_expire_centisecs = 3000

vm.dirty_ratio = 20

vm.dirty_writeback_centisecs = 500

vm.dirtytime_expire_seconds = 43200

The duration of page retention from the moment of change is determined by the parameter:

vm . dirty_expire_centisecs - how much the buffer can be dirty before it is marked for writing, default is 3000 (30 seconds), can be changed to 500 (5 seconds ) .

The retention is also affected by the parameter: vm . dirty _ writeback _ centisecs - the waiting period between writes to disk, by default 500 (5 seconds), can be reduced to 250 (2.5 seconds). Recommended values for other parameters:

vm . dirty_ratio = 10​

vm . dirty _ background _ ratio = 3

These parameters can be left unchanged, since the linux cache pages are sent to write by the instance processes by calling fdatasync on WAL , fsync on tablespace files at the end of the checkpoint and do this for fault tolerance (protection against power failure). These parameters can be used to smooth out the peak load that occurs at the end of the checkpoint. But even for load smoothing there are PostgreSQL parameters checkpoint_flush_after and bgwriter_flush_after , which are also set to optimal values for normal load and normal equipment ( HDD and SSD ).

11) Look at the PostgreSQL configuration parameters that affect the retention of dirty pages in the page cache:

postgres=# \dconfig *flush*

List of configuration parameters

Parameter | Value

------------------------+-------

backend_flush_after | 0

bgwriter_flush_after | 512kB

checkpoint_flush_after | 256kB

wal_writer_flush_after | 1MB

(4 rows)

These settings limit the amount of dirty pages in the linux page cache and reduce the chance of stalling when fsyncing at the end of a checkpoint or when linux flushes dirty pages to disk according to the settings in the previous item. If the settings in the previous item allow a large number of dirty pages to accumulate, relatively long delays may occur, making the instance run less smoothly.

12) Execute the following commands :

postgres=# alter system set shared_preload_libraries = pg_stat_statements, pg_wait_sampling, pg_stat_kcache, pg_qualstats, pg_store_plans, pg_prewarm;

13) Restart the instance, as the parameter change will only take effect after the instance is restarted:

root@tantor:~# systemctl restart tantor-se-server-16

load shared libraries into process memory when the instance starts . The loaded pg_wait_sampling library will be needed in the next practice.

Practice for Chapter 3

Part 1. Standard pgbench test

1) In the discussion https :// www . postgresql . org / message - id / flat /53 FD 5 D 6 C .40105%40 catalyst . net . nz an example of a pgbench test with HyperThread enabled and disabled on a 4-core processor was given . Let's see on what number of clients the maximum tps is achieved on your virtual machine. Create test tables with a scale factor of 300 as in the test at the link:

postgres@tantor:~$ pgbench -i -s 300

dropping old tables...

creating tables...

generating data (client-side)...

30000000 of 30000000 tuples (100%) done (elapsed 50.12 s, remaining 0.00 s)

vacuuming...

creating primary keys...

done in 78.79 s (drop tables 0.02 s, create tables 0.01 s, client-side generate 50.79 s, vacuum 2.46 s, primary keys 25.52 s).

The tables took 78 seconds to create.

2) Perform 15 second tests with different numbers of connections:

postgres@tantor:~$ pgbench -c 64 -T 15 2> /dev/null | grep tps

tps = 1773.570100 (without initial connection time)

postgres@tantor:~$ pgbench -c 100 -T 15 2> /dev/null | grep tps

tps = 1646.101354 (without initial connection time)

postgres@tantor:~$ pgbench -c 32 -T 15 2> /dev/null | grep tps

tps = 1685.528291 (without initial connection time)

postgres@tantor:~$ pgbench -c 16 -T 15 2> /dev/null | grep tps

tps = 1550.934797 (without initial connection time)

Maximum tps is achieved with 64 connections.

3) Repeat the test:

postgres @ tantor :~$ pgbench - c 64 - T 15 2> / dev / null | grep tps

tps = 1676.523228 (without initial connection time)

postgres@tantor:~$ pgbench -c 100 -T 15 2> /dev/null | grep tps

tps = 1572.848543 (without initial connection time)

postgres@tantor:~$ pgbench -c 32 -T 15 2> /dev/null | grep tps

tps = 1694.453483 (without initial connection time)

postgres@tantor:~$ pgbench -c 16 -T 15 2> /dev/null | grep tps

tps = 1477.982431 (without initial connection time)

tps values have dropped slightly in all tests. The default test makes changes to table rows and index records. Repeated runs of the tests give consistently worse results. The test with table changes cannot be used without recreating the tables.

In the linked discussion, depending on the processor, the maximum tps was achieved at 256, 96, 48 connections on processors with different numbers of cores. Accuracy to the nearest power of two: the value given is 256, and the real maximum can be in the range from 140 to 350.

4) You can use a select-only test that does not change data. This test depends on the buffer cache filling and the first test executions have a lower tps than the subsequent ones. The maximum tps was achieved at 16 connections.

postgres@tantor:~$ pgbench -b select-only -c 64 -T 15 2> /dev/null | grep tps

tps = 8687.455945 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 32 -T 15 2> /dev/null | grep tps

tps = 9112.963307 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 16 -T 15 2> /dev/null | grep tps

tps = 9611.093353 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 8 -T 15 2> /dev/null | grep tps

tps = 9548.481330 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 4 -T 15 2> /dev/null | grep tps

tps = 7841.653129 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 100 -T 15 2> /dev/null | grep tps

tps = 8780.049265 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 64 -T 15 2> /dev/null | grep tps

tps = 9200.191422 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 32 -T 15 2> /dev/null | grep tps

tps = 9499.272270 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 16 -T 15 2> /dev/null | grep tps

tps = 9820.530795 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 8 -T 15 2> /dev/null | grep tps

tps = 9637.473378 (without initial connection time)

postgres@tantor:~$ pgbench -b select-only -c 4 -T 15 2> /dev/null | grep tps

tps = 7825.917563 (without initial connection time)

Part 2. Binding processes to a processor core

If the host has a single processor, then binding PostgreSQL instance processes is unlikely to provide any benefit. The operating system binds its threads to cores. Threads that work with the same memory structures are assigned to the same core. When binding instance processes, it is worth considering which cores the operating system threads are assigned to, so that these cores do not become a bottleneck.

1) Look at the number of processor cores:

postgres@tantor:~$ nproc

4

2) Run the command to see the linux kernel thread affinity . The kernel thread daemon kthreadd has a process ID of pid= 2 :

for tid in $( pgrep - w 2 | tr '\ n ' ' '); do

 ps --no-headers -q "${tid}" -o pid -o user -o psr -o cmd;

taskset -cp "${tid}";

done

26 root 2 [cpuhp/2]

pid 26's current affinity list: 2

27 root 2 [idle_inject/2]

pid 27's current affinity list: 2

28 root 2 [migration/2]

pid 28's current affinity list: 2

29 root 2 [ksoftirqd/2]

pid 29's current affinity list: 2

73 root 3 [scsi_eh_2]

pid 73's current affinity list: 0-3

137 root 2 [kworker/2:1H-kblockd]

...

Part streams linux kernels are tied To to everyone from cpu cores . kernel threads are used by linux for execution background operations .

3) Run the command to see the affinity of processes named postgres :

for tid in $(pgrep postgres | tr '\n' ' '); do

ps --no-headers -q "${tid}" -o pid -o user -o psr -o cmd;

taskset -cp "${tid}";

done

126673 postgres 1 /opt/tantor/db/16/bin/postgres -D /var/lib/postgresql/tantor-se-16/data

pid 126673's current affinity list: 0-3

 126674 postgres 1 postgres: checkpointer 

pid 126674 's current affinity list: 0-3

126675 postgres 3 postgres: background writer

pid 126675's current affinity list: 0-3

126677 postgres 0 postgres: walwriter

pid 126677's current affinity list: 0-3

126678 postgres 1 postgres: autovacuum launcher

pid 126678's current affinity list: 0-3

126679 postgres 3 postgres: autoprewarm leader

pid 126679's current affinity list: 0-3

126680 postgres 0 postgres: pg_wait_sampling collector

pid 126680's current affinity list: 0-3

126681 postgres 3 postgres: logical replication launcher

pid 126681's current affinity list: 0-3

...

postgres processes There is no binding to the CPU . In the example, processes can be assigned to any available core: 0,1,2,3.

4) Bind the checkpointer process to core 1 :

postgres@tantor:~$ taskset -p 12290

pid 12290's current affinity list: 0-3

pid 12290's new affinity list: 1

5) Remove the checkpointer process binding to core 1:

postgres@tantor:~$ taskset -p -c 0-999 12290

pid 12290's current affinity list: 1

pid 12290's new affinity list: 0-3

There is no option to remove the binding, you can only specify a new binding in which to list all cores. The simplest is to specify a range from 0 to the maximum number of cores or higher .

Part 3. Execution context switches (" VCX / ICX ")

postgres processes :

postgres@tantor:~$ pidstat -w -l -C postgres

Linux 6.6.28-1-generic (tantor) _x86_64_ (4 CPU)

02:25:37 PM UID PID cswch/s nvcswch/s Command

02:25:37 PM 113 1405 0.10 0.02 /opt/tantor/db/16/bin/postgres -D /var/lib/postgresql/tantor-se-16/data

02:25:37 PM 113 1485 0.07 0.01 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf

02:25:37 PM 113 1586 0.01 0.00 postgres: checkpointer

02:25:37 PM 113 1587 0.39 0.00 postgres: background writer

02:25:37 PM 113 1620 0.02 0.00 postgres: 15/main: logger

02:25:37 PM 113 1693 0.26 0.00 postgres: walwriter

02:25:37 PM 113 1694 0.10 0.01 postgres: autovacuum launcher

02:25:37 PM 113 1695 0.02 0.04 postgres: autoprewarm leader

02:25:37 PM 113 1696    94.35 0.11 postgres: pg_wait_sampling collector

02:25:37 PM 113 1698 0.01 0.00 postgres: logical replication launcher

02:25:37 PM 113 1751 0.01 0.00 postgres: 15/main: checkpointer

02:25:37 PM 113 1752 0.39 0.00 postgres: 15/main: background writer

02:25:37 PM 113 1817 0.26 0.00 postgres: 15/main: walwriter

02:25:37 PM 113 1818 0.05 0.01 postgres: 15/main: autovacuum launcher

02:25:37 PM 113 1819 0.01 0.00 postgres: 15/main: logical replication launcher

02:25:37 PM 0 7653 0.00 0.00 pidstat -w -l -C postgres

than an hour or the operating system has been rebooted, pidstat will show that the pg_wait_sampling process collector Context switches at a rate of ~100 ( 94.46 in the example ) per second. This process collects statistics by polling instance processes. Most polling cycles complete quickly, the process releases the kernel and the cswch statistic is incremented. Jiffy is a unit of time that represents the number of timer interrupts from booting Linux. Each timer interrupt increases the number of Jiffies by one. The relationship between Jiffy and seconds is determined by the HZ constant in the Linux kernel. This value determines the number of Jiffies per second. In Linux before version 2.4, the tick rate between Jifies was 10 ms. Since Linux 2.4, the tick became 1 ms. Since kernel version 3.10, the tick rate varies and can be slowed down by setting CONFIG_NO_HZ_FULL=y when compiling the kernel. In this case, timer interrupts are disabled not only for idle processors, but also during code execution if there are no tasks in the execution queue on the core.

2) View the current number of execution context switches for the process pg_wait_sampling​​​​ collector . At intervals of ~seconds, execute the command:

postgres @ tantor :~$ grep ctxt / proc / 1696 / status

voluntary_ctxt_switches: 304 222

nonvoluntary_ctxt_switches: 334

postgres@tantor:~$ grep ctxt /proc/ 1696 /status

voluntary_ctxt_switches: 304 343

nonvoluntary_ctxt_switches: 335

of random context switches according to this cumulative statistic is about 100 per second. Involuntary switches are also present. The data matches the result of pidstat . Process pg_wait_sampling collector polls the state of all processes in the instance at a frequency specified by the parameter 

pg_wait_sampling.history_period or pg_wait_sampling.profile_period . The default values for these parameters are 10 milliseconds, which corresponds to a frequency of 100 times per second.

3) If you restart the instance, then after restarting the cswch / s values will gradually increase. Perform V root terminal :

root@tantor:~# systemctl restart tantor-se-server-16

root@tantor:~# pidstat -w -l -C postgres | grep sampling

02:36:31 PM 113 7728 4.25 0.00 postgres: pg_wait_sampling collector

The averaging period is from the moment the operating system is started , which makes the use of the pidstat utility meaningless.

To get a meaningful result, it is worth using cumulative statistics. The value will reach 10 per second with a real frequency of 100 per second in about 7 minutes. At the same time, cumulative statistics shows the frequency correctly 100 times per second. Run the command twice with an interval of about a second:

root @ tantor :~# grep ctxt / proc /7728/ status

voluntary_ctxt_switches: 13 433

nonvoluntary_ctxt_switches: 10

root@tantor:~# grep ctxt /proc/7728/status

voluntary_ctxt_switches: 13 551

nonvoluntary_ctxt_switches: 11

4) To monitor context switches across the entire operating system, you can use the perf utility :

root@tantor:~# perf stat -a

^C

Performance counter stats for 'system wide':

68,010.80 msec cpu-clock # 2,000 CPUs utilized

9,885 context-switches # 145.345 /sec

250 cpu-migrations # 3.676 /sec

2,774 page-faults # 40.788 /sec

<not supported> cycles

<not supported> instructions

<not supported> branches

<not supported> branch-misses

      34.005359735 seconds time elapsed

The utility with such parameters works until it is stopped by the key combination <ctrl+c>.

The utility gives the correct values: 145 context switches per second. Disadvantage: does not give the number of involuntary context switches.

To collect the same statistics for a single process, use the command:

perf stat - p process_number

Part 4. Monitoring CPU load

1) Install, if not installed, the atop and htop utilities in the terminal under the root user :

root @tantor:~# apt update

Hit:1 cdrom://OS Astra Linux 1.8.1.6 DVD 1.8_x86-64 InRelease

Hit:2 https://download.astralinux.ru/astra/stable/1.8_x86-64/repository-extended 1.8_x86-64 InRelease

Ign:3 https://download.astralinux.ru/astra/stable/1.8_x86-64/repository-devel 1.8_x86-64 InRelease

Hit:4 https://download.astralinux.ru/astra/stable/1.8_x86-64/repository-main 1.8_x86-64 InRelease

Err:5 https://download.astralinux.ru/astra/stable/1.8_x86-64/repository-devel 1.8_x86-64 Release

404 Not Found [IP: 130.193.50.59 443]

Reading package lists... Done

E: The repository 'https://download.astralinux.ru/astra/stable/1.8_x86-64/repository-devel 1.8_x86-64 Release' does not have a Release file.

N: Updating from such a repository can't be done securely, and is therefore disabled by default.

N: See apt-secure(8) manpage for repository creation and user configuration details.

root@tantor:~# apt install htop -y

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

htop is already the newest version (3.2.2-2+b1).

0 upgraded, 0 newly installed, 0 to remove and 603 not upgraded.

root@tantor:~# apt install atop -y

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

atop is already the newest version (2.8.1-1+b1).

0 upgraded, 0 newly installed, 0 to remove and 603 not upgraded.

2) Delete the directory if it exists and run the pg_basebackup utility in the postgres user terminal , slowing down the backup to 50 kilobytes per second:

postgres @tantor:~$ rm -rf /var/lib/postgresql/backup/1

postgres @tantor:~$ time pg_basebackup -c fast -D $HOME/backup/1 -P -r 50k

The utility will start the backup. You do not need to close the terminal window with the running utility.

3) In another terminal, as root , run the top command:

root @tantor:~# ps -e -o pcpu,vsz,rss,pss,cls,psr,rops,wops,s,cmd --sort -pcpu | head -2

%CPU VSZ RSS PSS CLS PSR ROPS WOPS S CMD

99.9 24608 8576 1486 TS 1 43 8385 R pg_basebackup -c fast -D /var/lib/postgresql/backup/1 -P -r 1M

What problem is visible in the command output?

CPU load 99.9%

Next, look at the output of the top, atop, and htop utilities in order to understand which utility is more convenient for identifying the problem of processor load.

4) Run the top utility:

root @ tantor :~# top

What CPU load does the utility show?

In the %CPU column the utility shows 100.0.

In the %Cpu(s) field: 25.5 us, 0.5 sy .

5) Press the 1 key on the keyboard. The utility output will change, it will show the load separately by processor cores:

Load on 3 core: %Cpu3: 100.0 us, 0.0 sy and this is the real load.

This means that the third core is fully loaded, and the USER / SYS ratio is 100:0 .

Look at the load metric on the top right average : 1.01, 1.00, 0.87

The metric is also present in the / proc / loadavg file and the output of the uptime command :

root@tantor:~# uptime

09:39:07 up 18:01, 3 users, load average: 0.19, 0.20, 0.18

This metric shows the number of active (working or willing to work) processes, averaged over intervals of 1, 5, 15 minutes. The metric takes into account processes that consume processor time and are waiting for a result from the disk subsystem. Processes that are waiting for data from network interfaces are not taken into account in the metric.

If the number of active processes is less than the number of cores, then if all processes are active, they will not experience a shortage of computing resources. But the number of processes is usually greater than the number of cores. If a large number of active processes appears, then access to the cores is divided between the active processes. When the DBMS is running, the processor is usually not a bottleneck (a scarce resource) and the metric does not indicate any problems. The metric can be useful in that by comparing three numbers you can assess whether the current load is peak or constant. If you create a large number of active processes, for example, by running the pgbench utility in a separate terminal with a number of sessions of 90:

postgres@tantor:~$ pgbench -c 90 -j 90 -T 10000 -P 5

pgbench (16.2)

starting vacuum...end.

progress: 5.0 s, 892.4 tps, lat 105.630 ms stddev 87.005, 0 failed

progress: 10.0 s, 413.0 tps, lat 237.783 ms stddev 257.410, 0 failed

...

By default, the configuration parameter max_connections=100 , so the number of sessions is selected to be 90.

Next, you can look at the output of the top utility for all processors and individually, and you will see that if the load exceeds the number of cores, then the total load on the processor becomes useful. If the total load %Cpu(s) is greater than 90%, then the processor is a bottleneck. With a long-term load, the 90% limit ( for the purpose of determining resource scarcity) can vary from 85% to 95% depending on the share of disk I/O waiting.

Example of total load - there is a %Cpu(s) metric :

Average load across all cores: 100-28.6=71.4% the processor is not a bottleneck.

However, if we reduce the number of server processes from 90 to ~50 for four cores in this example, then the maximum tps will be achieved. With a large load of processors, context switching, and under a very large wait for receiving timeslots on the processor core, a delay is added. The overhead of context switching (replacing the contents of kernel caches) cannot be measured directly.

Example of processor load - metrics %Cpu0 ... %Cpu 3 :

The processor load is useful when the load is less than the number of cores and to determine whether there are processes that load the core by 100% and for this process (a thread that cannot be paired and serviced by several cores) the processor is a bottleneck.

Close the top utility by pressing the q key on your keyboard .

If pgbench is running , it can be stopped with the key combination < ctrl + c >.

6) Run the mpstat utility :

root @ tantor :~# mpstat - n

Linux 6.6.28-1-generic (tantor) _x86_64_ (4 CPU)

08:40:00 PM NODE %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

08:40:00 PM  all    5.71 0.05 0.39 0.42 0.00 0.46 0.00 0.00 0.00 92.97

See if this utility is convenient for monitoring the load of one core at 100% and the USER / SYS ratio ?

7) Run the a top utility:

root @ tantor :~# atop

Knowing the real load, find it in the atop utility window CPU load given by the pg_basebackup utility .

After 10 seconds the window will change and display different numbers:

The displayed data is updated by default every 10 seconds.

Note for yourself: is it convenient to view the load and is the US E R/SYS ratio visible.

Close the utility by pressing the q key on your keyboard.

8) Run the htop utility :

root @ tantor :~# htop

Note for yourself: is it convenient to view the load and is the US E R/SYS ratio visible in the h top utility.

The load on the processor cores is shown as pseudo-graphics symbols.

Close the utility by pressing the q key on your keyboard.

The goal of this part of the practice is to choose a utility that will make it easier to view the load on processor cores.

Part 5. Collecting statistics into a file and viewing it with the atop utility

1) The atop utility allows you to collect and write statistics to a binary file. The file can be "played" on another host, which is convenient for analysis. The minimum frequency of statistics collection is once per second, by default once every 10 seconds. The volume of collected data is large, but you can use parameters to configure which statistics to collect to reduce the file size.

2) Start collecting statistics at 1 second intervals to the file /atop.record :

root@tantor:~# atop -w /atop.record 1

The terminal will not return a prompt, this is normal. If the file existed, the utility will not erase the collected data, but will start writing data to the end of the file.

3) In a separate root user terminal, run the atop utility in binary file viewing mode:

astra@tantor:~$ atop -r /atop.record

Use the keys on the keyboard ' t ' and 'T' (<Shift+t>) to go to the next and return to the previous interval. The first line issued by the utility shows the time for which statistics were collected. To exit the utility, press the key 'q'.

4) In the terminal where the utility is running, press < ctrl+c >:

root@tantor:~# atop -w /atop.record 1

^C

root@tantor:~#

The utility will stop. The file can be viewed again with the atop utility . You can copy the file to another host and view the file on the other host.

5) Delete the file /atop.record :

root @ tantor :~# rm - f / atop . record

6) If the pg_basebackup utility has not finished the backup, interrupt the utility by pressing the < ctrl + c > key combination on the keyboard:

postgres@tantor:~$ time pg_basebackup -c fast -D $HOME/backup/1 -P -r 50k

^C 3781/902114 kB (68%), 0/1 tablespace

real 19m57.486s

user 19m53.055s

sys 0m3.413s

Note that the time utility returned the correct US E R/SY S values .

7) Delete the directory with the backup:

postgres @ tantor :~$ rm - rf / var / lib / postgresql / backup /1

Part 6. Linux Time Source

1) Look at the list of sources that Linux considered possible to use:

postgres@tantor:~$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource

tsc acpi_pm

2) Look at what time source is used:

postgres@tantor:~$ cat /sys/devices/system/clocksource/clocksource0/current_clocksource

tsc

3) Create a text file named clock_timing.c with the following contents:

#include <time.h>

int main()

{

int rc;

long i;

struct timespec ts;

for(i=0; i< 10000000 ; i++) rc = clock_gettime(CLOCK_MONOTONIC, &ts);

return 0;

}

4) Compile file :

postgres @tantor:~$ gcc clock_timing.c -o clock_timing -lrt

5) The program reads the time indicator 10 million times. Measure the execution time of the program:

postgres @ tantor :~$ time ./ clock _ timing

real         0m13,967s

user         0m13,938s

sys         0m0,008s

time utility is useful for getting the execution time of programs. It returns the actual US E R/SYS and the total execution time of the program.

6 ) Run the pg_test_timing command :

postgres@tantor:~$ pg_test_timing 

Testing timing overhead for 3 seconds.

Per loop time including overhead: 3931.22 ns

Histogram of timing durations:

< us % of total count

     1 0.02475 497

2 60.52305 1215427

4 39.20322 787281

8 0.02480 498

16 0.08869 1781

32 0.06384 1282

64 0.05348 1074

128 0.01215 244

256 0.00388 78

512 0.00065 13

1024 0.00060 12

2048 0.00030 6

4096 0.00035 7

8192 0.00020 4

16384 0.00005 1

The pg_test_timing program comes standard with PostgreSQL and is used to measure the speed of a time source, and returns the distribution of values returned by the time source .

Distribution maximum at ~2.5 milliseconds.

The symbols " us " denote milliseconds ( µs ). The fluctuations in the time output in the utility output are not less than 1 millisecond. This means that numbers less than 1 millisecond are random.

pg_test_timing program provides more detailed information than clock_timing.c . The clock_timing.c program is provided to illustrate the ease of creating your own tests in C. The PostgreSQL code is written in C.

7) Create a table for the test by running the following commands in psql:

postgres=# drop table if exists t;

create table t(pk bigserial, c1 text default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

insert into t select *, 'a' from generate_series(1, 100000);

DROP TABLE

CREATE TABLE

INSERT 0 100000

8) Run the command:

postgres=# explain analyze select count(pk) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1352.50..1352.51 rows=1 width=8) (actual time=307.270..307.276 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1190.20 rows=64920 width=8) (actual time=0.017..152.937 rows=100000 loops=1)

Planning Time: 0.038 ms

Execution Time: 307 . 383 ms

(4 rows)

Command execution time is 307 milliseconds.

9) Replace the time source tsc on acpi_pm . In the root console , run the command:

root@tantor:~# echo acpi_pm >/sys/devices/system/clocksource/clocksource0/current_clocksource

The command can be executed without switching to the root console:

postgres@tantor:~$

sudo sh -c 'echo acpi_pm>/sys/devices/system/clocksource/clocksource0/current_clocksource'

10) Run the command:

postgres=# explain analyze select count(pk) from t;

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate (cost=1352.50..1352.51 rows=1 width=8) (actual time=805.557..805.573 rows=1 loops=1)

-> Seq Scan on t (cost=0.00..1190.20 rows=64920 width=8) (actual time=0.015..399.513 rows=100000 loops=1)

Planning Time: 0.148 ms

Execution Time: 805 . 792 ms

(4 rows)

Command execution time has been increased from 0.3 to 0.8 seconds.

For the duration of a simple explain command analyze was significantly affected by the change in time source.

11) Measure the execution time of the program:

postgres @ tantor :~$ time ./ clock _ timing

real 0m38.669s

user 0m16.329s

sys 0 m 22.331 s

The execution time has increased significantly - almost 3 times. The USER/SYS ratio has changed from 1:1 to 8:11. It can be seen that the USER/SYS ratio can be used to identify inefficient code. A skewed ratio towards SYS (deviation from the 60:40 ratio) indicates that the kernel code that is issuing the time is inefficient.

12 ) Run the pg_test_timing command :

postgres@tantor:~$ pg_test_timing 

Testing timing overhead for 3 seconds.

Per loop time including overhead: 3889.59 ns

Histogram of timing durations:

< us % of total count

     1 0.00389 30

2 0.00052 4

4 25.33700 195422

8 74.23528 572570

16 0.21470 1656

32 0.08376 646

64 0.10411 803

128 0.01776 137

256 0.00117 9

512 0.00013 1

1024 0.00052 4

2048 0.00052 4

4096 0.00039 3

8192 0.00000 0

16384 0.00026 2

The maximum distribution of the timestamp output speed with the acpi_pm time source was ~ 7 milliseconds, and with the tsc time source it was ~ 2.5 milliseconds.

13 ) Verify​ source time tsc :

root@tantor:~# echo tsc >/sys/devices/system/clocksource/clocksource0/current_clocksource

or

postgres@tantor:~$

sudo sh -c 'echo tsc >/sys/devices/system/clocksource/clocksource0/current_clocksource'

By default, the time source is selected by Linux every time it boots. There is a possibility that a slow time source will be selected arbitrarily. This can also happen after updating the Linux kernel. The probability is increased by: failure to disable power saving in BIOS, enabling Hyper Threading in BIOS, any hardware activity during the time source testing, for example, Intel ME activity. The probability of an arbitrary time source replacement is high on virtual machines, since the hardware load is uneven when loading a virtual machine. It is recommended to check which time source is used or to fix the desired time source in the Linux kernel boot parameters. An example of changing / boot / grub / grub . cfg is given in the theoretical part of the course. The fastest time source is tsc .

Part 7. Network connections

1) Open a root user terminal or switch to the root console:

postgres @ tantor :~$ su - 

Password : root

2) Check if the slow start algorithm after idle time is enabled:

root@tantor:~# sysctl -a | grep net.ipv4.tcp_slow_start_after_idle

net.ipv4.tcp_slow_start_after_idle = 1

The slow start algorithm after idle time is enabled. If the session was idle and there was no network traffic, then in networks with noticeable network latency ( network latency ) the data transfer rate will increase gradually, rather than immediately using the network bandwidth. The first three TCP packets are transmitted without delay. This is usually enough to transmit commands and small results. Most OLTP systems do not experience slowdowns, since the request and result fit into three network packets.

3) Look at what algorithm is used to control the load on the network data transmission channel:

root@tantor:~# sysctl -a | grep net.ipv4.tcp_congestion_control

net.ipv4.tcp_congestion_control = cubic

cubic algorithm is used . This is an algorithm that sets the volume of data transferred by measuring the loss of TCP packets, which is undesirable and leads to retransmission of packets. The probability of transferring large amounts of data, using a network with a large network delay is small, so the choice of algorithm is rarely paid attention to and the network is not a bottleneck for DBMS and their clients. However, if large amounts of data are transferred over a network channel, and this is streaming replication and backup, then their efficiency may be less than it could be.

4) Look at the parameters that determine keeping the socket open:

root@tantor:~# sysctl -a | grep keepalive

net.ipv4.tcp_keepalive_intvl = 75

net.ipv4.tcp_keepalive_probes = 9

net.ipv4.tcp_keepalive_time = 7200

These are default values and can be reduced.

In addition to these parameters, the values of the net.ipv4.tcp_retries2 parameter and the previously mentioned net.ipv4.tcp_slow_start_after_idle parameter are changed .

Part 8. Replacing the scheduling policy and checking the scheduler operation

1) Create a file to test the CPU load:

astra@tantor:~$ mcedit i.sh

#!/bin/sh

while true; do echo 1 > /dev/null ; done;

2) Check that the file contents have been saved successfully:

astra@tantor:~$ cat i.sh

#!/bin/sh

while true; do echo 1 > /dev/null ; done;

3) Launch processes equal to the number of virtual machine processor cores. An example is given for four cores:

astra@tantor:~$ nproc

4

astra @ tantor :~$ taskset ./ i . sh &

[2] 212239

astra@tantor:~$ taskset./i.sh &

[3] 212242

astra@tantor:~$ taskset./i.sh &

[4] 212243

astra@tantor:~$ taskset./i.sh &

[5] 212244

4) Two processes load both cores by 100%. This can be checked with the top utility.

Make sure that the pidstat utility is misleading, showing a small load:

root@tantor:~# pidstat -w | grep i.sh

06:58:52 AM 1000 212239 0.00 0.02 i.sh

06:58:52 AM 1000 212242 0.00 0.02 i.sh

06:58:52 AM 1000 212243 0.00 0.02 i.sh

06:58:52 AM 1000 212244 0.00 0.02 i . sh

5) Look at what the vmstat utility shows:

root@tantor:~# vmstat

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----

rb swpd free buff cache si so bi bo in cs us sy id wa st

 7 0 0 179276 271824 720548 0 0 2 3 75 78 2 0 98 0 0

vmstat utility shows no load and a small number of context switches.

Given that the number of switches is quite large.

Run the command at intervals of about one second:

astra@tantor:~$ grep ctxt /proc/22242/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 3841

astra@tantor:~$ grep ctxt /proc/22242/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 3945

Contexts are switched at a rate of 100 times per second.

6) Set the SCED_RR (Round Robin Scheduler) scheduler policy for the processes - the timeslice allocated to the processes is the same and equal to the value of the kernel.sched_rr_timeslice_ms parameter :

astra@tantor:~# sudo chrt -r -p 10 212242

astra@tantor:~# sudo chrt -r -p 10 212239

astra@tantor:~# sudo chrt -r -p 10 212243

astra@tantor:~# sudo chrt -r -p 10 212244

Processes with the SCHED_DEADLINE policy can preempt processes with SCHED_FIFO and SCHED_RR, that is, they have the highest priority.

7) Check the result of the command:

astra @ tantor :~$ chrt - p 212242

pid 212242's current scheduling policy: SCHED_RR

pid 212242's current scheduling priority: 10

8) Execute the command at intervals of about one second:

root@tantor:~# grep ctxt /proc/212233/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 27518

root@tantor:~# grep ctxt /proc/212233/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 27523

Contexts began to switch at a frequency of 10 times per second

9) Look at the RR scheduler timeslice :

astra @ tantor :~$ sudo sysctl - a | grep kernel . sched _ rr _ timeslice _ ms

kernel.sched_rr_timeslice_ms = 100

timeslice 100 milliseconds = 1/10 of a second, which corresponds to switching 10 times per second.

10) Set timeslice to one second:

astra@tantor:~# sudo sysctl kernel.sched_rr_timeslice_ms=1000

kernel.sched_rr_timeslice_ms = 1000

The frequency of context switches will not be more than 100 times per second regardless of the settings. This is correct, as it allows not to produce large overhead costs for the scheduler's work on context switching.

11) Check that the process contexts are c RR policy began to switch once per second. Run commands with an interval of once per second and see how the values of the forced context switch counter change:

root@tantor:~# grep ctxt /proc/212233/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 62348

root@tantor:~# grep ctxt /proc/212233/status

voluntary_ctxt_switches: 0

nonvoluntary_ctxt_switches: 62349

The values will increase by one at a rate of once per second.

12) Run the command and after about 20 seconds interrupt its execution by typing < ctrl + c >:

root@tantor:~# perf stat -p 212233

^C

Performance counter stats for process id '22381':

9,340.10 msec task-clock:u # 0.477 CPUs utilized

0 context-switches:u # 0.000 /sec                     

0 cpu-migrations:u # 0.000 /sec

0 page-faults:u # 0.000 /sec

<not supported> cycles:u

<not supported> instructions:u

<not supported> branches:u

<not supported> branch-misses:u

      19.595645993 seconds time elapsed

perf utility gives the correct data of zero arbitrary context switches per second . The number of forced switches, which are more valuable for diagnostics, is not shown, which is misleading.

The top utility shows the correct data with sufficient accuracy for a context switching frequency of 1 second: the load of processor cores reaches 100%. The total load of all cores %CPU is ~50% for each i.sh process.

13) Stop the processes:

astra @ tantor :~$ killall i . sh

[1]- Terminated taskset./i.sh

[2]+ Terminated taskset./i.sh

In this part of the practice, you learned how to test the result of changing the scheduler parameters. Using the example given, you can test the work of the schedulers SCHED _ OTHER , SCHED _ FIFO , SCHED _ DEADLINE .

SCHED_OTHER policy (and its derivatives SCHED_BATCH, SСHED_IDLE ) are not real-time policies. Real-time policies (for interactive tasks where responsiveness is important - getting timeslices with some frequency) include: SCHED_FIFO - preempted only by processes with higher priority or the SCHED_DEADLINE policy . Setting the SCHED_FIFO policy to a process results in the process occupying the core for a long time.

Practice for Chapter 4

Part 1. Disk subsystem parameters

1) Block devices are located in the /dev directory mounted on the devtmpfs virtual file system. See which devices from /dev are mounted:

root@tantor:~# mount | grep /dev 

udev on /dev type devtmpfs (rw,nosuid,relatime,size=1966296k,nr_inodes=491574,mode=755,inode64)

devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)

/dev/sda1 on / type ext4 (rw,relatime)

tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev,inode64)

hugetlbfs on /dev/hugepages type hugetlbfs (rw,relatime,pagesize=2M)

mqueue on /dev/mqueue type mqueue (rw,nosuid,nodev,noexec,relatime)

/dev contains files only for those devices that are currently available (connected). If a device is disconnected, the file is removed from /dev.

/ dev / sda is mounted in the virtual machine .

2) Look at the list of block devices:

root @ tantor :~# ls - l / dev | grep br

brw-rw---- 1 root disk 7, 0 loop0

brw-rw---- 1 root disk 7, 1 loop1

brw-rw---- 1 root disk 7, 2 loop2

brw-rw---- 1 root disk 7, 3 loop3

brw-rw---- 1 root disk 7, 4 loop4

brw-rw---- 1 root disk 7, 5 loop5

brw-rw---- 1 root disk 7, 6 loop6

brw-rw---- 1 root disk 7, 7 loop7

brw-rw---- 1 root disk 259, 0 nvme0n1

brw-rw---- 1 root disk 8, 0 sda

brw-rw---- 1 root disk 8, 1 sda1

brw-rw----+ 1 root cdrom 11, 0 sr0

The virtual machine has a virtual disk /dev/sda connected to it with one partition /dev/sda1.

The first letter b stands for block device. Instead of the file size, two numbers are given: the type and serial number (or operating mode) of the device.

3) Look at the contents of the / sys / dev / block directory :

root@tantor:~# ls -al /sys/dev/block

total 0

drwxr-xr-x 2 root root 0 .

drwxr-xr-x 4 root root 0 ..

lrwxrwxrwx 1 root root 0 11:0 -> ../../devices/pci0000:00/0000:00:01.1/ata2/host1/target1:0:0/1:0:0:0/block/sr0

lrwxrwxrwx 1 root root 0 259:0 -> ../../devices/pci0000:00/0000:00:0e.0/nvme/nvme0/nvme0n1

lrwxrwxrwx 1 root root 0 7:0 -> ../../devices/virtual/block/loop0

lrwxrwxrwx 1 root root 0 7:1 -> ../../devices/virtual/block/loop1

lrwxrwxrwx 1 root root 0 7:2 -> ../../devices/virtual/block/loop2

lrwxrwxrwx 1 root root 0 7:3 -> ../../devices/virtual/block/loop3

lrwxrwxrwx 1 root root 0 7:4 -> ../../devices/virtual/block/loop4

lrwxrwxrwx 1 root root 0 7:5 -> ../../devices/virtual/block/loop5

lrwxrwxrwx 1 root root 0 7:6 -> ../../devices/virtual/block/loop6

lrwxrwxrwx 1 root root 0 7:7 -> ../../devices/virtual/block/loop7

lrwxrwxrwx 1 root root 0 8:0 -> ../../devices/pci0000:00/0000:00:0d.0/ata1/host0/target0:0:0/0:0:0:0/block/sda

lrwxrwxrwx 1 root root 0 8:1 -> ../../devices/pci0000:00/0000:00:0d.0/ata1/host0/target0:0:0/0:0:0:0/block/sda/sda1

The directory contains symbolic links to block devices. The sda device is connected to the PCI bus via the ATA interface.

4) See what I/O scheduler is used with the sda device :

root@tantor:~# cat /sys/block/sda/queue/scheduler

[none] mq-deadline

root@tantor:~# cat /sys/dev/block/8:0/queue/scheduler

[none] mq-deadline

sda device uses a scheduler named none .

5) Do it next commands :

root@tantor:~# echo kyber > /sys/block/sda/queue/scheduler

root@tantor:~# cat /sys/dev/block/8:0/queue/scheduler

none mq-deadline [kyber]

root@tantor:~# echo bfq > /sys/block/sda/queue/scheduler

root@tantor:~# cat /sys/block/sda/queue/scheduler

none mq-deadline kyber [bfq]

root@tantor:~# echo abc > /sys/block/sda/queue/scheduler

-bash: echo: write error: Invalid argument

root@tantor:~# echo none > /sys/block/sda/queue/scheduler

root@tantor:~# cat /sys/block/sda/queue/scheduler

[none] mq-deadline kyber bfq

Teams change scheduler . Non-existent scheduler abc cannot be specified. Initially, not all available schedulers were shown, now they are.

6) Look at the contents of the file /etc/udev/rules.d/70-schedulerset.rules , which sets the parameters for selecting the I/O scheduler:

 root@tantor:~# cat /etc/udev/rules.d/70-schedulerset.rules

ACTION=="add|change", KERNEL=="sd[az]", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}=" none "

For all devices named sda .. sdz the scheduler is set to none .

7) View the physical sector size of the device:

root@tantor:~# lsblk -td

NAME ALIGNMENT MIN-IO OPT-IO PHY-SEC LOG-SEC ROTA SCHED RQ-SIZE RA WSAME

sda 0 512 0 512 512 0 none 32 128 0B

sr0 0 512 0 512 512 1 mq-deadline 2 128 0B

Linux thinks that the sector size of device sda is 512 bytes.

8) Look at the parameters with which the file system was mounted on the partition / dev / sda1 :

root@tantor:~# tune2fs -l /dev/sda1 | grep opt

Default mount options: user_xattr acl

root@tantor:~# mount | grep ext4

/dev/sda1 on / type ext4 (rw, relatime )

root@tantor:~# cat /etc/fstab | grep ext4

UUID=aca1a090-eba2-49ba-a8fc-ba12e9e2bf26 / ext4 defaults 1 1

The file system is mounted with default parameters . Parameters that determine the functionality of the file system can be stored in the partition itself (in the "superblock").

9) See the full list of parameters:

root @ tantor :~# cat / proc / fs / ext 4/ sda 1/ options

rw

bsddf

nogrpid

block_validity

dioread_nolock

nodiscard

delalloc

nowarn_on_error

journal_checksum

barrier

auto_da_alloc

user_xattr

acl

noquota

resuid=0

resgid=0

errors=continue

commit=5

min_batch_time=0

max_batch_time=15000

stripe=0

data=ordered

inode_readahead_blks=32

init_itable=10

max_dir_size_kb=0

10) Set the discard property in the file system superblock and verify that it is set:

root@tantor:~# tune2fs -o +discard /dev/sda1

tune2fs 1.47.0 (5-Feb-2023)

root@tantor:~# tune2fs -l /dev/sda1 | grep opt

Default mount options: user_xattr acl discard

discard property has been added to the options that were added .

discard option can be set:

in the filesystem superblock as the default mount option

in the file system mount options file - / etc / fstab

cryptsetup configuration - / etc / crypttab

in LVM configuration - /etc/lvm/lvm.conf​​​​​

in bootloader configuration - / boot / grub / grub . cfg

11) Check if the discard commands were sent by the operating system to the sda controller :

root@tantor:~# fstrim -v /

fstrim: /: the discard operation is not supported

root@tantor:~# lsblk --discard

NAME DISC-ALN DISC-GRAN DISC-MAX DISC-ZERO

sda 0 0B 0B 0

└─sda1 0 0B 0B 0

sr 0 0 0 B 0 B 0

Zeros in DISC-GRAN and DISC-MAX mean that discard was not used.

discard commands were sent.

In the virtual machine where the practices are run, partitions are files. Files take up space in the host file system, so discard is not used.

12) Look at the name of the package in which the fstrim utility was installed:

root@tantor:~# dpkg -S fstrim

util-linux:/sbin/fstrim

util-linux: /usr/share/man/man8/fstrim.8.gz

util-linux: /lib/systemd/system/fstrim.service

util-linux: /lib/systemd/system/fstrim.timer

util-linux: /usr/share/bash-completion/completions/fstrim

The package is called util - linux

Part 2. Installing packages in Astralinux

1) Launch a new terminal. In the astra user terminal, run the synaptic-pkexec utility:

astra @ tantor :~$ synaptic - pkexec

In the password entry window, enter astra and click OK

Synaptic is a graphical package manager for Astralinux .

2) In the Settings -> Repositories menu, make sure that the two checkboxes for the main and extended repositories are checked. Click OK.

3) In the menu Edit -> Search type fio . In the Look field in select Name . Click the Search button :

4) In the list of packages that appears, select the fio package . If the utility is installed (there is a green circle before the utility name), then skip this step. You can also install the uuid- runtime package if the uuid- runtime package is not installed.

If the fio utility is not installed, then select " Mark" from the drop-down menu for installation ". If the window does not close, click Mark. On the toolbar (three buttons under the menu), click Apply. In the window that appears, click Apply. If a window appears asking about Kernel update, click Next.

5) Close the Synaptic window by clicking the cross at the top right of the Synaptic application window.

6) In the astra user terminal window , type the command:

astra@tantor:~$ sudo apt install fio -y

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

fio is already the newest version (3.33-3+b1).

0 upgraded, 0 newly installed, 0 to remove and 636 not upgraded.

In the example, the command shows that the fio package is installed.

Close the astra user terminal by typing the key combination < ctrl + d >

7) Install nvme-cli and uuid-runtime packages:

root@tantor:~# apt install nvme-cli uuid-runtime -y

If a window with blue pseudo-graphics opens, click OK in the first window:

The window notifies you that you need to update all packages for which updates have been released.

You can remove notifications by uninstalling the apt - y package remove needrestart

The active element of the pseudographics windows is highlighted in red. To switch between the visual elements of the window, use the <TAB> key, the arrow keys on the keyboard, or the mouse.

The following window will open. Press the <TAB> key, the < OK > visual element will be highlighted in the window.

The uuidd service will be started without rebooting Linux and will prompt you to reboot the services that depend on what was done during package installation. You do not need to change the set of services. If the < OK > window element is highlighted in red, press the < ENTER > key on your keyboard or click the < OK > window element with your mouse .

The message about the need to re-login can be ignored and closed:

The nvme command line utility and the uuidd service were installed.

The nvme utility, installed in the nvme - cli package , is used to read the characteristics and metrics of NVMe devices. The utility is used to determine the state of memory chips, track service life, update firmware, erase contents, reinitialize, read error logs.

The uuidd package ensures that generated uuids are unique on multi-CPU hosts. It is installed because it is required to install nvme-cli.

nvme utility is used to obtain information about NVMe devices .

You have learned how to install packages in Astralinux.

Part 3. Working with SSD and testing disk performance with the fio utility

1) Run a few commands using the nvme utility :

root@tantor:~# nvme list

Node Generic SN           Model Namespace Usage Format FW Rev

------------ ---------- ------------ -------------------- --------- ----- ----------------------- --------

/dev/nvme0n1 /dev/ng0n1 VB1234-56789 ORCL-VBOX-NVME-VER12 1 8.59GB/8.59GB 512B + 0B 1.0

root@tantor:~# nvme list-subsys

nvme-subsys0 - NQN=nqn.2014.08.org.nvmexpress:80ee80eeVB1234-56789 ORCL-VBOX-NVME-VER12

\

+- nvme0 pcie 0000:00:0e.0 live

root@tantor:~# nvme id-ctrl /dev/nvme0n1 | head -6

NVME Identify Controller:

vid: 0x80ee

ssvid: 0x80ee

sn : VB1234-56789

mn : ORCL-VBOX-NVME-VER12

fr : 1.0

2) Look at the list of nvme devices :

root@tantor:~# ls /dev/nvm*

/dev/nvme0 /dev/nvme0n1 /dev/nvme-fabrics

There is one NVMe device.

3) Create a file system on the NVMe device:

root@tantor:~# mkfs -E discard /dev/nvme0n1

mke2fs 1.47.0 (5-Feb-2023)

Creating filesystem with 2097152 4k blocks and 524288 inodes

Filesystem UUID: 0c0f68b7-d1d9-47ff-908c-9831503837f3

Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Allocating group tables: done

Writing inode tables: done

Writing superblocks and filesystem accounting information: done

The option is used by default. It is given to emphasize the importance of cleaning the contents of the SSD. In the description of the command ( man mkfs . ext 4 ) specifies that the discard option sends a command to the device controller to clear the contents:

Attempt to discard blocks at mkfs time (discarding blocks initially is useful on solid state devices and sparse / thin-provisioned storage). When the device advertises that discard also zeroes data (any subsequent read after the discard and before write returns zero), then mark all not-yet-zeroed inode tables as zeroed. This significantly speeds up file system initialization. This is set as default.

Please note that the log was not created, the line is missing:

Creating journal (16384 blocks): done

4) Change the file system superblock to include the discard option:

root@tantor:~# tune2fs -o +discard /dev/nvme0n1

tune 2 fs 1.47.0 (5- Feb -2023)

5) Create a directory /u01 , mount the created file system in it, see what size the mounted partition is:

root@tantor:~# mkdir /u01

root@tantor:~# mount /dev/nvme0n1 /u01

root@tantor:/# df -Th | grep "^/dev"

/dev/sda1 ext4 47G 16G 29G 36% /

/dev/nvme0n1 ext2 7.9G 24K 7.5G 1% /u01

The partition is mounted in /u01 , its size is 8GB, file system is ext2 .

6) In the root user terminal , run the command:

root@tantor:~# fio --ioengine=psync --filename=/tmp/test --size=1G --time_based --name=fio --group_reporting --runtime=10 --direct=1 --sync=1 --iodepth=1 --rw=read --bs=8k --numjobs=1

fio: (g=0): rw=read, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1

fio-3.33

Starting 1 process

fio: Laying out IO file (1 file / 1024MiB)

Jobs: 1 (f=1): [R(1)][100.0%][r=36.4MiB/s][r=4664 IOPS][eta 00m:00s]

fio: (groupid=0, jobs=1): err= 0: pid=7432: Mon Dec 9 00:07:59 2024

read: IOPS= 4682 , BW=36.6MiB/s ( 38.4MB/s )(366MiB/10001msec)

clat (usec): min=92, max=8607, avg=207.12, stdev=306.94

lat (usec): min=93, max=8608, avg=208.56, stdev=306.95

clat percentiles (usec):

| 1.00th=[ 143], 5.00th=[ 149], 10.00th=[ 151], 20.00th=[ 155],

| 30.00th=[ 157], 40.00th=[ 159], 50.00th=[ 159], 60.00th=[ 161],

| 70.00th=[ 165], 80.00th=[ 172], 90.00th=[ 237], 95.00th=[ 253],

| 99.00th=[ 2245], 99.50th=[ 2900], 99.90th=[ 3490], 99.95th=[ 3654],

| 99.99th=[ 3982]

bw ( KiB/s): min=33213, max=40688, per=100.00%, avg=37505.37, stdev=1932.35, samples=19

iops : min= 4151, max= 5086, avg=4687.95, stdev=241.58, samples=19

lat (usec) : 100=0.01%, 250=94.32%, 500=3.66%, 750=0.61%, 1000=0.03%

lat (msec) : 2=0.15%, 4=1.22%, 10=0.01%

cpu : usr=12.33%, sys=11.35% , ctx=46830, majf=0, minf=14

IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%

submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%

complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%

issued rwts: total=46827,0,0,0 short=0,0,0,0 dropped=0,0,0,0

latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):

READ: bw=36.6MiB/s ( 38.4MB/s ), 36.6MiB/s-36.6MiB/s (38.4MB/s-38.4MB/s), io=366MiB (384MB), run=10001-10001msec

Disk stats (read/write):

sda: ios=45651/4, merge=0/1, ticks=8268/7, in_queue=8282, util=97.89%

The test results are highlighted in red. The rest of the verbose output does not require attention and is used in rare cases.

Test parameters:

PostgreSQL's work with the file system corresponds to -- ioengine = psync

PostgreSQL block size -- bs =8 k

The queue size ( iodepth ) is set to 1 because direct=1 is selected.

The test performed only reading from a file. Test result: maximum number of input/output operations IOPS= 4682 , average speed 38.4MB/s .

7) Test it NVMe partition :

root@tantor:~# fio --ioengine=psync --filename=/u01/test --size=1G --time_based --name=fio --group_reporting --runtime=10 --direct=1 --sync=1 --iodepth=1 --rw=read --bs=8k --numjobs=1

fio: (g=0): rw=read, bs=(R) 8192B-8192B, (W) 8192B-8192B, (T) 8192B-8192B, ioengine=psync, iodepth=1

fio-3.33

Starting 1 process

fio: Laying out IO file (1 file / 1024MiB)

Jobs: 1 (f=1): [R(1)][100.0%][r=43.1MiB/s][r=5520 IOPS][eta 00m:00s]

fio: (groupid=0, jobs=1): err= 0: pid=7441: Mon Dec 9 00:08:59 2024

read: IOPS= 5312 , BW=41.5MiB/s ( 43.5MB/s )(415MiB/10001msec)

clat (usec): min=77, max=11930, avg=181.67, stdev=300.00

lat (usec): min=78, max=11932, avg=183.12, stdev=300.04

clat percentiles (usec):

| 1.00th=[ 128], 5.00th=[ 133], 10.00th=[ 137], 20.00th=[ 139],

| 30.00th=[ 141], 40.00th=[ 143], 50.00th=[ 145], 60.00th=[ 147],

| 70.00th=[ 149], 80.00th=[ 151], 90.00th=[ 167], 95.00th=[ 229],

| 99.00th=[ 2147], 99.50th=[ 2835], 99.90th=[ 3523], 99.95th=[ 3752],

| 99.99th=[ 5932]

bw ( KiB/s): min=38848, max=43936, per=99.80%, avg=42413.47, stdev=1514.01, samples=19

iops : min= 4856, max= 5492, avg=5301.68, stdev=189.25, samples=19

lat (usec) : 100=0.11%, 250=97.55%, 500=0.99%, 750=0.12%, 1000=0.08%

lat (msec) : 2=0.08%, 4=1.04%, 10=0.02%, 20=0.01%

cpu : usr=12.13%, sys=12.54% , ctx=53130, majf=0, minf=15

IO depths : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%

submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%

complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%

issued rwts: total=53128,0,0,0 short=0,0,0,0 dropped=0,0,0,0

latency : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):

READ: bw=41.5MiB/s ( 43.5MB/s ), 41.5MiB/s-41.5MiB/s (43.5MB/s-43.5MB/s), io=415MiB (435MB), run=10001-10001msec