Socorro, crash-stats.mozilla.com and Postgres

Slide 1

Slide 1 text

Postgres and You Selena Deckelmann WebEng Workweek Q1 2014

Slide 2

Slide 2 text

Stuff to cover •How Postgres ﬁts in • Migrations • Middleware, stored procedures, crontabber • How to love JSON • Writing Queries • Common Admin issues

Slide 3

Slide 3 text

processors rabbitmq webapp crontabber middleware jenkins builds failing setupdb_app.py fakedata

Slide 4

Slide 4 text

http://uncommonrose.com/ presentations/socorro-crash-ﬂow-2014/

Slide 5

Slide 5 text

socorro/external/postgresql/model.py schema socorro1 DB processor01 processor10 … sp-admin01 mware01 mware01 … socorro1.webapp socorro4.webapp … raw_crashes processed_crashes reports Pretty much all the other tables Django-specific tables webapp-django/crashstats/* models

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

socorro1 socorro3 WAL Socorro1 .dev Socorro1. stage base_backup copy Sunday noon PT streaming rep Prod socorro2 backup4 base_backup & pg_dump backup reporting1 WAL socorro-db-zeus-rw socorro-db-zeus-ro very architecture very architecture such replicas such replicas wow wow

Slide 9

Slide 9 text

Making a test database PYTHONPATH=. \ socorro/external/postgresql/setupdb_app.py \ --database_name=socorro_test --dropdb PYTHONPATH=. \ socorro/external/postgresql/setupdb_app.py \ --database_name=socorro_test --dropdb \ --fakedata --fakedata_days=2

Slide 10

Slide 10 text

What setupdb_app.py does • Connects as database superuser, creates a database, creates socorro users, disconnects • Connects as user, creates a schema based on socorro/external/postgresql/model.py • Optional: loads data using fakedata.py • Stamps alembic revision to latest

Slide 11

Slide 11 text

Running tests make test-socorro or setupdb_app.py + nose

Slide 12

Slide 12 text

Stuff to cover • How Postgres ﬁts in •Migrations • Middleware, stored procedures, crontabber • How to love JSON • Writing Queries • Common Admin issues

Slide 13

Slide 13 text

Running migrations cp config/alembic.ini-dist config/alembic.ini PYTHONPATH=. \ alembic -c config/alembic.ini revision \ --autogenerate -m “Fixes bug XX add table” alembic -c config/alembic.ini upgrade HEAD alembic -c config/alembic.ini downgrade -1 alembic -c config/alembic.ini history alembic -c config/alembic.ini current alembic -c config/alembic.ini branches

Slide 14

Slide 14 text

Important files for alembic • socorro/lib/migrations.py • load_stored_proc(op, [procfile, ... ]) • fix_permissions(op, tablename) • socorro/lib/*type.py • citexttype.CitextType • jsontype.JsonType

Slide 15

Slide 15 text

Most common issues • Failure to test migration (see: Makeﬁle) • Problem with non-linear history (two migrations inherit from same parent) • Assumptions that don’t pan out on downgrade (protip: run downgrade then upgrade after a setupdb_app.py run)

Slide 16

Slide 16 text

Stuff to cover • How Postgres ﬁts in • Migrations •Middleware, stored procedures, crontabber • How to love JSON • Writing Queries

Slide 17

Slide 17 text

Postgres DB table Internal Middleware API Django model webapp view Crontabber Job Stored procedure Crontabber Config Data from: reports_clean raw_crashes processed_crashes Data into: Brand new table! Lives in: socorro/cron/jobs Lives in: Socorro puppet module Lives in: socorro/external/ postgresql/*.py Lives in: webapp-django/ crashstats/crashstats/ model.py Lives in: webapp-django/ crashstats/crashstats/ views.py

Slide 18

Slide 18 text

Stored Procedure • socorro/external/postgresql/raw_sql/procs • update_XXX.sql and backfill_XXX.sql • see also: backfill_named_table.sql • backfill_matviews.sql • TODO: invoke a crontabber job to backfill instead (better performance) • Tests? (fakedata may invoke)

Slide 19

Slide 19 text

Adding a Postgres table • socorro/external/postgresql/models.py • socorro/external/postgresql/fakedata.py • maybe also: setupdb_app.py (but prob not) • Tests!

Slide 20

Slide 20 text

Crontabber job • socorro/cron/jobs/matview.py • Remember to check dependencies • socorro/cron/crontabber.py - DEFAULT_JOBS

Slide 21

Slide 21 text

Crontabber config • puppet: modules/socorro/files/stage/etc- socorro/crontabber.ini • puppet: modules/socorro/files/prod/etc- socorro/crontabber.ini

Slide 22

Slide 22 text

Internal middleware • socorro/external/postgresql/XXX.py • see: socorro/external/postgresql/ crashes.py for an example • Expose a route to the new endpoint

Slide 23

Slide 23 text

Django Model • And here’s where stuff gets hazy... • webapp-django/crashstats/crashstats/ models.py

Slide 24

Slide 24 text

Webapp view • webapp-django/crashstats/crashstats/ views.py

Slide 25

Slide 25 text

Problems

Slide 26

Slide 26 text

Caching conﬁg? • Has solved performance problems in the past *cough* TCBS *cough*

Slide 27

Slide 27 text

Complex middleware • Rob? :)

Slide 28

Slide 28 text

Alternatives • What if we pull out aggregation data into a different data store? • Why? Extreme Simpliﬁcation • Provide JSON aggregates, use D3

Slide 29

Slide 29 text

Stuff to cover • How Postgres ﬁts in • Migrations • Middleware, stored procedures, crontabber •How to love JSON • Writing Queries • Common Admin issues

Slide 30

Slide 30 text

Our JSON is complicated

Slide 31

Slide 31 text

{ "client_crash_date":"2014-03-24 00:05:03.000000", "hang_type":0, "dump":"====PIPE DUMP ENDS===\n", "startedDateTime":"2014-03-24 00:00:13.050209", "java_stack_trace":null, "product":"Firefox", "crashedThread":null, "cpu_info":null, "pluginVersion":null, "install_age":2765858, "distributor":null, "topmost_filenames":"", "processor_notes":"sp-processor03_phx1_mozilla_com. 5265:2012; HybridCrashProcessor; MDSW emitted no header l ines; MDSW did not identify the crashing thread; CSignatu

Slide 32

Slide 32 text

"addons":[ [ "[email protected]", "1.6.0" ], [ "{60364604-8b4c-42f4-a2ca-a76ca7b61b37}", "9.5.3.1" ], [ "[email protected]", "0.94.61" ], [ "[email protected]", "1.1.2"

Slide 33

Slide 33 text

with a as ( select processed_crash from processed_crashes limit 1 ), b as ( select json_array_elements(processed_crash->'addons') as addons from a ) select addons->>0 from b;

Slide 34

Slide 34 text

JSON operators • Extract JSON object: -> • Extract array element: ->>2 • Extract text element: ->>‘blah’ • JSON type (can be confusing cuz doesn’t automatically cast to TEXT)

Slide 35

Slide 35 text

JSON functions • json_enhancements: https://bitbucket.org/IVC-Inc/ json_enhancements

Slide 36

Slide 36 text

But, easier than reports • Big unanswered question: Can we kill the reports table? • advanced search • reports/list • update_reports_clean() • 3 reports THAT SHOULD DIE

Slide 37

Slide 37 text

Upgrading to 9.3 • Would be nice. • Not a priority. • Might just go to 9.4 in the fall......... Discuss?

Slide 38

Slide 38 text

Stuff to cover • How Postgres ﬁts in • Migrations • Middleware, stored procedures, crontabber • How to love JSON •Writing Queries • Common Admin issues

Slide 39

Slide 39 text

Protips! • Don’t ever use now()::timestamp (magical, immediate performance killer) • Run EXPLAIN, talk to a buddy • Check for unnecessary JOINs • Don’t expect local/stage performance to be similar to prod performance • Always use “WHERE date_processed ...” (more on this later!)

Slide 40

Slide 40 text

Writing queries in python • Isolate your queries in a function with the ﬁrst parameter as the connection • DO NOT PASS CURSORS AROUND

Slide 41

Slide 41 text

Always use date_processed • WHERE date_processed BETWEEN ... AND ... • Tables affected: • raw_crashes, processed_crashes • reports, reports_clean • plugins, plugins_modules

Slide 42

Slide 42 text

Avoid reports • Avoid using the reports table • Deprecation target AND usually slower than reports_clean • matview? Use raw_crashes or processed_crashes and make indexes!

Slide 43

Slide 43 text

Adding indexes to partitioned tables • see report_partition_info • Happens weekly • To add to old partitions, do “manually” in a migration based on pg_class data

Slide 44

Slide 44 text

Use EXPLAIN • Check for unnecessary JOINs (remove!) • Don’t SELECT * ever • Start with WITH • Test performance with EXPLAIN ANALYZE • ANALYZE tables

Slide 45

Slide 45 text

Adding other indexes • Must exactly match predicate! • Best practice: Create in a transaction and test exactly the query you want to use the index to verify your index is correct

Slide 46

Slide 46 text

INSERTs • ALWAYS EXPLICITLY NAME COLUMNS

Slide 47

Slide 47 text

Altering tables • Never specify a DEFAULT in a CREATE or ALTER that creates a column on an existing table • Drop children before parents • Never LOCK EXCLUSIVE on parents unless you want to have a downtime • DROPs and ALTERs on children might deadlock with processors (they retry, NBD)

Slide 48

Slide 48 text

Stuff to cover • How Postgres ﬁts in • Migrations • Middleware, stored procedures, crontabber • How to love JSON • Writing Queries •Common Admin issues

Slide 49

Slide 49 text

High load on master • Check if a backup is running (OOPS) SOLUTION: kill backup LONG TERM: Investigate why backup was running against master, correct conﬁguration/process documents

Slide 50

Slide 50 text

High load on master • Check if load is coming from middleware (cacheable query?) SOLUTION: kill middleware processes, sometimes have to restart middleware LONG TERM: Cache a query or refactor SQL

Slide 51

Slide 51 text

High load on master • Check the logs :) (checkpoints, temp ﬁles) • Check for a Suspicious Process or Processes (Long running, memory consuming)

Slide 52

Slide 52 text

Maintenance of tables • We now drop old partitions without an exclusive lock on the parent • This is mostly safe -- might screw up READ ONLY queries looking at the data at the time of the drops • Worth it, because otherwise we need downtime windows • TODO: Now can be automated!

Slide 53

Slide 53 text

Maintenance of tables • Probably could be doing more • Probably not worth the time given that we drop old partitions • Newer Postgres will automatically FREEZE tables, reducing overhead of periodic VACUUMs for transaction wraparound