Postgres Performance for Humans by Craig Kerstiens

Transcript

1. 1.

   Postgres Performance
   for Humans @craigkerstiens

2. 2.

   Shameless plugs http://www.postgresweekly.com! http://www.craigkerstiens.com! http://www.postgresguide.com! http://www.postgresapp.com! ! http://www.heroku.com/postgres !

3. 3.

   Postgres - TLDR

4. 4.

   Postgres - TLDR Datatypes Conditional Indexes Transactional DDL Foreign Data

   Wrappers Concurrent Index Creation Extensions Common Table Expressions Fast Column Addition Listen/Notify Table Inheritance Per Transaction sync replication Window functions NoSQL inside SQL Momentum
5. 5.

   TLDR in a quote http://www.craigkerstiens.com/2012/04/30/why-postgres/ “It’s the emacs of databases”

6. 6.

   OLTP vs OLAP

7. 7.

   OLTP vs OLAP Web apps

8. 8.

   OLTP vs OLAP BI/Reporting

9. 9.

   Postgres Setup/Config On Amazon Use Heroku OR ‘postgresql when its

   not your dayjob’ Other clouds ‘postgresql when its not your dayjob’ Real hardware High performance PostgreSQL ! http://thebuild.com/blog/2012/06/04/postgresql-when-its-not-your-job-at-djangocon-europe/
10. 10.

    None
11. 11.

    Cache rules everything around me

12. 12.

    Cache Hit Rate SELECT 'index hit rate' as name, (sum(idx_blks_hit)

    - sum(idx_blks_read)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT 'cache hit rate' as name, case sum(idx_blks_hit) when 0 then 'NaN'::numeric else to_char((sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit + idx_blks_read), '99.99')::numeric end as ratio FROM pg_statio_user_indexes)
13. 13.

    Cache Hit Rate name | ratio ----------------+------------------------ cache hit rate

    | 0.99
14. 14.

    Index Hit Rate SELECT relname, 100 * idx_scan / (seq_scan

    + idx_scan), n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC; ! ! !
15. 15.

    Index Hit Rate relname | percent_of_times_index_used | rows_in_table ---------------------+-----------------------------+--------------- events

    | 0 | 669917 app_infos_user_info | 0 | 198218 app_infos | 50 | 175640 user_info | 3 | 46718 rollouts | 0 | 34078 favorites | 0 | 3059 schema_migrations | 0 | 2 authorizations | 0 | 0 delayed_jobs | 23 | 0
16. 16.

    Rough guidelines Cache hit rate >= 99% ! Index hit

    rate >= 95% where on > 10,000 rows
17. 17.

    Shortcuts

18. 18.

    $ cat ~/.psqlrc ! \set ON_ERROR_ROLLBACK interactive ! -- automatically

    switch between extended and normal \x auto ! -- always show how long a query takes \timing ! \set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;' ! psql
19. 19.

    $ cat ~/.psqlrc ! \set ON_ERROR_ROLLBACK interactive ! -- automatically

    switch between extended and normal \x auto ! -- always show how long a query takes \timing ! \set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;' ! psql
20. 20.

    How Data is Retrieved

21. 21.

    Sequential Scanning Record 1 Record 2 Record 3 Record 4

    Record 5 Record … Record 1 Record 2 Record 3 Record 4 Record 5 Record …
22. 22.

    Index Scans A-F G-L M-R S-Z G H I J

    K L Record 57 Record … Record …
23. 23.

    Sequential Scans Good for large reports ! Computing over lots

    of data (1k + rows) Index Scans Good for small results ! Most common queries in your app
24. 24.

    Understanding Specific Query Performance

25. 25.

    Understanding Query Performance ! SELECT last_name FROM employees WHERE salary

    >= 50000;
26. 26.

    Explain # EXPLAIN SELECT last_name FROM employees WHERE salary >=

    50000; ! QUERY PLAN -------------------------------------------------- Seq Scan on employees (cost=0.00..35811.00 rows=1 width=6) Filter: (salary >= 50000) (3 rows)
27. 27.

    Explain # EXPLAIN SELECT last_name FROM employees WHERE salary >=

    50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees width=6) Filter: (salary >= 50000) (3 rows) startup time max time rows return (cost=0.00..35811.00 rows=1
28. 28.

    Explain Analyze # EXPLAIN ANALYZE SELECT last_name FROM employees WHERE

    salary >= 50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees (cost=0.00..35811.00 rows=1 width=6) (actual time=2.401..295.247 rows=1428 loops=1) Filter: (salary >= 50000) Total runtime: 295.379 (3 rows) ! Filter: (salary >= 50000) (3 rows) startup time max time rows return actual time 2.401..295.247 rows=1428 295.379
29. 29.

    Rough guidelines Page response times < 100 ms Common queries

    < 10ms Rare queries < 100ms
30. 30.

    Explain Analyze # EXPLAIN ANALYZE SELECT last_name FROM employees WHERE

    salary >= 50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees (cost=0.00..35811.00 rows=1 width=6) (actual time=2.401..295.247 rows=1428 loops=1) Filter: (salary >= 50000) Total runtime: 295.379 (3 rows) ! Filter: (salary >= 50000) (3 rows) startup time max time rows return actual time 2.401..295.247 rows=1428 295.379
31. 31.

    # CREATE INDEX idx_emps ON employees (salary); Indexes!

32. 32.

    Indexes! EXPLAIN ANALYZE SELECT last_name FROM employees WHERE salary >=

    50000; QUERY PLAN -------------------------------------------------- Index Scan using idx_emps on employees (cost=0.00..8.49 rows=1 width=6) (actual time = 0.047..1.603 rows=1428 loops=1) Index Cond: (salary >= 50000) Total runtime: 1.771 ms (3 rows)
33. 33.

    pg_stat_statements $ select * from pg_stat_statements where query ~ 'from

    users where email'; ! ! userid │ 16384 dbid │ 16388 query │ select * from users where email = ?; calls │ 2 total_time │ 0.000268 rows │ 2 shared_blks_hit │ 16 shared_blks_read │ 0 shared_blks_dirtied │ 0 shared_blks_written │ 0 local_blks_hit │ 0 local_blks_read │ 0 local_blks_dirtied │ 0 local_blks_written │ 0 ...
34. 34.

    SELECT (total_time / 1000 / 60) as total, (total_time/calls) as

    avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100; pg_stat_statements
35. 35.

    total | avg | query --------+--------+------------------------- 295.76 | 10.13 |

    SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) ! pg_stat_statements
36. 36.

    Indexes

37. 37.

    Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST)

    K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST)
38. 38.

    Indexes Which do I use?

39. 39.

    BTree ! ! ! This is what you usually want

40. 40.

    Generalized Inverted Index (GIN) ! ! ! Use with multiple

    values in 1 column Array/hStore
41. 41.

    Generalized Search Tree (GIST) ! ! ! Full text search

    Shapes
42. 42.

    Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST)

    K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST) VODKA (Coming soon)
43. 43.

    More indexes

44. 44.

    Indexes Conditional Functional Concurrent creation

45. 45.

    Conditional > SELECT * FROM places; ! name | population

    ----------------------------------- ACMAR | 6055 ARAB | 13650
46. 46.

    Conditional > SELECT * FROM places WHERE population > 10000;

    ! name | population ----------------------------------- ARAB | 13650
47. 47.

    Conditional > CREATE INDEX idx_large_population ON places(name) where population >

    10000; !
48. 48.

    Functional > SELECT * FROM places; ! data ----------------------------------- {"city":

    "ACMAR", "pop": 6055}! {"city": "ARAB", "pop": 13650}!
49. 49.

    > SELECT * FROM places WHERE get_numeric('pop', data) > 10000;

    ! data ----------------------------------- {"city": "ARAB", "pop": 13650} Functional
50. 50.

    > CREATE INDEX idx_large_population ON places(get_numeric('pop', data)); ! Functional

51. 51.

    Conditional and Functional > CREATE INDEX idx_large_population ON places(data) WHERE

    get_numeric('pop', data) > 10000; !
52. 52.

    CREATE INDEX CONCURRENTLY ... ! roughly 2-3x slower Doesn’t lock

    table One more thing
53. 53.

    hstore / JSON

54. 54.

    hstore CREATE EXTENSION hstore; CREATE TABLE users ( id integer

    NOT NULL, email character varying(255), data hstore, created_at timestamp without time zone, last_login timestamp without time zone );
55. 55.

    hstore INSERT INTO users VALUES ( 1, 'craig.kerstiens@gmail.com', 'sex =>

    "M", state => “California”', now(), now() ); !
56. 56.

    SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON

57. 57.

    hstore Indexes work gin gist

58. 58.

    json Functional indexes work have fun

59. 59.

    jsonb The world is better

60. 60.

    Pooling

61. 61.

    ! Application/Framework layer ! Stand alone daemon Options

62. 62.

    pgbouncer pgpool PG options

63. 63.

    Adding Cache

64. 64.

    Replication options slony londiste bucardo pgpool ! ! wal-e barman

65. 65.

    Replication options slony londiste bucardo pgpool ! ! wal-e barman

66. 66.

    Backups

67. 67.

    Logical pg_dump ! can be human readable, is portable

68. 68.

    Physical The bytes on disk ! Base backup

69. 69.

    Logical Good across architectures Good for portability ! Has load

    on DB ! Works < 50 GB Physical More initial setup Less portability ! Limited load on system ! Use above 50 GB
70. 70.

    Recap

71. 71.

    OLAP ! Whole other talk Disk IO is important Order

    on disk is helpful (pg-reorg) MPP solutions on top of Postgres Recap
72. 72.

    OLTP (webapps) Ensure bulk of data is cache Optimize overall

    query load with pg_stat_statements Efficient use of indexes When cache sucks, throw more at it Recap
73. 73.

    Questions http://www.speakerdeck.com/u/ craigkerstiens/!