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

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

View Slide

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

View Slide

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

View Slide

Aprenda a usar o seu ORM
Aprenda quando NÃO USAR o seu ORM

View Slide

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

View Slide

Ajustes no PostgreSQL

View Slide

View Slide

Troubleshooting PostgreSQL

View Slide

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

View Slide

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

View Slide

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
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

View Slide

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

View Slide

Logs

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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();

View Slide

VACUUM, ANALYZE

View Slide

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

View Slide

Í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

View Slide

Í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’)

View Slide

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

View Slide

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');

View Slide

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')
;

View Slide

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;

View Slide

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
\.

View Slide

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

View Slide

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

View Slide

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;

View Slide

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’

View Slide

Window Functions

View Slide

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
...

View Slide

Window Functions
SELECT
category, title, soma, total,
trunc((soma / total) * 100,2) AS "%"
FROM
(SELECT
title, category, total_sales,
sum(total_sales) OVER () total,
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
...

View Slide

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,
sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS soma
FROM sales_pgday) sales
ORDER BY category, "%";

View Slide

PGConf Brasil 2018

View Slide

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

View Slide

[email protected]

View Slide

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

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

Fábio Telles Rodriguez

More Decks by Fábio Telles Rodriguez

Other Decks in Programming

Featured

Transcript