Share
Embed
ISHOCON1 反省会
https://github.com/showwin/ISHOCON1 を社内ISUCONの問題として使用した時の解説スライドです。
ITO Shogo
September 01, 2016
Transcript
22 slides
-
comments.product_id ʹ INDEX ΛுΔ # MySQL > show create table
comments; CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `content` varchar(128) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200073 DEFAULT CHARSET=utf8mb4 > alter table comments add index pid_idx(product_id); -
EXPLAIN ͯ͠ΈΔ # MySQL > explain SELECT p.id, p.name, p.description,
p.image_path, p.price, h.created_at FROM histories as h LEFT OUTER JOIN products as p ON h.product_id = p.id WHERE h.user_id = '1234' ORDER BY h.id DESC; +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ | 1 | SIMPLE | h | index | NULL | PRIMARY | 4 | NULL | 500636 | Using where | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | ishocon1.h.product_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ # history.user_id ͷݕࡧʹ ref => NULL Ͱશݕࡧ͍ͯ͠Δ > alter table histories add index uid_idx(user_id); -
images Λ Nginx ͔Β৴ $ sudo vim /etc/nginx/nginx.conf location /images/
{ root /home/ishocon/webapp/ruby/public; } location / { proxy_pass http://app; } $ sudo service nginx restart # 403ʹͳΔ $ pwd /home $ ll drwx------ 3 ec2-user ec2-user 4096 Oct 5 2015 ec2-user drwx------ 11 ishocon ishocon 4096 Aug 9 20:38 ishocon $ chmod 777 ishocon -
͍ͭͰʹ… TCP → Unix domain Socket είΞ: 17585 (΄ͱΜͲมΘΓͳ͍…) $
vim unicorn_config.rb #listen 8080 listen ‘/tmp/unicorn.sock' $ sudo vim /etc/nginx/nginx.conf upstream app { #server localhost:8080; server unix:/tmp/unicorn.sock; } # unicorn ͱ Nginx ࠶ىಈ ༨༟͕Ͱ͖ͨ -
είΞ: 24163 24000(status 200) - 19000(image) = 5000 ݅ͷΞΫηε ͳͷʹɺ19ສճϦΫΤετ͕
users ςʔϒϧʹ… by kataribe by qt-query-digest -
# ݪҼ͍ͭͩ͜ def current_user db.xquery('SELECT * FROM users WHERE id
= ?', session[:user_id]).first end # Α͘ݟΔͱଞͷॴͰ3ύλʔϯͷΘΕํ͕͋Δ # 1. current_user[:id] # 2. current_user[:name] # 3. unless current_user # ໊લใ session ʹೖΕͯ͠·͑ʂ def current_user return unless session[:user_id] { id: session[:user_id] name: session[:user_name] } end είΞ: 28104 (+4000) -
• update_last_login (࠷ऴϩάΠϯ࣌ؒͷߋ৽) ॲཧଞͰ༻͍ͯ͠ͳͯ͘ෆཁͳͷ Ͱআ: 32465 (+600) • GET /logout
redirect login ͢Δͱ status code 3xx ʹͳΔͷͰ login ͷςϯϓϨʔ τΛ render ͯ͠ 200 Ͱฦ͢: 32866 (+400) • GET /users/:id user[:name] Λ Redis Ͱ user[:id] → user[:name] ͱGETͰ͖ΔΑ͏ ʹ͢Δ: 32405 (ޡࠩ…) • ίϝϯτͷ݅Λ Redis (INCR) Ͱཧ: 33308 (+900) • GET /users/:id ͷઆ໌70จࣈΛSQLͰ LEFT(p.description, 70) ʹͯऔಘ: 35479 (+2000) ΞϓϦέʔγϣϯվળ -
• GET / ֤ product ͷ࠷৽ίϝϯτ5ͭͷ id Λ Redis ͷϦετͰอଘɻදࣔ
͢Δ50ݸͷ product ͷ comment_id Λશ෦औಘͯ͠ɺSELECT * FROM comments WHERE id IN (…) ORDER BY product_id DESC, created_at Ͱ औಘ͢Δɻ͜Εͩͱίϝϯτͨ͠Ϣʔβ໊͕औಘͰ͖ͳ͍ͷͰɺ user_id→user_name ʹม͑ͨ comments_user_name ςʔϒϧΛ৽͘͠ ࡞Δ: 45463 (+10000) • GET / ͷϖʔδωʔγϣϯ LIMIT 50 OFFSET page*50 Λ WHERE IN (ids) ʹ͢Δ: 50193 (+5000) ΞϓϦέʔγϣϯվળ -
• GET /products/:id SELECT * FROM comments ͍ͯ͠Δ͚Ͳ view Ͱͦ
ͷใΛ͍ͬͯͳ͍ͷͰআ: 52659 (+2500) • GET /logout Nginx Ͱ Cookie ͷΛআͯ͠(ϩάΞτॲཧ)ɺ੩తϑΝ ΠϧΛฦ͢: 55236 (+2500) • GET /users/:id ߪೖ߹ܭֹۚΛ Redis Ͱܭࢉɻˠ DB͔ΒߪೖཤྺΛ LIMIT 30 ͰͱΕΔ: 63489 (+8000) ΞϓϦέʔγϣϯվળ