Share
Embed
Socorro, crash-stats.mozilla.com and Postgres
A brain dump of all the stuff related to Postgres and crash-stats.mozilla.com
Selena Deckelmann
March 25, 2014
Transcript
53 slides
-
Stuff to cover •How Postgres fits in • Migrations •
Middleware, stored procedures, crontabber • How to love JSON • Writing Queries • Common Admin issues -
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 -
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 -
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 -
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 -
Stuff to cover • How Postgres fits in •Migrations •
Middleware, stored procedures, crontabber • How to love JSON • Writing Queries • Common Admin issues -
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 -
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 -
Most common issues • Failure to test migration (see: Makefile)
• 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) -
Stuff to cover • How Postgres fits in • Migrations
•Middleware, stored procedures, crontabber • How to love JSON • Writing Queries -
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 -
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) -
Adding a Postgres table • socorro/external/postgresql/models.py • socorro/external/postgresql/fakedata.py • maybe
also: setupdb_app.py (but prob not) • Tests! -
Crontabber job • socorro/cron/jobs/matview.py • Remember to check dependencies •
socorro/cron/crontabber.py - DEFAULT_JOBS -
Internal middleware • socorro/external/postgresql/XXX.py • see: socorro/external/postgresql/ crashes.py for an
example • Expose a route to the new endpoint -
Django Model • And here’s where stuff gets hazy... •
webapp-django/crashstats/crashstats/ models.py -
Caching config? • Has solved performance problems in the past
*cough* TCBS *cough* -
Alternatives • What if we pull out aggregation data into
a different data store? • Why? Extreme Simplification • Provide JSON aggregates, use D3 -
Stuff to cover • How Postgres fits in • Migrations
• Middleware, stored procedures, crontabber •How to love JSON • Writing Queries • Common Admin issues -
{ "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 -
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; -
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) -
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 -
Upgrading to 9.3 • Would be nice. • Not a
priority. • Might just go to 9.4 in the fall......... Discuss? -
Stuff to cover • How Postgres fits in • Migrations
• Middleware, stored procedures, crontabber • How to love JSON •Writing Queries • Common Admin issues -
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!) -
Writing queries in python • Isolate your queries in a
function with the first parameter as the connection • DO NOT PASS CURSORS AROUND -
Always use date_processed • WHERE date_processed BETWEEN ... AND ...
• Tables affected: • raw_crashes, processed_crashes • reports, reports_clean • plugins, plugins_modules -
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! -
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 -
Use EXPLAIN • Check for unnecessary JOINs (remove!) • Don’t
SELECT * ever • Start with WITH • Test performance with EXPLAIN ANALYZE • ANALYZE tables -
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 -
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) -
Stuff to cover • How Postgres fits in • Migrations
• Middleware, stored procedures, crontabber • How to love JSON • Writing Queries •Common Admin issues -
High load on master • Check if a backup is
running (OOPS) SOLUTION: kill backup LONG TERM: Investigate why backup was running against master, correct configuration/process documents -
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 -
High load on master • Check the logs :) (checkpoints,
temp files) • Check for a Suspicious Process or Processes (Long running, memory consuming) -
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! -
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