Postgres Performance for Humans by Craig Kerstiens

Transcript

1. Postgres Performance
   for Humans @craigkerstiens
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. Postgres - TLDR
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. TLDR in a quote http://www.craigkerstiens.com/2012/04/30/why-postgres/ “It’s the emacs of databases”
6. OLTP vs OLAP
7. OLTP vs OLAP Web apps
8. OLTP vs OLAP BI/Reporting
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/
11. Cache rules everything around me
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. Cache Hit Rate name | ratio ----------------+------------------------ cache hit rate | 0.99
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. 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. Rough guidelines Cache hit rate >= 99% ! Index hit rate >= 95% where on > 10,000 rows
17. Shortcuts
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. $ 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. How Data is Retrieved
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. Index Scans A-F G-L M-R S-Z G H I J K L Record 57 Record … Record …
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. Understanding Specific Query Performance
25. Understanding Query Performance ! SELECT last_name FROM employees WHERE salary >= 50000;
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. 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. 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. Rough guidelines Page response times < 100 ms Common queries < 10ms Rare queries < 100ms
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. # CREATE INDEX idx_emps ON employees (salary); Indexes!
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. 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. 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. total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) ! pg_stat_statements
36. Indexes
37. Indexes B-Tree Generalized Inverted Index (GIN) Generalized Search Tree (GIST) K Nearest Neighbors (KNN) Space Partitioned GIST (SP-GIST)
38. Indexes Which do I use?
39. BTree ! ! ! This is what you usually want
40. Generalized Inverted Index (GIN) ! ! ! Use with multiple values in 1 column Array/hStore
41. Generalized Search Tree (GIST) ! ! ! Full text search Shapes
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. More indexes
44. Indexes Conditional Functional Concurrent creation
45. Conditional > SELECT * FROM places; ! name | population ----------------------------------- ACMAR | 6055 ARAB | 13650
46. Conditional > SELECT * FROM places WHERE population > 10000; ! name | population ----------------------------------- ARAB | 13650
47. Conditional > CREATE INDEX idx_large_population ON places(name) where population > 10000; !
48. Functional > SELECT * FROM places; ! data ----------------------------------- {"city": "ACMAR", "pop": 6055}! {"city": "ARAB", "pop": 13650}!
49. > SELECT * FROM places WHERE get_numeric('pop', data) > 10000; ! data ----------------------------------- {"city": "ARAB", "pop": 13650} Functional
50. > CREATE INDEX idx_large_population ON places(get_numeric('pop', data)); ! Functional
51. Conditional and Functional > CREATE INDEX idx_large_population ON places(data) WHERE get_numeric('pop', data) > 10000; !
52. CREATE INDEX CONCURRENTLY ... ! roughly 2-3x slower Doesn’t lock table One more thing
53. hstore / JSON
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. hstore INSERT INTO users VALUES ( 1, 'craig.kerstiens@gmail.com', 'sex => "M", state => “California”', now(), now() ); !
56. SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json; JSON
57. hstore Indexes work gin gist
58. json Functional indexes work have fun
59. jsonb The world is better
60. Pooling
61. ! Application/Framework layer ! Stand alone daemon Options
62. pgbouncer pgpool PG options
63. Adding Cache
64. Replication options slony londiste bucardo pgpool ! ! wal-e barman
65. Replication options slony londiste bucardo pgpool ! ! wal-e barman
66. Backups
67. Logical pg_dump ! can be human readable, is portable
68. Physical The bytes on disk ! Base backup
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. Recap
71. OLAP ! Whole other talk Disk IO is important Order on disk is helpful (pg-reorg) MPP solutions on top of Postgres Recap
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. Questions http://www.speakerdeck.com/u/ craigkerstiens/!