learning System design as a landscape architect 2

Tue, Mar 1, 2022 3-minute read

Rethink system design in a much fun way, as a former urban planner/landscape planner. Take Flash Sale as example

what’s is the <strong>scenario</strong> planning of this project
Service
select database for each service
design schema
data flow
Limited Time Offer

Flash Sale –

1. what’s is the scenario planning of this project

1000 people were coming to the site per second, but there will be 100 k people when an attractive limited-time offer include a big discount.

QPS then increased by 100

Analyzing flash sale Problems

Large flow and high concurrency
Over sale
Malicious ticked grab
fixed schedule
Purchase limit

Demand disassembly shops:

register for sales on
set up detail information

clients:

flash sale page
buy
order
pay

2.1 Service

Gateway

limited time pomotion Service + database
Commodity info & Stock service + database
Order Service + database
Payment Service + database

2.2 select database for each service

2.3 design schema

commodity_info

commodity_id    name    desc    price
11             iphone   xxxxx   5999

stock_info

stock_id   commodity_id    event_id    stock    lock
1             11               0        300        0
2             11               30        10        5

flashSale_info

flashSale_id    flashSale_name    commodity_id    price     number    
30                  xxxx               189        4000        100

order_info

order_id        flashSale_id     commodity_id    user_id     paid   
30                  30              189             jack       1

Indexing can dramatically increase query speed

2.4 data flow

store –select –> commodity_info |—-insert –> stock_info |–select –> flashSale_info

user –select –> flashSale_info–> commodity_info–> stock_info |—-insert –> order_info |–update –> stock_info

2.5 Limited Time Offer

deduct a quantity from a database

SELECT stock FROM `stock_info`
WHERE commodity_id = 11 AND flashSale_id = 30;

UPDATE `stock_info` SET stock = stock - 1
WHERE commodity_id = 11 AND flashSale_id = 30;

solve oversale problem

transaction database and lock (slow)

1. Start Transcation
1. add lock

SELECT stock FROM `stock_info`
WHERE commodity_id = 11 AND flashSale_id = 30 FOR UPDATE;

1. deducted

UPDATE `stock_info` SET stock = stock - 1
WHERE commodity_id = 11 AND flashSale_id = 30;

1. commit

1. add lock

SELECT stock FROM `stock_info`
WHERE commodity_id = 11 AND flashSale_id = 30;

1. deducted

UPDATE `stock_info` SET stock = stock - 1
WHERE commodity_id = 11 AND flashSale_id = 30 AND stock > 0;

how about too many concurrent connections cause mysql crash

On average, a mysql web server can handle 1000 requests per second but a single point of Redis can support 100,000 QPS. Consider loading stock information into Redis. update quantity directly through Redis

before the event begin,

store –> read from mysql flashSale_info–>`add in Redis

SET flashSale:30:commodity:11:stock 10

SET KEY(flashSale:30:commodity:11:stock) VALUE (10)

deduct quantity through Redis

data flow:

user –> read from redis –> stock > 0 – NO – > the end of event

user –> read from redis –> stock > 0 – YES – > stock - 1 (redis) –> lock the quentity, create order –> pay –> stock -1

GET flashSale:30:commodity:11:stock

DECR flashSale:30:commodity:11:stock

Most requests are blocked by Redis.

For example, if there are only 100 iPhones, then The volume of requests to MySQL is theoretically 100.

over sale problem

Lua: check-and-set

the limited time traffic is still too much for mysql, sell 100000

Message Queue for the Peak Clipping

deduct quantity timing

Lock the stock after placing the order, and reduce the quantity after the payment is successful

Limit Orders Per Customer

Redis data verification

I'm Riley Shen