Biggish Data with Rails and Postgres

Tips and tricks for scaling Rails apps with large databases.

Given at RubyNation 2014

Transcript

1. Biggish Data with Rails and Postgres Ben Curtis / ben@honeybadger.io / @stympy
2. Yeah, I’m cool like that
3. Setting the Stage So you want to store some data…
4. Use a Real Computer Big disks — Lots of RAM
5. Increase Read-Ahead blockdev --setra 2048 /dev/sda
6. Use a modern filesystem 2ndquadrant.com/media/pdfs/talks/RightingWrites.pdf
7. Tell PG about all that RAM github.com/gregs1104/pgtune
8. Vacuum Regularly devcenter.heroku.com/articles/heroku-postgres-database-tuning
9. Don’t dump it, ship it github.com/wal-e/wal-e
10. Some Common Problems PG, Y U SLOW?
11. $ rails console > Error.count()
12. db=# EXPLAIN (format yaml) SELECT count(*) FROM errors; QUERY PLAN -------------------------------------- - Plan: + Node Type: "Aggregate" + Total Cost: 49971.71 + Plan Rows: 1 + Plans: + - Node Type: "Seq Scan" + Relation Name: “errors" + Total Cost: 48172.96 + Plan Rows: 719496 +
13. $ rails console > user.errors.page(100)
14. db=# EXPLAIN ANALYZE SELECT id FROM errors OFFSET 500000 LIMIT 100; ! QUERY PLAN -------------------------------------- - Plan: + Node Type: "Limit" + Actual Rows: 100 + Plans: + - Node Type: "Seq Scan" + Relation Name: "errors" + Actual Rows: 500100 +
15. db=# EXPLAIN ANALYZE SELECT msg FROM errors WHERE id >= 500000 AND id < 500100; ! QUERY PLAN -------------------------------------------------------- - Plan: + Node Type: "Bitmap Heap Scan" + Relation Name: "errors" + Actual Rows: 100 + Plans: + - Node Type: "Bitmap Index Scan" + Index Name: "errors_pkey" + Total Cost: 5.42 + Actual Rows: 100 + Index Cond: "((id >= 500000) AND (id <= 500100))"
16. Long-running Migrations The only constant in life is change
17. class AddIndexToAsksActive < ActiveRecord::Migration disable_ddl_transaction! ! def change add_index :asks, :active, algorithm: :concurrently end end *Rails >= 4
18. Deadlock? Wha?
19. Too Many Locks wiki.postgresql.org/wiki/Lock_Monitoring
20. Intensive DB Queries www.postgresql.org/docs/current/static/warm-standby.html
21. Too Many DB Connections www.craigkerstiens.com/2014/05/22/on-connection-pooling
22. $ rails console > Error.where(“created_at < ‘1/1/2012’”). delete_all
23. $ rails console > Error.where(project_id: 5).delete_all
24. Partitioning for deletion & archival github.com/keithf4/pg_partman
25. Distributed & Unique IDs www.wekeroad.com/2014/05/29/a-better-id-generator- for-postgresql
26. Don’t fear the elephant
27. Thanks! Ben Curtis / bencurtis.com / @stympy speakerdeck.com/stympy