# Virtual Machines (Compute Cloud) ## Создание виртуальной машины: name vm: otus-db-pg-vm-1 disk: SSD 20 2CPU x 8MEM Цена: 3 370 ₽ / месяц 3 540 ₽ ## Подключение к VM: ```bash cd ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo chmod 400 vk_cloud_otus.pem ssh -i vk_cloud_otus.pem ubuntu@212.233.123.246 sudo su ``` ## Установка Postgres (Ubuntu 22.04): ```bash sudo apt update \ && sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' \ && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - \ && sudo apt update \ && sudo apt -y install postgresql-15 \ && sudo apt -y install unzip mc nano sudo -u postgres psql -c "SELECT version();" pg_lsclusters ``` ## Установить пароль для Postgres: ```bash sudo -u postgres psql \password #123 \q ``` ## Добавить сетевые правила для подключения к Postgres: ```bash cd /etc/postgresql/15/main/ sudo nano /etc/postgresql/15/main/postgresql.conf #listen_addresses = 'localhost' listen_addresses = '*' sudo nano /etc/postgresql/15/main/pg_hba.conf #host all all 127.0.0.1/32 md5 password scram-sha-256 host all all 0.0.0.0/0 scram-sha-256 sudo pg_ctlcluster 15 main restart pg_lsclusters ``` ## Подключение к Postgres: ```bash cd ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo PGPASSWORD=123 psql -h212.233.123.246 -p5432 -Upostgres -dpostgres \c postgres \l+ \du \dt \dt+ \d table \q exit ``` ## Тестирование (pgbench) ### inizialization export PGPASSWORD=123 psql -h212.233.123.246 -p5432 -Upostgres -dpostgres -c "CREATE DATABASE benchmark;" pgbench -h212.233.123.246 -p5432 -Upostgres -i -s 15 benchmark -- 1 500 000 rows ### run test pgbench -h212.233.123.246 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark -- 3 минуты # Cloud Database for PostgreSQL ## Создание Name: otus-db-pg-cd-1 Максимальный размер диска SSD - 5120 GB Максимальный размер диска High-IOPS SSD - 2048 GB Версия: 13 (для обновления) Цена: 4 415.60 ₽ / месяц Имя базы данных для создания: otus-db Имя пользователя: otus Пароль пользователя: R4R829D08BmK2kB6- ## Подключение ```bash cd ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo export PGPASSWORD=R4R829D08BmK2kB6- psql -h212.233.76.209 -p5432 -Uotus -dotus-db ``` ## CLI && API https://mcs.mail.ru/docs/ru/additionals/account/project/api/apiendpoints cd ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo source openrc.sh openstack flavor list openstack project list https://mcs.mail.ru/docs/ru/manage/tools-for-using-services/rest-api/case-keystone-token# openstack token issue -c id -f value 213423423143 curl https://infra.mail.ru:9696/v2.0/networks -H "Accept: application/json" -H "X-Auth-Token: 213423423143" | jq ## Обновление версии: https://mcs.mail.ru/docs/ru/dbs/dbaas/instructions/postgre-update# curl --location --request PATCH 'https://infra.mail.ru:8779/v1.0/1ba1bae54ee9476788a09562a02d0906/instances/886ba701-b11a-4e40-b24b-cb985c322b66' \ --header 'X-Auth-Token: 213423423143' \ --header 'Content-Type: application/json' \ -d '{ "instance":{ "datastore_version": "14" } }' ## Тестирование (pgbench) ### inizialization export PGPASSWORD=R4R829D08BmK2kB6- psql -h212.233.76.209 -p5432 -Uotus -dotus-db -c "CREATE DATABASE benchmark;" pgbench -h212.233.76.209 -p5432 -Uotus -i -s 15 benchmark -- 1 500 000 rows ### run test pgbench -h212.233.76.209 -p5432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark -- 3 минуты ### extensions - PostgreSQL Server Exporter curl http://212.233.76.209:9100/metrics ### Create Replica select * from pg_stat_activity \gx select * from pg_stat_replication; select * from pg_replication_slots; ```bash cd ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo export PGPASSWORD=R4R829D08BmK2kB6- psql -h79.137.175.239 -p5432 -Uotus -dotus-db ``` ## Резервное копирование psql -h212.233.76.209 -p5432 -Uotus -dbenchmark -c "select count(*) from pgbench_history;" pg_dump -h212.233.76.209 -p5432 -Uotus -dbenchmark --table=pgbench_history > ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/pg_dump.sql less ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/pg_dump.sql mkdir -p ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/pg_basebackup pg_basebackup -h212.233.76.209 -p5432 -Uotus -R -D ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/pg_basebackup pg_basebackup -h212.233.76.209 -p5432 -Uotus --no-slot -R -D ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/pg_basebackup psql -h212.233.76.209 -p5432 -Uotus -dotus-db -c "CREATE USER replica WITH REPLICATION encrypted password 'test123'"; psql -h212.233.76.209 -p5432 -Uotus -dotus-db -c "\du+"; export PGPASSWORD=R4R829D08BmK2kB6- psql -h212.233.76.209 -p5432 -Uotus -dotus-db ssh -i ~/Otus/PostgreSQL_Cloud/PG_VK_Cloud/demo/vk_cloud_otus.pem admin@212.233.76.209 psql -Uos_admin \du ALTER USER otus WITH CREATEDB CREATEROLE REPLICATION; SHOW hba_file; mkdir -p /home/admin/pg_basebackup pg_basebackup -hlocalhost -p5432 -Uotus -R -D /home/admin/pg_basebackup cat /var/lib/postgresql/13/data/pg_hba.conf ```sql -- SELECT * FROM pg_replication_slots \gx SELECT * FROM pg_stat_replication \gx select * from pg_settings where name like '%port%'; CREATE TABLE test(s text); INSERT INTO test SELECT 'Just a line' FROM generate_series(1,100000); SELECT *, pg_current_wal_lsn() from pg_stat_replication \gx select count(*) from test; INSERT INTO test VALUES ('Replica'); ``` ============================================================================================================================= # Test Outputs 202307 ## VM vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.123.246 -p5432 -Upostgres -i -s 15 benchmark 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)... 1500000 of 1500000 tuples (100%) done (elapsed 1.75 s, remaining 0.00 s) vacuuming... creating primary keys... done in 9.23 s (drop tables 0.03 s, create tables 0.15 s, client-side generate 7.89 s, vacuum 0.32 s, primary keys 0.84 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.123.246 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3) starting vacuum...end. progress: 60.0 s, 125.4 tps, lat 350.568 ms stddev 150.553, 0 failed progress: 120.0 s, 145.7 tps, lat 342.774 ms stddev 141.907, 0 failed progress: 180.0 s, 144.4 tps, lat 346.329 ms stddev 149.950, 0 failed transaction type: scaling factor: 15 query mode: simple number of clients: 50 number of threads: 2 maximum number of tries: 1 duration: 180 s number of transactions actually processed: 24984 number of failed transactions: 0 (0.000%) latency average = 346.372 ms latency stddev = 147.319 ms initial connection time = 7066.476 ms tps = 144.142656 (without initial connection time) ## DBaaS vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.76.209 -p5432 -Uotus -i -s 15 benchmark 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)... 1500000 of 1500000 tuples (100%) done (elapsed 1.59 s, remaining 0.00 s) vacuuming... creating primary keys... done in 10.03 s (drop tables 0.04 s, create tables 0.16 s, client-side generate 8.56 s, vacuum 0.35 s, primary keys 0.92 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.76.209 -p5432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3, server 14.8) starting vacuum...end. progress: 60.0 s, 138.9 tps, lat 336.225 ms stddev 127.566, 0 failed progress: 120.0 s, 147.0 tps, lat 340.085 ms stddev 138.413, 0 failed progress: 180.0 s, 142.7 tps, lat 350.604 ms stddev 161.622, 0 failed transaction type: scaling factor: 15 query mode: simple number of clients: 50 number of threads: 2 maximum number of tries: 1 duration: 180 s number of transactions actually processed: 25763 number of failed transactions: 0 (0.000%) latency average = 342.554 ms latency stddev = 143.773 ms initial connection time = 3738.647 ms tps = 145.442527 (without initial connection time) ============================================================================================================================= 2CPU x 8MEM ubuntu@otus-db-pg-vm-1:~$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Address sizes: 46 bits physical, 48 bits virtual Byte Order: Little Endian CPU(s): 2 On-line CPU(s) list: 0,1 Vendor ID: GenuineIntel Model name: Intel Xeon Processor (Skylake, IBRS) CPU family: 6 Model: 85 Thread(s) per core: 1 Core(s) per socket: 2 Socket(s): 1 Stepping: 4 BogoMIPS: 4589.21 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology cpuid tsc_known_f req pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ibrs ibpb fsgsbase bmi1 hle avx2 smep bmi2 erms invpcid rtm avx512f avx512dq rdseed adx smap clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat Virtualization features: Hypervisor vendor: KVM Virtualization type: full Caches (sum of all): L1d: 64 KiB (2 instances) L1i: 64 KiB (2 instances) L2: 8 MiB (2 instances) L3: 16 MiB (1 instance) NUMA: NUMA node(s): 1 NUMA node0 CPU(s): 0,1 Vulnerabilities: Itlb multihit: KVM: Mitigation: VMX unsupported L1tf: Mitigation; PTE Inversion Mds: Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown Meltdown: Mitigation; PTI Mmio stale data: Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown Retbleed: Mitigation; IBRS Spec store bypass: Vulnerable Spectre v1: Mitigation; usercopy/swapgs barriers and __user pointer sanitization Spectre v2: Mitigation; IBRS, IBPB conditional, RSB filling Srbds: Not affected Tsx async abort: Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown ============================================================================================================================= # Test Outputs 202311 ## VM pgbench -h212.233.123.246 -p5432 -Upostgres -i -s 15 benchmark 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)... 1500000 of 1500000 tuples (100%) done (elapsed 2.14 s, remaining 0.00 s) vacuuming... creating primary keys... done in 11.20 s (drop tables 0.04 s, create tables 0.18 s, client-side generate 9.70 s, vacuum 0.36 s, primary keys 0.92 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.123.246 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3, server 15.5 (Ubuntu 15.5-1.pgdg22.04+1)) starting vacuum...end. progress: 60.0 s, 115.1 tps, lat 381.327 ms stddev 173.832, 0 failed progress: 120.0 s, 133.0 tps, lat 376.058 ms stddev 168.660, 0 failed progress: 180.0 s, 129.4 tps, lat 386.265 ms stddev 175.007, 0 failed transaction type: scaling factor: 15 query mode: simple number of clients: 50 number of threads: 2 maximum number of tries: 1 duration: 180 s number of transactions actually processed: 22695 number of failed transactions: 0 (0.000%) latency average = 381.477 ms latency stddev = 172.850 ms initial connection time = 7134.118 ms tps = 130.740724 (without initial connection time) ## DBaaS pgbench -h212.233.76.209 -p5432 -Uotus -i -s 15 benchmark 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)... 1500000 of 1500000 tuples (100%) done (elapsed 2.09 s, remaining 0.00 s) vacuuming... creating primary keys... done in 13.47 s (drop tables 0.05 s, create tables 0.22 s, client-side generate 11.63 s, vacuum 0.43 s, primary keys 1.13 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h212.233.76.209 -p5432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3, server 14.10) starting vacuum...end. progress: 60.0 s, 100.2 tps, lat 380.024 ms stddev 152.498, 0 failed progress: 120.0 s, 128.8 tps, lat 388.433 ms stddev 184.280, 0 failed progress: 180.0 s, 133.7 tps, lat 373.744 ms stddev 161.935, 0 failed transaction type: scaling factor: 15 query mode: simple number of clients: 50 number of threads: 2 maximum number of tries: 1 duration: 180 s number of transactions actually processed: 21812 number of failed transactions: 0 (0.000%) latency average = 380.874 ms latency stddev = 167.981 ms initial connection time = 14067.800 ms tps = 130.922939 (without initial connection time) ============================================================================================================================= # Links https://platformv.sbertech.ru/products/pangolin https://docs.arenadata.io/ru/ADB/current/introduction/intro.html