otus pg notes

exam.md

Final HW

Slides

Решаем две задачи: с одной стороны отказоустойчивый и масштабируемый класстер на базе cockroachdb, а с другой, авто генерируемая админка к его табличкам

TLDR

Для запуска необходимо выполнить

docker-compose up

Как все запуститься, инициализировать базу

docker exec -it db ./cockroach workload init movr

После чего можно открывать в браузере localhost:3000

Как все устроенно

В backend у нас есть милдвари, ф-ии возвращающие ф-ии, на вход принимают искомую табличку, на выходе - обработчик для базовых crud операций

Благодаря этому добавление новой таблички сводиться к условному:

app.get('/users/:id', fields(['id', 'city', 'name', 'address', 'credit_card']), single('users'))
app.delete('/users/:id', remove('users'))
app.post('/users', fields(['id', 'city', 'name', 'address', 'credit_card']), create('users'))
app.get('/users', limit, offset, fields(['id', 'city', 'name', 'address', 'credit_card']), list('users'))

Имея такую заготовку мы выносим в файл конфига информацию о табличках:

pakcage.json

{
  ...
  "tables": {
    "users": ["id", "city", "name", "address", "credit_card"],
    "vehicles": ["id", "city", "type", "owner_id", "creation_time", "status", "current_location"],
    "rides": ["id", "city", "vehicle_city", "rider_id", "start_address", "end_address", "start_time", "end_time", "revenue"]
  }
  ...
}

Что позволяет в backend сделать вот так:

for(const [table, allowedFields] of Object.entries(tables)) {
    app.get(`/${table}/:id`, fields(allowedFields), single(table))
    app.delete(`/${table}/:id`, remove(table))
    app.post(`/${table}`, fields(allowedFields), create(table))
    app.get(`/${table}`, limit, offset, fields(allowedFields), list(table))
}

Теперь добавление новой таблички сводиться к ее добавлению в конфиг

Дальше эту же информацию мы отдаем на frontend и уже там рисуем менюшку и саму табличку

Поскольку мы знаем колонки, табличка на лету пересрисовывается и подстраиватся

Под капотом:

docker-compose.yml

version: "3.9"
services:
  app:
    container_name: app
    build: .
    ports:
      - 3000:3000
    environment:
      PORT: "3000"
      CONNECTION_STRING: postgresql://root@db:26257/movr?sslmode=disable
    depends_on:
      - db
  db:
    container_name: db
    image: cockroachdb/cockroach:v21.1.7
    command: 
      - start-single-node
      - --insecure
    ports:
      - 26257:26257
      - 8080:8080

Dockerfile

FROM node:14-alpine

WORKDIR /app

COPY package*.json .
RUN npm install

COPY index.js .
COPY index.html .
COPY index.jsx .

ENV CONNECTION_STRING=postgresql://root@db:26257/movr?sslmode=disable
ENV PORT=3000

EXPOSE 3000

ENTRYPOINT [ "node" ]
CMD [ "index.js" ]

# docker build -t app .
# docker run -it --rm -p 3000:3000 --link=db app

Backend

index.js

const {tables} = require('./package.json')
const express = require('express')
const { Pool } = require('pg')

const pool = new Pool({connectionString: process.env.CONNECTION_STRING || 'postgresql://root@localhost:26257/movr?sslmode=disable'})

const app = express()

app.use(express.json())
app.use(express.static('.'))

const limit = (req, _, next) => {
    const { query: { limit = '10' } } = req
    req.limit = isNaN(parseInt(limit)) || parseInt(limit) > 10 ? 10 : parseInt(limit)
    next()
}

const offset = (req, _, next) => {
    const { query: { offset = '0' } } = req
    req.offset = isNaN(parseInt(offset)) ? 0 : parseInt(offset)
    next()
}

const fields = allowedFields => (req, _, next) => {
    let { query: { fields = allowedFields } } = req
    req.allowedFields = allowedFields
    req.fields = Array.isArray(fields) ? fields : [fields]
    req.fields = req.fields.filter(f => allowedFields.includes(f))
    next()
}

const single = table => async (req, res) => {
    const { params: { id } } = req
    const {fields} = req
    const {rows} = await pool.query(`select ${fields.join(',')} from ${table} WHERE id = $1`, [id])
    res.json(rows.shift())
}

const remove = table => async (req, res) => {
    const { params: { id } } = req
    await pool.query(`delete from ${table} WHERE id = $1`, [id])
    res.sendStatus(200)
}

const create = table => async (req, res) => {
    const {fields, allowedFields} = req
    const {rows} = await pool.query(`insert into ${table}(${allowedFields.join(',')}) values (gen_random_uuid(), ${allowedFields.filter(f => f !== 'id').map((_, i) => `\$${i+1}`).join(',')}) RETURNING ${fields.join(',')}`, allowedFields.filter(f => f !== 'id').map(f => req.body[f]))
    res.json(rows.shift())
}

const list = table => async (req, res) => {
    const {limit, offset, fields} = req
    const {rows} = await pool.query(`select ${fields.join(',')} from ${table} LIMIT $1 OFFSET $2`, [limit, offset])
    res.json(rows)
}

// app.get('/users/:id', fields(['id', 'city', 'name', 'address', 'credit_card']), single('users'))
// app.delete('/users/:id', remove('users'))
// app.post('/users', fields(['id', 'city', 'name', 'address', 'credit_card']), create('users'))
// app.get('/users', limit, offset, fields(['id', 'city', 'name', 'address', 'credit_card']), list('users'))

for(const [table, allowedFields] of Object.entries(tables)) {
    app.get(`/${table}/:id`, fields(allowedFields), single(table))
    app.delete(`/${table}/:id`, remove(table))
    app.post(`/${table}`, fields(allowedFields), create(table))
    app.get(`/${table}`, limit, offset, fields(allowedFields), list(table))
}

app.get('/tables', (_, res) => res.json(tables))

app.listen(process.env.PORT || 3000)

Frontend

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
    <title>demo</title>
</head>
<body>
    <div id="root"></div>
    <script src="https://unpkg.com/babel-standalone/babel.js"></script>
    <script crossorigin src="https://unpkg.com/react@17/umd/react.development.js"></script>
    <script crossorigin src="https://unpkg.com/react-dom@17/umd/react-dom.development.js"></script>
    <script type="text/babel" src="index.jsx"></script>
</body>
</html>

index.jsx

const MenuItem = ({table, currentTable,  setCurrentTable}) => <li className="nav-item">
    <a className={currentTable === table ? 'nav-link active' : 'nav-link'} onClick={() => setCurrentTable(table)}>{table}</a>
</li>

const MenuItemLoading = () => <li className="nav-item px-3"><span className="fs-4">loading...</span></li>

const Header = ({tables, currentTable, setCurrentTable}) => <div className="container">
    <header className="d-flex justify-content-center py-3">
        <ul className="nav nav-pills">
            <li className="nav-item px-3">
                <span className="fs-4">MovR</span>
            </li>
            {tables ? Object.keys(tables).map(table => <MenuItem key={table} table={table} currentTable={currentTable} setCurrentTable={setCurrentTable} />) : <MenuItemLoading />}
        </ul>
    </header>
</div>

function Table({currentTable, allowedFields}) {
    const [limit, setLimit] = React.useState(10)
    const [offset, setOffset] = React.useState(0)
    const [rows, setRows] = React.useState([])

    React.useEffect(async () => {
        const data = await fetch(`/${currentTable}?limit=${limit}&offset=${offset}`).then(r => r.json())
        console.log(data)
        setRows(data)
    }, [currentTable, limit, offset])

    const submit = e => {
        e.preventDefault()
        setLimit(e.target.elements.limit.value)
        setOffset(e.target.elements.offset.value)
    }

    const remove = async id => {
        await fetch(`/${currentTable}/${id}`, {method: 'DELETE'})
        setRows(rows.filter(row => row.id !== id))
    }

    const create = async e => {
        e.preventDefault()
        
        const body = allowedFields
            .filter(f => f !== 'id')
            .map(f => ({k: f, v: e.target.elements[f].value}))
            .reduce((acc, x) => Object.assign(acc, {[x.k]: x.v}), {})

        const item = await fetch(`/${currentTable}`, {method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify(body)}).then(r => r.json())
        setRows([item].concat(rows))
        console.log('created', item)
        window.scrollTo(0, 0)
    }

    return <div className="container">
        <form className="row mb-2" onSubmit={e => submit(e)}>
            <div className="col-auto">
                <label className="col-form-label">limit</label>
            </div>
            <div className="col-auto">
                <input className="form-control" name="limit" type="number" min="1" max="100" defaultValue={limit} />
            </div>
            <div className="col-auto">
                <label className="col-form-label">offset</label>
            </div>
            <div className="col-auto">
                <input className="form-control" name="offset" type="number" min="0" defaultValue={offset} />
            </div>
            <div className="col-auto">
                <input className="form-control" type="submit" value="Submit" />
            </div>
        </form>
        <table className="table">
            <thead className="table-dark">
                <tr>
                    {allowedFields.map(f => <th key={f}>{f}</th>)}
                    <th />
                </tr>
            </thead>
            <tbody>
                {rows.map(row => <tr key={row.id}>
                    {allowedFields.map(f => <td key={row.id+f}>{row[f]}</td>)}
                    <td key="actions">
                        <button className="btn btn-sm btn-danger" onClick={() => remove(row.id)}>&times;</button>
                    </td>
                </tr>)}
            </tbody>
        </table>
        <form onSubmit={create}>
            {allowedFields.filter(f => f !== 'id').map(f => <div key={f} className="mb-3">
                <label className="form-label">{f}</label>
                <input type="text" className="form-control" name={f} />
            </div>)}
            <button type="submit" className="btn btn-primary">Create</button>
        </form>
    </div>
}

function App() {
    const [currentTable, setCurrentTable] = React.useState('users')
    const [tables, setTables] = React.useState(null)

    React.useEffect(async () => {
        const data = await fetch('/tables').then(r => r.json())
        console.log(data)
        setTables(data)
    }, [setTables])

    return <main>
        <Header tables={tables} currentTable={currentTable} setCurrentTable={setCurrentTable} />
        <Table allowedFields={tables ? tables[currentTable] : []} currentTable={currentTable} />
    </main>
}

ReactDOM.render(<App />, root)

В кубере соотв это будет деплоймент приложеньки и сам cockroach

kubectl apply -f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/master/config/crd/bases/crdb.cockroachlabs.com_crdbclusters.yaml
operator
kubectl apply -f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/master/manifests/operator.yaml

apiVersion: crdb.cockroachlabs.com/v1alpha1
kind: CrdbCluster
metadata:
  name: cockroachdb
spec:
  dataStore:
    pvc:
      spec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: "100Gi"
        volumeMode: Filesystem
  resources:
    requests:
      cpu: "3"
      memory: "12Gi"
    limits:
      cpu: "3"
      memory: "12Gi"
  tlsEnabled: true
  image:
    name: cockroachdb/cockroach:v21.1.5
  nodes: 3
  additionalLabels:
    crdb: otus

hw1.md

gcp: pgotus

add project editor to ***@yandex.ru ✅

создан шаблон для виртуальных машин из которого создана db1

примечания:

• установка postgres зашита в startup script шаблона, соотв можно быстренько развернуть кластер
• включеная опция preemtible - для таких машин самое то, а цена вдвое меньше
• ssh ключ добавлен на уровне проекта, дабы не добавлять его каждый раз

шаги в самой виртуалке:

запустить везде psql из под пользователя postgres: sudo -u postgres psql

выключить auto commit - \set AUTOCOMMIT OFF

текущий уровень изоляции: read committed

видите ли вы новую запись - нет, т.к. первая сессия еще не закомитила изменения

после коммита в первой сессии запись видна - т.к. текущий уровень изоляции read committed

в repeatable read запись во второй сессии не видна ни во время транзакции ни после ее комита т.к. именно в этом суть этого уровня изоляции

hw10.md

Not so big data

TLRD:

• по скольку у нас тут не совсем большая дата, но и сервер малюсенький, результат интересный, после добавления индекса запрос выполняется менее 8 сек
• любопытный факт mysql из коробки дал приблизительно такой же результат как и затюненная постргря

Export

Экспорт делает csv где то по 255мб каждый, соотв для 10гб нам понадоибиться первых 40 файлов, а остальные можно удалять

Команда gsutil du gs://otus-hw10 распечатает размер и название файла, осталось сложить это в файл и написать скрипт который посчитает сколько точно файлов нам понадобиться

gsutil du gs://otus-hw10 > files.txt

with open('files.txt') as f:
    lines = f.readlines()
    lines = [x.strip() for x in lines]

    gb = 1024 * 1024 * 1024
    max = 10 * gb
    total = 0
    files = []
    for line in lines:
        size, file = line.split()
        size = int(size)
        if total + size <= max:
            files.append(file)
            total += size
            print(file, size)

    print(total / gb)

все файлы больше 40-го удаляем дабы за зря не платить за хранение

for i in $(seq 40 292); do gsutil rm gs://otus-hw10/taxt-*$i.csv; done

примечание: поленился написать по нормальному в скрипте ошибка, под звездочку попадают файлы 1хх и 2хх, как следствие удаляется сразу 40, 140 и 240 из за чего под конец насыпал варнингов, но не суть, файлы почистили

дальше нам эти файлы нужно объединить в один, но при этом есть одна сложность - в csv зашит заголовок от которого нам надо избавиться, делаем следующий финт ушами:

for i in $(gsutil ls gs://otus-hw10)
do
    gsutil cp $i .
    tail -n +2 *.csv >> data.csv
    rm taxt-*.csv
done

gsutil cp data.csv gs://otus-hw10/
gsutil rm gs://otus-hw10/taxt-*.csv

примечание: почистил за собой storage и сложил финальный файл, т.к. он будет использоваться для другой базы

Postgres

Перед непосредственной вставкой небольшой тюнинг:

echo "
# DB Version: 13
# OS Type: linux
# DB Type: dw
# Total Memory (RAM): 4 GB
# CPUs num: 2
# Connections num: 20
# Data Storage: ssd

max_connections = 20
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
max_parallel_maintenance_workers = 1
" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

sudo systemctl restart postgresql

sudo -u postgres psql -c "CREATE DATABASE demo"

sudo -u postgres psql demo -c "create table taxi_trips (
unique_key text,
taxi_id text,
trip_start_timestamp TIMESTAMP,
trip_end_timestamp TIMESTAMP,
trip_seconds bigint,
trip_miles numeric,
pickup_census_tract bigint,
dropoff_census_tract bigint,
pickup_community_area bigint,
dropoff_community_area bigint,
fare numeric,
tips numeric,
tolls numeric,
extras numeric,
trip_total numeric,
payment_type text,
company text,
pickup_latitude numeric,
pickup_longitude numeric,
pickup_location text,
dropoff_latitude numeric,
dropoff_longitude numeric,
dropoff_location text
)"

sudo -u postgres psql demo -c "\timing" -c "COPY taxi_trips FROM '/home/marchenko_alexandr/data.csv' (FORMAT csv, null '', DELIMITER ',')"
# COPY 26727331
# Time: 418889.519 ms (06:58.890)

sudo -u postgres psql demo -c "\timing" -c "VACUUM ANALYZE"
# Time: 235911.304 ms (03:55.911)

sudo -u postgres psql demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(trip_total)*100, 0) + 0 as tips_percent, count(*) as c
FROM taxi_trips
group by payment_type
order by 3;"
# Time: 198781.817 ms (03:18.782)

payment_type	tips_percent	c
Way2ride	6	9
Split	17	178
Prepaid	0	944
Pcard	2	4052
Dispute	0	6348
No Charge	4	75600
Mobile	15	137300
Unknown	0	178979
Prcard	1	230535
Credit Card	17	11199714
Cash	0	14893672

Интересно что будет если сделать индекс

sudo -u postgres psql demo -c "\timing" -c "CREATE INDEX payment_type_idx ON taxi_trips (payment_type)"
# Time: 223128.115 ms (03:43.128)

sudo -u postgres psql demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(trip_total)*100, 0) + 0 as tips_percent, count(*) as c
FROM taxi_trips
group by payment_type
order by 3;"
# Time: 198294.076 ms (03:18.294)

Любопытно, эффекта нет, хотя в принципе оно и понятно, ему что так что так нужно будет перелопатить всю табличку

Интересно сможет ли он имея все нужные данные в индексе быстрее ответить на вопрос

sudo -u postgres psql demo -c "DROP INDEX payment_type_idx"

sudo -u postgres psql demo -c "\timing" -c "CREATE INDEX payment_type_idx ON taxi_trips (payment_type, tips, trip_total)"
# Time: 276247.024 ms (04:36.247)

sudo -u postgres psql demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(trip_total)*100, 0) + 0 as tips_percent, count(*) as c
FROM taxi_trips
group by payment_type
order by 3;"
# Time: 8163.513 ms (00:08.164)

🎉 вот совсем другое дело, после пары прогонов вообще меньше 8 сек

MySQL

Так теперь очередь mysql, дабы не делать еще одну виртуалку и туда сюда не гонять файлы, я просто стопнул постгрю и на тот же сервер поставил mysql

sudo systemctl stop postgresql
sudo apt install -y mysql-server
sudo mysql_secure_installation
sudo mysqladmin -p -u root version

Импорт

sudo mysql -u root -e "create database demo"

sudo mysql -u root -D demo -e "create table taxi_trips (
unique_key text,
taxi_id text,
trip_start_timestamp text,
trip_end_timestamp text,
trip_seconds bigint,
trip_miles numeric,
pickup_census_tract bigint,
dropoff_census_tract bigint,
pickup_community_area bigint,
dropoff_community_area bigint,
fare numeric,
tips numeric,
tolls numeric,
extras numeric,
trip_total numeric,
payment_type text,
company text,
pickup_latitude numeric,
pickup_longitude numeric,
pickup_location text,
dropoff_latitude numeric,
dropoff_longitude numeric,
dropoff_location text
)"

sudo mysql -vv -u root -D demo -e "SHOW VARIABLES LIKE 'secure_file_priv'"

sudo mv data.csv /var/lib/mysql-files/

sudo mysql -vv -u root -D demo -e "LOAD DATA INFILE '/var/lib/mysql-files/data.csv' IGNORE
INTO TABLE taxi_trips
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
(
@unique_key,
@taxi_id,
@trip_start_timestamp,
@trip_end_timestamp,
@trip_seconds,
@trip_miles,
@pickup_census_tract,
@dropoff_census_tract,
@pickup_community_area,
@dropoff_community_area,
@fare,
@tips,
@tolls,
@extras,
@trip_total,
@payment_type,
@company,
@pickup_latitude,
@pickup_longitude,
@pickup_location,
@dropoff_latitude,
@dropoff_longitude,
@dropoff_location
)
SET
unique_key = NULLIF(@unique_key, ''),
taxi_id = NULLIF(@taxi_id, ''),
trip_start_timestamp = NULLIF(@trip_start_timestamp, ''),
trip_end_timestamp = NULLIF(@trip_end_timestamp, ''),
trip_seconds = NULLIF(@trip_seconds, ''),
trip_miles = NULLIF(@trip_miles, ''),
pickup_census_tract = NULLIF(@pickup_census_tract, ''),
dropoff_census_tract = NULLIF(@dropoff_census_tract, ''),
pickup_community_area = NULLIF(@pickup_community_area, ''),
dropoff_community_area = NULLIF(@dropoff_community_area, ''),
fare = NULLIF(@fare, ''),
tips = NULLIF(@tips, ''),
tolls = NULLIF(@tolls, ''),
extras = NULLIF(@extras, ''),
trip_total = NULLIF(@trip_total, ''),
payment_type = NULLIF(@payment_type, ''),
company = NULLIF(@company, ''),
pickup_latitude = NULLIF(@pickup_latitude, ''),
pickup_longitude = NULLIF(@pickup_longitude, ''),
pickup_location = NULLIF(@pickup_location, ''),
dropoff_latitude = NULLIF(@dropoff_latitude, ''),
dropoff_longitude = NULLIF(@dropoff_longitude, ''),
dropoff_location = NULLIF(@dropoff_location, '')
"
# Query OK, 26727331 rows affected, 65535 warnings (28 min 46.80 sec)
# Records: 26727331  Deleted: 0  Skipped: 0  Warnings: 165175460

🤦‍♂️ шоб я еще раз что то импортировал в mysql

ну и собственно запрос

sudo mysql -vv -u root -D demo -e "SELECT payment_type, round(sum(tips)/sum(trip_total)*100, 0) + 0 as tips_percent, count(*) as c
FROM taxi_trips
group by payment_type
order by 3"

# 11 rows in set (3 min 41.71 sec)

Любопытный факт, mysql из коробки дал такой же результат как и затюненная постгря

hw11.md

Sample database

curl https://edu.postgrespro.ru/demo_small.zip
unzip demo_small.zip
docker run -it --rm -p 5432:5432 -v $PWD:/demo --name=db -e POSTGRES_PASSWORD=123 -e POSTGRES_DB=demo postgres:13-alpine
docker exec -it db psql -U postgres -f /demo/demo_small.sql

Joins

-- Реализовать прямое соединение двух или более таблиц

-- top 5 городов
select a.aircraft_code, dep.city departure, arr.city arrive, count(*)
from bookings.aircrafts a
         join bookings.flights f on a.aircraft_code = f.aircraft_code
         join bookings.airports dep on dep.airport_code = f.departure_airport
         join bookings.airports arr on arr.airport_code = f.departure_airport
group by 1, 2, 3
order by 4 desc
limit 5

-- Реализовать левостороннее (или правостороннее) соединение двух или более таблиц

-- заполняемость при перелетах
select f.flight_id, count(tf.*) / count(s.*) * 100 from bookings.flights f
join bookings.aircrafts a on f.aircraft_code = a.aircraft_code
join bookings.seats s on a.aircraft_code = s.aircraft_code
left join bookings.ticket_flights tf on f.flight_id = tf.flight_id
group by 1

-- Реализовать кросс соединение двух или более таблиц
-- Реализовать полное соединение двух или более таблиц

-- самые дальние друг от друга аэеропорты
select a.city, b.city, (point(a.longitude,a.latitude) <@> point(b.longitude,b.latitude)) as distance from bookings.airports a
cross join bookings.airports b
order by distance desc
limit 1

Metrics

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

select n_mod_since_analyze + n_ins_since_vacuum / EXTRACT(EPOCH FROM (now() - last_autovacuum)), relname from pg_stat_user_tables
where last_autovacuum is not null
order by 1 desc

pid'ы которые скорее всего в скором времени завесят базу

select
EXTRACT(EPOCH FROM (now() - state_change)) as idle_seconds,
pid
from pg_stat_activity
where state = 'idle' and EXTRACT(EPOCH FROM (now() - state_change)) > 60

какая из баз более нагруженна на запись, а какая на чтение

select
(tup_inserted + tup_deleted + tup_updated) / EXTRACT(EPOCH FROM (now() - stats_reset)) writes_per_second,
tup_returned / EXTRACT(EPOCH FROM (now() - stats_reset)) reads_per_second,
datname
from pg_stat_database

hw12.md

Partitioning

Из всего что перепробовал, мне больше всего нравиться старый добрый inherits, с ним получилось поразвлекаться, в последнем примере я делаю не только партиционирование но еще и денормализацию (партиции в двух разных разрезах, да это х2 данных, но при этом более эффективная работа с ними), так же с таким подходом в любой момент времени можно переиграть и переделать партиции без потенциальной потери данных (в новых вариантах нужен detach и на время работ вставка данных будет невозможна)

• ✅ Adding new partitions
• ❌ Create partitions with trigger
• ✅ Generating partitions programmatically
• ❌ pg_partman
• 🎉 Semi-auto partitioning
• 🤔 Good old partitioning
• 💡 Denormalized partitions

✅ Adding new partitions

Пример как добавлять новые партиции и переезжать туда данные

Примечания:

• важно в процессе создания новой партиции, вставка в default будет не возможна т.к. мы вынужденны ее отключить, из-за чего будет потеря новых данных
• то как быстро мы создадим новую партицию, зависит от того сколько данных уже успело накопиться в партиции по умолчанию (время необходимое на их перенос в новую партицию)

DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
) PARTITION BY LIST (continent);

CREATE TABLE temperatures_africa PARTITION OF temperatures FOR VALUES IN ('Africa');
CREATE TABLE temperatures_antarctica PARTITION OF temperatures FOR VALUES IN ('Antarctica');
-- this one will catch everything else except africa and antarctica defined above
CREATE TABLE temperatures_default PARTITION OF temperatures DEFAULT;

INSERT INTO temperatures (date, continent, value) VALUES
(now(), 'Africa', 1),
(now(), 'Antarctica', 2),
(now(), 'Asia', 3); -- this one should land in 'temperatures_default' table

SELECT * FROM temperatures; -- all 3 rows
SELECT * FROM temperatures_africa; -- 1 row
SELECT * FROM temperatures_antarctica; -- 1 row
SELECT * FROM temperatures_default; -- 1 row

-- create new partition for asia
CREATE TABLE temperatures_asia PARTITION OF temperatures FOR VALUES IN ('Asia');
-- ERROR: updated partition constraint for default partition "temperatures_default" would be violated by some row

ALTER TABLE temperatures DETACH PARTITION temperatures_default; -- WARNING: while we are doing this inserts for everything except africa and antarctica wont work
INSERT INTO temperatures (date, continent, value) VALUES (now(), 'Asia', 4); -- ERROR: no partition of relation "temperatures" found for row Detail: Partition key of the failing row contains (continent) = (Asia).
CREATE TABLE temperatures_asia PARTITION OF temperatures FOR VALUES IN ('Asia'); -- create partition
INSERT INTO temperatures_asia SELECT * FROM temperatures_default WHERE continent = 'Asia'; -- sync data
DELETE FROM temperatures_default WHERE continent = 'Asia'; -- cleanup
ALTER TABLE temperatures ATTACH PARTITION temperatures_default DEFAULT; -- attach default partition back

INSERT INTO temperatures (date, continent, value) VALUES (now(), 'Asia', 4);
SELECT * FROM temperatures; -- 4 rows
SELECT * FROM temperatures_default; -- 0 rows
SELECT * FROM temperatures_asia; -- 2 rows

❌ Create partitions with trigger

К сожалению этот вариант не сработал

Идея была такой:

• записи попадают в default партицию
• вешаем на нее on before insert trigger
• поскольку запись попала сюда, партиции нет
• создаем ее
• вставляем в нее строку
• на производительность нагрузка минимальная т.к. все это происходит только при первой вставке, все последующие пойдут в новую партицию

Ссылки:

• Postgres partition management with triggers. Issue altering tables
• Error applying constraints - "because it is being used by active queries in this session"

Примечания:

• технически можно выкрутиться и вставлять записи через хранимку в которой сделать все это, но в этом уже нет особого смысла и получиться то же самое что отслеживать со стороны приложения

DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
) PARTITION BY RANGE (date);

CREATE TABLE temperatures_default PARTITION OF temperatures DEFAULT;

-- this one will be used as a on before insert trigger to default partition
-- if record lands here it means that we do not have partition yet
-- we are going to create it, so all next inserts will land into new partition
-- because of that it wont affect performance - we are running just once per partition
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
DECLARE
    year INT;
    slug TEXT;
    sql TEXT;
BEGIN
    slug := replace(lower(NEW.continent), ' ', '_'); -- 'South America' -> 'south_america'
    year := extract(year from NEW.date); -- '2021-03-02 12:32:49' -> 2021

    -- Create partition
    SELECT format('CREATE TABLE IF NOT EXISTS temperatures_%s_%s PARTITION OF temperatures FOR VALUES from (''%s-01-01'') to (''%s-01-01'');', slug, year, year, year + 1) INTO sql;
    EXECUTE sql;

    -- Insert data into new partition
    SELECT format('INSERT INTO temperatures_%s_%s values ($1.*)', slug, year) into sql;
    EXECUTE sql USING NEW;
    RETURN NEW;
END;
$$;

CREATE TRIGGER before_insert_row_trigger
BEFORE INSERT ON temperatures_default
FOR EACH ROW EXECUTE PROCEDURE insert_row();

INSERT INTO temperatures (date, continent, value) VALUES ('2020-01-01 12:00:00', 'Africa', 45);
-- ERROR: cannot CREATE TABLE .. PARTITION OF "temperatures" because it is being used by active queries in this session
-- Where: SQL statement "CREATE TABLE IF NOT EXISTS temperatures_africa_2020 PARTITION OF temperatures FOR VALUES from ('2020-01-01') to ('2021-01-01');"

✅ Generating partitions programmatically

В этом примере мы строим следующую структуру:

• temperatures
  • temperatures_asia
    • temperatures_asia_2020
    • temperatures_asia_2021
    • temperatures_asia_default
  • temperatures_africa
    • temperatures_africa_2020
    • temperatures_africa_2021
    • temperatures_africa_default
  • ...

Поинятное дело табличек таких будет много, а еще им не плохо бы навесить индексы и прочее, делать это руками - unreal, поэтому делаем простенькую хранимку которая нам все это дело сгенерирует

DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
) PARTITION BY LIST (continent);


CREATE OR REPLACE FUNCTION Demo() RETURNS BOOLEAN LANGUAGE plpgsql AS
$$
DECLARE
    continent VARCHAR;
    year INT;
    slug VARCHAR;
    sql TEXT;
    dry BOOLEAN = false;
BEGIN
    FOR continent IN (SELECT 'Africa' AS continent UNION SELECT 'Antarctica' UNION SELECT 'Asia' UNION SELECT 'Europe' UNION SELECT 'North America' UNION SELECT 'South America' UNION SELECT 'Australia') LOOP
        raise notice 'Continent: %', continent;

        -- Split temperatures by continents
        slug = replace(lower(continent), ' ', '_');
        SELECT format('CREATE TABLE IF NOT EXISTS temperatures_%s PARTITION OF temperatures FOR VALUES IN (''%s'') PARTITION BY RANGE(date);', slug, continent) INTO sql;
        raise notice 'SQL: %', sql;
        IF dry = false THEN
            EXECUTE sql;
        END IF;

        FOR year IN 2020..2022 LOOP
            raise notice 'Year: %', year;

            -- Inner split continents by years
            SELECT format('CREATE TABLE IF NOT EXISTS temperatures_%s_%s PARTITION OF temperatures_%s FOR VALUES from (''%s-01-01'') to (''%s-01-01'');', slug, year, slug, year, year + 1) INTO sql;
            raise notice 'SQL: %', sql;
            IF dry = false THEN
                EXECUTE sql;
            END IF;

            -- House keeping (just for demo purposes we are creating primary key and unique index)
            SELECT format('ALTER TABLE temperatures_%s_%s ADD PRIMARY KEY (id);', slug, year) INTO sql;
            raise notice 'SQL: %', sql;
            IF dry = false THEN
                EXECUTE sql;
            END IF;
            SELECT format('CREATE UNIQUE INDEX idx_temperatures_%s_%s_date_continent ON temperatures_%s_%s(date, continent);', slug, year, slug, year) INTO sql;
            raise notice 'SQL: %', sql;
            IF dry = false THEN
                EXECUTE sql;
            END IF;

        END LOOP;

        -- Just in case, default partitions for future years
        SELECT format('CREATE TABLE IF NOT EXISTS temperatures_%s_default PARTITION OF temperatures_%s DEFAULT;', slug, slug) INTO sql;
        raise notice 'SQL: %', sql;
        IF dry = false THEN
            EXECUTE sql;
        END IF;

    END LOOP;
    RETURN true;
END
$$;

-- Initialize table
select Demo();

-- 58 objects
SELECT relname FROM pg_class WHERE relname = 'temperatures' OR relname LIKE 'temperatures_%' ORDER BY relname;

-- Insert demo data (3m)
INSERT INTO temperatures (date, continent, value)
SELECT timestamp, continent, floor(random() * 100)::int - 50
FROM generate_series(timestamp '2020-12-01', timestamp '2021-02-01', interval  '1 second') AS timestamps(timestamp),
(SELECT 'Africa' AS continent UNION SELECT 'Antarctica' UNION SELECT 'Asia' UNION SELECT 'Europe' UNION SELECT 'North America' UNION SELECT 'South America' UNION SELECT 'Australia') AS continents(continent);

-- 37.5M, 2s
SELECT count(*) FROM temperatures;
-- 2.6M, 153ms
SELECT count(*) FROM temperatures_europe_2021;

-- Some report, 42s
SELECT continent, extract(year from date) as year, avg(value) as avg_temperature
FROM temperatures
GROUP BY CUBE(1,2);

-- Some report, 4s
SELECT continent, extract(year from date) as year, avg(value) as avg_temperature
FROM temperatures_europe
GROUP BY CUBE(1,2);

❌ pg_partman

pg_partman - partition management extension for PostgreSQL

Оказалась бестолковой поделкой, по сути, с тем же у спехом можно по расписанию раз в день создавать партиции.

FROM postgres:13

RUN apt -qq update && apt -qq -y install build-essential postgresql-server-dev-all postgresql-server-dev-13 wget unzip

WORKDIR /partman

RUN wget https://github.com/pgpartman/pg_partman/archive/refs/tags/v4.5.1.zip && unzip v4.5.1.zip

WORKDIR /partman/pg_partman-4.5.1

RUN make install

docker build -t partman .
docker run -it --rm -p 5432:5432 -e POSTGRES_PASSWORD=123 partman postgres -c shared_preload_libraries=pg_partman_bgw

SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';

CREATE EXTENSION pg_partman;

DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
) PARTITION BY RANGE (date);

CREATE INDEX ON temperatures (date);

SELECT create_parent('public.temperatures', 'date', 'native', 'daily');

-- 10 tables
SELECT relname FROM pg_stat_user_tables WHERE relname = 'temperatures' OR relname LIKE 'temperatures_%' ORDER BY relname;

INSERT INTO temperatures (date, continent, value) VALUES ('2021-07-01 12:00:00', 'Europe', 1);

SELECT * FROM temperatures;
SELECT * FROM temperatures_p2021_07_01;

-- trying to insert data from past
INSERT INTO temperatures (date, continent, value) VALUES ('2001-01-01 12:00:00', 'Europe', 2);

SELECT * FROM temperatures;
SELECT * FROM temperatures_default;

Данные вставились в default партицию, т.к. pg_partman не создает партиции на лету, а просто по расписнию 🤔

🎉 Semi-auto partitioning

В чем заключается идея: заводим табличку пустышку и треггер на нее

Внутри триггера проворачиваем трюк с созданием партиции

DROP TABLE IF EXISTS temperatures;
DROP TABLE IF EXISTS temperatures_partitioned;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
);

CREATE TABLE temperatures_partitioned (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
) PARTITION BY RANGE (date);

CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
DECLARE
    year INT;
    slug TEXT;
    sql TEXT;
BEGIN
    year := extract(year from NEW.date); -- '2021-03-02 12:32:49' -> 2021

    -- Create partition
    SELECT format('CREATE TABLE IF NOT EXISTS temperatures_partitioned_%s PARTITION OF temperatures_partitioned FOR VALUES from (''%s-01-01'') to (''%s-01-01'');', year, year, year + 1) INTO sql;
    EXECUTE sql;

    -- Insert data into new partition
    SELECT format('INSERT INTO temperatures_partitioned_%s values ($1.*)', year) into sql;
    EXECUTE sql USING NEW;
    RETURN NEW;
END;
$$;

CREATE TRIGGER before_insert_row_trigger
BEFORE INSERT ON temperatures
FOR EACH ROW EXECUTE PROCEDURE insert_row();

INSERT INTO temperatures (date, continent, value) VALUES ('2020-01-01 12:00:00', 'Europe', 1);

SELECT relname FROM pg_stat_user_tables WHERE relname like 'temperatures%';
SELECT * FROM temperatures; -- 1
SELECT * FROM temperatures_partitioned; -- 1
SELECT * FROM temperatures_partitioned_2020; -- 1

INSERT INTO temperatures (date, continent, value) VALUES ('2021-01-01 12:00:00', 'Europe', 2);

SELECT relname FROM pg_stat_user_tables WHERE relname like 'temperatures%';
SELECT * FROM temperatures; -- 2
SELECT * FROM temperatures_partitioned; -- 2
SELECT * FROM temperatures_partitioned_2020; -- 1
SELECT * FROM temperatures_partitioned_2021; -- 1

🤔 Good old partitioning

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

DROP TABLE IF EXISTS temperatures_default;
DROP TABLE IF EXISTS temperatures_2020;
DROP TABLE IF EXISTS temperatures_2021;
DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
);

CREATE TABLE temperatures_default () INHERITS (temperatures);
CREATE TABLE temperatures_2020 (like temperatures including all) INHERITS (temperatures);
ALTER TABLE temperatures_2020 add check (date between date'2020-01-01' and date'2021-01-01' - 1);
CREATE TABLE temperatures_2021 () INHERITS (temperatures);
ALTER TABLE temperatures_2021 add check (date between date'2021-01-01' and date'2022-02-01' - 1);

CREATE OR REPLACE FUNCTION temperatures_insert_row()
RETURNS TRIGGER AS $$
BEGIN
    if new.date between date'2020-01-01' and date'2021-01-01' - 1 then
        INSERT INTO temperatures_2020 VALUES (NEW.*);
    elsif new.date between date'2021-01-01' and date'2022-02-01' - 1 then
        INSERT INTO temperatures_2021 VALUES (NEW.*);
    else
        -- raise exception 'this date not in your partitions. add partition';
        INSERT INTO temperatures_default VALUES (NEW.*);
    end if;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER temperatures_insert_row
BEFORE INSERT ON temperatures
FOR EACH ROW EXECUTE PROCEDURE temperatures_insert_row();

INSERT INTO temperatures (date, continent, value) VALUES ('2020-01-01 12:00:00', 'Europe', 1);

SELECT * FROM temperatures;
SELECT * FROM temperatures_2020;
SELECT * FROM temperatures_2021;

INSERT INTO temperatures (date, continent, value) VALUES ('2021-01-01 12:00:00', 'Europe', 2);

SELECT * FROM temperatures;
SELECT * FROM temperatures_2020;
SELECT * FROM temperatures_2021;

SELECT relname, n_tup_ins, n_live_tup FROM pg_stat_user_tables WHERE relname like 'temperatures%';

INSERT INTO temperatures (date, continent, value) VALUES ('2019-01-01 12:00:00', 'Europe', 3);

SELECT * FROM temperatures;
SELECT * FROM temperatures_2020;
SELECT * FROM temperatures_2021;
SELECT * FROM temperatures_default;

SELECT relname, n_tup_ins, n_live_tup FROM pg_stat_user_tables WHERE relname like 'temperatures%';

-- New partition
CREATE TABLE temperatures_2019 () INHERITS (temperatures);
ALTER TABLE temperatures_2019 add check (date between date'2019-01-01' and date'2020-02-01' - 1);

-- Move data
with delta as (
  delete from temperatures_default where extract(year from date) = 2019
  returning *
)
insert into temperatures_2019
select * from delta;

-- Test
SELECT * FROM temperatures_default;
SELECT * FROM temperatures_2019;

💡 Denormalized partitions

Идея такая - мы ж делаем все это партиционирование для ускорения выборки

А почему бы не сделать партиции сразу в нескольких разрезах, тем самым ускоряя выборки в этих самых разрезах

DROP TABLE IF EXISTS temperatures_year_2018;
DROP TABLE IF EXISTS temperatures_year_2020;
DROP TABLE IF EXISTS temperatures_year_2021;
DROP TABLE IF EXISTS temperatures_year_other;
DROP TABLE IF EXISTS temperatures_continent_asia;
DROP TABLE IF EXISTS temperatures_continent_europe;
DROP TABLE IF EXISTS temperatures_continent_other;
DROP TABLE IF EXISTS temperatures_year;
DROP TABLE IF EXISTS temperatures_continent;
DROP TABLE IF EXISTS temperatures;


CREATE TABLE temperatures (
    id SERIAL,
    date TIMESTAMP NOT NULL,
    continent VARCHAR NOT NULL CHECK (continent in ('Africa','Antarctica','Asia','Europe','North America','South America','Australia')),
    value INT
);

-- by years
CREATE TABLE temperatures_year () INHERITS (temperatures);
CREATE TABLE temperatures_year_2020 (like temperatures including all) INHERITS (temperatures_year);
ALTER TABLE temperatures_year_2020 add check (date between date'2020-01-01' and date'2020-12-31');
CREATE TABLE temperatures_year_2021 () INHERITS (temperatures_year);
ALTER TABLE temperatures_year_2021 add check (date between date'2021-01-01' and date'2021-12-31');
CREATE TABLE temperatures_year_other () INHERITS (temperatures_year);

-- by continents
CREATE TABLE temperatures_continent () INHERITS (temperatures);
CREATE TABLE temperatures_continent_europe () INHERITS (temperatures_continent);
ALTER TABLE temperatures_continent_europe add check (continent = 'Europe');
CREATE TABLE temperatures_continent_other () INHERITS (temperatures_continent);

-- trigger
CREATE OR REPLACE FUNCTION temperatures_insert_row()
RETURNS TRIGGER AS $$
BEGIN
    if new.date between date'2020-01-01' and date'2020-12-31' then
        INSERT INTO temperatures_year_2020 VALUES (NEW.*);
    elsif new.date between date'2021-01-01' and date'2021-12-31' then
        INSERT INTO temperatures_year_2021 VALUES (NEW.*);
    else
        INSERT INTO temperatures_year_other VALUES (NEW.*);
    end if;

    if new.continent = 'Europe' then
        INSERT INTO temperatures_continent_europe VALUES (NEW.*);
    else
        INSERT INTO temperatures_continent_other VALUES (NEW.*);
    end if;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER temperatures_insert_row
BEFORE INSERT ON temperatures
FOR EACH ROW EXECUTE PROCEDURE temperatures_insert_row();

-- DEMO

SELECT relname, n_tup_ins, n_live_tup FROM pg_stat_user_tables WHERE relname like 'temperatures%';

INSERT INTO temperatures (date, continent, value) VALUES ('2020-01-01 12:00:00', 'Europe', 1);

SELECT relname, n_tup_ins, n_live_tup FROM pg_stat_user_tables WHERE relname like 'temperatures%';

SELECT * FROM temperatures_year_2020; -- 1 row
SELECT * FROM temperatures_continent_europe; -- 1 row

INSERT INTO temperatures (date, continent, value) VALUES ('2018-01-01 12:00:00', 'Asia', 2);

SELECT * FROM temperatures_year_other; -- 1 row
SELECT * FROM temperatures_continent_other; -- 1 row
SELECT * FROM temperatures_year; -- 2 rows
SELECT * FROM temperatures_continent; -- 2 rows

-- REPARTITIONING
-- ---------------------------------
-- Create new tables
CREATE TABLE temperatures_year_2018 () INHERITS (temperatures_year);
ALTER TABLE temperatures_year_2018 add check (date between date'2018-01-01' and date'2018-12-31');
CREATE TABLE temperatures_continent_asia () INHERITS (temperatures_continent);
ALTER TABLE temperatures_continent_asia add check (continent = 'Asia');

-- Modify trigger so new records will land in new tables
CREATE OR REPLACE FUNCTION temperatures_insert_row()
RETURNS TRIGGER AS $$
BEGIN
    if new.date between date'2020-01-01' and date'2020-12-31' then
        INSERT INTO temperatures_year_2020 VALUES (NEW.*);
    elsif new.date between date'2021-01-01' and date'2021-12-31' then
        INSERT INTO temperatures_year_2021 VALUES (NEW.*);
    elsif new.date between date'2018-01-01' and date'2018-12-31' then
        INSERT INTO temperatures_year_2018 VALUES (NEW.*);
    else
        INSERT INTO temperatures_year_other VALUES (NEW.*);
    end if;

    if new.continent = 'Europe' then
        INSERT INTO temperatures_continent_europe VALUES (NEW.*);
    elsif new.continent = 'Asia' then
        INSERT INTO temperatures_continent_asia VALUES (NEW.*);
    else
        INSERT INTO temperatures_continent_other VALUES (NEW.*);
    end if;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- check that new trigger works
INSERT INTO temperatures (date, continent, value) VALUES ('2018-01-01 12:00:00', 'Asia', 3);

SELECT * FROM temperatures_year_other; -- old row still here
SELECT * FROM temperatures_continent_other; -- old row still here

SELECT * FROM temperatures_year_2018; -- new row landed here
SELECT * FROM temperatures_continent_asia; -- new row landed here

SELECT * FROM temperatures_year WHERE extract(YEAR from date) = 2018; -- but we still see both
SELECT * FROM temperatures_continent WHERE continent = 'Asia'; -- but we still see both

-- move old data
with delta as (
  delete from temperatures_year_other where extract(year from date) = 2018
  returning *
)
insert into temperatures_year_2018
select * from delta;

with delta as (
  delete from temperatures_continent_other where continent = 'Asia'
  returning *
)
insert into temperatures_continent_asia
select * from delta;

-- TEST

SELECT * FROM temperatures_year_other; -- 0 rows, data moved
SELECT * FROM temperatures_continent_other; -- 0 rows, data moved

SELECT * FROM temperatures_year_2018; -- 2 rows, data moved
SELECT * FROM temperatures_continent_asia; -- 2 rows, data moved

SELECT * FROM temperatures_year WHERE extract(YEAR from date) = 2018; -- 2 rows
SELECT * FROM temperatures_continent WHERE continent = 'Asia'; -- 2 rows

hw13.md

PostgreSQL High Availability

Разворачивал в Google Cloud Kubernetes.

Для стрима логов потребовалось повозиться с сервисными учетками, после чего вижу в Google Storage файлики архивов

К сожалению сильно поздно обратил внимание на примечание в доке:

Standby cluster: So far only streaming from a S3 WAL archive is supported

Что делает не возможным сетап мульти регионального кластера в Google Cloud без дополнительной возни еще и с Amazon 🤔

Получилось выкрутиться и не использовать helm, а собрать все нужные yml руками, так удобнее, тк. можно радикально менять то что мы деплоим

Пример деплоймента:

db.yml

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: demo-cluster
spec:
  teamId: demo
  volume:
    size: 1Gi
  numberOfInstances: 2
  users:
    demo:
    - superuser
    - createdb
  databases:
    demo: demo
  postgresql:
    version: "13"

Пример оператора:

operator.yml

---
apiVersion: v1
kind: Secret
metadata:
  name: credentials
type: Opaque
stringData:
  credentials.json: |
    {
      "type": "service_account",
      "project_id": "pgotus",
      "private_key_id": "5341ec7be32fa00537f0ab5a5fb64d37d5ab6ba2",
      "private_key": "-----BEGIN PRIVATE KEY-----\n******\n-----END PRIVATE KEY-----\n",
      "client_email": "[email protected]",
      "client_id": "112989314471558820685",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/otusdemo%40pgotus.iam.gserviceaccount.com"
    }
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pod-env-overrides
data:
  USE_WALG_BACKUP: "true"
  USE_WALG_RESTORE: "true"
  CLONE_USE_WALG_RESTORE: "true"
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-operator
data:
  # https://github.com/zalando/postgres-operator/blob/master/docs/reference/operator_parameters.md
  # additional_pod_capabilities: "SYS_NICE"
  # additional_secret_mount: "some-secret-name"
  # additional_secret_mount_path: "/some/dir"
  api_port: "8080"
  aws_region: eu-central-1
  cluster_domain: cluster.local
  cluster_history_entries: "1000"
  cluster_labels: application:spilo
  cluster_name_label: cluster-name
  # connection_pooler_default_cpu_limit: "1"
  # connection_pooler_default_cpu_request: "500m"
  # connection_pooler_default_memory_limit: 100Mi
  # connection_pooler_default_memory_request: 100Mi
  connection_pooler_image: "registry.opensource.zalan.do/acid/pgbouncer:master-16"
  # connection_pooler_max_db_connections: 60
  # connection_pooler_mode: "transaction"
  # connection_pooler_number_of_instances: 2
  # connection_pooler_schema: "pooler"
  # connection_pooler_user: "pooler"
  # custom_service_annotations: "keyx:valuez,keya:valuea"
  # custom_pod_annotations: "keya:valuea,keyb:valueb"
  db_hosted_zone: db.example.com
  debug_logging: "true"
  # default_cpu_limit: "1"
  # default_cpu_request: 100m
  # default_memory_limit: 500Mi
  # default_memory_request: 100Mi
  # delete_annotation_date_key: delete-date
  # delete_annotation_name_key: delete-clustername
  docker_image: registry.opensource.zalan.do/acid/spilo-13:2.0-p7
  # downscaler_annotations: "deployment-time,downscaler/*"
  # enable_admin_role_for_users: "true"
  # enable_crd_validation: "true"
  # enable_database_access: "true"
  enable_ebs_gp3_migration: "false"
  # enable_ebs_gp3_migration_max_size: "1000"
  # enable_init_containers: "true"
  # enable_lazy_spilo_upgrade: "false"
  enable_master_load_balancer: "true"
  enable_pgversion_env_var: "true"
  # enable_pod_antiaffinity: "false"
  # enable_pod_disruption_budget: "true"
  # enable_postgres_team_crd: "false"
  # enable_postgres_team_crd_superusers: "false"
  enable_replica_load_balancer: "false"
  # enable_shm_volume: "true"
  # enable_sidecars: "true"
  enable_spilo_wal_path_compat: "true"
  enable_team_member_deprecation: "false"
  # enable_team_superuser: "false"
  enable_teams_api: "false"
  # etcd_host: ""
  external_traffic_policy: "Cluster"
  # gcp_credentials: ""
  # kubernetes_use_configmaps: "false"
  # infrastructure_roles_secret_name: "postgresql-infrastructure-roles"
  # infrastructure_roles_secrets: "secretname:monitoring-roles,userkey:user,passwordkey:password,rolekey:inrole"
  # inherited_annotations: owned-by
  # inherited_labels: application,environment
  # kube_iam_role: ""
  # log_s3_bucket: ""
  logical_backup_docker_image: "registry.opensource.zalan.do/acid/logical-backup:v1.6.3"
  # logical_backup_google_application_credentials: ""
  logical_backup_job_prefix: "logical-backup-"
  logical_backup_provider: "s3"
  # logical_backup_s3_access_key_id: ""
  logical_backup_s3_bucket: "my-bucket-url"
  # logical_backup_s3_region: ""
  # logical_backup_s3_endpoint: ""
  # logical_backup_s3_secret_access_key: ""
  logical_backup_s3_sse: "AES256"
  logical_backup_schedule: "30 00 * * *"
  major_version_upgrade_mode: "manual"
  master_dns_name_format: "{cluster}.{team}.{hostedzone}"
  # master_pod_move_timeout: 20m
  # max_instances: "-1"
  # min_instances: "-1"
  # min_cpu_limit: 250m
  # min_memory_limit: 250Mi
  # minimal_major_version: "9.5"
  # node_readiness_label: ""
  # oauth_token_secret_name: postgresql-operator
  # pam_configuration: |
  #  https://info.example.com/oauth2/tokeninfo?access_token= uid realm=/employees
  # pam_role_name: zalandos
  pdb_name_format: "postgres-{cluster}-pdb"
  # pod_antiaffinity_topology_key: "kubernetes.io/hostname"
  pod_deletion_wait_timeout: 10m
  # pod_environment_configmap: "default/my-custom-config"
  # pod_environment_secret: "my-custom-secret"
  pod_label_wait_timeout: 10m
  pod_management_policy: "ordered_ready"
  # pod_priority_class_name: "postgres-pod-priority"
  pod_role_label: spilo-role
  # pod_service_account_definition: ""
  pod_service_account_name: "postgres-pod"
  # pod_service_account_role_binding_definition: ""
  pod_terminate_grace_period: 5m
  # postgres_superuser_teams: "postgres_superusers"
  # protected_role_names: "admin"
  ready_wait_interval: 3s
  ready_wait_timeout: 30s
  repair_period: 5m
  replica_dns_name_format: "{cluster}-repl.{team}.{hostedzone}"
  replication_username: standby
  resource_check_interval: 3s
  resource_check_timeout: 10m
  resync_period: 30m
  ring_log_lines: "100"
  role_deletion_suffix: "_deleted"
  secret_name_template: "{username}.{cluster}.credentials"
  # sidecar_docker_images: ""
  # set_memory_request_to_limit: "false"
  spilo_allow_privilege_escalation: "true"
  # spilo_runasuser: 101
  # spilo_runasgroup: 103
  # spilo_fsgroup: 103
  spilo_privileged: "false"
  storage_resize_mode: "pvc"
  super_username: postgres
  # target_major_version: "13"
  # team_admin_role: "admin"
  # team_api_role_configuration: "log_statement:all"
  # teams_api_url: http://fake-teams-api.default.svc.cluster.local
  # toleration: ""
  # wal_gs_bucket: ""
  # wal_s3_bucket: ""
  watched_namespace: "*"  # listen to all namespaces
  workers: "8"
  # https://postgres-operator.readthedocs.io/en/latest/administrator/#google-cloud-platform-setup
  wal_gs_bucket: otusdemo
  additional_secret_mount: credentials
  additional_secret_mount_path: /var/secrets/google
  gcp_credentials: /var/secrets/google/credentials.json
  pod_environment_configmap: pod-env-overrides
---
apiVersion: v1
kind: ServiceAccount
metadata:
  name: postgres-operator
  namespace: default
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: postgres-operator
rules:
- apiGroups:
  - acid.zalan.do
  resources:
  - postgresqls
  - postgresqls/status
  - operatorconfigurations
  verbs:
  - create
  - delete
  - deletecollection
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - acid.zalan.do
  resources:
  - postgresteams
  verbs:
  - get
  - list
  - watch
- apiGroups:
  - apiextensions.k8s.io
  resources:
  - customresourcedefinitions
  verbs:
  - create
  - get
  - patch
  - update
- apiGroups:
  - ""
  resources:
  - configmaps
  verbs:
  - get
- apiGroups:
  - ""
  resources:
  - events
  verbs:
  - create
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - ""
  resources:
  - endpoints
  verbs:
  - create
  - delete
  - deletecollection
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - ""
  resources:
  - secrets
  verbs:
  - create
  - delete
  - get
  - update
- apiGroups:
  - ""
  resources:
  - nodes
  verbs:
  - get
  - list
  - watch
- apiGroups:
  - ""
  resources:
  - persistentvolumeclaims
  verbs:
  - delete
  - get
  - list
  - patch
  - update
- apiGroups:
  - ""
  resources:
  - persistentvolumes
  verbs:
  - get
  - list
  - update
- apiGroups:
  - ""
  resources:
  - pods
  verbs:
  - delete
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - ""
  resources:
  - pods/exec
  verbs:
  - create
- apiGroups:
  - ""
  resources:
  - services
  verbs:
  - create
  - delete
  - get
  - patch
  - update
- apiGroups:
  - apps
  resources:
  - statefulsets
  - deployments
  verbs:
  - create
  - delete
  - get
  - list
  - patch
- apiGroups:
  - batch
  resources:
  - cronjobs
  verbs:
  - create
  - delete
  - get
  - list
  - patch
  - update
- apiGroups:
  - ""
  resources:
  - namespaces
  verbs:
  - get
- apiGroups:
  - policy
  resources:
  - poddisruptionbudgets
  verbs:
  - create
  - delete
  - get
- apiGroups:
  - ""
  resources:
  - serviceaccounts
  verbs:
  - get
  - create
- apiGroups:
  - rbac.authorization.k8s.io
  resources:
  - rolebindings
  verbs:
  - get
  - create
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: postgres-operator
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: postgres-operator
subjects:
- kind: ServiceAccount
  name: postgres-operator
  namespace: default
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: postgres-pod
rules:
- apiGroups:
  - ""
  resources:
  - endpoints
  verbs:
  - create
  - delete
  - deletecollection
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - ""
  resources:
  - pods
  verbs:
  - get
  - list
  - patch
  - update
  - watch
- apiGroups:
  - ""
  resources:
  - services
  verbs:
  - create
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-operator
  labels:
    application: postgres-operator
spec:
  replicas: 1
  strategy:
    type: "Recreate"
  selector:
    matchLabels:
      name: postgres-operator
  template:
    metadata:
      labels:
        name: postgres-operator
    spec:
      serviceAccountName: postgres-operator
      containers:
      - name: postgres-operator
        image: registry.opensource.zalan.do/acid/postgres-operator:v1.6.3
        imagePullPolicy: IfNotPresent
        resources:
          requests:
            cpu: 100m
            memory: 250Mi
          limits:
            cpu: 500m
            memory: 500Mi
        securityContext:
          runAsUser: 1000
          runAsNonRoot: true
          readOnlyRootFilesystem: true
          allowPrivilegeEscalation: false
        env:
        - name: CONFIG_MAP_NAME
          value: "postgres-operator"
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-operator
spec:
  type: ClusterIP
  ports:
  - port: 8080
    protocol: TCP
    targetPort: 8080
  selector:
    name: postgres-operator

Примечания:

• файл собирался из оригинальных манифестов
• использовались файлы из ветки v1.6.3 т.к. из мастера забирать все как то стремно
• в оригинальных файлах зачем то выключен сервис для мастера что по началу сбило с толку
• все еще стремная штука, честно говоря я бы не рискнул на ней подымать что то боевое, а для чего то маленького - сильно избыточное (имею в виду и сам кубер и кол-во виртуалок, еще и реплики и т.д. и т.п.)
• интереса ради поковырял pg_auto_failover но практически моментально уперся в это - с одной стороны выглядит как раз тем что надо, а с другой, пока еще как то не очень готовое

pg_auto_failover

крайне странная но все же рабочая штука

# NETWORK - без этого не завдеться демка
docker network create my

# MONITOR - тут и далее мы используем аутентификацию по паролю за вместо trust - наибольших головняк
docker run --network=my --hostname=monitor -it --rm -e PGDATA=/tmp/data -e PGPORT=5000 -p 5000:5000 --name=monitor citusdata/pg_auto_failover:v1.6.1 pg_autoctl create monitor --ssl-self-signed --auth md5 --run

# пароль надо добавлять руками
docker exec -it monitor psql "postgres://localhost:5000/pg_auto_failover" -c "ALTER USER autoctl_node PASSWORD 'autoctl_node_password'"
# убедиться что пускаем по паролю
docker exec -it monitor cat /tmp/data/pg_hba.conf

# NODE1 - подключиться автоматом, т.к. передали пароль в connection string к monitor
docker run --network=my --hostname=node1 -it --rm -e PGDATA=/tmp/data -e PGPORT=5001 -p 5001:5001 --link=monitor --name=node1 citusdata/pg_auto_failover:v1.6.1 pg_autoctl create postgres --ssl-self-signed --auth md5 --pg-hba-lan --username demo --dbname demo --monitor postgresql://autoctl_node:autoctl_node_password@monitor:5000/pg_auto_failover --run

# задаем в настройках для pg_auto_failover пароль от pgautofailover_replicator пользователя
docker exec -it node1 pg_autoctl config set replication.password replication_password
# дублируем его же в базе
docker exec -it node1 psql "postgres://localhost:5001/demo" -c "ALTER USER pgautofailover_replicator PASSWORD 'replication_password'"
# и еще один для пользователя pgautofailover_monitor
docker exec -it node1 psql "postgres://localhost:5001/demo" -c "ALTER USER pgautofailover_monitor PASSWORD 'monitor_password'"

# демо табличка с данными
docker exec -it node1 psql "postgres://localhost:5001/demo" -c "CREATE TABLE t1(a int)"
docker exec -it node1 psql "postgres://localhost:5001/demo" -c "INSERT INTO t1 VALUES (1), (2)"

# проверим что мы все еще пускаем всех по паролям
docker exec -it node1 cat /tmp/data/pg_hba.conf


# NODE2 - один в один та же команда что и для node1, за исключением портов, имени контейнера и т.п.
docker run --network=my --hostname=node2 -it --rm -e PGPASSWORD=replication_password -e PGDATA=/tmp/data -e PGPORT=5002 -p 5002:5002 --link=monitor --name=node2 citusdata/pg_auto_failover:v1.6.1 pg_autoctl create postgres --ssl-self-signed --auth md5 --pg-hba-lan --username demo --dbname demo --monitor postgresql://autoctl_node:autoctl_node_password@monitor:5000/pg_auto_failover --run

# опять же задаем пароль для pgautofailover_replicator
docker exec -it node2 pg_autoctl config set replication.password replication_password
# смотрим что у нас по авторизации
docker exec -it node2 cat /tmp/data/pg_hba.conf
# что с кластером
docker exec -it monitor pg_autoctl show state
# приехали ли данные
docker exec -it node2 psql "postgres://localhost:5002/demo" -c "select * from t1"

# должно вывести connection string, но при авторизации по паролю не работает
# docker exec -it monitor pg_autoctl show uri

# пробуем подключиться "снаружи"
docker run -it --rm --network=my postgres:13-alpine psql "postgres://node1:5001,node2:5002/demo?target_session_attrs=read-write&sslmode=require" -U demo -c "select * from t1"

# по скольку у нас вход по паролю, нужно его задать
docker exec -it node1 psql "postgres://localhost:5001/demo" -c "ALTER USER demo PASSWORD 'demo'"



# SWITCHOVER

docker exec -it monitor pg_autoctl show state

docker exec -it monitor pg_autoctl perform switchover

hw14.md

Chicago Taxi Trips 🚕

Имеем базочку в 76Гб и 196 млн записей. Сверять будем следующий запрос:

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*)
FROM taxi_trips
group by payment_type
order by 3 desc;

TLDR: безоговорочный лидер - bigquery

• 🥇 bigquery - 1 сек
• 🥈 postgres - 22 сек
• 🥉 cockroach - 147 сек

Bonus

• postgres -

Примечания:

• запросы выполнялись по 3 раза, брался лучший результат
• импорт в postgres быстрее чем в cockroach
• ни postgres ни cockroach не выедали все ресурсы
• если похимичить, результаты для cockroach и postgres радикально меняются
• интереса ради решил упороться с партициями и попробовать еще больше соков выжать из постгри, см. bonus но по факту оно дает результат только для сценария без индексов

Roadmap:

• cockroachdb cluster in kubernetes
• single node postgres
• bonus

Cockroach

Cockroach будем подымать в Kubernetes с помощью их оператора

Конфигурация кластера:

• зона: europe-west3-c (frankfurt)
• машинки: e2-standard-4 (4cpu, 16ram) x3

custom resource definitions

kubectl apply -f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/master/config/crd/bases/crdb.cockroachlabs.com_crdbclusters.yaml

operator

kubectl apply -f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/master/manifests/operator.yaml

kubectl get po -l app=cockroach-operator

cockroach cluster

apiVersion: crdb.cockroachlabs.com/v1alpha1
kind: CrdbCluster
metadata:
  name: cockroachdb
spec:
  dataStore:
    pvc:
      spec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: "100Gi"
        volumeMode: Filesystem
  resources:
    requests:
      cpu: "3"
      memory: "12Gi"
    limits:
      cpu: "3"
      memory: "12Gi"
  tlsEnabled: true
  image:
    name: cockroachdb/cockroach:v21.1.5
  nodes: 3
  additionalLabels:
    crdb: otus

Примечание: по сколькуо сам кубер так же забирает какие то ресурсы, мы не можем зарезервировать все 4 ядра и 16 оперативки, оставляем чуть чуть для системы

Проваливаемся в cockroach:

kubectl create -f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/master/examples/client-secure-operator.yaml

kubectl exec -it cockroachdb-client-secure -- ./cockroach sql --certs-dir=/cockroach/cockroach-certs --host=cockroachdb-public

Согластно инструкции имортируем dataset:

IMPORT TABLE taxi_trips(...) CSV DATA (...)

IMPORT table taxi_trips (
    unique_key text,
    taxi_id text,
    trip_start_timestamp TIMESTAMP,
    trip_end_timestamp TIMESTAMP,
    trip_seconds bigint,
    trip_miles numeric,
    pickup_census_tract bigint,
    dropoff_census_tract bigint,
    pickup_community_area bigint,
    dropoff_community_area bigint,
    fare numeric,
    tips numeric,
    tolls numeric,
    extras numeric,
    trip_total numeric,
    payment_type text,
    company text,
    pickup_latitude numeric,
    pickup_longitude numeric,
    pickup_location text,
    dropoff_latitude numeric,
    dropoff_longitude numeric,
    dropoff_location text
)
CSV DATA (
    'gs://otusdemo/taxi-000000000000.csv',
    'gs://otusdemo/taxi-000000000001.csv',
    'gs://otusdemo/taxi-000000000002.csv',
    'gs://otusdemo/taxi-000000000003.csv',
    'gs://otusdemo/taxi-000000000004.csv',
    'gs://otusdemo/taxi-000000000005.csv',
    'gs://otusdemo/taxi-000000000006.csv',
    'gs://otusdemo/taxi-000000000007.csv',
    'gs://otusdemo/taxi-000000000008.csv',
    'gs://otusdemo/taxi-000000000009.csv',
    'gs://otusdemo/taxi-000000000010.csv',
    'gs://otusdemo/taxi-000000000011.csv',
    'gs://otusdemo/taxi-000000000012.csv',
    'gs://otusdemo/taxi-000000000013.csv',
    'gs://otusdemo/taxi-000000000014.csv',
    'gs://otusdemo/taxi-000000000015.csv',
    'gs://otusdemo/taxi-000000000016.csv',
    'gs://otusdemo/taxi-000000000017.csv',
    'gs://otusdemo/taxi-000000000018.csv',
    'gs://otusdemo/taxi-000000000019.csv',
    'gs://otusdemo/taxi-000000000020.csv',
    'gs://otusdemo/taxi-000000000021.csv',
    'gs://otusdemo/taxi-000000000022.csv',
    'gs://otusdemo/taxi-000000000023.csv',
    'gs://otusdemo/taxi-000000000024.csv',
    'gs://otusdemo/taxi-000000000025.csv',
    'gs://otusdemo/taxi-000000000026.csv',
    'gs://otusdemo/taxi-000000000027.csv',
    'gs://otusdemo/taxi-000000000028.csv',
    'gs://otusdemo/taxi-000000000029.csv',
    'gs://otusdemo/taxi-000000000030.csv',
    'gs://otusdemo/taxi-000000000031.csv',
    'gs://otusdemo/taxi-000000000032.csv',
    'gs://otusdemo/taxi-000000000033.csv',
    'gs://otusdemo/taxi-000000000034.csv',
    'gs://otusdemo/taxi-000000000035.csv',
    'gs://otusdemo/taxi-000000000036.csv',
    'gs://otusdemo/taxi-000000000037.csv',
    'gs://otusdemo/taxi-000000000038.csv',
    'gs://otusdemo/taxi-000000000039.csv',
    'gs://otusdemo/taxi-000000000040.csv',
    'gs://otusdemo/taxi-000000000041.csv',
    'gs://otusdemo/taxi-000000000042.csv',
    'gs://otusdemo/taxi-000000000043.csv',
    'gs://otusdemo/taxi-000000000044.csv',
    'gs://otusdemo/taxi-000000000045.csv',
    'gs://otusdemo/taxi-000000000046.csv',
    'gs://otusdemo/taxi-000000000047.csv',
    'gs://otusdemo/taxi-000000000048.csv',
    'gs://otusdemo/taxi-000000000049.csv',
    'gs://otusdemo/taxi-000000000050.csv',
    'gs://otusdemo/taxi-000000000051.csv',
    'gs://otusdemo/taxi-000000000052.csv',
    'gs://otusdemo/taxi-000000000053.csv',
    'gs://otusdemo/taxi-000000000054.csv',
    'gs://otusdemo/taxi-000000000055.csv',
    'gs://otusdemo/taxi-000000000056.csv',
    'gs://otusdemo/taxi-000000000057.csv',
    'gs://otusdemo/taxi-000000000058.csv',
    'gs://otusdemo/taxi-000000000059.csv',
    'gs://otusdemo/taxi-000000000060.csv',
    'gs://otusdemo/taxi-000000000061.csv',
    'gs://otusdemo/taxi-000000000062.csv',
    'gs://otusdemo/taxi-000000000063.csv',
    'gs://otusdemo/taxi-000000000064.csv',
    'gs://otusdemo/taxi-000000000065.csv',
    'gs://otusdemo/taxi-000000000066.csv',
    'gs://otusdemo/taxi-000000000067.csv',
    'gs://otusdemo/taxi-000000000068.csv',
    'gs://otusdemo/taxi-000000000069.csv',
    'gs://otusdemo/taxi-000000000070.csv',
    'gs://otusdemo/taxi-000000000071.csv',
    'gs://otusdemo/taxi-000000000072.csv',
    'gs://otusdemo/taxi-000000000073.csv',
    'gs://otusdemo/taxi-000000000074.csv',
    'gs://otusdemo/taxi-000000000075.csv',
    'gs://otusdemo/taxi-000000000076.csv',
    'gs://otusdemo/taxi-000000000077.csv',
    'gs://otusdemo/taxi-000000000078.csv',
    'gs://otusdemo/taxi-000000000079.csv',
    'gs://otusdemo/taxi-000000000080.csv',
    'gs://otusdemo/taxi-000000000081.csv',
    'gs://otusdemo/taxi-000000000082.csv',
    'gs://otusdemo/taxi-000000000083.csv',
    'gs://otusdemo/taxi-000000000084.csv',
    'gs://otusdemo/taxi-000000000085.csv',
    'gs://otusdemo/taxi-000000000086.csv',
    'gs://otusdemo/taxi-000000000087.csv',
    'gs://otusdemo/taxi-000000000088.csv',
    'gs://otusdemo/taxi-000000000089.csv',
    'gs://otusdemo/taxi-000000000090.csv',
    'gs://otusdemo/taxi-000000000091.csv',
    'gs://otusdemo/taxi-000000000092.csv',
    'gs://otusdemo/taxi-000000000093.csv',
    'gs://otusdemo/taxi-000000000094.csv',
    'gs://otusdemo/taxi-000000000095.csv',
    'gs://otusdemo/taxi-000000000096.csv',
    'gs://otusdemo/taxi-000000000097.csv',
    'gs://otusdemo/taxi-000000000098.csv',
    'gs://otusdemo/taxi-000000000099.csv',
    'gs://otusdemo/taxi-000000000100.csv',
    'gs://otusdemo/taxi-000000000101.csv',
    'gs://otusdemo/taxi-000000000102.csv',
    'gs://otusdemo/taxi-000000000103.csv',
    'gs://otusdemo/taxi-000000000104.csv',
    'gs://otusdemo/taxi-000000000105.csv',
    'gs://otusdemo/taxi-000000000106.csv',
    'gs://otusdemo/taxi-000000000107.csv',
    'gs://otusdemo/taxi-000000000108.csv',
    'gs://otusdemo/taxi-000000000109.csv',
    'gs://otusdemo/taxi-000000000110.csv',
    'gs://otusdemo/taxi-000000000111.csv',
    'gs://otusdemo/taxi-000000000112.csv',
    'gs://otusdemo/taxi-000000000113.csv',
    'gs://otusdemo/taxi-000000000114.csv',
    'gs://otusdemo/taxi-000000000115.csv',
    'gs://otusdemo/taxi-000000000116.csv',
    'gs://otusdemo/taxi-000000000117.csv',
    'gs://otusdemo/taxi-000000000118.csv',
    'gs://otusdemo/taxi-000000000119.csv',
    'gs://otusdemo/taxi-000000000120.csv',
    'gs://otusdemo/taxi-000000000121.csv',
    'gs://otusdemo/taxi-000000000122.csv',
    'gs://otusdemo/taxi-000000000123.csv',
    'gs://otusdemo/taxi-000000000124.csv',
    'gs://otusdemo/taxi-000000000125.csv',
    'gs://otusdemo/taxi-000000000126.csv',
    'gs://otusdemo/taxi-000000000127.csv',
    'gs://otusdemo/taxi-000000000128.csv',
    'gs://otusdemo/taxi-000000000129.csv',
    'gs://otusdemo/taxi-000000000130.csv',
    'gs://otusdemo/taxi-000000000131.csv',
    'gs://otusdemo/taxi-000000000132.csv',
    'gs://otusdemo/taxi-000000000133.csv',
    'gs://otusdemo/taxi-000000000134.csv',
    'gs://otusdemo/taxi-000000000135.csv',
    'gs://otusdemo/taxi-000000000136.csv',
    'gs://otusdemo/taxi-000000000137.csv',
    'gs://otusdemo/taxi-000000000138.csv',
    'gs://otusdemo/taxi-000000000139.csv',
    'gs://otusdemo/taxi-000000000140.csv',
    'gs://otusdemo/taxi-000000000141.csv',
    'gs://otusdemo/taxi-000000000142.csv',
    'gs://otusdemo/taxi-000000000143.csv',
    'gs://otusdemo/taxi-000000000144.csv',
    'gs://otusdemo/taxi-000000000145.csv',
    'gs://otusdemo/taxi-000000000146.csv',
    'gs://otusdemo/taxi-000000000147.csv',
    'gs://otusdemo/taxi-000000000148.csv',
    'gs://otusdemo/taxi-000000000149.csv',
    'gs://otusdemo/taxi-000000000150.csv',
    'gs://otusdemo/taxi-000000000151.csv',
    'gs://otusdemo/taxi-000000000152.csv',
    'gs://otusdemo/taxi-000000000153.csv',
    'gs://otusdemo/taxi-000000000154.csv',
    'gs://otusdemo/taxi-000000000155.csv',
    'gs://otusdemo/taxi-000000000156.csv',
    'gs://otusdemo/taxi-000000000157.csv',
    'gs://otusdemo/taxi-000000000158.csv',
    'gs://otusdemo/taxi-000000000159.csv',
    'gs://otusdemo/taxi-000000000160.csv',
    'gs://otusdemo/taxi-000000000161.csv',
    'gs://otusdemo/taxi-000000000162.csv',
    'gs://otusdemo/taxi-000000000163.csv',
    'gs://otusdemo/taxi-000000000164.csv',
    'gs://otusdemo/taxi-000000000165.csv',
    'gs://otusdemo/taxi-000000000166.csv',
    'gs://otusdemo/taxi-000000000167.csv',
    'gs://otusdemo/taxi-000000000168.csv',
    'gs://otusdemo/taxi-000000000169.csv',
    'gs://otusdemo/taxi-000000000170.csv',
    'gs://otusdemo/taxi-000000000171.csv',
    'gs://otusdemo/taxi-000000000172.csv',
    'gs://otusdemo/taxi-000000000173.csv',
    'gs://otusdemo/taxi-000000000174.csv',
    'gs://otusdemo/taxi-000000000175.csv',
    'gs://otusdemo/taxi-000000000176.csv',
    'gs://otusdemo/taxi-000000000177.csv',
    'gs://otusdemo/taxi-000000000178.csv',
    'gs://otusdemo/taxi-000000000179.csv',
    'gs://otusdemo/taxi-000000000180.csv',
    'gs://otusdemo/taxi-000000000181.csv',
    'gs://otusdemo/taxi-000000000182.csv',
    'gs://otusdemo/taxi-000000000183.csv',
    'gs://otusdemo/taxi-000000000184.csv',
    'gs://otusdemo/taxi-000000000185.csv',
    'gs://otusdemo/taxi-000000000186.csv',
    'gs://otusdemo/taxi-000000000187.csv',
    'gs://otusdemo/taxi-000000000188.csv',
    'gs://otusdemo/taxi-000000000189.csv',
    'gs://otusdemo/taxi-000000000190.csv',
    'gs://otusdemo/taxi-000000000191.csv',
    'gs://otusdemo/taxi-000000000192.csv',
    'gs://otusdemo/taxi-000000000193.csv',
    'gs://otusdemo/taxi-000000000194.csv',
    'gs://otusdemo/taxi-000000000195.csv',
    'gs://otusdemo/taxi-000000000196.csv',
    'gs://otusdemo/taxi-000000000197.csv',
    'gs://otusdemo/taxi-000000000198.csv',
    'gs://otusdemo/taxi-000000000199.csv',
    'gs://otusdemo/taxi-000000000200.csv',
    'gs://otusdemo/taxi-000000000201.csv',
    'gs://otusdemo/taxi-000000000202.csv',
    'gs://otusdemo/taxi-000000000203.csv',
    'gs://otusdemo/taxi-000000000204.csv',
    'gs://otusdemo/taxi-000000000205.csv',
    'gs://otusdemo/taxi-000000000206.csv',
    'gs://otusdemo/taxi-000000000207.csv',
    'gs://otusdemo/taxi-000000000208.csv',
    'gs://otusdemo/taxi-000000000209.csv',
    'gs://otusdemo/taxi-000000000210.csv',
    'gs://otusdemo/taxi-000000000211.csv',
    'gs://otusdemo/taxi-000000000212.csv',
    'gs://otusdemo/taxi-000000000213.csv',
    'gs://otusdemo/taxi-000000000214.csv',
    'gs://otusdemo/taxi-000000000215.csv',
    'gs://otusdemo/taxi-000000000216.csv',
    'gs://otusdemo/taxi-000000000217.csv',
    'gs://otusdemo/taxi-000000000218.csv',
    'gs://otusdemo/taxi-000000000219.csv',
    'gs://otusdemo/taxi-000000000220.csv',
    'gs://otusdemo/taxi-000000000221.csv',
    'gs://otusdemo/taxi-000000000222.csv',
    'gs://otusdemo/taxi-000000000223.csv',
    'gs://otusdemo/taxi-000000000224.csv',
    'gs://otusdemo/taxi-000000000225.csv',
    'gs://otusdemo/taxi-000000000226.csv',
    'gs://otusdemo/taxi-000000000227.csv',
    'gs://otusdemo/taxi-000000000228.csv',
    'gs://otusdemo/taxi-000000000229.csv',
    'gs://otusdemo/taxi-000000000230.csv',
    'gs://otusdemo/taxi-000000000231.csv',
    'gs://otusdemo/taxi-000000000232.csv',
    'gs://otusdemo/taxi-000000000233.csv',
    'gs://otusdemo/taxi-000000000234.csv',
    'gs://otusdemo/taxi-000000000235.csv',
    'gs://otusdemo/taxi-000000000236.csv',
    'gs://otusdemo/taxi-000000000237.csv',
    'gs://otusdemo/taxi-000000000238.csv',
    'gs://otusdemo/taxi-000000000239.csv',
    'gs://otusdemo/taxi-000000000240.csv',
    'gs://otusdemo/taxi-000000000241.csv',
    'gs://otusdemo/taxi-000000000242.csv',
    'gs://otusdemo/taxi-000000000243.csv',
    'gs://otusdemo/taxi-000000000244.csv',
    'gs://otusdemo/taxi-000000000245.csv',
    'gs://otusdemo/taxi-000000000246.csv',
    'gs://otusdemo/taxi-000000000247.csv',
    'gs://otusdemo/taxi-000000000248.csv',
    'gs://otusdemo/taxi-000000000249.csv',
    'gs://otusdemo/taxi-000000000250.csv',
    'gs://otusdemo/taxi-000000000251.csv',
    'gs://otusdemo/taxi-000000000252.csv',
    'gs://otusdemo/taxi-000000000253.csv',
    'gs://otusdemo/taxi-000000000254.csv',
    'gs://otusdemo/taxi-000000000255.csv',
    'gs://otusdemo/taxi-000000000256.csv',
    'gs://otusdemo/taxi-000000000257.csv',
    'gs://otusdemo/taxi-000000000258.csv',
    'gs://otusdemo/taxi-000000000259.csv',
    'gs://otusdemo/taxi-000000000260.csv',
    'gs://otusdemo/taxi-000000000261.csv',
    'gs://otusdemo/taxi-000000000262.csv',
    'gs://otusdemo/taxi-000000000263.csv',
    'gs://otusdemo/taxi-000000000264.csv',
    'gs://otusdemo/taxi-000000000265.csv',
    'gs://otusdemo/taxi-000000000266.csv',
    'gs://otusdemo/taxi-000000000267.csv',
    'gs://otusdemo/taxi-000000000268.csv',
    'gs://otusdemo/taxi-000000000269.csv',
    'gs://otusdemo/taxi-000000000270.csv',
    'gs://otusdemo/taxi-000000000271.csv',
    'gs://otusdemo/taxi-000000000272.csv',
    'gs://otusdemo/taxi-000000000273.csv',
    'gs://otusdemo/taxi-000000000274.csv',
    'gs://otusdemo/taxi-000000000275.csv',
    'gs://otusdemo/taxi-000000000276.csv',
    'gs://otusdemo/taxi-000000000277.csv',
    'gs://otusdemo/taxi-000000000278.csv',
    'gs://otusdemo/taxi-000000000279.csv',
    'gs://otusdemo/taxi-000000000280.csv',
    'gs://otusdemo/taxi-000000000281.csv',
    'gs://otusdemo/taxi-000000000282.csv',
    'gs://otusdemo/taxi-000000000283.csv',
    'gs://otusdemo/taxi-000000000284.csv',
    'gs://otusdemo/taxi-000000000285.csv',
    'gs://otusdemo/taxi-000000000286.csv',
    'gs://otusdemo/taxi-000000000287.csv',
    'gs://otusdemo/taxi-000000000288.csv',
    'gs://otusdemo/taxi-000000000289.csv',
    'gs://otusdemo/taxi-000000000290.csv',
    'gs://otusdemo/taxi-000000000291.csv',
    'gs://otusdemo/taxi-000000000292.csv',
    'gs://otusdemo/taxi-000000000293.csv'
)
WITH skip = '1', nullif = '';
-- Time: 2267.594s total (execution 2267.593s / network 0.001s)

Примечания:

• поскольку мы экспортнули данные из bigquery в тот же проект что и kubernetes имеем безпроблемный доступ к google storage без возни с паролями
• к сожалению не сработал импорт по wildcard и пришлось перечислять все 294 файла, не стал разбираться так как натурально время деньги, кластер же работает :)

Проверяем запрос:

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*)
FROM taxi_trips
group by payment_type
order by 3 desc;
-- Time: 602.006s total (execution 602.006s / network 0.001s)
-- Time: 578.319s total (execution 578.318s / network 0.001s)
-- Time: 565.098s total (execution 565.097s / network 0.001s)

Химичим

CREATE INDEX payment_type_idx ON taxi_trips (payment_type, tips, fare);
-- Time: 1575.747s total (execution 0.060s / network 1575.688s)

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*)
FROM taxi_trips
group by payment_type
order by 3 desc;
-- Time: 161.803s total (execution 161.803s / network 0.001s)
-- Time: 147.007s total (execution 147.007s / network 0.000s)
-- Time: 148.132s total (execution 148.132s / network 0.000s)

Postgres

Для честного эксперимента, я делаю машинку похожую на мощностя которые были доступны cockroach, а именно 12cpu, 48ram

Скрипт для "склеивания" csv фалов

for i in $(gsutil ls gs://otusdemo)
do
        gsutil cp $i .
        tail -n +2 taxi-*.csv >> data.csv
        rm taxi-*.csv
done

Дальше ставим постгрю

apt -qq update && apt -qq install -y postgresql postgresql-contrib

Перед непосредственной вставкой небольшой тюнинг:

echo "
# DB Version: 12
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 48 GB
# CPUs num: 12
# Connections num: 100
# Data Storage: ssd

max_connections = 100
shared_buffers = 12GB
effective_cache_size = 36GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 157286kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 12
max_parallel_workers_per_gather = 4
max_parallel_workers = 12
max_parallel_maintenance_workers = 4
" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

systemctl start postgresql

sudo -u postgres psql -c "CREATE DATABASE demo"

sudo -u postgres psql demo -c "create table taxi_trips (
unique_key text,
taxi_id text,
trip_start_timestamp TIMESTAMP,
trip_end_timestamp TIMESTAMP,
trip_seconds bigint,
trip_miles numeric,
pickup_census_tract bigint,
dropoff_census_tract bigint,
pickup_community_area bigint,
dropoff_community_area bigint,
fare numeric,
tips numeric,
tolls numeric,
extras numeric,
trip_total numeric,
payment_type text,
company text,
pickup_latitude numeric,
pickup_longitude numeric,
pickup_location text,
dropoff_latitude numeric,
dropoff_longitude numeric,
dropoff_location text
)"

sudo -u postgres psql demo -c "\timing" -c "COPY taxi_trips FROM '/data.csv' (FORMAT csv, null '', DELIMITER ',')"
# COPY 196188630
# Time: 1634930.640 ms (27:14.931)

sudo -u postgres psql demo -c "\timing" -c "VACUUM ANALYZE"
# Time: 347251.542 ms (05:47.252)

sudo -u postgres psql demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc"
# Time: 334716.469 ms (05:34.716)
# Time: 334546.379 ms (05:34.546)
# Time: 334552.559 ms (05:34.553)

Химичим:

sudo -u postgres psql demo -c "\timing" -c "CREATE INDEX payment_type_idx ON taxi_trips (payment_type, tips, fare)"
# Time: 691848.946 ms (11:31.849)

sudo -u postgres psql demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc"
# Time: 22759.725 ms (00:22.760)
# Time: 22507.007 ms (00:22.507)
# Time: 23075.058 ms (00:23.075)

Bonus

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

По началу думал прям добавить три диска и отдельные tablespaces, но затем, посмотрев на телеметрию понял что в фактический диск мы не упираемся (где то 150 mbs) да и как оказалось я выел квоту и мне гугла просто напросто не дал резервировать еще диски, потому решил остановаиться просто на варианте с партиционированной табличкой

drop table taxi_trips;

create table taxi_trips (
unique_key text,
taxi_id text,
trip_start_timestamp TIMESTAMP,
trip_end_timestamp TIMESTAMP,
trip_seconds bigint,
trip_miles numeric,
pickup_census_tract bigint,
dropoff_census_tract bigint,
pickup_community_area bigint,
dropoff_community_area bigint,
fare numeric,
tips numeric,
tolls numeric,
extras numeric,
trip_total numeric,
payment_type text,
company text,
pickup_latitude numeric,
pickup_longitude numeric,
pickup_location text,
dropoff_latitude numeric,
dropoff_longitude numeric,
dropoff_location text
) PARTITION BY LIST (payment_type);

CREATE TABLE taxi_trips_cash PARTITION OF taxi_trips FOR VALUES IN ('Cash');

CREATE TABLE taxi_trips_credit_card PARTITION OF taxi_trips FOR VALUES IN ('Credit Card');

CREATE TABLE taxi_trips_other PARTITION OF taxi_trips DEFAULT;

COPY taxi_trips FROM '/data.csv' (FORMAT csv, null '', DELIMITER ',');
-- Time: 1642310.568 ms (27:22.311)

VACUUM ANALYZE;
-- Time: 346734.163 ms (05:46.734)

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc;
-- Time: 192897.569 ms (03:12.898)
-- Time: 194734.589 ms (03:14.735)
-- Time: 275556.895 ms (04:35.557)


CREATE INDEX cash_payment_type_idx ON taxi_trips_cash (payment_type, tips, fare);
-- Time: 385334.999 ms (06:25.335)

CREATE INDEX credit_card_payment_type_idx ON taxi_trips_credit_card (payment_type, tips, fare);
-- Time: 256547.121 ms (04:16.547)

CREATE INDEX other_payment_type_idx ON taxi_trips_other (payment_type, tips, fare);
-- Time: 11146.934 ms (00:11.147)


SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc;
-- Time: 25717.701 ms (00:25.718)
-- Time: 26760.766 ms (00:26.761)
-- Time: 25084.045 ms (00:25.084)

hw15.md

Citus Kubernetes

TDLR: 3.5 сек

Подготовку данных сделал по аналогии с hw10.md

Дальше пришлось похимичить с yml для кубера что бы собрать кластер

kube.yml

---
apiVersion: v1
kind: Pod
metadata:
  name: gateway
  labels:
    app: gateway
spec:
  containers:
    - name: gateway
      image: citusdata/citus:10.1
      args:
        - -c
        - citus.shard_replication_factor=2
      env:
        - name: POSTGRES_PASSWORD
          value: demo
        - name: POSTGRES_DB
          value: demo
      ports:
        - name: gateway
          containerPort: 5432
          protocol: TCP
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: worker
  labels:
    app: worker
spec:
  serviceName: worker
  replicas: 4
  selector:
    matchLabels:
      app: worker
  template:
    metadata:
      labels:
        app: worker
    spec:
      containers:
      - name: worker
        image: citusdata/citus:10.1
        args:
          - -c
          - citus.shard_replication_factor=2
          - -c
          - shared_buffers=2560MB
          - -c
          - effective_cache_size=7680MB
          - -c
          - maintenance_work_mem=640MB
          - -c
          - checkpoint_completion_target=0.9
          - -c
          - wal_buffers=16MB
          - -c
          - default_statistics_target=100
          - -c
          - random_page_cost=1.1
          - -c
          - effective_io_concurrency=200
          - -c
          - work_mem=13107kB
          - -c
          - min_wal_size=2GB
          - -c
          - max_wal_size=8GB
          - -c
          - max_worker_processes=3
          - -c
          - max_parallel_workers_per_gather=2
          - -c
          - max_parallel_workers=3
          - -c
          - max_parallel_maintenance_workers=2
        env:
          - name: POSTGRES_PASSWORD
            value: demo
          - name: POSTGRES_DB
            value: demo
          - name: PGPASSWORD
            value: demo
          - name: MY_POD_IP
            valueFrom:
              fieldRef:
                fieldPath: status.podIP
        ports:
          - name: worker
            containerPort: 5432
            protocol: TCP
        resources:
          limits:
            cpu: "3"
            memory: "10000Mi"
          requests:
            cpu: "3"
            memory: "10000Mi"
        lifecycle:
          postStart:
            exec:
              command: ["/bin/sh", "-c", "sleep 5 && psql -U postgres -h gateway -d demo -p 5432 -c \"SELECT citus_add_node('$MY_POD_IP', 5432)\""]
        volumeMounts:
        - name: worker
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: worker
    spec:
      accessModes: 
        - ReadWriteOnce
      resources:
        requests:
          storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: gateway
spec:
  clusterIP: None
  selector:
    app: gateway
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432

Примечания:

• на выходе получаем gateway, worker-1, ..., worker-N
• каждый воркер запускаясь, регистрирует себя в кластере, соотв работает scale
• не стал упарываться с секретами и паролями, т.к. и так yml большой получился
• настройки из pgtune для 3cpu, 10gb
• после применения yml дожидаемся пока создадутся все поды и проверям что кластер собрался до кучи, выполняя kubectl exec -it gateway -- psql -U postgres -d demo -c "SELECT citus_get_active_worker_nodes()"

Создаем и шардируем табличку

create table taxi_trips (
  unique_key text,
  taxi_id text,
  trip_start_timestamp TIMESTAMP,
  trip_end_timestamp TIMESTAMP,
  trip_seconds bigint,
  trip_miles numeric,
  pickup_census_tract bigint,
  dropoff_census_tract bigint,
  pickup_community_area bigint,
  dropoff_community_area bigint,
  fare numeric,
  tips numeric,
  tolls numeric,
  extras numeric,
  trip_total numeric,
  payment_type text,
  company text,
  pickup_latitude numeric,
  pickup_longitude numeric,
  pickup_location text,
  dropoff_latitude numeric,
  dropoff_longitude numeric,
  dropoff_location text
);

SELECT create_distributed_table('taxi_trips', 'unique_key');

Ну и собственно говоря сама заливка (пришлось повозиться, по концовке просто провалился в главную ноду и прямо от туда все делал)

sudo -u postgres psql demo -c "\timing" -c "COPY taxi_trips FROM '/data10.csv' (FORMAT csv, null '', DELIMITER ',')"
-- Time: 400831.554 ms (06:40.832)

После заливки проверяем что получилось

kubectl exec -it gateway -- psql -U postgres -d demo -c "\dt+"
# single, empty taxi_trips table
kubectl exec -it worker-0 -- psql -U postgres -d demo -c "\dt+"
# 16 taxi_trips_00000 tables, each 342mb
# ...
kubectl exec -it worker-3 -- psql -U postgres -d demo -c "\dt+"
# 16 taxi_trips_00000 tables, each 342mb

Ну и собственно говоря наш запрос:

kubectl exec -it gateway -- psql -U postgres -d demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc"
-- Time: 5021.066 ms (00:05.021)
-- Time: 3621.682 ms (00:03.622)
-- Time: 3491.556 ms (00:03.492)
-- Time: 3425.094 ms (00:03.425)
-- Time: 3454.331 ms (00:03.454)

Пробуем похимичить

kubectl exec -it gateway -- psql -U postgres -d demo -c "\timing" -c "CREATE INDEX payment_type_idx ON taxi_trips (payment_type, tips, fare)"
-- Time: 50386.118 ms (00:50.386)

kubectl exec -it gateway -- psql -U postgres -d demo -c "\timing" -c "SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc"
-- Time: 1926.075 ms (00:01.926)
-- Time: 1845.834 ms (00:01.846)
-- Time: 1819.195 ms (00:01.819)

hw16.md

Postgres like services in public clouds

После эпопеи с aurora и spanner тестировать azure sql никакого желания нет, очевидно, что если он сможет пережевать весь объем и создать колончный индекс он всех уделает, но это надо буквально целый день времени

Spanner разочаровал производительностью, получилось загнать всего 5гб данных и при этом скорость отработки запроса 3 секунды, что не есть удовлетворительно.

Aurora при этом поглотила весь датасет, но так и не отработала запрос, после 12+ часов сумарного ожидания, не дождавшись погасил все инстанцы

Cloud Spanner

TLDR: время 3.5 сек на датасете в 5гб

Табличка

CREATE TABLE taxi_trips (
unique_key	STRING(40),
taxi_id	STRING(128),
trip_start_timestamp	TIMESTAMP,
trip_end_timestamp	TIMESTAMP,
trip_seconds	INT64,
trip_miles	FLOAT64,
pickup_census_tract	INT64,
dropoff_census_tract	INT64,
pickup_community_area	INT64,
dropoff_community_area	INT64,
fare	FLOAT64,
tips	FLOAT64,
tolls	FLOAT64,
extras	FLOAT64,
trip_total	FLOAT64,
payment_type	STRING(50),
company	STRING(MAX),
pickup_latitude	FLOAT64,
pickup_longitude	FLOAT64,
pickup_location	STRING(MAX),
dropoff_latitude	FLOAT64,
dropoff_longitude	FLOAT64,
dropoff_location	STRING(MAX)
) PRIMARY KEY (unique_key);

Импортер

Program.cs

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using Google.Cloud.Spanner.Data;
using Importer;
using Newtonsoft.Json;
using Enumerable = System.Linq.Enumerable;
using QueryOptions = Google.Cloud.BigQuery.V2.QueryOptions;
namespace importer
{
public static class Program
{
private static int totalRows = 0;
    public static async Task Main()
    {
        Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", "/Users/mac/OneDrive/Desktop/importer/credentials.json");
        
        var timer = Stopwatch.StartNew();
        var queue = new ConcurrentQueue<string>();
        var spanner = new SpannerConnection("Data Source=projects/pgotus/instances/pgotus/databases/chicago_taxi_trips");
        spanner.Open();

        var writers = new List<Task>();
        for (var i = 0; i < 40; i++)
        {
            writers.Add(Task.Run(() =>
            {
                Thread.Sleep(5000);
                string sql;
                while (queue.TryDequeue(out sql))
                {
                    try
                    {
                        var rows = spanner.CreateDmlCommand(sql).ExecuteNonQuery();
                        Interlocked.Add(ref totalRows, rows);
                        Console.WriteLine($"{totalRows} rows inserted in {timer.Elapsed}");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"{ex.Message}");
                        queue.Enqueue(sql);
                        Thread.Sleep(1000);
                    }
                }
            }));
        }
        
        File.ReadLines("D:/taxi_trips/data10.csv").Skip(1).Select(line => line
            .Split(",")
            .Select(cell => cell.Trim())
            .Select(cell => string.IsNullOrEmpty(cell) ? "NULL" : double.TryParse(cell, out _) ? cell : $"'{cell}'"))
            .Select(cells => $"({string.Join(", ", cells)})")
            .Chunk(100)
            .Select(values => $"INSERT INTO taxi_trips (unique_key, taxi_id, trip_start_timestamp, trip_end_timestamp, trip_seconds, trip_miles, pickup_census_tract, dropoff_census_tract, pickup_community_area, dropoff_community_area, fare, tips, tolls, extras, trip_total, payment_type, company, pickup_latitude, pickup_longitude, pickup_location, dropoff_latitude, dropoff_longitude, dropoff_location) VALUES {string.Join(", ", values)}")
            .AsParallel().ForAll(sql => queue.Enqueue(sql));
        
        await Task.WhenAll(writers);
        Console.WriteLine($"DONE IN {timer.Elapsed}");
    }
}

}

Примечания:

• получилось разогнать спаннер до вставки 1млн записей за 30мин, что совсем не удовлетворительно, при этом в панеле вижу полную утилизацию, при том что я на тысяче юнитов и стоить все это будет $1K/mo 🤔
• скрипт по вставке один поток вычитывает и обрабатывает сроки из csv, затем через очередь в 40 потоков проивзодит вставки по 100 строк, соотв за один проход мы вставляем 4К строк
• утилизация машины с которой производил вставку - никакая - можно было бы пробовать еще больше потоков, но нет смысла из-за того что мы в полку положили спаннер
• после полутора часа поднял до 2К процессорных юнитов, но значимого эффекта не дало
• через почти 4 часа, вставилось 10млн строк и приблизительно 5гб из десяти

Пробую прогнать наш запрос:

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc
-- 15.43 s elapsed
-- 11.21 s elapsed
-- 5.73 s elapsed
-- 4.11 s elapsed
-- 4.93 s elapsed
-- 3.55 s elapsed
-- 2.93 s elapsed
-- 10.29 s elapsed
-- 3.76 s elapsed
-- 3.96 s elapsed

Amazon Aurora

Аврора db.r5.2xlarge (8cpu, 64ram)

Создал табличку

create table taxi_trips (...)

create table taxi_trips (
unique_key text,
taxi_id text,
trip_start_timestamp text,
trip_end_timestamp text,
trip_seconds bigint,
trip_miles numeric,
pickup_census_tract bigint,
dropoff_census_tract bigint,
pickup_community_area bigint,
dropoff_community_area bigint,
fare numeric,
tips numeric,
tolls numeric,
extras numeric,
trip_total numeric,
payment_type text,
company text,
pickup_latitude numeric,
pickup_longitude numeric,
pickup_location text,
dropoff_latitude numeric,
dropoff_longitude numeric,
dropoff_location text
);

Согластно инструкции загнал предварительно импортированный в s3 csv файл

LOAD DATA FROM S3 ... INTO TABLE taxi_trips ...

LOAD DATA FROM S3 's3://otuspg/data.csv'
    INTO TABLE taxi_trips
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
@uk,
@taxi_id,
@trip_start_timestamp,
@trip_end_timestamp,
@trip_seconds,
@trip_miles,
@pickup_census_tract,
@dropoff_census_tract,
@pickup_community_area,
@dropoff_community_area,
@fare,
@tips,
@tolls,
@extras,
@trip_total,
@payment_type,
@company,
@pickup_latitude,
@pickup_longitude,
@pickup_location,
@dropoff_latitude,
@dropoff_longitude,
@dropoff_location
)
SET
unique_key = NULLIF(@uk, ''),
taxi_id = NULLIF(@taxi_id, ''),
trip_start_timestamp = NULLIF(@trip_start_timestamp, ''),
trip_end_timestamp = NULLIF(@trip_end_timestamp, ''),
trip_seconds = NULLIF(@trip_seconds, ''),
trip_miles = NULLIF(@trip_miles, ''),
pickup_census_tract = NULLIF(@pickup_census_tract, ''),
dropoff_census_tract = NULLIF(@dropoff_census_tract, ''),
pickup_community_area = NULLIF(@pickup_community_area, ''),
dropoff_community_area = NULLIF(@dropoff_community_area, ''),
fare = NULLIF(@fare, ''),
tips = NULLIF(@tips, ''),
tolls = NULLIF(@tolls, ''),
extras = NULLIF(@extras, ''),
trip_total = NULLIF(@trip_total, ''),
payment_type = NULLIF(@payment_type, ''),
company = NULLIF(@company, ''),
pickup_latitude = NULLIF(@pickup_latitude, ''),
pickup_longitude = NULLIF(@pickup_longitude, ''),
pickup_location = NULLIF(@pickup_location, ''),
dropoff_latitude = NULLIF(@dropoff_latitude, ''),
dropoff_longitude = NULLIF(@dropoff_longitude, ''),
dropoff_location = NULLIF(@dropoff_location, '')
;

Примечаниe: пришлось повозиться с ролями и параметрами что бы достучаться до s3 и побороть ошибку "Both aurora_load_from_s3_role and aws_default_s3_role are not specified, please see documentation for more details", нужно было создать отдельную групу параметров, так как група по умолчанию не меняется и уже там указать новую роль для обоих настроек и перекрикрепить к базе

Так ну и наш запрос:

SELECT payment_type, round(sum(tips)/sum(tips+fare)*100) tips_persent, count(*) FROM taxi_trips group by payment_type order by 3 desc;
-- 

Примечания:

• в самом начале загонял файлик на 256мб и запрос там работал минутами
• после импорта выполнял optimize table taxi_trips
• datagrip почему то ни в какую не хотел отправлять запрос один стейтментом, пришлось мудрить с докерами и консольным клиентом
• в процессе импорта mysql клиент отвалился, но судя по графикам мониторинга запрос все еще фигачит
• импорт занял около 2-3 часов
• прогон optimize - занимает бесконечность, я так и не понял доделался или нет, после 3х часов уже забил
• первых пару запросов не отработали и mysql отвалился, а cpu ушел в полку
• прошло еще два часа и cpu все так же в полке

hw2.md

• ✅ создайте виртуальную машину - hw2-1
• ✅ поставьте на нее PostgreSQL - происходит автоматом т.к. зашито в шаблон машины
• ✅ проверьте что кластер запущен - можно просто pg_lsclusters без sudo, кластер online
• ✅ произвольную таблицу с произвольным содержимым - sudo -u postgres psql -c "create database sample" && sudo -u postgres pgbench -i -s 1 sample
• ✅ остановите postgres например через sudo -u postgres pg_ctlcluster 12 main stop
• ✅ создайте новый standard persistent диск - hw2-2
• ✅ добавьте свеже-созданный диск к виртуальной машине
• ✅ проинициализируйте диск (см. disk)
• ✅ сделайте пользователя postgres владельцем /mnt/data - нужно выполнять из под sudo
• ✅ перенесите содержимое /var/lib/postgres/13 в /mnt/data - примечание, в 20.04 из apt 12-я версия, как следствие пути чуть другие, так же требует привилегий sudo mv /var/lib/postgresql/12 /mnt/data
• ✅ попытайтесь запустить кластер - sudo -u postgres pg_ctlcluster 12 main start
• ❌ не получилось, оно сразу же ругается Error: /var/lib/postgresql/12/main is not accessible or does not exist
• ✅ задание - echo "data_directory = '/mnt/data/12/main'" | sudo tee -a /etc/postgresql/12/main/postgresql.conf, наобходимо указать правильный(новый) data_directoryu
• ✅ попытайтесь запустить кластер - sudo -u postgres pg_ctlcluster 12 main start - все завелось, т.к. теперь postgres видит папку с своими файликами
• ✅ проверьте содержимое ранее созданной таблицы - sudo -u postgres psql sample -c "select count(*) from pgbench_accounts"

⭐ disaster scenario

• гасим hw2-1 но не удаляем (т.к. по умолчанию следом за ней удаляться диски) - sudo poweroff
• создаем из шаблона hw2-star
• останов базы sudo systemctl stop postgresql
• удалем папку sudo rm -rf /var/lib/postgresql
• примечание: пришлось отвязать диск от hw2-1, только после этого дало его привязать к этой машинке
• проверим что система видит добавленный диск - sudo lsblk --fs
• запись в fstab - echo "LABEL=hw2-2 /var/lib/postgresql ext4 defaults 0 2" | sudo tee -a /etc/fstab
• нужно создать папку - mkdir /var/lib/postgresql
• монтируем - sudo mount -a
• проверяем что файлы на месте - sudo ls -la /var/lib/postgresql/12/main/
• запускаемся - sudo systemctl start postgresql
• проверяем что с базой - pg_lsclusters - все ок, мы online
• проверяем данные - sudo -u postgres psql sample -c "select count(*) from pgbench_accounts" - 🎉

Если работать с виртуальными машинами, это наиболее предпочтительный способ, т.к. внешние диски будет сильно проще переносить между машинками в случае такой необходимости.

disk

sudo apt update && sudo apt install -y parted
sudo parted -l | grep Error # /dev/sdb
lsblk # will show that sdb has no partitions
sudo parted /dev/sdb mklabel gpt # our disk will be partitioned via gpt standard
sudo parted -a opt /dev/sdb mkpart primary ext4 0% 100% # create partition
sudo mkfs.ext4 -L hw2-2 /dev/sdb1 # format partition and give it `hw2-2` label
# sudo e2label /dev/sdb1 newlabel # change label example
sudo lsblk --fs # list partitions with labels
# sudo lsblk -o NAME,FSTYPE,LABEL,UUID,MOUNTPOINT # alternative approach
sudo mkdir -p /mnt/data # directory where partition will be mounted
# sudo mount -o defaults /dev/sdb1 /mnt/data # temporary mount
echo "LABEL=hw2-2 /mnt/data ext4 defaults 0 2" | sudo tee -a /etc/fstab # permanent mount config
sudo mount -a # remount everything
df -h # check that disk present
ls -f /mnt/data # there will be `lost+found` directory which means that we are at ext4 root
echo "success" | sudo tee /mnt/data/test_file # check write
cat /mnt/data/test_file # check read
sudo rm /mnt/data/test_file # check delete

hw3.md

Prepartions

• сделать в GCE инстанс с Ubuntu 20.04
• поставить на нем Docker Engine
• сделать каталог /var/lib/postgres
• развернуть контейнер с PostgreSQL 13 смонтировав в него /var/lib/postgres

Да бы не делать каждый раз, завел еще один шаблон pg-docker-template в котором вот такой startup script

# install docker
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh
rm get-docker.sh

# data
sudo mkdir -p /var/lib/postgres

# postgres
sudo docker run -d --name=pg --restart=always -e POSTGRES_PASSWORD='P@ssword' -e POSTGRES_USER='otus' -e POSTGRES_DB='otus' -v /var/lib/postgres:/var/lib/postgresql/data  -p 5432:5432 postgres:13-alpine

На основе этого шаблона сделал виртуалку hw3

Tables

• развернуть контейнер с клиентом postgres
• подключится из контейнера с клиентом к контейнеру с сервером и сделать таблицу с парой строк

Не уверен правильно ли понял, сделал разными вариантами:

sudo docker exec -it pg psql -U otus otus -c 'create table messages(message text)'
sudo docker run -it --rm --link=pg postgres:13-alpine psql -h pg -U otus otus -c "insert into messages values ('Hello World')"

Remote

• подключится к контейнеру с сервером с ноутбука/компьютера извне инстансов GCP

На своей машине выполнил вот такое:

docker run -it --rm postgres:13-alpine psql -h 35.207.74.65 -U otus otus -c "select * from messages"

Recreate

• удалить контейнер с сервером
• создать его заново
• подключится снова из контейнера с клиентом к контейнеру с сервером
• проверить, что данные остались на месте

# stop and remove
sudo docker stop pg
sudo docker rm pg

# recreate
sudo docker run -d --name=pg --restart=always -e POSTGRES_PASSWORD='P@ssword' -e POSTGRES_USER='otus' -e POSTGRES_DB='otus' -v /var/lib/postgres:/var/lib/postgresql/data  -p 5432:5432 postgres:13-alpine

# check
sudo docker exec -it pg psql -U otus otus -c 'select * from messages'

Notes

• оставляйте в ЛК ДЗ комментарии что и как вы делали и как боролись с проблемами

Примечания:

• Важно монтироваться к /var/lib/postgresql/data, в первый раз делал просто на /var/lib/postgresql и ничего не сохранялось
• Вообще лучше локально завести папку с таким же путем а не /var/lib/postgres что бы меньшу путаницы было
• Сто процентов будут проблемы с правами, благо при старте контейнер фиксит овнера, но это требует отдельного разбирательства
• Есть смысл завести отдельный диск и volume и подлючать уже его
• Из логов контейнера видно что он запускается с локалями en_us, пока не ясно чем это черевато и нужно ли это править
• Из логов контейнера видно что он не производит никаких оптимизаций - нужно отдельно тюнить

hw36-backend.md

Пример бека на node, express, pg, ab

package.json

{
  "name": "rest",
  "version": "1.0.0",
  "private": true,
  "scripts": {
    "start": "nodemon index.js"
  },
  "dependencies": {
    "express": "^4.17.1",
    "pg": "^8.7.1"
  },
  "devDependencies": {
    "nodemon": "^2.0.12"
  }
}

index.js

const express = require('express')
const { Pool } = require('pg');

const pool = new Pool({
    user: process.env.POSTGRES_USERNAME || 'demo',
    host: process.env.POSTGRES_HOSTNAME || 'localhost',
    database: process.env.POSTGRES_DATABASE || 'demo',
    password: process.env.POSTGRES_PASSWORD || 'demo',
    port: process.env.POSTGRES_PORT || 5432,
})

const app = express()

app.use(express.json());

const wellKnownPeopleQueryStringParametersMiddleware = allowedFields => (req, res, next) => {
    const {query: {fields = allowedFields, limit = '10', offset = '0', name}} = req
    req.fields = Array.isArray(fields) ? fields : [fields]
    req.fields = req.fields.filter(f => allowedFields.includes(f))
    req.limit = isNaN(parseInt(limit)) || parseInt(limit) > 10 ? 10 : parseInt(limit)
    req.offset = isNaN(parseInt(offset)) ? 0 : parseInt(offset)
    req.name = name ? `${name}%` : undefined
    next()
}

const peopleMiddleware = wellKnownPeopleQueryStringParametersMiddleware(['id', 'name', 'age'])


app.get('/people/:id', peopleMiddleware, async ({params: {id}, fields}, res) => {
    try {
        const {rows} = await pool.query(`SELECT ${fields.join(',')} FROM people WHERE id = $1`, [id])
        const row = rows.shift()
        res.status(row ? 200 : 404).json(row)
    } catch (error) {
        res.status(500).json(error)
    }
})

app.delete('/people/:id', async (req, res) => {
    try {
        await pool.query(`DELETE FROM people WHERE id = $1`, [id])
        res.sendStatus(200)
    } catch (error) {
        res.status(200).json(error)
    }
})

app.get('/people', peopleMiddleware, async ({fields, limit, offset, name}, res) => {
    console.log({fields, limit, offset, name})
    try {
        const {rows} = await pool.query(`SELECT ${fields.join(',')} FROM people WHERE $1::text IS NULL OR name LIKE $1::text LIMIT $2 OFFSET $3`, [name, limit, offset])
        res.json(rows)
    } catch (error) {
        res.status(500).json(error)
    }
})

app.post('/people', peopleMiddleware, async ({body: {name, age}, fields}, res) => {
    age = isNaN(parseInt(age)) ? 0 : parseInt(age)
    if (!name || !age) {
        return res.status(400).json({message: 'name and age are required body parameters'})
    }

    try {
        const {rows} = await pool.query(`INSERT INTO people(name, age) VALUES($1, $2) RETURNING ${fields.join(',')}`, [name, age])
        res.json(rows.shift())
    } catch (error) {
        console.log(error)
        res.status(500).json(error)
    }
})

app.listen(process.env.PORT || 3000)

Dockerfile

FROM node:14-alpine

WORKDIR /app

COPY package*.json .
RUN npm install

COPY index.js .

ENV POSTGRES_HOSTNAME=demo
ENV POSTGRES_USERNAME=demo
ENV POSTGRES_PASSWORD=demo
ENV POSTGRES_DATABASE=demo
ENV POSTGRES_PORT=5432

ENTRYPOINT [ "node" ]
CMD [ "index.js" ]

docker-compose.yml

version: "3.9"
services:
  web:
    build: .
    ports:
      - 3000:3000
    environment:
      PORT: "3000"
      POSTGRES_HOSTNAME: db
      POSTGRES_USERNAME: demo
      POSTGRES_PASSWORD: demo
      POSTGRES_DATABASE: demo
      POSTGRES_PORT: "5432"
  db:
    image: postgres:12-alpine
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    environment:
      POSTGRES_PASSWORD: demo
      POSTGRES_USER: demo
      POSTGRES_DB: demo

init.sql

CREATE TABLE IF NOT EXISTS people (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    age             INT NOT NULL
);

-- INSERT INTO people(name, age) VALUES ('Michael', 7), ('Kira', 4);
INSERT INTO people(name, age)
SELECT 'Michael', 7
UNION ALL
SELECT 'Kira', 4
WHERE NOT EXISTS (SELECT * FROM people);

demo.http

### Retrieve all
GET http://localhost:3000/people

### Projection
GET http://localhost:3000/people?fields=name&fields=age&fields=foo

### Paging
GET http://localhost:3000/people?limit=1&offset=1

### Search
GET http://localhost:3000/people?name=Mi

### Create
POST http://localhost:3000/people?fields=id&fields=name
Content-Type: application/json

{
    "name": "Alex",
    "age": 35
}

### Delete
DELETE http://localhost:3000/people/5

Тест нагрузки

docker-compose up -d
docker run -it --rm --network=rest_default httpd:alpine ab -n 1000 -c 100 http://web:3000/people

Server Hostname:        web
Server Port:            3000

Document Path:          /people
Document Length:        66 bytes

Concurrency Level:      100
Time taken for tests:   0.789 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      273000 bytes
HTML transferred:       66000 bytes
Requests per second:    1267.77 [#/sec] (mean)
Time per request:       78.879 [ms] (mean)
Time per request:       0.789 [ms] (mean, across all concurrent requests)
Transfer rate:          337.99 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.4      0       2
Processing:    37   77  34.9     66     214
Waiting:        5   67  34.1     57     203
Total:         37   77  35.3     66     215

hw4.md

1 создайте новый кластер PostgresSQL 13 (на выбор - GCE, CloudSQL)

hw4 на основе шаблона из первой домашки

2 зайдите в созданный кластер под пользователем postgres

sudo -u postgres psql

3 создайте новую базу данных testdb

CREATE DATABASE testdb;

4 зайдите в созданную базу данных под пользователем postgres

\c testdb

5 создайте новую схему testnm

CREATE SCHEMA testnm;

6 создайте новую таблицу t1 с одной колонкой c1 типа integer

CREATE TABLE t1(c1 integer);

7 вставьте строку со значением c1=1

INSERT INTO t1 VALUES(1);

8 создайте новую роль readonly

CREATE ROLE readonly;

9 дайте новой роли право на подключение к базе данных testdb

ALTER ROLE readonly WITH LOGIN;

Примечание: попытка залогиниться sudo -u postgres psql -U readonly -d testdb ожидаемо не работает и падает с ошибкой Peer authentication failed for user "readonly", если мы хотим оставить без парольный, локальный вход, необходимо завести пользователя в системе или завести пароль для роли readonly и подключаться к localhost за вместо юниксового сокета

ALTER USER readonly WITH PASSWORD '123';

sudo -u postgres psql -h localhost -U readonly -d testdb

Примечание: забыли обсудить что по мимо LOGIN есть еще CONNECT

10 дайте новой роли право на использование схемы testnm

GRANT USAGE ON SCHEMA testnm TO readonly;

11 дайте новой роли право на select для всех таблиц схемы testnm

GRANT SELECT ON ALL TABLES IN SCHEMA testnm TO readonly;

12 создайте пользователя testread с паролем test123

CREATE ROLE testread WITH PASSWORD 'test123';

13 дайте поль readonly пользователю testread

GRANT readonly TO testread;

14 зайдите под пользователем testread в базу данных testdb

sudo -u postgres psql -h localhost -U testread -d testdb

Примечание: так не получилось, \du выводит новую роль и показывает что она member of readonly, но при этом пишет что не может логиниться, причем что любопытно, попытка изнутри постгри \c - testread дает тот же результат

https://www.postgresql.org/docs/current/role-membership.html

Ответ в доке: LOGIN, SUPERUSER, CREATEDB спец права которые не наследуются и требуют их явной установки

ALTER ROLE testread WITH LOGIN;

16 получилось?

(могло если вы делали сами не по шпаргалке и не упустили один существенный момент про который позже)

ERROR: permission denied for table t1

17-21 а почему так получилось с таблицей

Проблема в шаге №6, мы создали табличку в схеме public, а пользователю выдавали права на чтение для схемы testnm

22-25 fixing

DROP TABLE t1;
CREATE TABLE testnm.t1(c1 integer);
INSERT INTO testnm.t1 VALUES(1);

26-39 получилось?

🤦‍♂️ это жесть, пришлось смотреть в шпаргалку, так бы долго искал

После этих пунктов уже сложно сориентироваться по пунктам домашки и соотнести их с шпаргалкой, но в целом все получилось

Выводы:

• вся история с правами требует очень хорошего понимания, если такого понимания нет - лучше вообще не трогать
• очень удивило история с тем что права фиксируются только к созданным табличкам, это черевато проблемами, т.к. в "живом" проекте нон стоп что то меняется
• выглядит так что подобный трюк как в шпаргалке можно попробовать сделать в template1 что бы все последующие базы имели такие настройки
• любопытно вот еще что, можно drop schema public в базе сделать, по сути эффект будет похожим, так же, что бы обезопаситься убрать из search_path схему $user

ссылки которые гуглились в процессе выполнения:

• PostgreSQL - How to grant access to users?
• PostgreSQL role inheritance not working?
• 21.3. Role Membership

hw5v2.md

Autovacuum

Задача: покрутить ручки автовакуума и собрать статистику

По скольку эксперимент долгий (целый час) будем запускать в паралель.

План действий - запускаем сразу четыре виртуалки:

1. настройки по умолчанию default
2. настройки заниженые по минимуму min
3. настройки завышенные по максимуму max
4. настройки со слайдов slieds

После завершения эксперимента, собираем статистику и подготавливаем графики

Затем попытаюсь сделать выводы, аргументированно предложить свой вариант

Результаты

Так же прикрепляю скрин с v1 в которой настройки базы по умолчанию

• autovacuum_max_workers = 3 -> 10 - в нашем случае вероятно не имеет смысла больше 4, т.к. pgbench создал столько табличек, а как следствие 5-му и выше будет просто нечего чистить (плюс не стоить забывать что на нашей виртуалке всего 2 ядра)
• autovacuum_naptime = 60s -> 15s - сложно сказать не нашел способа посмотреть как долго выполнялись автовакуумы, так же в виду того что у нас нагрузка не типичная (долбим нон стоп) этот показатель стоит сделать по меньше, но сделав его сильно малым (условно 1 секунду) сервер ничем другим не будет заниматься кроме как автовакуумом
• autovacuum_vacuum_threshold = 50 -> 25 - учитывая что у нас в секунду получается в районе 400 tps, из которых только третья часть это обновления и удаления (пускай для ровного счата будет 100 tps) получается мы важдую секунду все равно превышаем этот порог, и судя по всему его можн было бы выставить в 100 и даже больше (мы ведь не хотим что бы автовакуум прям каждую секунду фигачил)
• autovacuum_vacuum_scale_factor = 0.2 -> 0.1 - тут аналогично предыдущей настройке
• autovacuum_vacuum_cost_delay = 2 -> 10 и autovacuum_vacuum_cost_limit = -1 -> 1000 - учитывая очень малый размер табличек, мы скорее всего их все за раз перерабатываем т.к. скорее всего они уже в памяти и не нужно ходить на диск

в целом настройки увеличили кол-во прогонов автовакуума в 4 раза, а вот tsp особо не поменялся и даже стал чуть хуже, если было 403 с настройками по умолчанию стало 398

Примечания

• у меня в шаблоне ssd диск, за вместо предложенного standard, возможно всюду результаты будут чуть выше чем ожидаемые
• всякие кибаны и т.п. это круто, но объективно не успею, сбор статистику будет выполнен на основе вывода прогресса pgbench и затем обработан скриптом
• виртуалки создаются на основе шаблона pg-template
• любопытный факт, впервые за все время столкнулся с тем что отобрали preemtible виртуалку :)
• в предложенных настройках maintanence 512mb что по идее половину запусков отменило, т.к. попытка выставить 10 автовакуумов уже будет больше чем есть памяти всего

Касаемо pgbench_accounts, она не вакуумировалась т.к. в обоих сценариях не был привышен порог, считал таким запросом:

select 
    n_dead_tup as fact, 
    autovacuum_vacuum_threshold::decimal + autovacuum_vacuum_scale_factor::decimal * reltuples::decimal as threshold,
    n_dead_tup >= autovacuum_vacuum_threshold::decimal + autovacuum_vacuum_scale_factor::decimal * reltuples::decimal as should_be_vacuumed
from (
select 
    n_dead_tup, 
    (select setting from pg_settings where name = 'autovacuum_vacuum_threshold') as autovacuum_vacuum_threshold, 
    (select setting from pg_settings where name = 'autovacuum_vacuum_scale_factor') as autovacuum_vacuum_scale_factor,
    (select reltuples from pg_class where relname = 'pgbench_accounts') as reltuples
    from pg_stat_all_tables where relname = 'pgbench_accounts'
) as x;

Алгоритм действий на каждой виртуалке

Создается виртуалка hw5-experiment-name, где experiment-name будет одно из: default, min, max, slides

Настройки:

echo "max_connections = 40" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "shared_buffers = 1GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_cache_size = 3GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "maintenance_work_mem = 512MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "checkpoint_completion_target = 0.9" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_buffers = 16MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "default_statistics_target = 500" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "random_page_cost = 4" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_io_concurrency = 2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "work_mem = 6553kB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "min_wal_size = 4GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "max_wal_size = 16GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

Далее серия вызовов по типу:

echo "autovacuum_analyze_scale_factor = 0.2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

После чего перезапуск базы

sudo systemctl restart postgresql

И проверка что настройки применились

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE category like '%Autovacuum%'"

Инициализация тестовой базы

sudo -u postgres pgbench -i postgres

И собственно запуск самого теста

sudo -u postgres pgbench -c8 -P 60 -T 3600 -U postgres --progress=60 postgres  > hw5-experiment-name.txt 2>&1

Примечания:

• тут я добавляю --progress=60 который будет каждую минуту рапортавот искомые tps
• так же перенаправляем вывод в файл hw5-experiment-name.txt имена которых будут аналогичны виртуалкам

После завершения эксперимента обрабатываем файл следующим скриптом:

DATA=$(cat log.txt | head -n -11 | tail -n +2 | cut -d " " -f 4 | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/')
echo "https://quickchart.io/chart?c={ type: 'bar', data: { labels: ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60'], datasets: [{ label: 'tps', data: [ $DATA ] }] }}"

И статистика:

sudo -u postgres psql -c "select relname, n_live_tup, n_dead_tup, autovacuum_count from pg_stat_all_tables where relname like ('pgbench_%');"

Лог работы

hw5-default

виртуалка: hw5-default

настройки: по умолчанию

лог:

echo "max_connections = 40" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "shared_buffers = 1GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_cache_size = 3GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "maintenance_work_mem = 512MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "checkpoint_completion_target = 0.9" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_buffers = 16MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "default_statistics_target = 500" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "random_page_cost = 4" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_io_concurrency = 2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "work_mem = 6553kB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "min_wal_size = 4GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "max_wal_size = 16GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

sudo systemctl restart postgresql

sudo -u postgres pgbench -i postgres

sudo -u postgres pgbench -c8 -P 60 -T 3600 -U postgres --progress=60 postgres  > hw5-default.txt 2>&1

DATA=$(cat hw5-default.txt | head -n -11 | tail -n +2 | cut -d " " -f 4 | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/')
echo "https://quickchart.io/chart?c={ type: 'bar', data: { labels: ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60'], datasets: [{ label: 'tps', data: [ $DATA ] }] }}"

sudo -u postgres psql -c "select relname, n_live_tup, n_dead_tup, autovacuum_count from pg_stat_all_tables where relname like ('pgbench_%');"

результат:

starting vacuum...end.
progress: 60.0 s, 609.7 tps, lat 13.114 ms stddev 7.870
progress: 120.0 s, 589.1 tps, lat 13.575 ms stddev 7.426
progress: 180.0 s, 603.2 tps, lat 13.258 ms stddev 7.349
progress: 240.0 s, 596.4 tps, lat 13.410 ms stddev 7.273
progress: 300.0 s, 598.6 tps, lat 13.361 ms stddev 7.711
progress: 360.0 s, 597.6 tps, lat 13.382 ms stddev 7.292
progress: 420.0 s, 395.3 tps, lat 20.234 ms stddev 26.017
progress: 480.0 s, 391.4 tps, lat 20.433 ms stddev 26.975
progress: 540.0 s, 388.1 tps, lat 20.606 ms stddev 26.975
progress: 600.0 s, 387.3 tps, lat 20.651 ms stddev 26.712
progress: 660.0 s, 379.0 tps, lat 21.097 ms stddev 27.131
progress: 720.0 s, 380.3 tps, lat 21.034 ms stddev 26.991
progress: 780.0 s, 372.5 tps, lat 21.467 ms stddev 27.111
progress: 840.0 s, 376.8 tps, lat 21.224 ms stddev 27.011
progress: 900.0 s, 372.3 tps, lat 21.483 ms stddev 27.234
progress: 960.0 s, 378.0 tps, lat 21.163 ms stddev 26.643
progress: 1020.0 s, 388.0 tps, lat 20.617 ms stddev 27.184
progress: 1080.0 s, 373.5 tps, lat 21.402 ms stddev 26.914
progress: 1140.0 s, 385.3 tps, lat 20.758 ms stddev 26.802
progress: 1200.0 s, 386.7 tps, lat 20.684 ms stddev 27.348
progress: 1260.0 s, 387.5 tps, lat 20.641 ms stddev 27.095
progress: 1320.0 s, 387.6 tps, lat 20.631 ms stddev 26.934
progress: 1380.0 s, 380.9 tps, lat 20.992 ms stddev 27.218
progress: 1440.0 s, 382.3 tps, lat 20.917 ms stddev 27.035
progress: 1500.0 s, 373.4 tps, lat 21.416 ms stddev 27.128
progress: 1560.0 s, 362.0 tps, lat 22.091 ms stddev 27.632
progress: 1620.0 s, 378.8 tps, lat 21.112 ms stddev 27.336
progress: 1680.0 s, 379.3 tps, lat 21.084 ms stddev 26.847
progress: 1740.0 s, 387.9 tps, lat 20.617 ms stddev 26.873
progress: 1800.0 s, 391.3 tps, lat 20.438 ms stddev 26.890
progress: 1860.0 s, 391.3 tps, lat 20.439 ms stddev 26.951
progress: 1920.0 s, 385.6 tps, lat 20.735 ms stddev 26.957
progress: 1980.0 s, 384.6 tps, lat 20.798 ms stddev 27.063
progress: 2040.0 s, 371.0 tps, lat 21.557 ms stddev 27.463
progress: 2100.0 s, 378.8 tps, lat 21.110 ms stddev 27.086
progress: 2160.0 s, 382.7 tps, lat 20.887 ms stddev 26.958
progress: 2220.0 s, 389.9 tps, lat 20.505 ms stddev 26.727
progress: 2280.0 s, 386.5 tps, lat 20.697 ms stddev 27.386
progress: 2340.0 s, 382.5 tps, lat 20.901 ms stddev 26.919
progress: 2400.0 s, 383.6 tps, lat 20.859 ms stddev 27.055
progress: 2460.0 s, 392.4 tps, lat 20.384 ms stddev 26.952
progress: 2520.0 s, 384.2 tps, lat 20.815 ms stddev 26.848
progress: 2580.0 s, 384.4 tps, lat 20.803 ms stddev 27.133
progress: 2640.0 s, 377.7 tps, lat 21.167 ms stddev 26.832
progress: 2700.0 s, 388.6 tps, lat 20.576 ms stddev 26.757
progress: 2760.0 s, 383.4 tps, lat 20.860 ms stddev 26.576
progress: 2820.0 s, 386.7 tps, lat 20.685 ms stddev 26.529
progress: 2880.0 s, 378.8 tps, lat 21.110 ms stddev 27.077
progress: 2940.0 s, 364.5 tps, lat 21.939 ms stddev 26.829
progress: 3000.0 s, 378.1 tps, lat 21.157 ms stddev 26.865
progress: 3060.0 s, 378.9 tps, lat 21.108 ms stddev 26.663
progress: 3120.0 s, 376.5 tps, lat 21.232 ms stddev 26.530
progress: 3180.0 s, 383.5 tps, lat 20.858 ms stddev 26.986
progress: 3240.0 s, 365.8 tps, lat 21.860 ms stddev 26.933
progress: 3300.0 s, 384.3 tps, lat 20.789 ms stddev 26.623
progress: 3360.0 s, 377.0 tps, lat 21.223 ms stddev 26.736
progress: 3420.0 s, 373.2 tps, lat 21.437 ms stddev 26.649
progress: 3480.0 s, 379.9 tps, lat 21.053 ms stddev 26.678
progress: 3540.0 s, 380.5 tps, lat 21.008 ms stddev 27.010
progress: 3600.0 s, 379.9 tps, lat 21.054 ms stddev 27.190
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 3600 s
number of transactions actually processed: 1451717
latency average = 19.832 ms
latency stddev = 25.178 ms
tps = 403.251812 (including connections establishing)
tps = 403.252096 (excluding connections establishing)

relname	n_live_tup	n_dead_tup	autovacuum_count
pgbench_history	1451522	0	0
pgbench_tellers	10	0	61
pgbench_accounts	100000	8654	0
pgbench_branches	1	0	61

hw5-min

виртуалка: hw5-min

настройки:

key	val
autovacuum_max_workers	1
autovacuum_naptime	1s
autovacuum_vacuum_threshold	1
autovacuum_vacuum_scale_factor	0.01
autovacuum_vacuum_cost_delay	1
autovacuum_vacuum_cost_limit	1

лог:

echo "max_connections = 40" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "shared_buffers = 1GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_cache_size = 3GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "maintenance_work_mem = 512MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "checkpoint_completion_target = 0.9" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_buffers = 16MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "default_statistics_target = 500" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "random_page_cost = 4" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_io_concurrency = 2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "work_mem = 6553kB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "min_wal_size = 4GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "max_wal_size = 16GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

echo "autovacuum_max_workers = 1" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_naptime = 1s" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_threshold = 1" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_scale_factor = 0.01" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_delay = 1" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_limit = 1" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE category like '%Autovacuum%'"

sudo -u postgres pgbench -i postgres

sudo -u postgres pgbench -c8 -P 60 -T 3600 -U postgres --progress=60 postgres  > hw5-min.txt 2>&1

DATA=$(cat hw5-min.txt | head -n -11 | tail -n +2 | cut -d " " -f 4 | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/')
echo "https://quickchart.io/chart?c={ type: 'bar', data: { labels: ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60'], datasets: [{ label: 'tps', data: [ $DATA ] }] }}"

sudo -u postgres psql -c "select relname, n_live_tup, n_dead_tup, autovacuum_count from pg_stat_all_tables where relname like ('pgbench_%');"

результаты:

starting vacuum...end.
progress: 60.0 s, 638.4 tps, lat 12.523 ms stddev 6.236
progress: 120.0 s, 626.8 tps, lat 12.763 ms stddev 7.102
progress: 180.0 s, 591.9 tps, lat 13.514 ms stddev 8.067
progress: 240.0 s, 553.0 tps, lat 14.463 ms stddev 8.734
progress: 300.0 s, 562.3 tps, lat 14.224 ms stddev 8.475
progress: 360.0 s, 594.6 tps, lat 13.452 ms stddev 8.325
progress: 420.0 s, 473.8 tps, lat 16.879 ms stddev 20.057
progress: 480.0 s, 371.6 tps, lat 21.521 ms stddev 28.015
progress: 540.0 s, 380.7 tps, lat 21.012 ms stddev 28.121
progress: 600.0 s, 384.7 tps, lat 20.795 ms stddev 27.282
progress: 660.0 s, 383.3 tps, lat 20.868 ms stddev 27.863
progress: 720.0 s, 375.3 tps, lat 21.308 ms stddev 27.779
progress: 780.0 s, 374.2 tps, lat 21.378 ms stddev 28.161
progress: 840.0 s, 379.6 tps, lat 21.064 ms stddev 27.498
progress: 900.0 s, 383.6 tps, lat 20.856 ms stddev 27.672
progress: 960.0 s, 393.6 tps, lat 20.318 ms stddev 27.360
progress: 1020.0 s, 393.7 tps, lat 20.320 ms stddev 27.577
progress: 1080.0 s, 398.3 tps, lat 20.077 ms stddev 26.898
progress: 1140.0 s, 394.8 tps, lat 20.265 ms stddev 27.156
progress: 1200.0 s, 392.2 tps, lat 20.389 ms stddev 27.654
progress: 1260.0 s, 398.9 tps, lat 20.057 ms stddev 27.244
progress: 1320.0 s, 397.2 tps, lat 20.126 ms stddev 27.560
progress: 1380.0 s, 399.2 tps, lat 20.045 ms stddev 26.945
progress: 1440.0 s, 388.9 tps, lat 20.569 ms stddev 26.993
progress: 1500.0 s, 389.7 tps, lat 20.512 ms stddev 27.035
progress: 1560.0 s, 391.5 tps, lat 20.426 ms stddev 27.457
progress: 1620.0 s, 394.0 tps, lat 20.303 ms stddev 26.954
progress: 1680.0 s, 400.6 tps, lat 19.958 ms stddev 26.724
progress: 1740.0 s, 390.8 tps, lat 20.465 ms stddev 27.567
progress: 1800.0 s, 405.4 tps, lat 19.729 ms stddev 26.773
progress: 1860.0 s, 406.5 tps, lat 19.667 ms stddev 26.767
progress: 1920.0 s, 400.5 tps, lat 19.974 ms stddev 26.964
progress: 1980.0 s, 382.3 tps, lat 20.922 ms stddev 27.872
progress: 2040.0 s, 388.5 tps, lat 20.583 ms stddev 26.789
progress: 2100.0 s, 389.4 tps, lat 20.534 ms stddev 27.065
progress: 2160.0 s, 389.2 tps, lat 20.549 ms stddev 27.176
progress: 2220.0 s, 391.2 tps, lat 20.445 ms stddev 27.689
progress: 2280.0 s, 389.6 tps, lat 20.534 ms stddev 27.218
progress: 2340.0 s, 390.3 tps, lat 20.498 ms stddev 26.872
progress: 2400.0 s, 391.9 tps, lat 20.412 ms stddev 26.967
progress: 2460.0 s, 393.0 tps, lat 20.348 ms stddev 27.060
progress: 2520.0 s, 388.9 tps, lat 20.560 ms stddev 27.200
progress: 2580.0 s, 394.3 tps, lat 20.285 ms stddev 27.217
progress: 2640.0 s, 396.1 tps, lat 20.198 ms stddev 27.008
progress: 2700.0 s, 399.9 tps, lat 20.001 ms stddev 27.029
progress: 2760.0 s, 400.0 tps, lat 19.996 ms stddev 27.100
progress: 2820.0 s, 402.5 tps, lat 19.873 ms stddev 26.916
progress: 2880.0 s, 398.6 tps, lat 20.053 ms stddev 26.890
progress: 2940.0 s, 400.8 tps, lat 19.957 ms stddev 26.939
progress: 3000.0 s, 401.1 tps, lat 19.949 ms stddev 26.486
progress: 3060.0 s, 400.8 tps, lat 19.943 ms stddev 26.506
progress: 3120.0 s, 394.4 tps, lat 20.293 ms stddev 27.475
progress: 3180.0 s, 388.5 tps, lat 20.585 ms stddev 26.946
progress: 3240.0 s, 391.6 tps, lat 20.418 ms stddev 27.314
progress: 3300.0 s, 384.3 tps, lat 20.814 ms stddev 27.210
progress: 3360.0 s, 392.8 tps, lat 20.364 ms stddev 27.275
progress: 3420.0 s, 390.8 tps, lat 20.462 ms stddev 27.314
progress: 3480.0 s, 374.9 tps, lat 21.332 ms stddev 28.264
progress: 3540.0 s, 386.4 tps, lat 20.694 ms stddev 27.057
progress: 3600.0 s, 383.7 tps, lat 20.856 ms stddev 27.010
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 3600 s
number of transactions actually processed: 1487160
latency average = 19.361 ms
latency stddev = 25.375 ms
tps = 413.095893 (including connections establishing)
tps = 413.096186 (excluding connections establishing)

relname	n_live_tup	n_dead_tup	autovacuum_count
pgbench_history	1481380	0	0
pgbench_tellers	10	0	220
pgbench_accounts	100000	0	204
pgbench_branches	1	0	221

hw5-max

виртуалка: hw5-max

настройки:

key	val
autovacuum_max_workers	100
autovacuum_naptime	600s
autovacuum_vacuum_threshold	1000
autovacuum_vacuum_scale_factor	0.99
autovacuum_vacuum_cost_delay	100
autovacuum_vacuum_cost_limit	10000

лог:

echo "max_connections = 40" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "shared_buffers = 1GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_cache_size = 3GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "maintenance_work_mem = 512MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "checkpoint_completion_target = 0.9" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_buffers = 16MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "default_statistics_target = 500" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "random_page_cost = 4" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_io_concurrency = 2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "work_mem = 6553kB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "min_wal_size = 4GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "max_wal_size = 16GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

echo "autovacuum_max_workers = 100" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_naptime = 600s" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_threshold = 1000" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_scale_factor = 0.99" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_delay = 100" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_limit = 10000" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE category like '%Autovacuum%'"

sudo -u postgres pgbench -i postgres

sudo -u postgres pgbench -c8 -P 60 -T 3600 -U postgres --progress=60 postgres  > hw5-max.txt 2>&1

DATA=$(cat hw5-max.txt | head -n -11 | tail -n +2 | cut -d " " -f 4 | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/')
echo "https://quickchart.io/chart?c={ type: 'bar', data: { labels: ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60'], datasets: [{ label: 'tps', data: [ $DATA ] }] }}"

sudo -u postgres psql -c "select relname, n_live_tup, n_dead_tup, autovacuum_count from pg_stat_all_tables where relname like ('pgbench_%');"

результат:

starting vacuum...end.
progress: 60.0 s, 694.4 tps, lat 11.515 ms stddev 6.111
progress: 120.0 s, 682.2 tps, lat 11.726 ms stddev 5.867
progress: 180.0 s, 661.1 tps, lat 12.100 ms stddev 7.206
progress: 240.0 s, 663.3 tps, lat 12.058 ms stddev 7.585
progress: 300.0 s, 666.9 tps, lat 11.993 ms stddev 7.459
progress: 360.0 s, 644.1 tps, lat 12.419 ms stddev 10.279
progress: 420.0 s, 423.3 tps, lat 18.892 ms stddev 26.598
progress: 480.0 s, 426.5 tps, lat 18.754 ms stddev 26.266
progress: 540.0 s, 424.7 tps, lat 18.834 ms stddev 26.557
progress: 600.0 s, 419.4 tps, lat 19.070 ms stddev 26.656
progress: 660.0 s, 413.2 tps, lat 19.358 ms stddev 26.707
progress: 720.0 s, 411.2 tps, lat 19.452 ms stddev 26.825
progress: 780.0 s, 411.3 tps, lat 19.445 ms stddev 26.872
progress: 840.0 s, 416.6 tps, lat 19.197 ms stddev 26.911
progress: 900.0 s, 416.9 tps, lat 19.187 ms stddev 26.607
progress: 960.0 s, 412.5 tps, lat 19.394 ms stddev 26.923
progress: 1020.0 s, 418.4 tps, lat 19.114 ms stddev 26.597
progress: 1080.0 s, 409.3 tps, lat 19.547 ms stddev 26.576
progress: 1140.0 s, 418.2 tps, lat 19.126 ms stddev 26.671
progress: 1200.0 s, 412.9 tps, lat 19.369 ms stddev 27.015
progress: 1260.0 s, 417.9 tps, lat 19.143 ms stddev 26.782
progress: 1320.0 s, 416.5 tps, lat 19.193 ms stddev 26.573
progress: 1380.0 s, 411.7 tps, lat 19.425 ms stddev 26.539
progress: 1440.0 s, 415.2 tps, lat 19.265 ms stddev 26.595
progress: 1500.0 s, 417.5 tps, lat 19.154 ms stddev 26.708
progress: 1560.0 s, 421.5 tps, lat 18.970 ms stddev 26.591
progress: 1620.0 s, 416.0 tps, lat 19.219 ms stddev 26.509
progress: 1680.0 s, 412.5 tps, lat 19.392 ms stddev 26.345
progress: 1740.0 s, 422.8 tps, lat 18.917 ms stddev 26.511
progress: 1800.0 s, 417.9 tps, lat 19.143 ms stddev 26.806
progress: 1860.0 s, 424.0 tps, lat 18.867 ms stddev 26.317
progress: 1920.0 s, 422.2 tps, lat 18.945 ms stddev 26.485
progress: 1980.0 s, 415.3 tps, lat 19.259 ms stddev 26.484
progress: 2040.0 s, 419.8 tps, lat 19.053 ms stddev 26.436
progress: 2100.0 s, 427.6 tps, lat 18.704 ms stddev 26.381
progress: 2160.0 s, 424.1 tps, lat 18.861 ms stddev 26.050
progress: 2220.0 s, 421.3 tps, lat 18.981 ms stddev 26.556
progress: 2280.0 s, 414.2 tps, lat 19.312 ms stddev 26.449
progress: 2340.0 s, 421.2 tps, lat 18.989 ms stddev 26.462
progress: 2400.0 s, 421.5 tps, lat 18.978 ms stddev 26.433
progress: 2460.0 s, 434.0 tps, lat 18.429 ms stddev 26.310
progress: 2520.0 s, 427.0 tps, lat 18.732 ms stddev 26.533
progress: 2580.0 s, 431.9 tps, lat 18.518 ms stddev 26.325
progress: 2640.0 s, 430.6 tps, lat 18.577 ms stddev 26.188
progress: 2700.0 s, 427.8 tps, lat 18.698 ms stddev 26.445
progress: 2760.0 s, 432.3 tps, lat 18.504 ms stddev 26.528
progress: 2820.0 s, 428.7 tps, lat 18.662 ms stddev 26.451
progress: 2880.0 s, 429.9 tps, lat 18.609 ms stddev 26.514
progress: 2940.0 s, 422.5 tps, lat 18.921 ms stddev 26.570
progress: 3000.0 s, 430.5 tps, lat 18.578 ms stddev 26.871
progress: 3060.0 s, 441.0 tps, lat 18.141 ms stddev 26.378
progress: 3120.0 s, 439.0 tps, lat 18.221 ms stddev 26.190
progress: 3180.0 s, 436.0 tps, lat 18.348 ms stddev 26.684
progress: 3240.0 s, 432.2 tps, lat 18.501 ms stddev 26.356
progress: 3300.0 s, 436.2 tps, lat 18.341 ms stddev 26.489
progress: 3360.0 s, 443.9 tps, lat 18.016 ms stddev 25.751
progress: 3420.0 s, 434.4 tps, lat 18.414 ms stddev 26.541
progress: 3480.0 s, 437.0 tps, lat 18.304 ms stddev 26.608
progress: 3540.0 s, 434.0 tps, lat 18.430 ms stddev 26.358
progress: 3600.0 s, 437.0 tps, lat 18.307 ms stddev 26.810
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 3600 s
number of transactions actually processed: 1613598
latency average = 17.845 ms
latency stddev = 24.769 ms
tps = 448.219077 (including connections establishing)
tps = 448.219389 (excluding connections establishing)

relname	n_live_tup	n_dead_tup	autovacuum_count
pgbench_history	1613285	0	0
pgbench_tellers	10	738	0
pgbench_accounts	100000	7398	0
pgbench_branches	1	240	6

hw5-slides

виртуалка: hw5-slides

настройки:

key	val
autovacuum_max_workers	10
autovacuum_naptime	15s
autovacuum_vacuum_threshold	25
autovacuum_vacuum_scale_factor	0.1
autovacuum_vacuum_cost_delay	10
autovacuum_vacuum_cost_limit	1000

лог:

echo "max_connections = 40" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "shared_buffers = 1GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_cache_size = 3GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "maintenance_work_mem = 512MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "checkpoint_completion_target = 0.9" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_buffers = 16MB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "default_statistics_target = 500" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "random_page_cost = 4" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "effective_io_concurrency = 2" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "work_mem = 6553kB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "min_wal_size = 4GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "max_wal_size = 16GB" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

echo "autovacuum_max_workers = 10" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_naptime = 15s" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_threshold = 25" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_scale_factor = 0.1" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_delay = 10" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "autovacuum_vacuum_cost_limit = 1000" | sudo tee -a /etc/postgresql/12/main/postgresql.conf

sudo systemctl restart postgresql

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE category like '%Autovacuum%'"

sudo -u postgres pgbench -i postgres

sudo -u postgres pgbench -c8 -P 60 -T 3600 -U postgres --progress=60 postgres  > hw5-slides.txt 2>&1

DATA=$(cat hw5-slides.txt | head -n -11 | tail -n +2 | cut -d " " -f 4 | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/')
echo "https://quickchart.io/chart?c={ type: 'bar', data: { labels: ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60'], datasets: [{ label: 'tps', data: [ $DATA ] }] }}"

sudo -u postgres psql -c "select relname, n_live_tup, n_dead_tup, autovacuum_count from pg_stat_all_tables where relname like ('pgbench_%');"

результат:

starting vacuum...end.
progress: 60.0 s, 578.6 tps, lat 13.818 ms stddev 7.104
progress: 120.0 s, 585.9 tps, lat 13.653 ms stddev 7.057
progress: 180.0 s, 571.7 tps, lat 13.992 ms stddev 6.938
progress: 240.0 s, 545.1 tps, lat 14.670 ms stddev 6.592
progress: 300.0 s, 565.4 tps, lat 14.144 ms stddev 6.743
progress: 360.0 s, 556.9 tps, lat 14.358 ms stddev 6.841
progress: 420.0 s, 563.1 tps, lat 14.202 ms stddev 6.411
progress: 480.0 s, 560.4 tps, lat 14.270 ms stddev 6.889
progress: 540.0 s, 553.5 tps, lat 14.448 ms stddev 6.769
progress: 600.1 s, 494.3 tps, lat 16.156 ms stddev 15.264
progress: 660.1 s, 372.4 tps, lat 21.471 ms stddev 26.669
progress: 720.1 s, 373.6 tps, lat 21.413 ms stddev 26.201
progress: 780.1 s, 369.3 tps, lat 21.653 ms stddev 26.982
progress: 840.1 s, 368.8 tps, lat 21.667 ms stddev 26.403
progress: 900.0 s, 367.8 tps, lat 21.769 ms stddev 26.813
progress: 960.0 s, 366.8 tps, lat 21.807 ms stddev 26.605
progress: 1020.0 s, 369.8 tps, lat 21.628 ms stddev 26.762
progress: 1080.0 s, 370.6 tps, lat 21.576 ms stddev 26.359
progress: 1140.1 s, 358.7 tps, lat 22.265 ms stddev 27.654
progress: 1200.1 s, 367.5 tps, lat 21.764 ms stddev 26.664
progress: 1260.0 s, 367.8 tps, lat 21.774 ms stddev 26.425
progress: 1320.0 s, 370.1 tps, lat 21.613 ms stddev 26.569
progress: 1380.1 s, 367.8 tps, lat 21.717 ms stddev 26.442
progress: 1440.0 s, 364.9 tps, lat 21.933 ms stddev 26.491
progress: 1500.1 s, 364.8 tps, lat 21.892 ms stddev 26.797
progress: 1560.1 s, 368.3 tps, lat 21.713 ms stddev 26.417
progress: 1620.1 s, 365.8 tps, lat 21.858 ms stddev 26.568
progress: 1680.1 s, 364.8 tps, lat 21.920 ms stddev 26.851
progress: 1740.1 s, 366.7 tps, lat 21.806 ms stddev 26.384
progress: 1800.1 s, 358.9 tps, lat 22.279 ms stddev 26.241
progress: 1860.1 s, 367.4 tps, lat 21.766 ms stddev 26.362
progress: 1920.1 s, 365.9 tps, lat 21.852 ms stddev 26.804
progress: 1980.1 s, 366.9 tps, lat 21.798 ms stddev 26.185
progress: 2040.1 s, 371.6 tps, lat 21.511 ms stddev 26.315
progress: 2100.0 s, 366.0 tps, lat 21.874 ms stddev 26.306
progress: 2160.1 s, 368.1 tps, lat 21.703 ms stddev 26.734
progress: 2220.1 s, 367.3 tps, lat 21.776 ms stddev 26.294
progress: 2280.0 s, 366.9 tps, lat 21.817 ms stddev 26.090
progress: 2340.0 s, 364.0 tps, lat 21.972 ms stddev 26.351
progress: 2400.1 s, 354.8 tps, lat 22.488 ms stddev 26.846
progress: 2460.1 s, 367.5 tps, lat 21.759 ms stddev 26.268
progress: 2520.1 s, 370.4 tps, lat 21.588 ms stddev 26.329
progress: 2580.0 s, 368.5 tps, lat 21.732 ms stddev 26.335
progress: 2640.0 s, 371.6 tps, lat 21.520 ms stddev 26.286
progress: 2700.0 s, 364.3 tps, lat 21.952 ms stddev 26.847
progress: 2760.1 s, 364.4 tps, lat 21.915 ms stddev 26.286
progress: 2820.1 s, 370.0 tps, lat 21.602 ms stddev 26.397
progress: 2880.0 s, 367.4 tps, lat 21.801 ms stddev 26.489
progress: 2940.1 s, 368.2 tps, lat 21.685 ms stddev 26.241
progress: 3000.1 s, 364.9 tps, lat 21.913 ms stddev 26.984
progress: 3060.1 s, 366.9 tps, lat 21.803 ms stddev 26.422
progress: 3120.1 s, 366.8 tps, lat 21.798 ms stddev 26.398
progress: 3180.1 s, 371.3 tps, lat 21.543 ms stddev 26.292
progress: 3240.1 s, 370.9 tps, lat 21.554 ms stddev 26.291
progress: 3300.1 s, 369.5 tps, lat 21.635 ms stddev 26.420
progress: 3360.0 s, 362.6 tps, lat 22.087 ms stddev 27.085
progress: 3420.0 s, 365.9 tps, lat 21.857 ms stddev 26.308
progress: 3480.1 s, 366.0 tps, lat 21.827 ms stddev 26.499
progress: 3540.0 s, 369.9 tps, lat 21.643 ms stddev 26.315
progress: 3600.1 s, 369.6 tps, lat 21.603 ms stddev 26.410
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
duration: 3600 s
number of transactions actually processed: 1436214
latency average = 20.045 ms
latency stddev = 23.745 ms
tps = 398.936992 (including connections establishing)
tps = 398.937277 (excluding connections establishing)

relname	n_live_tup	n_dead_tup	autovacuum_count
pgbench_history	1436012	0	0
pgbench_tellers	10	0	240
pgbench_accounts	100000	9793	0
pgbench_branches	1	0	239

hw6.md

Journals

Виртуалка: hw6

Настройте выполнение контрольной точки раз в 30 секунд.

echo "checkpoint_timeout = 30s" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
sudo systemctl restart postgresql

10 минут c помощью утилиты pgbench подавайте нагрузку.

sudo -u postgres psql -c "select pg_stat_reset()"
sudo -u postgres psql -c "select pg_stat_reset_shared('bgwriter')"

sudo du -h /var/lib/postgresql/12/main/pg_wal # 17mb
sudo -u postgres pgbench -i postgres
sudo -u postgres pgbench -c8 -P 60 -T 600 -U postgres postgres

Измерьте, какой объем журнальных файлов был сгенерирован за это время.

sudo du -h /var/lib/postgresql/12/main/pg_wal # 161mb

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

Отталкиваясь от этого отчета - 12mb

Примечание: для чистоты эксперимента, пришлось сбросить статистику и прогнать эксперимент еще раз

Проверьте данные статистики: все ли контрольные точки выполнялись точно по расписанию. Почему так произошло?

При выполнении нагрузочного тестирования в постгресе ничего не будет происходить в положенном порядке

Судя по статистике среднее время выполнения контролькой точке 120 секунд

Поскольку мы заказали делать точки каждые 25 секунде - они просто напросто накладывались друг на дргуа

При такой нагрузке нет смысла так часто делать контрольные точки

Сравните tps в синхронном/асинхронном режиме утилитой pgbench. Объясните полученный результат.

sudo -u postgres pgbench -P 1 -T 10 postgres # 430
echo "synchronous_commit = off" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
sudo systemctl restart postgresql
sudo -u postgres pgbench -P 1 -T 10 postgres # 1110

Разница почти в 3 раза.

Причина в более эффективном сбрасывании на диск, теперь за вместо того что бы писать на диск на каждый чих, мы делаем это отдельным процессом, по расписанию и пачками.

Создайте новый кластер с включенной контрольной суммой страниц. Создайте таблицу. Вставьте несколько значений. Выключите кластер. Измените пару байт в таблице. Включите кластер и сделайте выборку из таблицы.

sudo pg_createcluster 12 demo -p 5433 -- --data-checksums
sudo pg_ctlcluster 12 demo start
sudo -u postgres psql -p 5433 -c "create table messages(message text)"
sudo -u postgres psql -p 5433 -c "insert into messages (message) values ('hello')"
sudo -u postgres psql -p 5433 -c "insert into messages (message) values ('world')"
sudo -u postgres psql -p 5433 -c "SELECT pg_relation_filepath('messages');" # base/13427/16384
sudo pg_ctlcluster 12 demo stop
sudo dd if=/dev/zero of=/var/lib/postgresql/12/demo/base/13427/16384 oflag=dsync conv=notrunc bs=1 count=8
sudo pg_ctlcluster 12 demo start
sudo -u postgres psql -p 5433 -c "select * from messages"

Что и почему произошло?

data checksums гарантирует целостность на уровне байтиков в файлах и ругается о том что файл был поврежден

WARNING:  page verification failed, calculated checksum 40176 but expected 64197
ERROR:  invalid page in block 0 of relation base/13427/16384

Как проигнорировать ошибку и продолжить работу?

Есть сразу несколько вариантов:

• в самом сеансе можно попросить postgres игнорировать ошибку и выдывать что есть
• можно найти поврежденные строки и удалить их
• можно выставить настройку зануляющую поврежденные строки и выполнить полный вакуум

SET zero_damaged_pages = on;
vacuum full messages;
select * from messages;

Примечание: Во всех сценариях, если конечно мы не полезем в дампы и хэксы файла, поврежденная строка - потеряна

hw7.md

Настройте сервер так, чтобы в журнал сообщений сбрасывалась информация о блокировках, удерживаемых более 200 миллисекунд. Воспроизведите ситуацию, при которой в журнале появятся такие сообщения.

подготовка

sudo -u postgres psql -c "ALTER SYSTEM SET deadlock_timeout TO 200"
sudo -u postgres psql -c "select pg_reload_conf()"
sudo -u postgres psql -c "show deadlock_timeout"
sudo -u postgres psql -c "create table messages(id int primary key,message text)"
sudo -u postgres psql -c "insert into messages values (1, 'hello')"
sudo -u postgres psql -c "insert into messages values (2, 'world')"

session 1

sudo -u postgres psql << EOF
BEGIN;
SELECT message FROM messages WHERE id = 1 FOR UPDATE;
SELECT pg_sleep(10);
UPDATE messages SET message = 'message from session 1' WHERE id = 2;
COMMIT;
EOF

session 2

sudo -u postgres psql << EOF
BEGIN;
SELECT message FROM messages WHERE id = 2 FOR UPDATE;
UPDATE messages SET message = 'message from session 2' WHERE id = 1;
COMMIT;
EOF

результат

sudo -u postgres psql -c "select * from messages"

id	message
2	world
1	message from session 2

Первая сессия оборвалась с ошибкой

ERROR:  deadlock detected
DETAIL:  Process 5777 waits for ShareLock on transaction 516; blocked by process 5787.
Process 5787 waits for ShareLock on transaction 515; blocked by process 5777.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "messages"
ROLLBACK

Вторая при этом успешно завершилась

В логе при этом

cat /var/log/postgresql/postgresql-12-main.log | grep "deadlock detected" -A 10

2021-05-30 07:12:01.541 UTC [5777] postgres@postgres ERROR:  deadlock detected
2021-05-30 07:12:01.541 UTC [5777] postgres@postgres DETAIL:  Process 5777 waits for ShareLock on transaction 516; blocked by process 5787.
        Process 5787 waits for ShareLock on transaction 515; blocked by process 5777.
        Process 5777: UPDATE messages SET message = 'message from session 1' WHERE id = 2;
        Process 5787: UPDATE messages SET message = 'message from session 2' WHERE id = 1;
2021-05-30 07:12:01.541 UTC [5777] postgres@postgres HINT:  See server log for query details.
2021-05-30 07:12:01.541 UTC [5777] postgres@postgres CONTEXT:  while updating tuple (0,2) in relation "messages"
2021-05-30 07:12:01.541 UTC [5777] postgres@postgres STATEMENT:  UPDATE messages SET message = 'message from session 1' WHERE id = 2;

Смоделируйте ситуацию обновления одной и той же строки тремя командами UPDATE в разных сеансах. Изучите возникшие блокировки в представлении pg_locks и убедитесь, что все они понятны. Пришлите список блокировок и объясните, что значит каждая.

подготовка

sudo -u postgres psql -c "drop table messages"
sudo -u postgres psql -c "create table messages(id int primary key,message text)"
sudo -u postgres psql -c "insert into messages values (1, 'hello')"

эксперимент

в каждом терминале:

BEGIN;
SELECT pg_backend_pid() as pid, txid_current() as tid;
UPDATE messages SET message = 'message from session 1' WHERE id = 1;

примечание: каждая сессия вставляет свое сообщение

сеанс	pid	tid
1	6261	530
2	6274	531
3	6284	532

блокировки

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

blocked_pid	blocked_user	blocking_pid	blocking_user	blocked_statement	current_statement_in_blocking_process	blocked_application	blocking_application
6274	postgres	6261	postgres	UPDATE messages SET message = 'message from session 2' WHERE id = 1;	UPDATE messages SET message = 'message from session 1' WHERE id = 1;	psql	psql
6284	postgres	6274	postgres	UPDATE messages SET message = 'message from session 3' WHERE id = 1;	UPDATE messages SET message = 'message from session 2' WHERE id = 1;	psql	psql

6261 блокирует 6274, а та в свою очередь - 6284

SELECT 
row_number() over(ORDER BY pid, virtualxid, transactionid::text::bigint) as n,
CASE
WHEN locktype = 'relation' THEN 'отношение'
WHEN locktype = 'extend' THEN 'расширение отношения'
WHEN locktype = 'frozenid' THEN 'замороженный идентификатор'
WHEN locktype = 'page' THEN 'страница'
WHEN locktype = 'tuple' THEN 'кортеж'
WHEN locktype = 'transactionid' THEN 'идентификатор транзакции'
WHEN locktype = 'virtualxid' THEN 'виртуальный идентификатор'
WHEN locktype = 'object' THEN 'объект'
WHEN locktype = 'userlock' THEN 'пользовательская блокировка'
WHEN locktype = 'advisory' THEN 'рекомендательная'
END AS locktype,

relation::regclass,
-- CASE WHEN relation IS NULL THEN 'цель блокировки — не отношение или часть отношения' ELSE CAST(relation::regclass AS TEXT) END AS relation,

CASE WHEN page IS NOT NULL AND tuple IS NOT NULL THEN (select message from messages m where m.ctid::text = '(' || page || ',' || tuple || ')' limit 1) ELSE NULL END AS row, -- page, tuple, 

virtualxid, transactionid, virtualtransaction, 

pid, 
CASE WHEN pid = 6261 THEN 'session1' WHEN pid = 6274 THEN 'session2' WHEN pid = 6284 THEN 'session3' END AS session,

mode, 

CASE WHEN granted = true THEN 'блокировка получена' ELSE 'блокировка ожидается' END AS granted,
CASE WHEN fastpath = true THEN 'блокировка получена по короткому пути' ELSE 'блокировка получена через основную таблицу блокировок' END AS fastpath 
FROM pg_locks WHERE pid in (6261, 6274,6284) 
ORDER BY pid, virtualxid, transactionid::text::bigint;

n	locktype	relation	row	virtualxid	transactionid	virtualtransaction	pid	session	mode	granted	fastpath
1	виртуальный идентификатор			4/202		4/202	6261	session1	ExclusiveLock	блокировка получена	блокировка получена по короткому пути
2	идентификатор транзакции				530	4/202	6261	session1	ExclusiveLock	блокировка получена	блокировка получена через основную таблицу блокировок
3	отношение	messages_pkey				4/202	6261	session1	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути
4	отношение	messages				4/202	6261	session1	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути
5	виртуальный идентификатор			5/22		5/22	6274	session2	ExclusiveLock	блокировка получена	блокировка получена по короткому пути
6	идентификатор транзакции				530	5/22	6274	session2	ShareLock	блокировка ожидается	блокировка получена через основную таблицу блокировок
7	идентификатор транзакции				531	5/22	6274	session2	ExclusiveLock	блокировка получена	блокировка получена через основную таблицу блокировок
8	кортеж	messages	hello			5/22	6274	session2	ExclusiveLock	блокировка получена	блокировка получена через основную таблицу блокировок
9	отношение	messages_pkey				5/22	6274	session2	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути
10	отношение	messages				5/22	6274	session2	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути
11	виртуальный идентификатор			6/6		6/6	6284	session3	ExclusiveLock	блокировка получена	блокировка получена по короткому пути
12	идентификатор транзакции				532	6/6	6284	session3	ExclusiveLock	блокировка получена	блокировка получена через основную таблицу блокировок
13	кортеж	messages	hello			6/6	6284	session3	ExclusiveLock	блокировка ожидается	блокировка получена через основную таблицу блокировок
14	отношение	messages				6/6	6284	session3	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути
15	отношение	messages_pkey				6/6	6284	session3	RowExclusiveLock	блокировка получена	блокировка получена по короткому пути

Примечание:

• каждый сеанс держит эксклюзивные (exclusive lock) блокировки на номера своих транзакций (transactionid - 2, 7, 12 строки) и виртуальной транзакции (virtualxid - 1, 5, 11 - строки)
• первый сеанс захватил эксклюзивную блокировку строки для ключа и самой строки, строки 3, 4
• оставшиеся два запроса хоть и ожидают блокировки так же повесили row exclusive lock на ключ и строку, строки - 9, 10 и 14, 15
• так же оставшиеся два сеанса повесили экслоюзивную блокировку на сам кортеж, т.к. хотят обновить именно его, а он уже обновлен в первом сеансе, строки 8 и 13
• оставшаяся блокировка share lock в 6 строке вызванна тем что мы пытаемся обновить ту же строку что и в первом сеансе у которого уже захвачен row exclusive lock

Воспроизведите взаимоблокировку трех транзакций. Можно ли разобраться в ситуации постфактум, изучая журнал сообщений?

подготовка

sudo -u postgres psql -c "drop table messages"
sudo -u postgres psql -c "create table messages(id int primary key,message text)"
sudo -u postgres psql -c "insert into messages values (1, 'one')"
sudo -u postgres psql -c "insert into messages values (2, 'two')"
sudo -u postgres psql -c "insert into messages values (3, 'three')"

эксперимент

step	session 1 (pid: 6946, tid: 538)	session 2 (pid: 6956, tid: 539)	session 3 (pid: 6966, tid: 540)
1	begin;	begin;	begin;
2	SELECT pg_backend_pid() as pid, txid_current() as tid;	SELECT pg_backend_pid() as pid, txid_current() as tid;	SELECT pg_backend_pid() as pid, txid_current() as tid;
3	SELECT message FROM messages WHERE id = 1 FOR UPDATE;
4		SELECT message FROM messages WHERE id = 2 FOR UPDATE;
5			SELECT message FROM messages WHERE id = 3 FOR UPDATE;
6	UPDATE messages SET message = 'message from session 1' WHERE id = 2;
7		UPDATE messages SET message = 'message from session 2' WHERE id = 3;
8			UPDATE messages SET message = 'message from session 3' WHERE id = 1;

результаты

• первый сеанс висит на апдейте
• второй сеанс обновил строку
• третий сеанс вылетел с ошибкой

ERROR:  deadlock detected
DETAIL:  Process 6966 waits for ShareLock on transaction 538; blocked by process 6946.
Process 6946 waits for ShareLock on transaction 539; blocked by process 6956.
Process 6956 waits for ShareLock on transaction 540; blocked by process 6966.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "messages"

лог

2021-05-30 08:56:27.777 UTC [6966] postgres@postgres ERROR:  deadlock detected
2021-05-30 08:56:27.777 UTC [6966] postgres@postgres DETAIL:  
        Process 6966 waits for ShareLock on transaction 538; blocked by process 6946.
        Process 6946 waits for ShareLock on transaction 539; blocked by process 6956.
        Process 6956 waits for ShareLock on transaction 540; blocked by process 6966.
        Process 6966: UPDATE messages SET message = 'message from session 3' WHERE id = 1;
        Process 6946: UPDATE messages SET message = 'message from session 1' WHERE id = 2;
        Process 6956: UPDATE messages SET message = 'message from session 2' WHERE id = 3;
2021-05-30 08:56:27.777 UTC [6966] postgres@postgres HINT:  See server log for query details.
2021-05-30 08:56:27.777 UTC [6966] postgres@postgres CONTEXT:  while updating tuple (0,1) in relation "messages"
2021-05-30 08:56:27.777 UTC [6966] postgres@postgres STATEMENT:  UPDATE messages SET message = 'message from session 3' WHERE id = 1;

В логе мы видим что процесс № 6966 (третий сеанс) споймал deadlock

Детали нам говорят о том что:

• третий сенас ждал первого и второго, сеанс два при этом ждал третьего (кольцо)
• далее приведены запросы, но из-за того что блокировку мы вызвали ранее по ним можно только сказать что мы пытались обновить, но не причину блокировки

Могут ли две транзакции, выполняющие единственную команду UPDATE одной и той же таблицы (без where), заблокировать друг друга?

подготовка

sudo -u postgres psql -c "drop table test"
sudo -u postgres psql -c "create table test(id integer primary key generated always as identity, n float)"
sudo -u postgres psql -c "insert into test(n) select random() from generate_series(1,1000000)"

session 1

sudo -u postgres psql << EOF
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE test SET n = (select id from test order by id asc limit 1 for update);
COMMIT;
EOF

session 2

sudo -u postgres psql << EOF
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE test SET n = (select id from test order by id desc limit 1 for update);
COMMIT;
EOF

В моем случае первый сеанс отвалился с ошибкой:

ERROR:  deadlock detected
DETAIL:  Process 8056 waits for ShareLock on transaction 608; blocked by process 8066.
Process 8066 waits for ShareLock on transaction 607; blocked by process 8056.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (15554,62) in relation "test"
ROLLBACK

Примечание: мы забираем id for update в первом сеансе отсортированные во возрастанию, а во втором по убыванию из-за чего по началу вроде как все ок, но когда два запроса "пересекаются" начинаются проблемы

hw8.md

Postgres tuning

Получилось расскачать tps с 579 до 1405.

Learning points:

• work_mem - меньше лучше
• трогать систему конретно в этом стенде не стоит
• самое не ожиданное решение - удаление не используемых индексов.

Примечание: tpcc рекомендуемый в описании к домашней работе не рабочий, детали в переписке по концовке решили делать нативным sysbench

Preparation

# pg13
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
sudo systemctl start postgresql

# sysbench
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

# prep
sudo -u postgres psql -c "CREATE ROLE sbtest LOGIN SUPERUSER PASSWORD 'sbtest'"
sudo -u postgres psql -c "CREATE DATABASE sbtest"
sudo -u postgres sysbench \
--db-driver=pgsql \
--oltp-table-size=1000000 \
--oltp-tables-count=100 \
--threads=1 \
--pgsql-host=localhost \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run

# check
sudo -u postgres psql sbtest -c "select count(*) as tables, sum(n_live_tup) as rows, pg_size_pretty(pg_database_size('sbtest')) as size from pg_stat_user_tables"

tables	rows	size
100	100000000	24 GB

Experiments plan

Для каждого типа настроек прогоняем следующий эксперимент

sysbench \
--db-driver=pgsql \
--report-interval=5 \
--oltp-table-size=1000000 \
--oltp-tables-count=100 \
--threads=64 \
--time=600 \
--pgsql-host=localhost \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
run 2>&1 | tee web.txt

Перед запуском база перезапускается, т.к. меняются настройки, что безусловно влияет на результат, но т.к. это относиться ко всем экспериментам то и результат будет у всех искажен одинаково

Defaults - 579 tps

result

SQL statistics:
    queries performed:
        read:                            4865126
        write:                           1389986
        other:                           695048
        total:                           6950160
    transactions:                        347499 (579.02 per sec.)
    queries:                             6950160 (11580.68 per sec.)
    ignored errors:                      10     (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.1494s
total number of events:              347499
Latency (ms):
min:                                    7.05
avg:                                  110.52
max:                                 1216.23
95th percentile:                      240.02
sum:                             38404223.34
Threads fairness:
events (avg/stddev):           5429.6719/25.35
execution time (avg/stddev):   600.0660/0.05

pgtune.leopard.in.ua: Web application - 1059 tps

config

# DB Version: 13
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 8
# Connections num: 64
# Data Storage: ssd
max_connections = 64
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 32MB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

result

SQL statistics:
    queries performed:
        read:                            8897616
        write:                           2542122
        other:                           1271124
        total:                           12710862
    transactions:                        635535 (1059.03 per sec.)
    queries:                             12710862 (21180.83 per sec.)
    ignored errors:                      9      (0.01 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.1097s
total number of events:              635535
Latency (ms):
min:                                    5.77
avg:                                   60.42
max:                                  401.58
95th percentile:                      121.08
sum:                             38400941.40
Threads fairness:
events (avg/stddev):           9930.2344/46.32
execution time (avg/stddev):   600.0147/0.03

Примечания:

• результат - х2 от настроек по умолчанию
• очень забавно, т.к. в слепую скопировал max connections и в тесте мы запускаем 64 потока, то в процессе эксперимента не смог подключиться что бы посмотреть что внутри базы происходит

pgtune.leopard.in.ua: OLTP - 1270 tps

config

# DB Version: 13
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 32 GB
# CPUs num: 8
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 20971kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

result

SQL statistics:
    queries performed:
        read:                            10673712
        write:                           3049599
        other:                           1524827
        total:                           15248138
    transactions:                        762397 (1270.43 per sec.)
    queries:                             15248138 (25408.94 per sec.)
    ignored errors:                      11     (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.1071s
total number of events:              762397
Latency (ms):
min:                                    5.50
avg:                                   50.37
max:                                  428.74
95th percentile:                       80.03
sum:                             38400023.08
Threads fairness:
events (avg/stddev):           11912.4531/50.89
execution time (avg/stddev):   600.0004/0.03

Примечания:

• в процессе прогона теста видны просадки в tps но они не такие большие как в случае с web application, причина в том что мы увеличили размеры для wal size а как следствие реже и большим батчем работаем с ним
• результат еще лучше чем в предыдущий 1059 vs 1270 tps
• а вот work mem я себе думаю вернуть назад к 32мб
• все последующие эксперименты будут от этого конфига плюс новые параметры

pgconfig.org: Web application - 751 tps

config

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 328MB
maintenance_work_mem = 2GB
min_wal_size = 512MB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
max_connections = 100
random_page_cost = 1.1
effective_io_concurrency = 200
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

result

SQL statistics:
    queries performed:
        read:                            6314112
        write:                           1803988
        other:                           902040
        total:                           9020140
    transactions:                        450998 (751.55 per sec.)
    queries:                             9020140 (15031.28 per sec.)
    ignored errors:                      10     (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.0895s
total number of events:              450998
Latency (ms):
min:                                    7.05
avg:                                   85.15
max:                                  487.11
95th percentile:                      139.85
sum:                             38401788.42
Threads fairness:
events (avg/stddev):           7046.8438/21.24
execution time (avg/stddev):   600.0279/0.02

Примечания:

• Тут более консервативные рекомментдации и уже в процессе видно что результат будет сильно хуже
• Пока что выглядит так что из всех расчитываемых по формулам параметров наибольшую риль играют wal size
• Нужен еще один эксперимент с work mem большим что бы окончательно подтвердить или опровергнуть

big work_mem - 1169 tps

config

max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 256MB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

result

SQL statistics:
    queries performed:
        read:                            9828700
        write:                           2808179
        other:                           1404117
        total:                           14040996
    transactions:                        702048 (1169.79 per sec.)
    queries:                             14040996 (23395.88 per sec.)
    ignored errors:                      2      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.1463s
total number of events:              702048
Latency (ms):
min:                                    6.12
avg:                                   54.70
max:                                  334.50
95th percentile:                       92.42
sum:                             38399754.62
Threads fairness:
events (avg/stddev):           10969.5000/45.31
execution time (avg/stddev):   599.9962/0.04

Примечания:

• у меня стабильно гигабайт пять памяти свободных есть, совсем не проблема для такой нагрузки увеличить work mem
• в процессе вижу что результат будет чуть хуже
• work mem делать большим конретно на этом стенде смысла нет
• в последующих экспериментах использую конифиг с наилучшими показателями

Async - 1346 pts

По скольку, по условиям, домашки мы можем себе это позволить:

config

max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 32MB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
fsync = off
synchronous_commit = off
checkpoint_timeout = 1d
wal_init_zero = off

result

SQL statistics:
    queries performed:
        read:                            11313582
        write:                           3232445
        other:                           1616233
        total:                           16162260
    transactions:                        808113 (1346.67 per sec.)
    queries:                             16162260 (26933.46 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.0792s
total number of events:              808113
Latency (ms):
min:                                    3.37
avg:                                   47.52
max:                                  386.12
95th percentile:                       64.47
sum:                             38399816.88
Threads fairness:
events (avg/stddev):           12626.7656/108.28
execution time (avg/stddev):   599.9971/0.02

Примечания:

• очень странно, у меня выбило сервер (preemtible), прогон по началу показывал заниженные цифры хотя 100% должно быть лучше, только после пары минут начал выдавать 1.3K tps, пришлось перезапустить
• выключение синхронных комитов конретно в этом сетапе дает очень не значительный выигрыш, вот если бы мы это сделали на этапе инициализации эксперимента там было бы значимое ускорение

System - 1315

Согласно рекомендациям postgresqltuner.pl и паре статей, тюним саму систему:

echo "
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.overcommit_memory=2
net.ipv4.tcp_timestamps=0
vm.nr_hugepages=4500
" | sudo tee /etc/sysctl.d/40-postgres.conf
sudo sysctl --system

result

SQL statistics:
    queries performed:
        read:                            11055576
        write:                           3158722
        other:                           1579376
        total:                           15793674
    transactions:                        789681 (1315.91 per sec.)
    queries:                             15793674 (26318.33 per sec.)
    ignored errors:                      3      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.0995s
total number of events:              789681
Latency (ms):
min:                                    3.81
avg:                                   48.63
max:                                  353.44
95th percentile:                       71.83
sum:                             38400182.81
Threads fairness:
events (avg/stddev):           12338.7656/147.22
execution time (avg/stddev):   600.0029/0.02

Примечания:

• в целом стало даже хуже, конретно для этого стенда эти настройки не дают значимого эффекта, хоть и имеют смысл в целом
• лишний раз доказывает о том что в настройки особенно системы стоит лезть только если понимаешь что делаешь

Indexes - 1405

postgresqltuner.pl указал на одну очень важную штуку, а именно то что созданные индексы не используются

первым делом грохнул индексы

drop index k_1;
...
drop index k_100;

посмотрел на сами запросы, покрутил разными тулами не похоже что можно добавить какой то индекс, но по идее удаление не используемых индексов должно дать прирост

откатил настройки системы (т.к. там все равно хуже результат) перезапустил систему и прогнал тест еще раз, пришлось перезапускать, совсем холодный старт, очень медленно работало

result

SQL statistics:
    queries performed:
        read:                            11808314
        write:                           3373790
        other:                           1686914
        total:                           16869018
    transactions:                        843450 (1405.53 per sec.)
    queries:                             16869018 (28110.59 per sec.)
    ignored errors:                      1      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          600.0928s
total number of events:              843450
Latency (ms):
min:                                    5.56
avg:                                   45.53
max:                                  336.44
95th percentile:                       59.99
sum:                             38399400.97
Threads fairness:
events (avg/stddev):           13178.9062/132.15
execution time (avg/stddev):   599.9906/0.02

Примечания:

• Верно пишут в статьях мол не забывайте смотреть на сами запросы, индексы и вот это все, т.к. это может дать значимы прирост, в нашем случае удаление не нужных индексов дало еще 60 tps

hw9.md

Виртуалки: hw9a, hw9b, hw9c, hw9d

Prepartions

На всем машинах

echo "listen_addresses = '*'" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "wal_level = 'logical'" | sudo tee -a /etc/postgresql/12/main/postgresql.conf
echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/12/main/pg_hba.conf
echo "host replication all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/12/main/pg_hba.conf

sudo systemctl restart postgresql

sudo -u postgres psql -c "CREATE USER demo SUPERUSER encrypted PASSWORD '123'"

На 1 ВМ создаем таблицы test для записи, test2 для запросов на чтение.

hw9a, hw9b, hw9c

sudo -u postgres psql -c "CREATE DATABASE demo"
sudo -u postgres psql demo -c "CREATE TABLE test1 (m text)"
sudo -u postgres psql demo -c "CREATE TABLE test2 (m text)"

Создаем публикацию таблицы test и подписываемся на публикацию таблицы test2 с ВМ №2.

hw9a

sudo -u postgres psql demo -c "CREATE PUBLICATION test1_pub FOR TABLE test1"

hw9b

sudo -u postgres psql demo -c "CREATE SUBSCRIPTION test_sub CONNECTION 'host=hw9a port=5432 user=demo password=123 dbname=demo' PUBLICATION test1_pub WITH (copy_data = true)"

На 2 ВМ создаем таблицы test2 для записи, test для запросов на чтение.

Создаем публикацию таблицы test2 и подписываемся на публикацию таблицы test1 с ВМ №1.

hw9b

sudo -u postgres psql demo -c "CREATE PUBLICATION test2_pub FOR TABLE test2"

hw9a

sudo -u postgres psql demo -c "CREATE SUBSCRIPTION test2_sub CONNECTION 'host=hw9b port=5432 user=demo password=123 dbname=demo' PUBLICATION test2_pub WITH (copy_data = true)"

Check

hw9a

sudo -u postgres psql demo -c "INSERT INTO test1 VALUES ('hello')"

hw9b

sudo -u postgres psql demo -c "INSERT INTO test2 VALUES ('world')"

hw9a

sudo -u postgres psql demo -c "select * from test2"

hw9b

sudo -u postgres psql demo -c "select * from test1"

3 ВМ использовать как реплику для чтения и бэкапов (подписаться на таблицы из ВМ №1 и №2 ).

hw9c

sudo -u postgres psql demo -c "CREATE SUBSCRIPTION test_sub_for_hw9c CONNECTION 'host=hw9a port=5432 user=demo password=123 dbname=demo' PUBLICATION test1_pub WITH (copy_data = true)"

sudo -u postgres psql demo -c "CREATE SUBSCRIPTION test2_sub_for_hw9c CONNECTION 'host=hw9b port=5432 user=demo password=123 dbname=demo' PUBLICATION test2_pub WITH (copy_data = true)"

Небольшое описание, того, что получилось.

Подписки крест на крест

hw9a.test1 -> hw9b.test1 hw9b.test2 -> hw9a.test2

Реплика

hw9a.test1 -> hw9c.test1 hw9a.test2 -> hw9c.test2

Примечание: на каждую подписку необходим свой слот репликации, необходимо убедиться что их достаточное кол-во max_wal_senders

Реализовать горячее реплицирование для высокой доступности на 4ВМ. Источником должна выступать ВМ №3. Написать с какими проблемами столкнулись.

hw9d

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/12/main/*
sudo -u postgres pg_basebackup --host=hw9c --port=5432 --username=demo --pgdata=/var/lib/postgresql/12/main/ --progress --write-recovery-conf --create-slot --slot=replica1
sudo systemctl start postgresql

hw9a

sudo -u postgres psql demo -c "INSERT INTO test1 VALUES ('crazyness')"

hw9d

sudo -u postgres psql demo -c "select * from test2"

pgbench.md

setup

docker run -it --rm ubuntu bash

ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
apt update && apt install -y postgresql postgresql-contrib iputils-ping sudo vim netcat
pg_ctlcluster 12 main start

init

sudo -u postgres psql -c "create database sample"
sudo -u postgres pgbench -i -s 1 sample
sudo -u postgres psql sample -c "select * from pgbench_accounts limit 100"
sudo -u postgres psql sample -c "select * from pgbench_branches limit 100"
sudo -u postgres psql sample -c "select * from pgbench_history limit 100"
sudo -u postgres psql sample -c "select * from pgbench_tellers limit 100"

sudo -u postgres psql sample -c "select count(*) from pgbench_accounts"
sudo -u postgres psql sample -c "select count(*) from pgbench_branches"
sudo -u postgres psql sample -c "select count(*) from pgbench_history"
sudo -u postgres psql sample -c "select count(*) from pgbench_tellers"

Performance

basic

sudo -u postgres pgbench -c 10 -j 2 -t 10000 sample

read only

sudo -u postgres pgbench -c 10 -j 2 -t 10000 -b select-only sample

read 80% write 20%

sudo -u postgres pgbench -c 10 -j 2 -t 10000 -P 5 -r -b tpcb-like@20 -b select-only@80 sample

Emulate single user

basic

sudo -u postgres pgbench -c 1 -j 1 -T 60 -R 1 -r sample

read only (1 user, 1 query per second)

sudo -u postgres pgbench -c 1 -j 1 -T 60 -R 1 -r -b select-only sample

read 80% write 20% (1 user)

sudo -u postgres pgbench -c 1 -j 1 -T 60 -R 1 -P 5 -r -b tpcb-like@20 -b select-only@80 sample

replication.md

start

docker run -it --rm -p 5551:5432 --name=db1 --hostname=db1 ubuntu bash
docker run -it --rm -p 5552:5432 --name=db2 --hostname=db2 ubuntu bash

on both containers

ln -snf /usr/share/zoneinfo/UTC /etc/localtime && echo UTC > /etc/timezone
apt update
apt install -y postgresql postgresql-contrib iputils-ping sudo vim netcat
echo "listen_addresses = '*'" >> /etc/postgresql/12/main/postgresql.conf
echo "host replication replica 0.0.0.0/0 md5" >> /etc/postgresql/12/main/pg_hba.conf

on host machine

docker inspect db1 | grep IPAddress # db1 - 172.17.0.2
docker inspect db2 | grep IPAddress # db2 - 172.17.0.3

on first container

check connectivity by sending pings

ping 172.17.0.3

start postgres

pg_ctlcluster 12 main start

and make sure it is up and running

pg_lsclusters

create replica user with password 123 and replication role

sudo -u postgres psql -c "create user replica with replication encrypted password '123'"

create sample database and fill it with approx 500mb of data

sudo -u postgres psql -c "create database sample"
sudo -u postgres pgbench -i -s 50 sample

on a second container

cleanup data directory

sudo -u postgres rm -rf /var/lib/postgresql/12/main/*

ensure first container is listening on expected port

nc -vz 172.17.0.2 5432

restore cluster from master (it will ask for 123 password of replica user, also note that it can take some time to backup restore 500mb)

sudo -u postgres pg_basebackup --host=172.17.0.2 --port=5432 --username=replica --pgdata=/var/lib/postgresql/12/main/ --progress --write-recovery-conf --create-slot --slot=replica1

notes:

• it will ask for 123 password of replica user created earlier
• it might take some time to backup restore 500mb of data
• it will wait for a checkpoint before starting, so run sudo -u postgres psql -c "checkpoint" on a master (somehow it did not happened in my case after few minutes)

make sure that connection info is saved

cat /var/lib/postgresql/12/main/postgresql.auto.conf

and that you have standby.signal file in place

ls -la /var/lib/postgresql/12/main/ | grep standby

start postgres

pg_ctlcluster 12 main start

and make sure it is up and running

pg_lsclusters

you should see online,recovery

on a first container

check replication

sudo -u postgres psql -c "select * from pg_replication_slots"
sudo -u postgres psql -c "select * from pg_stat_replication"

lets create table and fill it with some dummy data

sudo -u postgres psql sample -c "create table messages(message text)"
sudo -u postgres psql sample -c "insert into messages values('hello')"
sudo -u postgres psql sample -c "select * from messages"

almost immediatelly you should see that table and message on a replica

failover - on a first container

stop postgres

pg_ctlcluster 12 main stop

failover - on a second container

check logs

tail -n 100 /var/log/postgresql/postgresql-12-main.log

promote second container as a new master

sudo pg_ctlcluster 12 main promote

standby file should be removed automatically

ls -la /var/lib/postgresql/12/main/ | grep standby

Connection info in postgres.auto.conf will left inact, but it is ok, until there is no standby file

you should be able to write records now

sudo -u postgres psql sample -c "insert into messages values('world')"
sudo -u postgres psql sample -c "select * from messages"

before returning master back we need to create replication slot

sudo -u postgres psql -c "select * from pg_create_physical_replication_slot('replica2')"
sudo -u postgres psql -c "select * from pg_replication_slots"

note that even we have copied whole cluster there were no replica1 slot, they are per instance, replica2 name jusen just for demo purposes

failover - o a first container

assumming there were no writes on master we are just telling postgres to become standby

touch /var/lib/postgresql/12/main/standby.signal

start postgres

pg_ctlcluster 12 main start

and make sure it is up and running

pg_lsclusters

you should see online,recovery

now we gonna need to tell postgress from where to replicate

sudo -u postgres psql sample -c "ALTER SYSTEM SET primary_conninfo TO 'user=replica password=123 host=172.17.0.3 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'"
sudo -u postgres psql sample -c "ALTER SYSTEM SET primary_slot_name TO 'replica2'"

notes:

• do not forget to pass correct ip address of a second container
• do not forget to pass correct slot name

check that changes saved

cat /var/lib/postgresql/12/main/postgresql.auto.conf

somehow in my case sync did not started and I just rebooted postgres

pg_ctlcluster 12 main stop
pg_ctlcluster 12 main start

check that data is synced

sudo -u postgres psql sample -c "select * from messages"

important: we need to cleanup old replica1 replication slot

sudo -u postgres psql -c "select * from pg_replication_slots"
sudo -u postgres psql -c "select * from pg_drop_replication_slot('replica1')"