12-step program for scaling web application on postgresql

Upload: yoonghm

Post on 07-Feb-2018

220 views

Category:

Documents


0 download

TRANSCRIPT

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    1/101

    Proprietary andConfidential

    Konstantin GredeskoulCTO, Wanelo.com

    12-Step Program for Scaling Web

    Applications on PostgreSQL

    @kig

    @kigster

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    2/101

    Proprietary and

    What does it mean, to scale on top o

    PostgreSQL?

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    3/101

    Proprietary and

    And why should you care?

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    4/101

    Proprietary and

    Scalingmeans supporting morework load concurrently, where work

    is ofen interchanged with users

    But why on PostgreSQL?

    Because NoNoSQL is hawt! (again)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    5/101

    Proprietary and

    Relational databasesare great atsupporting constant change insofware

    They are not as great in auto

    scaling, like RIAK or Cassandra

    So the choice critically dependsonwhat you are trying to build

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    6/101

    Proprietary and

    Huge majority o applications arerepresented well by the relational

    model

    So i I need to build a new productor a service, my deault choicewould be PostgreSQL or criticaldata, + whatever else as needed

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    7/101

    Proprietary and

    This presentation is a walk-throughfilled with practical solutions

    Its based on a story o scalingwanelo.comto sustain 10s o thousand

    concurrent users, 3k req/sec

    But lets explore the applicationto learn a bit about waneloor our scalability journey

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    8/101

    Proprietary and

    Founded in 2010, Wanelo (wah-nee-loh, romWant, Need, Love) is a community and a social

    network for all of the world's shopping.

    Wanelo is a home to 12Mproducts, millionsousers, 200K+stores, and products on Wanelo

    have been savedinto collectionsover 2B times

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    9/101

    move ast with product development scale as needed, stay ahead o the curve keep overall costs low but spend where it matters automate everything avoid reinventing the wheel learn as we go remain in control o our inrastructure

    Early on we wanted to:

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    10/101

    Heroku or Not?

    Proprietary and

    Assuming we want ull control o our

    application layer, places like Heroku arent agreat fit

    But Heroku can be a great place to start.Itall depends on the size and complexity o theapp we are building.

    Ours would have been cost prohibitive.

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    11/101

    Foundations of web apps

    Proprietary and

    app server (we use unicorn) scalable web server in ront (we use nginx) database (we use postgresql)

    hosting environment (we useJoyentCloud)

    deployment tools (capistrano)

    server configuration tools (we use chef)

    programming language + ramework (RoR)

    many others, such as monitoring, alerting

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    12/101

    Lets review Basic Web App

    Proprietary and

    /var/pgsql/data

    incominghttp

    PostgreSQLServer

    /home/user/app/current/public

    nginx Unicorn / PassengerRuby VM

    N x UnicornsRuby VM

    no redundancy, no caching (yet)

    can only process N concurrent requests nginx will serve static assets, deal with slow clients

    web sessions probably in the DB or cookie

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    13/101

    First optimizations:cheap early on, well worth it

    Proprietary and

    Personalization via AJAX, so controller actionscan be cached entirely using caches_action

    Page returned unpersonalized, additional AJAX

    request loads personalization

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    14/101

    A few more basic performancetweaks that go a long way

    Proprietary and

    Install 2+ memcachedservers or caching anduse Dalli gem to connect to it or redundancy

    Switch to memcached-based web sessions. Usesessions sparingly, assume transient nature

    Setup CDNor asset_host and any usergenerated content. We use astly.com

    Redis is also an option, but I preer memcachedor redundancy

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    15/101

    Proprietary and

    browser PostgreSQL

    Server

    /home/user/app/current/public

    nginx Unicorn / Passenger

    Ruby VM

    N x Unicorns

    Ruby VM

    memcachedCDN

    cache images, JS

    Caching goes a long way

    geo distribute and cache your UGC and CSS/JS assets

    cache html and serialize objects in memcached can increase TTL to alleviate load, i traffic spikes

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    16/101

    Proprietary and

    Adding basic redundancy

    Multiple app serversrequire haproxy

    between nginx and unicorn

    Multiple long-running tasks (such as

    posting to Facebook or Twitter) requirebackground job processing ramework

    Multiple load balancersrequire DNS

    round robin and short TTL (dyn.com)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    17/101

    Proprietary and

    PostgreSQL

    Unicorn / Passenger

    Ruby VM (times N)haproxy

    incoming http

    DNS round robin

    or failover / HA solution

    nginx

    memcached

    redis

    CDN

    cache images, JS

    Load Balancers

    App Servers

    single DBObject Store

    User Generated

    Content

    Sidekiq / Resque

    Background WorkersData storesTransient to

    Permanent

    this architecture can horizontally scale up as

    ar the databaseat its center

    every other component can be scaled byadding more of it, to handle more traffic

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    18/101

    Proprietary and

    As long as we can scale the data

    store on the backend, we can scalethe app!

    Mostly :)At some point we may hit a limit on TCP/IPnetwork throughput, # o connections, butthis is at a whole another scale level

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    19/101

    The traffic keeps climbing

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    20/101

    Performance limits are near

    Proprietary and

    First signs o perormance problems start creeping up

    Symptoms o readscalability problems

    Pages load slowly or timeout

    Users are getting 503 Service Unavailable

    Database is slammed (very high CPU or read IO)

    Symptoms o writescalability problems

    Database write IO is maxed out, CPU is not

    Update operations are waiting on each other, piling up

    Application locks up, timeouts Replicas are not catching up

    Some pages load (cached?), some dont

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    21/101

    Proprietary and

    Both situations may easily result indowntime

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    22/101

    Proprietary and

    Even though we

    achieved 99.99% uptimein 2013, in 2014 we hada couple shortdowntimes caused by

    overloaded replica thatlasted around 5 minutes.

    But users quicklynotice

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    23/101

    Proprietary and

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    24/101

    Proprietary and

    Perhaps not :)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    25/101

    Proprietary and

    Common patterns or scaling high traffic webapplications, based on wanelo.com

    12-Step Programor curing your dependency on slow application latency

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    26/101

    Proprietary and

    For small / ast HTTP services, 10-12ms or lower

    I your app is high traffic (100K+ RPM) Irecommend 80ms or lower

    Whats a good latency?

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    27/101

    Proprietary and

    RubyVM (30ms) + Garbage collection (6ms) is CPUburn, easy to scale by adding more app servers

    Web services + Solr (25ms), memcached (15ms),database (6ms) are all waiting on IO

    CPU burn vs Waiting on IO?

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    28/101

    Proprietary and

    Step 1:

    Add More Cache!

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    29/101

    Moar Cache!!!

    Proprietary and

    Anything that can be cached, should be

    Cache hit = many database hits avoided

    Hit rate o 17% still saves DB hits

    We can cache many types o things

    Cache is cheap and ast (memcached)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    30/101

    Cache many types of things

    Proprietary and

    caches_actionin controllers is very effective

    fragmentcaches o reusable widgets

    we use gem Compositoror JSON API. We cacheserialized object ragments, grab them rom

    memcached using multi_getand merge them

    Shopiy open sourced IdentityCache, whichcaches AR models, so you can Product.etch(id)

    https://github.com/wanelo/compositorhttps://github.com/Shopiy/identity_cache

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    31/101

    But Caching has its issues

    Proprietary and

    Expiring cache is not easy

    CacheSweepersin Rails help

    We ound ourselves doing 4000 memcacheddeletes in a single request!

    Could deer expiring caches to background jobs,or use TTL i possible

    But we can cache even outside o our app:we cache JSON API responses usingCDN(astly.com)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    32/101

    Proprietary and

    Step 2:

    Optimize SQL

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    33/101

    SQL Optimization Find slow SQL(>100ms) and either remove it, cache

    the hell out o it, or fix/rewrite the query

    Enable slow query login postgresql.con:

    pg_stat_statementsis an invaluable contrib module:

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    34/101

    Fixing Slow Query

    Proprietary and

    Run explain plan to understand how DB runs the query

    Are there adequate indexes or the query? Is the database usingappropriate index? Has the table been recently analyzed?

    Can a complex join be simplified into a subselect?

    Can this query use an index-only scan? Can order by column be added to the index?

    pg_stat_user_indexesand pg_stat_user_tables or seq scans,

    unused indexes, cache ino

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    35/101

    SQL Optimization, ctd

    Proprietary and

    Instrumentation sofware such as NewRelic shows slow queries, withexplain plans, and time consuming transactions

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    36/101

    SQL Optimization: Example

    Proprietary and

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    37/101

    Proprietary and

    One day, I noticed lots of temp filescreated in the postgres.log

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    38/101

    Proprietary and

    Lets run this query

    This join takes a whole second to return :(

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    39/101

    Proprietary and

    Follows table

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    40/101

    Proprietary and

    Stories table

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    41/101

    Proprietary and

    So our index is partial, only on state = active

    So this query is a ull table scan

    But there stateisnt used in the query, a bug?

    Lets add state = active

    It was meant to be there anyway

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    42/101

    Proprietary and

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    43/101

    Proprietary and

    Step 3:Upgrade Hardware and RAM

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    44/101

    Hardware + RAM

    Proprietary and

    Sounds obvious, but better or aster hardware is anobvious choice when scaling out

    Large RAM will be used as file system cache

    On Joyents SmartOS ARC FS cache is very effective

    should be set to 25% o RAM or 12GB,

    whichever is smaller

    Using ast SSD disk array can make a huge difference

    Joyents native 16-disk RAID managed by ZFS insteado controller provides excellent perormance

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    45/101

    Hardware in the cloud

    Proprietary and

    SSD offerings rom Joyent and AWS

    Joyents max SSD node $12.9/hr

    AWS max SSD node $6.8/hr

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    46/101

    So whos better?

    Proprietary and

    JOYENT

    16 SSD drives: RAID10 + 2

    SSD Make: DCS3700

    CPU: E5-26902.9GHz

    AWS

    8 SSD drives

    SSD Make: ?

    CPU: E5-26702.6Ghz

    Perhaps you get what you pay or afer all.

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    47/101

    Proprietary and

    Step 4:Scale Reads by Replication

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    48/101

    Scale Reads by Replication

    Proprietary and

    postgresql.con (both master & replica)

    These settings have been tuned or SmartOS and ourapplication requirements (thanks PGExperts!)

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    49/101

    How to distribute reads?

    Proprietary and

    Some people have success using this setup or reads:app haproxy pgBouncer replica

    pgBouncer replica

    Id like to try this method eventually, but we choose to

    deal with distributing read traffic at the application level

    We tried many ruby-based solutions that claimed to dothis well, but many werent production ready

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    50/101

    Proprietary and

    Makarais a ruby gem rom

    TaskRabbit that we portedrom MySQL to PostgreSQLor sending reads to replicas

    Was the simplest library tounderstand, and port to PG

    Worked in the multi-threadedenvironment o SidekiqBackground Workers

    automatically retries i replicagoes down

    load balances with weights

    Was running in production

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    51/101

    Special considerations

    Proprietary and

    Application must be tuned to support eventualconsistency. Data may not yet be on replica!

    Must explicitly force fetchrom the master DB whenits critical (i.e. afer a user accounts creation)

    We ofen use below pattern o first trying the etch, i

    nothing ound retry on master db

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    52/101

    Replicas can specialize

    Proprietary and

    Background Workers can use dedicated replica notshared with the app servers, to optimize hit rate orfile system cache (ARC) on both replicas

    PostgreSQL

    Master

    Unicorn / Passenger

    Ruby VM (times N)

    App ServersSidekiq / Resque

    Background Workers

    PostgreSQL

    Replica 1

    PostgreSQL

    Replica 2

    PostgreSQL

    Replica 3

    ARC cache warm with

    queries from web traffic

    ARC cache warm with

    background job queries

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    53/101

    Big heavy reads go there

    Proprietary and

    Long heavy queries should run by the background jobsagainst a dedicated replica, to isolate their effect on

    web traffic

    PostgreSQL

    Master

    Sidekiq / Resque

    Background Workers

    PostgreSQL

    Replica 1

    Each type o load will produce a unique set o datacached by the file system

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    54/101

    Proprietary and

    Step 5:Use more appropriate tools

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    55/101

    Leveraging other tools

    Proprietary and

    Not every type o data is well suited or storing in a relationalDB, even though initially it may be convenient

    Redisis a great data store or transient or semi-

    persistent data with list, hash or set semantics We use it or ActivityFeed by precomputing each feed at write

    time. But we can regenerate it i the data is lost rom Redis

    We use twemproxyin ront o Redis which provides automatic

    horizontal sharding and connection pooling.

    We run clusters of 256 redis shardsacross many virtual zones;sharded redis instances use many cores, instead o one

    Solris great or ull text search, and deep paginatedsorted lists, such as trending, or related products

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    56/101

    Proprietary and

    True story: applying WAL logs onreplicas creates significant disk write load

    But we still have single master DB taking

    all the writes

    Replicas are unable to both serve live traffic andcatch up on replication. They fall behind.

    Back to PostgreSQL

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    57/101

    Proprietary and

    When replicas fall behind, application generateserrors, unable to find data it expects

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    58/101

    Proprietary and

    Step 6:Move write-heavy tables out:Replace with non-DB solutions

    M l

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    59/101

    Move event log out

    Proprietary and

    We were appending all user events into this table

    We were generating millions of rows per day!

    We solved it by replacing user event recording system touse rsyslog, appending to ASCII files

    We discovered rom pg_stat_user_tables top table bywrite volume was user_events

    Its cheap, reliable and scalable

    We now useJoyents Manta to analyze this data inparallel. Manta is an object store + native compute on

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    60/101

    Proprietary and

    For more inormation about how we migrateduser events to a file-based append-only log, and

    analyze it with Manta, please read

    http://wanelo.ly/event-collection

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    61/101

    Proprietary and

    Step 7:Tune PostgreSQL and yourFilesystem

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    62/101

    Tuning ZFS

    Proprietary and

    Problem: zones (virtual hosts) with writeproblems appeared to be writing 16 times

    more data to disk, compared to what virtual filesystem reports

    vfsstatsays 8Mb/sec write volume

    So whats going on?

    iostatsays 128Mb/sec is actually written to disk

    T i Fil t

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    63/101

    Proprietary and

    Turns out deault ZFS block size is 128Kb,

    and PostgreSQL page size is 8Kb.

    Every small write that touched a page, had towrite 128Kb o a ZFS block to the disk

    Tuning Filesystem

    This may be good or huge sequential writes,but not or random access, lots o tiny writes

    T ning ZFS & PgSQL

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    64/101

    Proprietary and

    Solution: Joyent changed ZFS block size or our zone,iostatwrite volume dropped to 8Mb/sec

    We also added commit_delay

    Tuning ZFS & PgSQL

    Installing and Configuring PG

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    65/101

    Proprietary and

    Many such settings are pre-defined in our open-sourceChef cookbookor installing PostgreSQL rom sources

    Installing and Configuring PG

    https://github.com/wanelo-chef/postgres

    It installs PG in eg /opt/local/postgresql-9.3.2

    It configures its data in /var/pgsql/data93

    It allows seamlessand safeupgrades o minor or majorversions o PostgreSQL, never overwriting binaries

    Additional resources online

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    66/101

    Additional resources online

    Proprietary and

    Josh Berkuss 5 steps to PostgreSQLPerormance on SlideShare is antastic

    PostgreSQL wiki pages on perormance tuning is

    excellent

    Run pgBench to determine and compareperormance o systems

    http://www.slideshare.net/PGExperts/five-steps-perorm2013

    http://wiki.postgresql.org/wiki/Perormance_Optimization

    http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    67/101

    Proprietary and

    Step 8:Buffer and serialize frequent

    updates

    Counters counters

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    68/101

    Counters, counters

    Proprietary and

    Problem: products.saves_countisincremented every time someone saves a

    product (by 1)

    At 200 inserts/sec, thats a lot o updates

    How can we reduce number of writesandlock contention?

    Worse: 100s o concurrent requests trying toobtain a row level lockon the same popularproduct

    Buffering and serializing

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    69/101

    Buffering and serializing

    Proprietary and

    Sidekiq background job ramework has twointer-related eatures:

    scheduling in the future(say 10 minutes ahead)

    UniqueJobextension

    Once every 10 minutes popular products are updated byadding a value stored in Redis to the database value, andresetting Redis value to 0

    We increment a counter in redis, and enqueue a jobthat says update product in 10 minutes

    Buffering explained

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    70/101

    Buffering explained

    Proprietary and

    Save Product

    Save Product

    Save Product

    1. enqueue update

    request for product

    with a delay

    PostgreSQL

    Update Request already

    on the queue

    3. Process Job

    Redis Cache

    2. increment

    counter

    4. Read & Reset to 0

    5. Update Product

    Buffering conclusions

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    71/101

    Buffering conclusions

    Proprietary and

    I not, to achieve read consistency, we candisplay the count as database value + redisvalue at read time

    I we show objects rom the database, theymight be sometimes behind on the counter. Itmight be ok

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    72/101

    Proprietary and

    Step 9:Optimize DB schema

    MVCC does copy on write

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    73/101

    MVCC does copy on write

    Proprietary and

    Problem: PostgreSQL rewrites the row or most updates (someexceptions exist, ie non-indexed column, a counter, timestamp)

    But we ofen index these so we can sort by them

    Rails and Hibernates partial updatesare not helping

    Are we updating User on each request?

    So updates can become expensiveon wide tables

    Schema tricks

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    74/101

    Schema tricks

    Proprietary and

    Solution: split wide tables into several 1-1tables to reduce update impact

    Much less vacuuming required when smallertables are requently updated

    id

    email

    d d

    Users

    id

    email

    Users

    refactor

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    75/101

    Proprietary and

    Dont update anything on each request :)

    encrypted_password

    reset_password_tokenreset_password_sent_at

    remember_created_at

    sign_in_count

    current_sign_in_at

    last_sign_in_at

    current_sign_in_ip

    last_sign_in_ip

    confirmation_token

    confirmed_atconfirmation_sent_at

    unconfirmed_email

    failed_attempts

    unlock_token

    locked_at

    authentication_token

    created_at

    updated_at

    username

    avatar

    state

    followers_count

    saves_count

    collections_count

    stores_count

    following_count

    stories_count

    created_at

    usernameavatar

    state

    user_id

    encrypted_passwordreset_password_token

    reset_password_sent_at

    remember_created_at

    sign_in_count

    current_sign_in_at

    last_sign_in_at

    current_sign_in_ip

    last_sign_in_ip

    confirmation_token

    confirmed_at

    confirmation_sent_at

    unconfirmed_email

    failed_attempts

    unlock_token

    locked_at

    authentication_token

    updated_at

    UserLogins

    user_id

    followers_countsaves_count

    collections_count

    stores_count

    following_count

    stories_count

    UserCounts

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    76/101

    Proprietary and

    Step 10:Shard Busy Tables Vertically

    Vertical sharding

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    77/101

    g

    Proprietary and

    Heavy tables with too many writes, can bemoved into their own separate database

    For us it was saves: now @ 2B+ rows

    At hundreds o inserts per second, and 4 indexes,we were eeling the pain

    It turns out moving a single table (in Rails) out isa not a huge effort: it took our team 3 days

    Vertical sharding - how to

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    78/101

    g

    Proprietary and

    Update code to point to the new database

    Implement any dynamic Rails associationmethods as real methods with 2 etches

    ie. save.productsbecomes a method on Save

    model, lookup up Products by IDs

    Update development and test setup with two

    primary databases and fix all the tests

    Here the application

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    79/101

    Proprietary and

    Web App

    PostgreSQL

    Master (Main Schema)

    PostgreSQL

    Replica (Main Schema)

    Vertically Sharded Database

    PostgreSQL

    Master (Split Table)

    connects to main masterDB + replicas, and a singlededicated DB or the busy

    table we moved

    Vertical sharding, deploying

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    80/101

    g, p y g

    Proprietary and

    Drop in write IO on the main DB afer splitting offthe high IOtable into a dedicated compute node

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    81/101

    Proprietary and

    For a complete and more detailed account oour vertical sharding effort, please read our

    blog post:

    http://wanelo.ly/vertical-sharding

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    82/101

    Proprietary and

    Step 11:Wrap busy tables with services

    Splitting off services

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    83/101

    Proprietary and

    Vertical Sharding is a great precursorto amicro-services architecture

    New service: Sinatra, client and server libs,updated tests & development, CI, deployment

    without changing db schema 2-3 weeks a pair of engineerslevel o effort

    We already have Saves in another database,lets migrate it to a light-weight HTTP service

    Adapter pattern to the rescue

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    84/101

    Proprietary and

    Main AppUnicorn w/ Rails

    PostgreSQLHTTP

    Client Adapter

    Service App

    Unicorn w/Sinatra

    Native

    Client Adaptor

    We used Adapter pattern to write two clientadapters: native and HTTP, so we can use the lib,but not yet switch to HTTP

    Services conclusions

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    85/101

    Proprietary and

    Now we can independently scale service

    backend, in particular reads by using replicas

    This prepares us or the next inevitable step:horizontal sharding

    At a cost o added request latency, lots o extracode, extra runtime inrastructure, and 2

    weeks of work

    Do this only i you absolutely have to

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    86/101

    Proprietary and

    Step 12:Shard Services Backend

    Horizontally

    Horizontal sharding in ruby

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    87/101

    Proprietary and

    We wanted to stick with PostgreSQL or criticaldata such as saves

    Really liked Instagrams approach with schemas

    Built our own schema-based shardingin ruby,

    on top o Sequel gem, and open sourced it

    It supports mapping o physical to logical shards,and connection pooling

    https://github.com/wanelo/sequel-schema-sharding

    Schema design for sharding

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    88/101

    Proprietary and

    https://github.com/wanelo/sequel-schema-sharding

    user_id

    product_idcollection_idcreated_at

    index__on_user_id_and_collection_id

    UserSaves Sharded by user_id

    product_id

    user_idupdated_at

    index__on_product_id_and_user_idindex__on_product_id_and_updated_at

    ProductSaves Sharded by product_idWe needed two lookups, by user_idand by product_id hence we neededtwo tables, independently sharded

    Since saves is a join table betweenuser, product, collection, we did not

    need unique ID generated

    Composite base62 encoded ID:fpua-1BrV-1kKEt

    Spreading your shards

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    89/101

    Proprietary and

    We split saves into 8192 logical shards,distributed across 8 PostgreSQL databases

    Running on 8 virtual zonesspanning 2 physical SSDservers, 4 per compute node

    Each database has 1024schemas (twice, because we

    sharded saves into two tables)

    https://github.com/wanelo/sequel-schema-sharding

    2 x 32-core 256GB RAM

    16-drive SSD RAID10+2

    PostgreSQL 9.3

    1

    3 4

    2

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    90/101

    Proprietary and

    Sampleconfiguration oshard mapping tophysical nodeswith readreplicas,supported by thelibrary

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    91/101

    Proprietary and

    How can we migrate the data rom old non-sharded backend to the new sharded backend

    without a long downtime?

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    92/101

    New records go to bothProprietary and

    HTTP Service

    Old Non-Sharded Backend

    New Sharded Backend

    1

    3 4

    2

    Read/Write

    Background

    Worker

    Enqueue

    Sidekiq Queue

    Create Save

    We migrated several times beore we got this right

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    93/101

    Proprietary and

    HTTP Service

    Old Non-Sharded Backend

    New Sharded Backend

    1

    3 4

    2

    Read/Write

    Background

    Worker

    Enqueue

    Sidekiq Queue

    Create Save

    Migration Script

    Migrate old rows

    Old Non-Sharded Backend

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    94/101

    Proprietary and

    Swap old and new backends

    HTTP Service

    New Sharded Backend

    1

    3 4

    2Read/Write

    Background

    Worker

    Enqueue

    Sidekiq Queue

    Create Save

    Horizontal sharding conclusions

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    95/101

    Proprietary and

    This is the final destination o any scalablearchitecture: just add more boxes

    Pretty sure we can now scale to 1,000, or 10,000inserts/second by scaling out

    Took 2 months of 2 engineers, including migration,but zero downtime. Its an advanced level effort andour engineers really nailed this.

    https://github.com/wanelo/sequel-schema-sharding

    Putting it all together

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    96/101

    Proprietary and

    This inrastructure complexity is not free

    It requires new automation, monitoring,

    graphing, maintenance and upgrades, and bringswith it a new source of bugs

    In addition, micro-services can be ownedby small teamsin the uture, achievingorganizational autonomy

    But the advantages are clear when scalingisone o the requirements

    4-core 16GB zones

    memcached

    iPhone Android Desktop clients

    MemCached Cluster

    Cluster of MemCached Servers

    is accessed via Dali fault tolerant library

    one or more can go down

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    97/101

    Proprietary and

    Systems Diagram

    incoming http

    requests

    8-core 8GB zones

    haproxy

    nginx

    Fastly CDNcache images, JS

    Load Balancers

    Amazon S3

    Product Images

    User Profile Pictures

    32-core 256GB 16-driv e SSD RAID10+2

    Supermicro "Richmond"

    SSD Make: Intel DCS3700,

    CPU: Intel E5-2690, 2.9GHz

    PostgreSQL 9.2

    Master

    Primary Database Schema

    User and Product Saves, Horizontally Sharded, Replicated

    32-core 256GB RAM

    16-drive SSD RAID10+2

    PostgreSQL 9.3

    1

    3 4

    2

    Read Replicas (non SSD)

    2

    4 2

    1

    Read Replica (SSD)

    PostgreSQL

    Async Replicas

    32-core 32GB high-CPU instances

    Unicorn

    Main Web/API App,

    Ruby 2.0

    Unicorn

    Saves Service

    haproxy

    pgbouncer

    iPhone, Android, Desktop clientsMakaradistributes DB

    load across 3 replicas

    and 1 master

    Redis Clusters for various custom

    user feeds, such as product feed

    1-core 1GB zones

    twemproxy

    Redis Proxy Cluster

    16GB high-mem 4-core zones

    32 redis instances per server

    redis-001

    redis-256

    8GB High CPU zones

    Solr Replica

    8GB High CPU zone

    Solr Master

    App Servers + Admin Servers

    Apache Solr Clusters

    32-core 32GB high-CPU instances

    Sidekiq Background

    Worker

    Unicorn

    Saves Service

    haproxy

    pgbouncer

    to DBs

    Solr Reads

    Solr Updates

    Background Worker Nodes

    redis

    Redis Sidekiq

    Jobs Queue / Bus

    Systems Status: Dashboard Monitoring & Graphing with Circonus, NewRelic, statsd, nagios

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    98/101

    Backend Stack & Key Vendors

    MRI R b jR b Si R b R il

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    99/101

    Proprietary and

    MRI Ruby, jRuby, Sinatra, Ruby on Rails

    PostgreSQL, Solr, redis, twemproxymemcached, nginx, haproxy, pgbouncer

    Joyent Cloud, SmartOS, Manta Object StoreZFS, ARC Cache, superb IO, SMF, Zones, dTrace, humans

    DynDNS, SendGrid, Che, SifScience

    LeanPlum, MixPanel, Graphite analytics, A/B Testing

    AWS S3 + Fastly CDN or user / product images

    Circonus, NewRelic, statsd, Boundary,PagerDuty, nagios:trending / monitoring / alerting

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    100/101

    Proprietary and

    We are hiring!

    DevOps, FullStack, Scaling Experts, iOS & Android

    Talk to me afer the presentation i you are interested in workingon real scalability problems, and on a product used and loved by millions :)

    http://wanelo.com/about/play

    Or email [email protected]

    Thanks!

  • 7/21/2019 12-Step Program for Scaling Web Application on PostgreSQL

    101/101

    Thanks!

    github.com/wanelo

    github.com/wanelo-che

    wanelo technical blog (srsly awsm)

    building.wanelo.com

    Proprietary and

    @kig

    @kig

    @kigster