Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Demystifing Postgres - Heroku Edition

3c568ede9e2cb4e22e25bb3362186a5c?s=47 zlu
September 08, 2014

Demystifing Postgres - Heroku Edition

Presentation given to SocialChorus Engineering

3c568ede9e2cb4e22e25bb3362186a5c?s=128

zlu

September 08, 2014
Tweet

Transcript

  1. Demystifying Postgres Heroku Edition

  2. Requirements • heroku pg-extras • newrelic (newrelic_rpm gem, newrelic.yml) •

    librato (enable heroku runtime metrics)
  3. Know Thy Database • Go to Heroku app facebook-envoy-production •

    Standard0 Plan: • Cache - 1GB (cache for index, table, and system) • Storage - 64 GB • Connection Limit - 120 (causes memory as well)
  4. DB Usage • Storage • pg:info - Table size: 6.5

    GB • Cache • Caches query results, query plan, and relations • Cache hit should be over 99% • pg:cache-hit table hit rate | 0.93700270695348766263 NOT IDEAL index hit rate | 0.76517846526624000529 AWFUL
  5. Fixing Cache • Potential Cause • Too much data (table

    and index) • Unused indexes • Bloat factor (wasted space from deletion/update/failed transactions) - should be < 10 • Fix and Challenge • Reduce unnecessary storage • Increase cache • Cache storage is expensive • Decide cache size is an art, must be done on production system
  6. Fix Storage • Identify unless data to delete (migration/code) •

    pg:table-size: failures table | 5832 MB • pg:index-size: failures_pkey | 89 MB • Has cache-hit improved? If not, increase storage • Upgrade heroku db to the next tier (standard2) • Overall index hit rate: 0.9865709375927209 • Overall table hit rate: 0.99908830338836100280
  7. Indexing and Scanning • Index scan v.s. sequential scan •

    Postgres query optimizer decides the most optimized way, based on cache, how to scan. • It’s not always good to perform index scan • Index scan requires more IO: reading index, reading table • Seq scan is good when result set is large (10% of table)
  8. Index Types • Balance-Tree (BTree) • Default for pg •

    balanced structure, good for most of cases (such as such as equality and ranges) and all data types • Partial • add_index :statuses, :liked, name: "index_statuses_on_disliked", where: "(liked IS FALSE)” • Faster since less storage is limited by where clause • Commonly applied to boolean fields in where clause • Expression • Useful for queries that match some function of data such as case-insensitive match of login email • create index users_lower_email on users(lower(email)) • Multi-column • Combining multiple columns (foo, bar), used for where foo = ‘fooz’ and bar = ‘baz'
  9. Identify Slow Query • pg:outliers or look at heroku database

    panel • Slowest query for facebook-envoy-production qry: SELECT COUNT(*) FROM "follows" WHERE "follows"."account_id" = ? AND "follows"."active" = ? exec_time: 467:27:38.383773 prop_exec_time: 76.3% ncalls: 5,469,425 sync_io_time: 55:44:28.64694 ! sync_io_time should be 0, meaning no wait.
  10. Explain Analyze • Download production data and experiment locally •

    rake gta:heroku:db:pull[production] • psql -d facebook_envoy_development ! explain analyze SELECT COUNT(*) FROM "follows" WHERE "follows"."account_id" = 1 AND "follows"."active" = true; Aggregate (cost=41274.28..41274.28 rows=1 width=0) (actual time=473.756..473.758 rows=1 loops= 1) -> Index Scan using index_follows_on_follower_uid_and_account_id on follows (cost=0.09..41 274.04 rows=466 width=0) (actual time=3.339..473.294 rows=333 loops=1 Index Cond: (account_id = 19876) Filter: active Rows Removed by Filter: 1 Total runtime: 474.043 ms (6 rows)
  11. Choosing Right Index • Partial or Multi-column? ! Aggregate (cost=14.93..14.94

    rows=1 width=0) (actual time=0.188..0.189 rows=1 loops=1) -> Index Only Scan using index_follows_on_account_id_and_active on follows (cost=0.09..14.93 ro ws=8 width=0) (actual time=0.162..0.165 rows=1 loops=1) Index Cond: ((account_id = 19876) AND (active = true)) Filter: (NOT active) Heap Fetches: 1 Total runtime: 0.375 ms (6 rows)
  12. Filtering explain analyze SELECT "hashtags".* FROM "hashtags" INNER JOIN "post_hashtags"

    ON "hashtags"."id" = "post_hashtags"."hashtag_id" WHERE "post_hashtags"."post _id" = 12345; ! Nested Loop (cost=0.06..4899.39 rows=26 width=20) (actual time=24.602..53.540 rows=33 loops=1) -> Seq Scan on post_hashtags (cost=0.00..4833.72 rows=26 width=4) (actual time=24.571..53.416 rows=33 loops=1 ) Filter: (post_id = 12345) Rows Removed by Filter: 542631 -> Index Scan using hashtags_pkey on hashtags (cost=0.06..2.52 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=33) Index Cond: (id = post_hashtags.hashtag_id) Total runtime: 53.618 ms (7 rows)
  13. Large Filter • Large number of rows being filtered is

    bad, it means many rows have been read into memory then abandoned by the filter. • Replace seq scan with index scan
  14. Adding Index Index Scan using index_post_hashtags_on_post_id on post_hashtags (cost=0.08..4.20 rows=26

    width=4) (actual time=0.044..0.068 rows=33 loops=1) Index Cond: (post_id = 12345) -> Hash (cost=37.27..37.27 rows=4091 width=20) (actual time=1.935..1.935 rows=4091 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 197kB -> Seq Scan on hashtags (cost=0.00..37.27 rows=4091 width=20) (actual time=0.008..0.799 rows=4091 loops=1) ! Total runtime: 2.122 ms (8 rows)
  15. More Optimization • Or when do you stop optimization? •

    Building and rebuilding index has a time cost (sync_io_time) and space cost • If the query is performed < 2ms, it’s reasonable and we can stop optimizing it. • N + 1 queries - reducing throughput
  16. Review • Cache-hit (>99%) • Query plan (<2ms) • Choosing

    right index type • Reducing filtering • Seq v.s. index scan • N + 1 queries • Always use ‘explain analyze’ on production data locally • Sanity-check: observe improvements with newrelic and librato
  17. Newrelic

  18. Librato

  19. RabbitMq

  20. RabbitMq

  21. More Tips • Un-used index: slow down db updates/deletes/ inserts

    • pg:diagnose ! ! ! YELLOW: Indexes Reason Index Index Scan Pct Scans Per Write Index Size Table Size ---------------------- ------------------------------------------- -------------- --------------- ---------- ---------- Low Scans, High Writes public.follows::index_follows_on_account_id 0.00 0.00 67 MB 274 MB !
  22. Good Index, Bad Index • pg:index-usage: percent of times index

    is used
  23. Bloat and Vacuum • Bloated table • Auto/Manual vacuum •

    Setting proper threshold for auto-vacuuming
  24. DB Connections • Database has limited connections. • Standard0 has

    120 • Each connection costs 7MB memory (part of our allocation) • pg:info shows current open connections • linksmith fail to close connections caused linktivator db connection going up to 495. • Temporary fix is to restart linksmith. • Permanent fix is to rescue and close db connections. • More Permanent fix is to shutdown linksmith in favor of bit.ly
  25. Reminders • Pick at least standard0 ($50) for production database

    • Always choose pt-backups with 1-month data retention • Think about add index when writing joins, use explain analyze compare the results before/after against production data locally • Turn up mq workers when db optimization is completed, monitor db load making sure it’s <=1 • Turn up scheduler frequency (without getting into rate limit) for more responsive audience engagement data (can we achieve 4hr delay instead of 24hr)