Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Biggish Data with Rails and Postgres
Search
Benjamin Curtis
June 06, 2014
Technology
1
110
Biggish Data with Rails and Postgres
Tips and tricks for scaling Rails apps with large databases.
Given at RubyNation 2014
Benjamin Curtis
June 06, 2014
Tweet
Share
More Decks by Benjamin Curtis
See All by Benjamin Curtis
An Introduction to Rack
stympy
0
64
Selling to Developers: Mission Impossible?
stympy
3
970
Machine Learning Techniques
stympy
8
1.3k
Rails Caching Strategies
stympy
3
480
The Money Train
stympy
0
82
Other Decks in Technology
See All in Technology
サーバー管理しないサーバーサービスManaged DevOps Pool
kkamegawa
0
140
不動産 x AIことはじめ~データの真価を拓くために
estie
0
130
Creative UIs with Compose: DroidKaigi 2024
chrishorner
1
610
Mocking in Rust Applications
taiki45
2
420
再考 アクターモデル/ reconsider actor model
ytake
0
380
効果的なオンコール対応と障害対応
ryuichi1208
6
3.1k
タイミーのレコメンドにおける ABテストの運用
ozeshun
1
210
20240911_New_Relicダッシュボード活用例
speakerdeckfk
0
110
watsonx.ai Dojo 環境準備について
oniak3ibm
PRO
0
360
事前準備が肝!AI活用のための業務改革
layerx
PRO
1
400
Analytics-Backed App Widget Development - Served with Jetpack Glance
miyabigouji
0
630
Jetpack Compose Modifier 徹底解説 / Jetpack Compose Modifier
wiroha
0
210
Featured
See All Featured
Agile that works and the tools we love
rasmusluckow
327
20k
Build The Right Thing And Hit Your Dates
maggiecrowley
30
2.3k
Making Projects Easy
brettharned
113
5.8k
VelocityConf: Rendering Performance Case Studies
addyosmani
322
23k
Put a Button on it: Removing Barriers to Going Fast.
kastner
58
3.4k
Unsuck your backbone
ammeep
667
57k
In The Pink: A Labor of Love
frogandcode
139
22k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
41
6.5k
Atom: Resistance is Futile
akmur
261
25k
Side Projects
sachag
451
42k
Rails Girls Zürich Keynote
gr2m
93
13k
The Power of CSS Pseudo Elements
geoffreycrofte
71
5.3k
Transcript
Biggish Data with Rails and Postgres Ben Curtis /
[email protected]
/ @stympy
Yeah, I’m cool like that
Setting the Stage So you want to store some data…
Use a Real Computer Big disks — Lots of RAM
Increase Read-Ahead blockdev --setra 2048 /dev/sda
Use a modern filesystem 2ndquadrant.com/media/pdfs/talks/RightingWrites.pdf
Tell PG about all that RAM github.com/gregs1104/pgtune
Vacuum Regularly devcenter.heroku.com/articles/heroku-postgres-database-tuning
Don’t dump it, ship it github.com/wal-e/wal-e
Some Common Problems PG, Y U SLOW?
$ rails console > Error.count()
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 +
$ rails console > user.errors.page(100)
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 +
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))"
Long-running Migrations The only constant in life is change
class AddIndexToAsksActive < ActiveRecord::Migration disable_ddl_transaction! ! def change add_index :asks,
:active, algorithm: :concurrently end end *Rails >= 4
Deadlock? Wha?
Too Many Locks wiki.postgresql.org/wiki/Lock_Monitoring
Intensive DB Queries www.postgresql.org/docs/current/static/warm-standby.html
Too Many DB Connections www.craigkerstiens.com/2014/05/22/on-connection-pooling
$ rails console > Error.where(“created_at < ‘1/1/2012’”). delete_all
$ rails console > Error.where(project_id: 5).delete_all
Partitioning for deletion & archival github.com/keithf4/pg_partman
Distributed & Unique IDs www.wekeroad.com/2014/05/29/a-better-id-generator- for-postgresql
Don’t fear the elephant
Thanks! Ben Curtis / bencurtis.com / @stympy speakerdeck.com/stympy