$30 off During Our Annual Pro Sale. View Details »

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

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

Fábio Telles Rodriguez

December 08, 2017
Tweet

More Decks by Fábio Telles Rodriguez

Other Decks in Programming

Transcript

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

    View Slide

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

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

    View Slide

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

    View Slide

  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

    View Slide

  6. Ajustes no PostgreSQL

    View Slide

  7. View Slide

  8. Troubleshooting PostgreSQL

    View Slide

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

    View Slide

  10. Troubleshooting PostgreSQL
    Siga aquele cara…
    (fique de olho no PID
    em vermelho)

    View Slide

  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

    View Slide

  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

    View Slide

  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)

    View Slide

  14. Logs

    View Slide

  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

    View Slide

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

    View Slide

  17. Logs
    O que logar:
    • log_line_prefix
    • log_connections, log_disconnections
    • log_lock_waits
    • log_checkpoints
    • log_temp_files

    View Slide

  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

    View Slide

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

    View Slide

  20. VACUUM, ANALYZE

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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;

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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;

    View Slide

  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’

    View Slide

  34. Window Functions

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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, "%";

    View Slide

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

    View Slide

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

    View Slide

  43. PGConf Brasil 2018

    View Slide

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

    View Slide

  45. View Slide