I suggest that you familiarize yourself with the decoding of the report of the beginning of 2016 by Andrei Salnikov "Typical errors in applications that lead to bloat in postgresql"
In this report, I will analyze the main errors in applications that arise at the stage of designing and writing application code. And I will take only those errors that lead to bloat in Postgresql. As a rule, this is the beginning of the end of the performance of your system as a whole, although initially no prerequisites for this were visible.
Glad to welcome everyone! This report is not as technical as the previous one from my colleague. This report is aimed at developers of backend systems mainly because we have a fairly large number of customers. And they all make the same mistakes. I’ll tell you about them. I will explain to what fatal and bad these errors lead.
Why are mistakes made? They are performed for two reasons: by chance, maybe it’s also because of ignorance of some kind of mechanism that occurs at the level between the base and the application, as well as in the base itself.
, . , . , , . .
, . , . - .
: , 2 MB. . – 2 000 .
, , . 2 . – , .
, . 2 MB. .
, . .
– . , . , – -. .
. . , , . , ( ) .
, , . – 2 000 , .
. - . :
- – , . . . . , , - . – , .
- , - exception. exception . .
- – . . . , , .
?
, . bloat. , , . . 10 , 10 , 20 . .
, . , . , , 300 . , . . .
. . . . Postgres , - .
? . . , , . . , . . – , .
. , . . . .
, , , . , . - . , .
: « ?». . , . . . . . - , .
, : . .
, . . , - . . . . .
, , . , .
, , , . . ?
. - . . , , . . , , , . , . - Postgres.
? - , : « , , id , ». id. . , - , , , . .
, - . , , . , . . - , , . PostgreSQL.
? ?
- , - , - . . , , id. id, , . , . , . .
. - , , . ? , . .
. , .
, : , , . «bloat», . , 2 MB, 300 MB? .
?
- 150 . , .
- . , . , , - . , , .
- . , .
- , 10 , 10 , 10 , . . . 20 , 10 . , . - , , , , .
- : « ?», , .
, .
, . , - , - . pgstattuple. , , , .
, , . . . – VACUUM FULL. , , . Pg_repack pgcompacttable – . .
, . . , . .
, , , , , :
- . -. idle in transaction. , , - , .
- , , . . . «» , .
, , VACUUM FULL’ . production.
. .
, , , . , , . , , , , . . , , .
. . , , . .
,
- . , : , . , - ETL. . .
- , . , , . , , .
- .
- . .
? . -.
. . , . , , 2 . , . .
. , . .
, . .
, . .
– . , 3 . – 3 . , , .
, . . – hot_standby_feedback. . Hot_standby_feedback . . .
-? - . , . , - -. , , . . . - .
, . . . , .
, , ?
- . , , . . . , Load Average .
- , . , . , .
- . raid. . . , , .
- : , . .
- - .
. . . , . , , , .
. - - . - . , -, , . . , .
, , . . , , . . , .
- , .
. . . , .
- hot_standby_feedback? , . - . - , .
- max_standby_streaming_delay? , – . , - , . , . , , - . , , – , .
- , , , hot_standby_feedback . . , . . , . . . , .
- , . streaming_delay. . , 6 , . .
:
. .
, .
- . . . , , , .
- . , , . , , , . : .
- . , Liquibase. . . . . , . . , . .
- , .
. , 15 GB. , , .
, , . , . . . 5 , , 7,5.
.
, :
bloat, .
, , , . .
, , . 7,5, . 2 , 1,5 , . . . – . .
, . . .
- . .
- . DBA , DBA. . , , , .
- , , , , . . , :
- , .
- . ? -, . , - -.
- , , , .
- , ( ) , . – .
- , , . - , .
- .
bloat .
.
https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql
https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat_approx.sql
, .
, bloat, pgstattuple.
, . . .
- , bloat .
- , – bloat bloat . , bloat Postgres . MVCC.
- 20 % bloat – . . . .
, , , , .
, bloat:
- , . . VACUUM FULL. , . VACUUM FULL? . . , . . , : , , . VACUUM FULL , .
- pg_repack. VACUUM FULL, . , , , . VACUUM FULL. , , . , -.
- – pgcompacttable. , . pgcompacttable , . , , , . , . . . .
.
bloat , :
, , . . !
! , . ? . . , , - . - joins. - , , - . . . , . ? , ? ?
– , DBA.
.
PostgreSQL , pg_stat_activity, . , .
5 ?
cron . , . . . , . , . .
, ?
. . .
! pg_repack . , …
.
… . ?
, , . . pg_repack , . , - . .
. . - ?
, .
, VACUUM FULL?
VACUUM FULL, , . , . pg_repack . , , .
! . , . . . – . - ?
. Postgres . . , . id , , . , . - . , . .
. . . , . . - , .
, . Postgres . . , , tos-. : , json. . bloat, . . . .
! statement timeout?
. . . . , , . . . . cron, . , . , 10 . . pg_stat_activity.
! . , . - exception, rollback . . . , . , PostgreSQL , ?
, , , ORM, . . auto commit on, , .
. . ?
. . auto commit on. . , - . «start transaction» «end transaction», .
! ! , , - . - , ?
- .
, DBA , . .
, - , .
?
reserved space, . . , , . , . – , . - , .
?
. , , , . , , . .
! . -, , , , . , . ?
.
?
. , pg_rapack, pgcompacttable. , . VACUUM FULL , , . . .
. , . , – , – .
? , . . , ? - . , , , , . , , , , . PostgreSQL , . , , .
Andrey, there is a question. These wonderful graphs that you showed during the presentation, is the result of work, some kind of your utility? What built the graphs?
This is an Okmeter service .
Is this a commercial product?
Yes. This is a commercial product.