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
58
Selling to Developers: Mission Impossible?
stympy
3
910
Machine Learning Techniques
stympy
8
1.2k
Rails Caching Strategies
stympy
3
470
The Money Train
stympy
0
81
Other Decks in Technology
See All in Technology
最近たまに見かけるTiDBってなんだ? - Findy
pingcap0315
2
580
0→1開発における技術選定において一番大切なこと
bicstone
1
320
AWS を使う上で知っておきたいオンプレミス知識/aws-on-premise-essentials
emiki
1
4.2k
マルチアカウント環境への発見的統制の導入
ch1aki
1
1.3k
ここが嬉しいABAC ここが辛いよABAC #再解説+補足編
masahirokawahara
0
210
OpenTelemetry を使ったトレースエグザンプラーの活用 / otel-trace-exemplar
k6s4i53rx
2
630
エンタープライズ環境下での Active Directory の運用 TIPS
tamaiyutaro
1
1.5k
小さな開発会社がWebサービスを作る理由
polidog
PRO
1
150
PHP"オレ"カンファレンスの告知
ysknsid25
0
360
SREとその組織類型
tatsuo48
8
1.5k
[PlatformCon 24] Platform Orchestrators: The Missing Middle of Internal Developer Platforms?
danielbryantuk
1
180
アプリがつくるNOT A HOTELブランド
hokuts
1
450
Featured
See All Featured
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
1
1.3k
Producing Creativity
orderedlist
PRO
336
39k
Building Effective Engineering Teams - LeadDev
addyosmani
27
1.8k
Writing Fast Ruby
sferik
620
60k
The Mythical Team-Month
searls
215
42k
What’s in a name? Adding method to the madness
productmarketing
PRO
15
2.6k
Intergalactic Javascript Robots from Outer Space
tanoku
266
26k
Git: the NoSQL Database
bkeepers
PRO
422
63k
Creatively Recalculating Your Daily Design Routine
revolveconf
209
11k
Practical Orchestrator
shlominoach
181
9.7k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
501
140k
Fontdeck: Realign not Redesign
paulrobertlloyd
76
4.9k
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