Aplicações 10x a 100x mais rápidas com PostgreSQL

by Fábio Telles Rodriguez

Slide 1

Slide 1 text

Aplicações 10x a 100x mais rápida com o PostgreSQL Fábio Telles Rodriguez

Slide 2

Slide 2 text

Fábio Telles Rodriguez • Consultor pela Timbira • DBA Oracle e PostgreSQL + 15 anos • Colaborador da Comunidade Brasileira de PostgreSQL • Blog: savepoint.blog.br • [email protected] • @telles

Slide 3

Slide 3 text

Antes de mais nada... Aprenda a usar o seu ORM

Slide 4

Slide 4 text

Antes de mais nada... Aprenda a usar o seu ORM Aprenda quando NÃO USAR o seu ORM

Slide 5

Slide 5 text

Antes de mais nada... Aprenda quando NÃO USAR o seu ORM • Não utilizar para relatórios e consultas complexas • Não utilizar para transações complexas (BEGIN …. COMMIT / ROLLBACK) • Utilizar apenas em operações CRUD

Slide 6

Slide 6 text

Ajustes no PostgreSQL

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

Troubleshooting PostgreSQL

Slide 9

Slide 9 text

Troubleshooting PostgreSQL Encontre o vilão • top / iotop • pg_stat_activity • pg_locks / pg_blocking_pids() • logs -> pgBadger

Slide 10

Slide 10 text

Troubleshooting PostgreSQL Siga aquele cara… (ﬁque de olho no PID em vermelho)

Slide 11

Slide 11 text

top -c top - 05:55:43 up 16 days, 1:05, 1 user, load average: 4,15, 4,91, 5,10 Tasks: 268 total, 5 running, 263 sleeping, 0 stopped, 0 zombie %Cpu(s): 29,4 us, 2,2 sy, 0,0 ni, 60,9 id, 7,0 wa, 0,0 hi, 0,2 si, 0,3 st KiB Mem : 49458404 total, 283300 free, 731524 used, 48443580 buff/cache KiB Swap: 2097148 total, 1933544 free, 163604 used. 41707168 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 28020 postgres 20 0 6685304 6,156g 6,150g R 99,7 13,1 7:37.10 postgres: user_zzz db_zzz 192.168.193.49(41822) SELECT 29777 postgres 20 0 6686492 5,927g 5,920g R 99,7 12,6 1:39.24 postgres: postgres db_zzz [local] DELETE 29804 postgres 20 0 6702516 5,622g 5,605g R 95,7 11,9 0:41.25 postgres: postgres db_zzz [local] CREATE TABLE AS 25214 postgres 20 0 6696060 6,171g 6,160g R 24,6 13,1 104:59.68 postgres: user_zzz db_zzz 192.168.163.81(58832) SELECT 28205 postgres 20 0 6685260 6,137g 6,131g S 23,6 13,0 6:10.22 postgres: user_zzz db_zzz 192.168.193.49(41828) idle 28268 postgres 20 0 6685308 6,149g 6,143g S 13,6 13,0 7:11.18 postgres: user_zzz db_zzz 192.168.193.49(41830) idle 13819 postgres 20 0 6697552 6,165g 6,147g S 12,0 13,1 36:54.04 postgres: user_zzz db_zzz 192.168.149.241(37806) idle 27643 postgres 20 0 7729100 6,167g 6,144g S 6,3 13,1 5:02.79 postgres: autovacuum worker process db_zzz 23484 postgres 20 0 6680064 8348 7888 S 1,3 0,0 109:13.33 postgres: wal sender process postgres 192.168.185.210(57958) streaming 4C24/9B795B90 6149 postgres 20 0 6679408 6,119g 6,119g S 1,0 13,0 151:08.36 postgres: writer process 22375 postgres 20 0 6687356 6,167g 6,160g S 1,0 13,1 39:53.23 postgres: user_zzz db_zzz 192.168.163.81(59232) idle 8398 postgres 20 0 6696236 6,172g 6,160g S 0,7 13,1 55:45.06 postgres: user_zzz db_zzz 192.168.163.81(59194) idle

Slide 12

Slide 12 text

iotop Total DISK READ : 16.97 M/s | Total DISK WRITE : 12.51 M/s Actual DISK READ: 16.92 M/s | Actual DISK WRITE: 8.57 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 28205 be/4 postgres 4.39 M/s 0.00 B/s 0.00 % 17.95 % postgres: user_zzz db_zzz 192.168.193.49(41828) idle 17811 be/4 postgres 3.22 M/s 54.53 K/s 0.00 % 10.81 % postgres: user_zzz db_zzz 192.168.149.241(38098) idle 9896 be/4 postgres 0.00 B/s 1744.80 K/s 0.00 % 6.78 % postgres: user_zzz db_zzz 192.168.163.81(59200) idle 27643 be/4 postgres 9.36 M/s 4.17 M/s 0.00 % 3.33 % postgres: autovacuum worker process db_zzz 25214 be/4 postgres 0.00 B/s 2.89 M/s 0.00 % 1.44 % postgres: user_zzz db_zzz 192.168.163.81(58832) idle 6150 be/4 postgres 0.00 B/s 1028.19 K/s 0.00 % 0.79 % postgres: wal writer process 3455 be/3 root 0.00 B/s 101.26 K/s 0.00 % 0.53 % [jbd2/sdc-8] 22375 be/4 postgres 0.00 B/s 179.15 K/s 0.00 % 0.24 % postgres: user_zzz db_zzz 192.168.163.81(59232) idle 30021 be/4 postgres 0.00 B/s 327.15 K/s 0.00 % 0.00 % postgres: postgres db_zzz [local] CREATE TABLE AS 6149 be/4 postgres 0.00 B/s 2.08 M/s 0.00 % 0.00 % postgres: writer process 27115 be/4 postgres 0.00 B/s 15.58 K/s 0.00 % 0.00 % postgres: user_zzz db_zzz 192.168.193.49(41806) SELECT

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Logs

Slide 15

Slide 15 text

Logs Onde logar: • log_destination: stderr, csvlog, syslog, eventlog (Windows) • logging_collector: on • log_directory: pg_log, log • log_ﬁlename*: postgresql-%Y-%m-%d_%H%M%S.log, pg-%A.log • log_rotation_age: 1d • log_rotation_size: ~50MB • log_truncate_on_rotation: on, off * http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html

Slide 16

Slide 16 text

Logs Quando logar: • client_min_messages, log_min_messages, log_min_error_statement • log_min_duration_statement

Slide 17

Slide 17 text

Logs O que logar: • log_line_preﬁx • log_connections, log_disconnections • log_lock_waits • log_checkpoints • log_temp_ﬁles

Slide 18

Slide 18 text

Logs Exemplo: # - Where to Log - log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 64MB # - When to Log - client_min_messages = log log_min_messages = notice log_min_error_statement = warning log_min_duration_statement = 120000 # - What to Log - log_checkpoints = on log_connections = off log_disconnections = off log_line_prefix = '%t [%p]: [%l] user=%u,db=%d,app=%a,host=%h' log_lock_waits = on log_statement = 'ddl' log_temp_files = 0

Slide 19

Slide 19 text

Logs Alteração em pleno vôo: • Alteração no postgresql.conf log_min_duration_statement = 0 log_connections = on log_disconnections = on • Comando SQL SELECT pg_rotate_logfile(), pg_reload_conf();

Slide 20

Slide 20 text

VACUUM, ANALYZE

Slide 21

Slide 21 text

VACUUM, ANALYZE NÃO DESLIGUE O AUTOVACUUM! • Limpa tuplas mortas • Atualiza estatísticas • Previne esgotamento de numeração de transações • Roda por padrão automaticamente cada vez que • 20% dos registros da tabela são atualizados • 50 registros são atualizados • Permite ajuste individual por tabela • pg_stat_user_tables

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Índices • Tipos: • B-tree (<, <=, =, >=, >) • Hash ( = ) • GiST (<<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=, &&) • SP-GiST (<<, >>, ~=, <@, <^, >^) • GIN ((<@, @>, =, &&) • BRIN (<, <=, =, >=, >) • Funcionalidades: funções, opclass, parcial, composto, etc • pg_stat_user_indexes • pg_stats • EXPLAIN, EXPLAIN ANALYZE

Slide 24

Slide 24 text

Índices Quando o PostgreSQL não usa o índice: • Baixo volume de dados (<1000); • Baixa seletividade (> ~10%); • Tipos de dados não batem (integer = numeric) • Funções no WHERE (nome || sobrenome = ‘Fábio Telles’)

Slide 25

Slide 25 text

Tipos de dados • Numeros: INTEGER, NUMERIC, FLOAT • Alfanumérico: CHAR, VARCHAR, TEXT • Tempo: DATE, TIME, TIMESTAMP, INTERVAL • Geoméricos: ponto, linha, polígono, etc • Boolean, Enum, bytea • Network, bit string, Full Text Search • XML, JSONB, UUID • Arrays, tipos compostos, Range types

Slide 26

Slide 26 text

Carga de dados • INSERT TRADICIONAL INSERT INTO t1 (a,b,c) VALUES (1, 'aaa', '2017-12-08'); INSERT INTO t1 (a,b,c) VALUES (2, 'bbb', '2017-12-07'); INSERT INTO t1 (a,b,c) VALUES (3, 'ccc', '2017-12-06'); INSERT INTO t1 (a,b,c) VALUES (4, 'ddd', '2017-12-05');

Slide 27

Slide 27 text

Carga de dados • INSERT múltiplo INSERT INTO t1 (a,b,c) VALUES (1, 'aaa', '2017-12-08'), (2, 'bbb', '2017-12-07'), (3, 'ccc', '2017-12-06'), (4, 'ddd', '2017-12-05') ;

Slide 28

Slide 28 text

Carga de dados • PREPARED STATEMENT PREPARE carga (integer, varchar, date) AS INSERT INTO t1 VALUES ($1, $2, $3); EXECUTE carga (1, 'aaa', '2017-12-08'); EXECUTE carga (2, 'bbb', '2017-12-07'); EXECUTE carga (3, 'ccc', '2017-12-06'); EXECUTE carga (4, 'ddd', '2017-12-05'); DEALLOCATE carga;

Slide 29

Slide 29 text

Carga de dados • COPY COPY t1 (a, b, c) FROM stdin; 1 aaa 2017-12-08 2 bbb 2017-12-07 3 ccc 2017-12-06 4 ddd 2017-12-05 \.

Slide 30

Slide 30 text

Carga de dados • SUBSELECT INSERT INTO t1 (a,b,c) SELECT m,n,o FROM t2 WHERE status = 0;

Slide 31

Slide 31 text

Carga de dados • RETURNING > CREATE TABLE t3 ( id serial, nome varchar); CREATE TABLE > INSERT INTO t3 (nome) VALUES ('Fábio') RETURNING id; id ---- 1 > INSERT INTO t3 (nome) VALUES ('Telles') RETURNING id; id ---- 2

Slide 32

Slide 32 text

Carga de dados • CTE (Common Table Expression) + RETURNING WITH upd AS ( UPDATE t2 SET c = c + '7 days'::interval WHERE a = 3 RETURNING *) INSERT INTO t1 SELECT a, b, c FROM upd;

Slide 33

Slide 33 text

Carga de dados • ON CONFLICT (MERGE ou UPSERT) INSERT INTO t1 (a,b,c) VALUES (5,’ddd’,’2018/12/10’) ON CONFLICT (a) DO UPDATE SET b = ‘ddd’, c= ‘2018/12/10’

Slide 34

Slide 34 text

Window Functions

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Window Functions SELECT title, category, total_sales, rank() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank, sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS sum, sum(total_sales) OVER () total, FROM sales_pgday; title | category | total_sales | rank | sum | total -----------------------------+---------------+--------------+------+---------+---------- FOOL MOCKINGBIRD | Action | 175.77 | 1 | 175.77 | 67416.51 AMERICAN CIRCUS | Action | 167.78 | 2 | 343.55 | 67416.51 STAGECOACH ARMAGEDDON | Action | 154.74 | 3 | 498.29 | 67416.51 EASY GLADIATOR | Action | 150.77 | 4 | 649.06 | 67416.51 ... BRIDE INTRIGUE | Action | 21.81 | 58 | 4329.19 | 67416.51 CROW GREASE | Action | 18.88 | 59 | 4348.07 | 67416.51 LAWRENCE LOVE | Action | 15.87 | 60 | 4363.94 | 67416.51 MONTEZUMA COMMAND | Action | 11.91 | 61 | 4375.85 | 67416.51 DOGMA FAMILY | Animation | 178.70 | 1 | 178.70 | 67416.51 SUNRISE LEAGUE | Animation | 170.76 | 2 | 349.46 | 67416.51 TITANIC BOONDOCK | Animation | 154.77 | 3 | 504.23 | 67416.51 FORRESTER COMANCHEROS | Animation | 146.73 | 4 | 650.96 | 67416.51 ...

Slide 39

Slide 39 text

Window Functions SELECT category, title, soma, total, trunc((soma / total) * 100,2) AS "%" FROM (SELECT title, category, total_sales, sum(total_sales) OVER () total, rank() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank, sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS soma FROM sales_pgday) sales; category | title | soma | total | % -------------+-----------------------------+---------+----------+------ Action | FOOL MOCKINGBIRD | 175.77 | 67416.51 | 0.26 Action | AMERICAN CIRCUS | 343.55 | 67416.51 | 0.50 Action | STAGECOACH ARMAGEDDON | 498.29 | 67416.51 | 0.73 Action | EASY GLADIATOR | 649.06 | 67416.51 | 0.96 Action | MINDS TRUMAN | 798.86 | 67416.51 | 1.18 ... Action | BULL SHAWSHANK | 4307.38 | 67416.51 | 6.38 Action | BRIDE INTRIGUE | 4329.19 | 67416.51 | 6.42 Action | CROW GREASE | 4348.07 | 67416.51 | 6.44 Action | LAWRENCE LOVE | 4363.94 | 67416.51 | 6.47 Action | MONTEZUMA COMMAND | 4375.85 | 67416.51 | 6.49 Animation | DOGMA FAMILY | 178.70 | 67416.51 | 0.26 Animation | SUNRISE LEAGUE | 349.46 | 67416.51 | 0.51 Animation | TITANIC BOONDOCK | 504.23 | 67416.51 | 0.74 Animation | FORRESTER COMANCHEROS | 650.96 | 67416.51 | 0.96 Animation | FALCON VOLUME | 778.73 | 67416.51 | 1.15 ...

Slide 40

Slide 40 text

Window Functions SELECT category, title, trunc((soma / total) * 100,2) AS "%", CASE WHEN (soma / total) <= 0.2 THEN 'A' WHEN (soma / total) > 0.2 AND (soma / total) <= 0.8 THEN 'B' ELSE 'C' END AS curva_abc FROM (SELECT title, category, total_sales, sum(total_sales) OVER (PARTITION BY category) total, rank() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank, sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS soma FROM sales_pgday) sales ORDER BY category, "%";

Slide 41

Slide 41 text

Slide 42

Slide 42 text

Slide 43

Slide 43 text

PGConf Brasil 2018

Slide 44

Slide 44 text

Comunidade • http://listas.postgresql.org.br/ • https://t.me/pgconfbr • https://t.me/postgresqlbr • https://t.me/DbaBrasil

Slide 45

Slide 45 text

[email protected]