Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Thoughts About Normal and Abnormal Data (PyCon ...

Markus H
October 27, 2017

Thoughts About Normal and Abnormal Data (PyCon UK 2017)

Markus H

October 27, 2017
Tweet

More Decks by Markus H

Other Decks in Technology

Transcript

  1. @m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female

    2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
  2. @m_holtermann PersonID Name Home planet Gender 1 Padmé Naboo Female

    2 Luke Tatooine Male 3 Leia Alderaan Female 3 Leia Naboo Female
  3. @m_holtermann PersonID Planet Name 1 Naboo 2 Tatooine 3 Alderaan

    3 Naboo PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female
  4. @m_holtermann Deletion Anomalies PersonID Planet Name 1 Naboo 2 Tatooine

    3 Alderaan 3 Naboo PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female
  5. @m_holtermann PlanetID Name Water 10 Naboo 85% 11 Tatooine 1%

    12 Alderaan 78% 13 Dagobah 88% PersonID Name Gender 1 Padmé Female 2 Luke Male 3 Leia Female PersonID PlanetID 1 10 2 11 3 10 3 12
  6. Task 1: Fetch a single page SELECT * FROM page

    INNER JOIN revision ON page.page_id = revision.page_id WHERE page.slug = 'some-slug' ORDER BY revision.date DESC LIMIT 1;
  7. Task 2: Fetch all pages SELECT page.name, last_revs.date FROM page

    INNER JOIN ( SELECT revision.page_id, MAX(revision.date) date FROM revision GROUP BY revision.page_id ) last_revs ON page.page_id = last_revs.page_id;
  8. @m_holtermann Benchmark Environment • Intel i7-6600U, 2.60GHz • 8 GB

    Memory • PostgreSQL 9.6.5 • 10k pages, 6m revisions
  9. @m_holtermann Task 1: Fetch a single page Concurrent queries 10

    Pages per connection 1000 Queries per page 10 Queries total 100000
  10. Task 1: Fetch a single page SELECT * FROM page

    INNER JOIN revision ON page.last_revision_id = revision.revision_id WHERE page.slug = 'some-slug';
  11. Task 2: Fetch all pages SELECT page.name, revision.date FROM page

    INNER JOIN revision ON page.last_revision_id = revision.revision_id;