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

Como acelerar sua aplicação utilizando recursos do PostgreSQL

Palestra realizada no PHP Conference Brasil 2017

Transcript

1. Aplicações 10x a 100x mais rápida com o PostgreSQL Fábio Telles Rodriguez
2. Fábio Telles Rodriguez • Consultor pela Timbira • DBA Oracle e PostgreSQL + 15 anos • Colaborador da Comunidade Brasileira de PostgreSQL • Blog: savepoint.blog.br • telles@timbira.com.br • @telles
3. Antes de mais nada... Aprenda a usar o seu ORM
4. Antes de mais nada... Aprenda a usar o seu ORM Aprenda quando NÃO USAR o seu ORM
5. 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
6. Ajustes no PostgreSQL
8. Troubleshooting PostgreSQL
9. Troubleshooting PostgreSQL Encontre o vilão • top / iotop • pg_stat_activity • pg_locks / pg_blocking_pids() • logs -> pgBadger
10. Troubleshooting PostgreSQL Siga aquele cara… (fique de olho no PID em vermelho)
11. 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
12. 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
13. pg_stat_activity SELECT pid, usename, client_addr, state, application_name, query_start, xact_start, wait_event_type, query FROM pg_stat_activity WHERE state NOT LIKE 'idle%' AND pid != pg_backend_pid() ORDER BY usename, client_addr desc, xact_start desc, backend_start desc; pid | usename | client_addr | state | application_name | query_start | xact_start | query -------+---------+------------+----------------+--------+--------------------+-------------------------------+-------------------------------+------------------------ 30339 | postgres | | active | psql | 2017-12-08 06:11:06.853674+00 | 2017-12-08 06:10:02.040758+00 | DELETE FROM 27643 | postgres | | active | | 2017-12-08 04:41:32.867775+00 | 2017-12-08 04:41:32.867775+00 | autovacuum: 28473 | user_zzz | 192.168.193.49 | active | ManagerAlertSearch | 2017-12-08 06:11:44.371805+00 | 2017-12-08 06:11:43.846183+00 | SELECT id FROM (3 registros)
14. Logs
15. Logs Onde logar: • log_destination: stderr, csvlog, syslog, eventlog (Windows) • logging_collector: on • log_directory: pg_log, log • log_filename*: 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
16. Logs Quando logar: • client_min_messages, log_min_messages, log_min_error_statement • log_min_duration_statement
17. Logs O que logar: • log_line_prefix • log_connections, log_disconnections • log_lock_waits • log_checkpoints • log_temp_files
18. 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
19. 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();
20. VACUUM, ANALYZE
21. 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
22. VACUUM, ANALYZE pg_stat_user_tables: relid | 16435 schemaname | public relname | cotacao_passagem seq_scan | 37 seq_tup_read | 14003357980 idx_scan | 668411712206 idx_tup_fetch | 130379107066 n_tup_ins | 3898463951 n_tup_upd | 0 n_tup_del | 3512368436 n_tup_hot_upd | 0 n_live_tup | 781268923 n_dead_tup | 4072081 n_mod_since_analyze | 809082 last_vacuum | 2017-12-06 14:53:33.876836+00 last_autovacuum | 2017-12-08 06:51:49.030022+00 last_analyze | 2017-12-06 14:57:56.533121+00 last_autoanalyze | 2017-12-08 06:52:25.995018+00 vacuum_count | 29 autovacuum_count | 973 analyze_count | 24 autoanalyze_count | 1601
23. Í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
24. Í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’)
25. 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
26. 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');
27. 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') ;
28. 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;
29. 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 \.
30. Carga de dados • SUBSELECT INSERT INTO t1 (a,b,c) SELECT m,n,o FROM t2 WHERE status = 0;
31. 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
32. 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;
33. 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’
34. Window Functions
35. Window Functions title | category | total_sales ----------------------+-------------+------------- AMADEUS HOLY | Action | 33.79 AMERICAN CIRCUS | Action | 167.78 ANTITRUST TOMATOES | Action | 37.90 BAREFOOT MANCHURIAN | Action | 66.82 BERETS AGENT | Action | 78.78 BRIDE INTRIGUE | Action | 21.81 BULL SHAWSHANK | Action | 21.84 CADDYSHACK JEDI | Action | 51.84 CAMPUS REMEMBER | Action | 90.81 CASUALTIES ENCINO | Action | 72.91 CELEBRITY HORN | Action | 32.76 CLUELESS BUCKET | Action | 112.75 CROW GREASE | Action | 18.88 DANCES NONE | Action | 31.86 DARKO DORADO | Action | 82.89 DARN FORRESTER | Action | 93.82 DEVIL DESIRE | Action | 83.85 DRAGON SQUAD | Action | 27.89 DREAM PICKUP | Action | 81.78 DRIFTER COMMANDMENTS | Action | 141.76
36. Window Functions ELECT title, category, total_sales, rank() OVER (ORDER BY total_sales DESC) AS rank FROM sales_pgday; title | category | total_sales | rank ------------------------------+--------------+-------------+------ TELEGRAPH VOYAGE | Music | 231.73 | 1 WIFE TURN | Documentary | 223.69 | 2 ZORRO ARK | Comedy | 214.69 | 3 GOODFELLAS SALUTE | Sci-Fi | 209.69 | 4 SATURDAY LAMBS | Sports | 204.72 | 5 TITANS JERK | Sci-Fi | 201.71 | 6 TORQUE BOUND | Drama | 198.72 | 7 HARRY IDAHO | Drama | 195.70 | 8 INNOCENT USUAL | Foreign | 191.74 | 9 HUSTLER PARTY | Comedy | 190.78 | 10 PELICAN COMFORTS | Documentary | 188.74 | 11 CAT CONEHEADS | Comedy | 181.70 | 12 ENEMY ODDS | Music | 180.71 | 13 BUCKET BROTHERHOOD | Travel | 180.66 | 14 RANGE MOONWALKER | Family | 179.73 | 15 MASSACRE USUAL | Games | 179.70 | 16 ...
37. Window Functions SELECT title, category, total_sales, rank() OVER ( PARTITION BY category ORDER BY total_sales DESC) AS rank FROM sales_pgday; title | category | total_sales | rank ------------------------------+-------------+-------------+------ FOOL MOCKINGBIRD | Action | 175.77 | 1 AMERICAN CIRCUS | Action | 167.78 | 2 STAGECOACH ARMAGEDDON | Action | 154.74 | 3 EASY GLADIATOR | Action | 150.77 | 4 ... CROW GREASE | Action | 18.88 | 59 LAWRENCE LOVE | Action | 15.87 | 60 MONTEZUMA COMMAND | Action | 11.91 | 61 DOGMA FAMILY | Animation | 178.70 | 1 SUNRISE LEAGUE | Animation | 170.76 | 2 TITANIC BOONDOCK | Animation | 154.77 | 3
38. 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 ...
39. 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 ...
40. 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, "%";
41. Window Functions category | title | % | curva_abc ---------------+-----------------------------+------+----------- Action | FOOL MOCKINGBIRD | 0.26 | A Action | AMERICAN CIRCUS | 0.50 | A Action | STAGECOACH ARMAGEDDON | 0.73 | A ... Action | CAMPUS REMEMBER | 2.99 | A Action | FORREST SONS | 3.12 | A Action | DEVIL DESIRE | 3.24 | A Action | KISSING DOLLS |21.63 | B Action | TRIP NEWTON |24.96 | B Action | DRIFTER COMMANDMENTS |28.20 | B Action | SUSPECTS QUILLS |31.25 | B ... Action | BAREFOOT MANCHURIAN |70.56 | B Action | HANDICAP BOONDOCK |72.02 | B Action | GRAIL FRANKENSTEIN |78.65 | B Action | SHRUNK DIVINE |79.86 | B Action | CADDYSHACK JEDI |81.05 | C Action | GOSFORD DONNIE |82.16 | C Action | ENTRAPMENT SATISFACTION |83.26 | C ...
42. Window Functions category | title | % | curva_abc ---------------+-----------------------------+------+----------- Action | FOOL MOCKINGBIRD | 0.26 | A Action | AMERICAN CIRCUS | 0.50 | A Action | STAGECOACH ARMAGEDDON | 0.73 | A ... Action | CAMPUS REMEMBER | 2.99 | A Action | FORREST SONS | 3.12 | A Action | DEVIL DESIRE | 3.24 | A Action | KISSING DOLLS |21.63 | B Action | TRIP NEWTON |24.96 | B Action | DRIFTER COMMANDMENTS |28.20 | B Action | SUSPECTS QUILLS |31.25 | B ... Action | BAREFOOT MANCHURIAN |70.56 | B Action | HANDICAP BOONDOCK |72.02 | B Action | GRAIL FRANKENSTEIN |78.65 | B Action | SHRUNK DIVINE |79.86 | B Action | CADDYSHACK JEDI |81.05 | C Action | GOSFORD DONNIE |82.16 | C Action | ENTRAPMENT SATISFACTION |83.26 | C ...
43. PGConf Brasil 2018
44. Comunidade • http://listas.postgresql.org.br/ • https://t.me/pgconfbr • https://t.me/postgresqlbr • https://t.me/DbaBrasil
45. contato@timbira.com.br