Proponho me familiarizar com a transcrição do relatório de início de 2020 de Andrei Borodin "Odyssey: arquitetura, configuração, monitoramento"
Mais recentemente, lançamos a versão 1.0 de nossas conexões extratoras Odyssey . Ele foi desenvolvido para resolver os problemas de gerenciamento de conexões de instalações PostgreSQL altamente carregadas. Neste relatório, gostaria de falar sobre a arquitetura e operação da Odyssey. Problemas que foram resolvidos em uma transição bastante longa entre 1.0rc e 1.0 também serão afetados.
![](https://habrastorage.org/webt/jm/wp/hk/jmwphkutdvmkbvdofhy7jdhgcda.png)
Olá a todos! Eu faço o Postgres no Yandex. E no último ano e meio, venho fazendo o gerenciamento de conexões. Coisa bastante crítica, porque fica no caminho de cada solicitação. Em russo, dizemos "Odisséia".
![](https://habrastorage.org/webt/w2/x-/mj/w2x-mj-clk1ysbnuw1kw1jmeldy.png)
Connection pooler. . , . connection pooler . . – , . , connection pooler , . Connection pooler . , .
![](https://habrastorage.org/webt/ry/tg/h5/rytgh5wobtwitdxdyhfwbr0nkjy.png)
Postgres . , Postgres ., - Postrgres’. . . .
., Postgres. , , , 1 .
. , Postgres. , , Odyssey .
![](https://habrastorage.org/webt/nj/j2/ay/njj2ay60vl_2eiiizxp-lzl9ray.png)
? , . Postgres , , – , .
, .
![](https://habrastorage.org/webt/qo/z9/6w/qoz96wr4nlzaeacfktldohkbclk.png)
, Postgres . . - . , procArray.
![](https://habrastorage.org/webt/1c/hm/j2/1chmj2fbbncs42ykn6mzifzfovm.png)
- , , Application-side pooling.
- – Proxy pooler.
- -, , pooler.
- , .
![](https://habrastorage.org/webt/6q/jj/kj/6qjjkjppv4wbbozbhszti1ounja.png)
Application-side pooling – , , .
![](https://habrastorage.org/webt/dg/jg/dx/dgjgdxng9cam36fewqkgnxfoxjc.png)
, , instances , .
![](https://habrastorage.org/webt/az/ms/bx/azmsbxzuzuuxzxvv9bh6p5u8qhu.png)
, . , , .
, , .
![](https://habrastorage.org/webt/qe/mm/hn/qemmhn_93qsco5nnjpytaubnrb4.png)
instance Postgres. , . , . , - – .
Application-side pooling . , ?
![](https://habrastorage.org/webt/9g/ea/zk/9geazki0s8s8cdxizjjdmdrfzqc.png)
pooler, . .
Pgpool II. , . . . - , , .
Crunchy-Proxy. Crunchy Data Proxy pooler. Go, - .
PgBouncer. pooler , pooling. . , , .
- PgBouncer.
![](https://habrastorage.org/webt/sh/9-/2m/sh9-2mvgroe5etq2n7bsp21riso.png)
.
PgBouncer . PgBouncer , . . , - , , , : , , . , PgBouncer .
, , , , Bouncer .
![](https://habrastorage.org/webt/cq/l6/qn/cql6qn0omiun_8guyhfhbasi9wk.png)
, , PgBouncer . . CPU 97 % . , , Bouncer .
![](https://habrastorage.org/webt/px/1j/t4/px1jt4kemsnmx6lqbdvg95exfdw.png)
, . , PgBouncer PgBouncer. PgBouncer, . reuseport. .
![](https://habrastorage.org/webt/u3/et/_r/u3et_rrvzafue2oyoe1oudshwzo.png)
PgBouncer, Bouncer, - .
PgBouncer, connection pool PgBouncer. , .
- PgBouncer, , c PgBouncer - .
PgBouncer TLS-. TLS- – , , . . . , , . TLS handshake . PgBouncer, , TLS , . , . . , , .
![](https://habrastorage.org/webt/m4/1q/op/m41qopirdgqcceoo0txeqxc0e0o.png)
, , , Odyssey – , PgBouncer .
- , PgBouncer , . . pull request. . . , PgBouncer .
Odyssey PgBouncer . .
![](https://habrastorage.org/webt/jx/n6/t5/jxn6t5pe0je8rok0d9dhqvoglcq.png)
connection pooler, Odyssey.
![](https://habrastorage.org/webt/6n/uf/4a/6nuf4a92hkmf8xiquzwzn0lzbqo.png)
Odyssey . Odyssey. Kiwi, Machinarium.
![](https://habrastorage.org/webt/2o/nr/fp/2onrfpptumor6earvanpjnl0kwg.png)
Kiwi Postgres. Postgres Proto3. Kiwi , , . libpq Postgres, , . connection pooler Postgres’ .
![](https://habrastorage.org/webt/wi/ma/wq/wimawqeglhp63-3ydbpap1p_r38.png)
Machinarium – , .
Machinarium . – , , thread . . – , . , , .
, . Machinarium. – , , , . sleep. , csw_worker Mashinarium’, - 0 .
Machinarium - , .
, , PgBouncer. PgBouncer libevent, Machinarium – libevent, connection pooling, .
![](https://habrastorage.org/webt/ax/pq/dc/axpqdcc4nixn6ca6jiqecz4kolg.png)
Odyssey . main-, . . main-, , . . TCP-, , . - , , , .
, router, wait_read sleep, , . -. . , . . . . , , , , , . , , . , .
, , , , PgBouncer PgBouncer. . . , show stats, show clients, show pulls .
![](https://habrastorage.org/webt/kl/zd/we/klzdwej4umd31hjqajfb0c9lczc.png)
Odyssey production instances Postgres. . , PgBouncer, , . PgBouncer .
![](https://habrastorage.org/webt/pd/be/69/pdbe69ymhbpbp7xvd27nclyq8_w.png)
, . Odyssey production- , Postgres profession Odyssey. , production , . . , , .
![](https://habrastorage.org/webt/gr/gq/07/grgq07qatffdbwmqxy5jnwci0zi.png)
. Odyssey, Odyssey TLS-. TLS- . .
Odyssey , , . connection pooler , , TLS-, , connection pooler, , TLS-.
, , .
![](https://habrastorage.org/webt/pz/y6/kr/pzy6krze-hukk3sz_oaggaisnsq.png)
, , , 20 000-30 000 TLS-, pooler TLS- . TLS handshake . - TLS handshake, , 3 30 000 TLS handshake, .
accept , . . , -, , , handshake -.
![](https://habrastorage.org/webt/9y/da/sx/9ydasxer6ktusc0ghbzlhodyv10.png)
, , , Postgres , GSSAPI.
, Postgres , -, TLS-, GSSAPI.
PSQL GSSAPI. PgBouncer, Odyssey 12 Postgres . , , . .
![](https://habrastorage.org/webt/kw/ul/pt/kwulptppv-pr2huc42x8uewgyqc.png)
Pooler , GSSAPI, , . TLS-.
Odyssey . 1.0 storages, , storage . . , . , walsender, : « ?». : «, basebackup» «, , walsender ».
, break inEnhaced. 1.1, , , , , . . . , . , .
![](https://habrastorage.org/webt/d2/t8/eu/d2t8euksh7dzddp-dkxojwjkbzg.png)
– connection pooler. Connection pooler . . pooler’ , 1 000 .
Odyssey , , 1 000 , 1 000 , . , . PgBouncer , , OLTP-. pooler , .
, , . , , , , 4- . , PgBouncer, .
![](https://habrastorage.org/webt/uo/xa/hr/uoxahrifuu0xavget0ufkpuonmo.png)
, – ML. PgBouncer, Odyssey – . , . .
pooler , . - . . ML-, . , .
![](https://habrastorage.org/webt/i-/0c/5f/i-0c5fjhgoljym154f20zgmyjxw.png)
, , . , , , .
, , , . , connection pooler, . , Python - . , , . .
PgBouncer , – , . TLS handshake, 15 , PgBouncer . . , 1.0. , , .
![](https://habrastorage.org/webt/lv/eb/ok/lvebokyfe5dmh_wzysn2c6hks7g.png)
client_max_routing TLS handshakes. , TLS-.
![](https://habrastorage.org/webt/y0/9w/gp/y09wgp_spjyowdikknaog6rgxcc.png)
, , .
![](https://habrastorage.org/webt/br/lo/lo/brlolongk249emwevgj5mq7e_bq.png)
Odyssey , , TLS-. listen.
Odyssey – , . , , .
Listen , .
![](https://habrastorage.org/webt/i6/sd/ld/i6sdldcpl2yflzjjodlcs6cbrym.png)
Storage – . , remote, local. Local – , . . , PgBouncer PgBouncer.
![](https://habrastorage.org/webt/kg/wc/yw/kgwcywexhtggzjnxitph-72fz8g.png)
, – database user, , . , , . .
, Postgres – client_fwd_error. «no», Bouncer. . . - , , Bouncer. «yes», , Postgres.
Pool_cancel pool_rollback – , , , , cancel .
Application_name_add_host – Bouncer, , ip- application_name .
, , quantiles quantiles .
![](https://habrastorage.org/webt/hf/8n/cu/hf8ncubvoktawr4gcjubjtbcs_m.png)
? Bouncer . , . 10 % , , , - . 10 % .
– , . 0,99 , 99 % 1,273 . , - , - - , - cron-. . , , – .
![](https://habrastorage.org/webt/us/z9/46/usz946dkwgzbrklt4i731ykjsvs.png)
Odyssey. PgBouncer.
Utilization – Odyssey. 4 , , Odyssey 4- . 400 %, , , .
Saturation Errors – , Odyssey, , . . - , .
![](https://habrastorage.org/webt/an/nq/gu/annqguo-35rymeyabicttux5sfq.png)
Odyssey , online restart. PgBouncer PgBouncer – PgBouncer. , , Odyssey .
reload Odyssey , , Odyssey , . , reload , restart , .
Odyssey , , – IPv4 IPv6 - PgBouncer Odyssey PgBouncer , Odyssey IPv6. , , IPv4 , . , , . , IPv4 , , .
![](https://habrastorage.org/webt/om/cu/ch/omcuchchrncipu_dynbrfeogljc.png)
Odyssey Open Source . . . , PgBouncer source-port, , - , . - , .
, SCRAM .
merge pause resume. , . . , . - pooler. , . , 11 12, 13 Postgres.
![](https://habrastorage.org/webt/v8/hs/ku/v8hskux8tsojdawundjwbcs7z18.png)
! . . Odyssey? . . hba , Postgres pg_hba.conf . .? GitHub , , .
, .
ip, hostname . ., . . , .
iptables .
, iptables – , . Odyssey – Bouncer, , , pg_hba .
. Issue GitHub.
, .
-. , .
, !
! metrics endpoint, Prometheus .
, Prometheus. , . : , . , , . – , . . Prometheus - , ? .
, Prometheus - , , , . endpoints metrics , Prometheus’. , , .
?
Zabbix . , , , , Bouncer , . endpoint .
. . Odyssey http-?
-, . , Bouncer.
. .
?
– , . .
. - Prometheus, , , , ?
– Odyssey , - , , , . , , . - ? , .
. – , . , . . . - , , . , , . , .
, , «connection pooler». - . , . .
, .
, .
, . , , , , , , . , pooler, . , . ? 2 . pooler? , pooler .
.
.
! . , - Prometheus, . , , - , , Prometheus, Cloudwatch, , ?
Odyssey?
call Odyssey. , . , , , . , , .
show stats. ?
- , .
, exporter Prometheus.
exporter ?
Exporter, .
. . , Postgres Odyssey : « ?». Odyssey , . exporter - http . , Odyssey. Postgres CentOS . , . Exporter – . . , , C, - , , Go. . ?
! ? ?
? , . . open source. , . -, . GitHub. . , .
, ?
GCC Clang.
. ?
C99.
, - ? ? , ?
20 000 . 10 000 – . . . - , , , , , - .
? 500 MB , - , - . , , .
10 000 , ?
, , . . . 10 000 , – ? . . TLS handshake – 100 . 100 , 10 . . 10 , 100 . , - . 100 – . 10 . TLS handshake – .
Odyssey.conf max clients. ? , instance Odyssey per_worker? ? . . user1 , , 50 Odyssey, user2 , , 100. ?
, . . , TLS handshake, , . . . , . TLS - , , . max clients – . , .
max clients 20 000 20 000 TCP . 20 001- , : «» TLS handshake. pool size. pool size «1», , , , , - . , 5300: too many clients for this pool, . . sorry, .
. . wait - pool size?
Database. database user. user pool size. Postgres. Postgres : «too many clients». Odyssey . , round trip postmaster. Postmaster , . , , pool size. pool size.
! . Bouncer : MD5 hash. , MD5 hash?
.
. . ?
, . . include .
, ?
, . Storage , listen , . . GitHub : « , . . - , ». , . , - . , , . .