Biggish Data with Rails and Postgres

Transcript

1. 1.

   Biggish Data with Rails and Postgres Ben Curtis / ben@honeybadger.io

   / @stympy
2. 2.

   Yeah, I’m cool like that

3. 3.

   Setting the Stage So you want to store some data…

4. 4.

   Use a Real Computer Big disks — Lots of RAM

5. 5.

   Increase Read-Ahead blockdev --setra 2048 /dev/sda

6. 6.

   Use a modern filesystem 2ndquadrant.com/media/pdfs/talks/RightingWrites.pdf

7. 7.

   Tell PG about all that RAM github.com/gregs1104/pgtune

8. 8.

   Vacuum Regularly devcenter.heroku.com/articles/heroku-postgres-database-tuning

9. 9.

   Don’t dump it, ship it github.com/wal-e/wal-e

10. 10.

    Some Common Problems PG, Y U SLOW?

11. 11.

    $ rails console > Error.count()

12. 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. 13.

    $ rails console > user.errors.page(100)

14. 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. 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. 16.

    Long-running Migrations The only constant in life is change

17. 17.

    class AddIndexToAsksActive < ActiveRecord::Migration disable_ddl_transaction! ! def change add_index :asks,

    :active, algorithm: :concurrently end end *Rails >= 4
18. 18.

    Deadlock? Wha?

19. 19.

    Too Many Locks wiki.postgresql.org/wiki/Lock_Monitoring

20. 20.

    Intensive DB Queries www.postgresql.org/docs/current/static/warm-standby.html

21. 21.

    Too Many DB Connections www.craigkerstiens.com/2014/05/22/on-connection-pooling

22. 22.

    $ rails console > Error.where(“created_at < ‘1/1/2012’”). delete_all

23. 23.

    $ rails console > Error.where(project_id: 5).delete_all

24. 24.

    Partitioning for deletion & archival github.com/keithf4/pg_partman

25. 25.

    Distributed & Unique IDs www.wekeroad.com/2014/05/29/a-better-id-generator- for-postgresql

26. 26.

    Don’t fear the elephant

27. 27.

    Thanks! Ben Curtis / bencurtis.com / @stympy speakerdeck.com/stympy