I propose to get acquainted with the decoding of the report of the beginning of 2019 by Andrey Salnikov, "Managing the load on PostgreSQL, when one server is already not enough"
Key points:
1) PostgreSQL standard load balancing practices. First, we discuss the reasons for the high load on the database. The next step is to consider those load balancing methods.
2) Questions will be discussed about how replication is organized in PostgreSQL, what are the differences between synchronous and asynchronous replicas, and how to configure replicas correctly.
, . – Data Egret. . . HeadHunter, Lamoda. DBA PostgreSQL. DBA – . . , .
, - , .
Postgres open source’ community, . , Postgres.
, RDBMS . – enterprise: Oracle MSSQL, open source: MySQL Posgres, .
- . . . – Postgres .
– .
, - . , , , . . . Postgres, , , . , , .
. ? , . , -, .
, .
, , . . , , , , , , .
. , ? , raid 1 raid 0. . -. , , . , . , .
, , – 20 000-30 000. 40 000, , - . .
, . 50 000 . .
, , - - . . – , . . , . , . . . , , - , , , . .
. . , , . . , . .
, . – «exclusive», «share».
20 000 ( ), , - .
: HH Lamoda. , ? 10-20-50 , .
. , .
, , . , , . - . , , , , , , . .
, , Postgres , . . Postgres Posix, , , . – . , , : «, » sleep. , . , . , .
. .
timestamps.
, 30 . . . . RDBMS , . , - . , . , . .
-. , . .
, – idle-. Idle- , - pool connection. , , . , , , , , , , . overhead, , -, .
– . – , .
– , . - , : , . .
– - , . , , , .
? . .
, . ? -, . -, - Postgres ( MVCC), . update/delete, , , , .
idle in transaction , , Postgres . , . . , . , . , . - ( ), 10 , 300 , 400 . . Postgres, .
– waiting. , - . , , . , , .
, . . : - , . , Ruby, Python. , . , , - , . idle in transaction.
instances , . . - . . . , .
-. , pool connection . pools, . Hibernate pools. , pool. , pool .
, . , , idle-. . . , . , , , . , . , . . .
, , .
, , , . , .
.
, . . , , – . , - .
, , , . . , .
, 2,5 . 2 – transaction log. . , . , . . . 1,5 . : « . ». 0,5 . 2,5 0,5 . , .
. , . , , . .
. ORM-. . , , , . .
, , 2 , , 490 , . 490 , , . - . .
, , . , , , , , .
. , , , . . , - . , , , - , .
, , . , , . , . . , , . . , , – 3-4 , .
. . , .
? , . , , - . , : , , . , , « , ». , , , . , , , .
. Json, , . . . - Json . . , .
. , , , , . . . . , , , . , Json, . Json, . , .
. , join. . , , , , , .
Java , hibernate . Hibernate, , . . , 2 , . , . 2 – , .
, , Python, 490 , . . 5 . . . , . , - , , .
2 . , , . .
, , , . . , , , , . . , , . . . LEFT JOIN, DISTINCT , , - .
GROUP BY – DISTINCT. . . , . .
– COUNT, MAX, MIN , . . , , . , , . , . . . - . . , MIN, MAX . . .
, . «WHERE id IN (SELECT id FROM)», . «NOT EXISTS». id, , .
, . SELECT ids. id , – .
NOT EXISTS , . , : «, id, ». WHERE, . , WHERE , , , .
– . . , - , , . cron- ( ) , , , . , .
. , -, , . - . , , . PIDs . , , .
, HH , cron …, . . cron . , , – .
. . , : Kafka, RabbitMQ, ZeroMQ, .
, . . Skype SkyTools PGQ. . . . . 20 000-30 000 .
– . , . , , .
. . DBA, , Postgres. .
. , , idles. , .
idle-. . . , 10 , 15, 15 , . . idle 15 . , , .
pgbouncer . . , .
PgBouncer transaction-, . . : start transaction commit rollback. , … – . – . , , , .
Pgpool-II, , .
PgBouncer , . . Odyssey. . , . . . . -, , , - Odyssey Pgbouncer. PgBouncer – .
. , COUNT 200 , 200 . MIN , .
200 , 100 . . .
– , . . . , . , , , . , .
, . ? , 100 , 2 200 . , . , 90 % 10 % . 200 , .
. . Oracle, partition. «» . - – .
. .
?
. , , .
. , : , , , . , . , , . , , . , , . , .
. , , , , .
– . , , , : HDD SSD. NVMe . – , . , NVMe, .
? WHERE , , , .
, - . . .
– . – , . – , , . . – drop table, .
. - , , . - . , – Coca-Cola, Pepsi, Nike, Adidas . . , . . . .
, , , . . . , . . . . , . , , .
, , , 4 , 2 , 2 , , : « ?».
, - . , . , RDBMS .
. , , , .
: .
– , . INSERT. INSERT INSERT’ . DELETE DELETE SQL . .
. INSERT . , . . . . , .
, .
9.4 . . 9.6 . 10-11 . , , , , .
Postgres . , – , . .
Postgres, , , , . – . INSERTS DELETES.
, . Slony, Londiste Burcado.
Burcado – . – Slony. Londiste – , . , .
, , SQL? . , , . , - . , .
, Postgres , . .
? .
– -, , -. -, . , -. - .
. , -. Postgres , Postgres , , , , , , .
. . .
, , , , , , .
. , . , , 10-20 .
. , , .
? -, . , -, 20 , , , , . - . . , . -.
. . , Postgres, . .
. delayed . , . , 8 , 8 , . , 6 , . . . , . . . , 200 – .
. . . . , , - , , . . . – . , . . , , .
. ?
wal_keep_segments. , . - , , , wal- – 16 . . . 1 000 , , 1,5 . , .
, …, , -, , , . , «0» . , .
Max_standby_streaming_delay. . ? , . , - . , , . , , , .
, , , , . , Postgres , .
Synchronous_standby_names. - . . . . . . . . . .
Hot_standby_feedback. , , . , , , . .
, - : « , -. , , ». .
. . Hot_standby_feedback , -, . , . .
Recovery_min_apply_delay. CI/CD , , - , . 6 . 6 , . 8 .
, . , .
.
Postgres , . .
, full story , -, - . -. .
? , , . , COUNT, MAX - , , .
. 20 , . . . , , - . Join, .
- , KPI -. . , . max_standby_streaming_delay. , 4 , .
, 2 , . . . OLAP-, , -, , . , OLAP-. .
, , HeadHunter , . - . . , , . .
RDBMS , , , RDBMS, . , ClickHouse, .
. , latency. , , , , - . , . , . . . , . , , , .
, - , . - . , . .
, , , , . . . .
The “Last Addiction Stage" scheme on HeadHunter looks something like this. There they have 11 servers in cascade for the main service. And it works. They have such a crazy monolith that works without problems like a clock. Partly due to the fact that their support team has a premium that depends on how downtime was. They have downtime time allowed. And how much they saved from this allowed time, so they have a good bonus. And they are trying. They have a downtime.
I have everything on this subject. It is more panoramic, but I think it will be useful.
Video: