Biggish Data with Rails and Postgres

Transcript

1. Biggish Data with Rails and Postgres Ben Curtis / [email protected]

   / @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